The Database Performance course discusses main database server performance management problems. Topics include database server performance tuning, data and statement caching solutions, dedicated physical and logical data structures (indexes, partitioned tables, materialized views), performance evaluation tools and standards (TPC).

  • Introduction to Oracle 12c Architecture: Oracle 12c Internal Architecture, starting and stopping DBMS components, instance background processes, SGA buffers, how SQL statements are processed, how compiled SQL code is executed, how Library Cache works, sizing Library Cache, how Buffer Cache works, sizing Buffer Cache, database files
  • Performance Monitoring Tools: dynamic performance views, Statspack, SQL_TRACE, SQL*Plus Autotrace.
  • Cost-based Query Optimization: SQL statement processing phases, SQL query optimization, how CBO works, estimating costs of execution plans, statistical models of tables and indexes, join methods, optimizer hints
  • Improving Performance with Data Structures: index structures (B*tres, bitmap, function-based, index-organized tables), materialized views, query rewrite, physical data structures (segments, extents, blocks), record migration, High-Water Mark
  • Improving Performance with Caching: tuning goals and techniques, buffer cache performance indicators, measuring cache hit ratio, using multiple buffer pools
  • Benchmarking Standards: TPC Benchmarking, history of benchmarks, TPC-C benchmark, TPC-H and TPC-R benchmarks.

© 2019 Maciej Zakrzewicz