Advanced Tuning  «Prev  Next»
Lesson 2 Oracle Tuning Prerequisites
ObjectiveVerify necessary background for this course.
In order to get the most out of this course, you should have a solid grasp of the basics of Oracle tuning concepts. However, you should also have a basic understanding of the
  1. UNIX operating system,
  2. PL/SQL coding techniques
, and the following Oracle database concepts:
Prior to taking this course, you should be able to do the following:
  1. Identify and diagnose Oracle memory performance problems, including problems in the library cache, data buffer cache, and data dictionary cache
  2. Understand basic Oracle tuning techniques, metrics and tuning goals for measuring and monitoring performance
  3. Understand basic Oracle SGA and memory management concepts

If you have not yet mastered these skills, consider visiting on of the other links posted above.
In the next lesson, you will learn about the hardware and software you will need to take this course.

New Features in Oracle Database Performance for Oracle Database 11g Release 2 (

The new and updated performance tuning features include:
Dynamic statistics enhancements
In previous releases, Oracle Database only gathered dynamic statistics (previously called dynamic sampling) when one or more of the tables in a query did not have optimizer statistics. Starting in Oracle Database 11g Release 2 (, the optimizer can automatically decide whether dynamic statistics are useful and which dynamic statistics level to use for all SQL statements. For example, the optimizer automatically decides whether to gather dynamic statistics during table scans, index access, joins, and GROUP BY operations. The enhanced behavior is enabled only when the OPTIMIZER_DYNAMIC_SAMPLING initialization parameter is set to the new value of 11.

Oracle Database 11g Release 2 ( Features in Oracle Database Performance

The new and updated performance tuning features include:
Resource Manager enhancements for parallel statement queuing .
You can use Resource Manager to control the order of statements in a parallel statement queue. For example, you can ensure that high-priority statements spend less time in the queue. Also, you can use a directive to prevent one consumer group from monopolizing all of the parallel servers, and to specify the maximum time in seconds that a parallel statement can wait to be launched.