This post outlines What Performance Features are available by version.
| Feature Name | Description | Version Introduced | License Requirements | Applies to? |
|---|---|---|---|---|
| Explain Plan | Show Query Execution Plan | Oracle V6 | None | Tuning SQL |
| CBO | Cost-Based Optimizer | Oracle 7 | None | Tuning SQL |
| Analyze / DBMS_STATS | Statistics collection for CBO | Oracle 7 | None | Tuning SQL |
| Event 10053 | Trace CBO decision making | Oracle 7 | None | Tuning SQL |
| SQL_TRACE / Event 10046 | Trace SQL Execution | Oracle 7 | None | Tuning SQL |
| Parallel Query | Allows Parallel Execution of a single SQL | Oracle 7.1 | Enterprise Edition (used to require PQO license) | Tuning SQL |
| Bitmap Indexes | Faster Queries of large tables with few distinct values | Oracle 7.2 | Enterprise Edition | Tuning SQL |
| Partitioning | Allows storage large objects in multiple segments | Oracle 8i | Enterprise Edition + Partitioning Option | Tuning SQL |
| Automatic PQ Tuning | Oracle dynamically figures out how many Parallel Execution servers to use | Oracle 8i | Enterprise Edition | Tuning SQL |
| Auto PGA Memory Management | Oracle dynamically allocates workarea memory needed for SQL execution based on a systemwide target | Oracle 9i | None | Tuning Memory |
| Dynamic SGA Memory Management | Allows dynamic resizing of the buffer cache and shared pool, including a buffer cache size advice mechanism that predicts the performance of running with different sizes for the buffer cache. | Oracle 9iR1 | None | Tuning Memory |
| Stored Outlines | Allows freezing of CBO execution plans | Oracle 9i | Enterprise Edition + Standard Edition (9iR2 onwards) | Tuning SQL |
| Oracle OLAP | More SQL options for Data Warehousing / Data Mining | Oracle 9i | Enterprise Edition + OLAP Option | Tuning SQL |
| User Defined Stats | Provides facility for users to define their own statistics - mostly for user defined types / objects | Oracle 9i | Enterprise Edition | Tuning SQL |
| Materialized View Query Rewrite | Allows reuse of pre-calculated group by / rollup type stuff | Oracle 9i | Enterprise Edition | Tuning SQL |
| PGA Memory Advisor | Helps to calculate an optimal pga_aggregate_target | Oracle 9iR2 | None | Tuning Memory |
| Enhanced Statistics Gathering | This includes enhanced Query Execution Stats, DBMS_STATS Improvements and System Statistics | Oracle 9iR2 | None | Tuning SQL |
| Dynamic Sampling of Optimizer Statistics | Dynamically gathers statistics if the existing statistics are incomplete or known to be inaccurate | Oracle 9iR2 | None | Tuning SQL |
| Auto DBMS_STATS Collection | Finds objects with stale stats and gathers new statistics for them | Oracle 10g | None | Tuning SQL |
| Automatic Workload Repository (AWR) | Automatic Workload Repository (AWR) is an infrastructure that collects, processes, and maintains performance statistics | Oracle 10g | Diagnostics Pack (Enterprise Edition Only) | Tuning Database |
| Active Session History (ASH) | Continual sampling history of top sessions and the SQL they are executing | Oracle 10g | Diagnostics Pack (Enterprise Edition Only) | Tuning Database |
| ADDM | Analyzes Workload Repository and makes tuning suggestions even pointing out Top SQL | Oracle 10g | Diagnostics Pack (Enterprise Edition Only) | Tuning Database |
| Automatic Shared Memory Tuning | Automates the configuration of System Global Area (SGA) memory-related parameters (buffer cache, shared pool) through self-tuning algorithms. | Oracle 10gR1 | None | Tuning Memory |
| Segment Advisor | Finds objects that are fragmented with free space and can be shrunk | Oracle 10g | None | Tuning SQL |
| trcsess Utility | The trcsess utility consolidates trace output from selected trace files | Oracle 10g | None | Tuning Storage |
| SQL Tuning Advisor (STA)/ SQL Profiles | Finds SQL where the optimizer makes bad decisions due to inaccurate usage of statistics and provides recommendations | Oracle 10g | Tuning Pack (Enterprise Edition Only) | Tuning SQL |
| SQL Tuning Sets | Used by Tuning Advisor (and later SQL Perf Analyzer) | Oracle 10g | Tuning Pack or Real Application Testing (Enterprise Edition Only) | Tuning SQL |
| SQL Access Advisor | Makes recommendations about indexes, materialized views, and partitions to create, drop, or retain | Oracle 10g | Tuning Pack (Enterprise Edition Only) | Tuning SQL |
| DBMS_STATS enhancements | Lock stats, restore historical stats | Oracle 10gR1 | None | Tuning SQL |
| DBMS_SQLDIAG | Provides an interface to the SQL Diagnosability functionality. | Oracle 10gR2 | None | Universal |
| ASH Reporting | Summarises Active Session History information making it more useable | Oracle 10gR2 | Diagnostics Pack (Enterprise Edition Only) | Tuning Database |
| SQL Execution History | Better reporting of how execution plans may have changed over time | Oracle 10gR2 | Diagnostics Pack (Enterprise Edition Only) | Tuning SQL |
| Virtual Columns | Allows 'virtual' columns to be added to a table | Oracle 11gR1 | None | Tuning SQL |
| SQL Test Case Builder | Generic package to record standard information for issue diagnosis. Builds package with SQL, associated table / index / view definitions etc. | Oracle 11gR1 | None | Universal |
| Real Application Testing (RAT) | Real Application Testing functionality allows potential issues with system changes to be identified before they are deployed in a production environment | Oracle 11gR1 | Real Application Testing option (Enterprise Edition Only) | Universal |
| SQL Performance Analyzer(SPA) | Tests and reports how SQL Performance can be affected by configuration / version changes | Oracle 11gR1 | Real Application Testing option (Enterprise Edition Only) | Tuning SQL |
| Auto SQL Tuning | Identifies Top SQL and applies SQL Profiles if needed | Oracle 11gR1 | Tuning Pack (Enterprise Edition Only) | Tuning SQL |
| SQL Plan Management (SPM) | Gives DBAs more control as to when execution plans can change | Oracle 11gR1 | Enterprise Edition | Tuning SQL |
| Manual Plan Evolution | Allows DBAs to explicitly evolve plans | Oracle 11gR1 | Enterprise Edition | Tuning SQL |
| Automatic Plan Evolution | Allow Auto SQL Tuning to evolve new plans without user intervention | Oracle 11gR1 | Tuning Pack (Enterprise Edition Only) | Tuning SQL |
| Enhanced Stats Management | Test how SQL Execution Plans may be affected by newly gathered statistics before publishing them | Oracle 11gR1 | None | Tuning SQL |
| MultiColumn Stats | Collect stats across columns that have some data dependency | Oracle 11gR1 | None | Tuning SQL |
| Adaptive Cursor Sharing (ACS) | When bind variables or cursor_sharing is used, allows shared SQL cursors to still have multiple shared plans where data distribution is skewed | Oracle 11gR1 | None | Tuning SQL |
| Query Result Cache | The query result cache enables explicit caching of results in database memory. Subsequent queries using the cached results will experience significant performance improvements. | Oracle 11gR1 | Enterprise Edition | Universal |
| Stored Outlines to Plan Management Migration | Allows 9i stored outlines to be turned into SQL Plans (since outlines are deprecated) | Oracle 11gR2 | Enterprise Edition | Tuning SQL |
| Real-Time Database Operations Monitoring | Allows database administrators to easily monitor and troubleshoot performance problems in long running jobs by composite database operation monitoring | Oracle 12cR1 | None | Tuning Database |
| Real-Time ADDM Analysis | Real-Time ADDM runs through a set of predefined criteria to analyze the current performance of the database. | Oracle 12cR1 | Diagnostics Pack (Enterprise Edition Only) | Tuning Database |
| Adaptive Query Optimization | Adaptive query optimization is a set of capabilities that enable the optimizer to make run-time adjustments to execution plans and discover additional information that can lead to better statistics. | Oracle 12cR1 | None | Tuning SQL |
| Enhanced Column Histogram Statistics | Two additional types of histograms have been introduced for columns which have more than 254 distinct values to improve the cardinality estimates generated using histograms. | Oracle 12cR1 | None | Tuning SQL |
| Online Statistics Gathering for Bulk Loads | This features automatically generates statistics for data that is added as part of a bulk load operation such as a CREATE TABLE AS SELECT operation or an INSERT INTO ... SELECT operation on an empty table. | Oracle 12cR1 | None | Tuning SQL |
| Session-Private Statistics for Global Temporary Tables | This feature allows global temporary tables to have a different set of statistics for each session. | Oracle 12cR1 | None | Tuning SQL |
| SQL Plan Directives | SQL plan directives allow the optimizer to automatically use prior execution information to determine that extended statistics may be required for a similar queries executed later. | Oracle 12cR1 | None | Tuning SQL |
| Adaptive SQL Plan Management | Adaptive SQL Plan Management allows the database to automatically verify, evolve and accept non-accepted plans that perform better than the existing accepted plan | Oracle 12cR1 | None | Tuning SQL |
| Automatic Column Group Detection | Detects potential column groups based upon workload | Oracle 12cR1 | None | Tuning SQL |
--Asifkhan
