템프테이블 모니터링

DB CAFE

추천메뉴 바로가기

DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
튜닝 { 오라클 튜닝 목록 }
모델링 { 데이터 모델링 가이드 }

Temp Segment를 과도하게 사용하는 쿼리를 수집시 아래 스크립트를 이용해서 10분정도 주기로 수집
select * from dba_hist_sql_plan ; 조회하시면 자세하게 확인 가능

test:/oracle] cat /oracle/twkim/monitor/dbms_temp_log.sh
---------------------------------------------------------------------
#!/bin/ksh
export ORACLE_BASE=/oracle
export ORACLE_HOME=$ORACLE_BASE/product/102/db
export ORACLE_SID=test
export NLS_LANG=American_America.KO16KSC5601
export TNS_ADMIN=$ORACLE_HOME/network/admin
export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
$ORACLE_HOME/bin/sqlplus -s test/test#ved1 << EOF
spool /oracle/twkim/monitor/dbms_tempspace.log
start /oracle/twkim/monitor/dbms_temp_log.sql
spool off
exit

test:/oracle] cat /oracle/twkim/monitor/dbms_temp_log.sql
insert
into ktw$temp_usage
select *
from ( with blocks AS (
select value block_size
from v$parameter
where name = 'db_block_size' )
SELECT /*+ RULE */
sysdate rdate,
s.inst_id,
s.sql_id,
s.username,
u.tablespace,
u.contents,
s.program,
u.extents,
u.blocks,
u.blocks * b.block_size/1024/1024 tsize,
sum(u.blocks * b.block_size/1024/1024) over (partition by null ) total,
s.MACHINE
FROM gv$session s,
gv$sort_usage u,
blocks b
WHERE s.saddr=u.session_addr
AND s.inst_id = u.inst_id
order by 9 desc )
where tsize > 2000
/
commit;

원본 주소 "http://dbcafe.co.kr/wiki/index.php?title=템프테이블_모니터링&oldid=7126"

Oracle