| TEST 준비 스크립트 ( 기본적인 사용법 ) |
|---|
| {CODE:SQL} SQL> select * from v$version where rownum <= 1 2 ; |
BANNER
SQL> CREATE TABLE T1( C1 INT, C2 CHAR(10) );
테이블이 생성되었습니다.
SQL> INSERT INTO T1
2 SELECT LEVEL, 'DUMMY'
3 FROM DUAL
4 CONNECT BY LEVEL <= 10000;
10000 개의 행이 만들어졌습니다.
SQL> COMMIT;
커밋이 완료되었습니다.
SQL> CREATE INDEX T1_NL ON T1( C1 );
인덱스가 생성되었습니다.
SQL> SELECT COUNT( * ) CNT
2 FROM T1
3 WHERE C1 = 1;
CNT
SQL>
| {CODE} | 기본 실행 계획 | |
|---|---|---|
| {CODE:SQL} SQL> EXPLAIN PLAN FOR 2 SELECT * 3 FROM T1 4 WHERE C1 = 1 AND C2 = 'DUMMY'; WHERE C1 = 1 AND C2 = 'DUMMY' * 4행에 오류: ORA-00604: 순환 SQL 레벨 1 에 오류가 발생했습니다 ORA-00001: 무결성 제약 조건(JLIVE.PLAN_INDEX)에 위배됩니다 | {CODE} | 어쭈 =_= |
| {CODE:SQL} |
SELECT * FROM DBA_SYNONYMS WHERE SYNONYM_NAME LIKE '%PLAN%';
truncate table PLAN_TABLE;
SQL> EXPLAIN PLAN FOR
2 SELECT *
3 FROM T1
4 WHERE C1 = 1 AND C2 = 'DUMMY';
해석되었습니다.
SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY );
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) |
| 0 | SELECT STATEMENT | 1 | 25 | 2 (0) | |
| TABLE ACCESS BY INDEX ROWID | T1 | 1 | 25 | 2 (0) |
| INDEX RANGE SCAN | T1_NL | 1 | 1 (0) |
Predicate Information (identified by operation id):
1 - filter("C2"='DUMMY')
2 - access("C1"=1)
Note
18 개의 행이 선택되었습니다.
SQL> EXPLAIN PLAN FOR
2 SELECT *
3 FROM T1
4 WHERE C1 = 1 AND C2 = 'DUMMY';
WHERE C1 = 1 AND C2 = 'DUMMY'
*
4행에 오류:
ORA-00604: 순환 SQL 레벨 1 에 오류가 발생했습니다
ORA-00001: 무결성 제약 조건(JLIVE.PLAN_INDEX)에 위배됩니다
SQL> DROP TABLE PLAN_TABLE PURGE;
테이블이 삭제되었습니다.
SQL>
SQL> @?/rdbms/admin/utlxplan.sql
테이블이 생성되었습니다.
SQL> EXPLAIN PLAN FOR
2 SELECT *
3 FROM T1
4 WHERE C1 = 1 AND C2 = 'DUMMY';
해석되었습니다.
SQL> SELECT * FROM TABLE ( DBMS_XPLAN.DISPLAY );
Plan hash value: 2154509930
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
| 0 | SELECT STATEMENT | 1 | 25 | 2 (0) | 00:00:01 | |
| TABLE ACCESS BY INDEX ROWID | T1 | 1 | 25 | 2 (0) | 00:00:01 |
| INDEX RANGE SCAN | T1_NL | 1 | 1 (0) | 00:00:01 |
Predicate Information (identified by operation id):
1 - filter("C2"='DUMMY')
2 - access("C1"=1)
Note
19 개의 행이 선택되었습니다.
| {CODE} * Row Source Operation을 보여준다. Index t1_nl을 INDEX RANGE SCAN으로 Access하는 실행 계획이 수립될 것임을 보여준다. * Predicate 정보를 보여준다. 2번 Operation, 즉 Index t1_nl에 대한 RS 단계에서는 access("C1" = 1 ) Predicate가 사용되었다. Index Access를 통해서 걸러진 Data는 1번 단계, 즉 TABLE ACCESS BY INDEX ROWID Operation에서 filter("C2" = 'dummy' ) Predicate를 이용해 다시 Fitering 된다. Predicate 정보는 이를 이해하지 못하면 Execution Plan의 절반 밖에 이해하지 못한다고 할 정도로 중요한 정보이다. * Note 정보를 통해 부가적으로 필요한 정보를 제공한다. 이 예제에서는 Dynamic Sampling이 사용되었음을 알려준다. Orcle 10g에서는 통계 정보가 없는 Table에 대해서 Dynamic Sampling을 수행한다. * Access Predicate는 Access Type을 결정하는데 사용되는 Predicate( 조건 )를 의미한다. 더 정확하게 말하면 실제 Block을 읽기 전에 어떤 방법으로 Block을 읽을 것인가를 결정한다는 의미이다. 따라서 Index Lookup 이나 Join 등은 Access Predicate로 표현된다. * Filter Predicate는 실제 Block을 읽은 후 Data를 걸러 내기 위해 사용되는 Predicate( 조건 )를 의미한다. | 그럼 통계 정보가 있다면.?? =_= |
|---|---|
| {CODE} |
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T1');
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> EXPLAIN PLAN FOR
2 SELECT *
3 FROM T1
4 WHERE C1 = 1 AND C2 = 'DUMMY';
해석되었습니다.
SQL> SELECT * FROM TABLE ( DBMS_XPLAN.DISPLAY );
PLAN_TABLE_OUTPUT
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
| 0 | SELECT STATEMENT | 1 | 14 | 2 (0) | 00:00:01 | |
| TABLE ACCESS BY INDEX ROWID | T1 | 1 | 14 | 2 (0) | 00:00:01 |
| INDEX RANGE SCAN | T1_NL | 1 | 1 (0) | 00:00:01 |
Predicate Information (identified by operation id):
1 - filter("C2"='DUMMY')
2 - access("C1"=1)
15 개의 행이 선택되었습니다.
| {CODE} | 그럼 유니크 인댁스를 사용한다면..?? =_= |
|---|---|
| {CODE} SQL> CREATE TABLE T2( C1 INT, C2 CHAR(10) ); |
테이블이 생성되었습니다.
SQL> INSERT INTO T2
2 SELECT LEVEL, 'DUMMY'
3 FROM DUAL
4 CONNECT BY LEVEL <= 10000;
10000 개의 행이 만들어졌습니다.
SQL> CREATE UNIQUE INDEX T1_UNIQUE_INDEX ON T2( C1 );
인덱스가 생성되었습니다.
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T2');
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> SELECT /*+ gather_plan_statistics /
2 FROM T1
3 WHERE C1 = 1 AND C2 = 'DUMMY';
C1 C2
SQL> select * from table( dbms_xplan.display_cursor( null, null, 'ALLSTATS LAST'));
SQL_ID 4p1gp2192gq0k, child number 0
Plan hash value: 2154509930
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
| TABLE ACCESS BY INDEX ROWID | T1 | 1 | 1 | 1 | 00:00:00.01 | 4 | 2 |
| INDEX RANGE SCAN | T1_NL | 1 | 1 | 1 | 00:00:00.01 | 3 | 2 |
Predicate Information (identified by operation id):
1 - filter("C2"='DUMMY')
2 - access("C1"=1)
19 개의 행이 선택되었습니다.
SQL>
SQL> SAVE XPLAN_C
file XPLAN_C.sql(이)가 생성되었습니다
SQL>
SQL> SELECT /*+ gather_plan_statistics /
2 FROM T2
3 WHERE C1 = 1 AND C2 = 'DUMMY';
C1 C2
SQL> @XPLAN_C
SQL_ID 0fnqn7r64jg7m, child number 0
Plan hash value: 3273381761
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
| TABLE ACCESS BY INDEX ROWID | T2 | 1 | 1 | 1 | 00:00:00.01 | 3 |
| INDEX UNIQUE SCAN | T1_UNIQUE_INDEX | 1 | 1 | 1 | 00:00:00.01 | 2 |
Predicate Information (identified by operation id):
1 - filter("C2"='DUMMY')
2 - access("C1"=1)
| {CODE} |
| 준비 |
|---|
| {CODE} SQL> DROP TABLE T1 PURGE; SQL> DROP TABLE T2 PURGE; |
SQL>
SQL> CREATE TABLE T1 ( C1 INT, C2 INT )
QL> CREATE TABLE T2 ( C1 INT, C2 INT );
SQL>
SQL> CREATE INDEX T1_N1 ON T1 ( C1 );
SQL> CREATE INDEX T1_N2 ON T2 ( C1 );
SQL> INSERT INTO T1 SELECT LEVEL, LEVEL
2 FROM DUAL
3 CONNECT BY LEVEL <= 1000;
SQL> INSERT INTO T2 SELECT LEVEL, LEVEL
2 FROM DUAL
3 CONNECT BY LEVEL <= 1000;
| {CODE} | NL |
|---|---|
| {CODE} SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T1'); SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T2'); |
SQL> EXPLAIN PLAN FOR
2 SELECT /*+ USE_NL( T1 T2 ) */ *
3 FROM T1
4 , T2
5 WHERE T1.C1 = T2.C1
6 AND T1.C2 = 1;
해석되었습니다.
SQL> SELECT * FROM TABLE ( DBMS_XPLAN.DISPLAY );
Plan hash value: 1090018354
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
| 0 | SELECT STATEMENT | 1 | 14 | 5 (0) | 00:00:01 | |
| 1 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 7 | 2 (0) | 00:00:01 |
| 2 | NESTED LOOPS | 1 | 14 | 5 (0) | 00:00:01 | |
| TABLE ACCESS FULL | T1 | 1 | 7 | 3 (0) | 00:00:01 |
| INDEX RANGE SCAN | T1_N2 | 1 | 1 (0) | 00:00:01 |
Predicate Information (identified by operation id):
3 - filter("T1"."C2"=1)
4 - access("T1"."C1"="T2"."C1")
17 개의 행이 선택되었습니다.
SQL> SAVE @PLAN
| {CODE} * NL JOIN에서는 후행 Table 에 대한 Access 단계( 4번 )dptj Access Predicate가 표현되는 것을 알 수 있다. Join 단계인 2번이 아니라 4번 단계에서 Access Predicate정보가 출력되는 것에 주의해야 한다. Join에 참여하지 못하는 Column에 대한 조건( 3번 )은 Filter Predicate로 표현된다. | Hash Join |
|---|---|
| {CODE} SQL> EXPLAIN PLAN FOR 2 SELECT /*+ USE_HASH( T1 T2 ) */ * 3 FROM T1 4 , T2 5 WHERE T1.C1 = T2.C1 6 AND T1.C2 = 1; |
SQL> SELECT * FROM TABLE ( DBMS_XPLAN.DISPLAY );
Plan hash value: 1838229974
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
| 0 | SELECT STATEMENT | 1 | 14 | 7 (15) | 00:00:01 | |
| HASH JOIN | 1 | 14 | 7 (15) | 00:00:01 | |
| TABLE ACCESS FULL | T1 | 1 | 7 | 3 (0) | 00:00:01 |
| 3 | TABLE ACCESS FULL | T2 | 1000 | 7000 | 3 (0) | 00:00:01 |
Predicate Information (identified by operation id):
1 - access("T1"."C1"="T2"."C1")
2 - filter("T1"."C2"=1)
| {CODE} |
| DMBS_XPLAN.DISPLAY Function이 제공하는 여러가지 Parameter.. |
|---|
| {CODE} SQL> EXPLAIN PLAN FOR 2 SELECT * 3 FROM T1 4 WHERE C1 = 1 AND C2 = 'DUMMY'; |
해석되었습니다.
SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY );
Plan hash value: 1420382924
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
| 0 | SELECT STATEMENT | 1 | 7 | 2 (0) | 00:00:01 | |
| TABLE ACCESS BY INDEX ROWID | T1 | 1 | 7 | 2 (0) | 00:00:01 |
| INDEX RANGE SCAN | T1_N1 | 1 | 1 (0) | 00:00:01 |
Predicate Information (identified by operation id):
1 - filter("C2"=TO_NUMBER('DUMMY'))
2 - access("C1"=1)
15 개의 행이 선택되었습니다.
SQL> EXPLAIN PLAN FOR
2 SELECT *
3 FROM T1
4 WHERE C1 = 1 AND C2 = 'DUMMY';
해석되었습니다.
SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY( 'PLAN_TABLE', NULL, 'TYPICAL', NULL ));
Plan hash value: 1420382924
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
| 0 | SELECT STATEMENT | 1 | 7 | 2 (0) | 00:00:01 | |
| TABLE ACCESS BY INDEX ROWID | T1 | 1 | 7 | 2 (0) | 00:00:01 |
| INDEX RANGE SCAN | T1_N1 | 1 | 1 (0) | 00:00:01 |
Predicate Information (identified by operation id):
1 - filter("C2"=TO_NUMBER('DUMMY'))
2 - access("C1"=1)
15 개의 행이 선택되었습니다.
SQL> EXPLAIN PLAN
2 SET STATEMENT_ID = 'TEST' FOR
3 SELECT *
4 FROM T1
5 WHERE C1 = 1 AND C2 = 'DUMMY';
해석되었습니다.
SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY( 'PLAN_TABLE', 'TEST', 'TYPICAL', NULL ));
Plan hash value: 1420382924
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
| 0 | SELECT STATEMENT | 1 | 7 | 2 (0) | 00:00:01 | |
| TABLE ACCESS BY INDEX ROWID | T1 | 1 | 7 | 2 (0) | 00:00:01 |
| INDEX RANGE SCAN | T1_N1 | 1 | 1 (0) | 00:00:01 |
Predicate Information (identified by operation id):
1 - filter("C2"=TO_NUMBER('DUMMY'))
2 - access("C1"=1)
15 개의 행이 선택되었습니다.
| {CODE} |
{CODE}
SQL> EXPLAIN PLAN FOR
2 SELECT *
3 FROM T1
4 WHERE C1 = 1 AND C2 = 'DUMMY';
해석되었습니다.
SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY( 'PLAN_TABLE', NULL, 'BASIC' ) );
Plan hash value: 1420382924
| Id | Operation | Name |
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID | T1 |
| 2 | INDEX RANGE SCAN | T1_N1 |
{CODE}
{CODE}
SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY( 'PLAN_TABLE', NULL, 'TYPICAL' ) );
Plan hash value: 1420382924
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
| 0 | SELECT STATEMENT | 1 | 7 | 2 (0) | 00:00:01 | |
| TABLE ACCESS BY INDEX ROWID | T1 | 1 | 7 | 2 (0) | 00:00:01 |
| INDEX RANGE SCAN | T1_N1 | 1 | 1 (0) | 00:00:01 |
Predicate Information (identified by operation id):
1 - filter("C2"=TO_NUMBER('DUMMY'))
2 - access("C1"=1)
{CODE}
| All Format |
|---|
| {CODE} SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY( 'PLAN_TABLE', NULL, 'ALL' ) ); |
Plan hash value: 1420382924
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
| 0 | SELECT STATEMENT | 1 | 7 | 2 (0) | 00:00:01 | |
| TABLE ACCESS BY INDEX ROWID | T1 | 1 | 7 | 2 (0) | 00:00:01 |
| INDEX RANGE SCAN | T1_N1 | 1 | 1 (0) | 00:00:01 |
Query Block Name / Object Alias (identified by operation id):
1 - SEL$1 / T1@SEL$1
2 - SEL$1 / T1@SEL$1
Predicate Information (identified by operation id):
1 - filter("C2"=TO_NUMBER('DUMMY'))
2 - access("C1"=1)
Column Projection Information (identified by operation id):
1 - "C1"NUMBER,22, "C2"NUMBER,22
2 - "T1".ROWID[ROWID,10], "C1"NUMBER,22
27 개의 행이 선택되었습니다.
| {CODE} | QB_NAME Hint : Query Block 명을 직접 조작 할 수 있다. |
|---|---|
| {CODE} SQL> EXPLAIN PLAN FOR 2 SELECT /*+ QB_NAME( X ) */ * 3 FROM T1 4 WHERE C1 = 1 AND C2 = 'DUMMY'; |
해석되었습니다.
SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY( 'PLAN_TABLE', NULL, 'ALL' ) );
Plan hash value: 1420382924
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
| 0 | SELECT STATEMENT | 1 | 7 | 2 (0) | 00:00:01 | |
| TABLE ACCESS BY INDEX ROWID | T1 | 1 | 7 | 2 (0) | 00:00:01 |
| INDEX RANGE SCAN | T1_N1 | 1 | 1 (0) | 00:00:01 |
Query Block Name / Object Alias (identified by operation id):
1 - X / T1@X <--
2 - X / T1@X
Predicate Information (identified by operation id):
1 - filter("C2"=TO_NUMBER('DUMMY'))
2 - access("C1"=1)
PLAN_TABLE_OUTPUT
1 - "C1"NUMBER,22, "C2"NUMBER,22
2 - "T1".ROWID[ROWID,10], "C1"NUMBER,22
27 개의 행이 선택되었습니다.
| {CODE} | 그럼 한번 해볼까나.?? =_= 안되네 음.. |
|---|---|
| {CODE} SQL> EXPLAIN PLAN FOR 2 SELECT /*+ FULL( @X ) NO_MERGE( A ) */ * 3 FROM ( 4 SELECT /*+ QB_NAME( X ) */ * 5 FROM T1 6 WHERE C1 = 1 AND C2 = '1' --gather_plan_statistics 7 ) A; |
해석되었습니다.
SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY( 'PLAN_TABLE', NULL, 'ALL' ) );
Plan hash value: 1420382924
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
| 0 | SELECT STATEMENT | 1 | 7 | 2 (0) | 00:00:01 | |
| TABLE ACCESS BY INDEX ROWID | T1 | 1 | 7 | 2 (0) | 00:00:01 |
| INDEX RANGE SCAN | T1_N1 | 1 | 1 (0) | 00:00:01 |
Query Block Name / Object Alias (identified by operation id):
1 - SEL$6CE2C157 / T1@X
2 - SEL$6CE2C157 / T1@X
Predicate Information (identified by operation id):
1 - filter("C2"=1)
2 - access("C1"=1)
Column Projection Information (identified by operation id):
1 - "C1"NUMBER,22, "C2"NUMBER,22
2 - "T1".ROWID[ROWID,10], "C1"NUMBER,22
27 개의 행이 선택되었습니다.
| {CODE} | ㅋㅋㅋ |
|---|---|
| {CODE} SQL> EXPLAIN PLAN FOR 2 SELECT /*+ QB_NAME( MAIN )NO_MERGE( A@MAIN ) / 3 FROM ( 4 SELECT /*+ QB_NAME( X ) */ * 5 FROM T1 6 WHERE C1 = 1 AND C2 = '1' 7 ) A 8 ; |
해석되었습니다.
SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY( 'PLAN_TABLE', NULL, 'ALL' ) );
Plan hash value: 2394988879
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
| 0 | SELECT STATEMENT | 1 | 26 | 2 (0) | 00:00:01 | |
| 1 | VIEW | 1 | 26 | 2 (0) | 00:00:01 | |
| TABLE ACCESS BY INDEX ROWID | T1 | 1 | 7 | 2 (0) | 00:00:01 |
| INDEX RANGE SCAN | T1_N1 | 1 | 1 (0) | 00:00:01 |
Query Block Name / Object Alias (identified by operation id):
1 - X / A@MAIN
2 - X / T1@X
3 - X / T1@X
Predicate Information (identified by operation id):
2 - filter("C2"=1)
3 - access("C1"=1)
Column Projection Information (identified by operation id):
1 - "A"."C1"NUMBER,22, "A"."C2"NUMBER,22
2 - "C1"NUMBER,22, "C2"NUMBER,22
3 - "T1".ROWID[ROWID,10], "C1"NUMBER,22
| {CODE} | InLine View FULL 안되네 음... |
|---|---|
| {CODE} SQL> EXPLAIN PLAN FOR 2 SELECT /*+ QB_NAME( MAIN ) FULL( T1@SUB) NO_MERGE( A@MAIN ) / 3 FROM ( 4 SELECT /*+ QB_NAME( SUB ) */ * 5 FROM T1 6 WHERE C1 = 1 AND C2 = '1' 7 ) A 8 ; |
해석되었습니다.
SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY( 'PLAN_TABLE', NULL, 'ALL' ) );
Plan hash value: 2394988879
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
| 0 | SELECT STATEMENT | 1 | 26 | 2 (0) | 00:00:01 | |
| 1 | VIEW | 1 | 26 | 2 (0) | 00:00:01 | |
| TABLE ACCESS BY INDEX ROWID | T1 | 1 | 7 | 2 (0) | 00:00:01 |
| INDEX RANGE SCAN | T1_N1 | 1 | 1 (0) | 00:00:01 |
Query Block Name / Object Alias (identified by operation id):
1 - SUB / A@MAIN
2 - SUB / T1@SUB
3 - SUB / T1@SUB
Predicate Information (identified by operation id):
2 - filter("C2"=1)
3 - access("C1"=1)
Column Projection Information (identified by operation id):
1 - "A"."C1"NUMBER,22, "A"."C2"NUMBER,22
2 - "C1"NUMBER,22, "C2"NUMBER,22
3 - "T1".ROWID[ROWID,10], "C1"NUMBER,22
30 개의 행이 선택되었습니다.
| {CODE} | NO_UNNEST |
|---|---|
| {CODE} SQL> EXPLAIN PLAN FOR 2 SELECT /*+ NO_UNNEST( @SUB ) */ * 3 FROM T1 4 WHERE C1 IN ( SELECT /*+ QB_NAME( SUB ) */ C1 FROM T1 WHERE C1 BETWEEN 1 AND 3 ) 5 AND C2 = '1'; |
해석되었습니다.
SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY( 'PLAN_TABLE', NULL, 'ALL' ) );
Plan hash value: 1322727408
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
| 0 | SELECT STATEMENT | 1 | 7 | 4 (0) | 00:00:01 | |
| FILTER | |||||
| TABLE ACCESS FULL | T1 | 1 | 7 | 3 (0) | 00:00:01 |
| FILTER | |||||
| INDEX RANGE SCAN | T1_N1 | 1 | 4 | 1 (0) | 00:00:01 |
Query Block Name / Object Alias (identified by operation id):
1 - SEL$1
2 - SEL$1 / T1@SEL$1
3 - SUB
4 - SUB / T1@SUB
Predicate Information (identified by operation id):
1 - filter( EXISTS (SELECT /*+ NO_UNNEST QB_NAME ("SUB") */ 0 FROM
"T1" "T1" WHERE :B1<=3 AND :B2>=1 AND "C1"=:B3 AND "C1"<=3 AND "C1">=1))
2 - filter("C2"=1)
3 - filter(:B1<=3 AND :B2>=1)
4 - access("C1"=:B1)
filter("C1"<=3 AND "C1">=1)
Column Projection Information (identified by operation id):
1 - "C1"NUMBER,22, "C2"NUMBER,22
2 - "C1"NUMBER,22, "C2"NUMBER,22
| {CODE} | UNNEST |
|---|---|
| {CODE} SQL> EXPLAIN PLAN FOR 2 SELECT /*+ UNNEST( @SUB ) */ * 3 FROM T1 4 WHERE C1 IN ( SELECT /*+ QB_NAME( SUB ) */ C1 FROM T1 WHERE C1 BETWEEN 1 AND 3 ) 5 AND C2 = '1'; |
해석되었습니다.
SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY( 'PLAN_TABLE', NULL, 'ALL' ) );
Plan hash value: 3994169173
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
| 0 | SELECT STATEMENT | 1 | 11 | 4 (0) | 00:00:01 | |
| 1 | NESTED LOOPS SEMI | 1 | 11 | 4 (0) | 00:00:01 | |
| TABLE ACCESS BY INDEX ROWID | T1 | 1 | 7 | 3 (0) | 00:00:01 |
| INDEX RANGE SCAN | T1_N1 | 3 | 2 (0) | 00:00:01 | |
| INDEX RANGE SCAN | T1_N1 | 3 | 12 | 1 (0) | 00:00:01 |
Query Block Name / Object Alias (identified by operation id):
1 - SEL$0C6FB14C
2 - SEL$0C6FB14C / T1@SEL$1
3 - SEL$0C6FB14C / T1@SEL$1
4 - SEL$0C6FB14C / T1@SUB
Predicate Information (identified by operation id):
2 - filter("C2"=1)
3 - access("C1">=1 AND "C1"<=3)
4 - access("C1"="C1")
filter("C1"<=3 AND "C1">=1)
Column Projection Information (identified by operation id):
1 - (#keys=0) "C1"NUMBER,22, "C2"NUMBER,22
2 - "C1"NUMBER,22, "C2"NUMBER,22
3 - "T1".ROWID[ROWID,10], "C1"NUMBER,22
34 개의 행이 선택되었습니다.
SQL> EXPLAIN PLAN FOR
2 SELECT /*+ UNNEST( @SUB ) HASH_SJ( @SUB ) */ *
3 FROM T1
4 WHERE C1 IN ( SELECT /*+ QB_NAME( SUB ) */ C1 FROM T1 WHERE C1 BETWEEN 1 AND 3 )
5 AND C2 = '1';
해석되었습니다.
SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY( 'PLAN_TABLE', NULL, 'ALL' ) );
Plan hash value: 2980426711
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
| 0 | SELECT STATEMENT | 1 | 11 | 6 (17) | 00:00:01 | |
| HASH JOIN SEMI | 1 | 11 | 6 (17) | 00:00:01 | |
| TABLE ACCESS BY INDEX ROWID | T1 | 1 | 7 | 3 (0) | 00:00:01 |
| INDEX RANGE SCAN | T1_N1 | 3 | 2 (0) | 00:00:01 | |
| INDEX RANGE SCAN | T1_N1 | 3 | 12 | 2 (0) | 00:00:01 |
Query Block Name / Object Alias (identified by operation id):
1 - SEL$0C6FB14C
2 - SEL$0C6FB14C / T1@SEL$1
3 - SEL$0C6FB14C / T1@SEL$1
4 - SEL$0C6FB14C / T1@SUB
Predicate Information (identified by operation id):
1 - access("C1"="C1")
2 - filter("C2"=1)
3 - access("C1">=1 AND "C1"<=3)
4 - access("C1">=1 AND "C1"<=3)
Column Projection Information (identified by operation id):
1 - (#keys=1) "C1"NUMBER,22, "C2"NUMBER,22
2 - "C1"NUMBER,22, "C2"NUMBER,22
3 - "T1".ROWID[ROWID,10], "C1"NUMBER,22
4 - "C1"NUMBER,22
SQL>
| {CODE} |
{CODE}
SQL> EXPLAIN PLAN FOR
2 SELECT *
3 FROM T1
4 WHERE C1 = 1 AND C2 = 'DUMMY'
5 ;
해석되었습니다.
SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY( 'PLAN_TABLE', NULL, 'OUTLINE' ) );
Plan hash value: 1420382924
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
| 0 | SELECT STATEMENT | 1 | 7 | 2 (0) | 00:00:01 | |
| TABLE ACCESS BY INDEX ROWID | T1 | 1 | 7 | 2 (0) | 00:00:01 |
| INDEX RANGE SCAN | T1_N1 | 1 | 1 (0) | 00:00:01 |
Outline Data
/*+
BEGIN_OUTLINE_DATA
INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."C1")) <--
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS <--
OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
1 - filter("C2"=TO_NUMBER('DUMMY'))
2 - access("C1"=1)
28 개의 행이 선택되었습니다.
{CODE}
{CODE}
SQL> EXPLAIN PLAN FOR
2 SELECT *
3 FROM T1
4 WHERE C1 = 1 AND C2 = 'DUMMY'
5 ;
해석되었습니다.
SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY( 'PLAN_TABLE', NULL, 'ADVANCED' ) );
Plan hash value: 1420382924
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
| 0 | SELECT STATEMENT | 1 | 7 | 2 (0) | 00:00:01 | |
| TABLE ACCESS BY INDEX ROWID | T1 | 1 | 7 | 2 (0) | 00:00:01 |
| INDEX RANGE SCAN | T1_N1 | 1 | 1 (0) | 00:00:01 |
Query Block Name / Object Alias (identified by operation id):
1 - SEL$1 / T1@SEL$1
2 - SEL$1 / T1@SEL$1
Outline Data
/*+
BEGIN_OUTLINE_DATA
INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."C1"))
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
1 - filter("C2"=TO_NUMBER('DUMMY'))
2 - access("C1"=1)
Column Projection Information (identified by operation id):
1 - "C1"NUMBER,22, "C2"NUMBER,22
2 - "T1".ROWID[ROWID,10], "C1"NUMBER,22
40 개의 행이 선택되었습니다.
{CODE}
| 기본적인 사용법 ( GATHER_PLAN_STATISTICS + DBMS_XPLAN.DISPLAY_CUROR ) 준비 |
|---|
| {CODE} SQL> CREATE TABLE T_PLAN( C1 VARCHAR2(10)); SQL> CREATE INDEX T_PLAN_IDX ON T_PLAN(C1); |
SQL> INSERT INTO T_PLAN
2 SELECT 'Many1'
3 FROM DUAL
4 CONNECT BY LEVEL <= 10000;
SQL> COMMIT;
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS( USER, 'T_PLAN', CASCADE => TRUE, NO_INVALIDATE => FALSE );
| {CODE} | 변경전 분포도 100% |
|---|---|
| {CODE} SQL> EXPLAIN PLAN FOR 2 SELECT * 3 FROM T_PLAN 4 WHERE C1 = 'Many1'; |
해석되었습니다.
SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY );
Plan hash value: 2521935493
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
| 0 | SELECT STATEMENT | 10000 | 60000 | 6 (0) | 00:00:01 | |
| TABLE ACCESS FULL | T_PLAN | 10000 | 60000 | 6 (0) | 00:00:01 |
Predicate Information (identified by operation id):
1 - filter("C1"='Many1')
| {CODE} | 변경 후 분포도 50% |
|---|---|
| {CODE} SQL> INSERT INTO T_PLAN 2 SELECT 'Many2' 3 FROM DUAL 4 CONNECT BY LEVEL <= 10000; |
SQL> EXPLAIN PLAN FOR
2 SELECT *
3 FROM T_PLAN
4 WHERE C1 = 'Many2';
해석되었습니다.
SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY );
Plan hash value: 1175009887
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
| 0 | SELECT STATEMENT | 1 | 6 | 1 (0) | 00:00:01 | |
| INDEX RANGE SCAN | T_PLAN_IDX | 1 | 6 | 1 (0) | 00:00:01 |
Predicate Information (identified by operation id):
1 - access("C1"='Many2')
13 개의 행이 선택되었습니다.
SQL>
| {CODE} * 통계 정보 생성 이후에 추가된 'Many2' 값을 모르기 때문에 부정확한 실행 계획을 수립함 * 실제 ROW 수에 비해 훨씬 낮은 예측 ROW 수를 가정 함 * ( GATHER_PLAN_STATISTICS + DBMS_XPLAN.DISPLAY_CUROR ) | 실제 플랜 GATHER_PLAN_STATISTICS + DBMS_XPLAN.DISPLAY_CUROR |
|---|---|
| {CODE} SQL> SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * ) 2 FROM T_PLAN 3 WHERE C1 = 'Many2'; |
COUNT(*)
SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( NULL, NULL, 'ALLSTATS LAST' ));
PLAN_TABLE_OUTPUT
Plan hash value: 2787416999
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
| 1 | SORT AGGREGATE | 1 | 1 | 1 | 00:00:00.01 | 34 | |
| INDEX RANGE SCAN | T_PLAN_IDX | 1 | 1 | 10000 | 00:00:00.01 | 34 |
Predicate Information (identified by operation id):
2 - access("C1"='Many2')
| {CODE} * ALLSTATS : Plan Statistics에 있는 모든 정보를 보여 달라는 의미 * LAST : 가장 최근에 수행한 정보만 보여달라는 의미 * 첫번째 NULL : SQL_ID * 두번째 NULL : Child Number를 의미한다. ( 소프트 파싱인경우 라이브러리 캐쉬에 조건 크기에따라 여러개의 라이브러리가 존재 한다. 맞음 ?? ) * E-Rows ( Estimated Row Counts ) : 예측 Row 수 ( 오라클의 옵티마이저는 통계 정보와 SQL 문의 WHERE 조건 값을 참조해서 특정 조건을 만족하는 로우수 계산 ) * A-Rows ( Actual Row Counts ): 실제 Row 수 * A-Time : 실제 수행 시간 * Buffers : Logical Reads Logical Reads는 Buffer Cache를 경유해 읽은 블록 수(이것을 Conventional Path Read라고 합니다)와 Buffer Cache를 경유하지 않고 Process가 직접 읽은 블록 수(이것을 Direct Path Read라고 부릅니다)를 합친 값입니다. 즉, 간단한게 말하면 특정 작업을 수행하는 과정에서(가령 쿼리를 실행하기 위해) 읽는 데이터베이스 블록(Database Block) 수라고 보면 되겠습니다 * Starts : 말 그대로 해당 오퍼레이션이 "시작"된 횟수를 의미합니다. 이 개념은 Nested Loops Join을 생각하시면 쉽게 이해할 수 있습니다. Nested Loops Join은 선행 테이블에서 읽는 로우수만큼 후행 테이블을 탐색하는 구조입니다. 만일 선행 테이블에서 100건이 나온다면 후행 테이블을 100번 액세스하게 됩니다. 이럴 경우에 후행 테이블에서 대한 읽기 작업의 Starts 값이 "100"이 되는 것입니다 | 통계 재생성 |
|---|---|
| {CODE} SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS( USER, 'T_PLAN', CASCADE => TRUE, NO_INVALIDATE => FALSE ); |
SQL> SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * )
2 FROM T_PLAN
3 WHERE C1 = 'Many2';
COUNT(*)
SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( NULL, NULL, 'ALLSTATS LAST' ));
SQL_ID bhxfgwb1bjqn2, child number 0
Plan hash value: 4286448300
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
| 1 | SORT AGGREGATE | 1 | 1 | 1 | 00:00:00.01 | 38 | |
| TABLE ACCESS FULL | T_PLAN | 1 | 10000 | 10000 | 00:00:00.01 | 38 |
Predicate Information (identified by operation id):
2 - filter("C1"='Many2')
19 개의 행이 선택되었습니다.
| {CODE} * E-Rows와 A-Rows값이 일치 하며, 이로 인해 TFS을 선택함. * 참고로, Oracle 11g부터는 SQL*Trace에서도 동일한 기능을 제공한다. 아래 결과를 보면 Tkprof Report의 Row Source Operation에서 실제 일량 외에 Cost, Cardinality 같은 정보를 추가 적으로 제공 ( P.56 맨위 ) |
h3.다양한 출력 Format
| Basic Format |
|---|
| {CODE} SQL> SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * ) 2 FROM T_PLAN 3 WHERE C1 = 'Few'; |
COUNT(*)
SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( NULL, NULL, 'BASIC' ));
PLAN_TABLE_OUTPUT
Plan hash value: 2787416999
| Id | Operation | Name |
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | INDEX RANGE SCAN | T_PLAN_IDX |
15 개의 행이 선택되었습니다.
| {CODE} | Typical Format |
|---|---|
| {CODE} SQL> SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * ) 2 FROM T_PLAN 3 WHERE C1 = 'Few'; |
COUNT(*)
SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( NULL, NULL, 'TYPICAL' ));
PLAN_TABLE_OUTPUT
Plan hash value: 2787416999
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
| 0 | SELECT STATEMENT | 1 (100) | ||||
| 1 | SORT AGGREGATE | 1 | 6 | |||
| INDEX RANGE SCAN | T_PLAN_IDX | 1 | 6 | 1 (0) | 00:00:01 |
Predicate Information (identified by operation id):
2 - access("C1"='Few')
20 개의 행이 선택되었습니다.
| {CODE} | ALL Format |
|---|---|
| {CODE} SQL> SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * ) 2 FROM T_PLAN 3 WHERE C1 = 'Few'; |
COUNT(*)
SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( NULL, NULL, 'ALL' ));
PLAN_TABLE_OUTPUT
Plan hash value: 2787416999
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
| 0 | SELECT STATEMENT | 1 (100) | ||||
| 1 | SORT AGGREGATE | 1 | 6 | |||
| INDEX RANGE SCAN | T_PLAN_IDX | 1 | 6 | 1 (0) | 00:00:01 |
Query Block Name / Object Alias (identified by operation id):
1 - SEL$1
2 - SEL$1 / T_PLAN@SEL$1
Predicate Information (identified by operation id):
2 - access("C1"='Few')
Column Projection Information (identified by operation id):
1 - (#keys=0) COUNT(*)22
31 개의 행이 선택되었습니다.
| {CODE} | ALLSTATS |
|---|---|
| {CODE} SQL> SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * ) 2 FROM T_PLAN 3 WHERE C1 = 'Few'; |
COUNT(*)
SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( NULL, NULL, 'ALLSTATS' ));
PLAN_TABLE_OUTPUT
Plan hash value: 2787416999
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
| 1 | SORT AGGREGATE | 3 | 1 | 3 | 00:00:00.01 | 6 | |
| INDEX RANGE SCAN | T_PLAN_IDX | 3 | 1 | 0 | 00:00:00.01 | 6 |
Predicate Information (identified by operation id):
2 - access("C1"='Few')
18 개의 행이 선택되었습니다.
SQL>
| {CODE} | Allstats Last |
|---|---|
| {CODE} SQL> SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * ) 2 FROM T_PLAN 3 WHERE C1 = 'Few'; |
COUNT(*)
SQL>
SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( NULL, NULL, 'ALLSTATS LAST' ));
PLAN_TABLE_OUTPUT
Plan hash value: 2787416999
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
| 1 | SORT AGGREGATE | 1 | 1 | 1 | 00:00:00.01 | 2 | |
| INDEX RANGE SCAN | T_PLAN_IDX | 1 | 1 | 0 | 00:00:00.01 | 2 |
Predicate Information (identified by operation id):
2 - access("C1"='Few')
18 개의 행이 선택되었습니다.
| {CODE} * Allstats Format : Allstats Format = Iostats Format + Memstats Format | Iostats Format : I/O Statistics 즉, Read/Write 정보를 보여주겠다는 것을 의미 |
|---|---|
| {CODE} SQL> SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * ) 2 FROM T_PLAN 3 WHERE C1 = 'Few'; |
COUNT(*)
SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( NULL, NULL, 'IOSTATS LAST' ));
PLAN_TABLE_OUTPUT
Plan hash value: 2787416999
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
| 1 | SORT AGGREGATE | 1 | 1 | 1 | 00:00:00.01 | 2 | |
| INDEX RANGE SCAN | T_PLAN_IDX | 1 | 1 | 0 | 00:00:00.01 | 2 |
Predicate Information (identified by operation id):
2 - access("C1"='Few')
| {CODE} * Buffers : Logical Reads Logical Reads는 Buffer Cache를 경유해 읽은 블록 수(이것을 Conventional Path Read라고 합니다)와 Buffer Cache를 경유하지 않고 Process가 직접 읽은 블록 수(이것을 Direct Path Read라고 부릅니다)를 합친 값입니다. 즉, 간단한게 말하면 특정 작업을 수행하는 과정에서(가령 쿼리를 실행하기 위해) 읽는 데이터베이스 블록(Database Block) 수라고 보면 되겠습니다 * Read : Physical Reads ( Buffer Cache에 데이터가 존재하지 않아서 Disk I/O를 한 경우 ) * Writes : Physical Writes | Memstats Format : Memory Statistics |
|---|---|
| {CODE} SQL> SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * ) 2 FROM T_PLAN 3 , T_PLAN 4 WHERE ROWNUM <= 100000; |
COUNT(*)
SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( NULL, NULL, 'MEMSTATS LAST' ));
PLAN_TABLE_OUTPUT
Plan hash value: 4051414749
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | OMem | 1Mem | Used-Mem |
| 1 | SORT AGGREGATE | 1 | 1 | 1 | 00:00:00.03 | ||||
| COUNT STOPKEY | 1 | 100K | 00:00:00.12 |
PLAN_TABLE_OUTPUT
| 3 | MERGE JOIN CARTESIAN | 1 | 400M | 100K | 00:00:00.02 | ||||
| 4 | TABLE ACCESS FULL | T_PLAN | 1 | 20000 | 5 | 00:00:00.01 | |||
| 5 | BUFFER SORT | 5 | 20000 | 100K | 00:00:00.01 | 568K | 461K | 504K (0) | |
| 6 | TABLE ACCESS FULL | T_PLAN | 1 | 20000 | 20000 | 00:00:00.01 |
Predicate Information (identified by operation id):
2 - filter(ROWNUM<=100000)
| {CODE} * OMem : Optimal 연산에 필요한 예상 Memory를 의미한다. ( Sort Area의 크기가 568K 정도면 Optimal 소트가 예상되고 ) * 1Mem : One Pass 연산에 필요한 예상 메모리 ( 461K 정도면 One Pass가 예상된다 ) * Used-Mem : 실제 사용된 Memory 크기를 의미한다. ( (0)이라 One Pass 실패 했다 만약.. 1보다 큰값은 Multi Pass연산을 의미 ) * Onepass : ? * multipass : ? | +Peeked binds : Bind Peeking이 활성화 되어 있을 경우에만 동작한다. =_= |
|---|---|
| {CODE} |
SQL> var b1 varchar2(10);
SQL> exec :b1 := 'Few';
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL>
SQL> SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * )
2 FROM T_PLAN
3 WHERE C1 = :b1;
COUNT(*)
SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( NULL, NULL, 'ALLSTATS LAST +PEEKED_BINDS' ));
PLAN_TABLE_OUTPUT
Plan hash value: 4286448300
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
| 1 | SORT AGGREGATE | 1 | 1 | 1 | 00:00:00.01 | 38 | |
| TABLE ACCESS FULL | T_PLAN | 1 | 10000 | 0 | 00:00:00.01 | 38 |
Predicate Information (identified by operation id):
2 - filter("C1"=:B1)
18 개의 행이 선택되었습니다.
| {CODE} | Bind Peeking |
|---|---|
| {CODE} |
SQL> SELECT A.KSPPINM NAME,
2 B.KSPPSTVL VALUE,
3 B.KSPPSTDF DEF_YN,
4 A.KSPPDESC DESCRIPTION
5 FROM X$KSPPI A,
6 X$KSPPSV B
7 WHERE A.INDX = B.INDX
8 and a.ksppinm like '%_optim_peek_user_binds%'
9 ;
NAME
SQL> alter session set "_optim_peek_user_binds"=true;
세션이 변경되었습니다.
SQL> var b1 varchar2(10);
SQL> exec :b1 := 'Few';
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL>
SQL> SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * )
2 FROM T_PLAN
3 WHERE C1 = :b1;
COUNT(*)
SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( NULL, NULL, 'ALLSTATS LAST +peeked_binds' ));
PLAN_TABLE_OUTPUT
Plan hash value: 2787416999
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
| 1 | SORT AGGREGATE | 1 | 1 | 1 | 00:00:00.01 | 2 | |
| INDEX RANGE SCAN | T_PLAN_IDX | 1 | 1 | 0 | 00:00:00.01 | 2 |
Peeked Binds (identified by position):
1 - (VARCHAR2(30), CSID=846): 'Few'
Predicate Information (identified by operation id):
2 - access("C1"=:B1)
23 개의 행이 선택되었습니다.
| {CODE} * Peeked Binds : Bind Peeking 을 사용하는 경우, Query가 최초로 실행되는 시점의 실제 Bind 값을 이용해서(Peeking해서) 실행 계획을 세우는 것을 의미한다. Bind 변수를 사용하는 경우 실제로 실행되는 시점에 어떤 값이 들어오는지 알 수 없기 때문에 컬럼의 평균적인 분포도만 가지고 비용을 계산할 수 밖에 없다. 대부분의 경우에는 큰 문제가 언되지만 다음과 같은 경우에는 치명적인 단점을 가지고있다. Ex ) - c1 = 1 : 99% ( Full Table Scan을 하는 것이 유리하다. ) - c1 = 99 : 1% ( Index Scan을 하는 것이 유리하다.) * Bind Capture : Bind 변수의 값을 Capture 하되 SQL Cursor 마다 정해진 Memory 크기만큼(_CURSOR_BIND_CAPTURE_AREA_SIZE Parameter ) 정해진 간격( _CURSOR_BIND_CAPTURE_INTERVAL Parameter ) 마다 Capture하는 기능이다. Capture 된 Bind 값은 V$SQL_BIND_CAPTURE View 를 통해 조회 가능하다. | SQL Cursor |
|---|---|
| {CODE} |
SQL> SELECT SQL_ID, CHILD_NUMBER, SQL_TEXT
2 FROM V$SQL
3 WHERE SQL_TEXT LIKE 'SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * ) FROM T_PLAN WHERE C1 = :b1%'
4 AND ROWNUM <= 1 ;
SQL_ID CHILD_NUMBER
SQL> COLUMN SQL_ID NEW_VALUE V_SQL_ID;
SQL> COLUMN CHILD_NUMBER NEW_VALUE V_CHILD_NUMBER;
SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( '&V_SQL_ID', '&V_CHILD_NUMBER', 'ALLSTATS' ));
구 1: SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( '&V_SQL_ID', '&V_CHILD_NUMBER', 'ALLSTATS' ))
신 1: SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( '2jt5zbafx7dz7', ' 1', 'ALLSTATS' ))
PLAN_TABLE_OUTPUT
Plan hash value: 2787416999
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
| 1 | SORT AGGREGATE | 1 | 1 | 1 | 00:00:00.01 | 2 | |
| INDEX RANGE SCAN | T_PLAN_IDX | 1 | 1 | 0 | 00:00:00.01 | 2 |
Predicate Information (identified by operation id):
2 - access("C1"=:B1)
18 개의 행이 선택되었습니다.
| {CODE} | Dictionary View와 연동 라이브러리 캐쉬에서 밀려난듯 |
|---|---|
| {CODE} SQL> EXPLAIN PLAN FOR 2 SELECT COUNT( * ) 3 FROM T_PLAN 4 WHERE C1 = 'Few'; |
해석되었습니다.
SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
| 0 | SELECT STATEMENT | 1 | 6 | 1 (0) | 00:00:01 | |
| 1 | SORT AGGREGATE | 1 | 6 | |||
| INDEX RANGE SCAN | T_PLAN_IDX | 1 | 6 | 1 (0) | 00:00:01 |
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
2 - access("C1"='Few')
14 개의 행이 선택되었습니다.
SQL> SELECT P.PLAN_TABLE_OUTPUT
2 FROM (SELECT DISTINCT SQL_ID, CHILD_NUMBER
3 FROM V$SQL_PLAN S
4 WHERE S.OBJECT_NAME = 'T_PLAN_IDX') S,
5 TABLE( DBMS_XPLAN.DISPLAY_CURSOR( S.SQL_ID, S.CHILD_NUMBER, 'TYPICAL' ) ) P;
선택된 레코드가 없습니다.
| {CODE} | 한번 실행해서 올려놓구.. |
|---|---|
| {CODE} SQL> SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * ) 2 FROM T_PLAN 3 WHERE C1 = 'Few'; |
COUNT(*)
SQL> SELECT P.PLAN_TABLE_OUTPUT
2 FROM (SELECT DISTINCT SQL_ID, CHILD_NUMBER
3 FROM V$SQL S
4 WHERE SQL_TEXT LIKE 'SELECT /*+ GATHER_PLAN_STATISTICS%') S,
5 TABLE( DBMS_XPLAN.DISPLAY_CURSOR( S.SQL_ID, S.CHILD_NUMBER, 'TYPICAL' ) ) P;
PLAN_TABLE_OUTPUT
Plan hash value: 2787416999
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
| 0 | SELECT STATEMENT | 1 (100) |
PLAN_TABLE_OUTPUT
| 1 | SORT AGGREGATE | 1 | 6 | |||
| INDEX RANGE SCAN | T_PLAN_IDX | 1 | 6 | 1 (0) | 00:00:01 |
Predicate Information (identified by operation id):
2 - access("C1"='Few')
SQL_ID 2jt5zbafx7dz7, child number 2
PLAN_TABLE_OUTPUT
NOTE: cannot fetch plan for SQL_ID: 2jt5zbafx7dz7, CHILD_NUMBER: 2
Please verify value of SQL_ID and CHILD_NUMBER;
It could also be that the plan is no longer in cursor cache (check v$sql_p
lan)
SQL_ID akh267xpw2cyf, child number 0
PLAN_TABLE_OUTPUT
NOTE: cannot fetch plan for SQL_ID: akh267xpw2cyf, CHILD_NUMBER: 0
Please verify value of SQL_ID and CHILD_NUMBER;
It could also be that the plan is no longer in cursor cache (check v$sql_p
lan)
38 개의 행이 선택되었습니다.
| {CODE} | Parallel Execution ( X ) |
|---|---|
| {CODE} SQL> ALTER TABLE T_PLAN PARALLEL 4; |
테이블이 변경되었습니다.
SQL> SELECT /*+ GATHER_PLAN_STATISTICS / COUNT()
2 FROM T_PLAN
3 ;
COUNT(*)
SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( NULL, NULL, 'ALLSTATS LAST' ));
PLAN_TABLE_OUTPUT
Plan hash value: 1491809887
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
| 1 | SORT AGGREGATE | 1 | 1 | 1 | 00:00:00.01 | 3 | |
| 2 | PX COORDINATOR | 1 | 4 | 00:00:00.01 | 3 |
PLAN_TABLE_OUTPUT
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 0 | 1 | 0 | 00:00:00.01 | 0 |
| 4 | SORT AGGREGATE | 0 | 1 | 0 | 00:00:00.01 | 0 | |
| 5 | PX BLOCK ITERATOR | 0 | 20000 | 0 | 00:00:00.01 | 0 | |
| TABLE ACCESS FULL | T_PLAN | 0 | 20000 | 0 | 00:00:00.01 | 0 |
Predicate Information (identified by operation id):
6 - access(:Z>=:Z AND :Z<=:Z)
22 개의 행이 선택되었습니다.
| {CODE} * Parallel Slave가 수행한 단계의 실제 일량( A-Rows, Buffers )이 전혀 수립되지 않는다 | Parallel DML ( X ) |
|---|---|
| {CODE} SQL> ALTER SESSION ENABLE PARALLEL DML; |
세션이 변경되었습니다.
SQL> INSERT /*+ GATHER_PLAN_STATISTICS */ INTO T_PLAN SELECT * FROM T_PLAN T;
20000 개의 행이 만들어졌습니다.
SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( NULL, NULL, 'ALLSTATS LAST' ));
PLAN_TABLE_OUTPUT
Plan hash value: 3480535512
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
| 1 | PX COORDINATOR | 1 | 4 | 00:00:01.43 | 8 | |||||
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 0 | 20000 | 0 | 00:00:00.01 | 0 |
PLAN_TABLE_OUTPUT
| 3 | LOAD AS SELECT | 0 | 0 | 00:00:00.01 | 0 | 256K | 256K | |||
| 4 | PX RECEIVE | 0 | 20000 | 0 | 00:00:00.01 | 0 | ||||
| 5 | PX SEND ROUND-ROBIN | :TQ10000 | 0 | 20000 | 0 | 00:00:00.01 | 0 | |||
| 6 | PX BLOCK ITERATOR | 0 | 20000 | 0 | 00:00:00.01 | 0 | ||||
| TABLE ACCESS FULL | T_PLAN | 0 | 20000 | 0 | 00:00:00.01 | 0 |
Predicate Information (identified by operation id):
7 - access(:Z>=:Z AND :Z<=:Z)
PLAN_TABLE_OUTPUT
23 개의 행이 선택되었습니다.
SQL> ROLLBACK;
롤백이 완료되었습니다.
| {CODE} |