"ORACLE 튜닝 대상 조회"의 두 판 사이의 차이

1 CPU를 많이 사용하는 세션의 식별[편집]

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;

2 Disk Read 가 많은 SQL문 찾기[편집]

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

3 Rollback Segment를 사용하고 있는 SQL문 조회[편집]

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;

4 오래도록 수행되는 Full Table Scan를 모니터링[편집]

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

5 CPU를 많이 사용하는 세션의 식별(SQL TEXT 조회)[편집]

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;

6 현재 세션에서 10초이상 걸리는 쿼리 조회 (SELECT절)[편집]

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;

7 현재 세션에서 PGA, UGA, CPU 사용량 세션별로 조회하는 쿼리[편집]

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;

8 sql 플랜 보기[편집]

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;

9 엑세스 VS FILTER 비교[편집]

https://blogs.oracle.com/sql/query-tuning-101%3a-comparing-execution-plans-and-access-vs-filter-predicates