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

質問 1

You need to migrate database from oracle Database 10g to 11g. You want the SQL workload to start the 10g plans in the 11g database instance and evolve better plans.
Examine the following steps:
1.Capture the pre-Oracle Database 11g plans in a SQL Tuning Set (STS)
2.Export the STS from the 10g system.
3.Import the STS into Oracle Database 11g.
4.Set the OPTIMIZER_FEATURES_ENABLE parameter to 10.2.0.
5.Run SQL Performance Analyzer for the STS.
6.Set the OPTIMIZER_FEATURES_ENABLE parameter to 11.2.0.
7.Rerun the SQL Performance Analyzer for the STS.
8.Set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINE to TRUE.
9.Use DBMS_SPM.EVOLVE_SQL_BASELINE function to evolve the plans.
10.
Set the OPTIMIZER_USE_SQL_PLAN_BASELINE to TRUE.
Identify the required steps in the correct order.

（A）1, 2, 3, 6, 9, 5

（B）1, 2, 3, 5, 9, 10

（C）4, 8, 10

（D）1, 2, 3, 4, 8, 10

（E）1, 2, 3, 4, 5, 6, 7,

正解：D 解答を投票する

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

質問 2

Examine the Exhibit1 to view the structure of an indexes for the EMPLOYEES table.

Examine the query:
SQL> SELECT * FROM employees WHERE employees_id IN (7876, 7900, 7902);
EMPLOYEE_ID is a primary key in the EMPLOYEES table that has 50000 rows.
Which statement is true regarding the execution of the query?

（A）The query uses a fast full index scan on the EMP_EMP_ID_PK index fetch the rows.

（B）The query uses an index skip scan on the EMP_EMP_ID_PK index to fetch the rows.

（C）The query uses the INLIST ITERATOR operator to iterate over the enumerated value list, and values are evaluated using an index range scan on the EMP_EMP_ID_PK index.

（D）The query uses the INLIST ITERATOR operator to iterate over the enumerated value list, and values are evaluated using an index unique scan on the EMP_EMP_ID_PK index.

（E）The query uses the INLIST ITERATOR operator to iterate over the enumerated value list, and values are evaluated using a fast full index scan on the EMP_EMP_ID_PK index.

正解：C 解答を投票する

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

質問 3

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）Drop the existing B* -tree index and re-create it as a bitmapped index on the CUST_ID column.

（B）Us the /*+ INDEX(CUST_ID_IDX) */ hint in the query.

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

（D）Decrease the value of the OPTIMIZER_DYNAMIC_SAMPLING parameter to 0.

正解：C 解答を投票する

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

質問 4

Examine the parallelism parameters for you instance. Now examine the DSS_PLAN with parallel statement directives:

Which two are true about the DSS_PLAN resource manager plan?

（A）URGENT_GROUP sessions will not be queued if 64 parallel execution servers are busy because their PARALLEL_TARGET_PERCENTAGE is not specified.

（B）An ETL_GRP sessions will be switched to URGENT_GROUPS if the session requests more than eight parallel executions servers.

（C）URGENT_GROUPS sessions will always be dequeued before sessions from other groups.

（D）OTHER_GROUPS sessions are queued for maximum of six minutes.

（E）ETL_GROUP sessions can collectively consume 64 parallel execution servers before queuing starts for this consumer group.

正解：C、D 解答を投票する

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

質問 5

You created a SQL Tuning Set (STS) containing resource-intensive SQL statements. You plan to run the SQL Tuning Advisor.
Which two types of recommendations can be provided by the SQL Tuning Advisor?

（A）Semantic restructuring for each SQL statement

（B）Creating a partition table to benefit from partition pruning for each statement

（C）Creating a materialized view to benefit from query rewrite for the entire workload

（D）Gathering missing or stale statistics at the schema level for the entire workload

（E）Gathering missing or stale statistics for objects used by the statements.

正解：A、E 解答を投票する

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

質問 6

You executed the following statement:

Which three statements are true about EXPLAIN PLAN?

（A）The execution plan is saved in PLAN_TABLE without executing the query.

（B）The execution plan generated can be fetched from the library cache by using the DBMS_XPLAIN.DISPLAY function.

（C）The execution plan is saved in DBA_HIST_SQL_PLAN without executing the query.

（D）The execution plan generated can be viewed using the DBMS_XPLAIN.DISPLAY function.

（E）The execution plan for the query is generated and displayed immediately as the output.

（F）The execution plan generated may not necessarily be the execution plan used during query execution.

正解：A、D、F 解答を投票する

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

質問 7

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）All trace information for the service connection to inst1 will be stored in a single trace file.

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

（C）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.

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

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

正解：D 解答を投票する

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

質問 8

View the exhibit and examine the plans in the SQL baseline for a given statement. Which interpretation is correct?

（A）A new plan cannot be evolved because SYS_SQL_bbedc41f554c408 is accepted.

（B）A new plan must be evolved using the DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE function before it can be used.

（C）Plan SYS_SQL_bbedc741a57b5fc2 can be used by the optimizer if the cost of the query is less than plan SYS_SQL_PLAN_bbedc741f554c408.

（D）Plan SYS_SQL_PLAN_bbedc741f554c408 will not be used until it is fixed by using the DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE function.

（E）Plan SYS_SQL_PLAN_bbdc741f554c408 will always be used by the optimizer for the query.

正解：B 解答を投票する

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

質問 9

See the table below:

All parallel execution servers are available and sessions use default settings for parallelism.
Which three are true about parallel execution in your instance?

（A）Parallel execution occurs for all DML statements.

（B）Parallel execution occurs for those statements that access tables with no dictionary DOP defined.

（C）Parallel execution occurs when estimated serial execution time exceeds the minimum time threshold.

（D）Parallel execution occurs for those statements that access tables with dictionary DOP defined.

（E）Parallel execution occurs for all DDL statements.

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

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

質問 10

Examine the Exhibit. Given two sets of parallel execution processes, SS1 and SS2, which is true?

（A）Each process SS1 reads some of the rows from the CUSTOMERS table and sends all the rows it reads to each process in SS2.

（B）Each process in SS1 reads all the rows from the CUSTOMERS table and distributes the rows evenly among the processes in SS2.

（C）Each process in SS1 reads some of the rows from the SALES table and distributes the rows evenly among the processes in SS2.

（D）Each process in SS1 reads all the rows from the SALES table and distributes the rows evenly among the processes in SS2.

（E）Each process in SS1 reads some of the rows from the SALES table and sends all the rows it reads to each process in SS2.

（F）Each process in the SS1 reads some of the rows from the CUSTOMERS table and distributes the rows evenly among the processes in SS2.

正解：D 解答を投票する

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

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

弊社を連絡する

関連リンク

トップ試験