1Z0-117 Online Practice Questions and Answers

Correct Answer: E

The Automatic Tuning Optimizer is meant to be used for complex and high-load SQL statements that have non-trivial impact on the entire system. The Automatic Database Diagnostic Monitor (ADDM) proactively identifies high-load SQL statements which are good candidates for SQL tuning.

Note:

* When SQL statements are executed by the Oracle database, the query optimizer is used to generate the execution plans of the SQL statements. The query

optimizer operates in two modes:

a normal mode and a tuning mode.

In normal mode, the optimizer compiles the SQL and generates an execution plan. The normal mode of the optimizer generates a reasonable execution plan for the vast majority of SQL statements. Under normal mode, the optimizer operates with very strict time constraints, usually a fraction of a second, during which it must find a good execution plan.

In tuning mode, the optimizer performs additional analysis to check whether the execution plan produced under normal mode can be improved further. The output of the query optimizer is not an execution plan, but a series of actions, along with their rationale and expected benefit for producing a significantly superior plan. When running in the tuning mode, the optimizer is referred to as the Automatic Tuning Optimizer.

Reference: Oracle Database Performance Tuning Guide, Automatic SQL Tuning

Correct Answer: BD

Note:

* The direct path read Oracle metric occurs during Direct Path operations when the data is asynchronously read from the database files into the PGA instead of

into the SGA data buffer.

Direct reads occur under these conditions:

-

When reading from the TEMP tablespace (a sort operation)

-

When reading a parallel full-table scan (parallel query factotum (slave) processes)

-Reading a LOB segment

* The optimizer uses a full table scan in any of the following cases:

-Lack of Index

-Large Amount of Data

-Small Table

-High Degree of Parallelism

Correct Answer: D

In previous database versions, new optimizer statistics were automatically published when they were gathered. In 11g this is still the default action, but you now have the option of keeping the newly gathered statistics in a pending state until you choose to publish them.

The DBMS_STATS.GET_PREFS function allows you to check the 'PUBLISH' attribute to see if statistics are automatically published. The default value of TRUE means they are automatically published, while FALSE indicates they are held in a pending state.

Reference: Statistics Collection Enhancements in Oracle Database 11g Release 1, Pending Statistics

Correct Answer: C

SERV_MOD_ACT_TRACE_ENABLE

serv_mod_act_trace_enable and serv_mod_act_trace_disable, which enables and disables trace for given service_name, module and action.

For example for a given service name you can trace all session started from SQL*Plus. Module and action in your own created application can be set using

dbms_application_info set_module and set_action procedures.

serv_mod_act_trace_enable fills sys table wri$_tracing_enabled and view dba_enabled_traces on top of this table as follows:

SQL> exec dbms_monitor.serv_mod_act_trace_enable(service_name=>'orcl', module_name=>'SQL*Plus')

PL/SQL procedure successfully completed.

SQL> select * from sys.wri$_tracing_enabled;

TRACE_TYPE PRIMARY_ID QUALIFIER_ID1 QUALIFIER_ID2 INSTANCE_NAME

FLAGS

---------- ---------- ------------- ------------- ------------- ----- 4 orcl SQL*Plus 8

SQL> select * from dba_enabled_traces;

TRACE_TYPE PRIMARY_ID QUALIFIER_ID1 QUALIFIER_ID2 WAITS BINDS

INSTANCE_NAME

-------------- ---------- ------------- ------------- ----- ----- ------------- SERVICE_MODULE orcl SQL*Plus TRUE FALSE

Correct Answer: ABDE

Direct-path INSERT is not supported for an index-organized table (IOT) if it is not partitioned, if it has a mapping table, or if it is reference by a materialized view.

Correct Answer: AB

The trcsess utility consolidates trace output from selected trace files based on several criteria:

Session ID Client ID Service name Action name Module name After trcsess merges the trace information into a single output file, the output file could be processed by TKPROF.

Note:

*

trcsess is useful for consolidating the tracing of a particular session for performance or debugging purposes. Tracing a specific session is usually not a problem in the dedicated server model as a single dedicated process serves a session during its lifetime. You can see the trace information for the session from the trace file belonging to the dedicated server serving it. However, in a shared server configuration a user session is serviced by different processes from time to time. The trace pertaining to the user session is scattered across different trace files belonging to different processes. This makes it difficult to get a complete picture of the life cycle of a session.

Reference: Oracle Database Performance Tuning Guide 11g , Using the trcsess Utility

*

Now there is a new tool, a command line utility called trcsess to help read the trace files. The trcsess command-line utility consolidates trace information from selected trace files, based on specified criteria. The criteria include session id, client id, service name, action name and module name.

Correct Answer: BC

Incorrect:

A, D, E: When PARALLEL_MIN_TIME_THRESHOLD is set to AUTO the PARALLEL_MIN_TIME_THRESHOLD is set to 30, not to 10. See note below.

Note:

* parallel_min_time_threshold

PARALLEL_MIN_TIME_THRESHOLD specifies the minimum execution time a statement should have before the statement is considered for automatic degree of parallelism. By default, this is set to 30 seconds. Automatic degree of parallelism is only enabled if PARALLEL_DEGREE_POLICY is set to AUTO or LIMITED.

Correct Answer: BD

B: An outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition.

Note:

*

All_rows attempts to optimize the query to get the very last row as fast as possible. This makes sense in a stored procedure for example where the client does

not regain control until the stored procedure completes. You don't care if you have to wait to get the first row if the last row gets back to you twice as fast. In a

client server/interactive application you may well care about that.

*

The optimizer uses nested loop joins to process an outer join in the following circumstances:

/ It is possible to drive from the outer table to inner table.

/ Data volume is low enough to make the nested loop method efficient.

*

First_rows attempts to optimize the query to get the very first row back to the client as fast as possible. This is good for an interactive client server environment

where the client runs a query and shows the user the first 10 rows or so and waits for them to page down to get more.

Correct Answer: B

How the CBO Evaluates IN-List Iterators

The IN-list iterator is used when a query contains an IN clause with values. The execution plan is identical to what would result for a statement with an equality clause instead of IN except for one additional step. That extra step occurs when the IN-list iterator feeds the equality clause with unique values from the IN-list.

Both of the statements in Example 2-1 and Example 2-1 are equivalent and produce the same plan.

Example 2-1 IN-List Iterators Initial Statement

SELECT header_id, line_id, revenue_amount FROM so_lines_all WHERE header_id IN (1011,1012,1013);

SELECT header_id, line_id, revenue_amount FROM so_lines_all WHERE header_id = 1011 OR header_id = 1012 OR header_id = 1013;

Plan

SELECT STATEMENT INLIST ITERATOR TABLE ACCESS BY INDEX ROWID SO_LINES_ALL INDEX RANGE SCAN SO_LINES_N1

Reference: Database Performance Tuning Guide and Reference

Correct Answer: BE

As per exhibit:

B, not A, Not C: First is line 5 executed, followed by line 4, followed by line 3.

Step 2 is line 4.

E: The Department_ID column has lower cardinality compared to the JOB_ID column, so it is better suited for a bitmapped index.

Note:

*

Oracle bitmap indexes are very different from standard b-tree indexes. In bitmap structures, a two-dimensional array is created with one column for every row in the table being indexed. Each column represents a distinct value within the bitmapped index. This two-dimensional array represents each value within the index multiplied by the number of rows in the table.

At row retrieval time, Oracle decompresses the bitmap into the RAM data buffers so it can be rapidly scanned for matching values. These matching values are delivered to Oracle in the form of a Row-ID list, and these Row-ID values may directly access the required information.

*

The real benefit of bitmapped indexing occurs when one table includes multiple bitmapped indexes. Each individual column may have low cardinality. The creation of multiple bitmapped indexes provides a very powerful method for rapidly answering difficult SQL queries.

*

Oracle bitmap indexes are very different from standard b-tree indexes. In bitmap structures, a two-dimensional array is created with one column for every row in the table being indexed. Each column represents a distinct value within the bitmapped index. This two-dimensional array represents each value within the index multiplied by the number of rows in the table.

At row retrieval time, Oracle decompresses the bitmap into the RAM data buffers so it can be rapidly scanned for matching values. These matching values are delivered to Oracle in the form of a Row-ID list, and these Row-ID values may directly access the required information.