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

質問 1

Which two tasks would you perform to achieve this?

（A）Set the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES initialization parameter to TRUE.

（B）Use the DBMS_SPM.ALTER_SQL_PLAN_BASELINE function to fix the plans.

（C）Use the DBMS_SPM.ALTER_SQL_BASELINE function to accept new plans.

（D）Set the OPTIMIZER_USE_SQL_PLAN_BASELINES initialization parameter to TRUE.

（E）Use the DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE function to fix the new plans.

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

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

質問 2

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）For every row of CUSTOMERS table, the row matching the join predicate from the ORDERS table are returned.

（C）The NESTED LOOP OUTER join is performed because the OPTIMZER_MODE parameter is set to ALL_ROWS.

（D）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、D 解答を投票する

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

質問 3

Examine the Exhibit to view the structure of an indexes for the SALES table.

The SALES table has 4594215 rows. The CUST_ID column has 2079 distinct values.
What would you do to influence the optimizer for better selectivity?

（A）Gather statistics for the indexes on the SALES table.

（B）Use the ALL_ROWS hint in the query.

（C）Drop bitmap index and create balanced B*Tree index on the CUST_ID column.

（D）Create a height-balanced histogram for the CUST_ID column.

正解：B 解答を投票する

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

質問 4

View the code sequence:

Which three statements are true about the execution of this query?

（A）The optimizer uses a B* tree access path for the EMPLOYEES table.

（B）The optimizer performs bitmap conversion from row IDs.

（C）The optimizer uses a bitmap access path for the EMPLOYEES table.

（D）The optimizer performs bitmap conversion from the row IDs of the combined maps.

（E）The optimizer performs bitmap conversion to row IDs of the combined maps.

（F）The optimizer uses an index join as an access path.

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

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

質問 5

View the exhibit and examine the findings provided by the SQL Tuning Advisor for SELECT Statement.

A SWL plan baseline already exists for the execution plan.
What two methods can you use to ensure that an alternate plan becomes an accepted plan?

（A）Use the DBMS_SPM.LOAD_PLANS_FROM_SQLSET function.

（B）Use the DBMS_SQLTUNE.CREATE_SQL_PLAN_BASELINE function and run the DBMS_STATS to manually refresh stale statistics.

（C）Use the DBMS_SQLTUNE.CREATE_SQL_PLAN_BASELINE function.

（D）Use the DBMS_SPM.ALTER_SQL_PLAN_BASELINE function.

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

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

質問 6

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 メンバーにのみ表示されます)

質問 7

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

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

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

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

（D）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

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

正解：E 解答を投票する

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

質問 8

Examine the parallelism parameters for your instance: Examine the Exhibit to view the query and its explain plan output.

All sessions use default parallelism settings.
What two steps could you take to make the query execute in parallel?

（A）Increase the value of PARALLEL_MIN_SERVERS.

（B）Increase the value of PARALLEL_MAX_SERVERS.

（C）Decrease the value of PARALLEL_MIN_PERCENT.

（D）Add a parallel hint.

（E）Decrease the value of PARALLEL_MIN_TIMETHRESHOLD.

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

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

質問 9

Tracing has been enabled for the HR user. You execute the following command to check the contents of the orcl_25052.trc trace file, which was generated during tracing:

Which two statements are correct about the execution of the command?

（A）TKPROF use TEMP_PLAN_TABLE in the HR schema as a temporary plan table.

（B）Execution plans for SQL statements are stored in TEMP_PLAN_TABLE and can be queried by the user.

（C）SQL statements in the output files are stored in the order of elapsed time.

（D）Recursive SQL statements are included in the output file.

（E）SCRIPT.SQL stores the statistics for all traced SWL statements.

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

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

質問 10

Examine the query and its execution plan:

Which statement is true regarding the execution plan?

（A）The query first fetches rows from the ORDERS table that satisfy the conditions, and then the join returns NULL form CUSTOMER_ID column when it does not find any corresponding rows in the CUSTOMERS table.

（B）The query first joins rows from the CUSTOMERS and ORDERS tables and returns NULL for the ORDERS table columns when it does not find any corresponding rows in the ORDERS table, and then fetches the rows that satisfy the conditions from the result set.

（C）This query first fetches rows from the CUSTOMERS table that satisfy the conditions, and then the join return NULL from the CUSTOMER_ID column when it does not find any corresponding rows in the ORDERS table.

（D）The query fetches rows from CUSTOMERS and ORDERS table simultaneously, and filters the rows that satisfy the conditions from the resultset.

正解：C 解答を投票する

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

弊社を連絡する

関連リンク

トップ試験