Databases Reference
In-Depth Information
Since this code is encapsulated within a block of PL/SQL, no line continuation dashes “-” are
required (on the second and third lines).
The second step involves displaying recommendations generated by the prior step. If you're
executing the function from SQL*Plus, set the
LONG
variable to a large number so that the output is fully
displayed. Also, you must provide as input to the
GET_TASK_SCRIPT
the name of the task defined in the
prior step (see Table 10-2 for a description of all
GET_TASK_SCRIPT
parameters). For example,
SQL> SET LONG 100000
SQL> select dbms_advisor.get_task_script('QUICK_SQL_TUNE') from dual;
Here is some sample output for this example:
DBMS_ADVISOR.GET_TASK_SCRIPT('QUICK_SQL_TUNE')
--------------------------------------------------------
Rem SQL Access Advisor: Version 11.2.0.2.0 - Production
Rem
Rem Username: MV_MAINT
Rem Task: QUICK_SQL_TUNE
Rem Execution date:
Rem
CREATE BITMAP INDEX "MV_MAINT"."EMP_IDX$$_099B0000"
ON "MV_MAINT"."EMP"
(UPPER("LAST_NAME"))
COMPUTE STATISTICS;
This output indicates that a bitmap function-based index may help with performance. You will need
to carefully analyze the output and test the recommendations to determine the actual benefit. You
should also consider factors such as whether a bitmap index is appropriate for your environment. Also,
we recommend you modify the script to incorporate your index naming standards and include a
tablespace name for the placement of the index.
If you want to re-execute a tuning task, you must first drop it. You can do so via the
DELETE_TASK
procedure.
SQL> exec dbms_advisor.delete_task('QUICK_SQL_TUNE');
PL/SQL procedure successfully completed.
