有时在分析复杂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###