All, After I added the index as suggested by Jonathan the usage dropped significantly in prod. I had to wait for approvals and stuff before I put that in. Many thanks to Jonathan, once again. Ram. On Sun, Jan 26, 2014 at 2:41 PM, Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx > wrote: > > > It looks like your showing the execution plan predications, not the actual > run time when you say 4:14 vs. 0:03. > > The plan you've got shows a index full scan, which is going read (nearly) > every leaf block in the index and that might take longer than predicted > because of the costing bug which I mentioned. I think you may have to > engineer a "use_concat " hint into the code - through an SQL Baseline, > given it's 3rd party code - to get the performance you really need. (On > the other hand, it's possibly that even with the index full scan you may > end up with the query running faster than it is at present; it's also > possible that the concatenation path will appear automatically with the > queries and statistics available on the production system) > > > > > > Regards > Jonathan Lewis > http://jonathanlewis.wordpress.com > @jloracle > ------------------------------ > *From:* oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on > behalf of Ram Raman [veeeraman@xxxxxxxxx] > *Sent:* 26 January 2014 00:43 > > *Cc:* ORACLE-L > *Subject:* Re: how many LIOs is too many > > List, > > Jonathan was spot on. As soon as I added an index on (maudrecno, memrecno) > in the test database the time dropped from 4:14 to 3 seconds. > > I have copied and pasted the outputs from the dev environment: > > https://docs.google.com/file/d/0B9YC82qZ8_3eYlJGalRVQTM3cUU/edit > > Thanks for all the people who helped. > > Ram. > > > >