orclz>
orclz> create table skew (pk number,nu number,vc varchar2(10));

Table created.

orclz> insert into skew (select rownum,1,'all ones' from dual connect by level < 1000000);

999999 rows created.

orclz> insert into skew values(0,0,'zero');

1 row created.

orclz> commit;

Commit complete.

orclz> exec dbms_Stats.gather_table_Stats(user,'skew')

PL/SQL procedure successfully completed.

orclz> --create a simple task:
orclz> exec dbms_advisor.quick_tune(dbms_advisor.sqlaccess_advisor,'quick','select count(pk) from skew where pk=0');

PL/SQL procedure successfully completed.

orclz> set long 1000000
orclz> set lin window
orclz> select dbms_advisor.get_task_script('quick') from dual;

DBMS_ADVISOR.GET_TASK_SCRIPT('QUICK')
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Rem  SQL Access Advisor: Version 19.0.0.0.0 - Production
Rem
Rem  Username:        SCOTT
Rem  Task:            quick
Rem  Execution date:
Rem

CREATE MATERIALIZED VIEW LOG ON
    "SCOTT"."SKEW"
    WITH ROWID, SEQUENCE("PK")
    INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW "SCOTT"."MV$$_04490000"
    REFRESH FAST WITH ROWID
    ENABLE QUERY REWRITE
    AS SELECT "SCOTT"."SKEW"."PK" C1, COUNT("SCOTT"."SKEW"."PK") M1, COUNT(*) M2 FROM
       "SCOTT"."SKEW" WHERE ("SCOTT"."SKEW"."PK" = 0) GROUP BY "SCOTT"."SKEW"."PK";

begin
  dbms_stats.gather_table_stats('"SCOTT"','"MV$$_04490000"',NULL,dbms_stats.auto_sample_size);
end;
/



orclz>

SQL> declare
  2  my_task_name VARCHAR2(30);
  3  begin
  4  my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
  5  sql_id => 'akh9zqqkx3wj7',
  6  scope => 'COMPREHENSIVE',
  7  time_limit => 3600,
  8  task_name => 'sql_tuning_task_akh9zqqkx3wj7',
  9  description => 'Task to tune query akh9zqqkx3wj7');
 10  DBMS_OUTPUT.PUT_LINE(my_task_name);
 11  end;
 12  /
sql_tuning_task_akh9zqqkx3wj7

PL/SQL procedure successfully completed.

SQL> begin
  2  DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'sql_tuning_task_akh9zqqkx3wj7');
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'sql_tuning_task_akh9zqqkx3wj7') from DUAL;
DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_TUNING_TASK_AKH9ZQQKX3WJ7')
------------------------------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : sql_tuning_task_akh9zqqkx3wj7
Tuning Task Owner  : MICHEL
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 3600
Completion Status  : COMPLETED
Started at         : 07/03/2021 20:16:52
Completed at       : 07/03/2021 20:16:55

-------------------------------------------------------------------------------
Schema Name: SYS
SQL ID     : akh9zqqkx3wj7
SQL Text   : select 1, max(id) from sys.wri$_adv_objects  where task_id = :1
             union all select 2, max(id) from sys.wri$_adv_recommendations
             where task_id = :1 union all select 3, max(id) from
             sys.wri$_adv_actions  where task_id = :1 union all select 4,
             max(id) from sys.wri$_adv_findings  where task_id = :1 union all
             select 5, max(id) from sys.wri$_adv_rationale  where task_id =
             :1
Bind Variables :
 1 -  (NUMBER):79856
 2 -  (NUMBER):79856
 3 -  (NUMBER):79856
 4 -  (NUMBER):79856
 5 -  (NUMBER):79856

-------------------------------------------------------------------------------
FINDINGS SECTION (5 findings)
-------------------------------------------------------------------------------

1- Statistics Finding
---------------------
  Optimizer statistics for table "SYS"."WRI$_ADV_OBJECTS" and its indices are
  stale.

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

2- Statistics Finding
---------------------
  Optimizer statistics for table "SYS"."WRI$_ADV_RECOMMENDATIONS" and its
  indices are stale.

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

3- Statistics Finding
---------------------
  Optimizer statistics for table "SYS"."WRI$_ADV_ACTIONS" and its indices are
  stale.

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

4- Statistics Finding
---------------------
  Optimizer statistics for table "SYS"."WRI$_ADV_FINDINGS" and its indices are
  stale.

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

5- Statistics Finding
---------------------
  Optimizer statistics for table "SYS"."WRI$_ADV_RATIONALE" and its indices
  are stale.

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

-------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                       |     5 |    42 |    10   (0)| 00:00:01 |
|   1 |  UNION-ALL                    |                       |       |       |            |          |
|   2 |   SORT AGGREGATE              |                       |     1 |     9 |            |          |
|   3 |    FIRST ROW                  |                       |     1 |     9 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN (MIN/MAX)| WRI$_ADV_OBJECTS_PK   |     1 |     9 |     2   (0)| 00:00:01 |
|   5 |   SORT AGGREGATE              |                       |     1 |     8 |            |          |
|   6 |    FIRST ROW                  |                       |     1 |     8 |     2   (0)| 00:00:01 |
|*  7 |     INDEX RANGE SCAN (MIN/MAX)| WRI$_ADV_REC_PK       |     1 |     8 |     2   (0)| 00:00:01 |
|   8 |   SORT AGGREGATE              |                       |     1 |     8 |            |          |
|   9 |    FIRST ROW                  |                       |     1 |     8 |     2   (0)| 00:00:01 |
|* 10 |     INDEX RANGE SCAN (MIN/MAX)| WRI$_ADV_ACTIONS_PK   |     1 |     8 |     2   (0)| 00:00:01 |
|  11 |   SORT AGGREGATE              |                       |     1 |     9 |            |          |
|  12 |    FIRST ROW                  |                       |     1 |     9 |     2   (0)| 00:00:01 |
|* 13 |     INDEX RANGE SCAN (MIN/MAX)| WRI$_ADV_FINDINGS_PK  |     1 |     9 |     2   (0)| 00:00:01 |
|  14 |   SORT AGGREGATE              |                       |     1 |     8 |            |          |
|  15 |    FIRST ROW                  |                       |     1 |     8 |     2   (0)| 00:00:01 |
|* 16 |     INDEX RANGE SCAN (MIN/MAX)| WRI$_ADV_RATIONALE_PK |     1 |     8 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

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

   4 - access("TASK_ID"=:1)
   7 - access("TASK_ID"=:1)
  10 - access("TASK_ID"=:1)
  13 - access("TASK_ID"=:1)
  16 - access("TASK_ID"=:1)

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

SQL> set long 1000000
SQL> set lin window
SQL> select dbms_advisor.get_task_script('quick') from dual;

DBMS_ADVISOR.GET_TASK_SCRIPT('QUICK')
--------------------------------------------------------------------------------
Rem  SQL Access Advisor: Version 19.0.0.0.0 - Production
Rem
Rem  Username:        USR_TEST
Rem  Task:            quick
Rem  Execution date:
Rem

SQL>