1Z0-117 Online Practice Questions and Answers

Correct Answer: CF

The output of the SQL Tuning Advisor is in the form of an advice or recommendations, along with a rationale for each recommendation and its expected benefit. The recommendation relates to collection of statistics on objects , creation of new indexes (F), restructuring of the SQL statement (C), or creation of a SQL profile. You can choose to accept the recommendation to complete the tuning of the SQL statements.

Reference: Oracle Database Performance Tuning Guide 11g , SQL Tuning Advisor

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: BD

D: V$SQL_PLAN contains the execution plan information for each child cursor loaded in the library cache.

B: The DBMS_XPLAN package supplies five table functions:

DISPLAY_SQL_PLAN_BASELINE - to display one or more execution plans for the SQL statement identified by SQL handle

DISPLAY - to format and display the contents of a plan table.

DISPLAY_AWR - to format and display the contents of the execution plan of a stored SQL statement in the AWR.

DISPLAY_CURSOR - to format and display the contents of the execution plan of any loaded cursor.

DISPLAY_SQLSET - to format and display the contents of the execution plan of statements stored in a SQL tuning set.

Correct Answer: AE

A: The ALL_ROWS hint instructs the optimizer to optimize a statement block with a goal of best throughput, which is minimum total resource consumption.

E: optimizer_mode=all_rows - This optimizer mode favors full-table scans (especially parallel full-table-scans) in cases where the server resources will be minimized. The all_rows mode is generally used during batch-oriented processing and for data warehouses where the goal is to minimize server resource consumption.

Correct Answer: CD

C: To adopt an alternative plan regardless of whether SQL Tuning Advisor recommends it, call DBMS_SQLTUNE.CREATE_SQL_PLAN_BASELINE. You can use this procedure to create a SQL plan baseline on any existing reproducible plan.

D: LOAD_PLANS_FROM_SQLSET Function

This function loads plans stored in a SQL tuning set (STS) into SQL plan baselines. The plans loaded from STS are not verified for performance but added as

accepted plans to existing or new SQL plan baselines. This procedure can be used to seed SQL management base with new SQL plan baselines.

Note:

* While tuning a SQL statement, SQL Tuning Advisor searches real-time and historical performance data for alternative execution plans for the statement. If plans

other than the original plan exist, then SQL Tuning Advisor reports an alternative plan finding.

SQL Tuning Advisor validates the alternative execution plans and notes any plans that are not reproducible. When reproducible alternative plans are found, you

can create a SQL plan baseline to instruct the optimizer to choose these plans in the future.

Incorrect:

Not A: ALTER_SQL_PLAN_BASELINE Function

This function changes an attribute of a single plan or all plans associated with a SQL statement using the attribute name/value format.

Usage Notes

When a single plan is specified, one of various statuses, or plan name, or description can be altered. When all plans for a SQL statement are specified, one of

various statuses, or description can be altered. This function can be called numerous times, each time setting a different plan attribute of same plan(s) or different

plan(s).

Reference: Oracle Database Performance Tuning Guide, Alternative Plan Analysis

Correct Answer: C

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: ABC

http://docs.oracle.com/cd/E11882_01/server.112/e23633/changes.htm#UPGRD12509

Correct Answer: BC

B: Dynamic sampling first became available in Oracle9i Database Release 2. It is the ability of the cost-based optimizer (CBO) to sample the tables a query references during a hard parse, to determine better default statistics for unanalyzed segments, and to verify its "guesses." This sampling takes place only at hard parse time and is used to dynamically generate better statistics for the optimizer to use, hence the name dynamic sampling.

There are two ways to use dynamic sampling:

The OPTIMIZER_DYNAMIC_SAMPLING parameter can be set at the database instance level and can also be overridden at the session level with the ALTER

SESSION command.

The DYNAMIC_SAMPLING query hint can be added to specific queries.

C: A histogram is a collection of information about the distribution of values within a column.

In some cases, the distribution of values within a column of a table will affect the optimizer's decision to use an index vs. perform a full-table scan. This scenario occurs when the value with a where clause has a disproportional amount of values, making a full-table scan cheaper than index access.

Histograms are also important for determinine the optimal table join order.

Incorrect:

A: Too much statistics would be gathered.

Note: STALE_PERCENT - This value determines the percentage of rows in a table that have to change before the statistics on that table are deemed stale and

should be regathered. The default value is 10%.

D: In Oracle PL/SQL, the VALIDATE keyword defines the state of a constraint on a column in a table.

E: OPTIMZER_USE_PENDING_STATISTICS specifies whether or not the optimizer uses pending statistics when compiling SQL statements.

Correct Answer: DE

http://docs.oracle.com/cd/E11882_01/server.112/e41573/advisor.htm#PFGRF94911

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.

Correct Answer: DE

D: For parallel statements, the optimizer automatically decides whether to use dynamic sampling and which level to use. The decision depends on the size of the tables and the complexity of the predicates. The optimizer expects parallel statements to be resource-intensive, so the additional overhead at compile time is worth it to ensure the best plan. The database ignores the For serially processed SQL statements, the dynamic sampling level depends on the value of the OPTIMIZER_DYNAMIC_SAMPLING parameter and is not triggered automatically by the optimizer. Serial statements are typically short-running, so that any overhead at compile time could have a huge impact on their performance. the value is honored.

Reference: Oracle Database Administrator's Guide, About Oracle Database Resource Manager

Correct Answer: AB

A: You can rely on hints to set the degree of parallelism, but these can be hard to set correctly.

B:

Decision tree for query parallelization

Correct Answer: BF

*

PX JOIN FILTER CREATE The bloom filter is created in line 4.

*

PX JOIN FILTER USE The bloom filter is used in line 11.

Note:

*

You can identify a bloom pruning in a plan when you see :BF0000 in the Pstart and Pstop columns of the execution plan and PART JOIN FILTER CREATE in the operations column.

*

A Bloom filter is a probabilistic algorithm for doing existence tests in less memory than a full list of keys would require. In other words, a Bloom filter is a method for representing a set of n elements (also called keys) to support membership queries.

*

The Oracle database makes use of Bloom filters in the following 4 situations:

-To reduce data communication between slave processes in parallel joins: mostly in RAC

-

To implement join-filter pruning: in partition pruning, the optimizer analyzes FROM and WHERE clauses in SQL statements to eliminate unneeded partitions when building the partition access list

-

To support result caches: when you run a query, Oracle will first see if the results of that query have already been computed and cached by some session or

user, and if so, it will retrieve the answer from the server result cache instead of gathering all of the database blocks

-

To filter members in different cells in Exadata: Exadata performs joins between large tables and small lookup tables, a very common scenario for data warehouses with star schemas. This is implemented using Bloom filters as to determine whether a row is a member of the desired result set.