Oracle Performance Tuning
Course Curriculum
Introduction to Oracle Performance Tuning
- The top-down approach to tuning
- The history of Oracle tuning
- A review of the Oracle architecture
- The goals of Oracle tuning
- Overview of SQL tuning
- Oracle network bottlenecks
- Oracle RAM usage and bottlenecks
- Oracle CPU usage and bottlenecks
- Oracle disk I/O overview
- Monitoring server (sat, vmstat, top, glance)
- Movement toward server consolidation
- Exercise: Monitor CPU
Oracle disk I/O tuning
- History of DASD
- Understanding disk I/O
- Monitoring disk I/O (AWR, dba_hist_filestatxs)
- Sorted hash cluster tables
- Disk I/O waits
- Oracle data buffer internals (db_cache_size)
- Caching data blocks in the PGA (parallel full-table scans)
- Blocksize and I/O performance
- Exercise – view tablespace fragmentation
Oracle CPU Tuning
- Finding your cpu_count
- Measuring CPU usage with vmstat
- Oracle CPU usage
- Using processor affinity
- _optimizer_cost_model=cpu
- Parallel query and CPU
- Exercise: Timing a parallel query
Oracle RAM tuning
- all_rows and first_rows optimization and RAM
- Multiple data buffers
- Multiple blocksizes
- Measuring RAM paging
- Oracle SGA RAM
- Oracle PGA RAM
- Using the KEEP pool
- Automatic Memory Management (AMM)
- Exercise: Use the KEEP pool
Oracle Instance Tuning
- Global tuning techniques
- Display possible unnecessary large-table full-table scans
- Library cache tuning
- Finding missing indexes
- Tuning the database writer
- instance parameter tuning
- Exercise – change optimizer parameters
Oracle SQL Tuning Introduction
- SQL tuning hierarchy
- SQL tuning goals
- optimizer modes
- query re-write
- cursor_sharing
- viewing execution plans (autotrace)
- verifying optimal join techniques
- parallelizing SQL execution
- dynamic statistics
- Exercise – count times when a table is invoked
- 12c new SQL features
- function-based virtual column
- adaptive execution plans
- 12c new optimizer metadata collection features
AWR and ASH
- AWR vs STATSPACK
- Reading an AWR report
- AWR report analyzer
- Basics of predictive modeling for performance
- Finding repeating signatures of data
Oracle RAC Tuning
- RAC architecture tuning
- Tuning disk I/O on RAC
- Tuning the cache fusion layer on RAC
Oracle Tuning Pack
- creating SQL Profiles
- Recommending new indexes
- “automatic” SQL Tuning
- SQL Performance Advisor (SPA)
- recommending object reorganization
Oracle Diagnostic pack
- AWR and ASH
- Real time SQL monitoring
- Comparing performance periods
- Oracle monitoring & alert notifications (setting thresholds)
Oracle Tracing
- 10046 tracing
- TKPROF tracing
- Trace analyzer
- Autotrace
Introduction to SQL Tuning
- Pre-SQL database communications
- The evolution of SQL
- Declarative languages
- Exercise – Show permutations of the same query
Optimizing Oracle SQL
- Parsing a SQL statement
- Semantic parsing
- Hard parse vs. soft parse
- Decision tree generation
- Making SQL reentrant
- Exercise – generate a syntax and semantic error
Optimizer statistics
- Types of metadata
- Index metadata
- Segment metadata
- Server metadata
- System stats
- Exercise run dbms_stats
Exposing & Reading Execution Plans
- autotrace
- SQL*Trace
- Determine the steps of an execution plan
- Exercise: determine the sequence of steps in a complex execution plan
altering execution plans
- Changing CBO statistics
- Changing init.ora SGA parameters
- Changing optimizer parameters
- Using hints
- Using the opt_param hint
Oracle indexing
- Overview of Oracle indexing
- bitmap indexes
- bitmap join indexes
- guidelines for creating indexes
- function-based indexes (FBI)
- index usage monitoring
- fishing in the library cache
- optimizing indexes
- The transient nature of indexing
- database modality of workloads
- SQL Workload analysis
- Identifying EOW, EOM and EOY SQL.
- Creating on-demand indexing
- oracle text indexes
- Exercise – create an index on the pubs database
Boolean/Built-in functions
- Boolean evaluation
- Decode and case
- Basics of BIFs
- Function-based indexing
- Creating a custom BIF using PL/SQL
- The effect of BIFs on SQL execution
- Exercise: Write a custom built-in function
Tuning Oracle sorting
- Basics of sorting
- RAM usage in a hash join
- RAM for indexes
- Use indexes to avoid sorts
- Sorts in execution plans
- Sorts in semi joins
- Use autotrace to see sorts
- What causes sorts?
- PGA and sorting
- super-sizing your PGA to avoid disk sorts
- sorted hash clusters
- Exercise: avoid sort by retrieving data in pre-sorted order
- Exercise: Use an “index” hint to avoid sort
Tuning Oracle subqueries
- Subquery overhead
- Scalar subqueries
- In-Line views
- Correlated vs non-correlated subqueries
- WHERE clause query re-write
- Tuning IN and NOT IN subqueries
- Tuning EXISTS subqueries
- Tuning correlated subqueries
- Using subquery hints (push_subq)
- Avoiding subqueries
- Non-correlated subqueries and NOT IN clause
- Automatic rewrite of EXISTS subqueries
- The merge_aj hint
- The anti-join hints
- Exercise: Tune a subquery
Tuning with temp objects (materialized views, global temporary tables)
- Tuning with temporary tables
- Oracle global temporary tables (GTT)
- The Oracle SQL WITH clause
- Exercise: Test query rewritten to use temp tables
Tuning full-table scans
- Basics of file multiblock I/O
- Deciding when to invoke full-table scan
- RAM caching in the SGA
- Automating table caching (KEEP Pool)
- Solid State Disks and full-table scans
- Using plan9i.sql to find full-table scans
- Tracking full-scans over time with AWR
- Exercise: Find high disk read SQL
Tuning parallel query
- Parallel query
- Parallel DML
- Parallel DDL
- Parallel utilities
- Setting optimal parallel degree
- RAC and parallel query
- Parallel query tuning and buffering
- Parallel query hints
- Exercise: Invoke parallel query
Oracle optimizer goals
- Roadblocks to SQL tuning
- optimizer_index_cost_adj
- Global changes and SQL tuning (parameters, statistics, reorganizations)
- Using the rule-based optimizer as a testing tool
- Configuring the optimizer
- Changing optimizer_mode
- Governors for the optimizer
- Using hidden parameters
- Using the opt_param hint
- gathering workload statistics
- optimizer_cost_model
Upcoming Batches Schedule
| 18th May 2020 | Regular | 7.30 am To 9.30 am |
| 23rd May 2020 | Weekend | 10.30 am To 02.30 pm |
| 25th May 2020 | Regular | 7.30 am To 9.30 am |
| 30th May 2020 | Weekend | 10.30 am To 02.30pm |