Do You Know the History of These Oracle Database Performance Related Features? Take the Quiz

Event 10053 is an internal event that externalizes some of the decisions made by the Cost Based Optimizer (CBO) in to a trace file.

Parallel Query Execution is a technology that may improve response time for certain queries by using multiple parallel execution servers. Requires Enterprise Edition.

The adaptive cursor sharing feature enables a single statement that contains bind variables to use multiple execution plans. Cursor sharing is "adaptive" because the cursor adapts its behavior so that the database does not always use the same plan for each execution or bind variable value.

Multi-Column statistics uses column groups to record statistics on related columns. This is particularly useful where there is a relationship between these columns that strongly affects their combined selectivity.

Partitioning offers support for very large tables and indexes by letting you decompose them into smaller and more manageable pieces called partitions. Partitioning can help you tune SQL statements to avoid unnecessary index and table scans (using partition pruning). It also enables you to improve the performance of massive join operations when large amounts of data (for example, several million rows) are joined together by using partition-wise joins. Requires Enterprise Edition + Partitioning Option.

The virtual column feature allows users to add a 'virtual' column to a table. This virtual column can be a combination of other columns and functions and from the performance point of view can be useful since statistics can be gathered on the virtual column. This can help with correcting cardinality estimates for column combinations that do not otherwise match the expected uniform selectivity profile.

SQL Profiles is a new feature from 10g which is managed by Oracle Enterprise Manager as part of the Automatic SQL Tuning process. Apart from OEM, SQL Profiles can be managed through the DBMS_SQLTUNE package. SQL Tuning Advisor automates the entire SQL tuning process. The automatic process replaces manual SQL tuning. Requires Tuning Pack.

This feature automates the collection of optimizer statistics for objects with stale or no statistics.

The Real Application Testing option is a collection of features that can prevent the undesirable impact of routine changes such as upgrades, configuration changes, platform migrations, etc. Real-world applications can be comprehensively tested to identify and address any issues prior to implementation on production to minimize impact when a change is actually made. Requires the Real Application Testing option.

Explain plan allows you to view the access path used by a query so that you can decide whether the methods used are sensible.

The V$ACTIVE_SESSION_HISTORY view provides sampled session activity in the instance. Active sessions are sampled every second and are stored in a circular buffer in SGA. Any session that is connected to the database and is waiting for an event that does not belong to the Idle wait class is considered as an active session. This includes any session that was on the CPU at the time of sampling. If ASH reports show most of the active session waits are SQL Execution related, then the SQL that is associated with the most waits may need tuning and usually worth calling the SQL Tuning Advisor on them (or tune them manually). Requires Diagnostics Pack.

A stored outline is a set of hints for a SQL statement. The hints direct the optimizer to choose a specific plan for the statement. A stored outline is a legacy technique for providing plan stability. Requires Enterprise Edition.

Bitmap indexing is an indexing technology where keys are stored in a 'compressed' format which can provide considerable space savings and speed improvements when compared to traditional B-tree indexing. It is particularly useful for complex data warehousing queries. Requires Enterprise Edition.

This feature tests how SQL Execution Plans may be affected by newly gathered statistics before publishing them. This is done by saving the new statistics as pending, then validating and publishing them only if they are satisfactory.

Event 10046 or SQL_TRACE records a varied set of information regarding the execution of SQL within an individual session. The performance specific information from that trace may be useful for problem diagnosis or tuning.

The Cost Based optimizer (CBO) was introduced in Oracle7 and replaced the Rule Based Optimizer (RBO) which was phased out in oracle 10g. The CBO uses statistics to determine the most optimal access path for queries.

The Automatic Workload Repository (AWR) collects, processes, and maintains performance statistics for problem detection and self-tuning purposes. This data is both in memory and stored in the database. The gathered data can be displayed in both reports and views. The Automatic Workload Repository is extremely useful for revealing SQL Tuning opportunities. If the database is slow, and the Top 5 (or so) timed events include "CPU" and "db file sequential read" and "db file scattered read" in any order, then it is usually worth jumping to the Top SQL (by logical and physical reads) section of an AWR report and calling the SQL Tuning Advisor on them (or tune them manually). Requires Diagnostics Pack.

Dynamic Sampling is a method of gathering additional statistics during optimization by recursively sampling statements. When dynamic sampling is enabled, queries are recursively generated by Oracle to test various selectivities based upon real values in order to improve their accuracy. This can result in the production of better explain plans.