Trace 분석 :: 오라클 실습[SSISO Community]
 
SSISO 카페 SSISO Source SSISO 구직 SSISO 쇼핑몰 SSISO 맛집
추천검색어 : JUnit   Log4j   ajax   spring   struts   struts-config.xml   Synchronized   책정보   Ajax 마스터하기   우측부분

오라클 실습
[1]
등록일:2009-06-15 00:11:50 (0%)
작성자:
제목:Trace 분석

 

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)

[출처] Trace 분석 |작성자 타락천사

[본문링크] Trace 분석
[1]
코멘트(이글의 트랙백 주소:/cafe/tb_receive.php?no=31535
작성자
비밀번호

 

SSISOCommunity

[이전]

Copyright byCopyright ⓒ2005, SSISO Community All Rights Reserved.