Run SQL Tuning advisory manually.

Sometimes OEM takes longer to run SQL tuning, but we can do it much faster than OEM using a manual technique.
Here's a short demonstration.

1. Create a tuning task

SET serveroutput ON

DECLARE

  l_sql_tune_task_id  VARCHAR2(100);

BEGIN

  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (

                          sql_id      => '&&my_sql_id',

                          scope       => DBMS_SQLTUNE.scope_comprehensive,

                          time_limit  => 60,

                          task_name   => 'sql_tuning_task_&&my_sql_id',

                          description => 'Tuning task for statement &&my_sql_id.');

  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

END;

/

2. Verify, 

SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_&&my_sql_id';

3. Execute  SQL Tuning task

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'sql_tuning_task_9tjc80371sgwy');

4. Verify the executed SQL Tuning task, It should be completed.

SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_&&my_sql_id';

5. View SQL Tuning Advisor recommendations.

SET LINES 150

SET pages 50000

SET long 5000000

SET longc 5000000

 

SELECT DBMS_SQLTUNE.report_tuning_task('sql_tuning_task_9tjc80371sgwy') AS recommendations FROM dual;

SELECT DBMS_SQLTUNE.report_tuning_task('sql_tuning_task_9tjc80371sgwy') AS recommendations FROM dual;SQL> SQL> SQL> SQL> SQL>

RECOMMENDATIONS

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

GENERAL INFORMATION SECTION

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

Tuning Task Name   : sql_tuning_task_9tjc80371sgwy

Tuning Task Owner  : SYS

Workload Type      : Single SQL Statement

Execution Count    : 2

Current Execution  : EXEC_252

Execution Type     : TUNE SQL

Scope              : COMPREHENSIVE

Time Limit(seconds): 60

Completion Status  : COMPLETED

Started at         : 12/09/2021 10:07:36

Completed at       : 12/09/2021 10:07:37

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

Schema Name   : NFROUGH

Container Name: PDB1

SQL ID        : 9tjc80371sgwy

SQL Text      : select * from tab1 where JOB is not null

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

FINDINGS SECTION (1 finding)

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

1- Statistics Finding

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

  Table "NFROUGH"."TAB1" was not analyzed.

  Recommendation

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

  - Consider collecting optimizer statistics for this table.

    execute dbms_stats.gather_table_stats(ownname => 'NFROUGH', tabname =>

            'TAB1', 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: 2211052296

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

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |      |    34M|   956M| 21456   (2)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| TAB1 |    34M|   956M| 21456   (2)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   1 - filter("JOB" IS NOT NULL)

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

-- Thank you.