Oracle Database 11g R2 - SQL Tuning
This Oracle Database 11g R2: SQL Tuning training class equips database administrators and application developers 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 to influence the behavior of the database to achieve performance gains.
The primary target audiences for this course are senior application designers and database developers, PL/SQL developers, database administrators,
web server administrators, system administrators, implementation specialists, and data center support engineers.
This course helps prepare student to take exam 1Z0-054: Oracle Database 11G R2: Performance Tuning.
- Learn the unique and differing tuning issues found in online database applications.
- Learn about resource and data warehouse environments and the important metrics of SQL statement.
- Learn about the internal mechanisms use for SQL statement execution within a database instance.
- Learn how these can affect performance for good or bad, including the Optimizer facilities.
- Learn about Engine, Estimator and Plan Generator.
- Learn to use a variety of techniques to examine the details of SQL statement execution, spotting trouble.
- Learn about areas and bottlenecks that require tuning.
- Learn about the Auto-Task framework and how to manage the automatic collection of Optimizer.
- Learn about statistics and automatic SQL tuning using both the programmatic and Enterprise Manager interfaces.
- Learn how statistic deficiencies can dramatically degrade performance.
- Learn how statistic deficiencies can dramatically degrade performance, and how these problems are resolved through customized Optimizer statistics
collection procedures using the DBMS_STATS().
- Learn to package, system statistics, histograms, expression statistics and MultiColumn statistics.
- Learn to influence the behavior of the Optimizer by setting database parameters and other SQL tuning.
- Learn to utilize the database advisory framework and the SQL Tuning and SQL Access advisors.
- Learn to use plan management to achieve plan stability that is adaptive and even dynamic.
- Learn to understand the self-tuning infrastructure and the automatic SQL tuning capabilities.
- Learn to 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.
- Learn techniques such as DBMS_MONITOR(), trcsess and tkprof.
- Tuning and the Oracle Database Advisory Framework
- The Challenges of Tuning
- Performance Metrics
- Management and Advisory Framework
- ADDM and AWR
- SQL Tuning Privileges
- Viewing and Monitoring the Execution Plan
- About the Execution Plan
- Collecting Performance Statistics
- Viewing the Execution Plan
- Real-time SQL Monitoring
- Understanding the Optimizer
- Optimization Methods
- Optimization Goals
- Optimizer Components
- Execution Plan Operations
- Execution Plan Methods and Operations
- Table Access Methods
- Join Methods
- Index Operations
- Data Operations
- Managing Optimizer Statistics
- More about Optimizer Statistics
- Automatic Maintenance Tasks
- Manually Gathering Statistics
- GATHER_SYS Parameter
- USING HISTORICAL STATISTICS
- Dynamic Sampling
- Locking Statistics
- Enhanced Optimizer Statistics
- About Optimizer System Statistics
- Manage System Statistics
- CREATE_STAT_TABLE(), DROP_STAT_TABLE()
- Pending and Published Statistics
- Histograms and Extended Statistics
- Why Are Histograms Needed?
- Histograms Internal Structure
- Manually Managing Histograms
- Expression Statistics
- Multicolumn Statistics
- Application Tracing
- Application Tracing Packages
- EM Application Monitoring and Tuning
- REAL-time Monitoring
- Using TRCSESS and TKPROF
- ADDM and the SQL Tuning Advisor
- ADDM Performance Analysis
- Using the SQL Tuning Advisor
- Automatic SQL Tuning
- Configuring Automatic SQL Tuning
- The SQL Access Advisor
- Using the SQL Access Advisor
- SQL Access Advisor Templates
- Performing a Quick Tune Task
- Index Database Parameters
- Plan Management
- SQL Management Base Architecture
- Using Plan Baselines
- Plan Management Using EM
- Managing Cursor Sharing
- About Cursor Sharing
- Bind Variables and Cursor Sharing
- THE CURSOR_SHARING Parameter
- Finding Candidates for Rewrite
- Optimizer Hints
- What Are Hints?
- Hint Explanations
Each student in our Live Online and our Onsite classes receives a comprehensive set of materials, including course notes and all the class examples.
Courses that can help you meet these prerequisites:
Live Online: Course Setup | WebEx Setup
- Introduction to SQL Training
- Oracle Database 11g R2 - SQL Tuning, Architecture, and Internals