oracle SQL優(yōu)化器SQL Tuning Advisor （STA)

一  創(chuàng)建測(cè)試數(shù)據(jù)

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.然后對(duì)這兩個(gè)表，obj與ind進(jìn)行聯(lián)合查詢(xún)，并通過(guò)autotrace查看其執(zhí)行計(jì)劃：

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

通過(guò)執(zhí)行計(jì)劃，可以清晰的看到，在執(zhí)行以上兩個(gè)表的聯(lián)合查詢(xún)的時(shí)候，兩張表走的全表掃和hash join。

3 查看該sql語(yǔ)句的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)進(jìn)行優(yōu)化
1 創(chuàng)建優(yōu)化任務(wù)
通過(guò)調(diào)用函數(shù)DBMS_SQLTUNE.CREATE_TUNING_TASK來(lái)創(chuàng)建優(yōu)化任務(wù)，

調(diào)用存儲(chǔ)過(guò)程DBMS_SQLTUNE.EXECUTE_TUNING_TASK執(zhí)行該任務(wù)：

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 執(zhí)行優(yōu)化任務(wù)

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 檢查優(yōu)化任務(wù)的狀態(tài)
通過(guò)查看user_advisor_tasks/dba_advisor_tasks視圖可以查看優(yōu)化任務(wù)的當(dāng)前狀態(tài)， 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 查看優(yōu)化結(jié)果

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

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

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

執(zhí)行優(yōu)化建議

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 刪除優(yōu)化任務(wù)

通過(guò)調(diào)用dbms_sqltuen.drop_tuning_task可以刪除已經(jīng)存在的優(yōu)化任務(wù)，可以釋放資源。

SQL>exec dbms_sqltune.drop_tuning_task('sql_profile_test_SQLID');