Re: Slow SQL, too many logical reads ?

"Charles Hooper" <hooperc2000_at_yahoo.com> wrote in message news:1178322805.645891.279390_at_u30g2000hsc.googlegroups.com...
>
> 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.
>

Charles,

I see Mladen has already answered your question. For more details, you could check the script

    $ORACLE_HOME/rdbms/admin/dbmsxplan.sql

The disappearance of the READS column is simply based on the fact that the pl/sql attempts (as far as I can tell) to hide any column where the value is always zero.

Would event 10132 help with the problem of identifying isolating queries where inconsistency with the supply of bind variables was causing problems. I'd be interested to hear of any cases where you can see a consistent pattern with bind variables disappearing - I think I had some email recently from someone who was seeing the same problem.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html