1、查询每个执行计划消耗的资源情况
SQL> set lines 150 pages 999;
SQL> col p_user for 99999 ;
SQL> col execs for 9999999 ;
SQL> select a.INSTANCE_NUMBER inst_id,to_char(b.END_INTERVAL_TIME,'yyyymmdd hh24:mi:ss') time,plan_hash_value,buffer_gets_delta/executions_delta get_exec, disk_reads_delta/executions_delta read_exec,cpu_time_delta/executions_delta/1000 cpu_exec_ms ,elapsed_time_delta/executions_delta/1000 elaps_exec_ms ,parsing_schema_id p_user,ROWS_PROCESSED_delta/executions_delta rows_exec,EXECUTIONS_DELTA execs
2 from dba_hist_sqlstat a, dba_hist_snapshot b
3 where a.sql_id='bq6kas7t6x9vh'
4 and a.snap_id = b.snap_id
5 and a.instance_number = b.instance_number
6 and b.END_INTERVAL_TIME between sysdate - 3 and sysdate
7 and executions_delta>0 order by 2,1;
------------------------Created by Tangyun[Tony.Tang]------------------------
INST_ID TIME PLAN_HASH_VALUE GET_EXEC READ_EXEC CPU_EXEC_MS ELAPS_EXEC_MS P_USER ROWS_EXEC EXECS
---------- ----------------- --------------- ---------- ---------- ----------- ------------- ------ ---------- --------
1 20151125 11:00:15 402930455 133 0 6.33266667 6.744 46 19.3333333 3
1 20151125 11:00:15 3783743255 144.666667 .333333333 8.1655 8.42 46 23.8333333 6
1 20151125 11:00:15 1639688791 100 0 4.666 4.99166667 46 13.6666667 3
1 20151125 12:00:18 3783743255 104.230769 0 7.30661538 12.2459231 46 18.9230769 13
1 20151125 12:00:18 402930455 78 0 5.66633333 8.95633333 46 13 3
1 20151125 12:00:18 1639688791 127.5 0 6.9985 7.252 46 11.5 2
1 20151125 13:00:21 1639688791 218.375671 .00017316 .886103896 .872582251 46 24.1582684 11550
1 20151125 14:00:23 1639688791 211.849579 0 .842681107 .83075722 46 22.2030686 6648
1 20151125 15:00:26 1639688791 221.616694 0 .915307131 .903271745 46 26.3946515 3702
9 rows selected.
------------------------Created by Tangyun[Tony.Tang]------------------------
2、固定执行计划为 1639688791。
SQL> select sql_handle, plan_name, accepted, fixed,optimizer_cost from dba_sql_plan_baselines ;
no rows selected
SQL> var n number
SQL> begin
2 :n:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'bq6kas7t6x9vh', plan_hash_value=>1639688791, fixed =>'YES', enabled=>'YES');
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> select sql_handle, plan_name, accepted, fixed,optimizer_cost from dba_sql_plan_baselines
SQL_HANDLE PLAN_NAME ACC FIX OPTIMIZER_COST
------------------------------ ------------------------------ --- --- --------------
QL_368f6240d3b95ad6 SQL_PLAN_3d3v2839vkqqqa3c44420 YES YES 34
－－－－删除固定执行计划的方法BEGIN－－－－－－－
declare
xx PLS_INTEGER;
BEGIN
xx :=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SQL_PLAN_3d3v2839vkqqqa3c44420',plan_name=>null);
END;
/
－－－－删除固定执行计划的方法END－－－－－－－
------------------------Created by Tangyun[Tony.Tang] 2016.03------------------------
--------------------------------------------------------------------------------------------------------------------------------
2013-02-05 16:19:36 标签：oracle sql profile 版权声明：原创作品，如需转载，请与作者联系。否则将追究法律责任。
- --1.准备阶段
- SQL> select * from v$version;
- BANNER
- ----------------------------------------------------------------
- Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
- PL/SQL Release 10.2.0.1.0 - Production
- CORE 10.2.0.1.0 Production
- TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
- NLSRTL Version 10.2.0.1.0 - Production
- SQL> create table test_raugher as select * from dba_objects;
- 表已创建。
- SQL> create index ind_objectid on test_raugher(object_id);
- 索引已创建。
- SQL> select object_id from test_raugher where rownum<2;
- OBJECT_ID
- ----------
- 20
- SQL> exec dbms_stats.gather_table_stats(user,'TEST_RAUGHER',cascade=>true);
- PL/SQL 过程已成功完成。
- --原sql执行计划
- SQL> set autot trace explain
- SQL> select * from test_raugher where object_id=20;
- 执行计划
- ----------------------------------------------------------
- Plan hash value: 800879874
- --------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- --------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | 95 | 2 (0)| 00:00:01 |
- | 1 | TABLE ACCESS BY INDEX ROWID| TEST_RAUGHER | 1 | 95 | 2 (0)| 00:00:01 |
- |* 2 | INDEX RANGE SCAN | IND_OBJECTID | 1 | | 1 (0)| 00:00:01 |
- --------------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 2 - access("OBJECT_ID"=20)
- SQL>
- --新sql执行计划
- SQL> select /*+ full(test_raugher) */ * from test_raugher where object_id=20;
- 执行计划
- ----------------------------------------------------------
- Plan hash value: 3725671026
- ----------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ----------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | 95 | 166 (2)| 00:00:02 |
- |* 1 | TABLE ACCESS FULL| TEST_RAUGHER | 1 | 95 | 166 (2)| 00:00:02 |
- ----------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 1 - filter("OBJECT_ID"=20)
- --2.获取新sql的sql_id
- SQL> col sql_id for a20
- SQL> col sql_text for a100
- SQL> select sql_id,sql_text from v$sql where sql_text like '%full(test_raugher)%';
- SQL_ID SQL_TEXT
- -------------------- ----------------------------------------------------------------------------------------------------
- 5nkhk378705z3 select sql_id,sql_text from v$sql where sql_text like '%full(test_raugher)%'
- g23hbdmcsdahc select /*+ full(test_raugher) */ * from test_raugher where object_id=20
- dqp79vx5pmw0k EXPLAIN PLAN SET STATEMENT_ID='PLUS4294967295' FOR select /*+ full(test_raugher) */ * from test_raug
- her where object_id=20
- --3.获取新sql的outline
- SQL> set pagesize 1000
- SQL> select * from table(dbms_xplan.display_cursor('g23hbdmcsdahc',null,'outline'));
- PLAN_TABLE_OUTPUT
- -----------------------------------------------------------------------------------------------
- -----------------------------------------------------------------------------------------------
- SQL_ID g23hbdmcsdahc, child number 0
- -------------------------------------
- select /*+ full(test_raugher) */ * from test_raugher where object_id=20
- Plan hash value: 3725671026
- ----------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ----------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | | | 166 (100)| |
- |* 1 | TABLE ACCESS FULL| TEST_RAUGHER | 1 | 95 | 166 (2)| 00:00:02 |
- ----------------------------------------------------------------------------------
- Outline Data
- -------------
- /*+
- BEGIN_OUTLINE_DATA
- IGNORE_OPTIM_EMBEDDED_HINTS
- OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
- ALL_ROWS
- OUTLINE_LEAF(@"SEL$1")
- FULL(@"SEL$1" "TEST_RAUGHER"@"SEL$1")
- END_OUTLINE_DATA
- */
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 1 - filter("OBJECT_ID"=20)
- 已选择31行。
- --4.创建sql profile(SQLPROFILE_001)
- SQL> declare
- 2 v_hints sys.sqlprof_attr;
- 3 begin
- 4 v_hints:=sys.sqlprof_attr(
- 5 'BEGIN_OUTLINE_DATA',
- 6 'IGNORE_OPTIM_EMBEDDED_HINTS',
- 7 'OPTIMIZER_FEATURES_ENABLE(''10.2.0.1'')',
- 8 'ALL_ROWS',
- 9 'OUTLINE_LEAF(@"SEL$1")',
- 10 'FULL(@"SEL$1" "TEST_RAUGHER"@"SEL$1")',
- 11 'END_OUTLINE_DATA');
- 12 dbms_sqltune.import_sql_profile(
- 13 'select * from test_raugher where object_id=20',
- 14 v_hints,'SQLPROFILE_001',
- 15 force_match=>true,replace=>false);
- 16 end;
- 17 /
- PL/SQL 过程已成功完成。
- --5.查看是否使用sql profile
- SQL> set autot trace explain
- SQL> select * from test_raugher where object_id=20;
- 执行计划
- ----------------------------------------------------------
- Plan hash value: 3725671026
- ----------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ----------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | 95 | 166 (2)| 00:00:02 |
- |* 1 | TABLE ACCESS FULL| TEST_RAUGHER | 1 | 95 | 166 (2)| 00:00:02 |
- ----------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 1 - filter("OBJECT_ID"=20)
- Note
- -----
- - SQL profile "SQLPROFILE_001" used for this statement
- SQL> select * from test_raugher where object_id=200;
- 执行计划
- ----------------------------------------------------------
- Plan hash value: 3725671026
- ----------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ----------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | 95 | 166 (2)| 00:00:02 |
- |* 1 | TABLE ACCESS FULL| TEST_RAUGHER | 1 | 95 | 166 (2)| 00:00:02 |
- ----------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 1 - filter("OBJECT_ID"=200)
- Note
- -----
- - SQL profile "SQLPROFILE_001" used for this statement
- DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
- sql_text => 'FULL QUERY TEXT',
- profile => sqlprof_attr('HINT SPECIFICATION WITH FULL OBJECT ALIASES'),
- name => 'PROFILE NAME',
- force_match => TRUE/FALSE,
- replace=> TRUE/FALSE);
sql_text用于指定sql的全文本，可查询V$SQLAREA.SQL_FULLTEXT或DBA_HIST_SQLTEXT.SQL_TEXT获得。
- BEGIN
- DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'PROFILE NAME');
- END;
- /
- SELECT name, created,category,sql_Text from dba_sql_profiles ORDER BY created DESC;
- SELECT sql_attr.attr_val outline_hints
- FROM dba_sql_profiles sql_profiles, sys.SQLPROF$ATTR sql_attr
- WHERE sql_profiles.signature = sql_attr.signature
- AND sql_profiles.name = 'SQLPROFILE_001'
- ORDER BY sql_attr.attr# ASC;