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');