Oracle 11g执行计划固定-Baseline
Oracle 11g执行计划固定-Baseline
测试Oracle 11g 固定执行计划-Baseline
一,选定sql_id
- SQL> pagesize 300
- SQL> set linesize 300
- SQL> set autotrace on
- SQL> var name varchar2(10);
- SQL> exec :name :='IT';
- select department_name
- from hr.departments dept
- where department_id in (select department_id from hr.employees emp)
- and department_name=:name;
- DEPARTMENT_NAME
- ------------------------------
- IT
- 1 rows selected.
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 2605691773
- ----------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ----------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | 19 | 3 (0)| 00:00:01 |
- | 1 | NESTED LOOPS SEMI | | 1 | 19 | 3 (0)| 00:00:01 |
- |* 2 | TABLE ACCESS FULL| DEPARTMENTS | 1 | 16 | 3 (0)| 00:00:01 |
- |* 3 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 41 | 123 | 0 (0)| 00:00:01 |
- ----------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 2 - filter("DEPARTMENT_NAME"=:NAME)
- 3 - access("DEPARTMENT_ID"="DEPARTMENT_ID")
- SQL> select sql_id from v$sql where sql_fulltext like'select department_name%';
- SQL_ID
- -------------
- bd8mzf35svfm3
上面的sql现在的执行计划是情况,假设如上的执行计划效率底下,我们想用其它的执行计划(全表扫描emp)来代替它,并用oracle 11g中的sql plan baseline来固定次SQL的执行。
二,添加Hint的sql:
- select department_name
- from hr.departments dept
- where department_id in (select/*+FULL(emp)*/ department_id from hr.employees emp)
- and department_name=:name;
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 2317224448
- ----------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ----------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | 19 | 7 (15)| 00:00:01 |
- |* 1 | HASH JOIN SEMI | | 1 | 19 | 7 (15)| 00:00:01 |
- |* 2 | TABLE ACCESS FULL| DEPARTMENTS | 1 | 16 | 3 (0)| 00:00:01 |
- | 3 | TABLE ACCESS FULL| EMPLOYEES | 107 | 321 | 3 (0)| 00:00:01 |
- ----------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 1 - access("DEPARTMENT_ID"="DEPARTMENT_ID")
- 2 - filter("DEPARTMENT_NAME"=:NAME)
- SQL> select sql_id from v$sql_plan where plan_hash_value=2317224448;
- SQL_ID
- -------------
- 5kuqnnugsrhj3
- 此计划sql_id:5kuqnnugsrhj3 hash_value:2317224448
|
评论暂时关闭