Developing Performance Tuning plan

Developing a long-term performance tuning plan for Oracle 19c requires a proactive, repeatable, and metrics-driven approach. Below is a comprehensive, long-term plan tailored for Oracle 19c, combining monitoring, baseline creation, capacity planning, automation, and regular review cycles.

1. 🔧 Establish a Performance Baseline
   • Capture current system metrics: Use AWR, ASH, and OEM Cloud Control to gather baseline values for:
   • Buffer cache hit ratio
   • Library cache hit ratio
   • Redo log wait times
   • Top SQL by elapsed time and resource usage
   • Snapshot Period: Capture performance during different workloads (peak and off-peak hours).
   • Tools:
   • DBMS_WORKLOAD_REPOSITORY.create_snapshot
   • awrrpt.sql
   • Oracle Enterprise Manager (OEM)
2. 🕵️‍♂️ Monitor Continuously and Proactively
   • Enable Automatic Monitoring:
   • Use AWR (licensed) or Statspack (if unlicensed)
   • Configure alert thresholds in OEM
   • Enable ASH for real-time session analytics
   • Track Trends:
   • Use V$SYSMETRIC_HISTORY, V$ACTIVE_SESSION_HISTORY, and V$EVENT_HISTOGRAM
3. 📊 Identify and Optimize Expensive SQL
   • Regularly analyze:
   • DBA_HIST_SQLSTAT
   • SQL Monitor Reports
   • SQL Plan Management (SPM) for plan stability
   • Implement SQL Baselines for consistent execution plans
   • Enable Adaptive Query Optimization and Automatic Indexing (optional, but useful with Oracle 19c)
4. 🧠 Memory and SGA Tuning
   • Use Automatic Memory Management (AMM) or ASMM
   • Review:
   • V$SGA_DYNAMIC_COMPONENTS
   • V$MEMORY_TARGET_ADVICE
   • Adjust PGA_AGGREGATE_TARGET and SGA_TARGET as needed
   • Check for memory pressure using V$MEMORY_RESIZE_OPS and V$MEMORY_DYNAMIC_COMPONENTS
5. 🧮 Storage and I/O Subsystem Tuning
   • Monitor:
   • V$IOSTAT_FILE, V$FILESTAT, V$TEMPFILE, V$TEMPSTAT
   • Move hot segments to faster storage
   • Enable Compression where applicable (e.g., OLTP compression)
   • Archive old data using Partitioning or Data Lifecycle Management
6. 📅 Schedule and Review AWR Reports
   • Automate AWR report generation using DBMS_SCHEDULER
   • Store and archive reports for monthly and quarterly reviews
   • Use compare reports (e.g., awrddrpt.sql) to identify regressions
7. 🚀 Tune for Growth: Capacity and Scalability Planning
   • Forecast growth using:
   • DBA_HIST_SEGMENT_SPACE_USAGE
   • DBA_HIST_SYSSTAT
   • Allocate tablespace proactively
   • Track session counts and CPU utilization using OEM or V$RESOURCE_LIMIT
8. 🔁 Establish a Regular Review Cycle

   Frequency	Tasks
   Daily	Check alerts, session waits, top SQL, locking issues
   Weekly	Review AWR snapshots, SQL tuning opportunities
   Monthly	Analyze growth trends, tune memory/storage parameters
   Quarterly	Forecast resource needs, review baselines, test new features
   Annually	Re-evaluate schema design, purge/archive data, upgrade plans

9. 📋 Document and Automate
   • Maintain a Performance Runbook with:
   • Tuning procedures
   • Baseline metrics
   • SLAs and thresholds
   • Use scripts or OEM jobs to automate:
   • Snapshot creation
   • Index rebuild analysis
   • Statistics collection
10. 🛡️ Stay Current and Test Changes
    • Regularly apply patches (RU/RUR) and test changes in a lower environment
    • Use SQL Performance Analyzer (SPA) to validate performance impact
    • Keep up with Oracle 19c features like:
    • Automatic Indexing
    • Real-Time Statistics
    • SQL Quarantine

While reactive tuning involves running a few Oracle scripts and interpreting the results, ongoing proactive tuning involves tracking changes in performance metrics over time and making planned changes to correct the situation. The Oracle DBA has many choices for accomplishing this goal.

1. Use third-party tools to collect Oracle performance data
2. Write extensions onto the Oracle utilities that dump the performance data into Oracle tables for later analysis.

Note:Oracle does not offer a standard Oracle utility to capture long-term (or historical) performance data.

• Tuning as an on-going process
  In either case, tuning is an ongoing process. A new, empty database may perform far differently than one that has been loaded with data. A system may have completely different performance patterns in the day than it does at night. In short, you must be able to track changes in Oracle performance and take appropriate actions that benefit the database as a whole.

There is another benefit to having a long-term performance plan. It is generally the job of the Oracle DBA to monitor the resource consumption of Oracle (disk, memory, and CPU) and notify the systems administrators in time for them to order enough of the needed resource. Many Oracle DBA performance planning systems include a forecasting function, where the DBA can predict future disk, memory, and CPU requirements based upon long-term growth patterns. In general, the Oracle DBA simply collects performance information over a long period of time to produce the most accurate forecast of performance. As you are collecting performance information, you must have ways to compare and analyze this data. The next lesson will introduce you to setting goals for your database's performance.