Oracle Tuning Prerequisites and Concepts

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

To understand Oracle tuning, you should have the following prerequisites:
Technical Knowledge

1. Basic understanding of relational database management systems (RDBMS) and database concepts.
2. Familiarity with SQL (Structured Query Language) and PL/SQL (Procedural Language/Structured Query Language).
3. Knowledge of Oracle Database architecture and components (e.g., SGA, PGA, data files, control files).
4. Understanding of database performance fundamentals (e.g., response time, throughput, resource utilization).

Oracle-Specific Knowledge

1. Oracle Database versions (e.g., 11g, 12c, 19c) and their features.
2. Oracle Database configuration parameters (e.g., init.ora, spfile).
3. Basic understanding of Oracle Database storage structures (e.g., tables, indexes, partitions).

Practical Experience

1. Hands-on experience with Oracle Database administration and querying.
2. Familiarity with Oracle Database tools (e.g., Enterprise Manager, SQL*Plus).
3. Experience with database troubleshooting and performance analysis.

Optional but Recommended

1. Knowledge of operating system (OS) concepts (e.g., Linux, Windows)
2. Familiarity with performance monitoring tools (e.g., Oracle Enterprise Manager, SQL Server Management Studio).
3. Understanding of data modeling and database design principles.
4. Experience with other database platforms or tuning methodologies.

Acquiring these prerequisites will provide a solid foundation for understanding Oracle tuning concepts, methods, and tools, enabling you to optimize Oracle Database performance effectively.

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 (11.2.0.4), 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.

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.
In the next lesson, you will learn about the hardware and software you will need to take this course.