查询SQL执行计划中的对象信息（脚本分享）

对于优化来说，表和索引信息（bytes;num_rows）方便开发DBA作出准确判断，快速定位SQL语句性能瓶颈

下面分享两个我自用着比较方便的脚本，如发现脚本有疏漏或可改进之处，请留言告知。谢谢！！

1.针对某个SQL_ID
WITH X AS
 (SELECT /*+ MATERIALIZE */
   OBJECT_OWNER,
   OBJECT_NAME,
   LISTAGG(OBJECT_ALIAS, ' | ') WITHIN GROUP(ORDER BY OBJECT_NAME) OBJECT_ALIAS,
   OBJECT_TYPE
    FROM (SELECT /*+ MATERIALIZE */
           OBJECT_OWNER,
           OBJECT_NAME,
           CASE
             WHEN OBJECT_TYPE LIKE 'TABLE%' THEN
              SUBSTR(OBJECT_ALIAS, 1, INSTR(OBJECT_ALIAS, '@') - 1)
             ELSE
              'NOALIAS'
           END OBJECT_ALIAS,
           OBJECT_TYPE,
           ID,
           TIMESTAMP,
           MAX(TIMESTAMP) OVER(ORDER BY TIMESTAMP DESC) AS MAX_TIME
            FROM V$SQL_PLAN
           WHERE SQL_ID = 'xxxxxxxxxx'
             AND OBJECT_NAME IS NOT NULL)
   WHERE TIMESTAMP = MAX_TIME
   GROUP BY OBJECT_NAME, OBJECT_OWNER, OBJECT_TYPE),
Z AS
 (SELECT B.OWNER,
         X.OBJECT_TYPE,
         B.SEGMENT_NAME OBJECT_NAME,
         X.OBJECT_ALIAS ALIAS,
         C.PARTITIONED,
         CASE
           WHEN C.PARTITIONED = 'YES' THEN
            SUM(B.BYTES / 1024 / 1024) OVER(PARTITION BY C.TABLE_NAME)
           ELSE
            B.BYTES / 1024 / 1024
         END SIZE_MB,
         C.NUM_ROWS,
         TRUNC(D.SAMPLE_SIZE / DECODE(D.NUM_ROWS, 0, 1, D.NUM_ROWS) * 100) || '%' ESTIMATE_PERCENT,
         D.LAST_ANALYZED,
         CASE
           WHEN D.STALE_STATS = 'YES' OR D.LAST_ANALYZED IS NULL THEN
            '统计信息过期'
           ELSE
            '统计信息未过期'
         END STATUS,
         ROW_NUMBER() OVER(PARTITION BY D.TABLE_NAME ORDER BY D.PARTITION_NAME) FLAG
    FROM DBA_SEGMENTS B, DBA_TABLES C, DBA_TAB_STATISTICS D, X
   WHERE B.OWNER || B.SEGMENT_NAME || B.PARTITION_NAME =
         D.OWNER || D.TABLE_NAME || D.PARTITION_NAME
     AND D.OWNER || D.TABLE_NAME = C.OWNER || C.TABLE_NAME
     AND B.OWNER || B.SEGMENT_NAME || SUBSTR(B.SEGMENT_TYPE, 1, 5) =
         X.OBJECT_OWNER || OBJECT_NAME ||SUBSTR(X.OBJECT_TYPE,1,5)
     AND B.SEGMENT_TYPE LIKE 'TABLE%'
     AND B.SEGMENT_NAME NOT LIKE '%BIN$%'
  UNION ALL
  SELECT B.OWNER,
         X.OBJECT_TYPE,
         B.SEGMENT_NAME OBJECT_NAME,
         X.OBJECT_ALIAS ALIAS,
         C.PARTITIONED,
         CASE
           WHEN C.PARTITIONED = 'YES' THEN
            SUM(B.BYTES / 1024 / 1024) OVER(PARTITION BY C.INDEX_NAME)
           ELSE
            B.BYTES / 1024 / 1024
         END SIZE_MB,
         C.NUM_ROWS,
         TRUNC(D.SAMPLE_SIZE / DECODE(D.NUM_ROWS, 0, 1, D.NUM_ROWS) * 100) || '%' ESTIMATE_PERCENT,
         D.LAST_ANALYZED,
         CASE
           WHEN D.STALE_STATS = 'YES' OR D.LAST_ANALYZED IS NULL THEN
            '统计信息过期'
           ELSE
            '统计信息未过期'
         END STATUS,
         ROW_NUMBER() OVER(PARTITION BY D.TABLE_NAME ORDER BY D.PARTITION_NAME) FLAG
    FROM DBA_SEGMENTS B, DBA_INDEXES C, DBA_IND_STATISTICS D, X
   WHERE B.OWNER || B.SEGMENT_NAME || B.PARTITION_NAME =
         D.OWNER || D.INDEX_NAME || D.PARTITION_NAME
     AND D.OWNER || D.INDEX_NAME = C.OWNER || C.INDEX_NAME
     AND B.OWNER || B.SEGMENT_NAME || SUBSTR(B.SEGMENT_TYPE, 1, 5) =
         X.OBJECT_OWNER || OBJECT_NAME ||SUBSTR(X.OBJECT_TYPE,1,5)
     AND B.SEGMENT_TYPE LIKE 'INDEX%'
     AND B.SEGMENT_NAME NOT LIKE '%BIN$%')
SELECT OWNER,
       OBJECT_TYPE,
       OBJECT_NAME,
       ALIAS,
       PARTITIONED,
       SIZE_MB,
       NUM_ROWS,
       ESTIMATE_PERCENT,
       LAST_ANALYZED,
       STATUS
  FROM Z
 WHERE FLAG = 1;


2.针对某段SQL代码
(1).EXPLAIN PLAN FOR SELECT xxxxxxx;
(2).WITH X AS
 (SELECT /*+ MATERIALIZE */
 OBJECT_OWNER,
 OBJECT_NAME,
 LISTAGG(OBJECT_ALIAS, ' | ') WITHIN GROUP(ORDER BY OBJECT_NAME) OBJECT_ALIAS,
 OBJECT_TYPE
  FROM (SELECT OBJECT_OWNER,
               OBJECT_NAME,
               CASE
                 WHEN OBJECT_TYPE LIKE 'TABLE%' THEN
                  SUBSTR(OBJECT_ALIAS, 1, INSTR(OBJECT_ALIAS, '@') - 1)
                 ELSE
                  'NOALIAS'
               END OBJECT_ALIAS,
               OBJECT_TYPE,
               TIMESTAMP,
               MAX(TIMESTAMP) OVER(ORDER BY TIMESTAMP DESC) AS MAX_TIME
          FROM PLAN_TABLE
         WHERE OBJECT_NAME IS NOT NULL)
 WHERE TIMESTAMP = MAX_TIME
 GROUP BY OBJECT_NAME, OBJECT_OWNER, OBJECT_TYPE),
Z AS
 (SELECT B.OWNER,
         X.OBJECT_TYPE,
         B.SEGMENT_NAME OBJECT_NAME,
         X.OBJECT_ALIAS ALIAS,
         C.PARTITIONED,
         CASE
           WHEN C.PARTITIONED = 'YES' THEN
            SUM(B.BYTES / 1024 / 1024) OVER(PARTITION BY C.TABLE_NAME)
           ELSE
            B.BYTES / 1024 / 1024
         END SIZE_MB,
         C.NUM_ROWS,
         TRUNC(D.SAMPLE_SIZE / DECODE(D.NUM_ROWS, 0, 1, D.NUM_ROWS) * 100) || '%' ESTIMATE_PERCENT,
         D.LAST_ANALYZED,
         CASE
           WHEN D.STALE_STATS = 'YES' OR D.LAST_ANALYZED IS NULL THEN
            '统计信息过期'
           ELSE
            '统计信息未过期'
         END STATUS,
         ROW_NUMBER() OVER(PARTITION BY D.TABLE_NAME ORDER BY D.PARTITION_NAME) FLAG
    FROM DBA_SEGMENTS B, DBA_TABLES C, DBA_TAB_STATISTICS D, X
   WHERE B.OWNER || B.SEGMENT_NAME || B.PARTITION_NAME =
         D.OWNER || D.TABLE_NAME || D.PARTITION_NAME
     AND D.OWNER || D.TABLE_NAME = C.OWNER || C.TABLE_NAME
     AND B.OWNER || B.SEGMENT_NAME || SUBSTR(B.SEGMENT_TYPE, 1, 5) =
         X.OBJECT_OWNER || OBJECT_NAME ||SUBSTR(X.OBJECT_TYPE,1,5)
     AND B.SEGMENT_TYPE LIKE 'TABLE%'
     AND B.SEGMENT_NAME NOT LIKE '%BIN$%'
  UNION ALL
  SELECT B.OWNER,
         X.OBJECT_TYPE,
         B.SEGMENT_NAME OBJECT_NAME,
         X.OBJECT_ALIAS ALIAS,
         C.PARTITIONED,
         CASE
           WHEN C.PARTITIONED = 'YES' THEN
            SUM(B.BYTES / 1024 / 1024) OVER(PARTITION BY C.INDEX_NAME)
           ELSE
            B.BYTES / 1024 / 1024
         END SIZE_MB,
         C.NUM_ROWS,
         TRUNC(D.SAMPLE_SIZE / DECODE(D.NUM_ROWS, 0, 1, D.NUM_ROWS) * 100) || '%' ESTIMATE_PERCENT,
         D.LAST_ANALYZED,
         CASE
           WHEN D.STALE_STATS = 'YES' OR D.LAST_ANALYZED IS NULL THEN
            '统计信息过期'
           ELSE
            '统计信息未过期'
         END STATUS,
         ROW_NUMBER() OVER(PARTITION BY D.TABLE_NAME ORDER BY D.PARTITION_NAME) FLAG
    FROM DBA_SEGMENTS B, DBA_INDEXES C, DBA_IND_STATISTICS D, X
   WHERE B.OWNER || B.SEGMENT_NAME || B.PARTITION_NAME =
         D.OWNER || D.INDEX_NAME || D.PARTITION_NAME
     AND D.OWNER || D.INDEX_NAME = C.OWNER || C.INDEX_NAME
     AND B.OWNER || B.SEGMENT_NAME || SUBSTR(B.SEGMENT_TYPE, 1, 5) =
         X.OBJECT_OWNER || OBJECT_NAME ||SUBSTR(X.OBJECT_TYPE,1,5)
     AND B.SEGMENT_TYPE LIKE 'INDEX%'
     AND B.SEGMENT_NAME NOT LIKE '%BIN$%')
SELECT OWNER,
       OBJECT_TYPE,
       OBJECT_NAME,
       ALIAS,
       PARTITIONED,
       SIZE_MB,
       NUM_ROWS,
       ESTIMATE_PERCENT,
       LAST_ANALYZED,
       STATUS
  FROM Z
 WHERE FLAG = 1;