SQL> select * from v$version where rownum <= 1
2 ;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
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
----------
1
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)에 위배됩니다
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)|
|* 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 25 | 2 (0)|
|* 2 | INDEX RANGE SCAN | T1_NL | 1 | | 1 (0)|
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("C2"='DUMMY')
2 - access("C1"=1)
Note
-----
- 'PLAN_TABLE' is old version <-- =_=
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 |
|* 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 25 | 2 (0)| 00:00:01 |
|* 2 | 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
-----
- dynamic sampling used for this statement <-- ^^
19 개의 행이 선택되었습니다.
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
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2154509930
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 14 | 2 (0)| 00:00:01 |
|* 2 | 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 개의 행이 선택되었습니다.
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
---------- ----------
1 DUMMY
SQL> select * from table( dbms_xplan.display_cursor( null, null, 'ALLSTATS LAST'));
SQL_ID 4p1gp2192gq0k, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */* FROM T1 WHERE C1 = 1 AND C2 = 'DUMMY'
Plan hash value: 2154509930
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
--------------------------------------------------------------------------------------------------------
|* 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 1 | 1 |00:00:00.01 | 4 | 2 |
|* 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
---------- ----------
1 DUMMY
SQL> @XPLAN_C
SQL_ID 0fnqn7r64jg7m, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */* FROM T2 WHERE C1 = 1 AND C2 = 'DUMMY'
Plan hash value: 3273381761
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------
|* 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 1 | 1 |00:00:00.01 | 3 |
|* 2 | 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)
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;
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 |
|* 3 | TABLE ACCESS FULL | T1 | 1 | 7 | 3 (0)| 00:00:01 |
|* 4 | 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
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 |
|* 1 | HASH JOIN | | 1 | 14 | 7 (15)| 00:00:01 |
|* 2 | 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)
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 |
|* 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 7 | 2 (0)| 00:00:01 |
|* 2 | 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 |
|* 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 7 | 2 (0)| 00:00:01 |
|* 2 | 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 |
|* 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 7 | 2 (0)| 00:00:01 |
|* 2 | 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, 'BASIC' ) );
Plan hash value: 1420382924
---------------------------------------------
| Id | Operation | Name |
---------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 |
| 2 | INDEX RANGE SCAN | T1_N1 |
---------------------------------------------
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 |
|* 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 7 | 2 (0)| 00:00:01 |
|* 2 | 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)
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 |
|* 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 7 | 2 (0)| 00:00:01 |
|* 2 | 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 개의 행이 선택되었습니다.
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 |
|* 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 7 | 2 (0)| 00:00:01 |
|* 2 | 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 개의 행이 선택되었습니다.
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 |
|* 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 7 | 2 (0)| 00:00:01 |
|* 2 | 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 개의 행이 선택되었습니다.
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 |
|* 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 7 | 2 (0)| 00:00:01 |
|* 3 | 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]
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 |
|* 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 7 | 2 (0)| 00:00:01 |
|* 3 | 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 개의 행이 선택되었습니다.
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 |
|* 1 | FILTER | | | | | |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 7 | 3 (0)| 00:00:01 |
|* 3 | FILTER | | | | | |
|* 4 | 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]
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 |
|* 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 7 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T1_N1 | 3 | | 2 (0)| 00:00:01 |
|* 4 | 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 |
|* 1 | HASH JOIN SEMI | | 1 | 11 | 6 (17)| 00:00:01 |
|* 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 7 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T1_N1 | 3 | | 2 (0)| 00:00:01 |
|* 4 | 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>
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 |
|* 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 7 | 2 (0)| 00:00:01 |
|* 2 | 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 개의 행이 선택되었습니다.
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 |
|* 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 7 | 2 (0)| 00:00:01 |
|* 2 | 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 개의 행이 선택되었습니다.
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 );
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 |
|* 1 | TABLE ACCESS FULL| T_PLAN | 10000 | 60000 | 6 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("C1"='Many1')
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 |
|* 1 | 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>
SQL> SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * )
2 FROM T_PLAN
3 WHERE C1 = 'Many2';
COUNT(*)
----------
10000
SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( NULL, NULL, 'ALLSTATS LAST' ));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
SQL_ID bhxfgwb1bjqn2, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * ) FROM T_PLAN WHERE C1 = 'Many2'
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 |
|* 2 | INDEX RANGE SCAN| T_PLAN_IDX | 1 | 1 | 10000 |00:00:00.01 | 34 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"='Many2')
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(*)
----------
10000
SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( NULL, NULL, 'ALLSTATS LAST' ));
SQL_ID bhxfgwb1bjqn2, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * ) FROM T_PLAN WHERE C1 =
'Many2'
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 |
|* 2 | TABLE ACCESS FULL| T_PLAN | 1 | 10000 | 10000 |00:00:00.01 | 38 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("C1"='Many2')
19 개의 행이 선택되었습니다.
\
SQL> SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * )
2 FROM T_PLAN
3 WHERE C1 = 'Few';
COUNT(*)
----------
0
SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( NULL, NULL, 'BASIC' ));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * ) FROM T_PLAN WHERE C1
= 'Few'
Plan hash value: 2787416999
----------------------------------------
| Id | Operation | Name |
----------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | INDEX RANGE SCAN| T_PLAN_IDX |
----------------------------------------
15 개의 행이 선택되었습니다.
SQL> SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * )
2 FROM T_PLAN
3 WHERE C1 = 'Few';
COUNT(*)
----------
0
SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( NULL, NULL, 'TYPICAL' ));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
SQL_ID dymqsvydfcjpf, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * ) FROM T_PLAN WHERE C1
= 'Few'
Plan hash value: 2787416999
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | INDEX RANGE SCAN| T_PLAN_IDX | 1 | 6 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"='Few')
20 개의 행이 선택되었습니다.
SQL> SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * )
2 FROM T_PLAN
3 WHERE C1 = 'Few';
COUNT(*)
----------
0
SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( NULL, NULL, 'ALL' ));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
SQL_ID 1a4gm5z0swug1, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * ) FROM T_PLAN WHERE
C1 = 'Few'
Plan hash value: 2787416999
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | 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 개의 행이 선택되었습니다.
SQL> SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * )
2 FROM T_PLAN
3 WHERE C1 = 'Few';
COUNT(*)
----------
0
SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( NULL, NULL, 'ALLSTATS' ));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID dymqsvydfcjpf, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * ) FROM T_PLAN WHERE C1 = 'Few'
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 |
|* 2 | 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>
SQL> SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * )
2 FROM T_PLAN
3 WHERE C1 = 'Few';
COUNT(*)
----------
0
SQL>
SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( NULL, NULL, 'ALLSTATS LAST' ));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------
SQL_ID dymqsvydfcjpf, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * ) FROM T_PLAN WHERE C1 = 'Few'
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 |
|* 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 개의 행이 선택되었습니다.
SQL> SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * )
2 FROM T_PLAN
3 WHERE C1 = 'Few';
COUNT(*)
----------
0
SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( NULL, NULL, 'IOSTATS LAST' ));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
SQL_ID dymqsvydfcjpf, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * ) FROM T_PLAN WHERE C1 = 'Few'
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 |
|* 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')
SQL> SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * )
2 FROM T_PLAN
3 , T_PLAN
4 WHERE ROWNUM <= 100000;
COUNT(*)
----------
100000
SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( NULL, NULL, 'MEMSTATS LAST' ));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------
SQL_ID 38uradzrgj5b6, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * ) FROM T_PLAN , T_PLAN WHERE ROWNUM <= 100000
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 | | | |
|* 2 | 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)
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(*)
----------
0
SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( NULL, NULL, 'ALLSTATS LAST +PEEKED_BINDS' ));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
SQL_ID 2jt5zbafx7dz7, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * ) FROM T_PLAN WHERE C1 = :b1
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 |
|* 2 | TABLE ACCESS FULL| T_PLAN | 1 | 10000 | 0 |00:00:00.01 | 38 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("C1"=:B1)
18 개의 행이 선택되었습니다.
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
--------------------------------------------------------------------------------
VALUE
------------------------------------------------------------------------------------------------------------------------------------------------------
DEF_YN
---------
DESCRIPTION
------------------------------------------------------------------------------------------------------------------------------------------------------
_optim_peek_user_binds
TRUE
TRUE
enable peeking of user binds
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(*)
----------
0
SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( NULL, NULL, 'ALLSTATS LAST +peeked_binds' ));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------
SQL_ID 2jt5zbafx7dz7, child number 2
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * ) FROM T_PLAN WHERE C1 = :b1
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 |
|* 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 개의 행이 선택되었습니다.
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_TEXT
--------------------------------------------------------------------------------------------------------------------
2jt5zbafx7dz7 1
SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * ) FROM T_PLAN WHERE C1 = :b1
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
--------------------------------------------------------------------------------------------------------------------
SQL_ID 2jt5zbafx7dz7, child number 1
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * ) FROM T_PLAN WHERE C1 = :b1
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 |
|* 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 개의 행이 선택되었습니다.
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
--------------------------------------------------------------------------------
Plan hash value: 2787416999
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | 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;
선택된 레코드가 없습니다.
SQL> SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * )
2 FROM T_PLAN
3 WHERE C1 = 'Few';
COUNT(*)
----------
0
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
--------------------------------------------------------------------------------
SQL_ID dymqsvydfcjpf, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * ) FROM T_PLAN WHERE C1
= 'Few'
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 | | |
|* 2 | 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
--------------------------------------------------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * ) FROM T_PLAN WHERE C1
= :b1
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
--------------------------------------------------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * ) FROM T_PLAN
WHERE C1 = :b1
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 개의 행이 선택되었습니다.
SQL> ALTER TABLE T_PLAN PARALLEL 4;
테이블이 변경되었습니다.
SQL> SELECT /*+ GATHER_PLAN_STATISTICS */ COUNT(*)
2 FROM T_PLAN
3 ;
COUNT(*)
----------
20000
SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( NULL, NULL, 'ALLSTATS LAST' ));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------
SQL_ID cbkdc4b0d9ntk, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */ COUNT(*) FROM T_PLAN
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 |
|* 6 | 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 개의 행이 선택되었습니다.
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
-------------------------------------------------------------------------------------------------------------------------------
SQL_ID guvtztakgk4jt, child number 0
-------------------------------------
INSERT /*+ GATHER_PLAN_STATISTICS */ INTO T_PLAN SELECT * FROM T_PLAN T
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 | | | |
|* 7 | 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;
롤백이 완료되었습니다.
- 강좌 URL : http://www.gurubee.net/lecture/3905
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.