Oracle Database 11g Release 2: SQL Tuning Exam

Step 1: (1)
Step 2: (2)
Step 3: (3)
Step 4: (4)
By setting the parameter OPTIMIZER_FEATURES_ENABLE to the 10g version used before the upgrade, you should be able to revert back to the same execution plans you had prior to the upgrade.
Step 5: (8)
OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES
In Oracle Database 11g a new feature called SQL Plan Management (SPM) has been introduced to guarantees any plan changes that do occur lead to better performance. When OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES is set to TRUE (default FALSE) Oracle will
automatically capture a SQL plan baseline for every repeatable SQL statement on the system. The execution plan found at parse time will be added to the SQL plan baseline as an accepted plan.

Step 6: (10)
OPTIMIZER_USE_SQL_PLAN_BASELINES enables or disables the use of SQL plan baselines stored in SQL Management Base. When enabled, the optimizer looks for a SQL plan baseline for the SQL statement being compiled. If one is found in SQL Management Base, then the optimizer will cost each of the baseline plans and pick one with the lowest cost.

* (A, not D): The explain plan process stores data in the PLAN_TABLE.
* EXPLAIN PLAN

The EXPLAIN PLAN method doesn't require the query to be run (A), greatly reducing the time it takes to get an execution plan for long-running queries compared to AUTOTRACE.

E: Use the DBMS_XPLAN.DISPLAY function to display the execution plan.

* The DBMS_XPLAN package provides an easy way to display the output of the EXPLAIN PLAN command in several, predefined formats. You can also use the DBMS_XPLAN package to display the plan of a statement stored in the Automatic Workload Repository (AWR) or stored in a SQL tuning set. It further provides a way to display the SQL execution plan and SQL execution runtime statistics for cached SQL cursors based on the information stored in the V$SQL_PLAN and
V$SQL_PLAN_STATISTICS_ALL fixed views.

Note:
*
First the query must be explained.

SQL> EXPLAIN PLAN FOR
2 SELECT * 3 FROM emp e, dept d 4 WHERE e.deptno = d.deptno
5 AND e.ename = 'SMITH';

Explained.

SQL>
Then the execution plan displayed. (not B)

SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql
Plan Table
-------------------------------------------------------------------------------- | Operation | Name | Rows | Bytes| Cost | Pstart| Pstop |
-------------------------------------------------------------------------------- | SELECT STATEMENT | | | | | | |
| NESTED LOOPS | | | | | | |
| TABLE ACCESS FULL |EMP | | | | | |
| TABLE ACCESS BY INDEX RO|DEPT | | | | | |
| INDEX UNIQUE SCAN |PK_DEPT | | | | | |
--------------------------------------------------------------------------------

8 rows selected.

SQL>
For parallel queries use the "utlxplp.sql" script instead of "utlxpls.sql".

Note:
* Before tracing can be enabled, the environment must first be configured to enable gathering of statistics.
* (gather statistics): DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE
Enables statistic gathering for a given combination of Service Name, MODULE and ACTION

* DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE
Enables SQL tracing for a given combination of Service Name, MODULE and ACTION globally unless an instance_name is specified.

dbms_monitor.serv_mod_act_trace_enable(
service_name IN VARCHAR2,
module_name IN VARCHAR2 DEFAULT ANY_MODULE, action_name IN VARCHAR2 DEFAULT ANY_ACTION,
waits IN BOOLEAN DEFAULT TRUE, binds IN BOOLEAN DEFAULT FALSE,
instance_name IN VARCHAR2 DEFAULT NULL, plan_stat IN VARCHAR2 DEFAULT NULL);
SELECT instance_name
FROM gv$instance;

exec dbms_monitor.serv_mod_act_trace_enable('TESTSERV', dbms_monitor.all_modules, dbms_monitor.all_actions, TRUE, TRUE, 'orabase');

exec dbms_monitor.serv_mod_act_trace_disable('TESTSERV', dbms_monitor.all_modules, dbms_monitor.all_actions, 'orabase');

* When solving tuning problems, session traces are very useful and offer vital information. Traces are simple and straightforward for dedicated server sessions, but for shared server sessions, many processes are involved. 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.
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.

* Once the trace files have been consolidated (with trcsess), tkprof can be run against the consolidated trace file for reporting purposes.

Manual Plan Loading
Manual plan loading can be used in conjunction with, or as an alternative to automatic plan capture. The load operations are performed using the DBMS_SPM package, which allows SQL plan baselines to be loaded from SQL tuning sets or from specific SQL statements in the cursor cache. Manually loaded statements are flagged as accepted by default. If a SQL plan baseline is present for a SQL statement, the plan is added to the baseline, otherwise a new baseline is created.

Note:
* The value of the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameter, whose default value is FALSE, determines if the system should automatically capture SQL plan baselines. When set to TRUE, the system records a plan history for SQL statements. The first plan for a specific statement is automatically flagged as accepted. Alternative plans generated after this point are not used until it is verified they do not cause performance degradations. Plans with acceptable performance are added to the SQL plan baseline during the evolution phase.

* Managing SQL plan baselines involves three phases:

Capturing SQL Plan Baselines
Selecting SQL Plan Baselines
Evolving SQL Plan Baselines

* Evolving SQL Plan Baselines

Evolving a SQL plan baseline is the process by which the optimizer determines if non-accepted plans in the baseline should be accepted. As mentioned previously, manually loaded plans are automatically marked as accepted, so manual loading forces the evolving process. When plans are loaded automatically, the baselines are evolved using the EVOLVE_SQL_PLAN_BASELINE function, which returns a CLOB reporting its results.

References:
SQL Plan Management in Oracle Database 11g Release 1

The first executed join is in line 6.
The second executed join is in line 1.

Incorrect:
A: Line 7 and 8 are executed first.

Note:
* Statistics quantify the data distribution and storage characteristics of tables, columns, indexes, and partitions.
* INDEX_COMBINE
Forces a bitmap index access path on tab.
Primarily this hint just tells Oracle to use the bitmap indexes on table tab. Otherwise Oracle will choose the best combination of indexes it can think of based on the statistics. If it is ignoring a bitmap index that you think would be helpful, you may specify that index plus all of the others taht you want to be used. Note that this does not force the use of those indexes, Oracle will still make cost based choices.
* Histograms Opportunities
Any column used in a where clause with skewed data Histograms are NOT just for indexed columns.
Adding a histogram to an un-indexed column that is used in

Note:

* Oracle provides the trcsess command-line utility that consolidates tracing information based on specific criteria.

The SQL Trace facility and TKPROF are two basic performance diagnostic tools that can help you monitor applications running against the Oracle Server.

Note: SERV_MOD_ACT_TRACE_ENABLE Procedure
Enables SQL tracing for a given combination of Service Name, MODULE and ACTION globally unless an instance_name is specified

References:
Oracle Database Performance Tuning Guide

Example:

Additional set operations will be done for the customer dimension and the product dimension. At this point in the star query processing, there are three bitmaps.
Each bitmap corresponds to a separate dimension table, and each bitmap represents the set of rows of the fact table that satisfy that individual dimension's constraints.

These three bitmaps are combined into a single bitmap using the bitmap AND operation. This final bitmap represents the set of rows in the fact table that satisfy all of the constraints on the dimension table.

References:
Oracle Database Data Warehousing Guide, Star Transformation with a Bitmap Index

Parse operations fall into the following categories, depending on the type of statement submitted and the result of the hash check:

A) Hard parse

If Oracle Database cannot reuse existing code, then it must build a new executable version of the application code. This operation is known as a hard parse, or a library cache miss. The database always perform a hard parse of DDL.

During the hard parse, the database accesses the library cache and data dictionary cache numerous times to check the data dictionary. When the database accesses these areas, it uses a serialization device called a latch on required objects so that their definition does not change (see "Latches"). Latch contention increases statement execution time and decreases concurrency.

B) Soft parse
A soft parse is any parse that is not a hard parse. If the submitted statement is the same as a reusable SQL statement in the shared pool, then Oracle Database reuses the existing code. This reuse of code is also called a library cache hit.

Soft parses can vary in the amount of work they perform. For example, configuring the session cursor cache can sometimes reduce the amount of latching in the soft parses, making them "softer."

In general, a soft parse is preferable to a hard parse because the database skips the optimization and row source generation steps, proceeding straight to execution.

Incorrect:
A, C: During the parse call, the database performs the following checks:

Syntax Check

Semantic Check

Shared Pool Check

The hard parse is within Shared Pool check.

References:
Oracle Database Concepts 11g, SQL Parsing

A: Parallel Target Percentage

It is possible for a single consumer group to launch enough parallel statements to use all the available parallel servers. If this happens, when a high-priority parallel statement from a different consumer group is run, no parallel servers are available to allocate to this group. You can avoid such a scenario by limiting the number of parallel servers that can be used by a particular consumer group.

Use the PARALLEL_TARGET_PERCENTAGE directive attribute to specify the maximum percentage of the parallel server pool that a particular consumer group can use. The number of parallel servers used by a particular consumer group is counted as the sum of the parallel servers used by all sessions in that consumer group.

Incorrect:

Not B: PARALLEL_SERVERS_TARGET specifies the number of parallel server processes allowed to run parallel statements before statement queuing will be used. When the parameter PARALLEL_DEGREE_POLICY is set to AUTO, Oracle will queue SQL statements that require parallel execution, if the necessary parallel server processes are not available. Statement queuing will begin once the number of parallel server processes active on the system is equal to or greater
than PARALLEL_SERVER_TARGET.
Not C: Would be true if we replace PARALLEL_MAX_SERVERS with PARALLEL_SERVER_TARGET.
Not E: The PARALLEL_QUEUE_TIMEOUT directive attribute enables you to specify the maximum time, in seconds, that a parallel statement can wait in the parallel statement queue before it is timed out. The PARALLEL_QUEUE_TIMEOUT attribute can be set for each consumer group. This attribute is applicable even if you do not specify other management attributes (mgmt_p1, mgmt_p2, and so on) in your resource plan.
Note:
* PARALLEL_DEGREE_POLICY
AUTO
Enables automatic degree of parallelism, statement queuing, and in-memory parallel execution.

* The PARALLEL_TARGET_PERCENTAGE attribute enables you to specify when parallel statements from a consumer group can be queued. Oracle Database maintains a separate parallel statement queue for each consumer group.

* PARALLEL_SERVERS_TARGET specifies the number of parallel server processes allowed to run parallel statements before statement queuing will be used.
When the parameter PARALLEL_DEGREE_POLICY is set to AUTO, Oracle will queue SQL statements that require parallel execution, if the necessary parallel server processes are not available. Statement queuing will begin once the number of parallel server processes active on the system is equal to or greater than PARALLEL_SERVER_TARGET.

By default, PARALLEL_SERVER_TARGET is set lower than the maximum number of parallel server processes allowed on the system (PARALLEL_MAX_SERVERS) to ensure each parallel statement will get all of the parallel server resources required and to prevent overloading the system with parallel server processes.

Note that all serial (non-parallel) statements will execute immediately even if statement queuing has been activated.

* Oracle Database Resource Manager (the Resource Manager) enables you to optimize resource allocation among the many concurrent database sessions.

The elements of the Resource Manager are:

/ Resource consumer group A group of sessions that are grouped together based on resource requirements. The Resource Manager allocates resources to resource consumer groups, not to individual sessions.

/ Resource plan A container for directives that specify how resources are allocated to resource consumer groups. You specify how the database allocates resources by activating a specific resource plan.

/ Resource plan directive Associates a resource consumer group with a particular plan and specifies how resources are to be allocated to that resource consumer group.