Slow EntityListIterator
Locked
 
|
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 |
|
|
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 <[hidden email]>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 > |
|
|
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 <[hidden email] > 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 < > [hidden email]> 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 >> > > |
|
|
In reply to this post by Daniel Riquelme
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 <[hidden email] >> 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 < >> [hidden email]> 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. |
|
|
In reply to this post by Daniel Riquelme
Is this in your custom code or in something that exists in OFBiz? If it is in OFBiz I'd like to change it... This problem has been around for a long time and is actually documented thoroughly in the EntityListIterator JavaDocs, but that doesn't mean all developers follow the recommendations there! -David On Jun 27, 2009, at 6:49 PM, Daniel Riquelme wrote: > 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 <[hidden email] >> 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 < >> [hidden email]> 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 >>> >> >> |
|
|
Hi,
This code is in org.ofbiz.order.order.OrderListState. A possible workaround would be to to do a select count(*). Any more suggestions ? On Sat, Jun 27, 2009 at 10:14 PM, David E Jones <[hidden email]> wrote: > > Is this in your custom code or in something that exists in OFBiz? If it is > in OFBiz I'd like to change it... > > This problem has been around for a long time and is actually documented > thoroughly in the EntityListIterator JavaDocs, but that doesn't mean all > developers follow the recommendations there! > > -David > > > > On Jun 27, 2009, at 6:49 PM, Daniel Riquelme wrote: > > 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 < >> [hidden email] >> >>> 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 < >>> [hidden email]> 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 >>>> >>>> >>> >>> > |
|
|
A bit of investigation shows that this is already implemented in
selectCountByCondition. Daniel On Sun, Jun 28, 2009 at 10:16 AM, Daniel Riquelme <[hidden email] > wrote: > Hi, > > This code is in org.ofbiz.order.order.OrderListState. > A possible workaround would be to to do a select count(*). > Any more suggestions ? > > > On Sat, Jun 27, 2009 at 10:14 PM, David E Jones <[hidden email]> wrote: > >> >> Is this in your custom code or in something that exists in OFBiz? If it is >> in OFBiz I'd like to change it... >> >> This problem has been around for a long time and is actually documented >> thoroughly in the EntityListIterator JavaDocs, but that doesn't mean all >> developers follow the recommendations there! >> >> -David >> >> >> >> On Jun 27, 2009, at 6:49 PM, Daniel Riquelme wrote: >> >> 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 < >>> [hidden email] >>> >>>> 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 < >>>> [hidden email]> 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 >>>>> >>>>> >>>> >>>> >> > |
|
|
In reply to this post by Daniel Riquelme
Interesting... looks like you're referring to line number 255 in OrderListState.java, is that correct? When I responded before I was incorrect, thinking of the "previous" method instead of the "last" method. If the "last" method really causes the JDBC driver to iterate of EVERY record then that is a REAL bummer! Most databases and JDBCs drivers will do a simple operation and jump to the last result instead of iterating through each one. This is used in other places in OFBiz to determine the result size without doing an additional query (which may also result in a different number because other things may have happened between the two queries). BTW, for those reading in: there is a convenience method in the ELI for doing this: getResultsSizeAfterPartialList(). We could change these to use a selectCount* instead of using EntityListIterator.last or .getResultsSizeAfterPartialList(), but I wonder if that would actually be slower on some databases. Has anyone played around with this more? Daniel: have you tried Oracle with any other JDBC drivers? If you're using the ones from Oracle I know historically they have had a number of issues and 3rd part drivers usually result in FAR better performance and resource utilization. -David On Jun 28, 2009, at 8:16 AM, Daniel Riquelme wrote: > Hi, > > This code is in org.ofbiz.order.order.OrderListState. > A possible workaround would be to to do a select count(*). > Any more suggestions ? > > On Sat, Jun 27, 2009 at 10:14 PM, David E Jones <[hidden email]> wrote: > >> >> Is this in your custom code or in something that exists in OFBiz? >> If it is >> in OFBiz I'd like to change it... >> >> This problem has been around for a long time and is actually >> documented >> thoroughly in the EntityListIterator JavaDocs, but that doesn't >> mean all >> developers follow the recommendations there! >> >> -David >> >> >> >> On Jun 27, 2009, at 6:49 PM, Daniel Riquelme wrote: >> >> 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 < >>> [hidden email] >>> >>>> 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 < >>>> [hidden email]> 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 >>>>> >>>>> >>>> >>>> >> |
|
|
Hi David, I have tested DataDirect driver with the same slow result.
I must be missing something. I have checked that caching of every row occurs by debugging. Obviously, I don't have the code to any of the jdbc drivers in question but I'm still able to see what methods are been called and it's pretty obvious that the driver is traversing and caching every single row. Maybe there is a configuration that disables this behaviour ? Daniel On Sun, Jun 28, 2009 at 12:04 PM, David E Jones <[hidden email]> wrote: > > Interesting... looks like you're referring to line number 255 in > OrderListState.java, is that correct? > > When I responded before I was incorrect, thinking of the "previous" method > instead of the "last" method. If the "last" method really causes the JDBC > driver to iterate of EVERY record then that is a REAL bummer! Most databases > and JDBCs drivers will do a simple operation and jump to the last result > instead of iterating through each one. > > This is used in other places in OFBiz to determine the result size without > doing an additional query (which may also result in a different number > because other things may have happened between the two queries). BTW, for > those reading in: there is a convenience method in the ELI for doing this: > getResultsSizeAfterPartialList(). > > We could change these to use a selectCount* instead of using > EntityListIterator.last or .getResultsSizeAfterPartialList(), but I wonder > if that would actually be slower on some databases. > > Has anyone played around with this more? > > Daniel: have you tried Oracle with any other JDBC drivers? If you're using > the ones from Oracle I know historically they have had a number of issues > and 3rd part drivers usually result in FAR better performance and resource > utilization. > > -David > > > > On Jun 28, 2009, at 8:16 AM, Daniel Riquelme wrote: > > Hi, >> >> This code is in org.ofbiz.order.order.OrderListState. >> A possible workaround would be to to do a select count(*). >> Any more suggestions ? >> >> On Sat, Jun 27, 2009 at 10:14 PM, David E Jones <[hidden email]> wrote: >> >> >>> Is this in your custom code or in something that exists in OFBiz? If it >>> is >>> in OFBiz I'd like to change it... >>> >>> This problem has been around for a long time and is actually documented >>> thoroughly in the EntityListIterator JavaDocs, but that doesn't mean all >>> developers follow the recommendations there! >>> >>> -David >>> >>> >>> >>> On Jun 27, 2009, at 6:49 PM, Daniel Riquelme wrote: >>> >>> 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 < >>>> [hidden email] >>>> >>>> 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 < >>>>> [hidden email]> 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 >>>>>> >>>>>> >>>>>> >>>>> >>>>> >>> > |
|
|
In reply to this post by David E. Jones-2
not sure if this is going to help. I am not an oracle person.
but since this does not happen on other DB, I did a search on google for oracle get last one solution i found used a Select max(fieldname) here is the thread http://www.daniweb.com/forums/thread93986.html# I would suggest you ask on Oracle support. Daniel Riquelme sent the following on 6/29/2009 9:05 AM: > Hi David, I have tested DataDirect driver with the same slow result. > I must be missing something. > I have checked that caching of every row occurs by debugging. Obviously, I > don't have the code to any of the jdbc drivers in question but I'm still > able to see what methods are been called and it's pretty obvious that the > driver is traversing and caching every single row. > Maybe there is a configuration that disables this behaviour ? > > Daniel > > On Sun, Jun 28, 2009 at 12:04 PM, David E Jones <[hidden email]> wrote: > >> Interesting... looks like you're referring to line number 255 in >> OrderListState.java, is that correct? >> >> When I responded before I was incorrect, thinking of the "previous" method >> instead of the "last" method. If the "last" method really causes the JDBC >> driver to iterate of EVERY record then that is a REAL bummer! Most databases >> and JDBCs drivers will do a simple operation and jump to the last result >> instead of iterating through each one. >> >> This is used in other places in OFBiz to determine the result size without >> doing an additional query (which may also result in a different number >> because other things may have happened between the two queries). BTW, for >> those reading in: there is a convenience method in the ELI for doing this: >> getResultsSizeAfterPartialList(). >> >> We could change these to use a selectCount* instead of using >> EntityListIterator.last or .getResultsSizeAfterPartialList(), but I wonder >> if that would actually be slower on some databases. >> >> Has anyone played around with this more? >> >> Daniel: have you tried Oracle with any other JDBC drivers? If you're using >> the ones from Oracle I know historically they have had a number of issues >> and 3rd part drivers usually result in FAR better performance and resource >> utilization. >> >> -David >> >> >> >> On Jun 28, 2009, at 8:16 AM, Daniel Riquelme wrote: >> >> Hi, >>> This code is in org.ofbiz.order.order.OrderListState. >>> A possible workaround would be to to do a select count(*). >>> Any more suggestions ? >>> >>> On Sat, Jun 27, 2009 at 10:14 PM, David E Jones <[hidden email]> wrote: >>> >>> >>>> Is this in your custom code or in something that exists in OFBiz? If it >>>> is >>>> in OFBiz I'd like to change it... >>>> >>>> This problem has been around for a long time and is actually documented >>>> thoroughly in the EntityListIterator JavaDocs, but that doesn't mean all >>>> developers follow the recommendations there! >>>> >>>> -David >>>> >>>> >>>> >>>> On Jun 27, 2009, at 6:49 PM, Daniel Riquelme wrote: >>>> >>>> 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 < >>>>> [hidden email] >>>>> >>>>> 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 < >>>>>> [hidden email]> 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. |
|
|
Thanks BJ I'll take a look at that link.
Nevertheless I've been thinking about this possible problem with oracle and I became concerned with the fact that oracle is one of the most used database engines in production today. I'm aware that no one else is reporting this issue so I'm confident that the problem must be configuration related. I will test this with postgres to see what happens. The MySQL driver has the same slow behaviour. Thanks a lot for the commenst so far On Mon, Jun 29, 2009 at 1:23 PM, BJ Freeman <[hidden email]> wrote: > not sure if this is going to help. I am not an oracle person. > but since this does not happen on other DB, I did a search on google for > oracle get last > one solution i found used a Select max(fieldname) > here is the thread > http://www.daniweb.com/forums/thread93986.html# > > I would suggest you ask on Oracle support. > > > Daniel Riquelme sent the following on 6/29/2009 9:05 AM: > > Hi David, I have tested DataDirect driver with the same slow result. > > I must be missing something. > > I have checked that caching of every row occurs by debugging. Obviously, > I > > don't have the code to any of the jdbc drivers in question but I'm still > > able to see what methods are been called and it's pretty obvious that the > > driver is traversing and caching every single row. > > Maybe there is a configuration that disables this behaviour ? > > > > Daniel > > > > On Sun, Jun 28, 2009 at 12:04 PM, David E Jones <[hidden email]> wrote: > > > >> Interesting... looks like you're referring to line number 255 in > >> OrderListState.java, is that correct? > >> > >> When I responded before I was incorrect, thinking of the "previous" > method > >> instead of the "last" method. If the "last" method really causes the > JDBC > >> driver to iterate of EVERY record then that is a REAL bummer! Most > databases > >> and JDBCs drivers will do a simple operation and jump to the last result > >> instead of iterating through each one. > >> > >> This is used in other places in OFBiz to determine the result size > without > >> doing an additional query (which may also result in a different number > >> because other things may have happened between the two queries). BTW, > for > >> those reading in: there is a convenience method in the ELI for doing > this: > >> getResultsSizeAfterPartialList(). > >> > >> We could change these to use a selectCount* instead of using > >> EntityListIterator.last or .getResultsSizeAfterPartialList(), but I > wonder > >> if that would actually be slower on some databases. > >> > >> Has anyone played around with this more? > >> > >> Daniel: have you tried Oracle with any other JDBC drivers? If you're > using > >> the ones from Oracle I know historically they have had a number of > issues > >> and 3rd part drivers usually result in FAR better performance and > resource > >> utilization. > >> > >> -David > >> > >> > >> > >> On Jun 28, 2009, at 8:16 AM, Daniel Riquelme wrote: > >> > >> Hi, > >>> This code is in org.ofbiz.order.order.OrderListState. > >>> A possible workaround would be to to do a select count(*). > >>> Any more suggestions ? > >>> > >>> On Sat, Jun 27, 2009 at 10:14 PM, David E Jones <[hidden email]> wrote: > >>> > >>> > >>>> Is this in your custom code or in something that exists in OFBiz? If > it > >>>> is > >>>> in OFBiz I'd like to change it... > >>>> > >>>> This problem has been around for a long time and is actually > documented > >>>> thoroughly in the EntityListIterator JavaDocs, but that doesn't mean > all > >>>> developers follow the recommendations there! > >>>> > >>>> -David > >>>> > >>>> > >>>> > >>>> On Jun 27, 2009, at 6:49 PM, Daniel Riquelme wrote: > >>>> > >>>> 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 < > >>>>> [hidden email] > >>>>> > >>>>> 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 < > >>>>>> [hidden email]> 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. > > |
|
|
In reply to this post by BJ Freeman
Just a side note on slow. from a pure DB perspective I have found adding
indexes especially on joins speeds up things. I only tested on 100K+ records. Postgresql and MsSQL Daniel Riquelme sent the following on 6/29/2009 5:50 PM: > Thanks BJ I'll take a look at that link. > Nevertheless I've been thinking about this possible problem with oracle and > I became concerned with the fact that oracle is one of the most used > database engines in production today. > I'm aware that no one else is reporting this issue so I'm confident that the > problem must be configuration related. > I will test this with postgres to see what happens. > The MySQL driver has the same slow behaviour. > > Thanks a lot for the commenst so far > > On Mon, Jun 29, 2009 at 1:23 PM, BJ Freeman <[hidden email]> wrote: > >> not sure if this is going to help. I am not an oracle person. >> but since this does not happen on other DB, I did a search on google for >> oracle get last >> one solution i found used a Select max(fieldname) >> here is the thread >> http://www.daniweb.com/forums/thread93986.html# >> >> I would suggest you ask on Oracle support. >> >> >> Daniel Riquelme sent the following on 6/29/2009 9:05 AM: >>> Hi David, I have tested DataDirect driver with the same slow result. >>> I must be missing something. >>> I have checked that caching of every row occurs by debugging. Obviously, >> I >>> don't have the code to any of the jdbc drivers in question but I'm still >>> able to see what methods are been called and it's pretty obvious that the >>> driver is traversing and caching every single row. >>> Maybe there is a configuration that disables this behaviour ? >>> >>> Daniel >>> >>> On Sun, Jun 28, 2009 at 12:04 PM, David E Jones <[hidden email]> wrote: >>> >>>> Interesting... looks like you're referring to line number 255 in >>>> OrderListState.java, is that correct? >>>> >>>> When I responded before I was incorrect, thinking of the "previous" >> method >>>> instead of the "last" method. If the "last" method really causes the >> JDBC >>>> driver to iterate of EVERY record then that is a REAL bummer! Most >> databases >>>> and JDBCs drivers will do a simple operation and jump to the last result >>>> instead of iterating through each one. >>>> >>>> This is used in other places in OFBiz to determine the result size >> without >>>> doing an additional query (which may also result in a different number >>>> because other things may have happened between the two queries). BTW, >> for >>>> those reading in: there is a convenience method in the ELI for doing >> this: >>>> getResultsSizeAfterPartialList(). >>>> >>>> We could change these to use a selectCount* instead of using >>>> EntityListIterator.last or .getResultsSizeAfterPartialList(), but I >> wonder >>>> if that would actually be slower on some databases. >>>> >>>> Has anyone played around with this more? >>>> >>>> Daniel: have you tried Oracle with any other JDBC drivers? If you're >> using >>>> the ones from Oracle I know historically they have had a number of >> issues >>>> and 3rd part drivers usually result in FAR better performance and >> resource >>>> utilization. >>>> >>>> -David >>>> >>>> >>>> >>>> On Jun 28, 2009, at 8:16 AM, Daniel Riquelme wrote: >>>> >>>> Hi, >>>>> This code is in org.ofbiz.order.order.OrderListState. >>>>> A possible workaround would be to to do a select count(*). >>>>> Any more suggestions ? >>>>> >>>>> On Sat, Jun 27, 2009 at 10:14 PM, David E Jones <[hidden email]> wrote: >>>>> >>>>> >>>>>> Is this in your custom code or in something that exists in OFBiz? If >> it >>>>>> is >>>>>> in OFBiz I'd like to change it... >>>>>> >>>>>> This problem has been around for a long time and is actually >> documented >>>>>> thoroughly in the EntityListIterator JavaDocs, but that doesn't mean >> all >>>>>> developers follow the recommendations there! >>>>>> >>>>>> -David >>>>>> >>>>>> >>>>>> >>>>>> On Jun 27, 2009, at 6:49 PM, Daniel Riquelme wrote: >>>>>> >>>>>> 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 < >>>>>>> [hidden email] >>>>>>> >>>>>>> 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 < >>>>>>>> [hidden email]> 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. >> >> > -- 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. |
|
|
In reply to this post by Daniel Riquelme
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:[hidden email]] Sent: Saturday, June 27, 2009 5:50 PM To: [hidden email] 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 <[hidden email] > 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 < > [hidden email]> 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 >> 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, >> 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 |
«
Return to OFBiz - User
|
1 view|%1 views
| Free forum by Nabble | Edit this page |