As of 22.214.171.124 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.