Free Oracle 1Z0-117 Exam Dumps Questions & Answers

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.View the execution plan for the statement from V$SQL_PLAN.
• B.Use the DBMS_XPLAN.DISPLAY function to view the execution plan.
• C.Identify the SQL_ID for the statement and fetch the execution plan PLAN_TABLE.
• D.Execute the statement with different bind values and set AUTOTRACE enabled for session.
• E.Identify the SQL_ID for the statementsand use DBMS_XPLAN.DISPLAY_CURSOR for that SQL_ID to view the execution plan.

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 a hint INDEX_COMBINE in all DSS queries.
• B.Set the OPTIMIZER_MODE initialization parameter to FIRST_ROWS_n.
• C.Set the OPTIMIZER_INDEX_CACHING initialization parameter to 0.
• D.Add the hint ALL_ROWS in the DOS queries.
• E.Set the OPTIMIZER_INDEX_COST_ADJ initialization parameter to 100.

An application frequently executed similar types of queries that vary only in the usage of literals in the WHERE clause. You plan to use bind variable in place of literal values.
The CURSOR_SHARING parameter to set to EXACT.
Which two statements are true about the usage of bind variables?

• A.Bind peeking will take place and subsequent execution of queries can have different plans based on the cardinality of the bind value in the column.
• B.Bind peeking will happen and subsequent execution of queries will have the same parent cursor but different child cursors.
• C.The number of latch gets in the library cache will be reduced.
• D.Bind peeking will not happen and the optimizer will use the same plan for all bind values if no histograms exist on a column use in the WHERE clause.
• E.Bind peeking will take place and subsequent execution of queries can have different plans only when the histograms exists on a column used in WHERE clause.

Examine Exhibit1 to view the query and its AUTOTRACE output.

Which two statements are true about tracing?

• A.The displayed plan will be stored in PLAN_TABLE.
• B.Subsequent execution of this statement will use the displayed plan that is stored in v$SQL.
• C.The query will not fetch any rows; it will display only the execution plan and statistics.
• D.The displayed plan may not necessarily be used by the optimizer.
• E.The execution plan generated can be viewed from v$SQLAREA.

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.Highly selective filter on indexed columns
• B.Indexes not built on filter columns
• C.Too many sort operations performed by queries
• D.Too many similar type of queries getting executed with cursor sharing disabled
• E.Histograms statistics not available