Learn How to Solve Critical Performance Problems with Step by Step Approach!

What you’ll learn

Use this as your checklist. This page is intentionally “study-friendly”: you can tick items as you learn.

• Learn How to Solve Critical Performance Problems with Step by Step Approach!Use DB time first, then drill into top SQL, then fix safely.
• Learn Advanced Indexing Techniques for Ultimate Database Performance!Composite indexes, function-based indexes, bitmap (DW), invisible-index testing.
• Learn How to Use Various Performance Tuning Tools for In-Depth Assistance!AWR/ADDM, SQL Monitor, DBMS_XPLAN, SQL Trace + TKPROF.
• Maximize the Query Performance by Using Advanced Tuning Tecniques!Rewrite SQL, fix stats, add access paths, stabilize plans.
• Learn Oracle Database Architecture by “Tuning” aspects.DB time, waits, memory areas (SGA/PGA), and how SQL consumes them.
• Identify and Optimize the Performance of poorly performing (bad) SQLs.Top SQL → plan → root cause → verify.
• Understand SQL Tuning TerminologiesDB time, AAS, wait classes, cardinality, selectivity, access paths.
• Learn How to Create “Better” QueriesRemove unnecessary work; keep predicates sargable; avoid cartesian joins.
• Learn How to Prioritize your SQL Tuning Efforts.Fix what contributes most DB time and/or blocks others.
• Learn the Details of How an SQL Code is InterpretedParsing → optimization → row source generation → execution.
• Learn Useful Hints to improve Performance of your QueriesHints as experiments, not permanent band-aids.
• Learn & Improve How to Use Oracle Optimizer and Execution Plans WellActual plan, E-Rows vs A-Rows, plan stability.
• Various Tips and Tricks to make the Oracle SQL queries run faster.Binds, stats, indexing, plan regression prevention.
• Oracle Performance Tuning on both Oracle11g and Oracle12c Environment.Technique still applies; tooling and support differ. Prefer modern LTS when possible.
• Learn Efficient Schema DesignDesign for access paths and stable cardinality assumptions.
• Weekly Quizzes & Assessment TestsIncluded near the end for self-check.
• Working materials and always up to date!Last updated: 2026-04-10. References link to primary docs.

The step-by-step workflow

Use this during incidents. The goal is to avoid “random tuning” and always keep evidence.

1. Define the goal and scope
   Is it response time or throughput? Is it system-wide, service-wide, or only one SQL statement? Write down the target (example: “p95 from 2.4s → 500ms”).
2. Measure DB time first
   DB time is your master metric (CPU + non-idle waits for active sessions). If DB time spikes, you must identify which wait class/event or CPU dominates.
3. Find the biggest contributors
   Identify top SQL (by DB time / CPU / elapsed time) and top wait events during the “bad window”. Fixing the top 1–5 SQLs often beats micro-optimizing everything.
4. Pick the right drill tool
   Now: SQL Monitor / Real-Time Monitoring. History: AWR/ADDM. No packs: SQL Trace + TKPROF.
5. Confirm the actual plan
   Avoid EXPLAIN PLAN-only tuning when possible. Prefer cursor-cache plan with row counts: compare E-Rows vs A-Rows per step.
6. Diagnose root cause by category
   Common categories: inefficient SQL shape, missing/stale stats, missing access path, plan regression, parsing storms, locking.
7. Apply the smallest safe fix
   Prefer: fix stats → add index (if needed) → rewrite SQL → plan stability (SPM/profile/patch) → hints (last resort).
8. Validate and prevent regression
   Re-measure DB time and statement time. If plan changed or improved, consider plan baselines for stability.

Fast triage cheat sheet

Execution plans (done right)

Execution plans are your “X-ray”. But you must prefer actual execution statistics over purely explained plans.

Copy‑paste starter: show plan with actual row counts (when available)

Copy-- 1) Run the statement with plan statistics enabled (example)
-- You may need: ALTER SESSION SET statistics_level = ALL;

SELECT /*+ gather_plan_statistics */ ...
FROM   ...
WHERE  ...;

-- 2) Display plan from cursor cache with actual stats
SELECT * FROM TABLE(
  DBMS_XPLAN.DISPLAY_CURSOR(
    NULL, NULL, 'TYPICAL +ALLSTATS LAST'
  )
);

SQL Rewriting & Optimization

Sometimes the best way to fix a performance issue is to stop relying on the optimizer to rescue bad code, and rewrite the query to remove anti-patterns.

Advanced indexing techniques

Index type selection (quick rules)

• B-tree: Most OLTP workloads (high concurrency, selective predicates).
• Bitmap: Mainly data warehousing / low-cardinality columns, but can be risky with OLTP DML concurrency.
• Function-based: When your predicate uses an expression (e.g., UPPER(col)).

Function‑based index example

Copy-- If your queries do: WHERE UPPER(email) = :email
-- then a normal index on email may not be used.
-- A function-based index can help:

CREATE INDEX users_email_u_idx ON users (UPPER(email));

Optimizer statistics

Automatic optimizer statistics collection (concept)

In modern Oracle versions, automatic statistics tasks are designed to refresh objects whose stats are missing or stale. That reduces risk of bad plans from outdated inputs.

DBMS_STATS: a safe “starter” command (table-level)

Copy-- Example: gather stats for one table (adjust options for your environment)
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname  => 'APP',
    tabname  => 'ORDERS',
    cascade  => TRUE
  );
END;
/

Cursor sharing & bind variables

Cursor sharing can improve performance dramatically by reducing hard parses and enabling plan reuse, but it requires discipline in SQL text consistency and bind usage.

Developer checklist

• Use bind variables in OLTP queries (avoid literal-per-request SQL)
• Standardize SQL formatting (spacing/case/comments) across the codebase
• Do not use CURSOR_SHARING=FORCE as a permanent production fix
• Minimize session-level optimizer changes that reduce cursor reuse

Plan stability tools

When a plan change causes a regression, you often need a controlled mechanism that keeps performance stable while the environment evolves.

Toolbelt

SQL Monitor report: generate HTML

Copy-- Example (requires proper licensing / pack enablement):
-- Generate an SQL Monitor report as HTML for a SQL_ID

SET PAGESIZE 0
SET LINESIZE 32767
SET LONG 5000000
SET LONGCHUNKSIZE 5000000

SPOOL sqlmon_report.html
SELECT DBMS_SQL_MONITOR.REPORT_SQL_MONITOR(
  sql_id => 'your_sql_id_here',
  type => 'HTML',
  report_level => 'ALL'
)
FROM dual;
SPOOL OFF

SQL Trace + TKPROF (no-pack fallback)

Copy-- Session-level SQL trace (example)
EXEC DBMS_SESSION.SET_SQL_TRACE(TRUE);

-- Run your workload here...

EXEC DBMS_SESSION.SET_SQL_TRACE(FALSE);

-- Then run TKPROF on the generated trace file (OS command)
-- tkprof input.trc output.txt sort=exeela

Practice labs

Weekly quizzes

References (primary starting points)

These are the core docs that back the methodology in this post.

• Oracle Lifetime Support Policy — Coverage for Technology (support dates table)
• Oracle Database Licensing Information User Manual (Diagnostics/Tuning pack requirements)
• Oracle SQL Tuning Guide (26ai) — methodology, tools, optimizer controls
• AWR documentation (what it collects and why)
• Monitoring Database Operations / SQL Monitor documentation
• Execution plans documentation (EXPLAIN PLAN vs actual plans, DBMS_XPLAN)
• Indexes & index-organized tables documentation