SQL自动优化实践-CSDN博客

sql自动优化调优建议器

– 创建任务

DECLARE  
  my_task_name VARCHAR2(30);  
  my_sqltext   CLOB;  
BEGIN  
  my_sqltext   := 'select count(*) from t where object_id=100';  
  my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text    => my_sqltext,  
   user_name   => 'HDP',   
   scope       => 'COMPREHENSIVE',  
   time_limit  => 600,  
   task_name   => 'hdp_tunesql_task',  
   description => 'Task to tune a query on a specified table');  
END;  
/

– 执行任务

exec DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => ‘hdp_tunesql_task’);

– 查看任务完成状况

SELECT task_name,status FROM USER_ADVISOR_TASKS WHERE task_name =’hdp_tunesql_task’;

– 打印输出信息
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( ‘hdp_tunesql_task’) from DUAL;

– 以下是一些记录过程

SQL> exec DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'hdp_tunesql_task');

PL/SQL procedure successfully completed.

SQL> SELECT task_name,status FROM USER_ADVISOR_TASKS WHERE task_name ='hdp_tunesql_task';

TASK_NAME
--------------------------------------------------------------------------------
STATUS
---------------------------------
hdp_tunesql_task
COMPLETED


SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'hdp_tunesql_task') from DUAL;

DBMS_SQLTUNE.REPORT_TUNING_TASK('HDP_TUNESQL_TASK')
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
----------------------------------------------------



begin
  DBMS_SQLTUNE.drop_tuning_task(task_name =>'hdp_tunesql_task');
end;


SQL> begin
  DBMS_SQLTUNE.drop_tuning_task(task_name =>'hdp_tunesql_task');
end;  2    3
  4  /

PL/SQL procedure successfully completed.

– 查看输出的内容

如下是生成的内容：

GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : hdp_tunesql_task
Tuning Task Owner  : HDP
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 600
Completion Status  : COMPLETED
Started at         : 02/05/2018 12:03:59
Completed at       : 02/05/2018 12:03:59

-------------------------------------------------------------------------------
Schema Name: HDP
SQL ID     : ggs8hb2kntz5y
SQL Text   : select count(*) from t where object_id=100

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

1- Index Finding (see explain plans section below)
--------------------------------------------------
  The execution plan of this statement can be improved by creating one or more
  indices.

  Recommendation (estimated benefit: 99.7%)
  -----------------------------------------
  - Consider running the Access Advisor to improve the physical schema design
    or creating the recommended index.
    create index HDP.IDX$$_1A860001 on HDP.T("OBJECT_ID");

  Rationale
  ---------
    Creating the recommended indices significantly improves the execution plan
    of this statement. However, it might be preferable to run "Access Advisor"
    using a representative SQL workload as opposed to a single statement. This
    will allow to get comprehensive index recommendations which takes into
    account index maintenance overhead and additional space consumption.

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

1- Original
-----------
Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     5 |   345   (1)| 00:00:05 |
|   1 |  SORT AGGREGATE    |      |     1 |     5 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |     1 |     5 |   345   (1)| 00:00:05 |
---------------------------------------------------------------------------

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

   2 - filter("OBJECT_ID"=100)

2- Using New Indices
--------------------
Plan hash value: 255218161

------------------------------------------------------------------------------------
| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                |     1 |     5 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |                |     1 |     5 |            |          |
|*  2 |   INDEX RANGE SCAN| IDX$$_1A860001 |     1 |     5 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------

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

   2 - access("OBJECT_ID"=100)

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