Ongoing Performance Tuning

In addition to monitoring Oracle for long-term tuning, you may also want to periodically monitor the database and report on exceptional conditions. Understanding the distinction between 1) immediate remedy problems and 2) non-immediate remedy problems is critical for maintaining high availability while planning for necessary downtime or maintenance windows.

1. ✅ 1. Immediate Remedy Problems
   • These are issues you can detect and fix on a live Oracle 19c system without requiring a restart or downtime. They typically involve SQL tuning, parameter adjustments, resource reallocation, or user activity control.
   🔧 Examples:
   • Blocking Sessions
     • → You can kill or resolve the blocking session using ALTER SYSTEM KILL SESSION.
   • High CPU Usage by a Session
     • → Identify the session (V$SESSION, V$ACTIVE_SESSION_HISTORY) and tune the SQL or terminate it.
   • Unusable Indexes
     • → Rebuild or drop the index online.
   • Statistics Outdated
     • → Gather optimizer stats immediately using DBMS_STATS.
   • Temp Tablespace Full
     • → Add another tempfile or increase its size dynamically.
   • Redo Log Space Waits
     • → Add more redo log groups or increase log file size online.
   • High Shared Pool Fragmentation
     • → Flush shared pool or resize it dynamically with ALTER SYSTEM.
   • Session-Related Errors
     • → Tune specific queries or resolve PL/SQL errors on the fly.
2. 🔁 2. Non-Immediate Remedy Problems
   • These are deeper or structural issues that require a planned bounce (shutdown/startup) of the database instance or other components. These problems can’t be resolved without impacting availability, and are often addressed during scheduled maintenance.
   ⏳ Examples:
   • Modifying Static Initialization Parameters
     • → Changes like DB_BLOCK_SIZE, PROCESSES, or DB_NAME require a database restart.
   • Changing SGA_TARGET/SGA_MAX_SIZE (if dynamic memory is already at max)
     • → Reducing or increasing SGA_MAX_SIZE requires a shutdown/startup.
   • Patching/Upgrading Oracle Binaries (RU/RUR/PSU)
     • → Requires downtime to apply and relink binaries.
   • Corruption Recovery of SYSTEM Tablespace
     • → May require instance shutdown and restore/recovery procedures.
   • OS-Level Kernel Parameter Changes
     • → Kernel changes (e.g., SHMMAX) often require server reboot and DB restart.
   • Undone Background Process Failures
     • → A crash of processes like LGWR, DBWn, or CKPT may require instance restart.
   • Redo Log File Size Decrease
     • → You must drop and recreate redo logs, which requires mount mode or downtime.

🧠 Summary Table

Problem Type	Action Required	Downtime Needed	Examples
Immediate Remedy	Tune/fix live system	❌ No	Kill session, rebuild index, resize tempfile
Non-Immediate Remedy	Restart system/db	✅ Yes	Static param changes, patching, redo size reduction

Here we find that there are two areas of Oracle performance.

1. Immediate remedy problems: Those conditions that we can immediately address
2. Non-immediate remedy problems: Those condition that we cannot fix until we have an opportunity to stop and re-start , or bounce the Oracle database

Below are some common immediate remedy and non-immediate remedy problems.

Immediate Fix Exceptions

Condition	Action
Table is approaching maximum extents	alter table maxextents unlimited
Table cannot take another extent	alter table NEXT parameter
A tablespace is > 95% full	add a data file to the tablespace
An unnecessary full-table scan	add a new index, or add an SQL hint
Speed-up a necessary full-table scan	invoke parallel query

Problems that require stopping and re-starting the Oracle instance

Condition	Action
Buffer hit ratio is low	increase the init.ora db_block_buffers parameter
Too many disk sorts	increase the sort_area_size init.ora parameter
Too many redo log requests	requires increasing the log_buffer init.ora parameter
Alleviate a disk I/O bottleneck	move an Oracle data file to another disk

Note that even though conditions such as a table failing to extend, or a tablespace becoming full (immediate remedy problems) are not considered Oracle tuning topics, they have a direct impact on the performance of the Oracle database because they will cause severe response time slowdowns.

If you can only bounce your database once each week, then you only have one opportunity to change the init.ora parameters to improve tuning. Hence, problems that cannot be fixed immediately do not require immediate reporting. Most Oracle DBAs will check their performance and tuning database the day prior to the maintenance window (i.e. bouncing Oracle) to see what init.ora parameters may be changed.
The next lesson concludes this module.