고정 헤더 영역
상세 컨텐츠
본문
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
Disk file operations I/O 1 0.00 0.00
db file sequential read 1 0.00 0.00
db file scattered read 23 0.11 0.62
SQL*Net message from client 2 3.84 3.84
********************************************************************************
select count(*) from ind_random where owner = 'SYS';
자주 사용하는 쿼리문인데 인덱스가 안 걸려 있는 경우: 인덱스를 생성하자.
인덱스를 먼저 생성한 다음 플러시하기
HR@ora11g> create index hr.ind_random_ix on hr.ind_random(owner);
Index created.
SYS@ora11g> alter system flush buffer_cache;
System altered.
SYS@ora11g> /
System altered.
HR@ora11g> alter session set tracefile_identifier = 'hr';
Session altered.
HR@ora11g> alter session set events '10046 trace name context forever, level 8';
Session altered.
HR@ora11g> select count(*) from ind_random where owner = 'SYS';
COUNT(*)
----------
29856
HR@ora11g> alter session set events '10046 trace name context off';
Session altered.
HR@ora11g> !
[oracle@oracle ~]$ cd $ORACLE_BASE/diag/rdbms/ora11g/ora11g/trace
[oracle@oracle trace]$ ls *hr.trc
ora11g_ora_32476_hr.trc
[oracle@oracle trace]$ tkprof ora11g_ora_32476_hr.trc text1.ext
TKPROF: Release 11.2.0.4.0 - Development on Wed Feb 14 11:57:39 2024
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
[oracle@oracle trace]$ cat text1.ext
더보기
[oracle@oracle trace]$ cat text1.ext
TKPROF: Release 11.2.0.4.0 - Development on Wed Feb 14 11:57:39 2024
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Trace file: ora11g_ora_32476_hr.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
SQL ID: 6xpydnkm336jb Plan Hash: 3059062694
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE
NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false')
NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_0"),
NVL(SUM(C2),:"SYS_B_1")
FROM
(SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("IND_RANDOM") FULL("IND_RANDOM")
NO_PARALLEL_INDEX("IND_RANDOM") */ :"SYS_B_2" AS C1, CASE WHEN
"IND_RANDOM"."OWNER"=:"SYS_B_3" THEN :"SYS_B_4" ELSE :"SYS_B_5" END AS C2
FROM "HR"."IND_RANDOM" SAMPLE BLOCK (:"SYS_B_6" , :"SYS_B_7") SEED
(:"SYS_B_8") "IND_RANDOM") SAMPLESUB
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.01 0.28 269 58 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.01 0.28 269 58 0 1
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84 (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT AGGREGATE (cr=58 pr=269 pw=0 time=285731 us)
3677 3677 3677 TABLE ACCESS SAMPLE IND_RANDOM (cr=58 pr=269 pw=0 time=12304 us cost=19 size=149234 card=5146)
(QUESTION. 이거 INDEX FAST FULL SCAN 나와야 정상이라는데 난 왜 TABLE ACCESS 나와? ...
게다가 길이도 너무 길어... 근데 또 밑에 index fast full scan 이 나오긴 나왔어...)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 10 0.01 0.01
db file scattered read 33 0.03 0.26
********************************************************************************
SQL ID: 82mm06avuvhq3 Plan Hash: 397536159
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS opt_param('parallel_execution_enabled',
'false') NO_PARALLEL(SAMPLESUB) NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE
*/ NVL(SUM(C1),:"SYS_B_0"), NVL(SUM(C2),:"SYS_B_1"), NVL(SUM(C3),:"SYS_B_2")
FROM
(SELECT /*+ NO_PARALLEL("IND_RANDOM") INDEX("IND_RANDOM" IND_RANDOM_IX)
NO_PARALLEL_INDEX("IND_RANDOM") */ :"SYS_B_3" AS C1, :"SYS_B_4" AS C2,
:"SYS_B_5" AS C3 FROM "HR"."IND_RANDOM" "IND_RANDOM" WHERE
"IND_RANDOM"."OWNER"=:"SYS_B_6" AND ROWNUM <= :"SYS_B_7") SAMPLESUB
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 7 7 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 7 7 0 1
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84 (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT AGGREGATE (cr=7 pr=7 pw=0 time=1330 us)
2500 2500 2500 VIEW (cr=7 pr=7 pw=0 time=1133 us cost=10 size=390 card=10)
2500 2500 2500 COUNT STOPKEY (cr=7 pr=7 pw=0 time=881 us)
2500 2500 2500 INDEX RANGE SCAN IND_RANDOM_IX (cr=7 pr=7 pw=0 time=631 us cost=10 size=14008 card=824)(object id 90016)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 7 0.00 0.00
********************************************************************************
SQL ID: 0bpvu28df5xzp Plan Hash: 2556419480
select count(*)
from
ind_random where owner = 'SYS'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.29 278 67 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.01 0.06 154 166 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.03 0.35 432 233 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT AGGREGATE (cr=166 pr=154 pw=0 time=65201 us)
29856 29856 29856 INDEX FAST FULL SCAN IND_RANDOM_IX (cr=166 pr=154 pw=0 time=29338 us cost=48 size=428281 card=25193)(object id 90016)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 5 0.00 0.00
SQL*Net message to client 2 0.00 0.00
db file scattered read 16 0.03 0.05
SQL*Net message from client 2 18.81 18.81
********************************************************************************
SQL ID: 06nvwn223659v Plan Hash: 0
alter session set events '10046 trace name context off'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Parsing user id: 84
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.02 0.29 278 67 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 0.01 0.06 154 166 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.03 0.35 432 233 0 1
Misses in library cache during parse: 1
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 3 0.00 0.00
SQL*Net message from client 3 31.78 50.60
db file sequential read 5 0.00 0.00
db file scattered read 16 0.03 0.05
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 0.01 0.28 276 65 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.02 0.28 276 65 0 2
Misses in library cache during parse: 2
Misses in library cache during execute: 2
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 17 0.01 0.01
db file scattered read 33 0.03 0.26
4 user SQL statements in session.
0 internal SQL statements in session.
4 SQL statements in session.
********************************************************************************
Trace file: ora11g_ora_32476_hr.trc
Trace file compatibility: 11.1.0.7
Sort options: default
1 session in tracefile.
4 user SQL statements in trace file.
0 internal SQL statements in trace file.
4 SQL statements in trace file.
4 unique SQL statements in trace file.
145 lines in trace file.
19 elapsed seconds in trace file.
SYS@ora11g> alter system flush buffer_cache;
System altered.
SYS@ora11g> /
HR@ora11g> alter session set tracefile_identifier = 'hr';
Session altered.
HR@ora11g> alter session set events '10046 trace name context forever, level 8';
Session altered.
HR@ora11g> select * from ind_random where owner = 'SYS';
29856 rows selected.
HR@ora11g> alter session set events '10046 trace name context off';
Session altered.
HR@ora11g> select count(*) from hr.ind_random;
COUNT(*)
----------
68183
HR@ora11g> !
[oracle@oracle ~]$ cd $ORACLE_BASE/diag/rdbms/ora11g/!ora11g/trace
[oracle@oracle trace]$ ls *hr.trc
ora11g_ora_853_hr.trc
[oracle@oracle trace]$ tkprof ora11g_ora_3105_hr.trc ora3105.etx
TKPROF: Release 11.2.0.4.0 - Development on Wed Feb 14 13:12:35 2024
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
[oracle@oracle trace]$ cat ora3105.etx
더보기
[oracle@oracle trace]$ cat ora3105.etx
TKPROF: Release 11.2.0.4.0 - Development on Wed Feb 14 13:12:35 2024
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Trace file: ora11g_ora_3105_hr.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
SQL ID: 7ng34ruy5awxq Plan Hash: 2542797530
select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.property,
i.pctfree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.distkey,i.lblkkey,
i.dblkkey,i.clufac,i.cols,i.analyzetime,i.samplesize,i.dataobj#,
nvl(i.degree,1),nvl(i.instances,1),i.rowcnt,mod(i.pctthres$,256),
i.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl(c.deferrable#+c.valid#,0),
nvl(i.spare1,i.intcols),i.spare4,i.spare2,i.spare6,decode(i.pctthres$,null,
null,mod(trunc(i.pctthres$/256),256)),ist.cachedblk,ist.cachehit,
ist.logicalread
from
ind$ i, ind_stats$ ist, (select enabled, min(cols) unicols,
min(to_number(bitand(defer,1))) deferrable#,min(to_number(bitand(defer,4)))
valid# from cdef$ where obj#=:1 and enabled > 1 group by enabled) c where
i.obj#=c.enabled(+) and i.obj# = ist.obj#(+) and i.bo#=:1 order by i.obj#
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.02 7 7 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.02 7 7 0 1
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT ORDER BY (cr=7 pr=7 pw=0 time=26579 us cost=6 size=372 card=2)
1 1 1 HASH JOIN OUTER (cr=7 pr=7 pw=0 time=26553 us cost=5 size=372 card=2)
1 1 1 NESTED LOOPS OUTER (cr=4 pr=4 pw=0 time=13914 us cost=2 size=286 card=2)
1 1 1 TABLE ACCESS CLUSTER IND$ (cr=3 pr=3 pw=0 time=7208 us cost=2 size=182 card=2)
1 1 1 INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=2 pw=0 time=7175 us cost=1 size=0 card=1)(object id 3)
0 0 0 TABLE ACCESS BY INDEX ROWID IND_STATS$ (cr=1 pr=1 pw=0 time=6701 us cost=0 size=52 card=1)
0 0 0 INDEX UNIQUE SCAN I_IND_STATS$_OBJ# (cr=1 pr=1 pw=0 time=6700 us cost=0 size=0 card=1)(object id 456)
0 0 0 VIEW (cr=3 pr=3 pw=0 time=12435 us cost=3 size=43 card=1)
0 0 0 SORT GROUP BY (cr=3 pr=3 pw=0 time=12433 us cost=3 size=15 card=1)
0 0 0 TABLE ACCESS CLUSTER CDEF$ (cr=3 pr=3 pw=0 time=12424 us cost=2 size=15 card=1)
1 1 1 INDEX UNIQUE SCAN I_COBJ# (cr=2 pr=2 pw=0 time=7141 us cost=1 size=0 card=1)(object id 30)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 7 0.00 0.02
********************************************************************************
SQL ID: 96g93hntrzjtr Plan Hash: 2239883476
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#,
sample_size, minimum, maximum, distcnt, lowval, hival, density, col#,
spare1, spare2, avgcln
from
hist_head$ where obj#=:1 and intcol#=:2
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 28 0.00 0.00 0 0 0 0
Fetch 28 0.00 0.01 7 84 0 28
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 56 0.00 0.01 7 84 0 28
Misses in library cache during parse: 0
Optimizer mode: RULE
Parsing user id: SYS (recursive depth: 2)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 7 0.01 0.01
********************************************************************************
SQL ID: db78fxqxwxt7r Plan Hash: 3312420081
select /*+ rule */ bucket, endpoint, col#, epvalue
from
histgrm$ where obj#=:1 and intcol#=:2 and row#=:3 order by bucket
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 19 0.00 0.00 0 0 0 0
Fetch 19 0.00 0.04 7 57 0 295
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 38 0.00 0.04 7 57 0 295
Misses in library cache during parse: 0
Optimizer mode: RULE
Parsing user id: SYS (recursive depth: 2)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 7 0.01 0.04
********************************************************************************
SQL ID: 5n1fs4m2n2y0r Plan Hash: 299250003
select pos#,intcol#,col#,spare1,bo#,spare2,spare3
from
icol$ where obj#=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.01 2 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.01 2 3 0 1
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 TABLE ACCESS BY INDEX ROWID ICOL$ (cr=3 pr=2 pw=0 time=17366 us cost=2 size=54 card=2)
1 1 1 INDEX RANGE SCAN I_ICOL1 (cr=2 pr=2 pw=0 time=17362 us cost=1 size=0 card=2)(object id 42)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 2 0.00 0.01
********************************************************************************
SQL ID: 83taa7kaw59c1 Plan Hash: 3765558045
select name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2,
nvl(scale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180,scale,181,scale,182,
scale,183,scale,231,scale,0),null$,fixedstorage,nvl(deflength,0),default$,
rowid,col#,property, nvl(charsetid,0),nvl(charsetform,0),spare1,spare2,
nvl(spare3,0)
from
col$ where obj#=:1 order by intcol#
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 16 0.00 0.00 0 3 0 15
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 18 0.00 0.00 0 3 0 15
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
15 15 15 SORT ORDER BY (cr=3 pr=0 pw=0 time=38 us cost=3 size=708 card=12)
15 15 15 TABLE ACCESS CLUSTER COL$ (cr=3 pr=0 pw=0 time=24 us cost=2 size=708 card=12)
1 1 1 INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=6 us cost=1 size=0 card=1)(object id 3)
********************************************************************************
SQL ID: 6aq34nj2zb2n7 Plan Hash: 2874733959
select col#, grantee#, privilege#,max(mod(nvl(option$,0),2))
from
objauth$ where obj#=:1 and col# is not null group by privilege#, col#,
grantee# order by col#, grantee#
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 2 2 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.01 2 2 0 0
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 SORT GROUP BY (cr=2 pr=2 pw=0 time=9622 us cost=4 size=15 card=1)
0 0 0 TABLE ACCESS BY INDEX ROWID OBJAUTH$ (cr=2 pr=2 pw=0 time=9615 us cost=3 size=15 card=1)
0 0 0 INDEX RANGE SCAN I_OBJAUTH1 (cr=2 pr=2 pw=0 time=9614 us cost=2 size=0 card=1)(object id 62)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 2 0.00 0.00
********************************************************************************
SQL ID: 2q93zsrvbdw48 Plan Hash: 2874733959
select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2))
from
objauth$ where obj#=:1 group by grantee#,privilege#,nvl(col#,0) order by
grantee#
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 2 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 2 0 0
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 SORT GROUP BY (cr=2 pr=0 pw=0 time=41 us cost=4 size=15 card=1)
0 0 0 TABLE ACCESS BY INDEX ROWID OBJAUTH$ (cr=2 pr=0 pw=0 time=7 us cost=3 size=15 card=1)
0 0 0 INDEX RANGE SCAN I_OBJAUTH1 (cr=2 pr=0 pw=0 time=7 us cost=2 size=0 card=1)(object id 62)
********************************************************************************
SQL ID: 3w4qs0tbpmxr6 Plan Hash: 1224215794
select con#,obj#,rcon#,enabled,nvl(defer,0),spare2,spare3
from
cdef$ where robj#=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 2 2 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 2 2 0 0
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 TABLE ACCESS BY INDEX ROWID CDEF$ (cr=2 pr=2 pw=0 time=2241 us cost=2 size=84 card=3)
0 0 0 INDEX RANGE SCAN I_CDEF3 (cr=2 pr=2 pw=0 time=2241 us cost=1 size=0 card=3)(object id 55)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 2 0.00 0.00
********************************************************************************
SQL ID: gx4mv66pvj3xz Plan Hash: 2570921597
select con#,type#,condlength,intcols,robj#,rcon#,match#,refact,nvl(enabled,0),
rowid,cols,nvl(defer,0),mtime,nvl(spare1,0),spare2,spare3
from
cdef$ where obj#=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 7 0.00 0.00 0 9 0 6
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 9 0.00 0.00 0 9 0 6
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
6 6 6 TABLE ACCESS CLUSTER CDEF$ (cr=9 pr=0 pw=0 time=9 us cost=2 size=192 card=4)
1 1 1 INDEX UNIQUE SCAN I_COBJ# (cr=2 pr=0 pw=0 time=7 us cost=1 size=0 card=1)(object id 30)
********************************************************************************
SQL ID: 53saa2zkr6wc3 Plan Hash: 3954488388
select intcol#,nvl(pos#,0),col#,nvl(spare1,0)
from
ccol$ where con#=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 6 0.00 0.00 0 0 0 0
Fetch 12 0.00 0.00 2 24 0 6
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 19 0.00 0.00 2 24 0 6
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 TABLE ACCESS BY INDEX ROWID CCOL$ (cr=4 pr=2 pw=0 time=3219 us cost=3 size=16 card=1)
1 1 1 INDEX RANGE SCAN I_CCOL1 (cr=3 pr=2 pw=0 time=3217 us cost=2 size=0 card=1)(object id 57)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 2 0.00 0.00
********************************************************************************
SQL ID: g4wmy06ts7k0j Plan Hash: 780424712
select *
from
ind_random where owner = 'SYS'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1992 0.05 0.10 986 2956 0 29856
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1994 0.05 0.10 986 2956 0 29856
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
29856 29856 29856 TABLE ACCESS FULL IND_RANDOM (cr=2956 pr=986 pw=0 time=45580 us cost=276 size=2817598 card=28751)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1992 0.00 0.00
Disk file operations I/O 1 0.00 0.00
db file sequential read 1 0.01 0.01
direct path read 22 0.02 0.04
SQL*Net message from client 1992 205.50 208.38
********************************************************************************
SQL ID: 06nvwn223659v Plan Hash: 0
alter session set events '10046 trace name context off'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Parsing user id: 84
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 1992 0.05 0.10 986 2956 0 29856
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1996 0.05 0.10 986 2956 0 29856
Misses in library cache during parse: 1
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1993 0.00 0.00
SQL*Net message from client 1993 205.50 217.28
db file sequential read 1 0.01 0.01
Disk file operations I/O 1 0.00 0.00
direct path read 22 0.02 0.04
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 8 0.00 0.00 0 0 0 0
Execute 60 0.00 0.00 0 0 0 0
Fetch 89 0.00 0.12 29 193 0 352
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 157 0.01 0.13 29 193 0 352
Misses in library cache during parse: 8
Misses in library cache during execute: 8
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 29 0.01 0.11
2 user SQL statements in session.
10 internal SQL statements in session.
12 SQL statements in session.
********************************************************************************
Trace file: ora11g_ora_3105_hr.trc
Trace file compatibility: 11.1.0.7
Sort options: default
1 session in tracefile.
2 user SQL statements in trace file.
10 internal SQL statements in trace file.
12 SQL statements in trace file.
12 unique SQL statements in trace file.
6365 lines in trace file.
208 elapsed seconds in trace file.
근데 나는 이렇게 했더니 아예 안보여...
아무튼 결과는 인덱스가 걸려 있더라도 풀 테이블 스캔으로 나온다고 하심.
왜나하면 랜덤 I/O가 더 많이 발생할 수 있기 때문에...
그런데 여기서 인덱스를 타도록 하고 싶다면?
SYS@ora11g> alter system flush buffer_cache;
System altered.
SYS@ora11g> /
HR@ora11g> alter session set tracefile_identifier = 'hr';
Session altered.
HR@ora11g> alter session set events '10046 trace name context forever, level 8';
Session altered.
HR@ora11g> select /*+ index(i ind_random_ix) */ * from ind_random where owner = 'SYS';
29856 rows selected.
--- 여기 이렇게 힌트를 써주면 된다.
HR@ora11g> alter session set events '10046 trace name context off';
Session altered.
HR@ora11g> !
[oracle@oracle ~]$ cd $ORACLE_BASE/diag/rdbms/ora11g/ora11g/trace
[oracle@oracle trace]$ ls *hr.trc
ora11g_ora_1259_hr.trc ora11g_ora_1279_hr.trc
[oracle@oracle trace]$ tkprof ora11g_ora_3608_hr.trc ora3608.etx
TKPROF: Release 11.2.0.4.0 - Development on Wed Feb 14 13:19:16 2024
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
그렇지만 이렇게 인덱스를 타도록 하면: I/O가 2배나 발생한다.
더보기
[oracle@oracle trace]$ tkprof ora11g_ora_3608_hr.trc ora3608.etx
TKPROF: Release 11.2.0.4.0 - Development on Wed Feb 14 13:19:16 2024
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
[oracle@oracle trace]$ cat ora3608.etx
TKPROF: Release 11.2.0.4.0 - Development on Wed Feb 14 13:19:16 2024
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Trace file: ora11g_ora_3608_hr.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
SQL ID: 5ts6g560k3s55 Plan Hash: 780424712
select /*+ index(i ind_random_ix) */ *
from
ind_random where owner = 'SYS'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1992 0.05 0.05 986 2956 0 29856
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1994 0.05 0.05 986 2956 0 29856
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
29856 29856 29856 TABLE ACCESS FULL IND_RANDOM (cr=2956 pr=986 pw=0 time=13498 us cost=276 size=2817598 card=28751)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1992 0.00 0.00
Disk file operations I/O 1 0.00 0.00
db file sequential read 1 0.00 0.00
direct path read 22 0.00 0.00
SQL*Net message from client 1992 8.81 11.49
********************************************************************************
SQL ID: 06nvwn223659v Plan Hash: 0
alter session set events '10046 trace name context off'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Parsing user id: 84
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 1992 0.05 0.05 986 2956 0 29856
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1996 0.05 0.05 986 2956 0 29856
Misses in library cache during parse: 1
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1993 0.00 0.00
SQL*Net message from client 1993 8.81 18.22
Disk file operations I/O 1 0.00 0.00
db file sequential read 1 0.00 0.00
direct path read 22 0.00 0.00
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 0 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
2 user SQL statements in session.
0 internal SQL statements in session.
2 SQL statements in session.
********************************************************************************
Trace file: ora11g_ora_3608_hr.trc
Trace file compatibility: 11.1.0.7
Sort options: default
1 session in tracefile.
2 user SQL statements in trace file.
0 internal SQL statements in trace file.
2 SQL statements in trace file.
2 unique SQL statements in trace file.
6047 lines in trace file.
11 elapsed seconds in trace file.
'오라클 퍼포먼스 튜닝' 카테고리의 다른 글
| 2024년 2월 14일 5교시 + 6교시 (0) | 2024.02.14 |
|---|---|
| 2024년 2월 14일 4교시 (0) | 2024.02.14 |
| 2024년 2월 14일 2교시 (0) | 2024.02.14 |
| 2024년 2월 14일 (발렌타인데이 ㅠㅠ) 1교시 (0) | 2024.02.14 |
| 2024년 2월 8일 1교시 (0) | 2024.02.08 |
