When the distinct option is removed the query returns in less than a sec. So the problem must be database related.
On Thu, Jun 25, 2009 at 8:30 PM, Daniel Riquelme <[email protected]>wrote: > Hi, > > I have 4.000.000+ Sales Orders I need to migrate to ofbiz.- > I've already have set up an Oracle database with the data. > The problem is that when I click on the ordermgr link the page never > displays, the browser keeps waiting for ever. > Eventually, the transactions gets timed out. > > I've been investigating this problem and found that is not common. > > I'm using the oracle driver version 11.1 with default ofbiz configuration. > The database is oracle 10g r2. > > I came to the conclusion that my database is not configured properly. > Nevertheles I would like to share this problem with you, perhaps there is a > simple solution to it that I'am missing. > > I have already read the posts regarding fetch size configuration. I've > tested with fetch-size=50 and fetch-size=500 with no results. > > An ofbiz debug shows that the slow operation is in: > org.ofbiz.entity.datasource.selectListIteratorByCondition > > The call that never returns is: > sqlP.executeQuery(); > > The SQL query is (as reported by oracle): > SELECT DISTINCT ORDER_ID, ORDER_TYPE_ID, ORDER_NAME, EXTERNAL_ID, > SALES_CHANNEL_ENUM_ID, ORDER_DATE, ENTRY_DATE, VISIT_ID, STATUS_ID, > CREATED_BY, FIRST_ATTEMPT_ORDER_ID, CURRENCY_UOM, SYNC_STATUS_ID, > BILLING_ACCOUNT_ID, ORIGIN_FACILITY_ID, WEB_SITE_ID, PRODUCT_STORE_ID, > TERMINAL_ID, TRANSACTION_ID, AUTO_ORDER_SHOPPING_LIST_ID, > NEEDS_INVENTORY_ISSUANCE, IS_RUSH_ORDER, INTERNAL_CODE, REMAINING_SUB_TOTAL, > GRAND_TOTAL, LAST_UPDATED_STAMP, LAST_UPDATED_TX_STAMP, CREATED_STAMP, > CREATED_TX_STAMP, RECIBIR_BODEGAL, RECEPCIONADA_BODEGAL, > FECHA_RECEPCION_BODEGAL FROM ERP.ORDER_HEADER WHERE ((STATUS_ID = :v0 OR > STATUS_ID = :v1 OR STATUS_ID = :v2) AND (ORDER_TYPE_ID = :v3)) ORDER BY > ORDER_DATE DESC > > Oracle reports that the session is waiting on a: > db file scattered read > > > I'm not an expert on this subject. > Any one has a tip ? > > Thanks, > Daniel >
