Oracle SQL優化之sql tuning advisor

有時在分析複雜SQL效能問題時比較耗時，當沒有頭緒時，可以通過sql tuning advisor看一下oracle的建議，

但是最終是否接受建議，需要DBA根據綜合情況自己判斷，算是增加了一種調優的方式。

一：建立測試資料

SQL> create user cjc identified by cjc;                   
User created.
SQL> grant connect,resource,dba to cjc;
Grant succeeded.
SQL> conn cjc/cjc
Connected.
SQL> create table t1 as select * from dba_tables;
Table created.
SQL> create table t2 as select * from dba_users;
Table created.
SQL> create table t3 as select * from dba_objects;
Table created.
SQL> create table t5 as select * from dba_data_files;
Table created.
SQL> create view v1 as select t1.table_name,t1.owner,t1.TABLESPACE_NAME,t1.NUM_ROWS,t3.CREATED,t3.LAST_DDL_TIME from t1,t3 where t1.table_name=t3.object_name and t1.owner=t3.owner;
View created.

根據實際情況，按使用者、庫或表級別收集統計資訊

---SQL> exec dbms_stats.gather_schema_stats('CJC'); 
---SQL> exec DBMS_STATS.GATHER_DATABASE_STATS (estimate_percent=>100,Degree=>8, Cascade=>TRUE, Granularity=>'ALL');

二：模擬效能差的SQL

SQL> 
set line 300
col table_name for a20
col owner for a20
col TABLESPACE_NAME for a20
select * from v1 where table_name='SDO_FEATURE_USAGE';
TABLE_NAME     OWNER  TABLESPACE_NAME NUM_ROWS CREATED   LAST_DDL_
-------------------- -------------------- -------------------- ---------- --------- ---------
SDO_FEATURE_USAGE    MDSYS  SYSAUX       20 07-NOV-20 07-NOV-20

三：執行sql tuning advisor

檢視耗時sql對應sql_id

SQL>  select sql_id,sql_text from v$sql where sql_text like '%SDO_FEATURE_USAGE%';
20vymfjk9wqw8
select * from v1 where table_name='SDO_FEATURE_USAGE'

生成並執行TUNING TASK

SQL>
DECLARE
  my_task_name VARCHAR2(30);
BEGIN
  my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id      => '20vymfjk9wqw8',
                                                  scope       => 'COMPREHENSIVE',
                                                  time_limit  => 3600,
                                                  task_name   => 'cjc_sql_tuing_task',
                                                  description => 'SQL TUNE TEST');
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'cjc_sql_tuing_task');
END;
/
PL/SQL procedure successfully completed.

檢視是否生成TUNING TASK

SQL> SELECT status FROM  DBA_ADVISOR_TASKS WHERE  task_name = 'cjc_sql_tuing_task';
STATUS
-----------
COMPLETED

檢視建議

根據給出的建議可知，可以建立如下索引：

create index CJC.IDX$$_00970001 on CJC.T3("OBJECT_NAME","OWNER");

效能預計提升98.58%。

SQL> SET LONG 10000
SET LONGCHUNKSIZE 1000
SET LINESIZE 300
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('cjc_sql_tuing_task') FROM   DUAL;

具體建議如下：

DBMS_SQLTUNE.REPORT_TUNING_TASK('CJC_SQL_TUING_TASK')
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : cjc_sql_tuing_task
Tuning Task Owner  : SYS
Workload Type   : Single SQL Statement
Scope   : COMPREHENSIVE
Time Limit(seconds): 3600
Completion Status  : COMPLETED
Started at   : 01/31/2021 19:18:31
Completed at   : 01/31/2021 19:18:31
DBMS_SQLTUNE.REPORT_TUNING_TASK('CJC_SQL_TUING_TASK')
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Schema Name: CJC
SQL ID   : 20vymfjk9wqw8
SQL Text   : select * from v1 where table_name='SDO_FEATURE_USAGE'
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- Index Finding (see explain plans section below)
--------------------------------------------------
DBMS_SQLTUNE.REPORT_TUNING_TASK('CJC_SQL_TUING_TASK')
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  The execution plan of this statement can be improved by creating one or more
  indices.
  Recommendation (estimated benefit: 98.58%)
  ------------------------------------------
  - Consider running the Access Advisor to improve the physical schema design
    or creating the recommended index.
    create index CJC.IDX$$_00970001 on CJC.T3("OBJECT_NAME","OWNER");
  - Consider running the Access Advisor to improve the physical schema design
    or creating the recommended index.
DBMS_SQLTUNE.REPORT_TUNING_TASK('CJC_SQL_TUING_TASK')
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    create index CJC.IDX$$_00970002 on CJC.T1("TABLE_NAME","OWNER");
  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.
-------------------------------------------------------------------------------
DBMS_SQLTUNE.REPORT_TUNING_TASK('CJC_SQL_TUING_TASK')
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 3101620303
---------------------------------------------------------------------------
| Id  | Operation   | Name | Rows  | Bytes | Cost (%CPU)| Time  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |  |1 |    89 |   425   (1)| 00:00:01 |
DBMS_SQLTUNE.REPORT_TUNING_TASK('CJC_SQL_TUING_TASK')
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|*  1 |  HASH JOIN   |  |1 |    89 |   425   (1)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T1   |1 |    33 |    33   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T3   |1 |    56 |   392   (1)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."TABLE_NAME"="T3"."OBJECT_NAME" AND
      "T1"."OWNER"="T3"."OWNER")
   2 - filter("T1"."TABLE_NAME"='SDO_FEATURE_USAGE')
DBMS_SQLTUNE.REPORT_TUNING_TASK('CJC_SQL_TUING_TASK')
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   3 - filter("T3"."OBJECT_NAME"='SDO_FEATURE_USAGE')
2- Using New Indices
--------------------
Plan hash value: 976976346
--------------------------------------------------------------------------------------------------------
| Id  | Operation      | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |     1 |    89 |     6 (0)| 00:00:01 |
|   1 |  NESTED LOOPS      |        |     1 |    89 |     6 (0)| 00:00:01 |
DBMS_SQLTUNE.REPORT_TUNING_TASK('CJC_SQL_TUING_TASK')
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   2 |   NESTED LOOPS      |        |     1 |    89 |     6 (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T3       |     1 |    56 |     4 (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN      | IDX$$_00970001 |     1 |       |     3 (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN      | IDX$$_00970002 |     1 |       |     1 (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID      | T1       |     1 |    33 |     2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("T3"."OBJECT_NAME"='SDO_FEATURE_USAGE')
DBMS_SQLTUNE.REPORT_TUNING_TASK('CJC_SQL_TUING_TASK')
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   5 - access("T1"."TABLE_NAME"='SDO_FEATURE_USAGE' AND "T1"."OWNER"="T3"."OWNER")
-------------------------------------------------------------------------------

###chenjuchao 2021-01-31 19:35###