1Z0-117試験無料問題集（125題）「Oracle Database 11g Release 2: SQL Tuning 認定」

出題：1

Which two statements are true about the trcsess utility?

A. It merges multiple trace files from a particular session into one single trace file.

B. It merges files pertaining to a user session scattered across different processes in a shared server configuration.

C. It produces multiple files only for DBA sessions, which can be consolidated into one formatted file using the tkprof utility.

D. It merges multiple trace files and produces a formatted output file.

E. It produces multiple files for a service, which can be consolidated into one formatted file using the tkprof utility.

正解：A,D 解答を投票する

解説: (GoShiken メンバーにのみ表示されます)

出題：2

Examine the initializing parameters:

An index exists on the column used in the WHERE of a query. You execute the query for the first time today and notice that the query is not using the index. The CUSTOMERS table has 55000 rows.
View the exhibit and examine the query and its execution plan.

What can be the two reasons for full table scan?

A. The value of the OPTIMIZER_INDEX_COST_ADJ parameter is set to a low value.

B. Average number of rows per block for the CUSTOMERS table is low.

C. The OPTIMIZER_MODE parameter is set to ALL_ROWS.

D. Histogram statistics for CUST_CITY_ID are missing.

E. The statistics for the CUSTOMERS table and the indexes stale.

F. The blocks fetched by the query are greater than the value specified by the DB_FILE_MULTIBLOCK_READ_COUNT parameter.

正解：D,E 解答を投票する

解説: (GoShiken メンバーにのみ表示されます)

出題：3

Examine Exhibit1 to view the query and its AUTOTRACE output.

Which two statements are true about tracing?

A. Subsequent execution of this statement will use the displayed plan that is stored in v$SQL.

B. The execution plan generated can be viewed from v$SQLAREA.

C. The displayed plan may not necessarily be used by the optimizer.

D. The displayed plan will be stored in PLAN_TABLE.

E. The query will not fetch any rows; it will display only the execution plan and statistics.

正解：D,E 解答を投票する

解説: (GoShiken メンバーにのみ表示されます)

出題：4

You are administering a database supporting an OLTP application. The application runs a series of extremely similar queries the MYSALES table where the value of CUST_ID changes.
Examine Exhibit1 to view the query and its execution plan.

Examine Exhibit 2 to view the structure and indexes for the MYSALES table. The MYSALES table has 4 million records.

Data in the CUST_ID column is highly skewed. Examine the parameters set for the instance:

Which action would you like to make the query use the best plan for the selectivity?

A. Collect histogram statistics for the CUST_ID column and use a bind variable instead of literal values.

B. Decrease the value of the OPTIMIZER_DYNAMIC_SAMPLING parameter to 0.

C. Drop the existing B* -tree index and re-create it as a bitmapped index on the CUST_ID column.

D. Us the /*+ INDEX(CUST_ID_IDX) */ hint in the query.

正解：A 解答を投票する

解説: (GoShiken メンバーにのみ表示されます)

出題：5

Which type of SQL statement would be selected for tuning by the automatic SQL framework?

A. Serial queries that are among the costliest in any or all of the four categories: the past week, any day in the past week, any hour in the past week, or single response, and have the potential for improvement

B. Serial and parallel queries that are among the costliest in any or all of the four categories: the past week, and day in the past week, any hour in the past week, or a single response, and that can benefit from access method changes.

C. Serial queries that have been tuned within the last 30days and have been SQL profiled by the SQL tuning Advisor.

D. Serial queries that top the AWR Top SQL in the past week only and whose poor performance can be traced to concurrency issues.

E. Serial and parallel queries that top the AWR Top SQL in the past week only and have been SQL profiled by the SQL Tuning Advisor.

正解：B 解答を投票する

解説: (GoShiken メンバーにのみ表示されます)

出題：6

A database supports three applications: CRM, ERP, and ACC. These applications connect to the database by using three different services: CRM_SRV for the CRM application, ERP_SRV for the ERP application, and ACC_SRV for the ACC application.
You enable tracing for the ACC_SRV service by issuing the following command:
SQL> EXECUTE DBMS for the ACC_SRV service by issuing the following command:
SQL> EXECUITIVE DBMS_MONITOR. SERV_MOD_ACT_TRACE_ENABLE (service_name => 'ACC_SRV', waits => TRUE, binds = > FALSE, instance_name = > 'inst1');
Which statement is true?

A. A single trace file is created for each session that uses the ACC_SRV service.

B. All trace information for the ACC_SRV service connected to inst1 is stored in multiple trace files, which can be consolidated by using the tkprof utility.

C. Only those SQL statements that are identified with the ACC_SRV service executed on the inst1 instance are recorded in trace files.

D. All trace information for the service connection to inst1 will be stored in a single trace file.

E. A trace file is not created because the module name is not specified.

正解：A 解答を投票する

解説: (GoShiken メンバーにのみ表示されます)

出題：7

Examine the query and its execution plan:

Which two statements are true regarding the execution plan?

A. The data is aggregated from the ORDERS table before joining to CUSTOMERS.

B. An outer join returns NULL for the ORDERS table columns along with the CUSTOMERS table rows when it does not find any corresponding rows in the ORDER table.

C. For every row of CUSTOMERS table, the row matching the join predicate from the ORDERS table are returned.

D. The NESTED LOOP OUTER join is performed because the OPTIMZER_MODE parameter is set to ALL_ROWS.

正解：B,D 解答を投票する

解説: (GoShiken メンバーにのみ表示されます)

出題：8

You are logged in as the HR user and you execute the following procedure:
SQL > exec DBMS_STATS.SET_TABLE_PREFS ('HR', 'EMPLOYEES', 'PUBLISH', 'FALSE');
SQL> exec DBMS_STATS.GATHER_TABLE_STATS ('HR', 'EMPLOYEES');
Which statement is true about the newly gathered statistics?

A. They are used by the optimizer for all sessions.

B. They are marked as pending and stored in the pending statistics table.

C. They are locked and cannot be overwritten.

D. They are temporary and purged when the session exits.

正解：B 解答を投票する

解説: (GoShiken メンバーにのみ表示されます)

出題：9

When would bind peeking be done for queries that vary only in values used in the WHERE clause?

A. When the column used in the WHERE clause has evenly distributed data and histogram exists on that column.

B. When the column used in the WHERE clause has non uniform distribution of data and histogram exists for the column.

C. When the column used in the WHERE clause has evenly distributed data and index exists on that column.

D. When the column used in the WHERE clause has non uniform distribution of data, uses a bind variable, and no histogram exists for the column.

正解：C 解答を投票する

出題：10

Which three statements are true the Automatic Tuning Optimizer (ATO)?

A. It identifies the syntactic, semantic, or design problems with structure of SQL statements leading to poor performance and suggests restricting the statements.

B. It identifies resource-intensive SQL statements, runs them through the SQL Tuning Advisor, and implements the recommendations automatically.

C. It picks up resource-intensive SQL statements from the ADDM and recommends the use of materialized views to improve query performance.

D. It identifies the objects with stale or missing statistics and gathers statistics automatically.

E. It investigates the effect of new or modified indexes on the access paths for a workload and recommends running that statistics through the SQL Access Advisor.

F. It recommends a SQL profile to help create a better execution plan.

正解：B,C,D 解答を投票する

解説: (GoShiken メンバーにのみ表示されます)

1Z0-117試験無料問題集「Oracle Database 11g Release 2: SQL Tuning 認定」