this did not mention anything about this issue but for reference http://docs.ofbiz.org/display/~jacopoc/OFBiz+and+Oracle
then type in the search box oracle I see some others but did not review them. I would suggest you take that query an run it external to ofbiz not familiar with oracle tools to suggest how. see if you get the same results. may be an index problem. Maybe cache of the results using memory. I remember some thing vague about cursors an oracle cursors allow only a segment the last command may send a query for the whole result set. there may be a workaround for this but I think you will find it in the oracle group not in ofbiz. Daniel Riquelme sent the following on 6/27/2009 5:49 PM: > Hi, after a few hours of investigation and database tunning I figured it > out. > The queryhas been optimized to run in 40~70 seconds. > The ofbiz log displays a line like the following: > > Ran query in 64695 milli-seconds: SELECT 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 = ? OR > STATUS_ID = ? OR STATUS_ID = ? OR STATUS_ID = ? OR STATUS_ID = ? OR > STATUS_ID = ? OR STATUS_ID = ?) AND (ORDER_TYPE_ID = ?)) ORDER BY ORDER_DATE > DESC > > After a very long wait an out of memory error occurs. > The VM has already been configured to use 3Gigs.- > > The method that actually never returns and consumes all available memory is > EntityListIterator.last > Which handles the job to the jdbc driver ScrollableResultSet.last() method > This methos caches every single line returned by the query. > > The Oracle documentation says the following about Scrollable Result Sets: > > "If the ResultSet is very large, resultset.last() may be a very > time-consuming operation, since it will use more resources on the server > side. So, unless you really need a scrollable ResultSet, refrain from using > this approach. " > This part of the documentation talks about the possible techniques for > counting the total number of rows in a query. > > After commenting the code not to use the EntityListIterator.last() the > request displays in about the same time the query finishes. > > Thanks a lot for your help, > Daniel > > On Fri, Jun 26, 2009 at 10:11 AM, Daniel Riquelme <[email protected] >> wrote: > >> 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 >>> >> > -- BJ Freeman http://www.businessesnetwork.com/automation http://bjfreeman.elance.com http://www.linkedin.com/profile?viewProfile=&key=1237480&locale=en_US&trk=tab_pro Systems Integrator.
