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

質問 1

An application user complains about statement execution taking longer than usual. You find that the query uses a bind variable in the WHERE clause as follows:

You want to view the execution plan of the query that takes into account the value in the bind variable PCAT.
Which two methods can you use to view the required execution plan?

（A）Use the DBMS_XPLAN.DISPLAY function to view the execution plan.

（B）View the execution plan for the statement from V$SQL_PLAN.

（C）Execute the statement with different bind values and set AUTOTRACE enabled for session.

（D）Identify the SQL_ID for the statementsand use DBMS_XPLAN.DISPLAY_CURSOR for that SQL_ID to view the execution plan.

（E）Identify the SQL_ID for the statement and fetch the execution plan PLAN_TABLE.

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

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

質問 2

Which two types of column filtering may benefit from partition pruning?

（A）Greater than operators on hash-partitioned tables

（B）Operators on range-partitioned tables

（C）Equally operates on range-partitioned tables.

（D）In-list operators on system-partitioned tables

（E）Equality operators on system-partitioned tables

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

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

質問 3

Which two are the fastest methods for fetching a single row from a table based on an equality predicate?

（A）Index unique scan on an created for a column with unique key

（B）Fast full index scan on an index created for a column with unique key

（C）Row fetch from a table using rowid

（D）Row fetch from a single table hash cluster

（E）Index range scan on an index created from a column with primary key

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

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

質問 4

A database instance is configured in the shared server mode and it supports multiple applications running on a middle tier. These applications connect to the database using different services. You enabled the statistics gathering for the service by using the following command: SQL > EXECUTE DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE ('APPS1', NULL, NULL);
Which two statements are true regarding statistics gathered for APPS1 service?

（A）The statistics are gathered for all the modules using the service only when DBMS_APPLICATION_INFO.SET_MODULE is executed to register with the service.

（B）The statistics are gathered for all the modules and actions within the service.

（C）The statistics are aggregated and stored in the V$SERV_MOD_ACT_STATS view.

（D）Statistics gathering is enabled only for the subsequent sessions using the service.

（E）The statistics are collected at the session level for all sessions connected using the service.

（F）The statistics are gathered for all the applications using the service only when DBMS_APPLICATION_INFO.SET_ACTION is executed to register with the service.

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

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

質問 5

Which three statements are true about histograms?

（A）They capture the distribution of different values in an index for better selectivity estimates.

（B）They help the optimizer in deciding whether to use an index or a full table scan.

（C）They help the optimizer to determine the fastest table join order.

（D）They provide improved selectivity estimates in the presence of data skew, resulting in execution plans with uniform distribution.

（E）They provide metadata about distribution of and occurrences of values in a table column.

（F）They can be used only with indexed columns.

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

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

質問 6

You plan to bulk load data INSERT INTO . . . SELECT FROM statements.
Which two situations benefit from parallel INSERT operations on tables that have no materialized views defined on them?

（A）Direct path insert of 10 rows into a partitioned, index-organized table containing 10 rows and a bitmapped secondary index

（B）Direct path insert of 10 rows into a partitioned, index-organized table containing one million rows and conventional B* tree secondary index.

（C）Direct path insert of a million rows into a partitioned, index-organized table containing 10 rows and a bitmapped secondary index.

（D）Conventional path insert of 10 rows into a nonpartitioned, heap-organized table one million rows and a bitmapped index.

（E）Direct path insert of a million rows into a partitioned, index-organized table containing one million rows and a conventional B*tree secondary index.

（F）Conventional path insert of a million rows into a nonpartitioned, heap-organized containing 10 rows and having a conventional B* tree index.

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

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

質問 7

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

質問 8

Examine the exhibit.

Which two are true concerning the execution plan?

（A）No partition-wise join is used

（B）The SALES table is composite partitioned

（C）A full partition-wise join is used

（D）A partial partition-wise join is used

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

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

質問 9

Exhibit A table has three distinct values in its ID column. In the ID column, values 10 and 20 have more than 20000 rows each and value 30 has only five rows. The statistics for the schema have been updated recently.

The CURSOR_SHARING parameter is set to EXACT.
The query was executed recently and the cursor for the query is bind aware. Examine the exhibits to view the commands executed.
You plan to execute the same query with a value of 30 for the bind variable V_ID.
Which statement is true in this scenario?

（A）The same execution plan will always be used irrespective of the value in the bind variable.

（B）A new execution plan will be generated depending on the access pattern and the bind value.

（C）Adaptive cursor sharing will happen only if you use the literal values instead of bind variables in the query.

（D）Adaptive cursor sharing will ensure that a new cursor is generated for each distinct value in the bind variable.

正解：D 解答を投票する

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

質問 10

While tuning a SQL statement, the SQL Tuning Advisor finds an existing SQL profile for a statement that has stale statistics. Automatic optimizer statistics is enabled for the database.
What does the optimizer do in this situation?

（A）Makes the statistics information available to GATHER_DATABASE_STATS_JOB_PROC

（B）Writes a warning message in the alert log file

（C）Updates the existing SQL profiles for which the statistics are stale.

（D）Starts the statistics collection process by running GATHER_STATS_JOB

正解：A 解答を投票する

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

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

弊社を連絡する

関連リンク

トップ試験