Re: how many LIOs is too many

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_at_jlcomp.demon.co.uk
> 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
> _at_jloracle
> ------------------------------
> *From:* oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on
> behalf of Ram Raman [veeeraman_at_gmail.com]
> *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.
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l