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 <[email protected]> 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 <[email protected]> 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 < >>>>> [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.
