oracle 11g performance tuning document

What's New in Oracle Performance?
This section describes new performance features of Oracle Database 10g Release 2
(10.2) and provides pointers to additional information. The features and enhancements
described in this section comprise the overall effort to optimize server performance.
For a summary of all new features for Oracle Database 10g Release 2 (10.2), see Oracle
Database New Features Guide. For information about using Oracle Enterprise Manager
to tune the performance of Oracle Database, see Oracle Database 2 Day + Performance
Tuning Guide.
The new and updated performance features in Oracle Database 10g Release 2 (10.2)
include:
■ Active Session History Reports
Active Session History (ASH) reports contains ASH information that can be used
to identify blocker and waiter identities and their associated transaction identifiers
and SQL for a specified duration. See "Generating Active Session History Reports"
on page 5-20.
■ Automatic PGA Memory Management
A new view has been added to monitor dynamic PGA memory usage for each
Oracle process. See "Monitoring the Performance of the Automatic PGA Memory
Management" on page 7-39 for descriptions of the V$PROCESS_MEMORY view.
■ Automatic Shared Memory Management
Automatic Shared Memory Management simplifies the configuration of System
Global Area (SGA) memory-related parameters through self-tuning algorithms.
Automatic Shared Memory Management has been enhanced and the streams pool
is now auto tuned as part of the automatic SGA management. See "Automatic
Shared Memory Management" on page 7-2.
■ Automatic Tuning of Multiblock Read Count
The DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter is now
automatically tuned to use a default value when this parameter is not set
explicitly. See "Controlling the Behavior of the Query Optimizer" on page 13-5 for
more information on the DB_FILE_MULTIBLOCK_READ_COUNT parameter.
■ Automatic Workload Repository Reports
Automatic Workload Repository (AWR) reports display statistics for a range of
snapshot Ids. Two new reports, awrsqrpt.sql and awrsqrpi.sql, have been
added to view statistics for a particular SQL statement. See "Generating Automatic
Workload Repository Reports" on page 5-15.
■ Configurable Automatic Workload Repository SQL Collection
The Automatic Workload Repository (AWR) collects, processes, and maintains
performance statistics for problem detection and self-tuning purposes, including
SQL statements that are producing the highest load on the system. This feature
has been enhanced to enable the configuration of the number of Top SQL to flush
for each SQL criteria (Elapsed Time, CPU Time, Parse Calls, Shareable Memory,
and Version Count). See "Overview of the Automatic Workload Repository" on
page 5-7.
■ Database Replay
You can capture a database workload on a production system and replay it on a
test system to ensure that system changes, such as database upgrades, will yield
desired results. For more information, see Chapter 21, "Database Replay".
■ Enhanced End to End Application Tracing
End to End Application Tracing identifies the source of an excessive workload,
such as a high load SQL statement. This feature has been enhanced to enable SQL
tracing on a session, instance wide, or an entire database level. See "End to End
Application Tracing" on page 20-1.
■ Improved System Statistics
The V$SYSSTAT view has added rows to capture the total number of physical
I/O’s performed by any Oracle process. See "Examine Load" on page 10-10.
Additionally, a new event has been added to reduce the memory used for
maintaining statistics on events in the Other wait class. See "events in wait class
other" on page 10-27.
■ SQL Access Advisor
The SQL Access Advisor and its related DBMS_ADVISOR package now recommend
function-based indexes, the recommendation process can now be interrupted and
there are improvements in the Oracle Enterprise Manager. See Chapter 17, "SQL
Access Advisor".
■ SQL Performance Analyzer
The SQL Performance Analyzer enables you to forecast the impact of system
changes on SQL performance by testing these changes using a SQL workload on a
test system. For more information, see Chapter 22, "SQL Performance Analyzer".
■ SQL Profiles
The DBMS_SQLTUNE package now also offers the ability to allow SQL with text
differing only in its literal values to share a SQL Profile by normalizing literal text
values to bind variables. See "Accepting a SQL Profile" on page 12-14.
■ SQL Tuning Advisor
Note: Only workload capture is currently supported in this release.
Captured workloads can be preprocessed and replayed on Oracle
Database 11g Release 1 (11.1) and subsequent releases.
Note: Only SQL workload capture is currently supported in this
release. Captured SQL workloads can be executed, and their
performance can be measured and compared, on Oracle Database 11g
Release 1 (11.1) and subsequent releases.
It is now possible to monitor the execution progress of the SQL Tuning Advisor
using the new V$ADVISOR_PROGRESS view. See "Checking the Progress of the
SQL Tuning Advisor" on page 12-8.
■ SQL Tuning Sets
SQL Tuning Sets can now be exported to or imported from another system using
the DBMS_SQLTUNE package procedures. See "Transporting a SQL Tuning Set" on
page 12-12.
■ V$SQLSTATS View
A new view, V$SQLSTATS, returns performance statistics for SQL cursors.
V$SQLSTATS contains a subset of columns that appear in V$SQL and
V$SQLAREA. However, the V$SQLSTATS view differs from V$SQL and
V$SQLAREA in that it is faster, more scalable, and has a greater data retention. It is
recommended that V$SQLSTATS be used in place of V$SQL when fetching
statistics for SQL cursors.
See Also: Oracle Database Reference for more information on
V$SQLSTATS
You would..
Share
Related Documents
  1. Oracle Bi Testing Strategy (584)
  2. Oracle Database Performance Tuning Guide 10g Release 2 (10.2) (2355)
  3. Oracle performance tuning (1061)
  4. J2EE Performance Tuning (1044)
  5. Sybase Query processor & Performance Tuning in ASE 15 Using Query Processing Metrics (1855)
  6. [Webinar] Performance Tuning for ActiveMQ (1345)
  7. Beginning Oracle SQL (Expert's Voice in Oracle) (444)
  8. JBOSS Performance tuning (1142)
  9. How to record Oracle NCA based application using HP LoadRunner? (817)
  10. Oracle Database 10g PL/SQL Programming (1730)
  11. ORACLE SQL*Plus (1289)
  12. Oracle9i Installation steps (1605)
  13. Beginning Oracle Database 11g Administration: From Novice to Professional (956)
  14. 2010-12-16, CMG International Conference @ USA (1468)
  15. Java Performance Tuning News (702)
  16. SQL Server Performance Monitoring Counters and its description (673)
  17. Best Practices on Optimizing Web Logic Server Performance - JVM Tuning (1793)
  18. IBM Http server parameter tuning for performance engineering (860)
  19. weblogic server performance & tuning (895)
  20. AIX 5L Practical Performance Tools and Tuning Guide (340)