|
An expert is a person
who has made all the mistakes that can be made in a very narrow field
A. Trace 관련 체크
[ 샘플 1 ]
select b.OWNER, b.SEGMENT_NAME, b.SEGMENT_TYPE, b.BYTES, b.tablespace_name,
a.TABLE_NAME, a.NUM_ROWS, a.INITIAL_EXTENT,a.NEXT_EXTENT
from scott_tables a,
scott_dba_segments b
where a.OWNER = b.owner
and a.table_name = b.segment_name
and a.owner='SCOTT'
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.030 0.046 0 116 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 3 0.000 0.001 0 29 0 20
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 5 0.030 0.047 0 145 0 20
==> 전체 일량
Misses in library cache during parse: 1
==> Hard Parse / Sorf Parse [ 여기선 Hard ]
Optimizer goal: ALL_ROWS
Parsing user: SCOTT (ID=57)
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
20 HASH JOIN (cr=29 pr=0 pw=0 time=345 us)
15 TABLE ACCESS BY INDEX ROWID SCOTT_TABLES (cr=5 pr=0 pw=0 time=97 us)
15 INDEX RANGE SCAN XAK1_SCOTT_TABLES (cr=2 pr=0 pw=0 time=20 us)(Object ID 53941)
29 TABLE ACCESS BY INDEX ROWID SCOTT_DBA_SEGMENTS (cr=24 pr=0 pw=0 time=191 us)
29 INDEX RANGE SCAN XAK1_SCOTT_DBA_SEGMENTS (cr=4 pr=0 pw=0 time=13 us)(Object ID 53940)
1. XAK1_SCOTT_TABLES INDEX RANGE SCAN 하는데 BLOCK 2개를 읽었고, 20 us 시간이 소요됨
관련 OBJECT_ID = 53941 ( 인덱스 )
2. SCOTT_TABLES 에 INDEX ROWID 를 통해서 ACCESS 하는데 5(3+2) 개의 BLOCK 을 읽었고, 97 us 시간이 소요됨
==> SCOTT_TABLES 관련 ACCESS 시 총 5개의 BLOCK을 읽었고, 총 97 us 시간이 소요됨
3. XAK1_SCOTT_DBA_SEGMENTS INDEX RANGE SCAN 하는데 BLOCK 을 4개 읽고, 13 us 시간이 소요됨
관련 OBJECT_ID = 53940 ( 인덱스 )
4. SCOTT_DBA_SEGMENTS 에 INDEX ROWID 를 통한 ACCESS 하는데 총 24(20+4) 개의 BLOCK 을 읽었고, 191 us 시간이 소요됨
==> SCOTT_DBA_SEGMENTS 관련 ACCESS 시 총 28개의 BLOCK을 읽었고, 총 191 us 시간이 소요됨
5. HASH JOIN 수행시 총 29(5+24) 개의 BLOCK 을 읽고, 345(57+97+191) us 시간이 소요됨
수행 시간
XAK1_SCOTT_TABLES 20
SCOTT_TABLES 77
XAK1_SCOTT_DBA_SEGMENTS 13
SCOTT_DBA_SEGMENTS 178
HASH JOIN 57
[ 샘플 2 ]
select /*+ ordered USE_NL(b a) */ a.EMPNO, a.ENAME, a.JOB, b.DEPTNO, b.DNAME
from emp a, dept b
where a.deptno = b.deptno
and a.empno > 7500
and b.dname ='SALES'
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.010 0.002 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.000 0.000 0 18 0 5
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 0.010 0.002 0 18 0 5
Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: SCOTT (ID=57)
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
5 NESTED LOOPS (cr=18 pr=0 pw=0 time=56 us)
12 TABLE ACCESS BY INDEX ROWID EMP (cr=4 pr=0 pw=0 time=168 us)
12 INDEX RANGE SCAN PK_EMP (cr=2 pr=0 pw=0 time=109 us)(Object ID 49711)
5 TABLE ACCESS BY INDEX ROWID DEPT (cr=14 pr=0 pw=0 time=102 us)
12 INDEX UNIQUE SCAN PK_DEPT (cr=2 pr=0 pw=0 time=44 us)(Object ID 49709)
1:M 관계이고, Nested Loop 로 12번 연결 작업
( DEPT 총 ROW 가 5개 임에도 불구하고, 12 ROW ACCESS 한 이유 )
[ 샘플 3]
select a.EMPNO, a.ENAME, a.JOB, b.DEPTNO, b.DNAME
from emp a, dept b
where a.deptno = b.deptno
and a.empno > 7500
and b.dname ='SALES'
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.002 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.000 0.000 0 6 0 5
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 0.000 0.002 0 6 0 5
!! 샘플2 와 샘플3 비교시 Query 가 18 --> 6 으로 절대적인 일량이 줄어들었다.
절대적인 일량이 줄어드는 것이 좋은 것이다.
Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: SCOTT (ID=57)
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
5 MERGE JOIN (cr=6 pr=0 pw=0 time=117 us)
1 TABLE ACCESS BY INDEX ROWID DEPT (cr=4 pr=0 pw=0 time=53 us)
4 INDEX FULL SCAN PK_DEPT (cr=2 pr=0 pw=0 time=19 us)(Object ID 49709)
5 SORT JOIN (cr=2 pr=0 pw=0 time=72 us)
12 TABLE ACCESS BY INDEX ROWID EMP (cr=2 pr=0 pw=0 time=48 us)
12 INDEX RANGE SCAN PK_EMP (cr=1 pr=0 pw=0 time=20 us)(Object ID 49711)
B.Predicate 정보 보는 방법
출처 : 강정식
결론 : 10.1 까지는 Predicate 정보를 보여주지 않는다. 이유를 모르겠네요
10.2 에서부터 Predicate 정보를 보여준다.
sql*plus에서 'AUTOTRACE' 명령어를 통해 보실 수 있습니다.
'AUTOTRACE' 구문
Set AUTOTRACE [OFF, ON, TRACE (ONLY)] [EXPLAIN] [STATISTICS]
SQL> set autotrace on : autotrace 설정
SQL> set autotrace traceonly : SQL문 실행과 숨기기
SQL> set autotrace traceonly explain : 통계 정보 조회하지 않고 실행 계획만 조회
[Sample]
SQL > set autotrace traceonly explain;
SQL > SELECT * FROM DEPT WHERE ROWNUM = 1;
1) 8.1.7.4
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production
SQL> set autotrace traceonly explain;
SQL> SELECT * FROM booking where rownum = 1 ;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=34950 Card=8844175 B
ytes=3882592825)
1 0 COUNT (STOPKEY)
2 1 TABLE ACCESS (FULL) OF 'BOOKING' (Cost=34950 Card=884417
5 Bytes=3882592825)
SQL> exit
2)10.1.0.4
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
scott@ARTDOM>set autotrace traceonly explain;
scott@ARTDOM>SELECT * FROM DEPT WHERE ROWNUM = 1;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=20)
1 0 COUNT (STOPKEY)
2 1 TABLE ACCESS (FULL) OF 'DEPT' (TABLE) (Cost=2 Card=1 Bytes=20)
scott@ARTDOM>exit
3)10.2.0.3
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL> set autotrace traceonly explain;
SQL> select * from bkg_booking where rownum = 1 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 4285486501
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 503 | 2 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS FULL| BKG_BOOKING | 1 | 503 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
SQL> exit
4)10.2.0.1
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> set autotrace traceonly explain;
SQL> select * from IMSBLTH6 where rownum = 1 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 1852230962
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 228 | 2 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS FULL| IMSBLTH6 | 1 | 228 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> set autotrace traceonly explain;
SQL> select * from I_BL_LOG where rownum = 1 ;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=31)
1 0 COUNT (STOPKEY)
2 1 TABLE ACCESS (FULL) OF 'I_BL_LOG' (TABLE) (Cost=2 Card=1
Bytes=31)
C. Trace 비교 ( 8i VS 10G )
1) 8i
Call Count CPU Time Elapsed Time 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 9 0.01 0.00 0 1039 0 76
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 11 0.01 0.00 0 1039 0 76
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user: HANSIS (ID=45)
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
76 SORT GROUP BY
124 NESTED LOOPS
390 TABLE ACCESS BY INDEX ROWID I_BKG_CUST
390 INDEX RANGE SCAN OF XAK1I_BKG_CUST (NONUNIQUE)
124 INDEX RANGE SCAN OF XAK4I_BOOKING (NONUNIQUE)
2) 10G
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 8 0.000 0.001 0 0 0 0
Execute 8 0.000 0.030 1 3 2 0
Fetch 7 0.000 0.000 0 7 0 0
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 23 0.000 0.032 1 10 2 0
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer goal: CHOOSE
Parsing user: SYS (ID=0)
Recursive depth: 1
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
0 NESTED LOOPS (cr=1 pr=0 pw=0 time=27 us)
0 NESTED LOOPS (cr=1 pr=0 pw=0 time=23 us)
0 TABLE ACCESS BY INDEX ROWID ASSOCIATION$ (cr=1 pr=0 pw=0 time=23 us)
0 INDEX RANGE SCAN ASSOC1 (cr=1 pr=0 pw=0 time=21 us)OF ASSOC1 (UNIQUE)
0 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=0 pr=0 pw=0 time=0 us)
0 INDEX UNIQUE SCAN I_OBJ1 (cr=0 pr=0 pw=0 time=0 us)OF I_OBJ1 (UNIQUE)
0 TABLE ACCESS CLUSTER USER$ (cr=0 pr=0 pw=0 time=0 us)
0 INDEX UNIQUE SCAN I_USER# (cr=0 pr=0 pw=0 time=0 us)OF I_USER# (UNIQUE)
|
