Oracle Consulting Oracle Training Oracle Support Development
Oracle Books
SQL Server Books
IT Books
Job Interview Books
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle


Privacy Policy



dbms_advisor tips

Oracle Tips by Burleson

Oracle 10g popular dbms_advisor procedures

Let's take a closer look at some of the most popular dbms_advisor procedures. For a full list of available procedures, we suggest consulting the Oracle Manual ‘PL/SQL Packages and Types Reference’.

Stage 1 uses the following procedures to manage the SQLAccess Task and workload object.

  • create_task - Creates a new Advisor task in the repository.

  • delete_task - Deletes the specified SQLAccess task from the repository.

  • update_task_attributes - Makes changes to the existing tasks

  • create_sqlwkld - Creates a new workload object

  • delete_sqlwkld - Deletes an entire workload object

  • quick_tune - The operation creates a task using the specified task name. The task will be created using either a specified SQLAccess task template or the built-in default template of sqlaccess_general. This procedure performs all of the necessary operations to analyze a single SQL statement. The operation creates a task for which all parameters are defaulted.

Stage 2 uses the following procedures to make changes to the workload object.

  • import_sqlwkld_sqlcache - Imports data into a workload from the SQL cache

  • add_sqlwkld_statement - Adds a single statement to a workload.

  • delete_sqlwkld_statement - Deletes one or more statements from a workload.

  • update_sqlwkld_statement - Updates one or more SQL statements in a workload.

Stage 3 uses the following procedures to set and reset parameters related to tasks and workload objects.

  • set_task_parameter - Modifies a user parameter within a SQLAccess task or a template.

  • set_sqlwkld_parameter - Sets the value of a workload parameter

  • reset_task - Resets a task to its initial state. All intermediate and recommendation data will be deleted.

  • reset_sqlwkld - Resets a workload to its initial state. All journal and log messages are cleared. Workload data will remain untouched.

Stage 4 uses the following procedures to handle the SQL Workload and the execution of analysis.

  • add_sqlwkld_ref - Establishes a link between the current SQLAccess task and a SQLWkld data object. The link allows an Advisor task to access relevant data for the purpose of doing an analysis.

  • delete_sqlwkld_ref - Removes a link between the current SQLAccess task and a SQLWkld data object.

  • execute_task - Performs the SQLAccess Advisor analysis or evaluation for the specified task. Task execution is a synchronous operation.

  • Oracle 10g popular dbms_advisor procedures

    • interrupt_task - Stops a currently executing task. The task will end its operations as it would at a normal exit.

    • cancel_task - Causes a currently executing operation to terminate. This call does a soft interrupt.

    • mark_recommendation - Marks a recommendation for import or implementation.

    • update_rec_attributes - Updates the owner and name of a new object as recommended by Advisor analysis.

    • create_task_script - Creates a SQL*Plus-compatible SQL script and sends the output to file. The script will contain all of the accepted recommendations from the specified task.

    The popular procedures and basic workflow stages are shown in Figure 16.2

    Fig 16.2 Procedures and their relation with tasks and workload objects


Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster

Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA   



 Copyright © 1996 -2017 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks