[20120830]11G SPM的学习6.txt--第3方优化.txt

[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。