최신 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 generates subqueries to transform a query.

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

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

D. The optimizer always uses a plan in which the transformation is used.

정답: C

설명: (DumpTOP 회원만 볼 수 있음)

문제2

Which statement is true about an automatic SQL task?

A. It will automatically implement SQL profiles if a three-fold benefit can be achieved and automatic profile implementation is enabled.

B. It will tune all the top SQL statements from AWR irrespective of the time it takes to complete the task in a maintenance window.

C. It will attempt to tune the currently running SQL statements that are highly resource intensive.

D. It will automatically implement new SQL profiles for the statements that have existing SQL profiles.

E. It will attempt to tune all-long-running queries that have existing SQL profiles.

정답: A

설명: (DumpTOP 회원만 볼 수 있음)

문제3

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 by using different services and tracing is enabled for the services. You want to view the detailed tracing setting for particular service.
What would you use to view the tracing information?

A. DBA_ENABLED_TRACES view

B. Trcsess and tkprof

C. DBMS_MONITOR package

D. DBMS_SERVICE package

정답: A

설명: (DumpTOP 회원만 볼 수 있음)

문제4

You identified some DSS queries that perform expensive join and aggregation operations.
The queries access historical data from noncurrent partition of the fact tables.
What three actions could you perform to improve the response time of the queries without modifying the SQL statements?

A. Create an STS for the statements, run SQL Access Advisor for the STS, and implement any generated recommendations for materialized views.

B. Set QUERY_REWRITE_INTEGRITY to ENFORCED at the instance level.

C. Set the QUERY_REWRITE_ENABLED to TRUE at the session level.

D. Set QUERY_REWRITE_ENABLED to TRUE at the instance level.

E. Create an STS for the statements, run SQL Tuning Advisor for the STS, and implement any generated recommendations for materialized views.

정답: A,D,E

설명: (DumpTOP 회원만 볼 수 있음)

문제5

Exhibit

Examine the following SQL statement:

Examine the exhibit to view the execution plan. Which statement is true about the execution plan?

A. The EXPLAIN PLAN generates the execution plan and stores it in c$SQL_PLAN after executing the query. Subsequent executions will use the same plan.

B. The row with the ID 3 is the first step executed in the execution plan.

C. The row with the ID 0 is the first step executed in the execution plan.

D. The EXPLAIN PLAN generates the execution plan and stores it in PLAN_TABLE without executing the query. Subsequent executions will always use the same plan.

E. The rows with the ID 3 and 4 are executed simultaneously.

정답: E

설명: (DumpTOP 회원만 볼 수 있음)

문제6

You execute the following query for the first time:

Examine the SQL statement processing steps:
1.The value of the variable SAL is obtained to run the query.
2.The syntax of the query is checked
3.A parse tree for the query is generated
4.Semantics for the query are checked
5.The required rows are fetched
6.The SQL is executed to produce the required result.
Which is the correct order of execution of the above query?

A. 2, 4, 1, 3, 6, 5

B. 1, 4, 2, 3, 6, 5

C. 1, 2 3, 4, 5, 6

D. 2, 3, 1, 4, 6, 5

정답: A

설명: (DumpTOP 회원만 볼 수 있음)

문제7

You are administering a database that supports an OLTP application. To set statistics preferences, you issued the following command:
SQL > DBMS_STATS.SET_GLOBAL_PREFS ('ESTIMATE_PERCENT', '9');
What will be the effect of executing this procedure?

A. It will influence dynamic sampling for a query to estimate the statistics based on ESTIMATE_PERCENT.

B. The automatic statistics gathering job running in the maintenance window will use global preferences unless table preferences for the same table exist.

C. It will influence the gathering of statistics for a table based on the value specified for ESTIMATE_PERCENT provided on table preferences for the same table exist.

D. New objects created will use global preference even if table preferences are specified.

정답: B

설명: (DumpTOP 회원만 볼 수 있음)

문제8

---
Your database supports a workload consisting of three categories of SQL statements: Statements that should execute in less than one minute Statement that may execute for up to 15 minutes Statements that may be executed for more than 15 minutes You set PARALLEL_DEGREE_POLICY to Auto.
You plan to prioritize queued statements by using the Database Resource manager.
Which two are true about parallelism prioritization by a consumer group?

A. Queuing is done for a consumer group exceeding its percentage, even if the number of busy PX servers in the instance has not reached PARALLEL_SERVERS_TARGET.

B. Having separate queues for consumer groups requires the use of management attributes (MGMT_P1, MGMT_P2 etc. . . )

C. Separate queue timeout using PARALLEL_QUEUE_TIMEOUT require the use of management attributes (MGMT_P1, MGMT_P2 etc . . . groups)

D. PARALLEL_TARGET_PERCENTAGE is used to prioritize a consumer group's use of the overall PARALLEL_SERVER_TARGET.

E. PARALLEL_TARGET_PERCENTAGE us used to prioritize a consumer group's use of the overall SPAN CLASS = 'OracleCode'> PARALLEL_MAX_SERVERS.

정답: B,D

설명: (DumpTOP 회원만 볼 수 있음)

문제9

Which three tasks are performed by the parallel execution coordinator process?

A. Managing the data flow between the producers and consumers during inter-operation parallelism.

B. Determining the desired number of parallel execution processes

C. Determining the parallel execution method for each operation in the execution plan.

D. Managing the data flow between the producers and consumers during intra-operation parallelism.

E. Any serial processing that is part of the execution plan.

F. Allocating parallel execution processes from the parallel execution server pool.

정답: A,C,F

설명: (DumpTOP 회원만 볼 수 있음)

문제10

View Exhibit1 and examine the structure and indexes for the MYSALES table.

The application uses the MYSALES table to insert sales record. But this table is also extensively used for generating sales reports. The PROD_ID and CUST_ID columns are frequently used in the WHERE clause of the queries. These columns have few distinct values relative to the total number of rows in the table. The MYSALES table has 4.5 million rows.
View exhibit 2 and examine one of the queries and its autotrace output.

Which two methods can examine one of the queries and its autotrace output?

A. Rebuild the index to rearrange the index blocks to have more rows per block by decreasing the value for PCTFRE attribute.

B. Collect histogram statistics for the CUST_ID and PROD_ID columns.

C. Create a composite index involving the CUST_ID and PROD_ID columns.

D. Use the INDEX_COMBINE hint in the query.

E. Drop the current standard balanced B* Tree indexes on the CUST_ID and PROD_ID columns and re-create as bitmapped indexes.

정답: C,D

설명: (DumpTOP 회원만 볼 수 있음)

최신 1Z0-117 무료덤프 - Oracle Database 11g Release 2: SQL Tuning

우리와 연락하기

유용한 링크

최신 업데이트