最新的Oracle Database認證 1Z0-117考試題庫

問題1

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. Create a height-balanced histogram for the CUST_ID column.

B. Use the ALL_ROWS hint in the query.

C. Gather statistics for the indexes on the SALES table.

D. Drop bitmap index and create balanced B*Tree index on the CUST_ID column.

正確答案: B

說明：（僅 VCESoft 成員可見）

問題2

Examine the exhibit.

Which two are true concerning the execution plan?

A. A full partition-wise join is used

B. A partial partition-wise join is used

C. The SALES table is composite partitioned

D. No partition-wise join is used

正確答案: A,C

說明：（僅 VCESoft 成員可見）

問題3

You are administering a database supporting an OLTP workload where the users perform frequent queries for fetching a new rows as possible, involving join operations on recently
inserted data. In addition at night, a few DSS queries are also performed. Examine the initialization parameters for the instance:

Which two options would you use for the optimizer?

A. Add the hint ALL_ROWS in the DOS queries.

B. Set the OPTIMIZER_MODE initialization parameter to FIRST_ROWS_n.

C. Set the OPTIMIZER_INDEX_CACHING initialization parameter to 0.

D. Add a hint INDEX_COMBINE in all DSS queries.

E. Set the OPTIMIZER_INDEX_COST_ADJ initialization parameter to 100.

正確答案: B,E

說明：（僅 VCESoft 成員可見）

問題4

Examine the utilization parameters for an instance:

You notice that despite having an index on the column used in the where clause, queries use full table scans with highly selective filters.
What are two possible reasons for the optimizer to use full table scans instead of index unique scans and index range scans?

A. The OPTIMIZER_INDEX_COST_ADJ initialization parameter is set to 100.

B. The blocks fetched by the query are greater than the value specified by the DB_FILE_MULTIBLOCK_READ_COUNT parameter.

C. The number of leaf blocks for the indexes is high.

D. The OPTIMIZER_MODE parameter is set to ALL_ROWS.

E. The clustering factor for the indexes is high.

正確答案: A,B

說明：（僅 VCESoft 成員可見）

問題5

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

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

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

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

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

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

正確答案: D,E

說明：（僅 VCESoft 成員可見）

問題6

Examine Exhibit1 to view the query and its AUTOTRACE output.

Which two statements are true about tracing?

A. Subsequent execution of this statement will use the displayed plan that is stored in v$SQL.

B. The execution plan generated can be viewed from v$SQLAREA.

C. The displayed plan may not necessarily be used by the optimizer.

D. The query will not fetch any rows; it will display only the execution plan and statistics.

E. The displayed plan will be stored in PLAN_TABLE.

正確答案: D,E

說明：（僅 VCESoft 成員可見）

問題7

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 uses an index join as an access path.

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

D. The optimizer performs bitmap conversion to row IDs of the combined maps.

E. The optimizer performs bitmap conversion from row IDs.

F. The optimizer performs bitmap conversion from the row IDs of the combined maps.

正確答案: B,C,F

說明：（僅 VCESoft 成員可見）

問題8

Which statement is true about an automatic SQL task?

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

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

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

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

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

正確答案: E

說明：（僅 VCESoft 成員可見）

問題9

You are working on a database that supports an OLTP workload. You see a large number of hard parses occurring and several almost identical SQL statements in the library cache that vary only in the literal values in the WHERE clause conditions.
Which two methods can you use to reduce hard parsing?

A. Set the CURSOR_SHARING parameter to SIMILAR.

B. Replace literals with bind variables and evolve a baseline for the statement.

C. Use the RESULT_CACHE hint in the queries.

D. Create baselines for the almost identical SQL statement by manually loading them from the cursor cache.

正確答案: A,B

說明：（僅 VCESoft 成員可見）

問題10

You are administering a database, where an application frequently executes identical SQL statements with the same syntax.
How will you optimize the query results without retrieving data blocks from the storage?

A. By ensuring that RESULT_CACHE_MODE parameter is set to MANUAL and using the RESULT_CACHE hint in the queries.

B. By setting the CURSOR_SHARING parameter to FORCE.

C. By creating a SQL plan baseline for the identical statements.

D. By using the CACHE hint to pin the queries in the library cache

E. By using the bind variables and setting the CURSOR_SHARING parameter to EXACT.

正確答案: A

說明：（僅 VCESoft 成員可見）

最新的Oracle Database 11g Release 2: SQL Tuning - 1Z0-117免費考試真題

最新更新

站內鏈接

聯系我們