Home » RDBMS Server » Performance Tuning » SQL Access Advisor from SQL*Plus (Oracle 11g, 12c)
SQL Access Advisor from SQL*Plus [message #684586] Sat, 03 July 2021 11:39 Go to next message
db_senior
Messages: 13
Registered: July 2021
Junior Member
Hi,
I Know that the SQL Access Advisor is an useful advisor like SQL Tuning Advisor, It makes suggestions about SQL access methods and helps resolve performance problems in Oracle database.

I create SQL Tuning Advisor manually with 3 sample steps:

1) Create Tuning Task
Quote:

declare
my_task_name VARCHAR2(30);
begin
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_id => '99d2sfn123phq',
scope => 'COMPREHENSIVE',
time_limit => 3600,
task_name => 'sql_tuning_task_99d2sfn123phq',
description => 'Task to tune query 99d2sfn123phq');
DBMS_OUTPUT.PUT_LINE(my_task_name);
end;
/
2) Execute Tuning task
Quote:

begin
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'sql_tuning_task_99d2sfn123phq');
end;
3) Get the Tuning advisor report
Quote:

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'sql_tuning_task_99d2sfn123phq') from DUAL;
How can I create the "SQL Access Advisor" using SQL*Plus (without Enterprise Manager) for sql_id 99d2sfn123phq?

The Oracle documentation is not very clear on this.

Thanks
Re: SQL Access Advisor from SQL*Plus [message #684587 is a reply to message #684586] Sat, 03 July 2021 12:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I don't use EM but is this not what you have done with all these statements?

Re: SQL Access Advisor from SQL*Plus [message #684588 is a reply to message #684586] Sat, 03 July 2021 12:26 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
THis works for me (though whether you think the advice is useful is a different matter):
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>
Re: SQL Access Advisor from SQL*Plus [message #684589 is a reply to message #684588] Sat, 03 July 2021 13:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Even with exactly the same statements than OP and with his version (11.2):
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)

-------------------------------------------------------------------------------
Re: SQL Access Advisor from SQL*Plus [message #684593 is a reply to message #684588] Sun, 04 July 2021 04:45 Go to previous messageGo to next message
db_senior
Messages: 13
Registered: July 2021
Junior Member
Hi John Watson,
Thanks but I'd like to generate SQL Access Advisor based on sql_id not SQL Text. Do you think this is possible?
Re: SQL Access Advisor from SQL*Plus [message #684594 is a reply to message #684593] Sun, 04 July 2021 05:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What about what I posted (that is your code)?

Re: SQL Access Advisor from SQL*Plus [message #684596 is a reply to message #684594] Sun, 04 July 2021 16:11 Go to previous messageGo to next message
db_senior
Messages: 13
Registered: July 2021
Junior Member
Michel Cadot wrote on Sun, 04 July 2021 05:16

What about what I posted (that is your code)?

You posted SQL Tuning Advisor (which I already knew) but I need SQL Access Advisor based on sql_id.
Re: SQL Access Advisor from SQL*Plus [message #684597 is a reply to message #684586] Mon, 05 July 2021 00:38 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Quote:
How can I create the "SQL Access Advisor" using SQL*Plus (without Enterprise Manager) for sql_id 99d2sfn123phq?

The Oracle documentation is not very clear on this.
I've already given you one example. Now it is your turn. What have you tried? What was the problem?
Re: SQL Access Advisor from SQL*Plus [message #684603 is a reply to message #684597] Mon, 05 July 2021 12:35 Go to previous message
db_senior
Messages: 13
Registered: July 2021
Junior Member
John Watson wrote on Mon, 05 July 2021 00:38
Quote:
How can I create the "SQL Access Advisor" using SQL*Plus (without Enterprise Manager) for sql_id 99d2sfn123phq?

The Oracle documentation is not very clear on this.
I've already given you one example. Now it is your turn. What have you tried? What was the problem?
I tried but I cannot see the report:
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>
Previous Topic: Function blocking itself
Next Topic: The predicate prevents the optimizer from using indices efficiently
Goto Forum:
  


Current Time: Thu Mar 28 14:43:27 CDT 2024