Few areas we need to investigate * Size of the table * Indexes (e.g., there are 2 filters and one ORDER BY) => preferably, index should be on STATUS_ID => indexes for SELECT have to be balanced with possible impact on INSERT operations, especially if there are periodic bulk INSERT with the index getting rebuilt for each row as opposed to each INSERT statement * Recency of table statistics (is the database/table analyzed regularly) * Database parameter settings * What is the location of the "Out of Memory" error?
-Abhai -----Original Message----- From: Daniel Riquelme [mailto:[email protected]] Sent: Saturday, June 27, 2009 5:50 PM To: [email protected] Subject: Re: Slow EntityListIterator 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 >> > > http://www.mindtree.com/email/disclaimer.html http://www.mindtree.com/email/disclaimer.html
