Oracle OCP 1Z0-050（26题）解析

Oracle OCP 1Z0-050（26题）解析

QUESTION 26:

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.SQL ACCESS_ADVISOR, :task_name, :sql_stmt);

END;

What is the outcome of this block of code?

A. It creates a task and workload but does not execute the task.

B. It produces an error because the SQL Tuning Set has not been created.

C. It produces an error because a template has not been created.

D. It creates a task and workload, and executes the task.

Answer: D

解析：

参考Oracle官方文档：

https://docs.oracle.com/cd/E11882_01/server.112/e41573/advisor.htm#PFGRF94918

 

Using SQL Access Advisor in Enterprise Manager or API, you can do the following:

• Recommend materialized views and indexes based on collected, user-supplied, or hypothetical workload information.

• Recommend partitioning of tables, indexes, and materialized views.

• Mark, update, and remove recommendations.

In addition, you can use SQL Access Advisor API to do the following:

• Perform a quick tune using a single SQL statement.

• Show how to make a materialized view fast refreshable.

• Show how to change a materialized view so that general query rewrite is possible.

 

To tune a single SQL statement, the QUICK_TUNE procedure accepts as its input a task_name and a SQL statement. The procedure creates a task and workload and executes this task. There is no difference in the results from using QUICK_TUNE. They are exactly the same as those from using EXECUTE_TASK, but this approach is easier to use when there is only a single SQL statement to be tuned. The syntax is as follows:

DBMS_ADVISOR.QUICK_TUNE (
   advisor_name           IN VARCHAR2,
   task_name              IN VARCHAR2,
   attr1                  IN CLOB,
   attr2                  IN VARCHAR2 := NULL,
   attr3                  IN NUMBER := NULL,
   task_or_template       IN VARCHAR2 := NULL);

The following example shows how to quick tune a single SQL statement:

VARIABLE task_name VARCHAR2(255);
VARIABLE sql_stmt VARCHAR2(4000);
EXECUTE :sql_stmt := 'SELECT COUNT(*) FROM customers 
                      WHERE cust_state_province =''CA''';
EXECUTE :task_name  := 'MY_QUICKTUNE_TASK';
EXECUTE DBMS_ADVISOR.QUICK_TUNE(DBMS_ADVISOR.SQLACCESS_ADVISOR,
              :task_name, :sql_stmt);

See Oracle Database PL/SQL Packages and Types Reference for more information regarding the QUICK_TUNE procedure and its parameters.