[20120830]11G SPM的学习6.txt--第3方优化.txt
继续前面的学习:
继续前面的学习:
SQL> select * from v$version ;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
假设我程序中的语句执行如下：
select /*+ use_merge(dept,emp)*/ ename,dname from dept,emp where dept.deptno=emp.deptno;
而实际上使用hash join效果更好，如何实现呢？前面我使用的方法是:
首先加载执行计划到SPM中。
var v_basenum number;
exec :v_basenum:=dbms_spm.load_plans_from_cursor_cache(sql_id => '&sql_id',plan_hash_value =>&plan_hash_value );
然后建立新的执行计划，然后获得新的sql_id以及plan_hash_value。修改旧的plan_name的attribute_name的enable='NO',就可以。
exec :v_basenum:=dbms_spm.load_plans_from_cursor_cache(sql_id => '&new_sql_id',plan_hash_value => &new_ plan_hash_value ,sql_handle => '&sql_handle');
exec :v_basenum := dbms_spm.alter_sql_plan_baseline(sql_handle=>'SYS_SQL_2a9db9f54b3cfa0c',plan_name=>'&bad_plan_name',attribute_name=>'enabled',attribute_value=>'NO');
dbms_spm.load_plans_from_cursor_cache支持好几种参数的传入，其中一种：
FUNCTION load_plans_from_cursor_cache( sql_id IN VARCHAR2,
plan_hash_value IN NUMBER := NULL,
sql_text IN CLOB,
fixed IN VARCHAR2 := 'NO',
enabled IN VARCHAR2 := 'YES'
)
RETURN PLS_INTEGER;
这回利用这个功能测试看看。
SQL> select /*+ use_merge(dept,emp)*/ ename,dname from dept,emp where dept.deptno=emp.deptno;
ENAME DNAME
---------- --------------
CLARK ACCOUNTING
.....
15 rows selected.
SQL> @dpc
PLAN_TABLE_OUTPUT
--------------------------------------
SQL_ID 9hmp82k32axpp, child number 0
-------------------------------------
select /*+ use_merge(dept,emp)*/ ename,dname from dept,emp where
dept.deptno=emp.deptno
Plan hash value: 844388907
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)| OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 7 (100)| | | |
| 1 | MERGE JOIN | | 14 | 7 (15)| | | |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 5 | 3 (0)| | | |
| 3 | INDEX FULL SCAN | PK_DEPT | 5 | 1 (0)| | | |
|* 4 | SORT JOIN | | 14 | 4 (25)| 2048 | 2048 | 2048 (0)|
| 5 | TABLE ACCESS FULL | EMP | 14 | 3 (0)| | | |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
filter("DEPT"."DEPTNO"="EMP"."DEPTNO")
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
30 rows selected.
SQL> select /*+ use_hash(dept,emp)*/ ename,dname from dept,emp where dept.deptno=emp.deptno;
ENAME DNAME
---------- --------------
SMITH RESEARCH
....
15 rows selected.
SQL> @dpc
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 2jsaagf7grtr4, child number 0
-------------------------------------
select /*+ use_hash(dept,emp)*/ ename,dname from dept,emp where
dept.deptno=emp.deptno
Plan hash value: 615168685
------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)| OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 7 (100)| | | |
|* 1 | HASH JOIN | | 14 | 7 (15)| 1156K| 1156K| 725K (0)|
| 2 | TABLE ACCESS FULL| DEPT | 5 | 3 (0)| | | |
| 3 | TABLE ACCESS FULL| EMP | 14 | 3 (0)| | | |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
27 rows selected.
var v_basenum number;
exec :v_basenum:=dbms_spm.load_plans_from_cursor_cache(sql_id => '2jsaagf7grtr4',plan_hash_value =>615168685,sql_text=>'select /*+ use_merge(dept,emp)*/ ename,dname from dept,emp where dept.deptno=emp.deptno' );
--注意sql_text后面的;分号不需要输入。我前面几次都不成功，就是多输入了分号。
SQL> column signature format 99999999999999999999
SQL> select sql_handle, plan_name, enabled, accepted,fixed,origin,signature from dba_sql_plan_baselines ;
SQL_HANDLE PLAN_NAME ENA ACC FIX ORIGIN SIGNATURE
------------------------------ ------------------------------ --- --- --- -------------- ---------------------
SYS_SQL_54c05202ed3f6591 SQL_PLAN_59h2k0bqmytcj8447c07a YES YES NO MANUAL-LOAD 6106971267238159761
--注意我修改select为大写，use_merge第1个字母大写。
SQL> SELECT /*+ Use_merge(dept,emp)*/ ename,dname from dept,emp where dept.deptno=emp.deptno;
ENAME DNAME
---------- --------------
SMITH RESEARCH
....
15 rows selected.
SQL> @dpc
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID cty0smwk286gc, child number 1
-------------------------------------
SELECT /*+ Use_merge(dept,emp)*/ ename,dname from dept,emp where
dept.deptno=emp.deptno
Plan hash value: 615168685
------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)| OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 7 (100)| | | |
|* 1 | HASH JOIN | | 14 | 7 (15)| 1156K| 1156K| 747K (0)|
| 2 | TABLE ACCESS FULL| DEPT | 5 | 3 (0)| | | |
| 3 | TABLE ACCESS FULL| EMP | 14 | 3 (0)| | | |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
Note
-----
- SQL plan baseline SQL_PLAN_cg6y3qktyghww8447c07a used for this statement
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
28 rows selected.
--可以发现使用SPM。
如果修改sql如下:
SQL> SELECT /*+ use_nl(dept,emp) */ ename,dname from dept,emp where dept.deptno=emp.deptno;
ENAME DNAME
---------- --------------
CLARK ACCOUNTING
....
15 rows selected.
SQL> @dpc
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
SQL_ID 38bnmwumwy6n9, child number 0
-------------------------------------
SELECT /*+ use_nl(dept,emp) */ ename,dname from dept,emp where
dept.deptno=emp.deptno
Plan hash value: 4192419542
---------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
---------------------------------------------------------
| 0 | SELECT STATEMENT | | | 12 (100)|
| 1 | NESTED LOOPS | | 14 | 12 (0)|
| 2 | TABLE ACCESS FULL| DEPT | 5 | 3 (0)|
|* 3 | TABLE ACCESS FULL| EMP | 3 | 2 (0)|
---------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("DEPT"."DEPTNO"="EMP"."DEPTNO")
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
27 rows selected.
--可以发现如果更换提示，不会使用SPM。
