ITPub博客

首页 > 数据库 > Oracle > Oracle OCP 1Z0 053 Q40(DBMS_ADVISOR.QUICK_TUNE)

Oracle OCP 1Z0 053 Q40(DBMS_ADVISOR.QUICK_TUNE)

原创 Oracle 作者:abstractcyj 时间:2016-04-04 14:52:22 0 删除 编辑
40.Evaluate the following code:
SQL>VARIABLE task_name VARCHAR2(255); SQL>VARIABLE sql_stmt VARCHAR2(4000);
SQL>BEGIN :sql_stmt := 'SELECT COUNT(*) FROM customers
WHERE cust_state_province =''CA''';
:task_name := 'MY_QUICKTUNE_TASK';
DBMS_ADVISOR.QUICK_TUNE(DBMS_ADVISOR.SQLACCESS_ADVISOR,
:task_name, :sql_stmt);
END;
What is the outcome of this block of code?
A. It creates a task and workload, and executes the task.
B. It creates a task and workload but does not execute the task.
C. It produces an error because a template has not been created.
D. It produces an error because the SQL Tuning Set has not been created.
Answer: A

如下代码可以执行:
declare
  v_sql_stmt varchar2(4000);
  v_task_name varchar2(200);
BEGIN
  v_sql_stmt  := 'SELECT COUNT(*) FROM emp WHERE ename =''CLARK''';
  v_task_name := 'MY_QUICKTUNE_TASK';
  dbms_advisor.quick_tune(dbms_advisor.sqlaccess_advisor,
                          v_task_name,
                          v_sql_stmt);
END;

通过DBMS_ADVISOR.get_task_script获取结果。

结果是个CLOB:

Rem  SQL Access Advisor: Version 11.2.0.4.0 - Production
Rem  
Rem  Username:        SCOTT
Rem  Task:            MY_QUICKTUNE_TASK
Rem  Execution date:   
Rem  


CREATE INDEX "SCOTT"."EMP_IDX$$_04E40000"
    ON "SCOTT"."EMP"
    ("ENAME")
    COMPUTE STATISTICS;



SQL> declare
  2    v_sql_stmt varchar2(4000);
  3    v_task_name varchar2(200);
  4  BEGIN
  5    v_sql_stmt  := 'SELECT COUNT(*) FROM emp WHERE ename =''CLARK''';
  6    v_task_name := 'MY_QUICKTUNE_TASK2';
  7    dbms_advisor.quick_tune(dbms_advisor.sqlaccess_advisor,
  8                            v_task_name,
  9                            v_sql_stmt);
 10  END;
 11  /


PL/SQL procedure successfully completed.


SQL> SELECT DBMS_ADVISOR.get_task_script('MY_QUICKTUNE_TASK2') AS script
  2  FROM   dual;


SCRIPT
--------------------------------------------------------------------------------
Rem  SQL Access Advisor: Version 11.2.0.4.0 - Production
Rem
Rem  Username:




SQL> 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8520577/viewspace-2075005/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
曾从事java方向开发多年。近年已经转入数据库方向。主要擅长SQL优化,Oracle数据库问题诊断,Oracle备份与恢复等。服务于医药物流,医院等行业

注册时间:2010-01-26

  • 博文量
    555
  • 访问量
    837468