WorkLoad

The SQL Access Advisor can be used without a workload; thereby, generating and using a hypothetical workload based on the dimensions defined in the schema. For best results, a workload must be provided in the form of an SQL Tuning Set, a user supplied table, or imported from SQL Cache.

The recommendation process including types and naming conventions, and workload customization including duration and filtering are controlled by SQL Access Advisor parameters. The values of these parameters are valid for the life span of the task or workload object, and can be set using set_task_parameter and set_sqlwkld_parameter.

Once a task exists and is linked to a workload, use the dbms_advisor.execute_task procedure to generate the recommendations. They are in-turn stored in the SQL Access Advisor repository. The status of this job can be viewed with dba_advisor_log. To view the recommendations, use the catalog views: user_advisor_actions, user_advisor_recommendations, and user_advisor_sqla_wk_stmts. The user views are for any user who uses the advisor tool. A DBA can look at the dba version of these views or get a script using use dbms_advisor.get_task_script procedure. OEM can also be used to get recommendations.

All the recommendations are stored in the SQL Access Advisor repository, which is a part of the Oracle database dictionary. This repository has many benefits such as being managed by the server, support of historical data, and etc.

Figure 7.9 - SQL Access Advisor