행위

ORACLE 튜닝 대상 조회

DB CAFE

Dbcafe (토론 | 기여)님의 2018년 8월 22일 (수) 14:29 판
thumb_up 추천메뉴 바로가기

    1. 46 CPU를 많이 사용하는 세션의 식별

  <syntaxhighlight lang="sql">

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; </syntaxhighlight>
    1. 47 Disk Read 가 많은 SQL문 찾기

  <syntaxhighlight lang="sql">

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

    1. 48 Rollback Segment를 사용하고 있는 SQL문 조회

  <syntaxhighlight lang="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; </syntaxhighlight>
    1. 49 오래도록 수행되는 Full Table Scan를 모니터링

  <syntaxhighlight lang="sql"> SELECT SID 

    , SERIAL#
    , OPNAME
    , TO_CHAR(START_TIME, 'HH24:MI:SS') AS "START"
    , (SOFAR / TOTALWORK) * 100 AS "PERCENT_COMPLETE"

 FROM V$SESSION_LONGOPS; </syntaxhighlight>
    1. 56 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;
    1. 57 현재 세션에서 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;
    1. 58 현재 세션에서 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;
원본 주소 "http://dbcafe.co.kr/wiki/index.php?title=ORACLE_튜닝_대상_조회&oldid=138"