Oracle Optimizer
Deep Dive with Banking Examples
Zaheer Abbas Mitaigiri
Lead Oracle Database Consultant
What is the Oracle Optimizer?
• The Oracle Optimizer is the brain behind SQL execution in Oracle Database. It's a
cost-based optimizer (CBO) that evaluates multiple ways to execute a SQL
statement (execution plans) and selects the one it calculates will be most
efficient.
Key Components:
1. Query Transformer: Rewrites queries for optimization
2. Estimator: Calculates selectivity, cardinality, and cost
3. Plan Generator: Explores alternative execution plans
How Optimizer Environment Impacts Performance
The optimizer environment consists of all parameters and statistics that influence
the optimizer's decisions. Changes can dramatically impact performance:
Banking Scenario Example:
• After optimizer_index_cost_adj
• Before Change: changed from 10 to 100:
SELECT * FROM transactions
SELECT * FROM transactions
WHERE account_number = '12345678' WHERE account_number = '12345678'
AND transaction_date > SYSDATE - 30; AND transaction_date > SYSDATE - 30;
Note: Same query now does full scan
Note: Fast query (15ms) using index on (4500ms)
account_number.
Critical Optimizer Parameters in Banking
Parameter Default Banking Impact Recommended Setting
optimizer_mode ALL_ROWS Batch vs real-time FIRST_ROWS for OLTP
processing
optimizer_index_cost_adj 100 Index vs full scan 10-50 for OLTP
preference
db_file_multiblock_read_count 128 Full scan speed 32 for SSD storage
statistics_level TYPICAL Statistics granularity ALL for data warehouses
optimizer_adaptive_features TRUE Adaptive plans FALSE for stability
Oracle Version Evolution
Oracle 11g Features: Oracle 12c Features:
• SQL Plan Management: Automatic • Adaptive Plans: Mid-execution plan
plan capture changes
• Adaptive Cursor Sharing: Different • SQL Plan Directives: Persistent
plans for different bind values optimizer "learnings"
• Cardinality Feedback: Runtime • In-Memory Column Store: Analytics
statistics adjustment acceleration
Banking Impact: Reduced plan Banking Use Case: Real-time fraud
regressions for ATM transaction detection improved by 40% with
queries in-memory
Oracle Version Evolution
Oracle 19c Features: Oracle 21c/23ai Features:
• Real-Time Statistics: No more stale • Machine Learning Optimizer: AI-
stats powered plan selection
• SQL Quarantine: Blocking bad plans • Persistent Memory Store: Faster plan
• Automatic Indexing: AI-driven index recall
creation • Blockchain Table Optimization: For
audit trails
Example: A European bank
reduced batch processing time by Use Case: Suspicious transaction
65% with auto-indexing pattern detection 3x faster
Diagnosing Optimizer Issues in Banking
1. Identify Plan Changes:
3. Check Statistics Freshness:
SELECT sql_id, plan_hash_value, executions,
elapsed_time/executions/1000 avg_ms
FROM v$sql
WHERE sql_text LIKE '%FROM transactions%' SELECT table_name, last_analyzed
ORDER BY last_active_time DESC; FROM dba_tables
2. Compare Optimizer Environments: WHERE owner = 'BANKING_APP';
SELECT name, value
FROM v$sql_optimizer_env
WHERE sql_id = '8g7h2k9m4n5p3'
ORDER BY name;
Oracle Optimizer Environment Step 1: Identify the problematic query
Analysis in a Banking System: Find the slow-running transaction analysis query
Banking Scenario: Customer SELECT sql_id, plan_hash_value, executions,
Transaction Analysis Query elapsed_time/executions/1000 avg_ms,
Degradation sql_text
Situation: A critical end-of-day FROM v$sql
report that analyzes high-value WHERE sql_text LIKE '%FROM transactions%'
transactions (over $10,000) AND sql_text LIKE '%WHERE amount > 10000%'
suddenly takes 4 hours instead of its
usual 15 minutes. The report is used ORDER BY elapsed_time DESC;
by the bank's fraud detection team.
Assuming we find SQL_ID 8g7h2k9m4n5p3 with two
different plan hash values.
Oracle Optimizer Environment Analysis in a Banking System………. (Continued)
Step 2: Check execution statistics
• Compare execution statistics for the SQL_ID
SELECT
plan_hash_value,
optimizer_env_hash_value,
executions,
elapsed_time/executions/1000 avg_ms,
buffer_gets/executions avg_lio,
disk_reads/executions avg_pio
FROM v$sql
WHERE sql_id = '8g7h2k9m4n5p3';
Oracle Optimizer Environment Analysis in a Banking System………. (Continued)
Step 3: Analyze optimizer environment SELECT name, value
differences FROM dba_hist_optimizer_env_details
WHERE optimizer_env_hash_value IN (SELECT * FROM good_env)
• Find optimizer parameter differences between good and INTERSECT
bad plans SELECT name, value
FROM dba_hist_optimizer_env_details
WITH WHERE optimizer_env_hash_value IN (SELECT * FROM bad_env)
good_env AS ( SELECT
SELECT optimizer_env_hash_value d.optimizer_env_hash_value,
FROM v$sql d.name,
WHERE sql_id = '8g7h2k9m4n5p3' AND avg_elapsed_time < 20000 d.value
), FROM dba_hist_optimizer_env_details d
bad_env AS ( WHERE d.optimizer_env_hash_value IN (
SELECT optimizer_env_hash_value SELECT optimizer_env_hash_value FROM v$sql WHERE sql_id =
'8g7h2k9m4n5p3'
FROM v$sql
)
WHERE sql_id = '8g7h2k9m4n5p3' AND avg_elapsed_time > 10000000
AND (d.name, d.value) NOT IN (SELECT name, value FROM
), common_params)
common_params AS ( ORDER BY d.name, d.optimizer_env_hash_value;
Potential Banking-Specific Findings:
1. Parameter Changes:
• optimizer_index_cost_adj increased from
50 to 200
• db_file_multiblock_read_count
decreased from 32 to 8 3. Index Problems:
• optimizer_mode changed from
FIRST_ROWS to ALL_ROWS • Partitioned index on TRANSACTION_DATE
became unusable
2. Statistics Issues: • Bitmap index on BRANCH_ID was
dropped
• Statistics on the TRANSACTIONS table are
stale
• Histograms missing on the AMOUNT
column
Banking-Specific Performance Fixes
1. Restore Optimal Parameters: 2. Implement SQL Plan Baseline:
• Create a logon trigger for reporting users • Capture the known good plan
CREATE OR REPLACE TRIGGER set_reporting_params DECLARE
AFTER LOGON ON DATABASE l_plans PLS_INTEGER;
WHEN (USER IN ('FRAUD_ANALYSIS', BEGIN
'EOD_REPORTING'))
l_plans :=
BEGIN DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
EXECUTE IMMEDIATE 'ALTER SESSION SET sql_id => '8g7h2k9m4n5p3',
optimizer_index_cost_adj=50';
plan_hash_value => 2840364952); -- Good plan
EXECUTE IMMEDIATE 'ALTER SESSION SET hash value
db_file_multiblock_read_count=32';
END;
EXECUTE IMMEDIATE 'ALTER SESSION SET
optimizer_mode=FIRST_ROWS'; /
END;
/
Banking-Specific Performance Fixes ….. Contd
4. Materialized View for Daily Aggregations:
3. Banking-Specific Index Optimization:
• Create pre-aggregated data for reports
• Create a function-based index for high-value transactions
CREATE MATERIALIZED VIEW mv_daily_high_value
CREATE INDEX idx_high_value_trans ON transactions (
REFRESH COMPLETE ON DEMAND
CASE WHEN amount > 10000 THEN 1 ELSE NULL END)
ENABLE QUERY REWRITE
TABLESPACE reporting_idx;
AS
SELECT
• Partitioned index for date ranges
transaction_date,
CREATE INDEX idx_trans_date_amount ON transactions(transaction_date,
branch_id,
amount) customer_id,
LOCAL TABLESPACE reporting_idx
COUNT(*) transaction_count,
COMPRESS 1;
SUM(amount) total_amount,
MAX(amount) max_transaction
FROM transactions
WHERE amount > 10000
GROUP BY transaction_date, branch_id, customer_id;
Monitoring Solution for Banking Environment
Banking-Specific Performance Fixes .. Contd
• Create a dashboard view for transaction query
5. Resource Manager Plan: performance
CREATE OR REPLACE VIEW v_fraud_query_perf AS
SELECT
• Prioritize fraud detection queries s.sql_id,
s.plan_hash_value,
BEGIN TO_CHAR(h.begin_interval_time, 'YYYY-MM-DD HH24:MI')
snapshot_time,
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA(); s.executions_delta,
s.elapsed_time_delta/NULLIF(s.executions_delta,0)/1000
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP( avg_ms,
consumer_group => 'FRAUD_DETECTION', s.buffer_gets_delta/NULLIF(s.executions_delta,0) avg_lio,
e.name parameter_name,
comment => 'Fraud analysis queries');
e.value parameter_value
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE( FROM
plan => 'BANKING_PRIORITY', dba_hist_sqlstat s,
dba_hist_snapshot h,
group_or_subplan => 'FRAUD_DETECTION', dba_hist_optimizer_env_details e
comment => 'Fraud detection priority', WHERE
mgmt_p1 => 80); s.snap_id = h.snap_id AND
s.optimizer_env_hash_value = e.optimizer_env_hash_value
DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA(); AND
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA(); s.sql_id = '8g7h2k9m4n5p3'
END; ORDER BY
h.begin_interval_time DESC;
/
Root Cause Analysis
The investigation revealed: The combination of these factors
caused the optimizer to:
• Parameter Changes: A database
upgrade changed default optimizer • Choose full table scans instead of
parameters index access
• Statistics Issue: Automatic stats job • Use less efficient join methods
failed during a large data load • Incorrectly estimate cardinality for
• Index Problem: A maintenance high-value transactions.
window dropped what was
considered a "redundant" index
By systematically analyzing the optimizer environment changes and implementing banking-
specific optimizations, the query performance was restored to under 10 minutes, with additional
monitoring to prevent future regressions.
