Oracle Performance Tuning 1Z0-033

Training Provided by The Virtual Training Company VTC author, Gavin Powell, covers material in the 1Z0-033 Oracle 9i tuning certification examination, including the basics of performance tuning and tuning tools. Memory tuning covers the shared pool, the database buffer cache and other buffers. From an I/O perspective, topics covered are physical file structures of the database, tablespaces, datafiles and at the block level. Also covered are sorting, locking, latches and wait events, shared or dedicated server processes, statistics, SQL code tuning and resource management. There is also brief coverage of tuning in the operating system, plus specialized objects use. To begin learning today, simply click on one of the <b>Oracle Performance Tuning (1Z0-033)</b> lessons.<br><br><i>*View other requirements of the Oracle Certification by clicking here.</i>

Oracle Performance Tuning 1Z0-033

Introduction

1. Welcome
2. Prerequisites for this Course
3. About this Course pt. 1
4. About this Course pt. 2
5. Oracle Tuning Documentation

What is Performance Tuning?

1. Who Tunes and What Do They Tune?
2. The Different Stages of Tuning
3. Development/Testing/Production
4. Statistics and Performance Evaluation
5. Goal Oriented Tuning
6. Common Problems pt. 1
7. Common Problems pt. 2
8. Performance Versus Integrity
9. Oracle Enterprise Manager (OEM)

Performance Tools

1. The Alert Log
2. Process Trace Files
3. User Trace Files
4. Setting Up Tracing
5. TKPROF
6. Manually Collecting Statistics
7. STATSPACK and Collecting Statistics
8. Collecting Statistics in OEM
9. Introducing the Wait Event Interface
10. What to Tune

Tuning the Shared Pool

1. The Shared Pool
2. Shared Pool Efficiency
3. Shared Pool and Reserved Shared Pool
4. Library and Dictionary Cache Hit Ratios
5. Pinning Objects in the Shared Pool
6. The Large Pool
7. Caching Cursors
8. OEM and the Shared Pool
9. Performance Views

The Database Buffer Cache

1. Database Buffer Cache Efficiency
2. OLTP Versus Data Warehouse
3. Configure the Database Buffer Cache
4. Examine the Database Buffer Cache
5. The Buffer Cache Hit Ratio
6. Processes and the Buffer Cache
7. How to Tune Buffer Cache
8. How Else to Tune Buffer Cache
9. Free List Contention
10. Multiple Database Writers and Slaves
11. Monitor Buffer Cache and Subset Pools
12. OEM and the Database Buffer Cache
13. Performance and Metadata Views

Other Buffers

1. Tuning the Redo Log Buffer
2. Sizing the Redo Log Buffer
3. The Java Pool
4. Configuring Automatic PGA Memory
5. Configuring Manual PGA Memory
6. Tuning PGA Memory
7. Changing SGA Memory Online
8. SGA_MAX_SIZE
9. Changing Non SGA Memory Online
10. Performance Views

Tuning I/O: Physical File Structures

1. Datafiles/Tablespaces/Objects
2. Control Files and Recoverability
3. Distribution of Database Files
4. Dividing Data Across Tablespaces
5. Oracle Partitioning
6. Tablespace Storage Structures
7. Dictionary Managed Tablespaces
8. Locally Managed Tablespaces
9. Finding Tablespace Problems
10. Redo and Archive Log Files pt. 1
11. Redo and Archive Log Files pt. 2
12. Physical Structures in OEM
13. Performance Views
14. Metadata Views

Tuning I/O at the Block Level

1. Segments
2. Extents and Blocks
3. The High Water Mark
4. Reclaiming Unused Space
5. Row Chaining and Row Migration
6. Reorganizing Indexes
7. Detecting Problems With Indexes
8. Metadata Views

Tuning Sorts

1. How Does Sorting Work?
2. Which SQL Code Causes Sorting?
3. Demonstrating SQL Code Sorting
4. Sorting in Memory and on Disk
5. Analyzing Sorting
6. Minimizing Sorting
7. Sorting in OEM
8. Performance Views

Locking Issues

1. Locking Levels
2. Types of Contention pt. 1
3. Types of Contention pt. 2
4. Types of Locks
5. Detecting Lock Contention
6. How to Avoid Locking
7. Lock Monitoring and Removal in OEM
8. Performance and Metadata Views pt. 1
9. Performance and Metadata Views pt. 2

Latches and Wait Events

1. What is a Latch?
2. Different Types of Latches
3. Shared Pool Latches pt. 1
4. Shared Pool Latches pt. 2
5. Database Buffer Cache Latches pt. 1
6. Database Buffer Cache Latches pt. 2
7. Database Buffer Cache Latches pt. 3
8. Redo Log Latches
9. Session Connection Latches
10. Latch Spins, Misses and Sleeps
11. Latch Miss Rate
12. Latch Miss Total Time
13. What is a Wait Event?
14. Event Types and TIMED_STATISTICS
15. Idle Wait Events
16. Querying Significant Wait Events
17. Wait Events in an Active Database
18. Analyzing Significant Wait Events
19. The Wait Event Interface
20. The Wait Event Interface in OEM
21. Performance Views

Shared Servers and Dedicated Servers

1. Tuning Oracle Net Services
2. Shared Versus Dedicated Servers
3. Shared Server Configuration
4. Oracle Net Manager in OEM
5. Wait Events and Performance Views pt. 1
6. Wait Events and Performance Views pt. 2

Statistics

1. What are Statistics?
2. The Importance of Realistic Statistics
3. Statistics and OPTIMIZER_MODE
4. Statistics and CURSOR_SHARING
5. CBO Parameters pt. 1
6. CBO Parameters pt. 2
7. CBO Parameters pt. 3
8. Gathering Statistics using ANALYZE
9. Histograms and the ANALYZE Command
10. Gathering Statistics using DBMS_STATS
11. Gathering Statistics using OEM
12. What is STATSPACK?
13. Installing and Using STATSPACK
14. Metadata Views

Applications and Specialized Objects

1. Database Type Requirements
2. Oracle Special Storage Structures pt. 1
3. Oracle Special Storage Structures pt. 2
4. Applying Storage Structures
5. Creating BTree and Bitmap Indexes Syntax
6. Creating BTree and Bitmap Indexes Examples
7. Creating Index Organized Tables
8. Creating Clusters
9. Creating Materialized Views
10. Oracle Partitioning Methods Syntax
11. Oracle Partitioning Methods Examples pt. 1
12. Oracle Partitioning Methods Examples pt. 2
13. Metadata Views

SQL Code Tuning

1. Building Efficient SQL Code
2. Tuning Query Clauses pt. 1
3. Tuning Query Clauses pt. 2
4. Tuning Query Clauses pt. 3
5. Comparisons
6. Joins
7. Other Factors pt. 1
8. Other Factors pt. 2
9. SQL Code Execution Process
10. The Optimizer and What It Does
11. The Internal Machinations of the Optimizer pt. 1
12. The Internal Machinations of the Optimizer pt. 2
13. Hints
14. Types of Hints
15. Influence the Optimizer pt. 1
16. Influence the Optimizer pt. 2
17. Alter Table Scans
18. Alter Index Scans pt. 1
19. Alter Index Scans pt. 2
20. Modify Joins pt. 1
21. Modify Joins pt. 2
22. Perform SQL in Parallel
23. Change Queries and Subqueries
24. Finding Queries with Problems
25. EXPLAIN PLAN and the PLAN_TABLE
26. Autotrace in SQL*Plus
27. SQL Trace and TKPROF pt. 1
28. SQL Trace and TKPROF pt. 2
29. Finding Bad Queries in OEM
30. Plan Stability and Stored Outlines
31. Performance and Metadata Views

Resource Management

1. The Oracle Resource Manager
2. Resource Manager Components
3. Resource Plan Directives
4. Create Groups and Plans and Allocate
5. Metadata and Performance Views

The Operating System

1. Basic Operating System Tuning
2. Virtual Memory/Paging/Swapping
3. What Determines I/O Distribution?
4. Database Function and Methods of Distribution
5. Oracle Database File Types Distribution
6. Database Block Size
7. Resource Consumption in OEM

Conclusion

1. Wrapping it All Up!

Credits

1. About the Author

About The Training Provider: The Virtual Training Company

More Oracle Training from The Virtual Training Company

• Oracle Database Administration Fundamentals I
• Oracle Database Administration Fundamentals II
• Oracle Introduction to Oracle SQL and PL SQL