oracle performance Features and Versions

paulyibinyi發表於2015-12-30

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

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/7199859/viewspace-1967670/,如需轉載,請註明出處,否則將追究法律責任。

相關文章