618 Oracle Database 11g DBA Handbook
The workload consists of one or more SQL statements plus the statistics and attributes that
relate to the statement. The workload may include all SQL statements for an application. The SQL
Access Advisor ranks the entries in the workload according to statistics and business importance.
The workload is created using the DBMS_ADVISOR.CREATE_SQLWKLD procedure. To associate
a workload with a parent Advisor task, use the DBMS_ADVISOR.ADD_SQLWKLD_REF procedure.
If a workload is not provided, the SQL Access Advisor can generate and use a hypothetical workload
based on the dimensions defined in your schema.
Once a task exists and a workload is associated with the task, you can generate recommendations
via the DBMS_ADVISOR.EXECUTE_TASK procedure. The SQL Access Advisor will consider the
workload and the system statistics and will attempt to generate recommendations for tuning the
application. You can see the recommendations by executing the DBMS_ADVISOR.GET_TASK_
SCRIPT function or via data dictionary views. Each recommendation can be viewed via USER_
ADVISOR_RECOMMENDATIONS (there are ???ALL??? and ???DBA??? versions of this view available as
well). To relate recommendations to a SQL statement, you will need to use the USER_ADVISOR_
SQLA_WK_STMTS view and USER_ADVISOR_ACTIONS.
NOTE
See Chapter 6 for more examples of using the DBMS_ADVISOR
package.
When you execute the GET_TASK_SCRIPT procedure, Oracle generates an executable SQL
file that will contain the commands needed to create, alter, or drop the recommended objects.
Pages:
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910