woong:SYS >
@F:\app\Administrator\product\11.1.0\db_1\RDBMS\ADMIN\dbmssqlt.sql
grant administer any sql tuning set to woong;
grant administer any sql tuning set to woong;
grant create any sql profile to woong;
grant drop any sql profile to woong;
grant alter any sql profile to woong;
woong:WOONG >
create table t1(c1 int, c2 int);
create index t1_n1 on t1(c1);
create index t1_n2 on t1(c2);
insert into t1
select level, level
from dual
connect by level <= 1000;
@gather t1
---------------------------------------------------------
concat로 풀릴 때 좋은 실행계획이 나올수 있는 쿼리가
full table scan으로 풀린다는 가정을 한다.(full 힌트로 추가)
full table scan하는 안 좋은 실행계획을 concat로 풀리는 좋은 실행계획으로
쿼리 수정없이 stored outline으로 바꿔치기하는 것이 목표
---------------------------------------------------------
woong:WOONG >
t1 explain plan for
2 select /*+ full(t1) */
3 *
4 from t1
5 where c1 = 1 or c2 = 2
6 ;
해석되었습니다.
경 과: 00:00:00.00
woong:WOONG >
t1 @plan
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 14 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 2 | 14 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("C1"=1 OR "C2"=2)
13 개의 행이 선택되었습니다.
경 과: 00:00:00.01
---------------------------------------------------------------
원하는 실행계획
---------------------------------------------------------------
woong:WOONG >
t1 explain plan for
2 select /*+ use_concat */
3 *
4 from t1
5 where c1 = 1 or c2 = 2
6 ;
해석되었습니다.
경 과: 00:00:00.01
woong:WOONG >
t1 @plan
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 82564388
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 14 | 4 (0)| 00:00:01 |
| 1 | CONCATENATION | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 7 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T1_N2 | 1 | | 1 (0)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 7 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | T1_N1 | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("C2"=2)
4 - filter(LNNVL("C2"=2))
5 - access("C1"=1)
19 개의 행이 선택되었습니다.
경 과: 00:00:00.01
--------------------------------------------------------------
어떤 outline힌트를 사용하는 확인한다.
--------------------------------------------------------------
woong:WOONG >
t1 select * from table(dbms_xplan.display(null, null, 'advanced'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 82564388
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 14 | 4 (0)| 00:00:01 |
| 1 | CONCATENATION | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 7 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T1_N2 | 1 | | 1 (0)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 7 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | T1_N1 | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1_1 / T1@SEL$1
3 - SEL$1_1 / T1@SEL$1
4 - SEL$1_2 / T1@SEL$1_2
5 - SEL$1_2 / T1@SEL$1_2
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
INDEX_RS_ASC(@"SEL$1_2" "T1"@"SEL$1_2" ("T1"."C1"))
INDEX_RS_ASC(@"SEL$1_1" "T1"@"SEL$1" ("T1"."C2"))
OUTLINE(@"SEL$1")
OUTLINE_LEAF(@"SEL$1_2")
USE_CONCAT(@"SEL$1" 8)
OUTLINE_LEAF(@"SEL$1_1")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('11.1.0.6')
OPTIMIZER_FEATURES_ENABLE('11.1.0.6')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("C2"=2)
4 - filter(LNNVL("C2"=2))
5 - access("C1"=1)
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "T1".ROWID[ROWID,10], "C1"[NUMBER,22], "C2"[NUMBER,22]
2 - "T1".ROWID[ROWID,10], "C1"[NUMBER,22], "C2"[NUMBER,22]
3 - "T1".ROWID[ROWID,10], "C2"[NUMBER,22]
4 - "T1".ROWID[ROWID,10], "C1"[NUMBER,22], "C2"[NUMBER,22]
5 - "T1".ROWID[ROWID,10], "C1"[NUMBER,22]
56 개의 행이 선택되었습니다.
경 과: 00:00:00.06
---------------------------------------------------------------------------
dbms_sqltune.import_sql_profile라는 히든 프로시져를 이용하여
등록된 쿼리와 함께 수행될 힌트를 함께 등록한다.
---------------------------------------------------------------------------
woong:WOONG >
t1 begin
2
3 dbms_sqltune.import_sql_profile(
4 name => 'test_prof',
5 sql_text =>
6 'select /*+ full(t1) */
7 *
8 from t1
9 where c1 = 1 or c2 = 2',
10 profile => sqlprof_attr('USE_CONCAT(@"SEL$1" 8)',
11 'INDEX(@"SEL$1_2" "T1"@"SEL$1_2" ("T1"."C1"))',
12 'INDEX(@"SEL$1_1" "T1"@"SEL$1" ("T1"."C2"))')
13 );
14 end;
15 /
PL/SQL 처리가 정상적으로 완료되었습니다.
경 과: 00:00:00.00
----------------------------------------------------------------
안좋은 실행계획으로 수행했어도
SQL profile "test_prof"을 사용하여 원하는 실행계획으로 풀린다.
----------------------------------------------------------------
woong:WOONG >
t1 explain plan for
2 select /*+ full(t1) */
3 *
4 from t1
5 where c1 = 1 or c2 = 2
6 ;
해석되었습니다.
경 과: 00:00:00.01
woong:WOONG >
t1 @plan
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 82564388
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 14 | 4 (0)| 00:00:01 |
| 1 | CONCATENATION | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 7 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T1_N2 | 1 | | 1 (0)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 7 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | T1_N1 | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("C2"=2)
4 - filter(LNNVL("C2"=2))
5 - access("C1"=1)
Note
-----
- SQL profile "test_prof" used for this statement
23 개의 행이 선택되었습니다.
경 과: 00:00:00.01