OCP-1Z0-053-V13.02-221题

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;
/

 
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 2
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值