Re: Slow SQL, too many logical reads ?

On May 5, 3:03 pm, Mladen Gogala <mgogala.SPAM_ME...._at_verizon.net> wrote:
> On Fri, 04 May 2007 16:53:25 -0700, Charles Hooper wrote:
> > 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_ ?
>
> I believe it does. Here is my proof:
> NOTE: cannot fetch plan for SQL_ID: 9babjv8yq8ru3, CHILD_NUMBER: 0
> Please verify value of SQL_ID and CHILD_NUMBER;
> It could also be that the plan is no longer in cursor cache (check v
> $sql_p
> lan)
>
> LAST_OUTPUT_ROWS, OUTPUT_ROWS, LAST_CR_BUFFER_GETS,
> CR_BUFFER_GETS, LAST_CU_BUFFER_GETS, CU_BUFFER_GETS,
> LAST_DISK_READS, DISK_READS, LAST_DISK_WRITES, DISK_WRITES,
> LAST_ELAPSED_TIME, ELAPSED_TIME, POLICY,
> ESTIMATED_OPTIMAL_SIZE, ESTIMATED_ONEPASS_SIZE,
> LAST_MEMORY_USED, LAST_EXECUTION, LAST_DEGREE,
> TOTAL_EXECUTIONS, OPTIMAL_EXECUTIONS, ONEPASS_EXECUTIONS,
> MULTIPASSES_EXECUTIONS, ACTIVE_TIME, MAX_TEMPSEG_SIZE,
> LAST_TEMPSEG_SIZE
>
> from
> GV$SQL_PLAN_STATISTICS_ALL
> where inst_id = USERENV('Instance')
>
> The answer to your question is a resounding "yes".
>
> --http://www.mladen-gogala.com

Mladen, thanks for setting up the test case to show from where the source data for DBMS_XPLAN is being retrieved.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Sat May 05 2007 - 20:52:43 CDT