CREATE_TUNING_TASK Functions

韶博雅

已于 2023-10-04 09:04:36 修改

阅读量92

点赞数

文章标签： oracle

于 2023-08-05 22:06:11 首次发布

本文链接：https://blog.csdn.net/weixin_39735909/article/details/132124538

版权

1.CREATE_TUNING_TASK Functions

You can use different forms of this function to:

Create a tuning task for a single statement given its text.

Create a tuning task for a single statement from the shared SQL area given its identifier.

Create a tuning task for a single statement from the workload repository given a range of snapshot identifiers.

Create a tuning task for a SQL tuning set.

Create tuning task for a SQL Performance Analyzer

In all cases, the function mainly creates an advisor task and sets its parameters.

1.1. SQL text format

DBMS_SQLTUNE.CREATE_TUNING_TASK (
  sql_text         IN CLOB,
  bind_list        IN sql_binds := NULL,
  user_name        IN VARCHAR2  := NULL,
  scope            IN VARCHAR2  := SCOPE_COMPREHENSIVE,
  time_limit       IN NUMBER    := TIME_LIMIT_DEFAULT,
  task_name        IN VARCHAR2  := NULL,
  description      IN VARCHAR2  := NULL,
  con_name         IN VARCHAR2  := NULL)
RETURN VARCHAR2;

1.2 SQL ID format

DBMS_SQLTUNE.CREATE_TUNING_TASK (
  sql_id           IN VARCHAR2,
  plan_hash_value  IN NUMBER    := NULL,
  scope            IN VARCHAR2  := SCOPE_COMPREHENSIVE,
  time_limit       IN NUMBER    := TIME_LIMIT_DEFAULT,
  task_name        IN VARCHAR2  := NULL,
  description      IN VARCHAR2  := NULL,
  con_name         IN VARCHAR2  := NULL)
RETURN VARCHAR2;

1.3 AWR format

DBMS_SQLTUNE.CREATE_TUNING_TASK (
  begin_snap       IN NUMBER,
  end_snap         IN NUMBER,
  sql_id           IN VARCHAR2,
  plan_hash_value  IN NUMBER    := NULL,
  scope            IN VARCHAR2  := SCOPE_COMPREHENSIVE,
  time_limit       IN NUMBER    := TIME_LIMIT_DEFAULT,
  task_name        IN VARCHAR2  := NULL,
  description      IN VARCHAR2  := NULL,
  con_name         IN VARCHAR2  := NULL)
RETURN VARCHAR2;

1.4 SQL tuning set format

DBMS_SQLTUNE.CREATE_TUNING_TASK (
  sqlset_name       IN VARCHAR2,
  basic_filter      IN VARCHAR2 :=  NULL,
  object_filter     IN VARCHAR2 :=  NULL,
  rank1             IN VARCHAR2 :=  NULL,
  rank2             IN VARCHAR2 :=  NULL,
  rank3             IN VARCHAR2 :=  NULL,
  result_percentage IN NUMBER   :=  NULL,
  result_limit      IN NUMBER   :=  NULL,
  scope             IN VARCHAR2 :=  SCOPE_COMPREHENSIVE,
  time_limit        IN NUMBER   :=  TIME_LIMIT_DEFAULT,
  task_name         IN VARCHAR2 :=  NULL,
  description       IN VARCHAR2 :=  NULL
  plan_filter       IN VARCHAR2 :=  'MAX_ELAPSED_TIME',
  sqlset_owner      IN VARCHAR2 :=  NULL)
RETURN VARCHAR2;

1.5 SQL Performance Analyzer format

DBMS_SQLTUNE.CREATE_TUNING_TASK (
   spa_task_name     IN VARCHAR2,
   spa_task_owner    IN VARCHAR2 :=  NULL,
   spa_compare_exec  IN VARCHAR2 :=  NULL,
   basic_filter      IN VARCHAR2 :=  NULL,
   time_limit        IN NUMBER   :=  TIME_LIMIT_DEFAULT,
   task_name         IN VARCHAR2 :=  NULL,
   description       IN VARCHAR2 :=  NULL)
RETURN VARCHAR2;

2 Examples

VARIABLE stmt_task VARCHAR2(64);
VARIABLE sts_task VARCHAR2(64);
VARIABLE spa_tune_task VARCHAR2(64);

2.1 Create Tuning Task with SQL Text Format

EXEC :stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK( -
  sql_text => 'select quantity_sold from sales s, times t where s.time_id = t.time_id and s.time_id = TO_DATE(''24-NOV-00'')');

2.2 Create Tuning Task with SQL ID Format

EXEC :stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => 'ay1m3ssvtrh24');
 
-- tune in limited scope 
EXEC :stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => 'ay1m3ssvtrh24', -
   scope => 'LIMITED');
 
-- only give 10 minutes for tuning statement
EXEC :stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => 'ay1m3ssvtrh24', -
   time_limit => 600);

2.3 Create Tuning Task with AWR Snapshot Format

EXEC :stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(begin_snap => 1, -
   end_snap => 2, sql_id => 'ay1m3ssvtrh24');

2.4 Create Tuning Task with SQL Tuning Set Format

-- First we need to load an STS, then tune it
-- Tune our statements in order by buffer gets, time limit of one hour
-- the default ranking measure is elapsed time.
EXEC :sts_task := DBMS_SQLTUNE.CREATE_TUNING_TASK( -
  sqlset_name  => 'my_workload', -
  rank1        => 'BUFFER_GETS', -
  time_limit   => 3600, -
  description  => 'tune my workload ordered by buffer gets');

2.5 Create Tuning Task with SPA Task Format

-- Tune the SQLs that were reported as having regressed from the compare 
-- performance execution of the SPA task named task_123
EXEC :spa_tune_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(
   spa_task_name     => 'task_123',
   spa_task_owner    => 'SCOTT',
   spa_compare_exec  => 'exec1');

调用

EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK(:stmt_task);

案例：

--1
SQL> select * from a where object_id=20;
SQL> variable stmt_task clob;
SQL> EXEC :stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text => 'select * from a where object_id=20',  user_name => 'SCOTT');
SQL> EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK(:stmt_task);



--检查优化任务的状态
SQL> SELECT task_name,status FROM USER_ADVISOR_TASKS WHERE task_name ='STA_TASK';
TASK_NAME		       STATUS
------------------------------ -----------
STA_TASK		       COMPLETED


--查看优化建议
SET LONG 999999
set serveroutput on size 999999
SET LINESIZE 1000
SQL> select dbms_sqltune.report_tuning_task(:stmt_task) from dual;





--2
SQL> select sql_id,plan_hash_value,sql_fulltext executions from v$sql where sql_id='7jjpxm5p2aaq8';

SQL_ID	      PLAN_HASH_VALUE EXECUTIONS
------------- --------------- --------------------------------------------------------------------------------
7jjpxm5p2aaq8	   2248738933 select * from a where owner='SYS'


SQL> exec :stmt_task:=dbms_sqltune.create_tuning_task(sql_id=>'7jjpxm5p2aaq8',plan_hash_value => 2248738933 ,time_limit=>9,task_name=>'STA_TASK');
SQL> exec dbms_sqltune.execute_tuning_task(task_name=>'STA_TASK');

SET LONG 999999
set serveroutput on size 999999
SET LINESIZE 1000
SQL> select dbms_sqltune.report_tuning_task('STA_TASK') from dual;

DBMS_SQLTUNE.REPORT_TUNING_TASK('STA_TASK')
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : STA_TASK
Tuning Task Owner  : SYS
Workload Type	   : Single SQL Statement
Scope		   : COMPREHENSIVE
Time Limit(seconds): 9
Completion Status  : COMPLETED
Started at	   : 08/07/2023 19:16:11
Completed at	   : 08/07/2023 19:16:12

-------------------------------------------------------------------------------
Schema Name: SCOTT
SQL ID	   : 7jjpxm5p2aaq8
SQL Text   : select * from a where owner='SYS'

-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------

1- Statistics Finding
---------------------
  Table "SCOTT"."A" was not analyzed.

  Recommendation
  --------------
  - Consider collecting optimizer statistics for this table.
    execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname => 'A',
	    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt =>
	    'FOR ALL COLUMNS SIZE AUTO');

  Rationale
  ---------
    The optimizer requires up-to-date statistics for the table in order to
    select a good execution plan.

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original
-----------
Plan hash value: 2248738933

--------------------------------------------------------------------------
| Id  | Operation	  | Name | Rows  | Bytes | Cost (%CPU)| Time	 |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	 | 37040 |  7487K|   344   (1)| 00:00:05 |
|*  1 |  TABLE ACCESS FULL| A	 | 37040 |  7487K|   344   (1)| 00:00:05 |
--------------------------------------------------------------------------

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

--根据提示
execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname => 'A', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt =>'FOR ALL COLUMNS SIZE AUTO');

 select sql_id,plan_hash_value,sql_fulltext executions from v$sql where sql_id='7jjpxm5p2aaq8';

SQL_ID	      PLAN_HASH_VALUE EXECUTIONS
------------- --------------- --------------------------------------------------------------------------------
7jjpxm5p2aaq8	   2248738933 select * from a where owner='SYS'

固定执行计划

SQL> declare
u int;
begin
u:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'7jjpxm5p2aaq8',plan_hash_value=>'2248738933',fixed=>'YES');
end;
/  
PL/SQL procedure successfully completed.

SQL> select plan_name,sql_handle,enabled,accepted,fixed, module,sql_text from dba_sql_plan_baselines;

PLAN_NAME		       SQL_HANDLE		      ENA ACC FIX MODULE							   SQL_TEXT
------------------------------ ------------------------------ --- --- --- ---------------------------------------------------------------- --------------------------------------------------------------------------------
SQL_PLAN_3696gub53bp1k60236d92 SQL_3324cfd2ca35d432	      YES YES YES SQL*Plus

删除sta
exec DBMS_SQLTUNE.DROP_TUNING_TASK (task_name =>'STA_TASK');