转载

oracle performance Features and Versions

Features and Versions

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 (OnLine Analytical Processing)	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 (Automatic Database Diagnostic Monitor)	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
Oracle Orion I/O Calibration Tool	Oracle Orion is a tool for predicting the performance of an Oracle database without having to install Oracle or create a database. Oracle Orion is expressly designed for simulating Oracle database I/O workloads using the same I/O software stack as Oracle.	Oracle 11gR2 (11.2.0.2)	None	Calibrating I/O
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