RE: how many LIOs is too many
Date: Sun, 26 Jan 2014 20:41:38 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D901DD8694_at_exmbx05.thus.corp>
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-lReceived on Sun Jan 26 2014 - 21:41:38 CET