Re: Slow SQL, too many logical reads ?

On May 4, 2:03 pm, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> "Charles Hooper" <hooperc2..._at_yahoo.com> wrote in message
> ALTER SESSION SET STATISTICS_LEVEL=ALL;
>
> (Run your query)
>
> SELECT
> *
> FROM
> TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS'));
> ---------------
>
> For instance, ALLSTATS (note, might want to flush the shared pool
> first so that Starts indicates the number of times that portion of the
> SQL statement executed):
>
> Charles,
>
> There is an option for display_cursor which is 'ALLSTATS LAST'. this give
> you the stats from the last execution of the query, and makes the shared
> pool
> flush unnecessary.
>
> --
> Regards
>
> Jonathan Lewis http://jonathanlewis.wordpress.com
>
> Author: Cost Based Oracle: Fundamentalshttp://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>
> The Co-operative Oracle Users' FAQhttp://www.jlcomp.demon.co.uk/faq/ind_faq.html

Jonathan,

Thanks a lot for the tip (I was hoping that I was overlooking something). The impact of the plan is very hard to determine without specifying the "LAST" keyword, or without flushing the shared pool first. Based on a very quick test, the Starts, A-Rows, Buffers columns all increase in value with each execution, while the A-Time, and Reads columns do not increase when the "LAST" keyword is not specified. When the "LAST" keyword is specified, the impact of the plan is much more clear, but the READS column is no longer returned.

Just out of curiosity, do you think that DBMS_XPLAN is retrieving its source data by querying V$SQL_PLAN_STATISTICS_ALL (or the underlying views), and when the LAST keyword is specified, only returns those columns which are prefixed with LAST_ ? The reason that I ask is that I am currently investigating performance problems with a packaged application that is running against Oracle 10.2.0.2. Due to bind variable peeking, and the fact that the packaged application is either not supplying bind variable values during the initial parse call, or is specifying out of bounds values, Oracle is selecting to perform an index range scan using an index on a column that contains only two distinct values, when it should be using the index on the primary key column to retrieve table data. Oracle selects the correct index if bind variable peeking is disabled. I am trying to develop a logical approach to determine which SQL statements need to have an outline developed when bind variable peeking is disabled at the session level.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Fri May 04 2007 - 18:53:25 CDT