Re: Slow SQL, too many logical reads ?

From: Mladen Gogala <mgogala.SPAM_ME.NOT_at_verizon.net>
Date: Sat, 05 May 2007 19:03:15 GMT
Message-ID: <pan.2007.05.05.19.03.15@verizon.net>

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:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production With the Partitioning, OLAP and Data Mining options

SQL> alter session set tracefile_identifier=CHOOPER;

Session altered.

SQL> explain plan for
2 select * from emp;

Explained.

SQL> alter session set events='10046 trace name context forever, level 12';

Session altered.

SQL> select * from
2 TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS')); PLAN_TABLE_OUTPUT

SQL_ID 9babjv8yq8ru3, child number 0

BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END; 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)

8 rows selected.

SQL> It is trying to fetch from V$SQL_PLAN. Let's see the details: $ ls -l 10g_ora_4593_CHOOPER.trc -rw-r----- 1 501 168820 May 5 14:56 10g_ora_4593_CHOOPER.trc $ tkprof 10g_ora_4593_CHOOPER.trc /tmp/chooper.lis sys=yes waits=no

TKPROF: Release 10.2.0.3.0 - Production on Sat May 5 14:58:37 2007

In that file is the answer too your question:

select text
from
view$ where rowid=:1

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

Received on Sat May 05 2007 - 14:03:15 CDT