1.1.1 퍼포먼스 튜닝의 순서[편집]

1. 구체적인 목표치를 결정한다.
2. 불필요한 동작이 없는가 점검한다.
3. 베이스 라인을 수집한다.

     베이스 라인이라고 하는 것은 정상 작동시의 정보를 말한다.

1. 대체방법에 대한 리스트를 작성하고 최우선 순위를 정한다.

1.1.2 퍼포먼스 튜닝 실행시의 주의점[편집]

1. 파라미터는 한번에 하나씩만 변경한다.
2. 파라미터의 수치는 원래대로 돌릴 수 있게 해둔다.
3. 변경된 파라미터의 수치는 문서로 남긴다.
4. 관련된 수치를 비교할 때는 같은 타이밍에 수치를 수집한다.
5. 한번으로 끝내지않는다.

1.2.1 튜닝 대상 찾기[편집]

WITH TMP_SQLSTAT_HIST AS(
SELECT /*+ LEADING(X B) USE_NL(B) USE_HASH(A) FULL(B) */
       B.DBID, B.SQL_ID, B.INSTANCE_NUMBER
     , TO_CHAR(END_INTERVAL_TIME, 'YYYYMMDD') SNAP_DATE
     , MAX(PARSING_SCHEMA_NAME) PARSING_SCHEMA_NAME
     , B.PLAN_HASH_VALUE
     , MAX(MODULE) MODULE
     , SUM(EXECUTIONS_DELTA) EXECUTIONS
     , SUM(BUFFER_GETS_DELTA) BUFFER_GETS
     , ROUND(SUM(BUFFER_GETS_DELTA) / DECODE(SUM(EXECUTIONS_DELTA), 0, 1, 
                                             SUM(EXECUTIONS_DELTA))) EXEC_BUFFER_GETS
     , SUM(DISK_READS_DELTA) DISK_READS
     , ROUND(SUM(DISK_READS_DELTA) / DECODE(SUM(EXECUTIONS_DELTA), 0, 1, 
                                            SUM(EXECUTIONS_DELTA))) EXEC_DISK_READS
     , SUM(ROWS_PROCESSED_DELTA) ROWS_PROCESSED
     , ROUND(SUM(ROWS_PROCESSED_DELTA) / DECODE(SUM(EXECUTIONS_DELTA), 0, 1, 
                                                SUM(EXECUTIONS_DELTA))) EXEC_ROWS
     , SUM(CPU_TIME_DELTA) CPU_TIME
     , ROUND(SUM(CPU_TIME_DELTA) / DECODE(SUM(EXECUTIONS_DELTA), 0, 1, 
                                          SUM(EXECUTIONS_DELTA)) / 1000000, 6) EXEC_CPU_TIME
     , SUM(CLWAIT_DELTA) CLUSTER_WAIT_TIME
     , ROUND(SUM(CLWAIT_DELTA) / DECODE(SUM(EXECUTIONS_DELTA), 0, 1, 
                                        SUM(EXECUTIONS_DELTA)) / 1000000, 6) EXEC_CLWAIT_TIME
     , SUM(ELAPSED_TIME_DELTA) ELAPSED_TIME  
     , ROUND(SUM(ELAPSED_TIME_DELTA) / DECODE(SUM(EXECUTIONS_DELTA), 0, 1, 
                                              SUM(EXECUTIONS_DELTA)) / 1000000, 6) EXEC_ELAPSED_TIME     
     , ROUND(MAX(EXECUTIONS_DELTA) / 600, 3) EXEC_FOR_SEC
       --최종 수행된 SQL 조회, 여러 INSTANCE에서 수행시는 BUFER_GETS 높은 것 우선
     , ROW_NUMBER() OVER(PARTITION BY B.DBID, B.SQL_ID 
                             ORDER BY TO_CHAR(END_INTERVAL_TIME, 'YYYYMMDD') DESC
                                    , SUM(BUFFER_GETS_DELTA) DESC) RNK 
  FROM (SELECT /*+ NO_MERGE */
               DBID, MIN(SNAP_ID) MIN_SNAP_ID, MAX(SNAP_ID) MAX_SNAP_ID
          FROM SYS.WRM$_SNAPSHOT --DBA_HIST_SNAPSHOT --SQL 실행 구간
         WHERE END_INTERVAL_TIME >= TRUNC(TO_DATE('20130513', 'YYYYMMDD'), 'IW') - 
           AND END_INTERVAL_TIME < TRUNC(TO_DATE('20130513', 'YYYYMMDD'), 'IW') 
         GROUP BY DBID) X,    --DBA_HIST_SQLSTAT과 조인 시 해당 파티션만 SCAN하기 위해 만든 집합임
       SYS.WRM$_SNAPSHOT A,   --DBA_HIST_SNAPSHOT
       SYS.WRH$_SQLSTAT B     --DBA_HIST_SQLSTAT과
 WHERE X.DBID = B.DBID
   AND B.SNAP_ID BETWEEN X.MIN_SNAP_ID AND X.MAX_SNAP_ID
   AND A.DBID = B.DBID
   AND A.SNAP_ID = B.SNAP_ID
   AND A.INSTANCE_NUMBER = B.INSTANCE_NUMBER
   AND A.END_INTERVAL_TIME >= TRUNC(TO_DATE('20130513', 'YYYYMMDD'), 'IW') - 7
   AND A.END_INTERVAL_TIME < TRUNC(TO_DATE('20130513', 'YYYYMMDD'), 'IW')
   AND NVL(B.PARSING_SCHEMA_NAME,'-') NOT IN ('SYS','SYSMAN','DBSNMP','SYSTEM','EXFSYS')
   AND NOT REGEXP_LIKE(NVL(B.MODULE,'-'), 'Orange|SQL Developer|SQLGate|Data Pump|
                                           TOAD|golden|ERwin|PL.SQL Developer|
                                           SQL Loader|sqlplus|SQL.Plus|oracle|DBMS_SCHEDULER', 'i')
   AND PLAN_HASH_VALUE > 0
   AND MODULE IS NOT NULL
 GROUP BY B.DBID, B.SQL_ID, B.INSTANCE_NUMBER, B.PLAN_HASH_VALUE
        , TO_CHAR(END_INTERVAL_TIME, 'YYYYMMDD')
)
SELECT --같거나 비슷한 SQL은 같은 GROUP으로 표현
       DENSE_RANK() OVER(ORDER BY DBMS_LOB.SUBSTR(B.SQL_TEXT, 100, 1)) SQL_GRP_TYPE 
     , A.DBID             
     , A.INSTANCE_NUMBER    --최종 수행된 INSTANCE
     , A.SNAP_DATE          --최종 수행 일자
     , A.SQL_ID             --SQL_ID
     , A.PLAN_HASH_VALUE    --실행 계획에 종속적인 값
     , A.MODULE             --실행 모듈
     , A.EXECUTIONS         --총 실행수
     , A.EXEC_ROWS          --실행당 결과 건수
     , A.EXEC_BUFFER_GETS   --실행당 BUFFER_GET
     , A.BUFFER_GETS        --총 BUFFER_GET
     , A.EXEC_DISK_READS    --실행당 DISK_READ
     , A.EXEC_ELAPSED_TIME  --실행당 수행 시간
     , A.EXEC_CPU_TIME      --실행당 CPU 시간
     , A.EXEC_FOR_SEC       --초당 실행수, 특정 시간에만 집중적으로 수행되는 SQL 판별위함
     , A.ELAPSED_TIME       --총 수행시간
     , A.CPU_TIME           --총 CPU 시간
     , A.CLUSTER_WAIT_TIME  --총 CLUSTER 대기 시간
     , ROUND(A.CPU_TIME / A.ELAPSED_TIME, 2) CPU_RATE -- 수행시간 대비 CPU 시간 비율
  FROM (
        SELECT SNAP_DATE, DBID, INSTANCE_NUMBER, SQL_ID, PLAN_HASH_VALUE, MODULE 
             , EXECUTIONS, EXEC_ROWS, EXEC_BUFFER_GETS, BUFFER_GETS, EXEC_DISK_READS
             , ELAPSED_TIME, CPU_TIME, CLUSTER_WAIT_TIME
             , EXEC_ELAPSED_TIME, EXEC_CPU_TIME, EXEC_FOR_SEC
             , RANK() OVER(PARTITION BY DBID ORDER BY EXECUTIONS DESC) EXECUTIONS_RNK
             , RANK() OVER(PARTITION BY DBID ORDER BY BUFFER_GETS DESC) BUFFER_GETS_RNK
             , RANK() OVER(PARTITION BY DBID ORDER BY EXEC_CPU_TIME DESC) EXEC_CPU_TIME_RNK
             , RANK() OVER(PARTITION BY DBID ORDER BY EXEC_BUFFER_GETS DESC) EXEC_BUFFER_GETS_RNK
          FROM TMP_SQLSTAT_HIST
         WHERE NOT((CLUSTER_WAIT_TIME/DECODE(CPU_TIME, 0, 1, CPU_TIME) <= 0.95 OR 
                    CPU_TIME/ELAPSED_TIME <= 0.9) AND
                   (
                    (EXECUTIONS >= 300000 AND EXEC_BUFFER_GETS <= 20) OR
                    (EXECUTIONS >= 100000 AND EXECUTIONS < 300000 AND EXEC_BUFFER_GETS <= 50) OR
                    (EXECUTIONS >= 50000 AND EXECUTIONS < 100000 AND EXEC_BUFFER_GETS <= 100) OR
                    (EXECUTIONS >= 10000 AND EXECUTIONS < 50000 AND EXEC_BUFFER_GETS <= 200) OR
                    (EXECUTIONS >= 5000 AND EXECUTIONS < 10000 AND EXEC_BUFFER_GETS <= 500)  OR
                    (EXECUTIONS >= 3000 AND EXECUTIONS < 5000 AND EXEC_BUFFER_GETS <= 1000) OR
                    (EXECUTIONS >= 1000 AND EXECUTIONS < 3000 AND EXEC_BUFFER_GETS <= 2000) OR
                    (EXECUTIONS >= 500 AND EXECUTIONS < 1000 AND EXEC_BUFFER_GETS <= 3000) OR
                    (EXECUTIONS >= 100 AND EXECUTIONS < 500 AND EXEC_BUFFER_GETS <= 5000) OR
                    (EXECUTIONS >= 10 AND EXECUTIONS < 100 AND EXEC_BUFFER_GETS <= 10000) OR
                    (EXECUTIONS >= 3 AND EXECUTIONS < 10 AND EXEC_BUFFER_GETS <= 30000) OR
                    (EXECUTIONS < 3 AND EXEC_BUFFER_GETS <= 100000) 
                   )
                  ) --일간 실행수 대비 실행당 BUFFER_GETS가 크지 않은 경우 튜닝 대상 제외
                    --단 CPU_TIME 비율이 높으면 포함. 제외 기준은 변경 및 삭제가 자유로움
            AND NOT(EXEC_ELAPSED_TIME <= 0.0005) --실행당 ELASED_TIME이 0.0005초 미만 제외
            AND NOT(EXEC_ROWS*2 >= EXEC_BUFFER_GETS)--실행당 건수가 실행당 BUFFER_GET*2이상 제외   
            AND RNK = 1) A,
       DBA_HIST_SQLTEXT B
 WHERE A.DBID = B.DBID
   AND A.SQL_ID = B.SQL_ID
   AND (A.EXECUTIONS_RNK <= 30 OR --실행수 TOP 30
        A.BUFFER_GETS_RNK <= 30 OR --BUFFER_GET TOP 30
        A.EXEC_BUFFER_GETS_RNK <= 30 OR --실행당 BUFFER_GET TOP 30
        A.EXEC_FOR_SEC >= 10);

1.2.2 GV$SQL(V$SQL) 이용 튜닝 대상 조회[편집]

• (실행수 TOP 30, BUFFER_GET TOP 30,실행당 BUFFER_GET TOP 30,EXEC_FOR_SEC >= 10 초당 10회 이상 실행 )

WITH TMP_SQLSTAT_HIST AS(
SELECT SQL_ID, INST_ID
     , MAX(LAST_ACTIVE_TIME) SNAP_DATE
     , MAX(PARSING_SCHEMA_NAME) PARSING_SCHEMA_NAME
     , PLAN_HASH_VALUE
     , MAX(MODULE) MODULE
     , SUM(EXECUTIONS) EXECUTIONS
     , SUM(BUFFER_GETS) BUFFER_GETS
     , ROUND(SUM(BUFFER_GETS) / DECODE(SUM(EXECUTIONS), 0, 1, 
                                       SUM(EXECUTIONS))) EXEC_BUFFER_GETS
     , SUM(DISK_READS) DISK_READS
     , ROUND(SUM(DISK_READS) / DECODE(SUM(EXECUTIONS), 0, 1, 
                                      SUM(EXECUTIONS))) EXEC_DISK_READS
     , SUM(ROWS_PROCESSED) ROWS_PROCESSED
     , ROUND(SUM(ROWS_PROCESSED) / DECODE(SUM(EXECUTIONS), 0, 1, 
                                          SUM(EXECUTIONS))) EXEC_ROWS
     , SUM(CPU_TIME) CPU_TIME
     , ROUND(SUM(CPU_TIME) / DECODE(SUM(EXECUTIONS), 0, 1, 
                                    SUM(EXECUTIONS)) / 1000000, 6) EXEC_CPU_TIME
     , SUM(CLUSTER_WAIT_TIME) CLUSTER_WAIT_TIME
     , ROUND(SUM(CLUSTER_WAIT_TIME) / DECODE(SUM(EXECUTIONS), 0, 1, 
                                             SUM(EXECUTIONS)) / 1000000, 6) EXEC_CLWAIT_TIME
     , SUM(ELAPSED_TIME) ELAPSED_TIME  
     , ROUND(SUM(ELAPSED_TIME) / DECODE(SUM(EXECUTIONS), 0, 1, 
                                        SUM(EXECUTIONS)) / 1000000, 6) EXEC_ELAPSED_TIME          
  FROM GV$SQL B
 WHERE NVL(B.PARSING_SCHEMA_NAME,'-') NOT IN ('SYS','SYSMAN','DBSNMP','SYSTEM','EXFSYS')
   AND NOT REGEXP_LIKE(NVL(B.MODULE,'-'), 'Orange|SQL Developer|SQLGate|Data Pump|
                                           TOAD|golden|ERwin|PL.SQL Developer|
                                           SQL Loader|sqlplus|SQL.Plus|oracle|DBMS_SCHEDULER', 'i')
   AND PLAN_HASH_VALUE > 0
   AND MODULE IS NOT NULL
 GROUP BY SQL_ID, INST_ID, PLAN_HASH_VALUE
)
SELECT --같거나 비슷한 SQL은 같은 GROUP으로 표현
       DENSE_RANK() OVER(ORDER BY DBMS_LOB.SUBSTR(B.SQL_TEXT, 100, 1)) SQL_GRP_TYPE 
     , A.INST_ID            --최종 수행된 INSTANCE
     , A.SNAP_DATE          --최종 수행 일자
     , A.SQL_ID             --SQL_ID
     , A.PLAN_HASH_VALUE    --실행 계획에 종속적인 값
     , A.MODULE             --실행 모듈
     , A.EXECUTIONS         --총 실행수
     , A.EXEC_ROWS          --실행당 결과 건수
     , A.EXEC_BUFFER_GETS   --실행당 BUFFER_GET
     , A.BUFFER_GETS        --총 BUFFER_GET
     , A.EXEC_DISK_READS    --실행당 DISK_READ
     , A.EXEC_ELAPSED_TIME  --실행당 수행 시간
     , A.EXEC_CPU_TIME      --실행당 CPU 시간
     , A.ELAPSED_TIME       --총 수행시간
     , A.CPU_TIME           --총 CPU 시간
     , A.CLUSTER_WAIT_TIME  --총 CLUSTER 대기 시간
     , ROUND(A.CPU_TIME / A.ELAPSED_TIME, 2) CPU_RATE -- 수행시간 대비 CPU 시간 비율
  FROM (
        SELECT SNAP_DATE, INST_ID, SQL_ID, PLAN_HASH_VALUE, MODULE 
             , EXECUTIONS, EXEC_ROWS, EXEC_BUFFER_GETS, BUFFER_GETS, EXEC_DISK_READS
             , ELAPSED_TIME, CPU_TIME, CLUSTER_WAIT_TIME
             , EXEC_ELAPSED_TIME, EXEC_CPU_TIME
             , RANK() OVER(ORDER BY EXECUTIONS DESC) EXECUTIONS_RNK
             , RANK() OVER(ORDER BY BUFFER_GETS DESC) BUFFER_GETS_RNK
             , RANK() OVER(ORDER BY EXEC_CPU_TIME DESC) EXEC_CPU_TIME_RNK
             , RANK() OVER(ORDER BY EXEC_BUFFER_GETS DESC) EXEC_BUFFER_GETS_RNK
          FROM TMP_SQLSTAT_HIST
         WHERE NOT((CLUSTER_WAIT_TIME/DECODE(CPU_TIME, 0, 1, CPU_TIME) <= 0.95 OR 
                    CPU_TIME/ELAPSED_TIME <= 0.9) AND
                   (
                    (EXECUTIONS >= 300000 AND EXEC_BUFFER_GETS <= 20) OR
                    (EXECUTIONS >= 100000 AND EXECUTIONS < 300000 AND EXEC_BUFFER_GETS <= 50) OR
                    (EXECUTIONS >= 50000 AND EXECUTIONS < 100000 AND EXEC_BUFFER_GETS <= 100) OR
                    (EXECUTIONS >= 10000 AND EXECUTIONS < 50000 AND EXEC_BUFFER_GETS <= 200) OR
                    (EXECUTIONS >= 5000 AND EXECUTIONS < 10000 AND EXEC_BUFFER_GETS <= 500)  OR
                    (EXECUTIONS >= 3000 AND EXECUTIONS < 5000 AND EXEC_BUFFER_GETS <= 1000) OR
                    (EXECUTIONS >= 1000 AND EXECUTIONS < 3000 AND EXEC_BUFFER_GETS <= 2000) OR
                    (EXECUTIONS >= 500 AND EXECUTIONS < 1000 AND EXEC_BUFFER_GETS <= 3000) OR
                    (EXECUTIONS >= 100 AND EXECUTIONS < 500 AND EXEC_BUFFER_GETS <= 5000) OR
                    (EXECUTIONS >= 10 AND EXECUTIONS < 100 AND EXEC_BUFFER_GETS <= 10000) OR
                    (EXECUTIONS >= 3 AND EXECUTIONS < 10 AND EXEC_BUFFER_GETS <= 30000) OR
                    (EXECUTIONS < 3 AND EXEC_BUFFER_GETS <= 100000) 
                   )
                  ) --일간 실행수 대비 실행당 BUFFER_GETS가 크지 않은 경우 튜닝 대상 제외
                    --단 CPU_TIME 비율이 높으면 포함. 제외 기준은 변경 및 삭제가 자유로움
            AND NOT(EXEC_ELAPSED_TIME <= 0.0005) --실행당 ELASED_TIME이 0.0005초 미만 제외
            AND NOT(EXEC_ROWS*2 >= EXEC_BUFFER_GETS)--실행당 건수가 실행당 BUFFER_GET*2이상 제외   
            ) A,
       GV$SQL B
 WHERE A.INST_ID = B.INST_ID
   AND A.SQL_ID = B.SQL_ID
   AND B.CHILD_NUMBER = 0
   AND (A.EXECUTIONS_RNK <= 30 OR                             --실행수 TOP 30
        A.BUFFER_GETS_RNK <= 30 OR                            --TOTAL BUFFER_GETS TOP 30
        A.EXEC_BUFFER_GETS_RNK <= 30 OR                       --실행당 BUFFER_GETS TOP 30
        (A.EXECUTIONS >= 100 AND A.EXEC_ELAPSED_TIME >= 10)); --실행수 100이상, 수행시간 10초 이상

SELECT A.SID      , C.SERIAL# , A.VALUE
     , C.USERNAME , C.STATUS  , C.PROGRAM
  FROM V$SESSTAT A
     , V$STATNAME B
     , V$SESSION C
 WHERE A.STATISTIC# = B.STATISTIC# 
   AND A.SID = C.SID 
   AND B.NAME = 'CPU used by this session' 
   AND A.VALUE > 0 ORDER BY A.VALUE DESC;

SELECT DISK_READS, SQL_TEXT 
  FROM V$SQLAREA 
 WHERE DISK_READS > 100 
 ORDER BY DISK_READS DESC;

SELECT A.NAME    , B.XACTS    , C.SID
     , C.SERIAL# , C.USERNAME , D.SQL_TEXT
  FROM V$ROLLNAME A , V$ROLLSTAT B
     , V$SESSION C  , V$SQLTEXT D
     , V$TRANSACTION E
 WHERE A.USN = B.USN 
   AND B.USN = E.XIDUSN 
   AND C.TADDR = E.ADDR 
   AND C.SQL_ADDRESS = D.ADDRESS 
   AND C.SQL_HASH_VALUE = D.HASH_VALUE 
 ORDER BY A.NAME, C.SID, D.PIECE;

SELECT SID
     , SERIAL#
     , OPNAME
     , TO_CHAR(START_TIME, 'HH24:MI:SS') AS "START"
     , (SOFAR / TOTALWORK) * 100 AS "PERCENT_COMPLETE"
  FROM V$SESSION_LONGOPS;

SELECT A.*

     , (SELECT   SS.SQL_TEXT
           FROM     V$SQLAREA SS
           WHERE    SS.ADDRESS = A.SQL_ADDRESS
           AND      ROWNUM <= 1
          ) AS SQL_TEST

  FROM (

          SELECT A.SID      , C.SERIAL# , A.VALUE               
               , C.USERNAME , C.STATUS  , C.PROGRAM
               , C.SQL_ADDRESS
               , ROW_NUMBER() OVER (ORDER BY A.VALUE DESC) RN
            FROM V$SESSTAT A
               , V$STATNAME B
               , V$SESSION C
          WHERE  A.STATISTIC# = B.STATISTIC#
          AND    A.SID = C.SID
          AND    B.NAME = 'CPU used by this session'
          AND    A.VALUE > 0
          AND    C.STATUS = 'ACTIVE'
          AND    C.USERNAME IS NOT NULL
          ) A
 WHERE A.RN <= 10;

SELECT ABS(SYSDATE - A.LAST_LOAD_TIME) * 24 * 60 * 60 AS SEC_TIEM, A.* 
  FROM V$SQLAREA A
     , V$SESSION B
 WHERE A.SQL_TEXT LIKE '%SELECT%' 
   AND A.ADDRESS = B.SQL_ADDRESS 
   AND B.STATUS = 'ACTIVE' AND A.ELAPSED_TIME >= 10 * 1000000 
   -- 실행계획에서 10초 이상 걸리는 쿼리를 조회(실제 걸리는 시간은 아님.) 
  AND A.PARSING_SCHEMA_NAME NOT IN ('SYS', 'SYSTEM', 'SYSMAN') 
  AND B.USERNAME IS NOT NULL;

SELECT B.USERNAME  , A.SID     , A.PGA_USAGE
     , A.UGA_USAGE , A.CPU_USAGE_SECONDS     , B.MACHINE
     , B.PROGRAM   , B.MODULE
  FROM (SELECT B.SID
                , MAX(DECODE(C.NAME, 'session pga memory', TRUNC(B.VALUE / 1024 / 1024) || 'MB', 0)) AS PGA_USAGE
                , MAX(DECODE(C.NAME, 'session uga memory', TRUNC(B.VALUE / 1024 / 1024) || 'MB', 0)) AS UGA_USAGE
                , MAX(DECODE(C.NAME, 'CPU used by this session', (B.VALUE / 100) || ' Sec', 0)) AS CPU_USAGE_SECONDS
             FROM V$SESSTAT B
                , V$STATNAME C
           WHERE     B.STATISTIC# = C.STATISTIC#
           GROUP BY  B.SID) A
     , V$SESSION B

 WHERE B.SID = A.SID AND B.STATUS = 'ACTIVE' AND B.USERNAME IS NOT NULL;

select a.sql_id, a.child_number child,
       a.id
     , a.parent_id pid
      ----------------------------------------------------------------
     ,  lpad(' ', 2*(a.depth))||a.operation || decode(a.id,0,' (Optimizer='||a.optimizer||') ')
	    || decode(a.options,null,null,' (') || a.options || decode(a.options,null,null,')')
        || decode(a.object_name,null,null,' OF ' || a.object_owner||'.'||a.object_name 
                                         || ' ('|| a.object_type ||')')
        ----------------------------------------------------------------(
        || decode(a.cost||a.cardinality||a.bytes,null,null,' (')
        || decode(a.cost, null, null, ' Cost='||a.cost)
        || decode(a.cardinality, null, null, ' Card='||a.cardinality)
        || decode(a.bytes, null, null, ' Bytes='||a.bytes)
        || decode(a.cost||a.cardinality||a.bytes,null,null,' )')
        ----------------------------------------------------------------)
        || decode(a.other_tag,null,null,'(')||a.other_tag||decode(a.other_tag,null,null,')') as "Operation"
       ---------------------------------------------------------------
     , a.object_type         "Type"
       ----------------------------------------------------------------
     , a.cardinality         "E-Rows"
     , a.bytes               "E-Bytes"
     , a.cost
     , a.time                "E-Time"
       ----------------------------------------------------------------
     , a.partition_start     "Pstart"
     , a.partition_stop      "Pstop"
     , a.partition_id        "Pid"
       ----------------------------------------------------------------
     , a.qblock_name            "QB"
     , a.access_predicates
     , a.filter_predicates
       ----------------------------------------------------------------
  from v$sql_plan a
 where 1 = 1
   and a.sql_id = '5q1b8cn1s123u' --and plan_hash_value = ''
--   and a.child_number = 1
 order by a.sql_id, a.id, a.child_number;