221.To generate recommendations to improve the performance of a set of SQL queries in an application,
you execute the following blocks of code:
BEGIN dbms_advisor.create_task(dbms_advisor.sqlaccess_advisor,'TASK1'); END;/
BEGIN dbms_advisor.set_task_parameter('TASK1','ANALYSIS_SCOPE','ALL');
dbms_advisor.set_task_parameter('TASK1','MODE','COMPREHENSIVE');
END;
/
BEGIN
dbms_advisor.execute_task('TASK1');
dbms_output.put_line(dbms_advisor.get_task_script('TASK1'));
END;
/
The blocks of code execute successfully; however, you do not get the required outcome.
What could be the reason?
A. A template needs to be associated with the task.
B. A workload needs to be associated with the task.
C. The partial or complete workload scope needs to be associated with the task.
D. The type of structures (indexes, materialized views, or partitions) to be recommended need to be
specified for the task.
Answer: B
答案解析:
参考:http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_advis.htm#ARPLS65121
GET_TASK_SCRIPT Function
Creates and returns an executable SQL script of the Advisor task's recommendations in a buffer
SQL Access Advisor
GET_TASK_SCRIPT Function
This function 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.
Syntax
DBMS_ADVISOR.GET_TASK_SCRIPT ( task_name IN VARCHAR2 type IN VARCHAR2 := 'IMPLEMENTATION', rec_id IN NUMBER := NULL, act_id IN NUMBER := NULL, owner_name IN VARCHAR2 := NULL, execution_name IN VARCHAR2 := NULL, object_id IN NUMBER := NULL)RETURN CLOB; Parameters
Table 18-19 GET_TASK_SCRIPT Function Parameters
ParameterDescription task_name
The task name that uniquely identifies an existing task.
type
Specifies the type of script to generate. The possible values are IMPLEMENTATION and UNDO.
rec_id
An optional recommendation identifier number that can be used to extract a subset of the implementation script.
A zero or the value DBMS_ADVISOR.ADVISOR_ALL indicates all accepted recommendations would be included. The default is to include all accepted recommendations for the task.
act_id
Optional action identifier number that can be used to extract a single action as a DDL command.
A zero or the value DBMS_ADVISOR.ADVISOR_ALL indicates all actions for the recommendation would be included. The default is to include all actions for a recommendation.
owner_name
An optional task owner name.
execution_name
An identifier of a specific execution of the task. It is needed only for advisors that allow their tasks to be executed multiple times.
object_id
An identifier of an advisor object that can be targeted by the script.
Return Values
Returns the script as a CLOB buffer.
Usage Notes
Though the script is ready to execute, Oracle recommends that the user review the script for acceptable locations for new materialized views and indexes.
For a recommendation to appear in a generated script, it must be marked as accepted.
Examples
DECLARE task_id NUMBER; task_name VARCHAR2(30); workload_name VARCHAR2(30); buf CLOB;BEGIN task_name := 'My Task'; workload_name := 'My Workload'; DBMS_ADVISOR.CREATE_TASK(DBMS_ADVISOR.SQLACCESS_ADVISOR, task_id, task_name); DBMS_ADVISOR.CREATE_SQLWKLD(workload_name, 'My Workload'); DBMS_ADVISOR.ADD_SQLWKLD_REF(task_name, workload_name); DBMS_ADVISOR.ADD_SQLWKLD_STATEMENT(workload_name, 'MONTHLY', 'ROLLUP', 100,400,5041,103,640445,680000,2, 1,SYSDATE,1,'SH','SELECT AVG(amount_sold) FROM sh.sales'); DBMS_ADVISOR.EXECUTE_TASK(task_name); buf := DBMS_ADVISOR.GET_TASK_SCRIPT(task_name);END;/
版权声明:本文为博主原创文章,未经博主允许不得转载。