1Z0-117試験無料問題集（125題）「Oracle Database 11g Release 2: SQL Tuning 認定」

出題：1

Auto DOP is enabled for your instance.
You execute the following statements:

Which three are true about the execution of the join?

A. Dictionary DOP is used to calculate statements DOP.

B. The hint operates at the level of each table accessed by the statement.

C. The EMPLOYEES table is accessed in parallel.

D. Hinted DOP is used to calculate statement DOP.

E. The DEPARTMENTS table is accessed in parallel.

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

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

出題：2

What are three common reasons for SQL statements to perform poorly?

A. Stale or missing optimizer statistics

B. Histograms not existing on columns with evenly distributed data

C. Full table scans for queries with highly selective filters

D. OPTIMIZER_MODE parameter set to ALL_ROWS for DSS workload

E. High index clustering factor

正解：A,C,E 解答を投票する

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

出題：3

In Your Database, The Cursor_Shareing Parameter is set to EXACT. In the Employees table, the data is significantly skewed in the DEPTNO column. The value 10 is found in 97% of rows.
Examine the following command and out put.

Which three statements are correct?

A. The instance collects statistics and based on the pattern of executions creates a histogram on the column containing the bind value.

B. The same execution plan will always be used irrespective of the bind variable value.

C. The value for the bind variable will considered by the optimizer to determine the execution plan.

D. Bind peeking will take place only for the first execution of the statement and subsequent execution will use the same plan.

E. The DEPTNO column will become bind aware once histogram statistics are collected.

正解：A,C,E 解答を投票する

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

出題：4

Examine the Exhibit 1 to view the structure of and indexes for EMPLOYEES and DEPARTMENTS tables.

Which three statements are true regarding the execution plan?

A. The optimizer chooses sort-merge join because sorting is required for the join equality predicate.

B. The optimizer pushes the equality predicate into the view to satisfy the join condition.

C. The view operator collects all rows from a query block before they can be processed but higher operations in the plan.

D. The in-line query in the select list is processed as a view and then joined.

E. The optimizer chooses sort-merge join as a join method because an equality predicate is used for joining the tables.

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

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

出題：5

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, 3, 1, 4, 6, 5

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

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

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

正解：B 解答を投票する

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

出題：6

Examine the exhibit to view the query and its execution plan?

What two statements are true?

A. The HASH GROUP BY operation is the consumer of the HASH operation.

B. The HASH operation is the consumer of the HASH GROUP BY operation.

C. The SALES table scan is a producer for the HASH JOIN operation.

D. The HASH GROUP BY operation is the consumer of the TABLE ACCESS FULL operation for the CUSTOMER table.

E. The HASH GROUP BY operation is consumer of the TABLE ACCESS FULL operation for the SALES table.

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

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

出題：7

Identify two situations in which full table scans will be faster than index range scans.

A. A query executing in parallel on a partitioned table with partitioned indexes.

B. A highly selective query on a table having high clustering factor for an index.

C. A query with a highly selective filter fetching less than 5 percent of the rows from a table.

D. A query fetching less number of blocks than value specified by DB_FILE_MULTIBLOCK_READ_COUNT.

E. A query on a table with sparsely populated table blocks.

正解：A,D 解答を投票する

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

出題：8

You enable auto degree of parallelism (DOP) for your database instance. Examine the following query:

Which two are true about the execution of statement?

A. Dictionary DOP is used to determine the statement DOP only if the estimated serial execution time exceeds PARALLEL_MIN_TIME_THRESHOLD.

B. Dictionary DOP for the objects accessed by the query is used to determine the statement DOP.

C. The statement will be queued if insufficient parallel execution slaves are available to satisfy the statements DOP.

D. The statements may execute serially.

E. The statement will be queued if the number of busy parallel execution servers exceeds PARALLEL_SERVERS_TARGET.

F. Auto DOP is used to determine the statement DOP only if the estimated serial execution time exceeds PARALLEL_MIN_TIME_THRESHOLD.

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

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

出題：9

In your database, the CURSOR_SHARING parameter is set to FORCE.
A user issues the following SQL statement:
Select * from SH.CUSTOMERS where REIGN='NORTH'
Which two statements are correct?

A. Bind peeking will not happen and subsequent executions of the statement with different literal values will use the same plan.

B. Adaptive cursor sharing happens only if there is a histogram in the REIGN column of the CUSTOMERS table.

C. Adaptive cursor sharing happens irrespective of whether there is a histogram in the REIGN column of the CUSTOMERS table.

D. The literal value 'NORTH' is replaced by a system-generated bind variable.

正解：A,D 解答を投票する

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

出題：10

View the 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 are frequently used in WHERE clause of the queries. These columns have few distinct values relative to the total number of rows in the table.
View exhibit 2 and examine one of the queries and its auto trace output.

What should you do to improve the performance of the query?

A. Create composite index involving the CUST_ID and PROD_ID columns.

B. Use the INDEX_COMBINE hint in the query.

C. Gather index statistics for the MYSALES_PRODID_IDX and MYSALES_CUSTID_IDX indexes.

D. Gather histograms statistics for the CUST_ID and PROD_ID columns.

正解：C 解答を投票する

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

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