728x90
10046 trace - level별
** LEVEL 1 : 일반적인 SQL TRACE (SQL_TRACE=TRUE) 정보 제공
SELECT E.ENAME, E.DEPTNO, D.DNAME
FROM SCOTT.EMP E, SCOTT.DEPT D
WHERE E.DEPTNO = D.DEPTNO ;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 18 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 15 0 14
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 33 0 14
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
14 14 14 HASH JOIN (cr=15 pr=0 pw=0 time=325 us starts=1 cost=6 size=588 card=14)
4 4 4 TABLE ACCESS FULL DEPT (cr=7 pr=0 pw=0 time=15 us starts=1 cost=3 size=88 card=4)
14 14 14 TABLE ACCESS FULL EMP (cr=8 pr=0 pw=0 time=8 us starts=1 cost=3 size=280 card=14)** LEVEL 4 : SQL TRACE 정보 + BIND 정보 제공
** Bind 변수값을 확인하기 위해서는 직접 TRC파일을 검색하여야 한다
SELECT E.ENAME, E.DEPTNO, D.DNAME
FROM SCOTT.EMP E, SCOTT.DEPT D
WHERE E.DEPTNO = D.DEPTNO ;
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.00 8 10 0 14
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 8 10 0 14
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
14 14 14 MERGE JOIN (cr=10 pr=8 pw=0 time=1955 us starts=1 cost=6 size=308 card=14)
4 4 4 TABLE ACCESS BY INDEX ROWID DEPT (cr=4 pr=2 pw=0 time=761 us starts=1 cost=2 size=52 card=4)
4 4 4 INDEX FULL SCAN PK_DEPT (cr=2 pr=1 pw=0 time=454 us starts=1 cost=1 size=0 card=4)(object id 24075)
14 14 14 SORT JOIN (cr=6 pr=6 pw=0 time=1207 us starts=4 cost=4 size=126 card=14)
14 14 14 TABLE ACCESS FULL EMP (cr=6 pr=6 pw=0 time=1116 us starts=1 cost=3 size=126 card=14)
BIND 변수
-- BIND 변수 값 설정
-- BIND 변수 값 확인
** LEVEL 8 : SQL TRACE 정보 + WAIT EVENT 정보 제공
SELECT E.ENAME, E.DEPTNO, D.DNAME
FROM SCOTT.EMP E, SCOTT.DEPT D
WHERE E.DEPTNO = D.DEPTNO ;
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.00 0 10 0 14
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 10 0 14
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
14 14 14 MERGE JOIN (cr=10 pr=0 pw=0 time=166 us starts=1 cost=6 size=308 card=14)
4 4 4 TABLE ACCESS BY INDEX ROWID DEPT (cr=4 pr=0 pw=0 time=78 us starts=1 cost=2 size=52 card=4)
4 4 4 INDEX FULL SCAN PK_DEPT (cr=2 pr=0 pw=0 time=38 us starts=1 cost=1 size=0 card=4)(object id 24075)
14 14 14 SORT JOIN (cr=6 pr=0 pw=0 time=103 us starts=4 cost=4 size=126 card=14)
14 14 14 TABLE ACCESS FULL EMP (cr=6 pr=0 pw=0 time=52 us starts=1 cost=3 size=126 card=14)
-- WAIT EVENT 정보
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
SQL*Net message from client 2 14.03 14.03** LEVEL 12 : SQL TRACE 정보 + BIND 정보 + WAIT EVENT 정보 제공
SELECT E.ENAME, E.DEPTNO, D.DNAME
FROM SCOTT.EMP E, SCOTT.DEPT D
WHERE E.DEPTNO = D.DEPTNO ;
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.00 0 10 0 14
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 10 0 14
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
14 14 14 MERGE JOIN (cr=10 pr=0 pw=0 time=119 us starts=1 cost=6 size=308 card=14)
4 4 4 TABLE ACCESS BY INDEX ROWID DEPT (cr=4 pr=0 pw=0 time=63 us starts=1 cost=2 size=52 card=4)
4 4 4 INDEX FULL SCAN PK_DEPT (cr=2 pr=0 pw=0 time=27 us starts=1 cost=1 size=0 card=4)(object id 24075)
14 14 14 SORT JOIN (cr=6 pr=0 pw=0 time=73 us starts=4 cost=4 size=126 card=14)
14 14 14 TABLE ACCESS FULL EMP (cr=6 pr=0 pw=0 time=34 us starts=1 cost=3 size=126 card=14)
-- WAIT EVENT정보
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
SQL*Net message from client 2 200.66 200.66
BIND 변수
-- BIND 변수 값 설정
-- BIND 변수 값 확인
728x90
'Dev > Oracle' 카테고리의 다른 글
| [Oracle] Database shutdown Hang이 걸렸을 때 / shutdown abort 후 ORA-00265 (0) | 2022.08.28 |
|---|---|
| [Oracle] RMAN으로 아카이브 로그 삭제하기 (0) | 2022.08.20 |
| [Oracle] 10046 TRACE - 인덱스 사용 (0) | 2022.07.21 |
| [Oracle] 실행 계획 - EXPLAIN PLAN, SET AUTOTRACE (0) | 2022.07.20 |
| [Oracle] Oracle Database 생성 (0) | 2022.07.14 |
댓글