oracle SQL优化器SQL Tuning Advisor （STA)

一  创建测试数据 SQL> conn scott/oracle Connected. SQL> create table obj as select * from dba_objects; Table created. SQL> create table ind as select * from dba_indexes; Table created. SQL> insert into obj select * from obj; 86965 rows created. SQL> insert into obj select * from obj; 173930 rows created. SQL> insert into obj select * from obj; 347860 rows created. SQL> insert into obj select * from obj; 695720 rows created. SQL> commit   2  ; Commit complete. SQL> insert into ind select * from ind; 5069 rows created. SQL> insert into ind select * from ind; 10138 rows created. SQL> insert into ind select * from ind; 20276 rows created. SQL> commit; Commit complete. 2.然后对这两个表，obj与ind进行联合查询，并通过autotrace查看其执行计划： SQL> set timing on SQL> set autot trace SQL> set line 160 SQL> select count(*) from obj o, ind i where o.object_name=i.index_name; Elapsed: 00:00:00.23 Execution Plan ---------------------------------------------------------- Plan hash value: 380737209 ------------------------------------------------------------------------------------ | Id  | Operation     | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time    | ------------------------------------------------------------------------------------ |   0 | SELECT STATEMENT    |    | 1 | 83 |    | 11272   (1)| 00:02:16 | |   1 |  SORT AGGREGATE     |    | 1 | 83 |    | |    | |*  2 |   HASH JOIN     |    | 13M|  1086M|  1416K| 11272   (1)| 00:02:16 | |   3 |    TABLE ACCESS FULL| IND  | 49775 |   826K|    |   378   (0)| 00:00:05 | |   4 |    TABLE ACCESS FULL| OBJ  |  1456K| 91M|    |  5413   (1)| 00:01:05 | ------------------------------------------------------------------------------------ Predicate Information (identified by operation id): ---------------------------------------------------    2 - access("O"."OBJECT_NAME"="I"."INDEX_NAME") Note -----    - dynamic sampling used for this statement (level=2) Statistics ----------------------------------------------------------   0  recursive calls   0  db block gets       21308  consistent gets   0  physical reads   0  redo size 528  bytes sent via SQL*Net to client 523  bytes received via SQL*Net from client   2  SQL*Net roundtrips to/from client   0  sorts (memory)   0  sorts (disk)   1  rows processed 通过执行计划，可以清晰的看到，在执行以上两个表的联合查询的时候，两张表走的全表扫和hash join。 3 查看该sql语句的sql_id SQL> set autot off SQL> set timing off SQL> set line 160 SQL>col sql_text for a65 select sql_id, sql_text, optimizer_mode, plan_hash_value, child_number from v$sql where sql_text like 'select count(*) from obj o, ind i where o.object_name=i.index_name%';SQL> SQL>  SQL_ID       SQL_TEXT       OPTIMIZER_ PLAN_HASH_VALUE CHILD_NUMBER ------------- ------------------------------------------------------- ---------- --------------- ------------ 8xwgyq8mkv86x select count(*) from obj o, ind i where o.object_name=i ALL_ROWS        380737209     0       .index_name 二 使用SQL Tuning Advisor （STA)进行优化 1 创建优化任务 通过调用函数DBMS_SQLTUNE.CREATE_TUNING_TASK来创建优化任务， 调用存储过程DBMS_SQLTUNE.EXECUTE_TUNING_TASK执行该任务： SQL> DECLARE  a_tuning_task VARCHAR2(30);  BEGIN a_tuning_task := dbms_sqltune.create_tuning_task(sql_id    => ' 8xwgyq8mkv86x',  task_name => ' sql_profile_test_SQLID'); dbms_sqltune.execute_tuning_task(a_tuning_task);  END;  /  PL/SQL procedure successfully completed. 2 执行优化任务 SQL> conn / as sysdba Connected. SQL> grant advisor to scott; Grant succeeded. SQL> conn scott/oracle Connected. SQL> exec dbms_sqltune.execute_tuning_task('sql_profile_test_SQLID'); PL/SQL procedure successfully completed. 3 检查优化任务的状态 通过查看user_advisor_tasks/dba_advisor_tasks视图可以查看优化任务的当前状态， COMPLETED表示完成 SQL> SELECT task_name,status FROM USER_ADVISOR_TASKS WHERE task_name ='sql_profile_test_SQLID'; TASK_NAME        STATUS ------------------------------ ----------- sql_profile_test_SQLID         COMPLETED 4 查看优化结果 set long 999999 set serveroutput on size 999999 set line 160 select DBMS_SQLTUNE.REPORT_TUNING_TASK(' sql_profile_test_SQLID') from dual; DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST_SQLID') -------------------------------------------------------------------------------- GENERAL INFORMATION SECTION ------------------------------------------------------------------------------- Tuning Task Name   : sql_profile_test_SQLID Tuning Task Owner  : SCOTT Workload Type    : Single SQL Statement Execution Count    : 2 Current Execution  : EXEC_314 Execution Type    : TUNE SQL Scope    : COMPREHENSIVE Time Limit(seconds): 1800 Completion Status  : COMPLETED DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST_SQLID') -------------------------------------------------------------------------------- Started at    : 04/12/2020 18:23:49 Completed at    : 04/12/2020 18:23:49 ------------------------------------------------------------------------------- Schema Name: SCOTT SQL ID    : 8xwgyq8mkv86x SQL Text   : select count(*) from obj o, ind i where      o.object_name=i.index_name ------------------------------------------------------------------------------- FINDINGS SECTION (2 findings) DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST_SQLID') -------------------------------------------------------------------------------- ------------------------------------------------------------------------------- 1- Statistics Finding ---------------------   Table "SCOTT"."IND" was not analyzed.   Recommendation   --------------   - Consider collecting optimizer statistics for this table.     execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname =>     'IND', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST_SQLID') --------------------------------------------------------------------------------     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. 2- Statistics Finding ---------------------   Table "SCOTT"."OBJ" was not analyzed. DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST_SQLID') --------------------------------------------------------------------------------   Recommendation   --------------   - Consider collecting optimizer statistics for this table.     execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname =>     'OBJ', 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. DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST_SQLID') -------------------------------------------------------------------------------- ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original ----------- Plan hash value: 380737209 -------------------------------------------------------------------------------- ---- DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST_SQLID') -------------------------------------------------------------------------------- | Id  | Operation     | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time    | -------------------------------------------------------------------------------- ---- |   0 | SELECT STATEMENT    |    | 1 | 83 |    | 11272   (1)| 00:02: 16 | |   1 |  SORT AGGREGATE     |    | 1 | 83 |    | |    | |*  2 |   HASH JOIN     |    | 13M|  1086M|  1416K| 11272   (1)| 00:02: 16 | |   3 |    TABLE ACCESS FULL| IND  | 49775 |   826K|    |   378   (0)| 00:00: DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST_SQLID') -------------------------------------------------------------------------------- 05 | |   4 |    TABLE ACCESS FULL| OBJ  |  1456K| 91M|    |  5413   (1)| 00:01: 05 | -------------------------------------------------------------------------------- ---- Predicate Information (identified by operation id): ---------------------------------------------------    2 - access("O"."OBJECT_NAME"="I"."INDEX_NAME") DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST_SQLID') -------------------------------------------------------------------------------- ------------------------------------------------------------------------------- 执行优化建议 SQL> execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname =>'OBJ', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => 'FOR ALL COLUMNS SIZE AUTO');   PL/SQL procedure successfully completed. SQL> execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname =>'IND', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => 'FOR ALL COLUMNS SIZE AUTO'); PL/SQL procedure successfully completed. 5 删除优化任务 通过调用dbms_sqltuen.drop_tuning_task可以删除已经存在的优化任务，可以释放资源。 SQL>exec dbms_sqltune.drop_tuning_task('sql_profile_test_SQLID');