행위

"모니터링 SQL"의 두 판 사이의 차이

DB CAFE

(→‎physical read가 높은 세그먼트 조회)
 
(같은 사용자의 중간 판 23개는 보이지 않습니다)
1번째 줄: 1번째 줄:
 +
 +
 +
=== 연결되어 있는 OS 사용자 및 프로그램 조회  ===
 +
* V$SESSION
 +
<source lang="sql">
 +
SELECT SID
 +
    , SERIAL#
 +
    , OSUSER
 +
    , SUBSTRB(USERNAME, 1, 10) AS USER_NAME
 +
    , SUBSTRB(PROGRAM, 1, 30) AS PROGRAM_NAME
 +
    , STATUS
 +
    , TO_CHAR(LOGON_TIME, 'YYYY/MM/DD HH:MI') AS LOGON_TIME
 +
  FROM V$SESSION WHERE TYPE!= ‘BACKGROUND’ AND STATUS = ‘ACTIVE’;
 +
</source>
 +
 +
=== 1시간 이상 유휴 상태인 세션  ===
 +
 +
<source lang="sql">
 +
SELECT SID
 +
    , SERIAL#
 +
    , USERNAME
 +
    , TRUNC(LAST_CALL_ET / 3600, 2) || ' HR' LAST_CALL_ET
 +
  FROM V$SESSION
 +
WHERE LAST_CALL_ET > 3600
 +
  AND USERNAME IS NOT NULL;
 +
</source>
 +
 +
=== Active Session 중 Idle Time이 긴 작업  ===
 +
* V$SESSION
 +
* V$PROCESS
 +
<source lang="sql">
 +
SELECT VS.SID || ',' || VS.SERIAL# " SID"
 +
    , VP.SPID
 +
    , VS.MACHINE
 +
    , VS.PROGRAM
 +
    , VS.MODULE
 +
    , VS.STATUS
 +
    , TO_CHAR(VS.LOGON_TIME, 'MM/DD HH24:MI') LOGIN_TIME
 +
    , ROUND(VS.LAST_CALL_ET / 60) "IDLE"
 +
  FROM V$SESSION VS
 +
    , V$PROCESS VP
 +
WHERE VS.STATUS = 'ACTIVE'
 +
  AND VS.SID NOT IN (1, 2, 3, 4, 5, 6, 7)
 +
  AND VS.PADDR = VP.ADDR
 +
ORDER BY 8;
 +
</source>
 +
 +
=== DBUser 별로 Session 정보를 조회  ===
 +
<source lang="sql">
 +
SELECT S.USERNAME
 +
    , S.SID
 +
    , S.SERIAL#
 +
    , P.SPID
 +
    , S.OSUSER
 +
    , S.MACHINE
 +
    , S.PROGRAM
 +
    , TO_CHAR(S.LOGON_TIME, 'MM/DD HH24:MI') "LOGON_TIME"
 +
    , ROUND(S.LAST_CALL_ET / 60) "IDLE"
 +
  FROM V$SESSION S
 +
    , V$PROCESS P
 +
WHERE S.PADDR = P.ADDR AND S.USERNAME LIKE UPPER('&DBUSER%')
 +
ORDER BY 9;
 +
</source>
 +
 +
=== Session별 사용 명령어  ===
 +
* V$SESSION
 +
* V$SESSTAT
 +
* V$STATNAME
 +
* V$PROCESS
 +
 +
<source lang="sql">
 +
 +
SELECT SESS.SID
 +
 +
        ,SESS.SERIAL#
 +
        ,SUBSTR(SESS.USERNAME, 1, 10) "USER NAME"
 +
        ,SUBSTR(OSUSER, 1, 11) "OS USER"
 +
        ,SUBSTR(SESS.MACHINE, 1, 15) "MACHINE NAME"
 +
        ,STATUS
 +
        ,UPPER(
 +
            DECODE(NVL(COMMAND, 0)
 +
                  ,0, '---'
 +
                  ,1, 'CREATE TABLE'
 +
                  ,2, 'INSERT -'
 +
                  ,3, 'SELECT -'
 +
                  ,4, 'CREATE CLUST'
 +
                  ,5, 'ALTER CLUST'
 +
                  ,6, 'UPDATE -'
 +
                  ,7, 'DELETE -'
 +
                  ,8, 'DROP -'
 +
                  ,9, 'CREATE INDEX'
 +
                  ,10, 'DROP INDEX'
 +
                  ,11, 'ALTER INDEX'
 +
                  ,12, 'DROP TABLE'
 +
                  ,13, 'CREATE SEQ'
 +
                  ,14, 'ALTER SEQ'
 +
                  ,15, 'ALTER TABLE'
 +
                  ,16, 'DROP SEQ'
 +
                  ,17, 'GRANT'
 +
                  ,18, 'REVOKE'
 +
                  ,19, 'CREATE SYN'
 +
                  ,20, 'DROP SYN'
 +
                  ,21, 'CREATE VIEW'
 +
                  ,22, 'DROP VIEW'
 +
                  ,23, 'VALIDATE IX'
 +
                  ,24, 'CREATE PROC'
 +
                  ,25, 'ALTER PROC'
 +
                  ,26, 'LOCK TABLE'
 +
                  ,27, 'NO OPERATION'
 +
                  ,28, 'RENAME'
 +
                  ,29, 'COMMENT'
 +
                  ,30, 'AUDIT'
 +
                  ,31, 'NOAUDIT'
 +
                  ,32, 'CREATE DBLINK'
 +
                  ,33, 'DROP DB LINK'
 +
                  ,34, 'CREATE DATABASE'
 +
                  ,35, 'ALTER DATABASE'
 +
                  ,36, 'CREATE RBS'
 +
                  ,37, 'ALTER RBS'
 +
                  ,38, 'DROP RBS'
 +
                  ,39, 'CREATE TABLESPACE'
 +
                  ,40, 'ALTER TABLESPACE'
 +
                  ,41, 'DROP TABLESPACE'
 +
                  ,42, 'ALTER SESSION'
 +
                  ,43, 'ALTER USER'
 +
                  ,44, 'COMMIT'
 +
                  ,45, 'ROLLBACK'
 +
                  ,47, 'PL/SQL EXEC'
 +
                  ,48, 'SET TRANSACTION'
 +
                  ,49, 'SWITCH LOG'
 +
                  ,50, 'EXPLAIN'
 +
                  ,51, 'CREATE USER'
 +
                  ,52, 'CREATE ROLE'
 +
                  ,53, 'DROP USER'
 +
                  ,54, 'DROP ROLE'
 +
                  ,55, 'SET ROLE'
 +
                  ,56, 'CREATE SCHEMA'
 +
                  ,58, 'ALTER TRACING'
 +
                  ,59, 'CREATE TRIGGER'
 +
                  ,61, 'DROP TRIGGER'
 +
                  ,62, 'ANALYZE TABLE'
 +
                  ,63, 'ANALYZE INDEX'
 +
                  ,69, 'DROP PROCEDURE'
 +
                  ,71, 'CREATE SNAP LOG'
 +
                  ,72, 'ALTER SNAP LOG'
 +
                  ,73, 'DROP SNAP LOG'
 +
                  ,74, 'CREATE SNAPSHOT'
 +
                  ,75, 'ALTER SNAPSHOT'
 +
                  ,76, 'DROP SNAPSHOT'
 +
                  ,85, 'TRUNCATE TABLE'
 +
                  ,88, 'ALTER VIEW'
 +
                  ,91, 'CREATE FUNCTION'
 +
                  ,92, 'ALTER FUNCTION'
 +
                  ,93, 'DROP FUNCTION'
 +
                  ,94, 'CREATE PACKAGE'
 +
                  ,95, 'ALTER PACKAGE'
 +
                  ,96, 'DROP PACKAGE'
 +
                  ,46, 'SAVEPOINT'
 +
                  )
 +
          )
 +
            COMMAND
 +
        ,SESS.PROCESS "C.PROC"
 +
        ,PROC.SPID "S.PROC"
 +
        ,TO_CHAR(SESS.LOGON_TIME, 'YYYY-MM-DD HH24:MI')
 +
 +
FROM V$SESSION SESS
 +
    , V$SESSTAT STAT
 +
    , V$STATNAME NAME
 +
    , V$PROCESS PROC
 +
WHERE SESS.SID = STAT.SID AND STAT.STATISTIC# = NAME.STATISTIC# AND SESS.USERNAME IS NOT NULL AND NAME.NAME = 'RECURSIVE CALLS' AND SESS.PADDR = PROC.ADDR ORDER BY 3, 1, 2; </source>
 +
 +
=== 사용자 session 2시간 이상 idle 상태가 지속되는 session kill  ===
 +
* V$SESSION
 +
* V$PROCESS
 +
<source lang="sql">
 +
SET PAGESIZE 0 SPOOL KILLIDLE3.SQL
 +
 +
SELECT DISTINCT '!KILL -9 ' || B.SPID, 'ALTER SYSTEM KILL SESSION '''|| A.SID || ',' || A.SERIAL# || ''';'
 +
  FROM V$SESSION A
 +
    , V$PROCESS B
 +
WHERE A.PADDR IN (SELECT S.PADDR
 +
                    FROM V$SESSION S
 +
                    WHERE STATUS = 'INACTIVE'
 +
                    GROUP BY  S.PADDR
 +
                  HAVING MIN(ROUND(LAST_CALL_ET / 60)) > 120)
 +
  AND A.PADDR = B.ADDR
 +
  AND A.STATUS = 'INACTIVE';
 +
 +
SPOOL OFF
 +
</source>
 +
 +
=== Oracle Process의 정보  ===
 +
* V$SESSION
 +
* V$PROCESS
 +
* SYS.V_$SESS_IO
 +
<source lang="sql">
 +
SELECT S.STATUS "STATUS"
 +
        ,S.SERIAL# "SERIAL#"
 +
        ,S.TYPE "TYPE"
 +
        ,S.USERNAME "DB USER"
 +
        ,S.OSUSER "CLIENT USER"
 +
        ,S.SERVER "SERVER"
 +
        ,S.MACHINE "MACHINE"
 +
        ,S.MODULE "MODULE"
 +
        ,S.TERMINAL "TERMINAL"
 +
        ,S.PROGRAM "PROGRAM"
 +
        ,P.PROGRAM "O.S. PROGRAM"
 +
        ,S.LOGON_TIME "CONNECT TIME"
 +
        ,LOCKWAIT "LOCK WAIT"
 +
        ,SI.PHYSICAL_READS "PHYSICAL READS"
 +
        ,SI.BLOCK_GETS "BLOCK GETS"
 +
        ,SI.CONSISTENT_GETS "CONSISTENT GETS"
 +
        ,SI.BLOCK_CHANGES "BLOCK CHANGES"
 +
        ,SI.CONSISTENT_CHANGES "CONSISTENT CHANGES"
 +
        ,S.PROCESS "PROCESS"
 +
        ,P.SPID
 +
        ,P.PID
 +
        ,S.SERIAL#
 +
        ,SI.SID
 +
        ,S.SQL_ADDRESS "ADDRESS"
 +
        ,S.SQL_HASH_VALUE "SQL HASH"
 +
        ,S.ACTION
 +
  FROM V$SESSION S
 +
    , V$PROCESS P
 +
    , SYS.V_$SESS_IO SI
 +
WHERE S.PADDR = P.ADDR(+)
 +
  AND SI.SID(+) = S.SID
 +
  AND S.USERNAME IS NOT NULL
 +
  AND NVL(S.OSUSER, 'X') <> 'SYSTEM'
 +
  AND S.TYPE <> 'BACKGROUND'
 +
ORDER BY 3;
 +
</source>
 +
 +
=== 오브젝트에 접속되어 있는 프로그램 조회  ===
 +
* V$SESSION
 +
* V$ACCESS
 +
<source lang="sql">
 +
SELECT SUBSTR(B.OBJECT, 1, 15) AS OBJECT, SUBSTR(A.PROGRAM, 1, 15) AS PROGRAM, COUNT(*) AS CNT
 +
  FROM V$SESSION A
 +
      ,V$ACCESS B
 +
WHERE A.SID = B.SID
 +
  AND B.OWNER NOT IN ('SYS')
 +
  AND A.TYPE!= 'BACKGROUND'
 +
  AND B.OBJECT LIKE UPPER('&OBJECT_NAME') || '%' GROUP BY B.OBJECT, SUBSTR(A.PROGRAM, 1, 15); </source>
 +
 +
=== 롤백 세그먼트 경합 조회  ===
 +
* V$ROLLSTAT
 +
* V$ROLLNAME
 +
<source lang="sql">
 +
SELECT NAME T0
 +
    , GETS T1        ,WAITS T2
 +
    , TO_CHAR(TRUNC(WAITS / GETS * 100, 2), 099.99) || '%' T3
 +
    , TO_CHAR(ROUND(RSSIZE / 1024)) T4
 +
    , SHRINKS T5
 +
    , EXTENDS T6
 +
  FROM V$ROLLSTAT
 +
    , V$ROLLNAME
 +
WHERE V$ROLLSTAT.USN = V$ROLLNAME.USN;
 +
</source>
 +
 +
=== CPU를 많이 사용하는 세션의 식별  ===
 +
* V$SESSTAT
 +
* V$STATNAME
 +
* V$SESSION
 +
<source 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;
 +
</source>
 +
 +
=== Disk Read 가 많은 SQL문 찾기  ===
 +
* V$SQLAREA
 +
<source lang="sql">
 +
SELECT DISK_READS, SQL_TEXT
 +
  FROM V$SQLAREA
 +
WHERE DISK_READS > 100
 +
ORDER BY DISK_READS DESC; </source>
 +
 +
=== Rollback Segment를 사용하고 있는 SQL문 조회  ===
 +
* V$ROLLNAME
 +
* V$ROLLSTAT
 +
* V$SESSION
 +
* V$SQLTEXT
 +
* V$TRANSACTION
 +
<source 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;
 +
</source>
 +
 +
=== 오래도록 수행되는 Full Table Scan를 모니터링  ===
 +
* V$SESSION_LONGOPS
 +
<source 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;
 +
</source>
 +
 +
=== System 테이블스페이스에 비시스템 세그먼트 조회  ===
 +
* DBA_SEGMENTS
 +
<source lang="sql">
 +
SELECT OWNER
 +
    , SEGMENT_NAME
 +
    , SEGMENT_TYPE
 +
    , TABLESPACE_NAME
 +
  FROM DBA_SEGMENTS
 +
WHERE OWNER NOT IN ('SYS', 'SYSTEM')
 +
  AND TABLESPACE_NAME = 'SYSTEM';
 +
</source>
 +
 +
=== 인덱스의 Delete Space 조회  ===
 +
* INDEX_STATS
 +
<source lang="sql">
 +
--Delete Space% 값이 20% 가 넘으면, 그 인덱스는 다시 작성하는 것이 좋다.
 +
SELECT NAME
 +
    , LF_ROWS
 +
    , DEL_LF_ROWS
 +
    , (DEL_LF_ROWS / LF_ROWS) * 100 AS "DELETE SPACE%"
 +
  FROM INDEX_STATS WHERE NAME = UPPER('&INDEX_NAME');
 +
</source>
 +
 +
=== 딕셔너리/뷰 정보 조회  ===
 +
* DICTIONARY
 +
* DICT_COLUMNS
 +
<source lang="sql">
 +
SELECT A.TABLE_NAME
 +
    , B.COLUMN_NAME
 +
  FROM DICTIONARY A
 +
    , DICT_COLUMNS B
 +
WHERE A.TABLE_NAME = B.TABLE_NAME;
 +
</source>
 
=== 유저의 모든 세션 트레이스 ===
 
=== 유저의 모든 세션 트레이스 ===
 
<source lang=sql>
 
<source lang=sql>
58번째 줄: 416번째 줄:
 
AND
 
AND
 
sql_id='&sqlid'
 
sql_id='&sqlid'
 +
/
 +
</source>
 +
 +
=== 현재 실행 중인 세션의 SQL 조회  ===
 +
<source lang=sql>
 +
select sesion.sid,
 +
sesion.username,
 +
optimizer_mode,
 +
hash_value,
 +
address,
 +
cpu_time,
 +
elapsed_time,
 +
sql_text
 +
from v$sqlarea sqlarea, v$session sesion
 +
where sesion.sql_hash_value = sqlarea.hash_value
 +
and sesion.sql_address = sqlarea.address
 +
and sesion.username is not null;
 +
</source>
 +
 +
=== ACTIVE 세션 정보 조회 ===
 +
 +
<source lang=sql>
 +
set echo off
 +
set linesize 95
 +
set head on
 +
set feedback on
 +
col sid head "Sid" form 9999 trunc
 +
col serial# form 99999 trunc head "Ser#"
 +
col username form a8 trunc
 +
col osuser form a7 trunc
 +
col machine form a20 trunc head "Client|Machine"
 +
col program form a15 trunc head "Client|Program"
 +
col login form a11
 +
col "last call" form 9999999 trunc head "Last Call|In Secs"
 +
col status form a6 trunc
 +
 +
select sid,serial#,substr(username,1,10) username,substr(osuser,1,10) osuser
 +
    , substr(program||module,1,15) program,substr(machine,1,22) machine
 +
    , to_char(logon_time,'ddMon hh24:mi') login
 +
    , last_call_et "last call",status
 +
  from gv$session
 +
where status='ACTIVE'
 +
order by 1
 +
/
 +
</source>
 +
 +
=== DB 대기이벤트 조회 ===
 +
 +
<source lang=sql>
 +
select a.sid,substr(b.username,1,10) username,substr(b.osuser,1,10) osuser
 +
    , substr(b.program||b.module,1,15) program
 +
    , substr(b.machine,1,22) machine
 +
    , a.event
 +
    , a.p1,b.sql_hash_value
 +
  from v$session_wait a
 +
    , V$session b
 +
where b.sid=a.sid
 +
  and a.event not in('SQL*Net message from client','SQL*Net message to client',
 +
'smon timer','pmon timer')
 +
  and username is not null
 +
order by 6
 +
/
 +
</source>
 +
 +
=== 세션의 템프 사용량 조회 ===
 +
 +
<source lang=sql>
 +
SELECT b.tablespace
 +
    , ROUND(((b.blocks*p.value)/1024/1024),2)||'M' AS temp_size
 +
    , a.inst_id as Instance
 +
    , a.sid||','||a.serial# AS sid_serial
 +
    , NVL(a.username, '(oracle)') AS username
 +
    , a.program , a.status , a.sql_id
 +
  FROM gv$session a
 +
    , gv$sort_usage b
 +
    , gv$parameter p
 +
WHERE p.name = 'db_block_size'
 +
  AND a.saddr = b.session_addr
 +
  AND a.inst_id=b.inst_id
 +
  AND a.inst_id=p.inst_id
 +
ORDER BY temp_size desc
 
/
 
/
 
</source>
 
</source>
103번째 줄: 542번째 줄:
 
AND a.statistic# = b.statistic# order by ROUND(a.VALUE/(1024*1024),2) desc
 
AND a.statistic# = b.statistic# order by ROUND(a.VALUE/(1024*1024),2) desc
 
</source>
 
</source>
 +
 +
=== 현재 SGA 사용량 ===
 +
<source lang=sql>
 +
select round(used.bytes /1024/1024 ,2) used_mb
 +
, round(free.bytes /1024/1024 ,2) free_mb
 +
, round(tot.bytes /1024/1024 ,2) total_mb
 +
from (select sum(bytes) bytes
 +
from v$sgastat
 +
where name != 'free memory') used
 +
, (select sum(bytes) bytes
 +
from v$sgastat
 +
where name = 'free memory') free
 +
, (select sum(bytes) bytes
 +
from v$sgastat) tot
 +
</source>
 +
 
=== physical read가 높은 세그먼트 조회 ===
 
=== physical read가 높은 세그먼트 조회 ===
 
<source lang=sql>
 
<source lang=sql>
134번째 줄: 589번째 줄:
 
FROM gv$session s, gv$sort_usage u ,gv$instance i
 
FROM gv$session s, gv$sort_usage u ,gv$instance i
 
WHERE s.saddr=u.session_addr and u.inst_id=i.inst_id order by MB DESC) a where rownum<10;
 
WHERE s.saddr=u.session_addr and u.inst_id=i.inst_id order by MB DESC) a where rownum<10;
 +
</source>
 +
 +
===[ash] TOP 쿼리 ===
 +
<source lang=sql>
 +
Query to get list of top running sqls in PAST between sysdate-1 to sysdate-23/34 . You can change accordingly
 +
 +
SELECT active_session_history.user_id,
 +
dba_users.username,
 +
sqlarea.sql_text,
 +
SUM(active_session_history.wait_time +
 +
active_session_history.time_waited)/1000000 ttl_wait_time_in_seconds
 +
FROM v$active_session_history active_session_history,
 +
v$sqlarea sqlarea,
 +
dba_users
 +
WHERE active_session_history.sample_time BETWEEN SYSDATE - 1 AND SYSDATE-23/24
 +
AND active_session_history.sql_id = sqlarea.sql_id
 +
AND active_session_history.user_id = dba_users.user_id
 +
and dba_users.username not in ('SYS','DBSNMP')
 +
GROUP BY active_session_history.user_id,sqlarea.sql_text, dba_users.username
 +
ORDER BY 4 DESC
 +
</source>
 +
 +
=== [ash] 블로킹 세션 찾기 ===
 +
<source lang=sql>
 +
Query will list the blocking session details between SYSDATE - 1 AND SYSDATE-23/24 ( PAST)
 +
 +
set pagesize 50
 +
set linesize 120
 +
col sql_id format a15
 +
col inst_id format '9'
 +
col sql_text format a50
 +
col module format a10
 +
col blocker_ses format '999999'
 +
col blocker_ser format '999999'
 +
SELECT distinct
 +
a.sql_id ,
 +
a.inst_id,
 +
a.blocking_session blocker_ses,
 +
a.blocking_session_serial# blocker_ser,
 +
a.user_id,
 +
s.sql_text,
 +
a.module,a.sample_time
 +
FROM GV$ACTIVE_SESSION_HISTORY a,
 +
gv$sql s
 +
where a.sql_id=s.sql_id
 +
and blocking_session is not null
 +
and a.user_id <> 0 -- exclude SYS user
 +
and a.sample_time BETWEEN SYSDATE - 1 AND SYSDATE-23/24
 +
</source>
 +
 +
=== cpu 과사용 세션 ===
 +
<source lang=sql>
 +
col program form a30 heading "Program"
 +
col CPUMins form 99990 heading "CPU in Mins"
 +
select rownum as rank, a.*
 +
from (
 +
SELECT v.sid, program, v.value / (100 * 60) CPUMins
 +
FROM v$statname s , v$sesstat v, v$session sess
 +
WHERE s.name = 'CPU used by this session'
 +
and sess.sid = v.sid
 +
and v.statistic#=s.statistic#
 +
and v.value>0
 +
ORDER BY v.value DESC) a
 +
where rownum < 11;
 +
</source>
 +
 +
=== 라이브러리 캐시 락을 잡는 세션 ===
 +
<source lang=sql>
 +
For standalone db:
 +
 +
select sid Waiter, p1raw,
 +
substr(rawtohex(p1),1,30) Handle,
 +
substr(rawtohex(p2),1,30) Pin_addr
 +
from v$session_wait where wait_time=0 and event like '%library cache%';
 +
 +
For RAC DB:
 +
 +
select a.sid Waiter,b.SERIAL#,a.event,a.p1raw,
 +
substr(rawtohex(a.p1),1,30) Handle,
 +
substr(rawtohex(a.p2),1,30) Pin_addr
 +
from v$session_wait a,v$session b where a.sid=b.sid
 +
and a.wait_time=0 and a.event like 'library cache%';
 +
 +
or
 +
 +
set lines 152
 +
col sid for a9999999999999
 +
col name for a40
 +
select a.sid,b.name,a.value,b.class
 +
from gv$sesstat a , gv$statname b
 +
where a.statistic#=b.statistic#
 +
and name like '%library cache%';
 +
</source>
 +
 +
=== 라이브러리 캐시에 의해 락이 발생된 세션 ===
 +
<source lang=sql>
 +
select to_char(SESSION_ID,'999') sid ,
 +
substr(LOCK_TYPE,1,30) Type,
 +
substr(lock_id1,1,23) Object_Name,
 +
substr(mode_held,1,4) HELD, substr(mode_requested,1,4) REQ,
 +
lock_id2 Lock_addr
 +
from dba_lock_internal
 +
where
 +
mode_requested'None'
 +
and mode_requestedmode_held
 +
and session_id in ( select sid
 +
from v$session_wait where wait_time=0
 +
and event like '%library cache%') ;
 +
</source>
 +
 +
=== 유저가 엑세스 하는 오브젝트 조회 ===
 +
<source lang=sql>
 +
set lines 299
 +
column object format a30
 +
column owner format a10
 +
select * from gv$access where owner='&OWNER' and object='&object_name' and
 +
/
 +
</source>
 +
 +
 +
=== FULL TABLE SCAN 하는 SQL ===
 +
<source lang=sql>
 +
select sql_id,object_owner,object_name from V$SQL_PLAN where
 +
operation='TABLE ACCESS' and
 +
options='FULL' and
 +
object_owner not in ('SYS','SYSTEM','DBSNMP');
 +
</source>
 +
 +
=== 딕셔너리 캐시 히트율===
 +
<source lang=sql>
 +
select sum(gets) as "Gets", sum(getmisses) as "Misses",
 +
(1-(sum(getmisses)/sum(gets)))*100 as "CACHE HIT RATIO"
 +
from gv$rowcache;
 +
 +
NOTE - CACHE HIT RATIO SHOULD BE MORE THAN 95 PERCENT.
 +
</source>
 +
 +
===데이터베이스 뮤텍스 SLEEP ===
 +
<source lang=sql>
 +
column mux format a18 heading 'Mutex Type' trunc;
 +
column loc format a32 heading 'Location' trunc;
 +
column sleeps format 9,999,999,990 heading 'Sleeps';
 +
column wt format 9,999,990.9 heading 'Wait |Time (s)';
 +
select e.mutex_type mux
 +
, e.location loc
 +
, e.sleeps - nvl(b.sleeps, 0) sleeps
 +
, (e.wait_time - nvl(b.wait_time, 0))/1000000 wt
 +
from DBA_HIST_MUTEX_SLEEP b
 +
, DBA_HIST_MUTEX_SLEEP e
 +
where b.snap_id(+) = &bid
 +
and e.snap_id = &eid
 +
and b.dbid(+) = e.dbid
 +
and b.instance_number(+) = e.instance_number
 +
and b.mutex_type(+) = e.mutex_type
 +
and b.location(+) = e.location
 +
and e.sleeps - nvl(b.sleeps, 0) > 0
 +
order by e.wait_time - nvl(b.wait_time, 0) desc;
 +
</source>
 +
 +
=== 대량 Physical read를 유발하는 쿼리 ===
 +
<source lang=sql>
 +
SELECT schema, sql_text, disk_reads, round(cpu,2) FROM
 +
(SELECT s.parsing_schema_name schema, t.sql_id, t.sql_text, t.disk_reads,
 +
t.sorts, t.cpu_time/1000000 cpu, t.rows_processed, t.elapsed_time
 +
FROM v$sqlstats t join v$sql s on(t.sql_id = s.sql_id)
 +
WHERE parsing_schema_name = 'SCOTT'
 +
ORDER BY disk_reads DESC)
 +
WHERE rownum <= 5;
 +
</source>
 +
 +
=== 대량 리두를 발생시키는 세션 조회 ===
 +
 +
<source lang=sql>
 +
set lines 2000
 +
set pages 1000
 +
col sid for 99999
 +
col name for a09
 +
col username for a14
 +
col PROGRAM for a21
 +
col MODULE for a25
 +
 +
select s.sid,sn.SERIAL#,n.name, round(value/1024/1024,2) redo_mb, sn.username,sn.status,substr (sn.program,1,21) "program", sn.type, sn.module,sn.sql_id
 +
from v$sesstat s join v$statname n on n.statistic# = s.statistic#
 +
join v$session sn on sn.sid = s.sid where n.name like 'redo size' and s.value!=0 order by
 +
redo_mb desc;
 +
 +
</source>
 +
 +
 +
=== 언두를 발생시키는 세션 조회 ===
 +
 +
<source lang=sql>
 +
select a.sid, a.serial#, a.username
 +
    , b.used_urec used_undo_record
 +
    , b.used_ublk used_undo_blocks
 +
  from v$session a
 +
    , v$transaction b
 +
where a.saddr=b.ses_addr ;
 +
 +
</source>
 +
 +
=== latch contetion을 유발하는 쿼리 ===
 +
<source lang=sql>
 +
col OBJECT_NAME for a30
 +
col owner for a12
 +
with bh_lc as
 +
(select
 +
lc.addr, lc.child#, lc.gets, lc.misses, lc.immediate_gets, lc.immediate_misses,
 +
lc.spin_gets, lc.sleeps,
 +
bh.hladdr, bh.tch tch, bh.file#, bh.dbablk, bh.class, bh.state, bh.obj
 +
from
 +
v$session_wait sw,
 +
v$latchname ld,
 +
v$latch_children lc,
 +
x$bh bh
 +
where lc.addr =sw.p1raw
 +
and sw.p2= ld.latch#
 +
and ld.name='cache buffers chains'
 +
and lower(sw.event) like '%latch%'
 +
and bh.hladdr=lc.addr
 +
)
 +
select bh_lc.hladdr, bh_lc.tch, o.owner, o.object_name, o.object_type,
 +
bh_lc.child#,
 +
bh_lc.gets, bh_lc.misses, bh_lc.immediate_gets,
 +
bh_lc.immediate_misses, spin_gets, sleeps
 +
from
 +
bh_lc, dba_objects o
 +
where bh_lc.obj = o.data_object_id(+)
 +
order by 1,2 desc;
 +
</source>
 +
 +
=== 래치 타입 과 hash value ===
 +
<source lang=sql>
 +
Set lines 160 pages 100
 +
Column event format A35
 +
Column name format A35
 +
select x.event, x.sql_hash_value,
 +
case when x.event like 'latch%' then
 +
l.name
 +
else ' '
 +
end name,
 +
x.cnt from (
 +
select substr(w.event, 1, 28) event, s.sql_hash_value, w.p2,count(*) cnt
 +
from v$session_wait w, v$session s, v$process p
 +
where s.sid=w.sid
 +
and p.addr = s.paddr
 +
and s.username is not null
 +
and w.event not like '%pipe%'
 +
and w.event not like 'SQL*%'
 +
group by substr(w.event, 1, 28), sql_hash_value,w.p2
 +
) x,
 +
v$latch l
 +
where
 +
x.p2 = l.latch#(+)
 +
order by cnt;
 +
</source>
 +
=== 커서에서 추출한 sql id에 대한 sql tuning advisor ===
 +
<source lang=sql>
 +
Create tuning task
 +
 +
set long 1000000000
 +
DECLARE
 +
l_sql_tune_task_id VARCHAR2(100);
 +
BEGIN
 +
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
 +
sql_id => 'apwfwjhgc9sk8',
 +
scope => DBMS_SQLTUNE.scope_comprehensive,
 +
time_limit => 500,
 +
task_name => 'apwfwjhgc9sk8_tuning_task_1',
 +
description => 'Tuning task for statement apwfwjhgc9sk8');
 +
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
 +
END;
 +
/
 +
 +
Execute tuning task
 +
 +
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'apwfwjhgc9sk8_tuning_task_1');
 +
 +
Generate report
 +
 +
SET LONG 10000000;
 +
SET PAGESIZE 100000000
 +
SET LINESIZE 200
 +
SELECT DBMS_SQLTUNE.report_tuning_task('apwfwjhgc9sk8_tuning_task_1') AS recommendations FROM dual;
 +
SET PAGESIZE 24
 +
</source>
 +
 +
=== SGA 타겟 advisor ===
 +
<source lang=sql>
 +
- STATISTICS_LEVEL should be TYPICAL/ALL.
 +
 +
SQL> show parameter statistics_level
 +
 +
NAME TYPE VALUE
 +
------------------------------------ -------------------------------- --------------------------
 +
statistics_level string TYPICAL
 +
 +
select * from v$sga_target_advice order by sga_size;
 +
 +
</source>
 +
 +
=== 쉐어드풀 advisor ===
 +
<source lang=sql>
 +
SELECT shared_pool_size_for_estimate "Size of Shared Pool in MB",
 +
shared_pool_size_factor "Size Factor",
 +
estd_lc_time_saved "Time Saved in sec" FROM v$shared_pool_advice;
 +
</source>
 +
 +
 +
 +
===쉐어드풀 flush를 유발하는 오브젝트 ===
 +
<source lang=sql>
 +
Set lines 160 pages 100
 +
Select * from x$ksmlru order by ksmlrnum;
 +
</source>
 +
 +
=== ADDM 리포트 생성 ===
 +
<source lang=sql>
 +
 +
cd $ORACLE_HOME/rdbms/admin
 +
 +
SQL> @addmrpt.sql
 +
 +
Specify the Begin and End Snapshot Ids
 +
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 +
Enter value for begin_snap: 1058
 +
Begin Snapshot Id specified: 1058
 +
 +
Enter value for end_snap: 1059
 +
End Snapshot Id specified: 1059
 +
 
</source>
 
</source>
  
 
출처 : https://dbaclass.com/monitor-your-db/
 
출처 : https://dbaclass.com/monitor-your-db/

2024년 7월 12일 (금) 15:45 기준 최신판

thumb_up 추천메뉴 바로가기


목차

1 연결되어 있는 OS 사용자 및 프로그램 조회[편집]

  • V$SESSION
SELECT SID
     , SERIAL#
     , OSUSER
     , SUBSTRB(USERNAME, 1, 10) AS USER_NAME
     , SUBSTRB(PROGRAM, 1, 30) AS PROGRAM_NAME
     , STATUS
     , TO_CHAR(LOGON_TIME, 'YYYY/MM/DD HH:MI') AS LOGON_TIME
  FROM V$SESSION WHERE TYPE!= ‘BACKGROUND’ AND STATUS = ‘ACTIVE’;

2 1시간 이상 유휴 상태인 세션[편집]

SELECT SID
     , SERIAL#
     , USERNAME
     , TRUNC(LAST_CALL_ET / 3600, 2) || ' HR' LAST_CALL_ET
  FROM V$SESSION 
 WHERE LAST_CALL_ET > 3600 
   AND USERNAME IS NOT NULL;

3 Active Session 중 Idle Time이 긴 작업[편집]

  • V$SESSION
  • V$PROCESS
SELECT VS.SID || ',' || VS.SERIAL# " SID"
     , VP.SPID
     , VS.MACHINE
     , VS.PROGRAM
     , VS.MODULE
     , VS.STATUS
     , TO_CHAR(VS.LOGON_TIME, 'MM/DD HH24:MI') LOGIN_TIME
     , ROUND(VS.LAST_CALL_ET / 60) "IDLE"
  FROM V$SESSION VS
     , V$PROCESS VP
 WHERE VS.STATUS = 'ACTIVE' 
   AND VS.SID NOT IN (1, 2, 3, 4, 5, 6, 7) 
   AND VS.PADDR = VP.ADDR 
 ORDER BY 8;

4 DBUser 별로 Session 정보를 조회[편집]

SELECT S.USERNAME
     , S.SID
     , S.SERIAL#
     , P.SPID
     , S.OSUSER
     , S.MACHINE
     , S.PROGRAM
     , TO_CHAR(S.LOGON_TIME, 'MM/DD HH24:MI') "LOGON_TIME"
     , ROUND(S.LAST_CALL_ET / 60) "IDLE"
  FROM V$SESSION S
     , V$PROCESS P
 WHERE S.PADDR = P.ADDR AND S.USERNAME LIKE UPPER('&DBUSER%') 
 ORDER BY 9;

5 Session별 사용 명령어[편집]

  • V$SESSION
  • V$SESSTAT
  • V$STATNAME
  • V$PROCESS
SELECT SESS.SID

         ,SESS.SERIAL#
         ,SUBSTR(SESS.USERNAME, 1, 10) "USER NAME"
         ,SUBSTR(OSUSER, 1, 11) "OS USER"
         ,SUBSTR(SESS.MACHINE, 1, 15) "MACHINE NAME"
         ,STATUS
         ,UPPER(
            DECODE(NVL(COMMAND, 0)
                  ,0, '---'
                  ,1, 'CREATE TABLE'
                  ,2, 'INSERT -'
                  ,3, 'SELECT -'
                  ,4, 'CREATE CLUST'
                  ,5, 'ALTER CLUST'
                  ,6, 'UPDATE -'
                  ,7, 'DELETE -'
                  ,8, 'DROP -'
                  ,9, 'CREATE INDEX'
                  ,10, 'DROP INDEX'
                  ,11, 'ALTER INDEX'
                  ,12, 'DROP TABLE'
                  ,13, 'CREATE SEQ'
                  ,14, 'ALTER SEQ'
                  ,15, 'ALTER TABLE'
                  ,16, 'DROP SEQ'
                  ,17, 'GRANT'
                  ,18, 'REVOKE'
                  ,19, 'CREATE SYN'
                  ,20, 'DROP SYN'
                  ,21, 'CREATE VIEW'
                  ,22, 'DROP VIEW'
                  ,23, 'VALIDATE IX'
                  ,24, 'CREATE PROC'
                  ,25, 'ALTER PROC'
                  ,26, 'LOCK TABLE'
                  ,27, 'NO OPERATION'
                  ,28, 'RENAME'
                  ,29, 'COMMENT'
                  ,30, 'AUDIT'
                  ,31, 'NOAUDIT'
                  ,32, 'CREATE DBLINK'
                  ,33, 'DROP DB LINK'
                  ,34, 'CREATE DATABASE'
                  ,35, 'ALTER DATABASE'
                  ,36, 'CREATE RBS'
                  ,37, 'ALTER RBS'
                  ,38, 'DROP RBS'
                  ,39, 'CREATE TABLESPACE'
                  ,40, 'ALTER TABLESPACE'
                  ,41, 'DROP TABLESPACE'
                  ,42, 'ALTER SESSION'
                  ,43, 'ALTER USER'
                  ,44, 'COMMIT'
                  ,45, 'ROLLBACK'
                  ,47, 'PL/SQL EXEC'
                  ,48, 'SET TRANSACTION'
                  ,49, 'SWITCH LOG'
                  ,50, 'EXPLAIN'
                  ,51, 'CREATE USER'
                  ,52, 'CREATE ROLE'
                  ,53, 'DROP USER'
                  ,54, 'DROP ROLE'
                  ,55, 'SET ROLE'
                  ,56, 'CREATE SCHEMA'
                  ,58, 'ALTER TRACING'
                  ,59, 'CREATE TRIGGER'
                  ,61, 'DROP TRIGGER'
                  ,62, 'ANALYZE TABLE'
                  ,63, 'ANALYZE INDEX'
                  ,69, 'DROP PROCEDURE'
                  ,71, 'CREATE SNAP LOG'
                  ,72, 'ALTER SNAP LOG'
                  ,73, 'DROP SNAP LOG'
                  ,74, 'CREATE SNAPSHOT'
                  ,75, 'ALTER SNAPSHOT'
                  ,76, 'DROP SNAPSHOT'
                  ,85, 'TRUNCATE TABLE'
                  ,88, 'ALTER VIEW'
                  ,91, 'CREATE FUNCTION'
                  ,92, 'ALTER FUNCTION'
                  ,93, 'DROP FUNCTION'
                  ,94, 'CREATE PACKAGE'
                  ,95, 'ALTER PACKAGE'
                  ,96, 'DROP PACKAGE'
                  ,46, 'SAVEPOINT'
                  )
          )
            COMMAND
         ,SESS.PROCESS "C.PROC"
         ,PROC.SPID "S.PROC"
         ,TO_CHAR(SESS.LOGON_TIME, 'YYYY-MM-DD HH24:MI')

 FROM V$SESSION SESS
    , V$SESSTAT STAT
    , V$STATNAME NAME
    , V$PROCESS PROC
WHERE SESS.SID = STAT.SID AND STAT.STATISTIC# = NAME.STATISTIC# AND SESS.USERNAME IS NOT NULL AND NAME.NAME = 'RECURSIVE CALLS' AND SESS.PADDR = PROC.ADDR ORDER BY 3, 1, 2;

6 사용자 session 2시간 이상 idle 상태가 지속되는 session kill[편집]

  • V$SESSION
  • V$PROCESS
SET PAGESIZE 0 SPOOL KILLIDLE3.SQL

SELECT DISTINCT '!KILL -9 ' || B.SPID, 'ALTER SYSTEM KILL SESSION '''|| A.SID || ',' || A.SERIAL# || ''';' 
  FROM V$SESSION A
     , V$PROCESS B
 WHERE A.PADDR IN (SELECT S.PADDR
                     FROM V$SESSION S
                    WHERE STATUS = 'INACTIVE'
                    GROUP BY  S.PADDR
                   HAVING MIN(ROUND(LAST_CALL_ET / 60)) > 120)
   AND A.PADDR = B.ADDR 
   AND A.STATUS = 'INACTIVE';

SPOOL OFF

7 Oracle Process의 정보[편집]

  • V$SESSION
  • V$PROCESS
  • SYS.V_$SESS_IO
SELECT S.STATUS "STATUS"
         ,S.SERIAL# "SERIAL#"
         ,S.TYPE "TYPE"
         ,S.USERNAME "DB USER"
         ,S.OSUSER "CLIENT USER"
         ,S.SERVER "SERVER"
         ,S.MACHINE "MACHINE"
         ,S.MODULE "MODULE"
         ,S.TERMINAL "TERMINAL"
         ,S.PROGRAM "PROGRAM"
         ,P.PROGRAM "O.S. PROGRAM"
         ,S.LOGON_TIME "CONNECT TIME"
         ,LOCKWAIT "LOCK WAIT"
         ,SI.PHYSICAL_READS "PHYSICAL READS"
         ,SI.BLOCK_GETS "BLOCK GETS"
         ,SI.CONSISTENT_GETS "CONSISTENT GETS"
         ,SI.BLOCK_CHANGES "BLOCK CHANGES"
         ,SI.CONSISTENT_CHANGES "CONSISTENT CHANGES"
         ,S.PROCESS "PROCESS"
         ,P.SPID
         ,P.PID
         ,S.SERIAL#
         ,SI.SID
         ,S.SQL_ADDRESS "ADDRESS"
         ,S.SQL_HASH_VALUE "SQL HASH"
         ,S.ACTION
  FROM V$SESSION S
     , V$PROCESS P
     , SYS.V_$SESS_IO SI
WHERE S.PADDR = P.ADDR(+)
  AND SI.SID(+) = S.SID 
  AND S.USERNAME IS NOT NULL 
  AND NVL(S.OSUSER, 'X') <> 'SYSTEM' 
  AND S.TYPE <> 'BACKGROUND' 
ORDER BY 3;

8 오브젝트에 접속되어 있는 프로그램 조회[편집]

  • V$SESSION
  • V$ACCESS
SELECT SUBSTR(B.OBJECT, 1, 15) AS OBJECT, SUBSTR(A.PROGRAM, 1, 15) AS PROGRAM, COUNT(*) AS CNT 
  FROM V$SESSION A
      ,V$ACCESS B
WHERE A.SID = B.SID 
  AND B.OWNER NOT IN ('SYS') 
  AND A.TYPE!= 'BACKGROUND' 
  AND B.OBJECT LIKE UPPER('&OBJECT_NAME') || '%' GROUP BY B.OBJECT, SUBSTR(A.PROGRAM, 1, 15);

9 롤백 세그먼트 경합 조회[편집]

  • V$ROLLSTAT
  • V$ROLLNAME
SELECT NAME T0
     , GETS T1         ,WAITS T2
     , TO_CHAR(TRUNC(WAITS / GETS * 100, 2), 099.99) || '%' T3
     , TO_CHAR(ROUND(RSSIZE / 1024)) T4
     , SHRINKS T5
     , EXTENDS T6
  FROM V$ROLLSTAT
     , V$ROLLNAME
WHERE V$ROLLSTAT.USN = V$ROLLNAME.USN;

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

  • V$SESSTAT
  • V$STATNAME
  • V$SESSION
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;

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

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

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

  • V$ROLLNAME
  • V$ROLLSTAT
  • V$SESSION
  • V$SQLTEXT
  • V$TRANSACTION
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;

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

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

14 System 테이블스페이스에 비시스템 세그먼트 조회[편집]

  • DBA_SEGMENTS
SELECT OWNER
     , SEGMENT_NAME
     , SEGMENT_TYPE
     , TABLESPACE_NAME
  FROM DBA_SEGMENTS 
 WHERE OWNER NOT IN ('SYS', 'SYSTEM') 
   AND TABLESPACE_NAME = 'SYSTEM';

15 인덱스의 Delete Space 조회[편집]

  • INDEX_STATS
--Delete Space% 값이 20% 가 넘으면, 그 인덱스는 다시 작성하는 것이 좋다.
SELECT NAME
     , LF_ROWS
     , DEL_LF_ROWS
     , (DEL_LF_ROWS / LF_ROWS) * 100 AS "DELETE SPACE%"
  FROM INDEX_STATS WHERE NAME = UPPER('&INDEX_NAME');

16 딕셔너리/뷰 정보 조회[편집]

  • DICTIONARY
  • DICT_COLUMNS
SELECT A.TABLE_NAME
     , B.COLUMN_NAME
  FROM DICTIONARY A
     , DICT_COLUMNS B
 WHERE A.TABLE_NAME = B.TABLE_NAME;

17 유저의 모든 세션 트레이스[편집]

--- CREATE THE BELOW TRIGGER TO ENABLE TRACE ALL SESSION OF USER ( SCOTT)
CREATE OR REPLACE TRIGGER USER_TRACE_TRG
AFTER LOGON ON DATABASE
BEGIN
    IF USER = 'SCOTT'
  THEN
    execute immediate 'alter session set events ''10046 trace name context forever, level 12''';
  END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/

18 커셔 내부 SQL의 상세내용 보기[편집]

select module,parsing_schema_name,inst_id,sql_id,plan_hash_value,child_number,sql_fulltext,
to_char(last_active_time,'DD/MM/YY HH24:MI:SS' ),sql_plan_baseline,executions,
elapsed_time/executions/1000/1000,rows_processed from gv$sql
where sql_id in ('&sql_id');

19 커셔 내부 sql쿼리 Flush[편집]

-- First get the address, hash_value of the sql_id

select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like '5qd8a442c328k';

ADDRESS          HASH_VALUE
---------------  ------------
C000007067F39FF0  4000666812

-- Now flush the query

SQL> exec DBMS_SHARED_POOL.PURGE ('C000007067F39FF0, 4000666812', 'C');

Note : For RAC, same need to be executed on all the nodes .

20 sql_id 의 바인드변수 값 조회[편집]

SELECT
sql_id,
b. LAST_CAPTURED,
t.sql_text sql_text,
b.HASH_VALUE,
b.name bind_name,
b.value_string bind_value
FROM
gv$sql t
JOIN
gv$sql_bind_capture b using (sql_id)
WHERE
b.value_string is not null
AND
sql_id='&sqlid'
/

21 현재 실행 중인 세션의 SQL 조회[편집]

select sesion.sid,
sesion.username,
optimizer_mode,
hash_value,
address,
cpu_time,
elapsed_time,
sql_text
from v$sqlarea sqlarea, v$session sesion
where sesion.sql_hash_value = sqlarea.hash_value
and sesion.sql_address = sqlarea.address
and sesion.username is not null;

22 ACTIVE 세션 정보 조회[편집]

set echo off
set linesize 95
set head on
set feedback on
col sid head "Sid" form 9999 trunc
col serial# form 99999 trunc head "Ser#"
col username form a8 trunc
col osuser form a7 trunc
col machine form a20 trunc head "Client|Machine"
col program form a15 trunc head "Client|Program"
col login form a11
col "last call" form 9999999 trunc head "Last Call|In Secs"
col status form a6 trunc

select sid,serial#,substr(username,1,10) username,substr(osuser,1,10) osuser
     , substr(program||module,1,15) program,substr(machine,1,22) machine
     , to_char(logon_time,'ddMon hh24:mi') login
     , last_call_et "last call",status
  from gv$session 
 where status='ACTIVE'
order by 1
/

23 DB 대기이벤트 조회[편집]

select a.sid,substr(b.username,1,10) username,substr(b.osuser,1,10) osuser
     , substr(b.program||b.module,1,15) program
     , substr(b.machine,1,22) machine
     , a.event
     , a.p1,b.sql_hash_value
  from v$session_wait a
     , V$session b
 where b.sid=a.sid
   and a.event not in('SQL*Net message from client','SQL*Net message to client',
'smon timer','pmon timer')
   and username is not null
 order by 6
/

24 세션의 템프 사용량 조회[편집]

SELECT b.tablespace
     , ROUND(((b.blocks*p.value)/1024/1024),2)||'M' AS temp_size
     , a.inst_id as Instance
     , a.sid||','||a.serial# AS sid_serial
     , NVL(a.username, '(oracle)') AS username
     , a.program , a.status , a.sql_id
  FROM gv$session a
     , gv$sort_usage b
     , gv$parameter p
WHERE p.name = 'db_block_size'
  AND a.saddr = b.session_addr
  AND a.inst_id=b.inst_id
  AND a.inst_id=p.inst_id
ORDER BY temp_size desc
/

25 현재 트랜잭션 조회[편집]

col name format a10
col username format a8
col osuser format a8
col start_time format a17
col status format a12
tti 'Active transactions'

select s.sid,username,t.start_time, r.name, t.used_ublk "USED BLKS",
decode(t.space, 'YES', 'SPACE TX',
decode(t.recursive, 'YES', 'RECURSIVE TX',
decode(t.noundo, 'YES', 'NO UNDO TX', t.status)
)) status
from sys.v_$transaction t, sys.v_$rollname r, sys.v_$session s
where t.xidusn = r.usn
and t.ses_addr = s.saddr
/

26 리스너 트레이스[편집]

- Set to the listener you want to trace

LSNRCTL> set cur LISTENER_TEST

-- Enable Trace:

LSNRCTL> set trc_level ADMIN
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_TEST)))
LISTENER_TEST parameter "trc_level" set to admin
The command completed successfully

27 세션의 PGA 사용량[편집]

set lines 2000
SELECT SID, b.NAME, ROUND(a.VALUE/(1024*1024),2) MB FROM
v$sesstat a, v$statname b
WHERE (NAME LIKE '%session uga memory%' OR NAME LIKE '%session pga memory%')
AND a.statistic# = b.statistic# order by ROUND(a.VALUE/(1024*1024),2) desc

28 현재 SGA 사용량[편집]

select round(used.bytes /1024/1024 ,2) used_mb
, round(free.bytes /1024/1024 ,2) free_mb
, round(tot.bytes /1024/1024 ,2) total_mb
from (select sum(bytes) bytes
from v$sgastat
where name != 'free memory') used
, (select sum(bytes) bytes
from v$sgastat
where name = 'free memory') free
, (select sum(bytes) bytes
from v$sgastat) tot

29 physical read가 높은 세그먼트 조회[편집]

set pagesize 200
setlinesize 120
col segment_name format a20
col owner format a10
select segment_name,object_type,total_physical_reads
from ( select owner||'.'||object_name as segment_name,object_type,
value as total_physical_reads
from v$segment_statistics
where statistic_name in ('physical reads')
order by total_physical_reads desc)
where rownum <=10;

30 템프 파일별 I/O 사용량[편집]

SELECT SUBSTR(t.name,1,50) AS file_name,
f.phyblkrd AS blocks_read,
f.phyblkwrt AS blocks_written,
f.phyblkrd + f.phyblkwrt AS total_io
FROM v$tempstat f,v$tempfile t
WHERE t.file# = f.file#
ORDER BY f.phyblkrd + f.phyblkwrt DESC;

 

select * from (SELECT u.tablespace, s.username, s.sid, s.serial#, s.logon_time, program, u.extents, ((u.blocks*8)/1024) as MB,
i.inst_id,i.host_name
FROM gv$session s, gv$sort_usage u ,gv$instance i
WHERE s.saddr=u.session_addr and u.inst_id=i.inst_id order by MB DESC) a where rownum<10;

31 [ash] TOP 쿼리[편집]

Query to get list of top running sqls in PAST between sysdate-1 to sysdate-23/34 . You can change accordingly

SELECT active_session_history.user_id,
dba_users.username,
sqlarea.sql_text,
SUM(active_session_history.wait_time +
active_session_history.time_waited)/1000000 ttl_wait_time_in_seconds
FROM v$active_session_history active_session_history,
v$sqlarea sqlarea,
dba_users
WHERE active_session_history.sample_time BETWEEN SYSDATE - 1 AND SYSDATE-23/24
AND active_session_history.sql_id = sqlarea.sql_id
AND active_session_history.user_id = dba_users.user_id
and dba_users.username not in ('SYS','DBSNMP')
GROUP BY active_session_history.user_id,sqlarea.sql_text, dba_users.username
ORDER BY 4 DESC

32 [ash] 블로킹 세션 찾기[편집]

Query will list the blocking session details between SYSDATE - 1 AND SYSDATE-23/24 ( PAST)

set pagesize 50
set linesize 120
col sql_id format a15
col inst_id format '9'
col sql_text format a50
col module format a10
col blocker_ses format '999999'
col blocker_ser format '999999'
SELECT distinct
a.sql_id ,
a.inst_id,
a.blocking_session blocker_ses,
a.blocking_session_serial# blocker_ser,
a.user_id,
s.sql_text,
a.module,a.sample_time
FROM GV$ACTIVE_SESSION_HISTORY a,
gv$sql s
where a.sql_id=s.sql_id
and blocking_session is not null
and a.user_id <> 0 -- exclude SYS user
and a.sample_time BETWEEN SYSDATE - 1 AND SYSDATE-23/24

33 cpu 과사용 세션[편집]

col program form a30 heading "Program"
col CPUMins form 99990 heading "CPU in Mins"
select rownum as rank, a.*
from (
SELECT v.sid, program, v.value / (100 * 60) CPUMins
FROM v$statname s , v$sesstat v, v$session sess
WHERE s.name = 'CPU used by this session'
and sess.sid = v.sid
and v.statistic#=s.statistic#
and v.value>0
ORDER BY v.value DESC) a
where rownum < 11;

34 라이브러리 캐시 락을 잡는 세션[편집]

For standalone db:

select sid Waiter, p1raw,
substr(rawtohex(p1),1,30) Handle,
substr(rawtohex(p2),1,30) Pin_addr
from v$session_wait where wait_time=0 and event like '%library cache%';

For RAC DB:

select a.sid Waiter,b.SERIAL#,a.event,a.p1raw,
substr(rawtohex(a.p1),1,30) Handle,
substr(rawtohex(a.p2),1,30) Pin_addr
from v$session_wait a,v$session b where a.sid=b.sid
and a.wait_time=0 and a.event like 'library cache%';

or

set lines 152
col sid for a9999999999999
col name for a40
select a.sid,b.name,a.value,b.class
from gv$sesstat a , gv$statname b
where a.statistic#=b.statistic#
and name like '%library cache%';

35 라이브러리 캐시에 의해 락이 발생된 세션[편집]

select to_char(SESSION_ID,'999') sid ,
substr(LOCK_TYPE,1,30) Type,
substr(lock_id1,1,23) Object_Name,
substr(mode_held,1,4) HELD, substr(mode_requested,1,4) REQ,
lock_id2 Lock_addr
from dba_lock_internal
where
mode_requested'None'
and mode_requestedmode_held
and session_id in ( select sid
from v$session_wait where wait_time=0
and event like '%library cache%') ;

36 유저가 엑세스 하는 오브젝트 조회[편집]

set lines 299
column object format a30
column owner format a10
select * from gv$access where owner='&OWNER' and object='&object_name' and
/


37 FULL TABLE SCAN 하는 SQL[편집]

select sql_id,object_owner,object_name from V$SQL_PLAN where
operation='TABLE ACCESS' and
options='FULL' and
object_owner not in ('SYS','SYSTEM','DBSNMP');

38 딕셔너리 캐시 히트율[편집]

select sum(gets) as "Gets", sum(getmisses) as "Misses",
(1-(sum(getmisses)/sum(gets)))*100 as "CACHE HIT RATIO"
from gv$rowcache;

NOTE - CACHE HIT RATIO SHOULD BE MORE THAN 95 PERCENT.

39 데이터베이스 뮤텍스 SLEEP[편집]

column mux format a18 heading 'Mutex Type' trunc;
column loc format a32 heading 'Location' trunc;
column sleeps format 9,999,999,990 heading 'Sleeps';
column wt format 9,999,990.9 heading 'Wait |Time (s)';
select e.mutex_type mux
, e.location loc
, e.sleeps - nvl(b.sleeps, 0) sleeps
, (e.wait_time - nvl(b.wait_time, 0))/1000000 wt
from DBA_HIST_MUTEX_SLEEP b
, DBA_HIST_MUTEX_SLEEP e
where b.snap_id(+) = &bid
and e.snap_id = &eid
and b.dbid(+) = e.dbid
and b.instance_number(+) = e.instance_number
and b.mutex_type(+) = e.mutex_type
and b.location(+) = e.location
and e.sleeps - nvl(b.sleeps, 0) > 0
order by e.wait_time - nvl(b.wait_time, 0) desc;

40 대량 Physical read를 유발하는 쿼리[편집]

SELECT schema, sql_text, disk_reads, round(cpu,2) FROM
(SELECT s.parsing_schema_name schema, t.sql_id, t.sql_text, t.disk_reads,
t.sorts, t.cpu_time/1000000 cpu, t.rows_processed, t.elapsed_time
FROM v$sqlstats t join v$sql s on(t.sql_id = s.sql_id)
WHERE parsing_schema_name = 'SCOTT'
ORDER BY disk_reads DESC)
WHERE rownum <= 5;

41 대량 리두를 발생시키는 세션 조회[편집]

set lines 2000
set pages 1000
col sid for 99999
col name for a09
col username for a14
col PROGRAM for a21
col MODULE for a25

select s.sid,sn.SERIAL#,n.name, round(value/1024/1024,2) redo_mb, sn.username,sn.status,substr (sn.program,1,21) "program", sn.type, sn.module,sn.sql_id
from v$sesstat s join v$statname n on n.statistic# = s.statistic#
join v$session sn on sn.sid = s.sid where n.name like 'redo size' and s.value!=0 order by
redo_mb desc;


42 언두를 발생시키는 세션 조회[편집]

select a.sid, a.serial#, a.username
     , b.used_urec used_undo_record
     , b.used_ublk used_undo_blocks
  from v$session a
     , v$transaction b
 where a.saddr=b.ses_addr ;

43 latch contetion을 유발하는 쿼리[편집]

col OBJECT_NAME for a30
col owner for a12
with bh_lc as
(select
lc.addr, lc.child#, lc.gets, lc.misses, lc.immediate_gets, lc.immediate_misses,
lc.spin_gets, lc.sleeps,
bh.hladdr, bh.tch tch, bh.file#, bh.dbablk, bh.class, bh.state, bh.obj
from
v$session_wait sw,
v$latchname ld,
v$latch_children lc,
x$bh bh
where lc.addr =sw.p1raw
and sw.p2= ld.latch#
and ld.name='cache buffers chains'
and lower(sw.event) like '%latch%'
and bh.hladdr=lc.addr
)
select bh_lc.hladdr, bh_lc.tch, o.owner, o.object_name, o.object_type,
bh_lc.child#,
bh_lc.gets, bh_lc.misses, bh_lc.immediate_gets,
bh_lc.immediate_misses, spin_gets, sleeps
from
bh_lc, dba_objects o
where bh_lc.obj = o.data_object_id(+)
order by 1,2 desc;

44 래치 타입 과 hash value[편집]

Set lines 160 pages 100
Column event format A35
Column name format A35
select x.event, x.sql_hash_value,
case when x.event like 'latch%' then
l.name
else ' '
end name,
x.cnt from (
select substr(w.event, 1, 28) event, s.sql_hash_value, w.p2,count(*) cnt
from v$session_wait w, v$session s, v$process p
where s.sid=w.sid
and p.addr = s.paddr
and s.username is not null
and w.event not like '%pipe%'
and w.event not like 'SQL*%'
group by substr(w.event, 1, 28), sql_hash_value,w.p2
) x,
v$latch l
where
x.p2 = l.latch#(+)
order by cnt;

45 커서에서 추출한 sql id에 대한 sql tuning advisor[편집]

Create tuning task

set long 1000000000
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_id => 'apwfwjhgc9sk8',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 500,
task_name => 'apwfwjhgc9sk8_tuning_task_1',
description => 'Tuning task for statement apwfwjhgc9sk8');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

Execute tuning task

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'apwfwjhgc9sk8_tuning_task_1');

Generate report

SET LONG 10000000;
SET PAGESIZE 100000000
SET LINESIZE 200
SELECT DBMS_SQLTUNE.report_tuning_task('apwfwjhgc9sk8_tuning_task_1') AS recommendations FROM dual;
SET PAGESIZE 24

46 SGA 타겟 advisor[편집]

- STATISTICS_LEVEL should be TYPICAL/ALL.

SQL> show parameter statistics_level

NAME TYPE VALUE
------------------------------------ -------------------------------- --------------------------
statistics_level string TYPICAL

select * from v$sga_target_advice order by sga_size;

47 쉐어드풀 advisor[편집]

SELECT shared_pool_size_for_estimate "Size of Shared Pool in MB",
shared_pool_size_factor "Size Factor",
estd_lc_time_saved "Time Saved in sec" FROM v$shared_pool_advice;


48 쉐어드풀 flush를 유발하는 오브젝트[편집]

Set lines 160 pages 100
Select * from x$ksmlru order by ksmlrnum;

49 ADDM 리포트 생성[편집]

cd $ORACLE_HOME/rdbms/admin

SQL> @addmrpt.sql

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1058
Begin Snapshot Id specified: 1058

Enter value for end_snap: 1059
End Snapshot Id specified: 1059

출처 : https://dbaclass.com/monitor-your-db/

원본 주소 "http://dbcafe.co.kr/wiki/index.php?title=모니터링_SQL&oldid=12262"