Active Session History 정보 검색 (by kwlee55) [2012.12.24]
V$ACTIVE_SESSION_HISTORY 검색
- SGA에 저장되어 있는 ASH 정보를 보여준다.
- 동일 세션은 여러 행으로 보여질 수 있으며, 가장 최근 세션 수행 정보를 보기 위해서는 가장 마지막 샘플을 검색해야 한다.
- MMNL 백그라운드 프로세스에 의해 디스크로 저장된다.
| 컬럼구성 |
|---|
DBA_HIST_ACTIVE_SESS_HISTORY 검색
- 디스크에 저장된 ASH 정보를 보여준다.
- V$ACTIVE_SESSION_HISTORY 동적뷰 구성 컬럼과 동일
- 단, EVENT# 컬럼 제외
- SNAP_ID, DBID, INSTANCE_NUMBER는 추가
- sql_stat4.sh
- input으로 넣은 스냅샷 구간에서 보고서를 생성
- 보고서 생성 구간의 SQL 정보 및 발생 대기 이벤트 분석을 통해 문제 SQL를 파악할 수 있다.
- 스크립트내 베이스 테이블 : DBA_HIST_SQLSTAT, ( V$ACTIVE_SESSION_HISTORY UNION ALL DBA_HIST_ACTIVE_SESS_HISTORY ), DBA_HIST_SQLTEXT, DBA_HIST_SYS_TIME_MODEL
수행 테스트
SYS@TEST >
select min(snap_id) begin_snap_id, max(snap_id) as end_snap_id
from dba_hist_snapshot 2
where begin_interval_time >= TO_DATE ('20121224', 'YYYYMMDD')
and end_interval_time <= TO_DATE ('20121224235959', 'YYYYMMDDHH24MISS');
BEGIN_SNAP_ID END_SNAP_ID
------------- -----------
778 791
[test(oracle):/oracle/product/11.1.0> sql_stat4.sh 1 778 780
sql_stat4.sh: line 1: !/usr/bin/ksh: No such file or directory
Select Order Type
1) ELP/EXE
2) CPU/EXE
3) BUFF/EXE
4) DISK/EXE
5) ROWS/EXE
6) ELP%
7) CPU%
8) BUFF%
9) DISK%
10) EXE%
11) CWT%
12) WAIT
\n
1
....
====================================================================================================================================
SQL_ID: 7yff8d2xqynmv MODULE: oracle@test PLAN_HASH_VALUE: 2236492967
ELP: 1.28 CPU: 1.27
WAIT: 0.01 EXE: 1.00
BUFF: 57,874.00 DISK: 57,858.00
ROWS: 0.00 CWT: 0.00
ELP/EXE: 1.28 CPU/EXE: 1.27
BUFF/EXE: 57,874.00 DISK/EXE: 57,858.00
ROWS/EXE: 0.00
ELP%: 0.47 CPU%: 0.80
BUFF%: 1.43 DISK%: 1.45
EXE%: 0.01 CWT%: 0.00
------------------------------------------------------------------------------------------------------------------------------------
EVNET:
CPU + Wait for CPU [EVT/TOT%] : 1.02 [EVT/SQL%] : 100.00
------------------------------------------------------------------------------------------------------------------------------------
SQL_TEXT:
SELECT "USERID","UPDATEDTTM" FROM "TEST"."V_TEST" "V_TEST" WHERE "UPDATEDTTM">='20121224
021502' AND "UPDATEDTTM"<='20121224021602'
....
sql_stat4.sh
!/usr/bin/ksh
## ######################################################################### ##
## # FILE NAME : SQL_Stat4.sh # ##
## # Written by Kim Jee Han # ##
## ######################################################################### ##
if [ "$#" -lt 3 ]
then
echo "Usage : SQL_Stat4.sh Inst_num Begin_snap End_snap"
exit
fi
inst_num=$1
bid=$2
eid=$3
echo "Select Order Type"
echo " "
echo " 1) ELP/EXE "
echo " 2) CPU/EXE "
echo " 3) BUFF/EXE "
echo " 4) DISK/EXE "
echo " 5) ROWS/EXE "
echo " 6) ELP% "
echo " 7) CPU% "
echo " 8) BUFF% "
echo " 9) DISK%"
echo " 10) EXE% "
echo " 11) CWT% "
echo " 12) WAIT "
echo "\n"
read choice
case $choice in
1) export ORD_TYPE="ELP/EXE";;
2) export ORD_TYPE="CPU/EXE";;
3) export ORD_TYPE="BUFF/EXE";;
4) export ORD_TYPE="DISK/EXE";;
5) export ORD_TYPE="ROWS/EXE";;
6) export ORD_TYPE="ELP%";;
7) export ORD_TYPE="CPU%";;
8) export ORD_TYPE="BUFF%";;
9) export ORD_TYPE="DISK%";;
10) export ORD_TYPE="EXE%";;
11) export ORD_TYPE="CWT%";;
12) export ORD_TYPE="WAIT";;
*) export ORD_TYPE="ELP/EXE";;
esac
sqlplus -s '/as sysdba' << EOF
variable dbtime number;
variable inst_num number;
variable bid number;
variable eid number;
begin
:inst_num :=$inst_num;
:bid := $bid;
:eid := $eid;
end;
/
column database_id heading "DB ID" new_value database_id format 999999999999
select dbid database_id from v\$database
/
variable dbid number;
begin
:dbid := &database_id;
end;
/
column db_time heading "DB TIME" new_value db_time format 9999999990.99
column db_cpu heading "DB CPU" new_value db_cpu format 9999999990.99
select db_cpu db_cpu, db_time db_time
from (
SELECT a.DBID, a.INST
,(e_db_cpu - b_db_cpu) / 1000000 DB_CPU
,(e_db_time - b_db_time) / 1000000 DB_TIME
FROM (
select e.dbid, e.instance_number inst
,sum(decode(b.stat_name, 'DB time', b.value)) b_db_time
,sum(decode(b.stat_name, 'DB CPU', b.value)) b_db_cpu
,sum(decode(e.stat_name, 'DB time', e.value)) e_db_time
,sum(decode(e.stat_name, 'DB CPU', e.value)) e_db_cpu
from DBA_HIST_SYS_TIME_MODEL b
,DBA_HIST_SYS_TIME_MODEL e
WHERE b.snap_id = :bid
AND e.snap_id = :eid
AND b.dbid = :dbid
AND e.dbid = :dbid
AND b.instance_number = :inst_num
AND e.instance_number = :inst_num
AND b.stat_id = e.stat_id
AND b.stat_name in ( 'DB time','DB CPU')
group by e.dbid, e.instance_number
) a
)
/
variable dbtime number;
variable dbcpu number;
begin
:dbtime := &db_time;
:dbcpu := &db_cpu;
end;
/
column b_time new_value b_time
column e_time new_value e_time
select to_char(BEGIN_INTERVAL_TIME,'YYYYMMDDHH24MISS') b_time ,to_char(END_INTERVAL_TIME,'YYYYMMDDHH24MISS') e_time
from dba_hist_snapshot
where snap_id = :eid
and instance_number = :inst_num
and dbid = :dbid
/
variable b_time varchar2(14);
variable e_time varchar2(14);
begin
:b_time := &b_time;
:e_time := &e_time;
end;
/
set pages 500 lines 150
set long 10000000
set heading off
spool SQL_Stat4_${inst_num}_${bid}_${eid}_$choice.txt
select lpad('=',132,'='),
'SQL_ID: '||lpad(SQL_ID,13,' '),
'MODULE: '||MODULE,
'PLAN_HASH_VALUE: '||HASH,
'ELP: ' ||lpad(to_char("ELP",'999,999,999,990.99'),26,' ') ||' '||
'CPU: ' ||lpad(to_char("CPU",'999,999,999,990.99'),26,' ') ||CHR(10)||
'WAIT: '||lpad(to_char("WAIT",'999,999,999,990.99'),25,' ') ||' '||
'EXE: ' ||lpad(to_char(EXE,'999,999,999,990.99'),26,' ') ||CHR(10)||
'BUFF: '||lpad(to_char("BUFF",'999,999,999,990.99'),25,' ') ||' '||
'DISK: '||lpad(to_char(DISK,'999,999,999,990.99'),25,' ') ||CHR(10)||
'ROWS: '||lpad(to_char("ROWS",'999,999,999,990.99'),25,' ') ||' '||
'CWT: ' ||lpad(to_char(CWT,'999,999,999,990.99'),26,' ') ||CHR(10)||
'ELP/EXE: ' ||lpad(to_char("ELP/EXE",'999,999,999,990.99'),22,' ') ||' '||
'CPU/EXE: ' ||lpad(to_char("CPU/EXE",'999,999,999,990.99'),22,' ') ||CHR(10)||
'BUFF/EXE: '||lpad(to_char("BUFF/EXE",'999,999,999,990.99'),21,' ') ||' '||
'DISK/EXE: '||lpad(to_char("DISK/EXE",'999,999,999,990.99'),21,' ') ||CHR(10)||
'ROWS/EXE: '||lpad(to_char("ROWS/EXE",'999,999,999,990.99'),21,' ') ||CHR(10)||
'ELP%: ' ||lpad(to_char("ELP%",'990.99'),25,' ') ||' '||
'CPU%: ' ||lpad(to_char("CPU%",'990.99'),25,' ') ||CHR(10)||
'BUFF%: '||lpad(to_char("BUFF%",'990.99'),24,' ') ||' '||
'DISK%: '||lpad(to_char("DISK%",'990.99'),24,' ') ||CHR(10)||
'EXE%: '||lpad(to_char("EXE%",'990.99'),25,' ') ||' '||
'CWT%: ' ||lpad(to_char("CWT%",'990.99'),25,' ') ||CHR(10)||lpad('-',132,'-')||CHR(10),
'EVNET: '||CHR(10)||event ||CHR(10)||lpad('-',132,'-')||CHR(10),
'SQL_TEXT: '||CHR(10)||SQLTEXT,
CHR(10)||lpad('=',132,'=')||CHR(10)
from (
select sqt.sql_id as SQL_ID,
substr(sqt.module,1,15) as MODULE,
rpad(to_char(sqt.hash),20,' ') as hash,
nvl((sqt.cput/1000000), to_number(null)) as "CPU",
nvl((sqt.elap/1000000),to_number(null)) - nvl((sqt.cput/1000000), to_number(null)) as "WAIT",
sqt.exec as "EXE",
sqt.bget as "BUFF",
sqt.dskr as "DISK",
sqt.rowp as "ROWS",
(sqt.clwait / 1000000) as "CWT",
rpad(round(nvl((sqt.elap/1000000),to_number(null)),2),10,' ') as "ELP",
lpad(round(decode(sqt.exec, 0, to_number(null),(sqt.elap / sqt.exec / 1000000)),3),15,' ') as "ELP/EXE",
decode(sqt.exec, 0, to_number(null),(sqt.cput / sqt.exec / 1000000)) as "CPU/EXE",
decode(sqt.exec, 0, to_number(null),(sqt.bget / sqt.exec)) as "BUFF/EXE",
decode(sqt.exec, 0, to_number(null),(sqt.dskr / sqt.exec)) as "DISK/EXE",
decode(sqt.exec, 0, to_number(null),(sqt.rowp / sqt.exec)) as "ROWS/EXE",
(100 * ((sqt.elap / 1000000) / :dbtime)) as "ELP%",
(100 * ((sqt.cput / 1000000) / :dbcpu)) as "CPU%",
(100*sqt.bget)/total_bget as "BUFF%",
(100 * sqt.dskr)/total_dskr as "DISK%",
(exec*100 / total_exe) as "EXE%",
decode(sqt.elap, 0, sqt.clwait, 100 * sqt.clwait / sqt.elap) as "CWT%" ,
nvl(st.sql_text, to_clob('** SQL Text Not Available **')) as "SQLTEXT",
sql_evt.evt as "EVENT"
from (
select sql_id, max(module) module,
max(plan_hash_value) hash,
sum(elapsed_time_delta) elap,
sum(cpu_time_delta) cput,
sum(executions_delta) exec,
sum(buffer_gets_delta) bget,
sum(sum(buffer_gets_delta)) over () total_bget,
sum(disk_reads_delta) dskr,
sum(sum(disk_reads_delta)) over () total_dskr,
sum(rows_processed_delta) rowp,
sum(clwait_delta) clwait,
sum(sum(executions_delta)) over () total_exe
from dba_hist_sqlstat
where dbid = :dbid
and instance_number = :inst_num
and :bid < snap_id
and snap_id <= :eid
group by sql_id
) sqt,
(
SELECT sql_id, hash,
evt1||' [EVT/TOT%] : '||tot1||' [EVT/SQL%] : '||sql1||CHR(10) ||
decode(evt2,null,null,evt2||' [EVT/TOT%] : '||tot2||' [EVT/SQL%] : '||sql2||CHR(10)) ||
decode(evt3,null,null,evt3||' [EVT/TOT%] : '||tot3||' [EVT/SQL%] : '||sql3||CHR(10)) ||
decode(evt4,null,null,evt4||' [EVT/TOT%] : '||tot3||' [EVT/SQL%] : '||sql4||CHR(10)) ||
decode(evt5,null,null,evt5||' [EVT/TOT%] : '||tot3||' [EVT/SQL%] : '||sql5||CHR(10)) ||
decode(evt6,null,null,evt6||' [EVT/TOT%] : '||tot3||' [EVT/SQL%] : '||sql6||CHR(10)) ||
decode(evt7,null,null,evt7||' [EVT/TOT%] : '||tot3||' [EVT/SQL%] : '||sql7||CHR(10)) ||
decode(evt8,null,null,evt8||' [EVT/TOT%] : '||tot3||' [EVT/SQL%] : '||sql8||CHR(10)) ||
decode(evt9,null,null,evt9||' [EVT/TOT%] : '||tot3||' [EVT/SQL%] : '||sql9||CHR(10)) ||
decode(evt10,null,null,evt10||' [EVT/TOT%] : '||tot3||' [EVT/SQL%] : '||sql10||CHR(10)) ||
decode(evt11,null,null,evt11||' [EVT/TOT%] : '||tot3||' [EVT/SQL%] : '||sql11||CHR(10)) ||
decode(evt12,null,null,evt12||' [EVT/TOT%] : '||tot3||' [EVT/SQL%] : '||sql12||CHR(10)) ||
decode(evt13,null,null,evt13||' [EVT/TOT%] : '||tot3||' [EVT/SQL%] : '||sql13||CHR(10)) ||
decode(evt14,null,null,evt14||' [EVT/TOT%] : '||tot3||' [EVT/SQL%] : '||sql14||CHR(10)) ||
decode(evt15,null,null,evt15||' [EVT/TOT%] : '||tot3||' [EVT/SQL%] : '||sql15||CHR(10)) ||
decode(evt16,null,null,evt16||' [EVT/TOT%] : '||tot3||' [EVT/SQL%] : '||sql16||CHR(10)) ||
decode(evt17,null,null,evt17||' [EVT/TOT%] : '||tot3||' [EVT/SQL%] : '||sql17||CHR(10)) ||
decode(evt18,null,null,evt18||' [EVT/TOT%] : '||tot3||' [EVT/SQL%] : '||sql18||CHR(10)) ||
decode(evt19,null,null,evt19||' [EVT/TOT%] : '||tot3||' [EVT/SQL%] : '||sql19||CHR(10)) ||
decode(evt20,null,null,evt20||' [EVT/TOT%] : '||tot3||' [EVT/SQL%] : '||sql20||CHR(10)) ||
decode(evt21,null,null,evt21||' [EVT/TOT%] : '||tot3||' [EVT/SQL%] : '||sql21||CHR(10)) ||
decode(evt22,null,null,evt22||' [EVT/TOT%] : '||tot3||' [EVT/SQL%] : '||sql22||CHR(10)) ||
decode(evt23,null,null,evt23||' [EVT/TOT%] : '||tot3||' [EVT/SQL%] : '||sql23||CHR(10)) ||
decode(evt24,null,null,evt24||' [EVT/TOT%] : '||tot3||' [EVT/SQL%] : '||sql24||CHR(10)) ||
decode(evt25,null,null,evt25||' [EVT/TOT%] : '||tot3||' [EVT/SQL%] : '||sql25) evt
FROM (
SELECT row_number() over (partition by sql_id, hash order by evt_c*100/tot_c) rn ,
sql_id, hash,
event evt1,
rpad(to_char(evt_c*100/tot_c,'990.99'),7,' ') tot1,
to_char(evt_c*100/sql_c,'990.99') sql1,
lead(event,1) over (partition by sql_id, hash order by evt_c) evt2,
lead(rpad(to_char(evt_c*100/tot_c,'990.99'),7,' '),1) over (partition by sql_id, hash order by evt_c) tot2,
lead(to_char(evt_c*100/sql_c,'990.99'),1) over (partition by sql_id, hash order by evt_c) sql2,
lead(event,2) over (partition by sql_id, hash order by evt_c) evt3,
lead(rpad(to_char(evt_c*100/tot_c,'990.99'),7,' '),2) over (partition by sql_id, hash order by evt_c) tot3,
lead(to_char(evt_c*100/sql_c,'990.99'),2) over (partition by sql_id, hash order by evt_c) sql3,
lead(event,3) over (partition by sql_id, hash order by evt_c) evt4,
lead(rpad(to_char(evt_c*100/tot_c,'990.99'),7,' '),3) over (partition by sql_id, hash order by evt_c) tot4,
lead(to_char(evt_c*100/sql_c,'990.99'),3) over (partition by sql_id, hash order by evt_c) sql4,
lead(event,4) over (partition by sql_id, hash order by evt_c) evt5,
lead(rpad(to_char(evt_c*100/tot_c,'990.99'),7,' '),4) over (partition by sql_id, hash order by evt_c) tot5,
lead(to_char(evt_c*100/sql_c,'990.99'),4) over (partition by sql_id, hash order by evt_c) sql5,
lead(event,5) over (partition by sql_id, hash order by evt_c) evt6,
lead(rpad(to_char(evt_c*100/tot_c,'990.99'),7,' '),5) over (partition by sql_id, hash order by evt_c) tot6,
lead(to_char(evt_c*100/sql_c,'990.99'),5) over (partition by sql_id, hash order by evt_c) sql6,
lead(event,6) over (partition by sql_id, hash order by evt_c) evt7,
lead(rpad(to_char(evt_c*100/tot_c,'990.99'),7,' '),6) over (partition by sql_id, hash order by evt_c) tot7,
lead(to_char(evt_c*100/sql_c,'990.99'),6) over (partition by sql_id, hash order by evt_c) sql7,
lead(event,7) over (partition by sql_id, hash order by evt_c) evt8,
lead(rpad(to_char(evt_c*100/tot_c,'990.99'),7,' '),7) over (partition by sql_id, hash order by evt_c) tot8,
lead(to_char(evt_c*100/sql_c,'990.99'),7) over (partition by sql_id, hash order by evt_c) sql8,
lead(event,8) over (partition by sql_id, hash order by evt_c) evt9,
lead(rpad(to_char(evt_c*100/tot_c,'990.99'),7,' '),8) over (partition by sql_id, hash order by evt_c) tot9,
lead(to_char(evt_c*100/sql_c,'990.99'),8) over (partition by sql_id, hash order by evt_c) sql9,
lead(event,9) over (partition by sql_id, hash order by evt_c) evt10,
lead(rpad(to_char(evt_c*100/tot_c,'990.99'),7,' '),9) over (partition by sql_id, hash order by evt_c) tot10,
lead(to_char(evt_c*100/sql_c,'990.99'),9) over (partition by sql_id, hash order by evt_c) sql10,
lead(event,10) over (partition by sql_id, hash order by evt_c) evt11,
lead(rpad(to_char(evt_c*100/tot_c,'990.99'),7,' '),10) over (partition by sql_id, hash order by evt_c) tot11,
lead(to_char(evt_c*100/sql_c,'990.99'),10) over (partition by sql_id, hash order by evt_c) sql11,
lead(event,11) over (partition by sql_id, hash order by evt_c) evt12,
lead(rpad(to_char(evt_c*100/tot_c,'990.99'),7,' '),11) over (partition by sql_id, hash order by evt_c) tot12,
lead(to_char(evt_c*100/sql_c,'990.99'),11) over (partition by sql_id, hash order by evt_c) sql12,
lead(event,12) over (partition by sql_id, hash order by evt_c) evt13,
lead(rpad(to_char(evt_c*100/tot_c,'990.99'),7,' '),12) over (partition by sql_id, hash order by evt_c) tot13,
lead(to_char(evt_c*100/sql_c,'990.99'),12) over (partition by sql_id, hash order by evt_c) sql13,
lead(event,13) over (partition by sql_id, hash order by evt_c) evt14,
lead(rpad(to_char(evt_c*100/tot_c,'990.99'),7,' '),13) over (partition by sql_id, hash order by evt_c) tot14,
lead(to_char(evt_c*100/sql_c,'990.99'),13) over (partition by sql_id, hash order by evt_c) sql14,
lead(event,14) over (partition by sql_id, hash order by evt_c) evt15,
lead(rpad(to_char(evt_c*100/tot_c,'990.99'),7,' '),14) over (partition by sql_id, hash order by evt_c) tot15,
lead(to_char(evt_c*100/sql_c,'990.99'),14) over (partition by sql_id, hash order by evt_c) sql15,
lead(event,15) over (partition by sql_id, hash order by evt_c) evt16,
lead(rpad(to_char(evt_c*100/tot_c,'990.99'),7,' '),15) over (partition by sql_id, hash order by evt_c) tot16,
lead(to_char(evt_c*100/sql_c,'990.99'),15) over (partition by sql_id, hash order by evt_c) sql16,
lead(event,16) over (partition by sql_id, hash order by evt_c) evt17,
lead(rpad(to_char(evt_c*100/tot_c,'990.99'),7,' '),16) over (partition by sql_id, hash order by evt_c) tot17,
lead(to_char(evt_c*100/sql_c,'990.99'),16) over (partition by sql_id, hash order by evt_c) sql17,
lead(event,17) over (partition by sql_id, hash order by evt_c) evt18,
lead(rpad(to_char(evt_c*100/tot_c,'990.99'),7,' '),17) over (partition by sql_id, hash order by evt_c) tot18,
lead(to_char(evt_c*100/sql_c,'990.99'),17) over (partition by sql_id, hash order by evt_c) sql18,
lead(event,18) over (partition by sql_id, hash order by evt_c) evt19,
lead(rpad(to_char(evt_c*100/tot_c,'990.99'),7,' '),18) over (partition by sql_id, hash order by evt_c) tot19,
lead(to_char(evt_c*100/sql_c,'990.99'),18) over (partition by sql_id, hash order by evt_c) sql19,
lead(event,19) over (partition by sql_id, hash order by evt_c) evt20,
lead(rpad(to_char(evt_c*100/tot_c,'990.99'),7,' '),19) over (partition by sql_id, hash order by evt_c) tot120,
lead(to_char(evt_c*100/sql_c,'990.99'),19) over (partition by sql_id, hash order by evt_c) sql20,
lead(event,20) over (partition by sql_id, hash order by evt_c) evt21,
lead(rpad(to_char(evt_c*100/tot_c,'990.99'),7,' '),20) over (partition by sql_id, hash order by evt_c) tot21,
lead(to_char(evt_c*100/sql_c,'990.99'),20) over (partition by sql_id, hash order by evt_c) sql21,
lead(event,21) over (partition by sql_id, hash order by evt_c) evt22,
lead(rpad(to_char(evt_c*100/tot_c,'990.99'),7,' '),21) over (partition by sql_id, hash order by evt_c) tot22,
lead(to_char(evt_c*100/sql_c,'990.99'),21) over (partition by sql_id, hash order by evt_c) sql22,
lead(event,22) over (partition by sql_id, hash order by evt_c) evt23,
lead(rpad(to_char(evt_c*100/tot_c,'990.99'),7,' '),22) over (partition by sql_id, hash order by evt_c) tot23,
lead(to_char(evt_c*100/sql_c,'990.99'),22) over (partition by sql_id, hash order by evt_c) sql23,
lead(event,23) over (partition by sql_id, hash order by evt_c) evt24,
lead(rpad(to_char(evt_c*100/tot_c,'990.99'),7,' '),23) over (partition by sql_id, hash order by evt_c) tot24,
lead(to_char(evt_c*100/sql_c,'990.99'),23) over (partition by sql_id, hash order by evt_c) sql24,
lead(event,24) over (partition by sql_id, hash order by evt_c) evt25,
lead(rpad(to_char(evt_c*100/tot_c,'990.99'),7,' '),24) over (partition by sql_id, hash order by evt_c) tot25,
lead(to_char(evt_c*100/sql_c,'990.99'),24) over (partition by sql_id, hash order by evt_c) sql25
FROM (
SELECT a.sql_id, a.sql_plan_hash_value as HASH,
rpad(a.event,45,' ') event, count(*) as evt_c,
sum(count(*)) over() tot_c,
sum(count(*)) over (partition by a.sql_id, a.sql_plan_hash_value) as sql_c
FROM (
SELECT 0 as snap_id,
a.sample_id, a.sample_time, a.session_id,
a.session_serial#, a.session_type, a.session_state,
a.user_id, a.sql_id, a.sql_opcode,
a.sql_child_number, a.sql_plan_hash_value, a.plsql_entry_object_id,
a.plsql_entry_subprogram_id, a.plsql_object_id, a.plsql_subprogram_id,
a.service_hash, a.qc_session_id, a.qc_instance_id,
nvl(a.event, 'CPU + Wait for CPU') as event,
nvl(a.event_id, 1) as event_id,
nvl(a.wait_class, 'CPU') as wait_class,
nvl(a.wait_class_id, 9999) as wait_class_id,
a.seq#, a.p1, a.p1text,
a.p2, a.p2text, a.p3,
a.p3text, a.wait_time, a.time_waited,
a.xid, a.blocking_session, a.blocking_session_serial#,
a.blocking_session_status, a.current_obj#, a.current_file#,
a.current_block#, a.program, a.module,
a.action, a.client_id
FROM DBA_HIST_ACTIVE_SESS_HISTORY a
WHERE dbid = :dbid
AND instance_number = :inst_num
AND :bid < snap_id
AND snap_id <= :eid
--------
UNION ALL
SELECT 0 as snap_id,
a.sample_id, a.sample_time, a.session_id,
a.session_serial#, a.session_type, a.session_state,
a.user_id, a.sql_id, a.sql_opcode,
a.sql_child_number, a.sql_plan_hash_value, a.plsql_entry_object_id,
a.plsql_entry_subprogram_id, a.plsql_object_id, a.plsql_subprogram_id,
a.service_hash, a.qc_session_id, a.qc_instance_id,
nvl(a.event, 'CPU + Wait for CPU') as event,
nvl(a.event_id, 1) as event_id,
nvl(a.wait_class, 'CPU') as wait_class,
nvl(a.wait_class_id, 9999) as wait_class_id,
a.seq#, a.p1, a.p1text,
a.p2, a.p2text, a.p3,
a.p3text, a.wait_time, a.time_waited,
a.xid, a.blocking_session, a.blocking_session_serial#,
a.blocking_session_status, a.current_obj#, a.current_file#,
a.current_block#, a.program, a.module,
a.action, a.client_id
FROM GV\$ACTIVE_SESSION_HISTORY a
WHERE 1=1
AND inst_id = :inst_num
AND to_char(sample_time,'YYYYMMDDHH24MISS') between :b_time and :e_time
--------
) a
WHERE a.sql_id is not null
GROUP BY a.sql_id, a.sql_plan_hash_value , a.event
)
)
WHERE rn=1
) sql_evt,
dba_hist_sqltext st
/* where st.sql_id(+) = sqt.sql_id
and sql_evt.sql_id = sqt.sql_id
and st.dbid(+) = :dbid
*/
where st.sql_id(+) = sqt.sql_id
and sql_evt.sql_id(+) = sqt.sql_id
and sql_evt.hash(+) = sqt.hash
and st.dbid(+) = :dbid
order by "$ORD_TYPE" desc, sql_id
)
/
spool off
exit
EOF
참고 : SQL명령어 코드
- 세션이 수행한 SQL의 종류를 나타내며, 동적 뷰 컬럼 및 SQL 추적 파일에 기록된다.
