login
회원가입 IDPW찾기










home > 정보마당 > 사이버강의실
제 목 [실전SQL튜닝] Join 메커니즘의 이해 및 실습
글쓴이 yjan 등록일 2009-03-28 첨부      
내 용 Join 메커니즘의 이해 및 실습

1.
Compile Time : 2009/03/28 14:32:59
Trace File Name : D:\oracle\product\10.2.0\admin\oradb\udump\oradb_ora_4612.trc
Trace Version : Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
Environment : Array Size = 10
Long Size = 80

********************************************************************************

select /*+ ordered use_hash( b a) */ * from dept b , big_emp a
where a.deptno=b.deptno

Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.001 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 70174 2.137 1.894 0 75568 0 701728
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 70176 2.137 1.895 0 75568 0 701728

Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: SCOTT (ID=54)

Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
701728 HASH JOIN (cr=75568 pr=0 pw=0 time=2105996 us)
4 TABLE ACCESS FULL DEPT (cr=7 pr=0 pw=0 time=41 us)
926560 TABLE ACCESS FULL BIG_EMP (cr=75561 pr=0 pw=0 time=1854439 us)


2.
Compile Time : 2009/03/28 14:01:42
Trace File Name : D:\oracle\product\10.2.0\admin\oradb\udump\oradb_ora_4612.trc
Trace Version : Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
Environment : Array Size = 10
Long Size = 80

********************************************************************************

select /*+ ordered use_hash( a b) */ * from big_emp a, dept b
where a.deptno=b.deptno

Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.001 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 70174 2.200 4.185 5022 5832 0 701728
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 70176 2.200 4.186 5022 5832 0 701728

Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: SCOTT (ID=54)

Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
701728 HASH JOIN (cr=5832 pr=5022 pw=5921 time=4212481 us)
926560 TABLE ACCESS FULL BIG_EMP (cr=5824 pr=0 pw=0 time=1853151 us)
4 TABLE ACCESS FULL DEPT (cr=8 pr=0 pw=0 time=65 us)
.



3.


Compile Time : 2009/03/28 14:37:46
Trace File Name : D:\oracle\product\10.2.0\admin\oradb\udump\oradb_ora_4612.trc
Trace Version : Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
Environment : Array Size = 10
Long Size = 80

********************************************************************************

select /*+ ordered use_nl( b a) */ * from dept b ,big_emp a
where a.deptno=b.deptno

Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.001 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 70174 1.700 1.712 0 93048 0 701728
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 70176 1.700 1.713 0 93048 0 701728

Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: SCOTT (ID=54)

Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
701728 NESTED LOOPS (cr=93048 pr=0 pw=0 time=4210426 us)
4 TABLE ACCESS FULL DEPT (cr=10 pr=0 pw=0 time=45 us)
701728 TABLE ACCESS FULL BIG_EMP (cr=93038 pr=0 pw=0 time=1498117 us)



4.

Compile Time : 2009/03/28 14:03:02
Trace File Name : D:\oracle\product\10.2.0\admin\oradb\udump\oradb_ora_4612.trc
Trace Version : Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
Environment : Array Size = 10
Long Size = 80

********************************************************************************

select /*+ use_merge( a b) */ * from big_emp a, dept b
where a.deptno=b.deptno

Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.001 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 70174 2.418 7.930 13631 5832 8 701728
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 70176 2.418 7.931 13631 5832 8 701728

Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: SCOTT (ID=54)

Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
701728 MERGE JOIN (cr=5832 pr=13631 pw=14682 time=13876851 us)
4 TABLE ACCESS BY INDEX ROWID DEPT (cr=8 pr=0 pw=0 time=111 us)
4 INDEX FULL SCAN PK_DEPT (cr=4 pr=0 pw=0 time=66 us)OF PK_DEPT (UNIQUE)
701728 SORT JOIN (cr=5824 pr=13631 pw=14682 time=7636770 us)
926560 TABLE ACCESS FULL BIG_EMP (cr=5824 pr=0 pw=0 time=1853148 us)



5.
Compile Time : 2009/03/28 14:03:26
Trace File Name : D:\oracle\product\10.2.0\admin\oradb\udump\oradb_ora_4612.trc
Trace Version : Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
Environment : Array Size = 10
Long Size = 80

********************************************************************************

select /*+ ordered use_merge( a b) */ * from big_emp a, dept b
where a.deptno=b.deptno

Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.001 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 70174 11.450 13.679 5352 5831 4 701728
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 70176 11.450 13.680 5352 5831 4 701728

Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: SCOTT (ID=54)

Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
701728 MERGE JOIN (cr=5831 pr=5352 pw=6353 time=34551033 us)
766113 SORT JOIN (cr=5824 pr=5352 pw=6353 time=5160631 us)
926560 TABLE ACCESS FULL BIG_EMP (cr=5824 pr=0 pw=0 time=1853147 us)
701728 SORT JOIN (cr=7 pr=0 pw=0 time=6218755 us)
4 TABLE ACCESS FULL DEPT (cr=7 pr=0 pw=0 time=43 us)




6.

Compile Time : 2009/03/28 14:04:31
Trace File Name : D:\oracle\product\10.2.0\admin\oradb\udump\oradb_ora_4612.trc
Trace Version : Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
Environment : Array Size = 10
Long Size = 80

********************************************************************************

select /*+ use_hash( a b) */ * from big_emp a, dept b
where a.deptno=b.deptno
and rownum < 21

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 3 0.000 0.001 0 13 0 20
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 5 0.000 0.002 0 13 0 20

Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: SCOTT (ID=54)

Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
20 COUNT STOPKEY (cr=13 pr=0 pw=0 time=473 us)
20 HASH JOIN (cr=13 pr=0 pw=0 time=463 us)
4 TABLE ACCESS FULL DEPT (cr=7 pr=0 pw=0 time=39 us)
34 TABLE ACCESS FULL BIG_EMP (cr=6 pr=0 pw=0 time=20 us)





7.


Compile Time : 2009/03/28 14:04:59
Trace File Name : D:\oracle\product\10.2.0\admin\oradb\udump\oradb_ora_4612.trc
Trace Version : Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
Environment : Array Size = 10
Long Size = 80

********************************************************************************

select /*+ ordered use_hash( a b) */ * from big_emp a, dept b
where a.deptno=b.deptno
and rownum < 21

Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.001 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 3 0.624 2.576 1581 5829 0 20
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 5 0.624 2.577 1581 5829 0 20

Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: SCOTT (ID=54)

Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
20 COUNT STOPKEY (cr=5829 pr=1581 pw=5859 time=2575943 us)
20 HASH JOIN (cr=5829 pr=1581 pw=5859 time=2575928 us)
926560 TABLE ACCESS FULL BIG_EMP (cr=5824 pr=0 pw=0 time=1853152 us)
2 TABLE ACCESS FULL DEPT (cr=5 pr=0 pw=0 time=67 us)




8.

Compile Time : 2009/03/28 14:38:29
Trace File Name : D:\oracle\product\10.2.0\admin\oradb\udump\oradb_ora_4612.trc
Trace Version : Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
Environment : Array Size = 10
Long Size = 80

********************************************************************************

select /*+ ordered use_nl( a b) */ * from big_emp a, dept b
where a.deptno=b.deptno
and rownum < 21

Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.000 0 0 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.000 0.001 0 29 0 20

Misses in library cache during parse: 0
Optimizer goal: ALL_ROWS
Parsing user: SCOTT (ID=54)

Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
20 COUNT STOPKEY (cr=29 pr=0 pw=0 time=82 us)
20 NESTED LOOPS (cr=29 pr=0 pw=0 time=70 us)
34 TABLE ACCESS FULL BIG_EMP (cr=6 pr=0 pw=0 time=196 us)
20 TABLE ACCESS BY INDEX ROWID DEPT (cr=23 pr=0 pw=0 time=581 us)
20 INDEX UNIQUE SCAN PK_DEPT (cr=3 pr=0 pw=0 time=234 us)OF PK_DEPT (UNIQUE)



9.
Compile Time : 2009/03/28 14:39:01
Trace File Name : D:\oracle\product\10.2.0\admin\oradb\udump\oradb_ora_4612.trc
Trace Version : Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
Environment : Array Size = 10
Long Size = 80

********************************************************************************

select /*+ ordered use_nl( b a) */ * from dept b , big_emp a
where a.deptno=b.deptno
and rownum < 21

Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.000 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 3 0.000 0.000 0 17 0 20
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 5 0.000 0.000 0 17 0 20

Misses in library cache during parse: 0
Optimizer goal: ALL_ROWS
Parsing user: SCOTT (ID=54)

Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
20 COUNT STOPKEY (cr=17 pr=0 pw=0 time=111 us)
20 TABLE ACCESS BY INDEX ROWID BIG_EMP (cr=17 pr=0 pw=0 time=86 us)
21 NESTED LOOPS (cr=10 pr=0 pw=0 time=970 us)
1 TABLE ACCESS FULL DEPT (cr=5 pr=0 pw=0 time=29 us)
20 INDEX RANGE SCAN IDX1 (cr=5 pr=0 pw=0 time=17 us)OF IDX1 (NONUNIQUE)



10.
Compile Time : 2009/03/28 14:06:52
Trace File Name : D:\oracle\product\10.2.0\admin\oradb\udump\oradb_ora_4612.trc
Trace Version : Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
Environment : Array Size = 10
Long Size = 80

********************************************************************************

select /*+ ordered use_merge( b a) */ * from dept b , big_emp a
where a.deptno=b.deptno
and rownum < 21

Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.001 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 3 0.796 7.377 8479 5826 8 20
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 5 0.796 7.378 8479 5826 8 20

Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: SCOTT (ID=54)

Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
20 COUNT STOPKEY (cr=5826 pr=8479 pw=14682 time=7376306 us)
20 MERGE JOIN (cr=5826 pr=8479 pw=14682 time=7376294 us)
1 TABLE ACCESS BY INDEX ROWID DEPT (cr=2 pr=0 pw=0 time=28 us)
1 INDEX FULL SCAN PK_DEPT (cr=1 pr=0 pw=0 time=15 us)OF PK_DEPT (UNIQUE)
20 SORT JOIN (cr=5824 pr=8479 pw=14682 time=7376243 us)
926560 TABLE ACCESS FULL BIG_EMP (cr=5824 pr=0 pw=0 time=1853144 us)




11.

Compile Time : 2009/03/28 14:07:08
Trace File Name : D:\oracle\product\10.2.0\admin\oradb\udump\oradb_ora_4612.trc
Trace Version : Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
Environment : Array Size = 10
Long Size = 80

********************************************************************************

select /*+ ordered use_merge( a b) */ * from big_emp a , dept b
where a.deptno=b.deptno
and rownum < 21

Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.001 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 3 0.952 2.489 181 5831 4 20
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 5 0.952 2.491 181 5831 4 20

Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: SCOTT (ID=54)

Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
20 COUNT STOPKEY (cr=5831 pr=181 pw=6353 time=2488789 us)
20 MERGE JOIN (cr=5831 pr=181 pw=6353 time=2488777 us)
19412 SORT JOIN (cr=5824 pr=181 pw=6353 time=2366020 us)
926560 TABLE ACCESS FULL BIG_EMP (cr=5824 pr=0 pw=0 time=1853149 us)
20 SORT JOIN (cr=7 pr=0 pw=0 time=102423 us)
4 TABLE ACCESS FULL DEPT (cr=7 pr=0 pw=0 time=79 us)
서울 구로구 구로동 197-10 이앤씨벤처드림타워2차 12층 1202, 1209호 (주)오엔제이시스템즈,오라클자바교육학원
대표자명 : 이 종 철 | 사업자번호 : 107-86-88432 | 전화:070-7555-4760~1 | 팩스 :02-837-4760
통신판매업 신고번호 : 제2009-서울구로-0447호
Copyright ⓒ oraclejava.co.kr All rights reserved.