加载中…
个人资料
微博
  • 博客等级：
  • 博客积分：
  • 博客访问：
  • 关注人气：
  • 获赠金笔：0支
  • 赠出金笔：0支
  • 荣誉徽章：
正文 字体大小：大 中 小

oracle_执行计划固定工具sql profile和spm

(2015-01-12 23:00:43)
标签：

sqlprofile

spa

paln

it

杂谈

 分类： oracle

   有些SQL，不管怎么优化，都不能选择合适的执行计划，所以就用到SQL PROFILE和SPM来固定SQL的执行计划，让SQL在执行时考虑SP和SPM的作用，从而达到实现选择我们规定的执行计划。

   工作原理如下：
http://s15/mw690/002oQdHJzy6P6rBi5LEfe&690profile和spm" TITLE="oracle_执行计划固定工具sql profile和spm" />



1.SQL PROFILE

--自动sql profile
DECLARE
         my_task_name VARCHAR2(30);
         my_sqltext CLOB;
BEGIN
         my_sqltext :='SQL --STATEMENT';
         my_task_name:= dbms_sqltune.create_tuning_task(
                                        sql_text => my_sqltext ,            [sql_id=>xxxxx,]
                                         user_name=> 'USER',
                                         scope =>'COMPREHENSIVE',
                                         time_limit => 60,
                                         task_name => 'test_sql_tuning_task1',
                                         description => 'task for sql tuning');
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name=>'test_sql_tuning_task1');
END;
/
SELECT task_name,advisor_name,status FROM user_advisor_tasks;
 set long 999999
set LONGCHUNKSIZE 999999
set serveroutput on size 999999
 set linesize 200
SELECT dbms_sqltune.report_tuning_task('test_sql_tuning_task1') FROM dual;

--- 让oracle接受上面的优化任务

DECLARE

my_sqlprofile_name VARCHAR2(30);

begin

my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (

task_name => ‘test_sql_tuning_task1′,

name => ‘test1′);

end;

/


--手动创建SQL PROFILE

对SQL语句添加hint，使用SELECT * FROM table(dbms_xplan.display(null,null,'outline'));

产生优化器对执行计划的outline dat。

SELECT count(1) FROM t WHERE object_id < 20; --需要优化的SQL

SELECT count(1) FROM t WHERE object_id < 20;    --添加HINT后正确执行计划的SQL

SELECT * FROM table(dbms_xplan.display_cursor(null,null,'outline')) ;


Outline Data

-------------


 


变为：


 




declare

  v_hints sys.sqlprof_attr;

begin

  v_hints := sys.sqlprof_attr(

       q'[BEGIN_OUTLINE_DATA]',

      q'[IGNORE_OPTIM_EMBEDDED_HINTS]',

      q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.3')]',

      q'[DB_VERSION('11.2.0.3')]',

      q'[ALL_ROWS]',

      q'[OUTLINE_LEAF(@"SEL$1")]',

      q'[INDEX(@"SEL$1" "T"@"SEL$1" ("T"."OBJECT_ID"))]',   --也可以只添加这个关键部分

      q'[END_OUTLINE_DATA]');

  dbms_sqltune.import_sql_profile('SELECT count(1) FROM t WHERE object_id < 20', 

                                  v_hints,

                                  'PROFILETEST1',

                                  force_match => true);

end;

/



--------SQL_TEXT 书写方法,直接将输出的内容加上字符号'和||串上和SQL中的字符号写两个‘’代表一个。

SELECT sql_text FROM v$sqltext WHERE sql_id='XXXXX';


declare

  v_hints sys.sqlprof_attr;

begin

  v_hints := sys.sqlprof_attr(

      q'[BEGIN_OUTLINE_DATA]',

      q'[IGNORE_OPTIM_EMBEDDED_HINTS]',

      q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.3')]',

      q'[DB_VERSION('11.2.0.3')]',

      q'[OPT_PARAM('_optim_peek_user_binds' 'false')]',

      q'[OPT_PARAM('_optimizer_extended_cursor_sharing' 'none')]',

      q'[OPT_PARAM('_gby_hash_aggregation_enabled' 'false')]',

      q'[OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')]',

      q'[OPT_PARAM('_optimizer_adaptive_cursor_sharing' 'false')]',

      q'[OPT_PARAM('_optimizer_join_factorization' 'false')]',

      q'[OPT_PARAM('_optimizer_use_cbqt_star_transformation' 'false')]',

      q'[OPT_PARAM('_optimizer_use_feedback' 'false')]',

      q'[OPT_PARAM('_fix_control' '6514189:0')]',

      q'[ALL_ROWS]',

      q'[OUTLINE_LEAF(@"SEL$1")]',

      q'[FULL(@"SEL$1" "A"@"SEL$1")]',

      q'[END_OUTLINE_DATA]');

  dbms_sqltune.import_sql_profile('select count(*)   from tbcs.reception a  where region = 535    a'||

                                  'nd recdate between to_date(''20150801000000'', ''yyyymmddhh24miss'')'||

                                  ' and        to_date(''20150802000000'', ''yyyymmddhh24miss'')    and'||

                                  ' recdefid = ''Charge''    and a.isbackprocess = 0', 

                                  v_hints,

                                  'PROFILETEST1',

                                  force_match => true);

end;

/


-- Disable a profile, so it is not be used by any sessions.

EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE ( name            =>  'PROFILETEST1',   

                                      attribute_name  =>  'STATUS', 

                                      value           =>  'DISABLED');

-- Enable it back:

EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE ( name            =>   'PROFILETEST1',   

                                      attribute_name  =>   'STATUS', 

                                      value           =>   'ENABLED');


 SELECT sql_text,created,status FROM dba_sql_profiles  ;


--也可以使用系统自带的脚本来捕获cursor中高消耗SQL来进行优化建议
SQL> @?/rdbms/admin/sqltrpt


2.SPM

----------------自动创建SPM
SQL> alter session set optimizer_capture_sql_plan_baselines=true;

Session altered.

SQL> create table t2 as select * from dba_objects;

Table created.

SQL> create index idx_t2 on t2(object_id) ;

Index created.

SQL> exec dbms_stats.gather_table_stats('SUN','T',cascade=>true) ;

PL/SQL procedure successfully completed.

SQL> SELECT object_id,object_name FROM t2 WHERE object_id between 100 and 110 ;

 OBJECT_ID OBJECT_NAME
---------- --------------------------------------------------------------------------------------------------------------------------------
       100 ORA$BASE
       101 FIXED_OBJ$
       102 I_FIXED_OBJ$_OBJ#
       103 MIGRATE$
       104 DEPENDENCY$
       105 ACCESS$
       106 I_DEPENDENCY1
       107 I_DEPENDENCY2
       108 I_ACCESS1
       109 VIEWCON$
       110 SYS_LOB0000000109C00005$$

11 rows selected.

SQL> SELECT * FROM table(dbms_xplan.display_cursor(null,null,'advanced'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  5gcrtm0v5xb5h, child number 0
-------------------------------------
SELECT object_id,object_name FROM t2 WHERE object_id between 100 and 110

Plan hash value: 2008370210

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |       |       |     3 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2     |    11 |   869 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_T2 |    11 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / T2@SEL$1
   2 - SEL$1 / T2@SEL$1

Outline Data
-------------

 

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID">=100 AND "OBJECT_ID"<=110)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "OBJECT_NAME"[VARCHAR2,128], "OBJECT_ID"[NUMBER,22]
   2 - "T2".ROWID[ROWID,10], "OBJECT_ID"[NUMBER,22]

Note
-----
   - dynamic sampling used for this statement (level=2)


49 rows selected.

执行2次SQL会在dba_sql_plan_baselines视图中看到这个SQL信息

SQL> SELECT sql_handle,plan_name,origin,enabled,accepted,sql_text FROM dba_sql_plan_baselines WHERE sql_text LIKE 'SELECT object%';

SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENA ACC SQL_TEXT
------------------------------ ------------------------------ -------------- --- --- --------------------------------------------------------------------------------
SQL_514b2b4cbb70a255           SQL_PLAN_52ktb9kxr18kp24c6dbb6 AUTO-CAPTURE   YES YES SELECT object_id,object_name FROM t2 WHERE object_id between 100 and 110


如果不关闭 optimizer_capture_sql_plan_baselines ，如果改SQL产生其他执行计划，也是被捕捉的。

关闭 alter session optimizer_capture_sql_plan_baselines=false;

删除表t2数据只剩object_id 在100到110。

delete from t2 WHERE object_id < 100 OR object_id > 110;
commit;
exec dbms_stats.gather_table_stats('SUN','T',no_invalidate=>false,cascade=>true) ;
按正常选择率计算的话，下面SQL应该走TABLE FULL SCAN会好过走索引。

SQL> SELECT * FROM table(dbms_xplan.display_cursor(null,null,'advanced'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  107qmkpmhhwp7, child number 0
-------------------------------------
 SELECT object_id,object_name FROM t2 WHERE object_id between 100 and
110

Plan hash value: 2008370210

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |       |       |     1 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2     |     1 |    79 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_T2 |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / T2@SEL$1
   2 - SEL$1 / T2@SEL$1

Outline Data
-------------

 

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID">=100 AND "OBJECT_ID"<=110)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "OBJECT_NAME"[VARCHAR2,128], "OBJECT_ID"[NUMBER,22]
   2 - "T2".ROWID[ROWID,10], "OBJECT_ID"[NUMBER,22]

Note
-----
   - dynamic sampling used for this statement (level=2)
   - SQL plan baseline SQL_PLAN_52ktb9kxr18kp24c6dbb6 used for this statement
    ----很显然这里走了索引，使用SPM

51 rows selected.



使用dbms_spm.alter_sql_plan_baseline
    dbms_spm.evolve_sql_plan_baseline
    dbms_spm.drop_sql_plan_baseline
  对现有sql plan baseline进行修改删除动作。
SQL> var temp number
SQL> exec :temp :=dbms_spm.DROP_SQL_PLAN_BASELINE(sql_handle=>'SQL_514b2b4cbb70a255',plan_name=>'SQL_PLAN_52ktb9kxr18kp24c6dbb6');

PL/SQL procedure successfully completed.



----------------手工创建SPM

SELECT object_id,object_name FROM t2 WHERE object_id between 100 and 110 ;

SELECT * FROM table(dbms_xplan.display_cursor(null,null,'advanced'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  76dm5rf899fb3, child number 0
-------------------------------------
SELECT object_id,object_name FROM t2 WHERE
object_id between 100 and 110

Plan hash value: 1513984157

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |    53 (100)|          |
|*  1 |  TABLE ACCESS FULL| T2   |     1 |    79 |    53   (0)| 00:00:01 |
--------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / T2@SEL$1

Outline Data
-------------

 

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("OBJECT_ID">=100 AND "OBJECT_ID"<=110))

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "OBJECT_NAME"[VARCHAR2,128], "OBJECT_ID"[NUMBER,22]

Note
-----
   - dynamic sampling used for this statement (level=2)


47 rows selected.
var temp number
exec :temp := dbms_spm.load_sql_plans_from_cursor_cache(sql_id=>'76dm5rf899fb3',plan_hash_value=>'1513984157');
 SELECT sql_handle,plan_name,origin,enabled,accepted,sql_text FROM dba_sql_plan_baselines WHERE sql_text LIKE 'SELECT object%';

SELECT object_id,object_name FROM t2 WHERE object_id between 100 and 110 ;

SELECT * FROM table(dbms_xplan.display_cursor(null,null,'advanced'));
exec :temp := dbms_spm.load_sql_plans_from_cursor_cache(sql_id=>'xxxxxxx',plan_hash_value=>'xxxxxxxxx');
 SELECT sql_handle,plan_name,origin,enabled,accepted,sql_text FROM dba_sql_plan_baselines WHERE sql_text LIKE 'SELECT object%';



至此和自动SPM相同。 
使用dbms_spm.alter_sql_plan_baseline
    dbms_spm.evolve_sql_plan_baseline
    dbms_spm.drop_sql_plan_baseline
  对现有sql plan baseline进行修改删除动作。
SQL> var temp number
SQL> exec :temp :=dbms_spm.DROP_SQL_PLAN_BASELINE(sql_handle=>'SQL_514b2b4cbb70a255',plan_name=>'SQL_PLAN_52ktb9kxr18kp24c6dbb6');

PL/SQL procedure successfully completed.



喜欢

0

阅读┊ 收藏 ┊ 喜欢▼ ┊打印┊举报/Report
  

新浪BLOG意见反馈留言板　欢迎批评指正

新浪简介 | About Sina | 广告服务 | 联系我们 | 招聘信息 | 网站律师 | SINA English | 产品答疑

Copyright © 1996 - 2022 SINA Corporation, All Rights Reserved

新浪公司 版权所有