初探S他的使用

利用Oracle 10g提供的SQL Tuning Advisor，DBA可以很容易的來根據STA給出的意見和建議來進行SQL的最佳化，一切終於變的如此的簡單了。

SQL> set timing on
SQL> set autot on
SQL> select count(*) from bigtab a,smalltab b
  2  where a.object_name=b.table_name;

  COUNT(*)
----------
    191597

已用時間:  00: 00: 27.53

執行計劃
----------------------------------------------------------
Plan hash value: 3089226980

--------------------------------------------------------------------------------
| Id  | Operation           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |          |     1 |    83 | 16418   (3)| 00:03:18 |
|   1 |  SORT AGGREGATE     |          |     1 |    83 |            |          |
|*  2 |   HASH JOIN         |          |  1928K|   152M| 16418   (3)| 00:03:18 |
|   3 |    TABLE ACCESS FULL| SMALLTAB |  1594 | 27098 |    12   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| BIGTAB   |  5465K|   343M| 16311   (2)| 00:03:16 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"."OBJECT_NAME"="B"."TABLE_NAME")

Note
-----
   - dynamic sampling used for this statement

統計資訊
----------------------------------------------------------
        446  recursive calls
          0  db block gets
      73581  consistent gets
      73386  physical reads
          0  redo size
        414  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          1  rows processed

現在我們可以建立並執行我們的SQL tuning task
DECLARE
  my_task_name VARCHAR2(30);
  my_sqltext   CLOB;
  BEGIN
  my_sqltext := 'select count(*) from bigtab a, smalltab b where a.object_name=b.table_name';
  my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
                  sql_text => my_sqltext,
                  user_name => 'SYS',
                  scope     => 'COMPREHENSIVE',
                  time_limit => 60,
                  task_name  => 'oracle_ace_task',
                  description => 'Task to tune a query on a specified table');
 
       DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'oracle_ace_task'); 
END;

或者我們可以利用一個procedure來生成

create or replace procedure sql_tuning(my_sqltext in clob,schema_name in varchar2)
is
my_task_name varchar2(30);
begin
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
                  sql_text => my_sqltext,
                  user_name => schema_name,
                  scope     => 'COMPREHENSIVE',
                  time_limit => 60,
                  task_name  => 'oracle_ace_task',
                  description => 'Task to tune a query on a specified table');
 DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'oracle_ace_task'); 
end;

exec sql_tuning('select count(*) from bigtab a, smalltab b where a.object_name=b.table_name','SYS');

在函式CREATE_TUNING_TASK，sql_text是需要最佳化的語句，user_name是該語句透過哪個使用者執行，scope是最佳化範圍（limited或comprehensive），time_limit最佳化過程的時間限制，task_name最佳化任務名稱，description最佳化任務描述。

可以透過檢視USER_ADVISOR_LOG和USER_ADVISOR_LOG來檢視建立過的最佳化任務。

SQL> select task_name, status from USER_ADVISOR_LOG where task_name='oracle_ace_task';
 
TASK_NAME                      STATUS
------------------------------        -----------
oracle_ace_task                COMPLETED

最後我們來生成tuning的報告.
SQL> set long 10000
SQL> set longchunksize 1000
SQL> set linesize 100
SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'tuning_sql_test') from DUAL;
/
PL/SQL procedure successfully completed.