Striving for Optimal Performance
  • Blog
    • Archive
    • Categories
    • Search
  • Troubleshooting Oracle Performance
    • Description
    • Structure
    • Table of Contents
    • Forewords
    • Reviews
    • Downloadable Files
    • Addenda and Errata
    • First Edition
  • Public Appearances
    • Past Public Appearances
  • Contact
  • About

DBMS_AUTO_SQLTUNE: ORA-01748 and Documentation Bugs

6 October 2010 4 Comments Written by Christian Antognini

As of 11.2.0.2 a new package, DBMS_AUTO_SQLTUNE, is available for accessing and configuring Automatic SQL Tuning. The package provides three features:

  • Execution of the Automatic SQL Tuning task (EXECUTE_AUTO_TUNING_TASK)
  • Generation of a report showing the output generated by the Automatic SQL Tuning tasks (REPORT_AUTO_TUNING_TASK)
  • Configuration of the Automatic SQL Tuning parameters (SET_AUTO_TUNING_TASK_PARAMETER)

In this post I would like to focus on the second functionality. With it you can for example execute the following commands in SQL*Plus to get a report for the most recent task:

SET LONG 1000000 PAGESIZE 0 LINESIZE 200
COLUMN report_auto_tuning_task FORMAT A200
SELECT dbms_auto_sqltune.report_auto_tuning_task FROM dual;

The REPORT_AUTO_TUNING_TASK function is not limited to being used without parameters. In fact, its signature is the following:

FUNCTION REPORT_AUTO_TUNING_TASK RETURNS CLOB
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 BEGIN_EXEC                     VARCHAR2                IN     DEFAULT
 END_EXEC                       VARCHAR2                IN     DEFAULT
 TYPE                           VARCHAR2                IN     DEFAULT
 LEVEL                          VARCHAR2                IN     DEFAULT
 SECTION                        VARCHAR2                IN     DEFAULT
 OBJECT_ID                      NUMBER                  IN     DEFAULT
 RESULT_LIMIT                   NUMBER                  IN     DEFAULT

The purpose of the parameters is the following:

  • BEGIN_EXEC/END_EXEC specifies which tasks have to be reported. With the default value, NULL, the most recent task is shown.
  • TYPE specifies the type of the report. Presently only TEXT is supported.
  • LEVEL specifies the level of detail shown in the report. This is somewhat similar to the FORMAT parameter of DBMS_XPLAN. The supported values are BASIC, TYPICAL (default) and ALL.
  • SECTION specifies which sections are shown in the report. The supported values are SUMMARY, FINDINGS, PLANS, INFORMATION, ERRORS and ALL (default). Possibly there is a bug with the value ERRORS. In fact, during my tests, it always returned the same report as the value INFORMATION!?!?
  • OBJECT_ID restricts the report to a single SQL statement. You can get the ID either from the report or by querying DBA_ADVISOR_OBJECTS.OBJECT_ID.
  • RESULT_LIMIT specifies the maximum number of SQL statements shown in the report.

The essential thing I would like to point out is that two parameters have as name a reserved word.

SQL> SELECT keyword
  2  FROM v$reserved_words
  3  WHERE keyword IN ('BEGIN_EXEC','END_EXEC','TYPE','LEVEL','SECTION','OBJECT_ID','RESULT_LIMIT');

KEYWORD
------------------------------
LEVEL
TYPE

As a result expect to get an error like the following one if you want to use named notation:

SELECT dbms_auto_sqltune.report_auto_tuning_task(level => 'basic') FROM dual
                                                       *
ERROR at line 1:
ORA-01748: only simple column names allowed here

These are the kind of things I cannot understand! It’s so easy to choose a name that doesn’t lead to such problems. For one time I hope that Oracle will break backward compatibility and change the name of these parameters.

Another thing I wanted to point out is that the values supported by the SECTION parameter are the ones I reported above, and not the ones documented here. In fact, there are two typos in the documentation. This is probably because the same typos can also be seen in $ORACLE_HOME/rdbms/admin/dbmssqlt.sql. In other words, it seems that the guy who wrote the documentation did a simple copy/paste of the content of the SQL file.

11gR2, Bug, Query Optimizer
UKOUG Conference in Birmingham
TKPROF New Features in 11gR2 – Release 11.2.0.1

4 Comments

1 Ping/Trackback

  1. What is new with Automatic SQL Tuning in 11.2.0.2? « Julian Dontcheff's Database Blog What is new with Automatic SQL Tuning in 11.2.0.2? « Julian Dontcheff's Database Blog
    24 April 2011    

    […] DBMS_AUTO_SQLTUNE: ORA-01748 and Documentation Bugs Morgan’s Library Oracle DBMS_AUTO_SQLTUNE Example Sample Code […]

  2. Uwe Küchler Uwe Küchler
    29 January 2013    

    Christian,
    just for completeness: The same goes for DBMS_ADVISOR.GET_TASK_REPORT and other subprograms of this package, where the reserved keywords “TYPE” and “LEVEL” are used as parameters. Argh. Looks like some folks at Oracle don’t bother to read their own documentation…

    Cheers,
    Uwe

    Reply
  3. Luis Santos Luis Santos
    30 August 2019    
    [19c]>  SELECT dbms_auto_sqltune.report_auto_tuning_task(level => 'basic') FROM dual;
     SELECT dbms_auto_sqltune.report_auto_tuning_task(level => 'basic') FROM dual
                                                            *
    ERROR at line 1:
    ORA-01748: only simple column names allowed here
    
    
    [19c]> SELECT dbms_auto_sqltune.report_auto_tuning_task("LEVEL" => 'basic') FROM dual;
    DBMS_AUTO_SQLTUNE.REPORT_AUTO_TUNING_TASK(LEVEL=>'BASIC')
    ------------------------------------------------------------------------------------------------------------------------------------
    GENERAL INFORMATION SECTION
    -------------------------------------------------------------------------------
    Tuning Task Name                        : SYS_AUTO_SQL_TUNING_TASK
    Tuning Task Owner                       : SYS
    Workload Type                           : Automatic High-Load SQL Workload
    Execution Count                         : 30
    Current Execution                       : EXEC_3842
    Execution Type                          : TUNE SQL
    Scope                                   : COMPREHENSIVE
    Global Time Limit(seconds)              : 3600
    Per-SQL Time Limit(seconds)             : 1200
    Completion Status                       : COMPLETED
    Started at                              : 08/29/2019 22:00:02
    Completed at                            : 08/29/2019 22:00:35
    Number of Candidate SQLs                : 14
    Cumulative Elapsed Time of SQL (s)      : 133
    ...
    Reply
    • Christian Antognini Christian Antognini
      30 August 2019    

      Thank you for posting a possible workaround, Luis.

      PS: for the casual reader, the proposed workaround isn’t specific to 19c…

      Reply
  1. What is new with Automatic SQL Tuning in 11.2.0.2? « Julian Dontcheff's Database Blog on 24 April 2011 at 12:33

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.