O'Reilly logo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Oracle: Performance Tuning (1Z0-033)

Video Description

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 Oracle Performance Tuning (1Z0-033) lessons.

Table of Contents

  1. Introduction
    1. Welcome 00:00:55
    2. Prerequisites for this Course 00:05:53
    3. About this Course pt. 1 00:04:28
    4. About this Course pt. 2 00:02:40
    5. Oracle Tuning Documentation 00:01:06
  2. What is Performance Tuning?
    1. Who Tunes and What Do They Tune? 00:02:07
    2. The Different Stages of Tuning 00:04:09
    3. Development/Testing/Production 00:04:02
    4. Statistics and Performance Evaluation 00:03:30
    5. Goal Oriented Tuning 00:05:06
    6. Common Problems pt. 1 00:03:16
    7. Common Problems pt. 2 00:04:37
    8. Performance Versus Integrity 00:01:53
    9. Oracle Enterprise Manager (OEM) 00:02:55
  3. Performance Tools
    1. The Alert Log 00:04:37
    2. Process Trace Files 00:04:25
    3. User Trace Files 00:01:33
    4. Setting Up Tracing 00:04:52
    5. TKPROF 00:03:02
    6. Manually Collecting Statistics 00:01:46
    7. STATSPACK and Collecting Statistics 00:01:33
    8. Collecting Statistics in OEM 00:04:22
    9. Introducing the Wait Event Interface 00:04:34
    10. What to Tune 00:05:12
  4. Tuning the Shared Pool
    1. The Shared Pool 00:02:52
    2. Shared Pool Efficiency 00:05:28
    3. Shared Pool and Reserved Shared Pool 00:04:14
    4. Library and Dictionary Cache Hit Ratios 00:04:53
    5. Pinning Objects in the Shared Pool 00:04:44
    6. The Large Pool 00:01:54
    7. Caching Cursors 00:02:18
    8. OEM and the Shared Pool 00:02:09
    9. Performance Views 00:00:57
  5. The Database Buffer Cache
    1. Database Buffer Cache Efficiency 00:03:19
    2. OLTP Versus Data Warehouse 00:02:56
    3. Configure the Database Buffer Cache 00:03:58
    4. Examine the Database Buffer Cache 00:03:20
    5. The Buffer Cache Hit Ratio 00:03:19
    6. Processes and the Buffer Cache 00:04:56
    7. How to Tune Buffer Cache 00:04:48
    8. How Else to Tune Buffer Cache 00:03:29
    9. Free List Contention 00:05:07
    10. Multiple Database Writers and Slaves 00:03:40
    11. Monitor Buffer Cache and Subset Pools 00:04:32
    12. OEM and the Database Buffer Cache 00:04:37
    13. Performance and Metadata Views 00:00:30
  6. Other Buffers
    1. Tuning the Redo Log Buffer 00:02:46
    2. Sizing the Redo Log Buffer 00:03:48
    3. The Java Pool 00:02:25
    4. Configuring Automatic PGA Memory 00:03:30
    5. Configuring Manual PGA Memory 00:04:11
    6. Tuning PGA Memory 00:03:06
    7. Changing SGA Memory Online 00:02:02
    8. SGA_MAX_SIZE 00:04:11
    9. Changing Non SGA Memory Online 00:02:13
    10. Performance Views 00:03:56
  7. Tuning I/O: Physical File Structures
    1. Datafiles/Tablespaces/Objects 00:02:16
    2. Control Files and Recoverability 00:02:47
    3. Distribution of Database Files 00:04:50
    4. Dividing Data Across Tablespaces 00:03:06
    5. Oracle Partitioning 00:01:16
    6. Tablespace Storage Structures 00:04:18
    7. Dictionary Managed Tablespaces 00:04:22
    8. Locally Managed Tablespaces 00:01:48
    9. Finding Tablespace Problems 00:05:18
    10. Redo and Archive Log Files pt. 1 00:03:56
    11. Redo and Archive Log Files pt. 2 00:02:56
    12. Physical Structures in OEM 00:01:32
    13. Performance Views 00:05:53
    14. Metadata Views 00:01:05
  8. Tuning I/O at the Block Level
    1. Segments 00:03:16
    2. Extents and Blocks 00:02:45
    3. The High Water Mark 00:01:26
    4. Reclaiming Unused Space 00:01:16
    5. Row Chaining and Row Migration 00:05:22
    6. Reorganizing Indexes 00:03:19
    7. Detecting Problems With Indexes 00:04:07
    8. Metadata Views 00:01:42
  9. Tuning Sorts
    1. How Does Sorting Work? 00:03:16
    2. Which SQL Code Causes Sorting? 00:01:53
    3. Demonstrating SQL Code Sorting 00:05:32
    4. Sorting in Memory and on Disk 00:02:25
    5. Analyzing Sorting 00:04:48
    6. Minimizing Sorting 00:03:04
    7. Sorting in OEM 00:04:55
    8. Performance Views 00:05:21
  10. Locking Issues
    1. Locking Levels 00:03:04
    2. Types of Contention pt. 1 00:02:31
    3. Types of Contention pt. 2 00:03:52
    4. Types of Locks 00:03:37
    5. Detecting Lock Contention 00:02:06
    6. How to Avoid Locking 00:04:48
    7. Lock Monitoring and Removal in OEM 00:03:39
    8. Performance and Metadata Views pt. 1 00:04:49
    9. Performance and Metadata Views pt. 2 00:01:43
  11. Latches and Wait Events
    1. What is a Latch? 00:01:31
    2. Different Types of Latches 00:03:30
    3. Shared Pool Latches pt. 1 00:03:18
    4. Shared Pool Latches pt. 2 00:02:35
    5. Database Buffer Cache Latches pt. 1 00:03:23
    6. Database Buffer Cache Latches pt. 2 00:03:12
    7. Database Buffer Cache Latches pt. 3 00:03:34
    8. Redo Log Latches 00:02:20
    9. Session Connection Latches 00:01:54
    10. Latch Spins, Misses and Sleeps 00:03:28
    11. Latch Miss Rate 00:02:14
    12. Latch Miss Total Time 00:03:30
    13. What is a Wait Event? 00:02:20
    14. Event Types and TIMED_STATISTICS 00:02:06
    15. Idle Wait Events 00:01:48
    16. Querying Significant Wait Events 00:03:40
    17. Wait Events in an Active Database 00:04:43
    18. Analyzing Significant Wait Events 00:05:17
    19. The Wait Event Interface 00:05:56
    20. The Wait Event Interface in OEM 00:05:23
    21. Performance Views 00:03:05
  12. Shared Servers and Dedicated Servers
    1. Tuning Oracle Net Services 00:02:33
    2. Shared Versus Dedicated Servers 00:02:14
    3. Shared Server Configuration 00:03:20
    4. Oracle Net Manager in OEM 00:01:23
    5. Wait Events and Performance Views pt. 1 00:04:13
    6. Wait Events and Performance Views pt. 2 00:05:24
  13. Statistics
    1. What are Statistics? 00:02:04
    2. The Importance of Realistic Statistics 00:03:40
    3. Statistics and OPTIMIZER_MODE 00:02:26
    4. Statistics and CURSOR_SHARING 00:03:47
    5. CBO Parameters pt. 1 00:03:10
    6. CBO Parameters pt. 2 00:04:05
    7. CBO Parameters pt. 3 00:02:48
    8. Gathering Statistics using ANALYZE 00:04:51
    9. Histograms and the ANALYZE Command 00:04:29
    10. Gathering Statistics using DBMS_STATS 00:03:52
    11. Gathering Statistics using OEM 00:02:23
    12. What is STATSPACK? 00:03:18
    13. Installing and Using STATSPACK 00:04:43
    14. Metadata Views 00:01:36
  14. Applications and Specialized Objects
    1. Database Type Requirements 00:04:01
    2. Oracle Special Storage Structures pt. 1 00:03:53
    3. Oracle Special Storage Structures pt. 2 00:04:50
    4. Applying Storage Structures 00:05:11
    5. Creating BTree and Bitmap Indexes Syntax 00:04:45
    6. Creating BTree and Bitmap Indexes Examples 00:02:49
    7. Creating Index Organized Tables 00:03:20
    8. Creating Clusters 00:02:14
    9. Creating Materialized Views 00:03:39
    10. Oracle Partitioning Methods Syntax 00:04:24
    11. Oracle Partitioning Methods Examples pt. 1 00:05:10
    12. Oracle Partitioning Methods Examples pt. 2 00:02:25
    13. Metadata Views 00:02:05
  15. SQL Code Tuning
    1. Building Efficient SQL Code 00:01:56
    2. Tuning Query Clauses pt. 1 00:04:32
    3. Tuning Query Clauses pt. 2 00:03:33
    4. Tuning Query Clauses pt. 3 00:04:46
    5. Comparisons 00:03:09
    6. Joins 00:05:09
    7. Other Factors pt. 1 00:03:57
    8. Other Factors pt. 2 00:03:41
    9. SQL Code Execution Process 00:03:50
    10. The Optimizer and What It Does 00:03:02
    11. The Internal Machinations of the Optimizer pt. 1 00:04:16
    12. The Internal Machinations of the Optimizer pt. 2 00:04:29
    13. Hints 00:04:58
    14. Types of Hints 00:01:36
    15. Influence the Optimizer pt. 1 00:03:50
    16. Influence the Optimizer pt. 2 00:04:55
    17. Alter Table Scans 00:01:05
    18. Alter Index Scans pt. 1 00:03:57
    19. Alter Index Scans pt. 2 00:04:01
    20. Modify Joins pt. 1 00:03:54
    21. Modify Joins pt. 2 00:05:01
    22. Perform SQL in Parallel 00:02:40
    23. Change Queries and Subqueries 00:02:02
    24. Finding Queries with Problems 00:00:56
    25. EXPLAIN PLAN and the PLAN_TABLE 00:04:49
    26. Autotrace in SQL*Plus 00:00:27
    27. SQL Trace and TKPROF pt. 1 00:03:25
    28. SQL Trace and TKPROF pt. 2 00:03:41
    29. Finding Bad Queries in OEM 00:01:59
    30. Plan Stability and Stored Outlines 00:04:13
    31. Performance and Metadata Views 00:02:22
  16. Resource Management
    1. The Oracle Resource Manager 00:00:46
    2. Resource Manager Components 00:01:26
    3. Resource Plan Directives 00:02:40
    4. Create Groups and Plans and Allocate 00:02:46
    5. Metadata and Performance Views 00:01:02
  17. The Operating System
    1. Basic Operating System Tuning 00:04:33
    2. Virtual Memory/Paging/Swapping 00:03:35
    3. What Determines I/O Distribution? 00:01:58
    4. Database Function and Methods of Distribution 00:01:57
    5. Oracle Database File Types Distribution 00:04:59
    6. Database Block Size 00:03:20
    7. Resource Consumption in OEM 00:01:29
  18. Conclusion
    1. Wrapping it All Up! 00:04:45
  19. Credits
    1. About the Author 00:01:14