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

質問 1

Which statement is true about the usage of the STAR_TRANSFORMATION hint in a query?

（A）The optimizer always uses bitmap indexes on the primary key column for any dimension table to transform a query.

（B）The optimizer always uses a plan in which the transformation is used.

（C）The optimizer uses transformation only if the cost is less than a query executing without transformation.

（D）The optimizer always generates subqueries to transform a query.

正解：C 解答を投票する

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

質問 2

Examine the initialization parameters for a database an OLTP overload.

What is the effect of changing the value of the parameter?

（A）It influences the optimizer to use full table scans instead of index scans as the estimated cost of full table scan is reduced.

（B）It influenced the optimizer to use bitmap indexes as the estimated cost conversion from bimap is rowed us reduced.

（C）It influences the optimizer to always use the value of the parameter?

（D）It influences the optimizer to use indexes instead of full table scans as the estimated cost of the using index is reduced.

正解：D 解答を投票する

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

質問 3

One of your databases supports a mixed workload.
When monitoring SQL performance, you detect many direct paths reads full table scans.
What are the two possible causes?

（A）Too many similar type of queries getting executed with cursor sharing disabled

（B）Histograms statistics not available

（C）Highly selective filter on indexed columns

（D）Indexes not built on filter columns

（E）Too many sort operations performed by queries

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

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

質問 4

You recently gathered statistics for a table by using the following commands:

You noticed that the performance of queries has degraded after gathering statistics. You want to use the old statistics. The optimizer statistics retention period is default.
What must you do to use the old statistics?

（A）Restore statistics from statistics history up to the desired time.

（B）Delete all the statistics collected after the desired time.

（C）Use the flashback to bring back the statistics to the desired time.

（D）Set OPTIMIZER_USE_PENDING_STATISTICS to TRUE.

正解：A 解答を投票する

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

質問 5

You have enabled DML by issuing: ALTER session ENABLE PARALLEL DML;
The PARELLEL_DEGREE_POLICY initialization parameter is set to AUTO.
Which two options true about DML statements for which parallel execution is requested?

（A）Statements for which PDML is requested will be queued if the number of busy parallel execution servers is greater than PARELLEL_SERVERS_TARGET parameter.

（B）Statements for which PDML is requested will execute serially estimated time is less than the time specified by the PARALLEL_MIN_THRESHOLD parameter.

（C）Statement for which PDML is requested will be queued if the number of busy parallel execution servers is greater than PARELLEL_DEGREE_LIMIT parameter.

（D）Statements for which PDML is requested will be queued if the number of busy parallel execution servers greater than PARALLEL_MIN_SERVERS parameter.

（E）Statements for which PDML is requested will always execute in parallel if estimated execution in parallel if estimated execution time is greater than the time specified by the PARELLEL_MIN_TIME_THRESHOLD parameter.

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

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

質問 6

Examine the parallelism parameter for your instance:

parallel_servers_target
Now examine the resource plan containing parallel statement directives:
Consumer Group resource plan containing parallel statement directives:

Which two are true about parallel statement queuing when this plan is active?

（A）A single ETL_GROUP session can consume up to eight parallel execution servers.

（B）A single ETL_GROUP session can consume up to 32 parallel execution servers.

（C）Urgent_group sessions collectively can consume up to 64 parallel execution servers before queuing starts for this consumer group.

（D）A single OTHER_GROUPS session will execute serially once it is queued for six minutes.

（E）A single OTHER_GROUPS session will execute in parallel once it is queued for six minutes.

（F）ETL_GROUP sessions can collectively consume up to 64 parallel execution servers before the queuing starts for this consumer.

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

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

質問 7

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

質問 8

Which are the two prerequisites for enabling star transformation on queries?

（A）A bitmap index should be built on each of the primary key columns of the fact table(s).

（B）A bitmap index must exist on all the columns that are used in the filter predicates of the query.

（C）The STAR_TRANSFORMATION_ENABLED parameter should be set to TRUE or TEMP_DISABLE.

（D）A B-tree index should be built on each of the foreign key columns of the fact table(s),

（E）A bitmap index should be built on each of the foreign key columns of the fact table(s).

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

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

質問 9

Examine the following query and execution plan:

Which query transformation technique is used in this scenario?

（A）Join predicate push-down

（B）Join conversion

（C）Subquery unnesting

（D）Subquery factoring

正解：A 解答を投票する

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

質問 10

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

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

弊社を連絡する

関連リンク

トップ試験