v$sql_plan_statistics

先看一下两个视图的信息：

V$SQL_PLAN contains the execution plan information for each child cursor loaded in the library cache.

V$SQL_PLAN_STATISTICS provides execution statistics at the row source level for each child cursor.

通过查询v$sql_plan和v$sql_plan_statistics,可以找到sql的执行计划以及实际执行时的统计信息，在默认情况下，oracle自动收集v$sql_planv的信息，而
$sql_plan_statistics的信息收集是由STATISTICS_LEVEL参数决定的，默认情况下，该参数被设置为typical，此时是不收集statistics相关信息的，如果需
要收集statistics相关信息，需要把该参数设置为all。

SQL>  select STATISTICS_NAME,DESCRIPTION,SESSION_STATUS,SYSTEM_STATUS,ACTIVATION_LEVEL,STATISTICS_VIEW_NAME from v$STATISTICS_LEVEL
  2  where ACTIVATION_LEVEL='ALL';

STATISTICS_NAME           DESCRIPTION                    SESSION_ SYSTEM_S ACTIVAT STATISTICS_VIEW
------------------------- ------------------------------ -------- -------- ------- ---------------
Timed OS Statistics       Enables gathering of timed ope ENABLED  ENABLED  ALL
                          rating system statistics

Plan Execution Statistics Enables collection of plan exe ENABLED  ENABLED  ALL     V$SQL_PLAN_STAT
                          cution statistics                                        ISTICS

通过如下sql可以查询给定hash_value的sql的执行计划以及统计信息。

SQL> set verify off echo off feed off
SQL> set linesize 300
SQL> set pagesize 3000
SQL> col hv head 'hv' noprint
SQL> col "cn" for 90 print
SQL> col "card" for 999,999,990
SQL> col "rows" for 999,999,990
SQL> col "ELAPSED" for 999,999.999
SQL> col "CPU" for 999,999.999
SQL> col "LOGICAL_READS" for 999,999,990
SQL> col "CR_GETS" for 999,999,990
SQL> col "CU_GETS" for 999,999,990
SQL> col "GETS" for 999,999,990
SQL> col "READS" for 999,999,990
SQL> col operation   format a30
SQL> col id format a9
SQL> col cost for 999,999,999
SQL>
SQL>
SQL> break on hv skip 0 on "cn" skip 0
SQL>
SQL> select p.hash_value hv  ,
  2      p.child_number "cn" ,
  3      to_char(p.id,'990')||decode(access_predicates,null,null,'A') || decode(filter_predicates,null,null,'F') id,
  4      p.cost "cost", p.cardinality "card",
  5      lpad(' ',depth)||p.operation||' '||p.options||' '||
  6     p.object_name||decode(p.partition_start,null,' ',':')||translate(p.partition_start,'(NUMBER','(NR')||
  7      decode(p.partition_stop,null,' ','-')||translate(p.partition_stop,'(NUMBE','(NR') "operation"
  8  ,p.position "pos",
  9  (select s.last_output_rows  from v$sql_plan_statistics s where
 10  s.address=p.address and s.hash_value=p.hash_value and s.child_number=p.child_number
 11  and s.operation_id=p.id) "ROWS",
 12  (select round(s.last_elapsed_time/1000000,2)  from v$sql_plan_statistics s where
 13  s.address=p.address and s.hash_value=p.hash_value and s.child_number=p.child_number
 14  and s.operation_id=p.id) "ELAPSED",
 15  (select s.last_cu_buffer_gets+s.last_cr_buffer_gets  from v$sql_plan_statistics s where
 16  s.address=p.address and s.hash_value=p.hash_value and s.child_number=p.child_number
 17  and s.operation_id=p.id) "LOGICAL_READS"
 18  from v$sql_plan p
 19  where p.hash_value=&HASH_VALUE
 20  order by p.child_number,p.id;
Enter value for hash_value: 1803702252

 cn ID                cost         card operation                             pos         ROWS      ELAPSED LOGICAL_READS
--- --------- ------------ ------------ ------------------------------ ---------- ------------ ------------ -------------
  0    0               135              SELECT STATEMENT                      135
       1AF             135        1,167  HASH JOIN                              1        9,997        1.360         1,138
       2                69            7   VIEW  VW_SQ_1                         1            7         .320           239
       3                69            7    HASH GROUP BY                        1            7         .320           239
       4                65       20,000     TABLE ACCESS FULL EMP               1       20,000         .180           239
       5                65       20,000   TABLE ACCESS FULL EMP                 2       20,000         .310           899

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10972173/viewspace-608630/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10972173/viewspace-608630/