Call: 080-42275527 / +91-9916056387

Performance management

This section explores the methods used for performance management in Oracle and shows tips and scripts for monitoring all components of any Oracle database. You will also learn the proper action to take when any area of Oracle becomes a bottleneck.

 

5-1: BOTTLENECK PERFORMANCE ANALYSIS

  • Drill-down into AWR reports
  • Top-5 timed events
  • External Server Bottlenecks (Network, I/O, RAM, CPU)
  • Network troubleshooting

 

5-2: INSTANCE TUNING

  • Changing init.ora optimizer parameters (index_optimizer_cost_adj, optimizer_mode)
  • Managing region parameters (shared_pool_size, db_cache_size)
  • Understanding instance contention (e.g. Buffer busy waits, library cache contention)

 

5-3: SQL AND CBO BEHAVIOR

  • Introduction to cost-based optimization
  • Changing the default optimizer modes
  • Optimizer parameters
  • Dynamic sampling
  • Collecting table and index statistics (dbms_stats)
  • Using column histograms and skew only

 

5-4: TRACING SQL EXECUTION

  • Using EXPLAIN PLAN
  • Using “set autotrace”
  • Interpreting EXPLAIN PLAN Output
  • Using TKPROF / SQL*Trace

 

5-5: SQL EXECUTION INTERNALS

  • Review of Basic joining methods
  • Merge join
  • Hash Join
  • Nested Loop join
  • Advanced SQL operators
  • Between operator

 

5-6: SQL TUNING

  • Using hints to improve SQL performance
  • Using parallel query to improve performance
  • SQL reusability within the library cache
  • Table high-water mark
  • Table striping and table partitions
  • Using indexes to improve performance
  • Identifying full-table scans
  • Re-writing SQL queries
  • Tuning sub-queries
Scroll Up