执行计划杂记

在oracle 11g中，dbms_xplan包已经增加了不少内容，不再像9i一般只局限于display。在这里小记一把和执行计划相关的内容。

(1) gather_plan_statistics 和dbms_xplan.display_cursor连用，如：

sys@ORA10G(9.115.110.142)> set SERVEROUT off
sys@ORA10G(9.115.110.142)>
sys@ORA10G(9.115.110.142)>
sys@ORA10G(9.115.110.142)>
sys@ORA10G(9.115.110.142)> select  /*+ gather_plan_statistics */ count(*) from dual;

  COUNT(*)
----------
         1

Elapsed: 00:00:00.00
sys@ORA10G(9.115.110.142)> select* from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  0cb33av5khrss, child number 0
-------------------------------------
select  /*+ gather_plan_statistics */ count(*) from dual

Plan hash value: 3910148636

-----------------------------------------------------------------------
| Id  | Operation      | Name | Starts | E-Rows | A-Rows |   A-Time   |
-----------------------------------------------------------------------
|   1 |  SORT AGGREGATE|      |      1 |      1 |      1 |00:00:00.01 |
|   2 |   FAST DUAL    |      |      1 |      1 |      1 |00:00:00.01 |
-----------------------------------------------------------------------


13 rows selected.

Elapsed: 00:00:00.96
sys@ORA10G(9.115.110.142)>

sys@ORA10G(9.115.110.142)> set SERVEROUT off

sys@ORA10G(9.115.110.142)>

sys@ORA10G(9.115.110.142)> select /*+ gather_plan_statistics */ count(*) from dual;

COUNT(*)

----------

Elapsed: 00:00:00.00

sys@ORA10G(9.115.110.142)> select* from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------

SQL_ID 0cb33av5khrss, child number 0

-------------------------------------

select /*+ gather_plan_statistics */ count(*) from dual

Plan hash value: 3910148636

-----------------------------------------------------------------------

-----------------------------------------------------------------------

| 1 | SORT AGGREGATE| | 1 | 1 | 1 |00:00:00.01 |

| 2 | FAST DUAL | | 1 | 1 | 1 |00:00:00.01 |

-----------------------------------------------------------------------

13 rows selected.

Elapsed: 00:00:00.96

sys@ORA10G(9.115.110.142)>

在此处看 E-Rows和A-Rows之间的差别是否巨大，如差距大则表示estimate rows和actual rows之间差距巨大，统计信息可能有问题。

(2) 看历史的执行计划：
历史的执行计划可以在dba_hist_sql_plan中找到，有不同的PLAN_HASH_VALUE，并且我们可以结合awr来一起看。

SQL> l
  1  select DBID,SQL_ID,PLAN_HASH_VALUE,to_char(TIMESTAMP,'yyyy-mm-dd hh24:mi:ss') TIMESTAMP from dba_hist_sql_plan
  2  where SQL_ID='frgzxkprkwntn'
  3  group by DBID,SQL_ID,PLAN_HASH_VALUE,to_char(TIMESTAMP,'yyyy-mm-dd hh24:mi:ss')
  4* order by TIMESTAMP
SQL> /

      DBID SQL_ID               PLAN_HASH_VALUE TIMESTAMP
---------- -------------------- --------------- -------------------
2694280230 frgzxkprkwntn              996644735 2012-06-15 01:06:53
2694280230 frgzxkprkwntn              883847748 2012-07-02 12:28:15
2694280230 frgzxkprkwntn             1190191859 2012-07-03 08:50:37
2694280230 frgzxkprkwntn             3418420592 2012-07-09 07:48:51
2694280230 frgzxkprkwntn             3487426888 2012-07-16 12:07:12

SQL> 
我们看到一个sql id为'frgzxkprkwntn'有5个历史版本的执行计划。我们可以利用dbms_xplan.display_awr(sql_id,plan_hash_value,dbid,FORMAT)来看，如果FORMAT为'peeked_binds'甚至可以看当时绑定的变量：

SQL> select * from table(dbms_xplan.DISPLAY_AWR('frgzxkprkwntn','996644735','2694280230','peeked_binds'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID frgzxkprkwntn
--------------------
SELECT COUNT(TBTC.REQUEST_OBJECT_ID) FROM TBTC_REQUEST TBTC,
TBLK_REQUEST_TX TBLK WHERE TBTC.REQUEST_TYPE_CODE = :B4 AND
TBTC.DIRECTION = :B3 AND TBTC.REQUEST_STATUS_CODE = :B2 AND
TBLK.REQUEST_OBJECT_ID = TBTC.REQUEST_OBJECT_ID AND
TBLK.REQUEST_TX_TYPE_CODE = :B1 AND EXISTS ( SELECT NULL FROM
MYTB_TX_HELPM_ENTRY MYTB, TB09_TX_ACTIVITY TB09 WHERE
(TBLK.REQUEST_TX_OBJECT_ID = MYTB.REQUEST_TX_OBJECT_ID AND
MYTB.REQUEST_TX_OBJECT_ID = TB09.REQUEST_TX_OBJECT_ID) AND
MYTB.TX_HELPM_ENTRY_TYPE_CODE = :B9 AND MYTB.HELPM_TS > :B8 AND
MYTB.HELPM_TS < :B7 AND MYTB.REQUEST_TX_OBJECT_ID =
TBLK.REQUEST_TX_OBJECT_ID AND TB09.ACTIVITY_ID = :B6 AND
TB09.TX_ACTIVITY_STATUS_CODE = :B5 AND NOT EXISTS ( SELECT NULL FROM
MYTB_TX_HELPM_ENTRY B WHERE B.REQUEST_TX_OBJECT_ID =
MYTB.REQUEST_TX_OBJECT_ID AND B.HELPM_CARRIER_ID =
MYTB.HELPM_CARRIER_ID AND (B.TX_HELPM_ENTRY_TYPE_CODE = :B11 OR
B.TX_HELPM_ENTRY_TYPE_CODE = :B10 ) ) )

Plan hash value: 996644735

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                             |       |       |   419 (100)|          |
|   1 |  SORT AGGREGATE                   |                             |     1 |    44 |            |          |
|   2 |   FILTER                          |                             |       |       |            |          |
|   3 |    NESTED LOOPS                   |                             |       |       |            |          |
|   4 |     NESTED LOOPS                  |                             |    59 |  2596 |   124   (0)| 00:00:02 |
|   5 |      TABLE ACCESS BY INDEX ROWID  | TBTC_REQUEST                |    50 |  1250 |    23   (0)| 00:00:01 |
|   6 |       INDEX RANGE SCAN            | TBTC_I_STATUS_REQ_OBJ_ID    |   317 |       |     5   (0)| 00:00:01 |
|   7 |      INDEX RANGE SCAN             | TBLK_I_REQ_OBJ_ID           |     2 |       |     2   (0)| 00:00:01 |
|   8 |     TABLE ACCESS BY INDEX ROWID   | TBLK_REQUEST_TX             |     1 |    19 |     3   (0)| 00:00:01 |
|   9 |    FILTER                         |                             |       |       |            |          |
|  10 |     NESTED LOOPS                  |                             |       |       |            |          |
|  11 |      NESTED LOOPS                 |                             |     1 |    68 |    10   (0)| 00:00:01 |
|  12 |       NESTED LOOPS ANTI           |                             |     1 |    48 |     7   (0)| 00:00:01 |
|  13 |        TABLE ACCESS BY INDEX ROWID| MYTB_TX_HELPM_ENTRY         |     1 |    28 |     4   (0)| 00:00:01 |
|  14 |         INDEX RANGE SCAN          | MYTB_C_PK                   |     1 |       |     3   (0)| 00:00:01 |
|  15 |        INLIST ITERATOR            |                             |       |       |            |          |
|  16 |         INDEX UNIQUE SCAN         | MYTB_C_PK                   |     1 |    20 |     3   (0)| 00:00:01 |
|  17 |       INDEX RANGE SCAN            | TB09_I_REQUEST_TX_OBJECT_ID |     1 |       |     2   (0)| 00:00:01 |
|  18 |      TABLE ACCESS BY INDEX ROWID  | TB09_TX_ACTIVITY            |     1 |    20 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   1 - :B4 (VARCHAR2(30), CSID=1): 'PORT'
   2 - :B3 (VARCHAR2(30), CSID=1): 'O'
   3 - :B2 (VARCHAR2(30), CSID=1): 'CTVR CNFM'
   4 - :B1 (VARCHAR2(30), CSID=1): 'BPCN'
   5 - :B9 (VARCHAR2(30), CSID=1): 'RECEIVED'
   6 - :B8 (DATE): 06/04/2012 00:00:00
   7 - :B7 (DATE): 06/14/2012 23:59:59
   8 - :B6 (NUMBER): 9
   9 - :B5 (VARCHAR2(30), CSID=1): 'OPEN'
  10 - :B11 (VARCHAR2(30), CSID=1): 'CONFIRMED'
  11 - :B10 (VARCHAR2(30), CSID=1): 'REJECTED'


60 rows selected.

SQL> 
SQL> select * from table(dbms_xplan.DISPLAY_AWR('frgzxkprkwntn','3487426888','2694280230','peeked_binds'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID frgzxkprkwntn
--------------------
SELECT COUNT(TBTC.REQUEST_OBJECT_ID) FROM TBTC_REQUEST TBTC,
TBLK_REQUEST_TX TBLK WHERE TBTC.REQUEST_TYPE_CODE = :B4 AND
TBTC.DIRECTION = :B3 AND TBTC.REQUEST_STATUS_CODE = :B2 AND
TBLK.REQUEST_OBJECT_ID = TBTC.REQUEST_OBJECT_ID AND
TBLK.REQUEST_TX_TYPE_CODE = :B1 AND EXISTS ( SELECT NULL FROM
MYTB_TX_HELPM_ENTRY MYTB, TB09_TX_ACTIVITY TB09 WHERE
(TBLK.REQUEST_TX_OBJECT_ID = MYTB.REQUEST_TX_OBJECT_ID AND
MYTB.REQUEST_TX_OBJECT_ID = TB09.REQUEST_TX_OBJECT_ID) AND
MYTB.TX_HELPM_ENTRY_TYPE_CODE = :B9 AND MYTB.HELPM_TS > :B8 AND
MYTB.HELPM_TS < :B7 AND MYTB.REQUEST_TX_OBJECT_ID =
TBLK.REQUEST_TX_OBJECT_ID AND TB09.ACTIVITY_ID = :B6 AND
TB09.TX_ACTIVITY_STATUS_CODE = :B5 AND NOT EXISTS ( SELECT NULL FROM
MYTB_TX_HELPM_ENTRY B WHERE B.REQUEST_TX_OBJECT_ID =
MYTB.REQUEST_TX_OBJECT_ID AND B.HELPM_CARRIER_ID =
MYTB.HELPM_CARRIER_ID AND (B.TX_HELPM_ENTRY_TYPE_CODE = :B11 OR
B.TX_HELPM_ENTRY_TYPE_CODE = :B10 ) ) )

Plan hash value: 3487426888

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                             |       |       |     8 (100)|          |
|   1 |  SORT AGGREGATE                       |                             |     1 |    57 |            |          |
|   2 |   NESTED LOOPS                        |                             |       |       |            |          |
|   3 |    NESTED LOOPS                       |                             |     1 |    57 |     8  (13)| 00:00:01 |
|   4 |     NESTED LOOPS                      |                             |     1 |    32 |     7  (15)| 00:00:01 |
|   5 |      VIEW                             | VW_SQ_1                     |     1 |    13 |     5   (0)| 00:00:01 |
|   6 |       HASH UNIQUE                     |                             |     1 |    68 |            |          |
|   7 |        FILTER                         |                             |       |       |            |          |
|   8 |         NESTED LOOPS                  |                             |       |       |            |          |
|   9 |          NESTED LOOPS                 |                             |     1 |    68 |     5   (0)| 00:00:01 |
|  10 |           NESTED LOOPS ANTI           |                             |     1 |    48 |     4   (0)| 00:00:01 |
|  11 |            TABLE ACCESS BY INDEX ROWID| MYTB_TX_HELPM_ENTRY         |     1 |    28 |     4   (0)| 00:00:01 |
|  12 |             INDEX RANGE SCAN          | MYTB_I_HELPM_TS_TXOBJ       |     1 |       |     3   (0)| 00:00:01 |
|  13 |            INLIST ITERATOR            |                             |       |       |            |          |
|  14 |             INDEX UNIQUE SCAN         | MYTB_C_PK                   |  1102K|    21M|     0   (0)|          |
|  15 |           INDEX RANGE SCAN            | TB09_I_REQUEST_TX_OBJECT_ID |     3 |       |     0   (0)|          |
|  16 |          TABLE ACCESS BY INDEX ROWID  | TB09_TX_ACTIVITY            |     1 |    20 |     1   (0)| 00:00:01 |
|  17 |      TABLE ACCESS BY INDEX ROWID      | TBLK_REQUEST_TX             |     1 |    19 |     1   (0)| 00:00:01 |
|  18 |       INDEX UNIQUE SCAN               | TBLK_C_PK                   |     1 |       |     0   (0)|          |
|  19 |     INDEX RANGE SCAN                  | TBTC_I_STATUS_REQ_OBJ_ID    |     1 |       |     0   (0)|          |
|  20 |    TABLE ACCESS BY INDEX ROWID        | TBTC_REQUEST                |     1 |    25 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   1 - :B4 (VARCHAR2(30), CSID=1): 'PORT'
   2 - :B3 (VARCHAR2(30), CSID=1): 'O'
   3 - :B2 (VARCHAR2(30), CSID=1): 'CTVR CNFM'
   4 - :B1 (VARCHAR2(30), CSID=1): 'BPCN'
   5 - :B9 (VARCHAR2(30), CSID=1): 'RECEIVED'
   6 - :B8 (DATE): 07/16/2012 00:00:00
   7 - :B7 (DATE): 07/16/2012 11:52:12
   8 - :B6 (NUMBER): 9
   9 - :B5 (VARCHAR2(30), CSID=1): 'OPEN'
  10 - :B11 (VARCHAR2(30), CSID=1): 'CONFIRMED'
  11 - :B10 (VARCHAR2(30), CSID=1): 'REJECTED'


62 rows selected.

SQL>

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

SQL> l

1 select DBID,SQL_ID,PLAN_HASH_VALUE,to_char(TIMESTAMP,'yyyy-mm-dd hh24:mi:ss') TIMESTAMP from dba_hist_sql_plan

2 where SQL_ID='frgzxkprkwntn'

3 group by DBID,SQL_ID,PLAN_HASH_VALUE,to_char(TIMESTAMP,'yyyy-mm-dd hh24:mi:ss')

4* order by TIMESTAMP

SQL> /

DBID SQL_ID PLAN_HASH_VALUE TIMESTAMP

---------- -------------------- --------------- -------------------

2694280230 frgzxkprkwntn 996644735 2012-06-15 01:06:53

2694280230 frgzxkprkwntn 883847748 2012-07-02 12:28:15

2694280230 frgzxkprkwntn 1190191859 2012-07-03 08:50:37

2694280230 frgzxkprkwntn 3418420592 2012-07-09 07:48:51

2694280230 frgzxkprkwntn 3487426888 2012-07-16 12:07:12

SQL>

我们看到一个sql id为'frgzxkprkwntn'有5个历史版本的执行计划。我们可以利用dbms_xplan.display_awr(sql_id,plan_hash_value,dbid,FORMAT)来看，如果FORMAT为'peeked_binds'甚至可以看当时绑定的变量：

SQL> select * from table(dbms_xplan.DISPLAY_AWR('frgzxkprkwntn','996644735','2694280230','peeked_binds'));

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL_ID frgzxkprkwntn

--------------------

SELECT COUNT(TBTC.REQUEST_OBJECT_ID) FROM TBTC_REQUEST TBTC,

TBLK_REQUEST_TX TBLK WHERE TBTC.REQUEST_TYPE_CODE = :B4 AND

TBTC.DIRECTION = :B3 AND TBTC.REQUEST_STATUS_CODE = :B2 AND

TBLK.REQUEST_OBJECT_ID = TBTC.REQUEST_OBJECT_ID AND

TBLK.REQUEST_TX_TYPE_CODE = :B1 AND EXISTS ( SELECT NULL FROM

MYTB_TX_HELPM_ENTRY MYTB, TB09_TX_ACTIVITY TB09 WHERE

(TBLK.REQUEST_TX_OBJECT_ID = MYTB.REQUEST_TX_OBJECT_ID AND

MYTB.REQUEST_TX_OBJECT_ID = TB09.REQUEST_TX_OBJECT_ID) AND

MYTB.TX_HELPM_ENTRY_TYPE_CODE = :B9 AND MYTB.HELPM_TS > :B8 AND

MYTB.HELPM_TS < :B7 AND MYTB.REQUEST_TX_OBJECT_ID =

TBLK.REQUEST_TX_OBJECT_ID AND TB09.ACTIVITY_ID = :B6 AND

TB09.TX_ACTIVITY_STATUS_CODE = :B5 AND NOT EXISTS ( SELECT NULL FROM

MYTB_TX_HELPM_ENTRY B WHERE B.REQUEST_TX_OBJECT_ID =

MYTB.REQUEST_TX_OBJECT_ID AND B.HELPM_CARRIER_ID =

MYTB.HELPM_CARRIER_ID AND (B.TX_HELPM_ENTRY_TYPE_CODE = :B11 OR

B.TX_HELPM_ENTRY_TYPE_CODE = :B10 ) ) )

Plan hash value: 996644735

-----------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 419 (100)| |

| 1 | SORT AGGREGATE | | 1 | 44 | | |

| 2 | FILTER | | | | | |

| 3 | NESTED LOOPS | | | | | |

| 4 | NESTED LOOPS | | 59 | 2596 | 124 (0)| 00:00:02 |

| 5 | TABLE ACCESS BY INDEX ROWID | TBTC_REQUEST | 50 | 1250 | 23 (0)| 00:00:01 |

| 6 | INDEX RANGE SCAN | TBTC_I_STATUS_REQ_OBJ_ID | 317 | | 5 (0)| 00:00:01 |

| 7 | INDEX RANGE SCAN | TBLK_I_REQ_OBJ_ID | 2 | | 2 (0)| 00:00:01 |

| 8 | TABLE ACCESS BY INDEX ROWID | TBLK_REQUEST_TX | 1 | 19 | 3 (0)| 00:00:01 |

| 9 | FILTER | | | | | |

| 10 | NESTED LOOPS | | | | | |

| 11 | NESTED LOOPS | | 1 | 68 | 10 (0)| 00:00:01 |

| 12 | NESTED LOOPS ANTI | | 1 | 48 | 7 (0)| 00:00:01 |

| 13 | TABLE ACCESS BY INDEX ROWID| MYTB_TX_HELPM_ENTRY | 1 | 28 | 4 (0)| 00:00:01 |

| 14 | INDEX RANGE SCAN | MYTB_C_PK | 1 | | 3 (0)| 00:00:01 |

| 15 | INLIST ITERATOR | | | | | |

| 16 | INDEX UNIQUE SCAN | MYTB_C_PK | 1 | 20 | 3 (0)| 00:00:01 |

| 17 | INDEX RANGE SCAN | TB09_I_REQUEST_TX_OBJECT_ID | 1 | | 2 (0)| 00:00:01 |

| 18 | TABLE ACCESS BY INDEX ROWID | TB09_TX_ACTIVITY | 1 | 20 | 3 (0)| 00:00:01 |

-----------------------------------------------------------------------------------------------------------------

Peeked Binds (identified by position):

--------------------------------------

1 - :B4 (VARCHAR2(30), CSID=1): 'PORT'

2 - :B3 (VARCHAR2(30), CSID=1): 'O'

3 - :B2 (VARCHAR2(30), CSID=1): 'CTVR CNFM'

4 - :B1 (VARCHAR2(30), CSID=1): 'BPCN'

5 - :B9 (VARCHAR2(30), CSID=1): 'RECEIVED'

6 - :B8 (DATE): 06/04/2012 00:00:00

7 - :B7 (DATE): 06/14/2012 23:59:59

8 - :B6 (NUMBER): 9

9 - :B5 (VARCHAR2(30), CSID=1): 'OPEN'

10 - :B11 (VARCHAR2(30), CSID=1): 'CONFIRMED'

11 - :B10 (VARCHAR2(30), CSID=1): 'REJECTED'

60 rows selected.

SQL>

SQL> select * from table(dbms_xplan.DISPLAY_AWR('frgzxkprkwntn','3487426888','2694280230','peeked_binds'));

PLAN_TABLE_OUTPUT

SQL_ID frgzxkprkwntn

--------------------

SELECT COUNT(TBTC.REQUEST_OBJECT_ID) FROM TBTC_REQUEST TBTC,

TBLK_REQUEST_TX TBLK WHERE TBTC.REQUEST_TYPE_CODE = :B4 AND

TBTC.DIRECTION = :B3 AND TBTC.REQUEST_STATUS_CODE = :B2 AND

TBLK.REQUEST_OBJECT_ID = TBTC.REQUEST_OBJECT_ID AND

TBLK.REQUEST_TX_TYPE_CODE = :B1 AND EXISTS ( SELECT NULL FROM

MYTB_TX_HELPM_ENTRY MYTB, TB09_TX_ACTIVITY TB09 WHERE

(TBLK.REQUEST_TX_OBJECT_ID = MYTB.REQUEST_TX_OBJECT_ID AND

MYTB.REQUEST_TX_OBJECT_ID = TB09.REQUEST_TX_OBJECT_ID) AND

MYTB.TX_HELPM_ENTRY_TYPE_CODE = :B9 AND MYTB.HELPM_TS > :B8 AND

MYTB.HELPM_TS < :B7 AND MYTB.REQUEST_TX_OBJECT_ID =

TBLK.REQUEST_TX_OBJECT_ID AND TB09.ACTIVITY_ID = :B6 AND

TB09.TX_ACTIVITY_STATUS_CODE = :B5 AND NOT EXISTS ( SELECT NULL FROM

MYTB_TX_HELPM_ENTRY B WHERE B.REQUEST_TX_OBJECT_ID =

MYTB.REQUEST_TX_OBJECT_ID AND B.HELPM_CARRIER_ID =

MYTB.HELPM_CARRIER_ID AND (B.TX_HELPM_ENTRY_TYPE_CODE = :B11 OR

B.TX_HELPM_ENTRY_TYPE_CODE = :B10 ) ) )

Plan hash value: 3487426888

---------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 8 (100)| |

| 1 | SORT AGGREGATE | | 1 | 57 | | |

| 2 | NESTED LOOPS | | | | | |

| 3 | NESTED LOOPS | | 1 | 57 | 8 (13)| 00:00:01 |

| 4 | NESTED LOOPS | | 1 | 32 | 7 (15)| 00:00:01 |

| 5 | VIEW | VW_SQ_1 | 1 | 13 | 5 (0)| 00:00:01 |

| 6 | HASH UNIQUE | | 1 | 68 | | |

| 7 | FILTER | | | | | |

| 8 | NESTED LOOPS | | | | | |

| 9 | NESTED LOOPS | | 1 | 68 | 5 (0)| 00:00:01 |

| 10 | NESTED LOOPS ANTI | | 1 | 48 | 4 (0)| 00:00:01 |

| 11 | TABLE ACCESS BY INDEX ROWID| MYTB_TX_HELPM_ENTRY | 1 | 28 | 4 (0)| 00:00:01 |

| 12 | INDEX RANGE SCAN | MYTB_I_HELPM_TS_TXOBJ | 1 | | 3 (0)| 00:00:01 |

| 13 | INLIST ITERATOR | | | | | |

| 14 | INDEX UNIQUE SCAN | MYTB_C_PK | 1102K| 21M| 0 (0)| |

| 15 | INDEX RANGE SCAN | TB09_I_REQUEST_TX_OBJECT_ID | 3 | | 0 (0)| |

| 16 | TABLE ACCESS BY INDEX ROWID | TB09_TX_ACTIVITY | 1 | 20 | 1 (0)| 00:00:01 |

| 17 | TABLE ACCESS BY INDEX ROWID | TBLK_REQUEST_TX | 1 | 19 | 1 (0)| 00:00:01 |

| 18 | INDEX UNIQUE SCAN | TBLK_C_PK | 1 | | 0 (0)| |

| 19 | INDEX RANGE SCAN | TBTC_I_STATUS_REQ_OBJ_ID | 1 | | 0 (0)| |

| 20 | TABLE ACCESS BY INDEX ROWID | TBTC_REQUEST | 1 | 25 | 1 (0)| 00:00:01 |

---------------------------------------------------------------------------------------------------------------------

Peeked Binds (identified by position):

--------------------------------------

1 - :B4 (VARCHAR2(30), CSID=1): 'PORT'

2 - :B3 (VARCHAR2(30), CSID=1): 'O'

3 - :B2 (VARCHAR2(30), CSID=1): 'CTVR CNFM'

4 - :B1 (VARCHAR2(30), CSID=1): 'BPCN'

5 - :B9 (VARCHAR2(30), CSID=1): 'RECEIVED'

6 - :B8 (DATE): 07/16/2012 00:00:00

7 - :B7 (DATE): 07/16/2012 11:52:12

8 - :B6 (NUMBER): 9

9 - :B5 (VARCHAR2(30), CSID=1): 'OPEN'

10 - :B11 (VARCHAR2(30), CSID=1): 'CONFIRMED'

11 - :B10 (VARCHAR2(30), CSID=1): 'REJECTED'

62 rows selected.

SQL>

(3) 利用WRI$_OPTSTAT_TAB_HISTORY看table的历史统计信息：

SQL>  l
  1  SELECT ob.owner, ob.object_name, ob.subobject_name, ob.object_type,obj#, savtime, flags, rowcnt, blkcnt, avgrln ,samplesize, analyzetime, cachedblk, cachehit, logicalread
  2  FROM sys.WRI$_OPTSTAT_TAB_HISTORY, dba_objects ob
  3  WHERE owner=upper('&OWNER')
  4  and object_name=upper('&TABLE_NAME')
  5  and object_type in ('TABLE')
  6* and object_id=obj#
SQL> 
 OWNER                          OBJECT_NAME        SUBOBJECT_NAME                 OBJECT_TYPE               OBJ# SAVTIME                                                                          FLAGS     ROWCNT     BLKCNT     AVGRLN SAMPLESIZE ANALYZETI  CACHEDBLK   CACHEHIT LOGICALREAD
------------------------------ ------------------ ------------------------------ ------------------- ---------- --------------------------------------------------------------------------- ---------- ---------- ---------- ---------- ---------- --------- ---------- ---------- -----------
KRADSNP                        L901_REQQESM                                      TABLE                    14444 16-JUN-12 01.00.29.995699 AM +10:00                                                 10    1424933      29130        125      18028 15-JUN-12
KRADSNP                        L901_REQQESM                                      TABLE                    14444 17-JUN-12 01.00.31.984135 AM +10:00                                                 10    1428883      29130        125      16538 16-JUN-12
KRADSNP                        L901_REQQESM                                      TABLE                    14444 18-JUN-12 01.00.32.334285 AM +10:00                                                 10    1319051      29130        125      17247 17-JUN-12
KRADSNP                        L901_REQQESM                                      TABLE                    14444 19-JUN-12 01.00.29.984877 AM +10:00                                                 10    1322830      29130        125      17082 18-JUN-12
KRADSNP                        L901_REQQESM                                      TABLE                    14444 20-JUN-12 01.00.30.797670 AM +10:00                                                 10    1346647      29130        126      16568 19-JUN-12
KRADSNP                        L901_REQQESM                                      TABLE                    14444 21-JUN-12 01.00.29.440412 AM +10:00                                                 10    1366048      29130        125      15637 20-JUN-12
KRADSNP                        L901_REQQESM                                      TABLE                    14444 22-JUN-12 01.00.28.382793 AM +10:00                                                 10    1349705      29130        124      18259 21-JUN-12
KRADSNP                        L901_REQQESM                                      TABLE                    14444 23-JUN-12 01.00.28.825838 AM +10:00                                                 10    1368109      29130        126      17594 22-JUN-12
KRADSNP                        L901_REQQESM                                      TABLE                    14444 24-JUN-12 01.00.33.820628 AM +10:00                                                 10    1357332      29130        125      16569 23-JUN-12
KRADSNP                        L901_REQQESM                                      TABLE                    14444 25-JUN-12 01.00.26.874720 AM +10:00                                                 10    1301871      29130        124      15469 24-JUN-12
KRADSNP                        L901_REQQESM                                      TABLE                    14444 26-JUN-12 01.00.29.456753 AM +10:00                                                 10    1290826      29130        125      15635 25-JUN-12
KRADSNP                        L901_REQQESM                                      TABLE                    14444 27-JUN-12 01.00.31.494028 AM +10:00                                                 10    1314902      29130        125      17265 26-JUN-12
KRADSNP                        L901_REQQESM                                      TABLE                    14444 28-JUN-12 01.00.28.557591 AM +10:00                                                 10    1317162      29130        125      15954 27-JUN-12
KRADSNP                        L901_REQQESM                                      TABLE                    14444 29-JUN-12 01.00.29.486669 AM +10:00                                                 10    1339159      29130        125      16541 28-JUN-12
KRADSNP                        L901_REQQESM                                      TABLE                    14444 30-JUN-12 01.00.31.189966 AM +10:00                                                 10    1325110      29130        125      17849 29-JUN-12
KRADSNP                        L901_REQQESM                                      TABLE                    14444 01-JUL-12 01.00.33.626269 AM +10:00                                                 10    1360004      29130        126      15343 30-JUN-12
KRADSNP                        L901_REQQESM                                      TABLE                    14444 02-JUL-12 01.00.30.358059 AM +10:00                                                 10    1300221      29130        124      15688 01-JUL-12
KRADSNP                        L901_REQQESM                                      TABLE                    14444 03-JUL-12 01.00.30.497122 AM +10:00                                                 10    1300176      29130        125      18058 02-JUL-12
KRADSNP                        L901_REQQESM                                      TABLE                    14444 04-JUL-12 01.00.32.501247 AM +10:00                                                 10    1299742      29130        125      14878 03-JUL-12
KRADSNP                        L901_REQQESM                                      TABLE                    14444 05-JUL-12 01.00.29.354968 AM +10:00                                                 10    1316943      29130        125      17739 04-JUL-12
KRADSNP                        L901_REQQESM                                      TABLE                    14444 06-JUL-12 01.00.31.051355 AM +10:00                                                 10    1321312      29130        126      15700 05-JUL-12
KRADSNP                        L901_REQQESM                                      TABLE                    14444 07-JUL-12 01.00.32.227695 AM +10:00                                                 10    1310964      29130        125      16257 06-JUL-12
KRADSNP                        L901_REQQESM                                      TABLE                    14444 08-JUL-12 01.00.34.881812 AM +10:00                                                 10    1330625      29130        125      15871 07-JUL-12
KRADSNP                        L901_REQQESM                                      TABLE                    14444 09-JUL-12 01.00.31.878459 AM +10:00                                                 10    1292054      29130        125      17255 08-JUL-12
KRADSNP                        L901_REQQESM                                      TABLE                    14444 10-JUL-12 01.00.32.263729 AM +10:00                                                 10    1298390      29130        124      16427 09-JUL-12
KRADSNP                        L901_REQQESM                                      TABLE                    14444 11-JUL-12 01.00.35.061859 AM +10:00                                                 10    1277699      29130        125      15476 10-JUL-12
KRADSNP                        L901_REQQESM                                      TABLE                    14444 12-JUL-12 01.00.33.607805 AM +10:00                                                 10    1292381      29130        126      15838 11-JUL-12
KRADSNP                        L901_REQQESM                                      TABLE                    14444 13-JUL-12 01.00.31.355977 AM +10:00                                                 10    1313700      29130        125      17322 12-JUL-12
KRADSNP                        L901_REQQESM                                      TABLE                    14444 14-JUL-12 01.00.34.465944 AM +10:00                                                 10    1325156      29130        125      19351 13-JUL-12
KRADSNP                        L901_REQQESM                                      TABLE                    14444 15-JUL-12 01.00.35.710237 AM +10:00                                                 10    1328732      29130        125      18537 14-JUL-12
KRADSNP                        L901_REQQESM                                      TABLE                    14444 16-JUL-12 01.00.29.063225 AM +10:00                                                 10    1267732      29130        126      16576 15-JUL-12
KRADSNP                        L901_REQQESM                                      TABLE                    14444 17-JUL-12 01.00.31.753118 AM +10:00                                                 10    1270792      29130        127      16013 16-JUL-12

32 rows selected.

SQL>

SQL> l

1 SELECT ob.owner, ob.object_name, ob.subobject_name, ob.object_type,obj#, savtime, flags, rowcnt, blkcnt, avgrln ,samplesize, analyzetime, cachedblk, cachehit, logicalread

2 FROM sys.WRI$_OPTSTAT_TAB_HISTORY, dba_objects ob

3 WHERE owner=upper('&OWNER')

4 and object_name=upper('&TABLE_NAME')

5 and object_type in ('TABLE')

6* and object_id=obj#

SQL>

OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_TYPE OBJ# SAVTIME FLAGS ROWCNT BLKCNT AVGRLN SAMPLESIZE ANALYZETI CACHEDBLK CACHEHIT LOGICALREAD

------------------------------ ------------------ ------------------------------ ------------------- ---------- --------------------------------------------------------------------------- ---------- ---------- ---------- ---------- ---------- --------- ---------- ---------- -----------

KRADSNP L901_REQQESM TABLE 14444 16-JUN-12 01.00.29.995699 AM +10:00 10 1424933 29130 125 18028 15-JUN-12

KRADSNP L901_REQQESM TABLE 14444 17-JUN-12 01.00.31.984135 AM +10:00 10 1428883 29130 125 16538 16-JUN-12

KRADSNP L901_REQQESM TABLE 14444 18-JUN-12 01.00.32.334285 AM +10:00 10 1319051 29130 125 17247 17-JUN-12

KRADSNP L901_REQQESM TABLE 14444 19-JUN-12 01.00.29.984877 AM +10:00 10 1322830 29130 125 17082 18-JUN-12

KRADSNP L901_REQQESM TABLE 14444 20-JUN-12 01.00.30.797670 AM +10:00 10 1346647 29130 126 16568 19-JUN-12

KRADSNP L901_REQQESM TABLE 14444 21-JUN-12 01.00.29.440412 AM +10:00 10 1366048 29130 125 15637 20-JUN-12

KRADSNP L901_REQQESM TABLE 14444 22-JUN-12 01.00.28.382793 AM +10:00 10 1349705 29130 124 18259 21-JUN-12

KRADSNP L901_REQQESM TABLE 14444 23-JUN-12 01.00.28.825838 AM +10:00 10 1368109 29130 126 17594 22-JUN-12

KRADSNP L901_REQQESM TABLE 14444 24-JUN-12 01.00.33.820628 AM +10:00 10 1357332 29130 125 16569 23-JUN-12

KRADSNP L901_REQQESM TABLE 14444 25-JUN-12 01.00.26.874720 AM +10:00 10 1301871 29130 124 15469 24-JUN-12

KRADSNP L901_REQQESM TABLE 14444 26-JUN-12 01.00.29.456753 AM +10:00 10 1290826 29130 125 15635 25-JUN-12

KRADSNP L901_REQQESM TABLE 14444 27-JUN-12 01.00.31.494028 AM +10:00 10 1314902 29130 125 17265 26-JUN-12

KRADSNP L901_REQQESM TABLE 14444 28-JUN-12 01.00.28.557591 AM +10:00 10 1317162 29130 125 15954 27-JUN-12

KRADSNP L901_REQQESM TABLE 14444 29-JUN-12 01.00.29.486669 AM +10:00 10 1339159 29130 125 16541 28-JUN-12

KRADSNP L901_REQQESM TABLE 14444 30-JUN-12 01.00.31.189966 AM +10:00 10 1325110 29130 125 17849 29-JUN-12

KRADSNP L901_REQQESM TABLE 14444 01-JUL-12 01.00.33.626269 AM +10:00 10 1360004 29130 126 15343 30-JUN-12

KRADSNP L901_REQQESM TABLE 14444 02-JUL-12 01.00.30.358059 AM +10:00 10 1300221 29130 124 15688 01-JUL-12

KRADSNP L901_REQQESM TABLE 14444 03-JUL-12 01.00.30.497122 AM +10:00 10 1300176 29130 125 18058 02-JUL-12

KRADSNP L901_REQQESM TABLE 14444 04-JUL-12 01.00.32.501247 AM +10:00 10 1299742 29130 125 14878 03-JUL-12

KRADSNP L901_REQQESM TABLE 14444 05-JUL-12 01.00.29.354968 AM +10:00 10 1316943 29130 125 17739 04-JUL-12

KRADSNP L901_REQQESM TABLE 14444 06-JUL-12 01.00.31.051355 AM +10:00 10 1321312 29130 126 15700 05-JUL-12

KRADSNP L901_REQQESM TABLE 14444 07-JUL-12 01.00.32.227695 AM +10:00 10 1310964 29130 125 16257 06-JUL-12

KRADSNP L901_REQQESM TABLE 14444 08-JUL-12 01.00.34.881812 AM +10:00 10 1330625 29130 125 15871 07-JUL-12

KRADSNP L901_REQQESM TABLE 14444 09-JUL-12 01.00.31.878459 AM +10:00 10 1292054 29130 125 17255 08-JUL-12

KRADSNP L901_REQQESM TABLE 14444 10-JUL-12 01.00.32.263729 AM +10:00 10 1298390 29130 124 16427 09-JUL-12

KRADSNP L901_REQQESM TABLE 14444 11-JUL-12 01.00.35.061859 AM +10:00 10 1277699 29130 125 15476 10-JUL-12

KRADSNP L901_REQQESM TABLE 14444 12-JUL-12 01.00.33.607805 AM +10:00 10 1292381 29130 126 15838 11-JUL-12

KRADSNP L901_REQQESM TABLE 14444 13-JUL-12 01.00.31.355977 AM +10:00 10 1313700 29130 125 17322 12-JUL-12

KRADSNP L901_REQQESM TABLE 14444 14-JUL-12 01.00.34.465944 AM +10:00 10 1325156 29130 125 19351 13-JUL-12

KRADSNP L901_REQQESM TABLE 14444 15-JUL-12 01.00.35.710237 AM +10:00 10 1328732 29130 125 18537 14-JUL-12

KRADSNP L901_REQQESM TABLE 14444 16-JUL-12 01.00.29.063225 AM +10:00 10 1267732 29130 126 16576 15-JUL-12

KRADSNP L901_REQQESM TABLE 14444 17-JUL-12 01.00.31.753118 AM +10:00 10 1270792 29130 127 16013 16-JUL-12

32 rows selected.

SQL>

同理，看索引的历史统计信息可以用WRI$_OPTSTAT_IND_HISTORY。

(4) 关于Exadata中的执行计划。
在Exadata中，由于存在storage index和smart scan，传统的执行计划不一定是最佳，因为如果能在Exadata storage server这一层中，进行抽取所需的数据，把过滤之后的数据传给database server，这样效率会高很多。所以我们可以试试尽量走全表扫描，或者index full scan，不要走index fast full scan。
如果走表的全表扫描，你会看到在执行计划中，有table access storage full，而不是我们平时的table access full，这是有用到smart scan的一个标准：

------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                            |      1 |        |  14604 |00:03:32.09 |     740K|    615K| 
|*  1 |  HASH JOIN OUTER                   |                            |      1 |  14604 |  14604 |00:03:32.09 |     740K|    615K| 
|   2 |   VIEW                             |                            |      1 |  14604 |  14604 |00:00:00.14 |   12708 |      0 | 
|   3 |    HASH GROUP BY                   |                            |      1 |  14604 |  14604 |00:00:00.14 |   12708 |      0 | 
|   4 |     TABLE ACCESS STORAGE FULL      | KDS_SP_ORSAR_OM_DASDQ_TEMP |      1 |    390K|    390K|00:00:00.11 |   12708 |      0 | 
|   5 |   VIEW                             |                            |      1 |  25723 |  12200 |00:03:31.94 |     728K|    615K| 
|   6 |    HASH GROUP BY                   |                            |      1 |  25723 |  12200 |00:03:31.94 |     728K|    615K| 
|*  7 |     HASH JOIN                      |                            |      1 |  25723 |    548K|00:03:32.07 |     728K|    615K| 
|   8 |      TABLE ACCESS STORAGE FULL     | kDS_CP_QUOTAALON_HQADERS   |      1 |     10M|     10M|00:00:01.44 |   99833 |      0 | 
|*  9 |      HASH JOIN                     |                            |      1 |     46M|    627M|00:02:03.78 |     628K|    615K| 
|  10 |       VIEW                         | VW_GBF_14                  |      1 |  14604 |  14604 |00:00:00.14 |   12708 |      0 | 
|  11 |        HASH GROUP BY               |                            |      1 |  14604 |  14604 |00:00:00.14 |   12708 |      0 | 
|  12 |         VIEW                       |                            |      1 |  14604 |  14604 |00:00:00.14 |   12708 |      0 |
|  13 |          HASH GROUP BY             |                            |      1 |  14604 |  14604 |00:00:00.13 |   12708 |      0 |
|  14 |           TABLE ACCESS STORAGE FULL| KDS_SP_ORSAR_OM_DASDQ_TEMP |      1 |    390K|    390K|00:00:00.10 |   12708 |      0 |
|  15 |       TABLE ACCESS STORAGE FULL    | kDS_CP_QUOTALAON_LMJES     |      1 |    180M|    180M|00:00:20.74 |     615K|    615K|
------------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | | 14604 |00:03:32.09 | 740K| 615K|

|* 1 | HASH JOIN OUTER | | 1 | 14604 | 14604 |00:03:32.09 | 740K| 615K|

| 2 | VIEW | | 1 | 14604 | 14604 |00:00:00.14 | 12708 | 0 |

| 3 | HASH GROUP BY | | 1 | 14604 | 14604 |00:00:00.14 | 12708 | 0 |

| 4 | TABLE ACCESS STORAGE FULL | KDS_SP_ORSAR_OM_DASDQ_TEMP | 1 | 390K| 390K|00:00:00.11 | 12708 | 0 |

| 5 | VIEW | | 1 | 25723 | 12200 |00:03:31.94 | 728K| 615K|

| 6 | HASH GROUP BY | | 1 | 25723 | 12200 |00:03:31.94 | 728K| 615K|

|* 7 | HASH JOIN | | 1 | 25723 | 548K|00:03:32.07 | 728K| 615K|

| 8 | TABLE ACCESS STORAGE FULL | kDS_CP_QUOTAALON_HQADERS | 1 | 10M| 10M|00:00:01.44 | 99833 | 0 |

|* 9 | HASH JOIN | | 1 | 46M| 627M|00:02:03.78 | 628K| 615K|

| 10 | VIEW | VW_GBF_14 | 1 | 14604 | 14604 |00:00:00.14 | 12708 | 0 |

| 11 | HASH GROUP BY | | 1 | 14604 | 14604 |00:00:00.14 | 12708 | 0 |

| 12 | VIEW | | 1 | 14604 | 14604 |00:00:00.14 | 12708 | 0 |

| 13 | HASH GROUP BY | | 1 | 14604 | 14604 |00:00:00.13 | 12708 | 0 |

| 14 | TABLE ACCESS STORAGE FULL| KDS_SP_ORSAR_OM_DASDQ_TEMP | 1 | 390K| 390K|00:00:00.10 | 12708 | 0 |

| 15 | TABLE ACCESS STORAGE FULL | kDS_CP_QUOTALAON_LMJES | 1 | 180M| 180M|00:00:20.74 | 615K| 615K|

------------------------------------------------------------------------------------------------------------------------------------

相关文章

在9i的数据库中排除某些表做owner级导出

细节：utl_file_dir错误设置导致ORA-29280

oracle的sysdba权限登录问题

发表回复 取消回复

发表回复取消回复