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.
[…] DBMS_AUTO_SQLTUNE: ORA-01748 and Documentation Bugs Morgan’s Library Oracle DBMS_AUTO_SQLTUNE Example Sample Code […]
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
Thank you for posting a possible workaround, Luis.
PS: for the casual reader, the proposed workaround isn’t specific to 19c…