다른 명령
연결되어 있는 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’;
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;
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;
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;
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;
사용자 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
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;
오브젝트에 접속되어 있는 프로그램 조회
- 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);
롤백 세그먼트 경합 조회
- 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;
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;
Disk Read 가 많은 SQL문 찾기
- V$SQLAREA
SELECT DISK_READS, SQL_TEXT FROM V$SQLAREA WHERE DISK_READS > 100 ORDER BY DISK_READS DESC;
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;
오래도록 수행되는 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;
System 테이블스페이스에 비시스템 세그먼트 조회
- DBA_SEGMENTS
SELECT OWNER , SEGMENT_NAME , SEGMENT_TYPE , TABLESPACE_NAME FROM DBA_SEGMENTS WHERE OWNER NOT IN ('SYS', 'SYSTEM') AND TABLESPACE_NAME = 'SYSTEM';
인덱스의 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');
딕셔너리/뷰 정보 조회
- DICTIONARY
- DICT_COLUMNS
SELECT A.TABLE_NAME , B.COLUMN_NAME FROM DICTIONARY A , DICT_COLUMNS B WHERE A.TABLE_NAME = B.TABLE_NAME;
유저의 모든 세션 트레이스
--- 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; /
커셔 내부 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');
커셔 내부 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 .
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' /
현재 실행 중인 세션의 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;
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 /
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 /
세션의 템프 사용량 조회
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 /
현재 트랜잭션 조회
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 /
리스너 트레이스
- 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
세션의 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
현재 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
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;
템프 파일별 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;
[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
[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
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;
라이브러리 캐시 락을 잡는 세션
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%';
라이브러리 캐시에 의해 락이 발생된 세션
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%') ;
유저가 엑세스 하는 오브젝트 조회
set lines 299 column object format a30 column owner format a10 select * from gv$access where owner='&OWNER' and object='&object_name' and /
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');
딕셔너리 캐시 히트율
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.
데이터베이스 뮤텍스 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;
대량 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;
대량 리두를 발생시키는 세션 조회
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;
언두를 발생시키는 세션 조회
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 ;
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;
래치 타입 과 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;
커서에서 추출한 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
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;
쉐어드풀 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;
쉐어드풀 flush를 유발하는 오브젝트
Set lines 160 pages 100 Select * from x$ksmlru order by ksmlrnum;
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