Course Overview

This Oracle Database 11g: SQL Tuning course is designed to equip database administrators and application developers with the skills needed to build efficient SQL statements and to tune database applications. Students will learn about the internals of SQL statement execution, how to monitor the performance of such execution, and how one can influence the behavior of the database to achieve performance gains.
This course covers information relevant for the certification test EXAM 1Z0-054: ORACLE DATABASE 11G R2: PERFORMANCE TUNING.

What you’ll learn

Upon completion of the Oracle Database 11g: SQL Tuning course, students will be able to:

• Consider the unique and differing tuning issues found in online database applications, enterprise resource and data warehouse environments, and the important metrics of SQL statement performance.
• Learn about the internal mechanisms used for SQL statement execution within a database instance and how these can affect performance for good or bad, including the optimizer facilities known as the transformation engine, estimator, and plan generator.
• Use a variety of techniques to examine the details of SQL statement execution, spotting trouble areas and bottlenecks that require tuning.
• Learn about the Auto-Task framework and how to manage the automatic collection of Optimizer statistics and automatic SQL tuning using both the programmatic and Enterprise Manager interfaces.
• Become trained in how statistical deficiencies can dramatically degrade performance and how these problems are resolved through customized optimizer statistics collection procedures using the DBMS_STATS() package, system statistics, histograms, expression statistics, and multicolumn statistics.
• Influence the behavior of the optimizer by setting database parameters and other SQL tuning techniques.
• Utilize the database advisory framework and the SQL Tuning and SQL Access advisors.
• Demonstrate plan management to achieve plan stability, which is adaptive and even dynamic.
• Understand the self-tuning infrastructure and the automatic SQL tuning capabilities found within the database.
• Employ SQL hints embedded into the statement text to resolve unique tuning challenges.
• Learn to identify poorly performing SQL statements using real-time SQL monitoring and application tracing techniques such as DBMS_MONITOR(), trcsess, and tkprof.