手工调用sql_turning_advisor

一、创建sql

SQL> select sql_id,sql_text from v$sql where sql_text like 'select * from t where object_id=999%';

SQL_ID                            SQL_TEXT
-------------        ------------------------------------------------------------
8r2k1grn6wran       select * from t where object_id=999

二、查看任务

SQL> SET SERVEROUTPUT ON
SQL> declare
  2  stmt_task VARCHAR2(40);
  3  begin
  4  stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => '8r2k1grn6wran');
  5  DBMS_OUTPUT.put_line('task_id: ' || stmt_task );
  6  end;
  7  /
task_id: TASK_11

PL/SQL procedure successfully completed.

SQL> select task_name from dba_advisor_log
  2  ;

TASK_NAME
--------------------------------------------------------------------------------------------------------------------------------
TASK_11
SYS_AUTO_SPM_EVOLVE_TASK
SYS_AI_SPM_EVOLVE_TASK
SYS_AI_VERIFY_TASK
SYS_AUTO_INDEX_TASK
AUTO_STATS_ADVISOR_TASK
INDIVIDUAL_STATS_ADVISOR_TASK

7 rows selected.

三、执行任务

SQL> Execute dbms_sqltune.Execute_tuning_task (task_name => 'TASK_11');

PL/SQL procedure successfully completed.

SQL> select status from dba_advisor_log where task_name='TASK_11';

STATUS
-----------
COMPLETED

四、查看结果

SQL> set long 65536
SQL> set longchunksize 65536
SQL> set linesize 100
SQL> select dbms_sqltune.report_tuning_task('TASK_11') from dual;


DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_11')
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : TASK_11
Tuning Task Owner  : SYS
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status  : COMPLETED
Started at         : 09/19/2022 17:13:40
Completed at       : 09/19/2022 17:13:41

-------------------------------------------------------------------------------
Schema Name   : SYS
Container Name: PDBPROD1
SQL ID        : 8r2k1grn6wran
SQL Text      : select * from t where object_id=999

-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------

1- Statistics Finding
---------------------
  Table "SYS"."T" was not analyzed.

  Recommendation
  --------------
  - Consider collecting optimizer statistics for this table.
    execute dbms_stats.gather_table_stats(ownname => 'SYS', tabname => 'T',
            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.

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original
-----------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   123 | 59163 |  3046   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |   123 | 59163 |  3046   (1)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=999)

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


SQL> SQL> 

execute dbms_stats.gather_table_stats(ownname => 'SYS', tabname => 'T',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt =>'FOR ALL COLUMNS SIZE AUTO');

SQL> set autotrace traceonly;
SQL> select * from t where object_id=999;

8 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     8 |  1056 |  3047   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |     8 |  1056 |  3047   (1)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=999)


Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
      11234  consistent gets
          0  physical reads
          0  redo size
       2941  bytes sent via SQL*Net to client
        397  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          8  rows processed