{"id":5802,"date":"2026-07-19T21:50:07","date_gmt":"2026-07-19T16:20:07","guid":{"rendered":"https:\/\/w3buddy.com\/?p=5802"},"modified":"2026-07-19T21:50:09","modified_gmt":"2026-07-19T16:20:09","slug":"oracle-performance-tuning-on-linux","status":"publish","type":"post","link":"https:\/\/w3buddy.com\/blog\/oracle-performance-tuning-on-linux\/","title":{"rendered":"Oracle Performance Tuning"},"content":{"rendered":"\n

A complete production-ready SOP for Oracle Database real-time performance tuning. Covers AWR, ADDM, ASH report generation and analysis, wait event analysis, SQL tuning with explain plan and SQL profiles, index analysis, undo and temp space issues, locking and blocking sessions, SGA and PGA parameter tuning, and Statspack setup for non-Enterprise Edition \u2014 with real commands, expected outputs, and consultant-level notes.<\/p>\n\n\n\n

\n\n\n\n

1. Document Info<\/h3>\n\n\n\n
Item<\/th>Detail<\/th><\/tr><\/thead>
Oracle Version<\/td>19c (19.3+)<\/td><\/tr>
OS<\/td>Oracle Linux 7.x \/ RHEL 7.x or 8.x<\/td><\/tr>
Tuning Tools<\/td>AWR, ADDM, ASH, SQL Tuning Advisor, Statspack<\/td><\/tr>
License Note<\/td>AWR, ADDM, ASH require Diagnostics Pack license<\/td><\/tr>
License Note<\/td>SQL Tuning Advisor requires Tuning Pack license<\/td><\/tr>
Statspack<\/td>Free \u2014 no additional license required<\/td><\/tr>
MOS Reference<\/td>Doc ID 1502095.1 (AWR Best Practices)<\/td><\/tr>
MOS Reference<\/td>Doc ID 1477599.1 (Performance Tuning Guide)<\/td><\/tr>
MOS Reference<\/td>Doc ID 2118253.1 (Top SQL Tuning Methods)<\/td><\/tr>
MOS Reference<\/td>Doc ID 223117.1 (Statspack Install and Usage)<\/td><\/tr>
Prepared By<\/td>Oracle DBA \/ Consultant<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\n\n\n\n

2. Performance Tuning \u2014 Concepts You Must Know First<\/h3>\n\n\n\n
\n

\ud83d\udcdd What is Oracle performance tuning?<\/strong> Performance tuning is the process of identifying and resolving bottlenecks that cause the database to respond slowly, consume excessive resources, or fail to meet SLA requirements. Tuning is always reactive (fixing current problems) and proactive (preventing future problems).<\/p>\n<\/blockquote>\n\n\n\n

\n

\ud83d\udcdd The Golden Rule of Oracle Tuning:<\/strong> Always identify the ROOT CAUSE before making any change. Never tune blindly. The most common mistake is changing parameters randomly without data to support the change. Every tuning action must be backed by evidence from AWR, ASH, or wait event analysis.<\/p>\n<\/blockquote>\n\n\n\n

\n\n\n\n

Oracle Performance Tuning Hierarchy<\/h4>\n\n\n\n
Start Here \u2192 Is there a specific slow SQL?\n                     \u2502\n              YES \u2500\u2500\u25ba\u2502\u2500\u2500\u25ba SQL Tuning (Section 7)\n                     \u2502\n              NO  \u2500\u2500\u25ba\u2502\u2500\u2500\u25ba Check Wait Events (Section 5)\n                     \u2502\n                     \u251c\u2500\u2500\u25ba CPU bottleneck? \u2192 Parameter Tuning (Section 9)\n                     \u2502\n                     \u251c\u2500\u2500\u25ba I\/O bottleneck? \u2192 Storage\/Index analysis\n                     \u2502\n                     \u251c\u2500\u2500\u25ba Memory issue? \u2192 SGA\/PGA tuning (Section 9)\n                     \u2502\n                     \u251c\u2500\u2500\u25ba Locking? \u2192 Session analysis (Section 8)\n                     \u2502\n                     \u2514\u2500\u2500\u25ba Undo\/Temp? \u2192 Space issues (Section 8)<\/code><\/pre>\n\n\n\n
\n\n\n\n
Key Performance Views<\/h4>\n\n\n\n
View<\/th>What It Shows<\/th><\/tr><\/thead>
v$session<\/td>Currently active sessions and their wait events<\/td><\/tr>
v$sql<\/td>SQL statements in the shared pool with execution stats<\/td><\/tr>
v$sqlstats<\/td>Cumulative SQL statistics (persists longer than v$sql)<\/td><\/tr>
v$event_histogram<\/td>Distribution of wait event times<\/td><\/tr>
v$system_event<\/td>System-wide wait event totals since startup<\/td><\/tr>
v$session_event<\/td>Per-session wait event totals<\/td><\/tr>
v$active_session_history<\/td>In-memory sample of session activity (last 1 hour)<\/td><\/tr>
dba_hist_active_sess_history<\/td>Historical ASH data (from AWR)<\/td><\/tr>
dba_hist_snapshot<\/td>AWR snapshot metadata<\/td><\/tr>
dba_hist_sqlstat<\/td>Historical SQL execution statistics<\/td><\/tr>
v$sga_target_advice<\/td>SGA sizing recommendations<\/td><\/tr>
v$pga_target_advice<\/td>PGA sizing recommendations<\/td><\/tr>
v$undostat<\/td>Undo usage statistics<\/td><\/tr>
v$sort_usage<\/td>Current temp segment usage<\/td><\/tr>
v$lock<\/td>Current locks held in the database<\/td><\/tr>
v$blocked_sessions<\/td>Sessions blocked by other sessions<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\n\n\n\n
3. Initial Performance Assessment<\/h3>\n\n\n\n
\n
\ud83d\udcdd Before diving into specific tools, always start with a quick overall assessment of the database health.<\/strong> This gives you context for what you find in AWR and ASH.<\/p>\n<\/blockquote>\n\n\n\n
\n\n\n\n
3.1 \u2014 Quick Database Health Check<\/h4>\n\n\n\n
sqlplus \/ as sysdba\n\n-- Check instance status and uptime\nset linesize 200\nset pagesize 50\ncol instance_name  for a15\ncol host_name      for a25\ncol version        for a15\ncol status         for a12\ncol startup_time   for a25\n\nSELECT instance_name, host_name, version_full,\n       status,\n       TO_CHAR(startup_time,'YYYY-MM-DD HH24:MI:SS') startup_time,\n       ROUND((SYSDATE - startup_time)*24,1) uptime_hours\nFROM   v$instance;<\/code><\/pre>\n\n\n\n
\n\n\n\n
3.2 \u2014 Check Current Active Sessions and Wait Events<\/h4>\n\n\n\n
\n
\ud83d\udcdd This is your real-time pulse check.<\/strong> Run this first when you get a call about performance problems.<\/p>\n<\/blockquote>\n\n\n\n
-- What is the database doing RIGHT NOW?\nset linesize 200\nset pagesize 100\ncol username   for a15\ncol status     for a10\ncol event      for a40\ncol wait_class for a20\ncol machine    for a25\ncol sql_id     for a15\ncol cnt        for 9999\n\nSELECT NVL(username,'(background)') username,\n       status,\n       event,\n       wait_class,\n       COUNT(*) cnt\nFROM   v$session\nWHERE  type   = 'USER'\nOR     status = 'ACTIVE'\nGROUP BY username, status, event, wait_class\nORDER BY cnt DESC;<\/code><\/pre>\n\n\n\n
\n\n\n\n
3.3 \u2014 Check Top Wait Events Right Now<\/h4>\n\n\n\n
-- System-wide wait events sorted by total wait time\nset linesize 200\nset pagesize 100\ncol event        for a45\ncol wait_class   for a20\ncol total_waits  for 9999999999\ncol time_waited_sec for 9999999\ncol avg_wait_ms  for 999999.99\n\nSELECT event,\n       wait_class,\n       total_waits,\n       ROUND(time_waited\/100,2)          time_waited_sec,\n       ROUND(time_waited\/total_waits\/100*1000,2) avg_wait_ms\nFROM   v$system_event\nWHERE  wait_class != 'Idle'\nAND    total_waits > 0\nORDER BY time_waited DESC\nFETCH FIRST 20 ROWS ONLY;<\/code><\/pre>\n\n\n\n
\n\n\n\n
3.4 \u2014 Check Hit Ratios<\/h4>\n\n\n\n
-- Buffer cache hit ratio (should be > 95%)\nset linesize 200\nset pagesize 50\n\nSELECT ROUND(\n    (1 - (phy.value \/ (con.value + cur.value))) * 100, 2\n) buffer_cache_hit_ratio\nFROM   v$sysstat phy,\n       v$sysstat con,\n       v$sysstat cur\nWHERE  phy.name = 'physical reads'\nAND    con.name = 'consistent gets'\nAND    cur.name = 'db block gets';<\/code><\/pre>\n\n\n\n
-- Library cache hit ratio (should be > 99%)\nset linesize 200\nset pagesize 50\n\nSELECT namespace,\n       ROUND(gethitratio*100,2)  get_hit_ratio,\n       ROUND(pinhitratio*100,2)  pin_hit_ratio,\n       reloads,\n       invalidations\nFROM   v$librarycache\nWHERE  namespace IN ('SQL AREA','TABLE\/PROCEDURE','BODY','TRIGGER')\nORDER BY namespace;<\/code><\/pre>\n\n\n\n
\n\n\n\n
4. AWR \u2014 Automatic Workload Repository<\/h3>\n\n\n\n
\n
\ud83d\udcdd What is AWR?<\/strong> AWR is Oracle’s built-in performance data repository. Every 60 minutes (by default), Oracle takes a snapshot of performance statistics and stores them in the SYSAUX tablespace. AWR reports compare two snapshots and show what the database was doing during that time period.<\/p>\n<\/blockquote>\n\n\n\n
\n
\u26a0\ufe0f LICENSE REQUIREMENT:<\/strong> AWR requires the Oracle Diagnostics Pack license. Do NOT use AWR queries or generate AWR reports in a non-licensed environment. Use Statspack instead (Section 11).<\/p>\n<\/blockquote>\n\n\n\n
\n\n\n\n
4.1 \u2014 Check AWR Snapshot Settings<\/h4>\n\n\n\n
-- Check current AWR configuration\nset linesize 200\nset pagesize 50\ncol snap_interval for a20\ncol retention     for a20\n\nSELECT extract(day    from snap_interval)*24*60 +\n       extract(hour   from snap_interval)*60 +\n       extract(minute from snap_interval)        snap_interval_mins,\n       extract(day    from retention)*24 +\n       extract(hour   from retention)            retention_hours\nFROM   dba_hist_wr_control;<\/code><\/pre>\n\n\n\n
\n\n\n\n
4.2 \u2014 Modify AWR Snapshot Settings<\/h4>\n\n\n\n
\n
\ud83d\udcdd Recommended settings for production:<\/strong> Interval 30 minutes (more granular than default 60), Retention 35 days (5 weeks for trend analysis).<\/p>\n<\/blockquote>\n\n\n\n
-- Change snapshot interval to 30 minutes, retention to 35 days\n-- interval in minutes, retention in minutes\nEXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(\n    interval  => 30,       -- snapshot every 30 minutes\n    retention => 50400     -- retain for 35 days (35*24*60 = 50400 minutes)\n);\n\n-- Verify\nSELECT extract(day    from snap_interval)*24*60 +\n       extract(hour   from snap_interval)*60 +\n       extract(minute from snap_interval)   snap_interval_mins,\n       extract(day    from retention)*24 +\n       extract(hour   from retention)       retention_hours\nFROM   dba_hist_wr_control;<\/code><\/pre>\n\n\n\n
\n\n\n\n
4.3 \u2014 List Available AWR Snapshots<\/h4>\n\n\n\n
-- List recent snapshots to identify time range for your report\nset linesize 200\nset pagesize 100\ncol snap_id    for 9999999\ncol begin_time for a25\ncol end_time   for a25\ncol level#     for 999\n\nSELECT snap_id,\n       TO_CHAR(begin_interval_time,'YYYY-MM-DD HH24:MI:SS') begin_time,\n       TO_CHAR(end_interval_time,  'YYYY-MM-DD HH24:MI:SS') end_time,\n       snap_level\nFROM   dba_hist_snapshot\nWHERE  begin_interval_time > SYSDATE - 2   -- last 2 days\nORDER BY snap_id DESC;<\/code><\/pre>\n\n\n\n
\n\n\n\n
4.4 \u2014 Take Manual AWR Snapshot<\/h4>\n\n\n\n
\n
\ud83d\udcdd When to take a manual snapshot?<\/strong> Before and after a tuning change so you can compare performance before and after with an exact time boundary.<\/p>\n<\/blockquote>\n\n\n\n
-- Take a manual snapshot right now\nEXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();\n\n-- Verify snapshot was taken\nSELECT snap_id,\n       TO_CHAR(end_interval_time,'YYYY-MM-DD HH24:MI:SS') snap_time\nFROM   dba_hist_snapshot\nORDER BY snap_id DESC\nFETCH FIRST 3 ROWS ONLY;<\/code><\/pre>\n\n\n\n
\n\n\n\n
4.5 \u2014 Generate AWR Report<\/h4>\n\n\n\n
\n
\ud83d\udcdd Two ways to generate AWR reports:<\/strong><\/p>\n\n\n\n
    \n
  • HTML format \u2014 rich formatting, charts, easy to read. Use for sharing with management or detailed analysis.<\/li>\n\n\n\n
  • TEXT format \u2014 plain text. Use for quick review in terminal or when email is plain text only.<\/li>\n<\/ul>\n<\/blockquote>\n\n\n\n
    -- Method 1: Generate AWR report to screen (HTML format)\n-- Replace begin_snap and end_snap with your actual snap IDs\n-- Replace dbid with your database DBID\n\n-- First get your DBID\nSELECT dbid FROM v$database;\n\n-- Generate HTML AWR report\n@?\/rdbms\/admin\/awrrpt.sql\n\n-- You will be prompted for:\n-- Report type: html or text\n-- Number of days to list snapshots: enter 1 or 2\n-- Begin snapshot ID: enter the snap ID (e.g., 1234)\n-- End snapshot ID: enter the snap ID (e.g., 1235)\n-- Report name: awrrpt_ORCL_20240115.html (or press Enter for default)<\/code><\/pre>\n\n\n\n
    -- Method 2: Generate AWR report programmatically (for scripts)\n-- Saves report to a variable then you can spool it\nset linesize 200\nset pagesize 0\nset long 100000\nset longchunksize 100000\nset trimspool on\n\nspool \/tmp\/awr_report.html\n\nSELECT *\nFROM   TABLE(\n    DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(\n        l_dbid     => (SELECT dbid FROM v$database),\n        l_inst_num => 1,\n        l_bid      => 1234,    -- begin snap ID -- replace with actual\n        l_eid      => 1235     -- end snap ID -- replace with actual\n    )\n);\n\nspool off;<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    4.6 \u2014 Key Sections to Read in an AWR Report<\/h4>\n\n\n\n
    \n
    \ud83d\udcdd AWR reports contain many sections. Here is where to focus first:<\/strong><\/p>\n<\/blockquote>\n\n\n\n
    Section<\/th>What to Look For<\/th><\/tr><\/thead>
    Report Summary<\/td>DB Time, Elapsed Time, DB CPU. High DB Time with low Elapsed = DB was busy.<\/td><\/tr>
    Top 10 Foreground Events<\/td>Top wait events by total wait time. These are your bottlenecks.<\/td><\/tr>
    SQL Ordered by Elapsed Time<\/td>Top SQL consuming the most total time. Start tuning here.<\/td><\/tr>
    SQL Ordered by CPU Time<\/td>Top SQL consuming the most CPU.<\/td><\/tr>
    SQL Ordered by Buffer Gets<\/td>SQL doing the most logical I\/O (potential full table scans).<\/td><\/tr>
    SQL Ordered by Physical Reads<\/td>SQL doing the most physical I\/O (cache miss or large table scans).<\/td><\/tr>
    Instance Activity Stats<\/td>Physical reads, redo size, sorts, parses.<\/td><\/tr>
    Memory Statistics<\/td>SGA and PGA usage and advice.<\/td><\/tr>
    IO Stats<\/td>Read\/write rates by file and tablespace.<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
    \n\n\n\n
    4.7 \u2014 Query AWR Data Directly (Without Report)<\/h4>\n\n\n\n
    \n
    \ud83d\udcdd Sometimes you need specific data from AWR without generating a full report.<\/strong><\/p>\n<\/blockquote>\n\n\n\n
    -- Top SQL by elapsed time for a specific AWR period\nset linesize 200\nset pagesize 100\ncol sql_id          for a15\ncol elapsed_secs    for 9999999.99\ncol executions      for 9999999\ncol avg_elapsed_ms  for 9999999.99\ncol cpu_secs        for 9999999.99\ncol sql_text        for a60\n\nSELECT s.sql_id,\n       ROUND(SUM(s.elapsed_time_delta)\/1000000,2)     elapsed_secs,\n       SUM(s.executions_delta)                         executions,\n       ROUND(SUM(s.elapsed_time_delta)\/\n             NULLIF(SUM(s.executions_delta),0)\/1000,2) avg_elapsed_ms,\n       ROUND(SUM(s.cpu_time_delta)\/1000000,2)          cpu_secs,\n       SUBSTR(t.sql_text,1,60)                         sql_text\nFROM   dba_hist_sqlstat   s,\n       dba_hist_sqltext   t,\n       dba_hist_snapshot  sn\nWHERE  s.sql_id       = t.sql_id\nAND    s.snap_id      = sn.snap_id\nAND    sn.begin_interval_time > SYSDATE - 1   -- last 24 hours\nGROUP BY s.sql_id, t.sql_text\nORDER BY elapsed_secs DESC\nFETCH FIRST 20 ROWS ONLY;<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    5. ASH \u2014 Active Session History<\/h3>\n\n\n\n
    \n
    \ud83d\udcdd What is ASH?<\/strong> ASH samples all active (non-idle) sessions every second and stores the sample in memory (v$active_session_history \u2014 last ~1 hour). Older samples are flushed to AWR (dba_hist_active_sess_history). ASH is invaluable for analyzing intermittent performance problems that may last only seconds or minutes.<\/p>\n<\/blockquote>\n\n\n\n
    \n
    \u26a0\ufe0f LICENSE REQUIREMENT:<\/strong> ASH requires Oracle Diagnostics Pack license.<\/p>\n<\/blockquote>\n\n\n\n
    \n\n\n\n
    5.1 \u2014 Real-Time ASH Analysis<\/h4>\n\n\n\n
    -- What were sessions doing in the last 5 minutes?\nset linesize 200\nset pagesize 100\ncol event      for a40\ncol wait_class for a20\ncol cnt        for 9999\ncol pct        for 999.99\n\nSELECT event,\n       wait_class,\n       COUNT(*)                              cnt,\n       ROUND(COUNT(*)*100\/SUM(COUNT(*))\n             OVER(),2)                       pct\nFROM   v$active_session_history\nWHERE  sample_time > SYSDATE - 5\/1440   -- last 5 minutes\nAND    session_type = 'FOREGROUND'\nGROUP BY event, wait_class\nORDER BY cnt DESC\nFETCH FIRST 20 ROWS ONLY;<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    5.2 \u2014 ASH Report Generation<\/h4>\n\n\n\n
    -- Generate ASH report for a specific time window\n-- Useful for analyzing a performance spike that already occurred\n@?\/rdbms\/admin\/ashrpt.sql\n\n-- When prompted:\n-- Report type: html or text\n-- Begin time: e.g., 01\/15\/2024 14:00:00\n-- Duration in minutes: e.g., 30\n-- Report name: ashrpt_ORCL_20240115.html<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    5.3 \u2014 ASH Analysis for Specific Time Window<\/h4>\n\n\n\n
    -- Identify what caused a performance problem between 2:00 PM and 2:30 PM\nset linesize 200\nset pagesize 100\ncol event        for a40\ncol wait_class   for a20\ncol sql_id       for a15\ncol username     for a15\ncol cnt          for 9999\n\n-- Top wait events during problem window\nSELECT event,\n       wait_class,\n       COUNT(*) cnt\nFROM   dba_hist_active_sess_history\nWHERE  sample_time BETWEEN\n       TO_TIMESTAMP('2024-01-15 14:00:00','YYYY-MM-DD HH24:MI:SS')\n   AND TO_TIMESTAMP('2024-01-15 14:30:00','YYYY-MM-DD HH24:MI:SS')\nAND    session_type = 'FOREGROUND'\nGROUP BY event, wait_class\nORDER BY cnt DESC\nFETCH FIRST 20 ROWS ONLY;<\/code><\/pre>\n\n\n\n
    -- Top SQL during problem window\nset linesize 200\nset pagesize 100\ncol sql_id       for a15\ncol cnt          for 9999\ncol event        for a40\ncol sql_text     for a60\n\nSELECT ash.sql_id,\n       COUNT(*)                cnt,\n       MAX(ash.event)          top_event,\n       SUBSTR(sq.sql_text,1,60) sql_text\nFROM   dba_hist_active_sess_history ash,\n       dba_hist_sqltext             sq\nWHERE  ash.sample_time BETWEEN\n       TO_TIMESTAMP('2024-01-15 14:00:00','YYYY-MM-DD HH24:MI:SS')\n   AND TO_TIMESTAMP('2024-01-15 14:30:00','YYYY-MM-DD HH24:MI:SS')\nAND    ash.sql_id      = sq.sql_id (+)\nAND    ash.session_type = 'FOREGROUND'\nGROUP BY ash.sql_id, sq.sql_text\nORDER BY cnt DESC\nFETCH FIRST 20 ROWS ONLY;<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    6. ADDM \u2014 Automatic Database Diagnostic Monitor<\/h3>\n\n\n\n
    \n
    \ud83d\udcdd What is ADDM?<\/strong> ADDM automatically analyzes AWR snapshots and identifies performance problems. Unlike AWR which shows raw data, ADDM provides specific recommendations \u2014 “This SQL is causing 40% of database time, create this index to fix it.” ADDM runs automatically after every AWR snapshot.<\/p>\n<\/blockquote>\n\n\n\n
    \n
    \u26a0\ufe0f LICENSE REQUIREMENT:<\/strong> ADDM requires Oracle Diagnostics Pack license.<\/p>\n<\/blockquote>\n\n\n\n
    \n\n\n\n
    6.1 \u2014 View ADDM Findings<\/h4>\n\n\n\n
    -- View recent ADDM task findings\nset linesize 200\nset pagesize 100\ncol task_name   for a35\ncol finding     for a60\ncol type        for a20\ncol impact_pct  for 999.99\n\nSELECT t.task_name,\n       f.type,\n       ROUND(f.impact\/t.db_time*100,2) impact_pct,\n       SUBSTR(f.message,1,60)          finding\nFROM   dba_advisor_tasks    t,\n       dba_advisor_findings f\nWHERE  t.task_id    = f.task_id\nAND    t.advisor_name = 'ADDM'\nAND    t.created    > SYSDATE - 1\nORDER BY f.impact DESC\nFETCH FIRST 20 ROWS ONLY;<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    6.2 \u2014 Generate ADDM Report Manually<\/h4>\n\n\n\n
    -- Run ADDM for specific snapshot range\nDECLARE\n    l_task_name VARCHAR2(30);\nBEGIN\n    DBMS_ADVISOR.CREATE_TASK(\n        advisor_name  => 'ADDM',\n        task_name     => l_task_name,\n        task_desc     => 'Manual ADDM analysis'\n    );\n\n    DBMS_ADVISOR.SET_TASK_PARAMETER(\n        task_name => l_task_name,\n        parameter => 'START_SNAPSHOT',\n        value     => 1234   -- replace with your begin snap ID\n    );\n\n    DBMS_ADVISOR.SET_TASK_PARAMETER(\n        task_name => l_task_name,\n        parameter => 'END_SNAPSHOT',\n        value     => 1235   -- replace with your end snap ID\n    );\n\n    DBMS_ADVISOR.EXECUTE_TASK(task_name => l_task_name);\n\n    DBMS_OUTPUT.PUT_LINE('Task: ' || l_task_name);\nEND;\n\/\n\n-- View the report\nset linesize 200\nset long 100000\nset pagesize 0\n\nSELECT DBMS_ADVISOR.GET_TASK_REPORT(\n    task_name => '<task_name_from_above>'\n) FROM dual;<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    7. Wait Event Analysis<\/h3>\n\n\n\n
    \n
    \ud83d\udcdd Why focus on wait events?<\/strong> Every time an Oracle session has to wait for something (a block to be read from disk, a lock to be released, a log write to complete), it records that wait. Wait events tell you exactly WHY the database is slow \u2014 not just that it is slow. Fixing the top wait event is always more effective than random parameter changes.<\/p>\n<\/blockquote>\n\n\n\n
    \n\n\n\n
    7.1 \u2014 Wait Event Classification<\/h4>\n\n\n\n
    \n
    \ud83d\udcdd Oracle groups wait events into classes. Know these classes:<\/strong><\/p>\n<\/blockquote>\n\n\n\n
    Wait Class<\/th>Typical Cause<\/th>Common Events<\/th><\/tr><\/thead>
    User I\/O<\/td>Reading data from disk<\/td>db file sequential read, db file scattered read<\/td><\/tr>
    System I\/O<\/td>Writing redo, control files<\/td>log file parallel write, control file I\/O<\/td><\/tr>
    Concurrency<\/td>Object contention<\/td>buffer busy waits, row cache lock<\/td><\/tr>
    Application<\/td>Application-level locks<\/td>enq: TM – contention, enq: TX – row lock<\/td><\/tr>
    Commit<\/td>Log file sync wait after commit<\/td>log file sync<\/td><\/tr>
    Configuration<\/td>Misconfigured parameters<\/td>log buffer space, library cache lock<\/td><\/tr>
    Network<\/td>Network delays<\/td>SQL*Net message from client<\/td><\/tr>
    Cluster<\/td>RAC-specific waits<\/td>gc buffer busy, gc cr request<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
    \n\n\n\n
    7.2 \u2014 Real-Time Wait Event Analysis<\/h4>\n\n\n\n
    -- Current wait events across all active sessions\nset linesize 200\nset pagesize 100\ncol sid        for 9999\ncol username   for a15\ncol event      for a40\ncol wait_class for a20\ncol seconds_in_wait for 9999999\ncol state      for a20\ncol sql_id     for a15\n\nSELECT s.sid,\n       s.username,\n       s.status,\n       s.event,\n       s.wait_class,\n       s.seconds_in_wait,\n       s.state,\n       s.sql_id\nFROM   v$session s\nWHERE  s.wait_class != 'Idle'\nAND    s.type        = 'USER'\nORDER BY s.seconds_in_wait DESC;<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    7.3 \u2014 Most Common Wait Events and Their Fixes<\/h4>\n\n\n\n
    -- Detailed wait event analysis with context\nset linesize 200\nset pagesize 100\ncol event          for a40\ncol wait_class     for a20\ncol total_waits    for 9999999999\ncol total_secs     for 9999999.99\ncol avg_wait_ms    for 999999.99\n\nSELECT event,\n       wait_class,\n       total_waits,\n       ROUND(time_waited_micro\/1000000,2)   total_secs,\n       ROUND(time_waited_micro\/\n             NULLIF(total_waits,0)\/1000,2)  avg_wait_ms\nFROM   v$system_event\nWHERE  wait_class    != 'Idle'\nAND    total_waits    > 100\nORDER BY time_waited_micro DESC\nFETCH FIRST 25 ROWS ONLY;<\/code><\/pre>\n\n\n\n
    Common wait events and what to do:<\/strong><\/p>\n\n\n\n
    Wait Event<\/th>Root Cause<\/th>Fix<\/th><\/tr><\/thead>
    db file sequential read<\/td>Single block I\/O \u2014 index scans<\/td>Tune SQL, ensure indexes are used, check I\/O subsystem<\/td><\/tr>
    db file scattered read<\/td>Multiblock I\/O \u2014 full table scans<\/td>Add indexes, tune SQL to avoid FTS<\/td><\/tr>
    log file sync<\/td>Sessions waiting for LGWR after COMMIT<\/td>Reduce commit frequency, faster I\/O for redo logs<\/td><\/tr>
    buffer busy waits<\/td>Contention on specific buffer<\/td>Hot block \u2014 check P1\/P2\/P3 for block address<\/td><\/tr>
    enq: TX – row lock<\/td>Row-level locking between sessions<\/td>Find blocking session and resolve<\/td><\/tr>
    enq: TM – contention<\/td>Table-level lock<\/td>Check for missing FK indexes<\/td><\/tr>
    latch: shared pool<\/td>Hard parses \u2014 shared pool contention<\/td>Use bind variables, increase shared pool<\/td><\/tr>
    latch free<\/td>Latch contention<\/td>Find specific latch type and tune<\/td><\/tr>
    gc buffer busy<\/td>RAC \u2014 remote block request<\/td>Tune application for RAC affinity<\/td><\/tr>
    log buffer space<\/td>Log buffer full \u2014 redo generation too fast<\/td>Increase LOG_BUFFER parameter<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
    \n\n\n\n
    7.4 \u2014 Diagnose Specific Wait Event (Buffer Busy Waits)<\/h4>\n\n\n\n
    -- Find the specific object causing buffer busy waits\nset linesize 200\nset pagesize 100\ncol object_name  for a40\ncol object_type  for a20\ncol owner        for a15\ncol count        for 9999\n\nSELECT o.owner,\n       o.object_name,\n       o.object_type,\n       COUNT(*) cnt\nFROM   v$session        s,\n       dba_objects      o\nWHERE  s.event     = 'buffer busy waits'\nAND    s.row_wait_obj# = o.object_id\nGROUP BY o.owner, o.object_name, o.object_type\nORDER BY cnt DESC;<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    8. SQL Tuning<\/h3>\n\n\n\n
    \n
    \ud83d\udcdd SQL tuning is the highest-impact performance tuning activity.<\/strong> A single poorly written SQL statement can consume 90% of database resources. Identifying and fixing the top 5 worst SQL statements usually solves 80% of performance problems.<\/p>\n<\/blockquote>\n\n\n\n
    \n\n\n\n
    8.1 \u2014 Identify Top SQL from AWR<\/h4>\n\n\n\n
    -- Top 20 SQL by total elapsed time (last 24 hours from AWR)\nset linesize 200\nset pagesize 100\ncol sql_id         for a15\ncol elapsed_secs   for 9999999.99\ncol executions     for 9999999\ncol avg_ms         for 9999999.99\ncol sql_text       for a60\n\nSELECT s.sql_id,\n       ROUND(SUM(s.elapsed_time_delta)\/1000000,2)   elapsed_secs,\n       SUM(s.executions_delta)                        executions,\n       ROUND(SUM(s.elapsed_time_delta)\/\n             NULLIF(SUM(s.executions_delta),0)\/1000,2) avg_ms,\n       SUBSTR(t.sql_text,1,60)                        sql_text\nFROM   dba_hist_sqlstat  s,\n       dba_hist_sqltext  t,\n       dba_hist_snapshot sn\nWHERE  s.sql_id       = t.sql_id\nAND    s.snap_id      = sn.snap_id\nAND    sn.begin_interval_time > SYSDATE - 1\nGROUP BY s.sql_id, t.sql_text\nORDER BY elapsed_secs DESC\nFETCH FIRST 20 ROWS ONLY;<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    8.2 \u2014 Find Currently Running SQL<\/h4>\n\n\n\n
    -- SQL currently executing (right now)\nset linesize 200\nset pagesize 100\ncol sid         for 9999\ncol username    for a15\ncol status      for a10\ncol event       for a35\ncol elapsed_sec for 999999\ncol sql_id      for a15\ncol sql_text    for a60\n\nSELECT s.sid,\n       s.username,\n       s.status,\n       s.event,\n       ROUND(s.last_call_et,0) elapsed_sec,\n       s.sql_id,\n       SUBSTR(q.sql_text,1,60) sql_text\nFROM   v$session s,\n       v$sql     q\nWHERE  s.sql_id   = q.sql_id\nAND    s.status   = 'ACTIVE'\nAND    s.type     = 'USER'\nAND    s.wait_class != 'Idle'\nORDER BY s.last_call_et DESC;<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    8.3 \u2014 Get Full SQL Text for a sql_id<\/h4>\n\n\n\n
    -- Get complete SQL text for a specific sql_id\nset linesize 200\nset long 100000\nset pagesize 0\n\n-- From shared pool (current)\nSELECT sql_fulltext\nFROM   v$sql\nWHERE  sql_id = '&sql_id'\nFETCH FIRST 1 ROW ONLY;\n\n-- From AWR (historical)\nSELECT sql_text\nFROM   dba_hist_sqltext\nWHERE  sql_id = '&sql_id';<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    8.4 \u2014 Generate Execution Plan (EXPLAIN PLAN)<\/h4>\n\n\n\n
    \n
    \ud83d\udcdd What is an execution plan?<\/strong> The execution plan shows HOW Oracle will execute a SQL statement \u2014 which indexes it will use, how it will join tables, in what order. Understanding execution plans is the most fundamental SQL tuning skill.<\/p>\n<\/blockquote>\n\n\n\n
    -- Generate explain plan for a SQL statement\n-- First delete any previous plan for this statement\nDELETE FROM plan_table WHERE statement_id = 'TEST_STMT';\n\nEXPLAIN PLAN\n    SET STATEMENT_ID = 'TEST_STMT'\n    FOR\n    SELECT e.employee_id, e.last_name, d.department_name\n    FROM   hr.employees   e,\n           hr.departments d\n    WHERE  e.department_id = d.department_id\n    AND    e.salary > 5000;\n\n-- Display the execution plan\nset linesize 200\nset pagesize 100\n\nSELECT *\nFROM   TABLE(DBMS_XPLAN.DISPLAY(\n    'PLAN_TABLE',\n    'TEST_STMT',\n    'TYPICAL'  -- options: BASIC, TYPICAL, ALL, ADVANCED\n));<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    8.5 \u2014 Get Actual Execution Plan for a Running SQL<\/h4>\n\n\n\n
    \n
    \ud83d\udcdd Why use actual plan instead of explain plan?<\/strong> EXPLAIN PLAN shows what Oracle WOULD do. The actual plan from v$sql_plan shows what Oracle ACTUALLY DID \u2014 including real row counts, real elapsed time, actual vs estimated rows. Actual plans are far more useful for tuning.<\/p>\n<\/blockquote>\n\n\n\n
    -- Get actual plan for a sql_id from shared pool\n-- This shows real execution statistics including actual rows processed\nset linesize 200\nset pagesize 100\n\nSELECT *\nFROM   TABLE(DBMS_XPLAN.DISPLAY_CURSOR(\n    '&sql_id',    -- replace with actual sql_id\n    NULL,          -- child_number (NULL = all children)\n    'ALLSTATS LAST'  -- shows actual rows, elapsed time per operation\n));<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    8.6 \u2014 Read and Interpret an Execution Plan<\/h4>\n\n\n\n
    \n
    \ud83d\udcdd Key things to look for in an execution plan:<\/strong><\/p>\n<\/blockquote>\n\n\n\n
    Example plan output:\n----------------------------------------------------------------------\n| Id  | Operation                  | Name        | Rows | Cost |\n----------------------------------------------------------------------\n|   0 | SELECT STATEMENT           |             |   10 |   50 |\n|   1 |  HASH JOIN                 |             |   10 |   50 |\n|   2 |   TABLE ACCESS FULL        | DEPARTMENTS |   27 |    3 |\n|   3 |   TABLE ACCESS BY INDEX ROWID | EMPLOYEES|   10 |   47 |\n|   4 |    INDEX RANGE SCAN        | EMP_SAL_IDX |   10 |    2 |\n----------------------------------------------------------------------<\/code><\/pre>\n\n\n\n
    \n
    \ud83d\udcdd What each operation means:<\/strong><\/p>\n\n\n\n
      \n
    • TABLE ACCESS FULL<\/code> \u2014 full table scan. May be correct for small tables, problematic for large ones.<\/li>\n\n\n\n
    • INDEX RANGE SCAN<\/code> \u2014 uses an index to find a range of values. Usually good.<\/li>\n\n\n\n
    • INDEX UNIQUE SCAN<\/code> \u2014 uses index to find exactly one row. Best.<\/li>\n\n\n\n
    • HASH JOIN<\/code> \u2014 joins two result sets using a hash. Good for large datasets.<\/li>\n\n\n\n
    • NESTED LOOPS<\/code> \u2014 for each row in outer set, look up inner set. Good when outer set is small and inner has index.<\/li>\n\n\n\n
    • MERGE JOIN<\/code> \u2014 requires both sets to be sorted. Used when large sorted data is available.<\/li>\n<\/ul>\n<\/blockquote>\n\n\n\n
      Red flags in execution plans:<\/strong><\/p>\n\n\n\n
      Red Flag<\/th>What It Means<\/th>Fix<\/th><\/tr><\/thead>
      TABLE ACCESS FULL on large table<\/td>Full table scan on millions of rows<\/td>Add index on predicate columns<\/td><\/tr>
      High Rows estimate vs actual mismatch<\/td>Stale statistics<\/td>Gather statistics on table<\/td><\/tr>
      CARTESIAN JOIN<\/td>Missing join condition<\/td>Fix the SQL query<\/td><\/tr>
      Many nested loops with large outer sets<\/td>N+1 query problem<\/td>Use HASH JOIN hint or fix query<\/td><\/tr>
      BUFFER SORT<\/td>Sort operation in memory<\/td>Tune sort area or query<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
      \n\n\n\n
      8.7 \u2014 SQL Tuning Advisor<\/h4>\n\n\n\n
      \n
      \ud83d\udcdd What is SQL Tuning Advisor (STA)?<\/strong> STA is an Oracle built-in tool that analyzes a specific SQL statement and provides recommendations \u2014 create this index, gather statistics on this table, accept this SQL profile. It is the automated version of manual SQL analysis.<\/p>\n<\/blockquote>\n\n\n\n
      \n
      \u26a0\ufe0f LICENSE REQUIREMENT:<\/strong> SQL Tuning Advisor requires Oracle Tuning Pack license.<\/p>\n<\/blockquote>\n\n\n\n
      -- Run SQL Tuning Advisor for a specific sql_id\nDECLARE\n    l_task_name  VARCHAR2(30);\n    l_sql_text   CLOB;\nBEGIN\n    -- Create tuning task for a SQL from AWR\n    l_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(\n        sql_id        => '&sql_id',\n        scope         => DBMS_SQLTUNE.SCOPE_COMPREHENSIVE,\n        time_limit    => 300,    -- max 300 seconds analysis time\n        task_name     => 'tune_sql_&sql_id',\n        description   => 'Tuning task for problem SQL'\n    );\n\n    DBMS_OUTPUT.PUT_LINE('Task created: ' || l_task_name);\nEND;\n\/\n\n-- Execute the tuning task\nEXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'tune_sql_&sql_id');\n\n-- View the recommendations\nset linesize 200\nset long 100000\nset pagesize 0\n\nSELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('tune_sql_&sql_id')\nFROM   dual;<\/code><\/pre>\n\n\n\n
      \n\n\n\n
      8.8 \u2014 Create and Use SQL Profile<\/h4>\n\n\n\n
      \n
      \ud83d\udcdd What is a SQL Profile?<\/strong> A SQL Profile is a set of corrective hints stored in the database that Oracle applies automatically whenever a specific SQL statement executes. It fixes execution plans without changing application code. STA often recommends creating a SQL profile.<\/p>\n<\/blockquote>\n\n\n\n
      -- Accept a SQL Profile recommended by STA\n-- (After running SQL Tuning Advisor above)\nEXEC DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(\n    task_name => 'tune_sql_&sql_id',\n    replace   => TRUE\n);\n\n-- List existing SQL profiles\nset linesize 200\nset pagesize 100\ncol name        for a30\ncol sql_text    for a50\ncol status      for a10\ncol created     for a20\n\nSELECT name,\n       sql_text,\n       status,\n       TO_CHAR(created,'YYYY-MM-DD HH24:MI') created\nFROM   dba_sql_profiles\nORDER BY created DESC;\n\n-- Drop a SQL profile if needed\nEXEC DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'SYS_SQLPROF_...');<\/code><\/pre>\n\n\n\n
      \n\n\n\n
      8.9 \u2014 Create SQL Plan Baseline (SQL Patch)<\/h4>\n\n\n\n
      \n
      \ud83d\udcdd What is a SQL Plan Baseline?<\/strong> A SQL Plan Baseline pins a specific execution plan to a SQL statement. Even if statistics change or the optimizer would normally choose a different plan, Oracle always uses the pinned plan. Use this when you have verified a specific plan is optimal and want to prevent plan regression.<\/p>\n<\/blockquote>\n\n\n\n
      -- Load a specific plan from cursor cache into baselines\n-- First find the sql_id and plan_hash_value of the good plan\nset linesize 200\nset pagesize 100\ncol sql_id          for a15\ncol plan_hash_value for 9999999999\ncol executions      for 9999999\ncol elapsed_secs    for 9999999.99\n\nSELECT sql_id, plan_hash_value, executions,\n       ROUND(elapsed_time\/1000000,2) elapsed_secs\nFROM   v$sql\nWHERE  sql_id = '&sql_id'\nORDER BY elapsed_time;\n\n-- Load the good plan as a SQL Plan Baseline\nDECLARE\n    l_plans PLS_INTEGER;\nBEGIN\n    l_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(\n        sql_id          => '&sql_id',\n        plan_hash_value => &plan_hash_value\n    );\n    DBMS_OUTPUT.PUT_LINE('Plans loaded: ' || l_plans);\nEND;\n\/\n\n-- Verify baseline was created\nset linesize 200\nset pagesize 100\ncol sql_handle     for a35\ncol plan_name      for a35\ncol enabled        for a8\ncol accepted       for a8\ncol fixed          for a8\n\nSELECT sql_handle, plan_name, enabled, accepted, fixed,\n       TO_CHAR(created,'YYYY-MM-DD HH24:MI') created\nFROM   dba_sql_plan_baselines\nORDER BY created DESC;<\/code><\/pre>\n\n\n\n
      \n\n\n\n
      9. Index Analysis and Rebuilding<\/h3>\n\n\n\n
      \n\n\n\n
      9.1 \u2014 Find Missing Indexes (High Full Table Scans)<\/h4>\n\n\n\n
      -- Tables with most full table scans (candidates for indexing)\nset linesize 200\nset pagesize 100\ncol owner      for a20\ncol table_name for a35\ncol full_scans for 9999999\ncol rows_processed for 9999999999\n\nSELECT o.owner,\n       o.object_name table_name,\n       COUNT(*)       full_scans\nFROM   v$sql_plan      p,\n       dba_objects     o\nWHERE  p.operation    = 'TABLE ACCESS'\nAND    p.options      = 'FULL'\nAND    p.object_owner = o.owner\nAND    p.object_name  = o.object_name\nAND    o.object_type  = 'TABLE'\nGROUP BY o.owner, o.object_name\nORDER BY full_scans DESC\nFETCH FIRST 20 ROWS ONLY;<\/code><\/pre>\n\n\n\n
      \n\n\n\n
      9.2 \u2014 Find Unusable and Invalid Indexes<\/h4>\n\n\n\n
      set linesize 200\nset pagesize 100\ncol owner       for a20\ncol index_name  for a35\ncol table_name  for a35\ncol status      for a12\ncol index_type  for a20\n\n-- Find unusable indexes\nSELECT owner, index_name, table_name, status, index_type\nFROM   dba_indexes\nWHERE  status IN ('UNUSABLE','INVALID','N\/A')\nORDER BY owner, table_name;\n\n-- Find unusable index partitions\nSELECT index_owner, index_name, partition_name, status\nFROM   dba_ind_partitions\nWHERE  status = 'UNUSABLE'\nORDER BY index_owner, index_name;<\/code><\/pre>\n\n\n\n
      \n\n\n\n
      9.3 \u2014 Identify Indexes Not Being Used<\/h4>\n\n\n\n
      -- Enable index monitoring (must be done per index)\n-- Run this on suspect indexes\nALTER INDEX hr.emp_name_idx MONITORING USAGE;\n\n-- Wait for application to run (at least 24 hours)\n-- Then check if index was used\nset linesize 200\nset pagesize 100\ncol index_name for a35\ncol table_name for a35\ncol monitoring for a5\ncol used       for a5\ncol start_time for a25\ncol end_time   for a25\n\nSELECT index_name, table_name, monitoring, used,\n       TO_CHAR(start_monitoring,'YYYY-MM-DD HH24:MI') start_time,\n       TO_CHAR(end_monitoring,'YYYY-MM-DD HH24:MI')   end_time\nFROM   v$object_usage\nORDER BY index_name;\n\n-- Stop monitoring\nALTER INDEX hr.emp_name_idx NOMONITORING USAGE;<\/code><\/pre>\n\n\n\n
      \n\n\n\n
      9.4 \u2014 Rebuild Fragmented Indexes<\/h4>\n\n\n\n
      \n
      \ud83d\udcdd Why rebuild indexes?<\/strong> Over time as rows are inserted, updated, and deleted, indexes can become fragmented \u2014 blocks are partially empty, the tree becomes unbalanced. This increases the number of block reads needed for index lookups. Rebuilding reclaims space and rebalances the tree.<\/p>\n<\/blockquote>\n\n\n\n
      \n
      \ud83d\udcdd When to rebuild?<\/strong> Only when index statistics show significant fragmentation. Do NOT rebuild indexes on a schedule without checking if they need it \u2014 unnecessary rebuilds waste time and generate redo.<\/p>\n<\/blockquote>\n\n\n\n
      -- Check index fragmentation using ANALYZE\n-- Run for specific indexes that seem slow\nANALYZE INDEX hr.emp_emp_id_pk VALIDATE STRUCTURE;\n\n-- Check fragmentation results\nset linesize 200\nset pagesize 100\ncol name         for a35\ncol del_lf_rows  for 9999999\ncol lf_rows      for 9999999\ncol pct_deleted  for 999.99\n\nSELECT name,\n       del_lf_rows,\n       lf_rows,\n       ROUND(del_lf_rows*100\/NULLIF(lf_rows,0),2) pct_deleted\nFROM   index_stats;\n\n-- If pct_deleted > 20% -- consider rebuild<\/code><\/pre>\n\n\n\n
      -- Rebuild index online (no downtime -- application can still use table)\nALTER INDEX hr.emp_emp_id_pk REBUILD ONLINE;\n\n-- Rebuild index with specific tablespace\nALTER INDEX hr.emp_emp_id_pk\n    REBUILD ONLINE\n    TABLESPACE users\n    PARALLEL 4;\n\n-- Rebuild all indexes on a table\nBEGIN\n    FOR idx IN (\n        SELECT index_name\n        FROM   dba_indexes\n        WHERE  table_owner = 'HR'\n        AND    table_name  = 'EMPLOYEES'\n        AND    status     != 'UNUSABLE'\n    ) LOOP\n        EXECUTE IMMEDIATE\n            'ALTER INDEX hr.' || idx.index_name || ' REBUILD ONLINE';\n        DBMS_OUTPUT.PUT_LINE('Rebuilt: ' || idx.index_name);\n    END LOOP;\nEND;\n\/<\/code><\/pre>\n\n\n\n
      \n\n\n\n
      9.5 \u2014 Coalesce Index (Alternative to Rebuild)<\/h4>\n\n\n\n
      \n
      \ud83d\udcdd COALESCE vs REBUILD:<\/strong> COALESCE merges adjacent leaf blocks within each branch \u2014 it is lighter weight than REBUILD and does not create a new segment. Use COALESCE for routine maintenance. Use REBUILD when you need to move the index to a different tablespace or change storage attributes.<\/p>\n<\/blockquote>\n\n\n\n
      -- Coalesce index (merges leaf blocks, lighter than rebuild)\nALTER INDEX hr.emp_emp_id_pk COALESCE;<\/code><\/pre>\n\n\n\n
      \n\n\n\n
      10. Undo and Temp Space Issues<\/h3>\n\n\n\n
      \n\n\n\n
      10.1 \u2014 Diagnose Undo Space Issues<\/h4>\n\n\n\n
      \n
      \ud83d\udcdd Common undo errors:<\/strong><\/p>\n\n\n\n
        \n
      • ORA-01555: Snapshot too old<\/code> \u2014 query ran too long and undo data it needed was overwritten<\/li>\n\n\n\n
      • ORA-30036: Unable to extend segment in undo tablespace<\/code> \u2014 undo tablespace is full<\/li>\n<\/ul>\n<\/blockquote>\n\n\n\n
        -- Check undo tablespace usage\nset linesize 200\nset pagesize 100\ncol tablespace_name for a25\ncol status          for a12\ncol total_mb        for 9999999\ncol used_mb         for 9999999\ncol free_mb         for 9999999\ncol used_pct        for 999.99\n\nSELECT u.tablespace_name,\n       u.status,\n       ROUND(t.total_bytes\/1024\/1024,2) total_mb,\n       ROUND(u.used_bytes\/1024\/1024,2)  used_mb,\n       ROUND((t.total_bytes-u.used_bytes)\/1024\/1024,2) free_mb,\n       ROUND(u.used_bytes*100\/t.total_bytes,2) used_pct\nFROM  (SELECT tablespace_name,\n              SUM(DECODE(status,'ACTIVE',bytes,\n                                'UNEXPIRED',bytes,0)) used_bytes\n       FROM   dba_undo_extents\n       GROUP BY tablespace_name) u,\n      (SELECT tablespace_name, SUM(bytes) total_bytes\n       FROM   dba_data_files\n       GROUP BY tablespace_name) t\nWHERE  u.tablespace_name = t.tablespace_name;<\/code><\/pre>\n\n\n\n
        -- Undo statistics -- check for tuning needs\nset linesize 200\nset pagesize 100\ncol begin_time  for a25\ncol end_time    for a25\ncol undotsn     for 9999\ncol txncount    for 9999999\ncol maxquerylen for 9999999\ncol ssolderrcnt for 9999\n\nSELECT TO_CHAR(begin_time,'YYYY-MM-DD HH24:MI') begin_time,\n       TO_CHAR(end_time,'YYYY-MM-DD HH24:MI')   end_time,\n       undotsn,\n       txncount,\n       maxquerylen,\n       ssolderrcnt    -- ORA-01555 occurrences\nFROM   v$undostat\nORDER BY begin_time DESC\nFETCH FIRST 20 ROWS ONLY;<\/code><\/pre>\n\n\n\n
        -- Check UNDO_RETENTION setting\nSHOW PARAMETER undo_retention;\n-- Default is 900 seconds (15 minutes)\n-- If ORA-01555 errors occur -- increase this value\n\n-- Increase undo retention (if ORA-01555 errors observed)\nALTER SYSTEM SET UNDO_RETENTION=3600 SCOPE=BOTH;  -- 1 hour\n\n-- Add datafile to undo tablespace if it is full\nALTER TABLESPACE undotbs1\n    ADD DATAFILE '\/u01\/app\/oracle\/oradata\/ORCL\/undotbs02.dbf'\n    SIZE 10G\n    AUTOEXTEND ON NEXT 1G MAXSIZE 50G;<\/code><\/pre>\n\n\n\n
        \n\n\n\n
        10.2 \u2014 Diagnose Temp Space Issues<\/h4>\n\n\n\n
        \n
        \ud83d\udcdd Common temp errors:<\/strong> ORA-01652: Unable to extend temp segment<\/code> \u2014 temp tablespace is full. Caused by large sort operations, hash joins, or long-running queries.<\/p>\n<\/blockquote>\n\n\n\n
        -- Check current temp usage\nset linesize 200\nset pagesize 100\ncol username    for a15\ncol session_num for 9999999\ncol sql_id      for a15\ncol tablespace  for a20\ncol segtype     for a15\ncol mb_used     for 9999999.99\n\nSELECT s.username,\n       s.sid || ',' || s.serial# session_num,\n       s.sql_id,\n       u.tablespace,\n       u.segtype,\n       ROUND(u.blocks * t.block_size \/ 1024\/1024,2) mb_used\nFROM   v$sort_usage u,\n       v$session    s,\n       dba_tablespaces t\nWHERE  u.session_addr = s.saddr\nAND    u.tablespace   = t.tablespace_name\nORDER BY mb_used DESC;\n\n-- Total temp usage\nset linesize 200\nset pagesize 50\n\nSELECT tablespace_name,\n       ROUND(total_blocks*8192\/1024\/1024,2)         total_mb,\n       ROUND(used_blocks*8192\/1024\/1024,2)           used_mb,\n       ROUND(free_blocks*8192\/1024\/1024,2)           free_mb,\n       ROUND(used_blocks*100\/NULLIF(total_blocks,0),2) used_pct\nFROM   v$temp_space_header;<\/code><\/pre>\n\n\n\n
        -- Add space to temp tablespace if needed\nALTER TABLESPACE temp\n    ADD TEMPFILE '\/u01\/app\/oracle\/oradata\/ORCL\/temp02.dbf'\n    SIZE 10G\n    AUTOEXTEND ON NEXT 1G MAXSIZE 50G;\n\n-- Check what SQL is consuming large temp space\nset linesize 200\nset pagesize 100\ncol sql_id     for a15\ncol mb_used    for 9999999.99\ncol sql_text   for a60\n\nSELECT u.sqladdr,\n       ROUND(SUM(u.blocks*8192\/1024\/1024),2) mb_used,\n       SUBSTR(q.sql_text,1,60) sql_text,\n       q.sql_id\nFROM   v$sort_usage u,\n       v$sql        q\nWHERE  u.sqlhash = q.hash_value\nGROUP BY u.sqladdr, q.sql_text, q.sql_id\nORDER BY mb_used DESC\nFETCH FIRST 10 ROWS ONLY;<\/code><\/pre>\n\n\n\n
        \n\n\n\n
        11. Locking and Blocking Sessions<\/h3>\n\n\n\n
        \n\n\n\n
        11.1 \u2014 Find Blocking Sessions<\/h4>\n\n\n\n
        \n
        \ud83d\udcdd What is a blocking session?<\/strong> When Session A holds a lock on a row and Session B also wants to lock that row, Session B waits. Session A is the blocker, Session B is the waiter. If this goes on for a long time, applications hang. Finding and resolving blockers is one of the most common DBA tasks.<\/p>\n<\/blockquote>\n\n\n\n
        -- Find all blocking sessions and their victims\nset linesize 200\nset pagesize 100\ncol blocker_sid    for 9999\ncol blocker_user   for a15\ncol blocker_machine for a25\ncol blocker_sql    for a50\ncol waiter_sid     for 9999\ncol waiter_user    for a15\ncol waiter_sql     for a50\ncol wait_secs      for 9999999\n\nSELECT b.sid                              blocker_sid,\n       b.username                         blocker_user,\n       b.machine                          blocker_machine,\n       SUBSTR(bq.sql_text,1,50)           blocker_sql,\n       w.sid                              waiter_sid,\n       w.username                         waiter_user,\n       SUBSTR(wq.sql_text,1,50)           waiter_sql,\n       w.seconds_in_wait                  wait_secs\nFROM   v$session b,\n       v$session w,\n       v$sql     bq,\n       v$sql     wq\nWHERE  b.sid      = w.blocking_session\nAND    b.sql_id   = bq.sql_id (+)\nAND    w.sql_id   = wq.sql_id (+)\nORDER BY wait_secs DESC;<\/code><\/pre>\n\n\n\n
        \n\n\n\n
        11.2 \u2014 Find All Locks in the Database<\/h4>\n\n\n\n
        -- Comprehensive lock view\nset linesize 200\nset pagesize 100\ncol sid         for 9999\ncol username    for a15\ncol lock_type   for a20\ncol object_name for a35\ncol mode_held   for a15\ncol mode_req    for a15\ncol block       for 9999\n\nSELECT s.sid,\n       s.username,\n       DECODE(l.type,\n           'TM','DML (Table Lock)',\n           'TX','Transaction (Row Lock)',\n           'UL','User Lock',\n           l.type)                 lock_type,\n       o.object_name,\n       DECODE(l.lmode,\n           0,'None', 1,'Null', 2,'Row Share',\n           3,'Row Excl', 4,'Share', 5,'Share Row Excl',\n           6,'Exclusive', l.lmode) mode_held,\n       DECODE(l.request,\n           0,'None', 1,'Null', 2,'Row Share',\n           3,'Row Excl', 4,'Share', 5,'Share Row Excl',\n           6,'Exclusive', l.request) mode_req,\n       l.block\nFROM   v$lock    l,\n       v$session s,\n       dba_objects o\nWHERE  l.sid     = s.sid\nAND    l.id1     = o.object_id (+)\nAND    l.type   IN ('TM','TX','UL')\nORDER BY l.block DESC, s.sid;<\/code><\/pre>\n\n\n\n
        \n\n\n\n
        11.3 \u2014 Find Lock Wait Chain (Deadlock Analysis)<\/h4>\n\n\n\n
        -- Show complete wait chain (who is blocking whom)\nset linesize 200\nset pagesize 100\ncol wait_chain for a200\n\nSELECT LPAD(' ',2*(LEVEL-1)) || s.sid || ' (' ||\n       NVL(s.username,'(bg)') || ')' ||\n       ' waits for ' || s.blocking_session ||\n       ' [' || s.event || ' ' || s.seconds_in_wait || 's]' wait_chain\nFROM   v$session s\nSTART WITH s.blocking_session IS NOT NULL\n       AND s.blocking_session NOT IN (\n           SELECT sid FROM v$session\n           WHERE  blocking_session IS NOT NULL\n       )\nCONNECT BY PRIOR s.sid = s.blocking_session\nORDER SIBLINGS BY s.sid;<\/code><\/pre>\n\n\n\n
        \n\n\n\n
        11.4 \u2014 Kill a Blocking Session<\/h4>\n\n\n\n
        \n
        \u26a0\ufe0f IMPORTANT:<\/strong> Never kill a session without first understanding what it is doing and getting approval from the application owner. Killing a session rolls back its transaction which may take time and can affect application users. Always try to contact the session owner first.<\/p>\n<\/blockquote>\n\n\n\n
        -- Get details about the blocking session before killing\nset linesize 200\nset pagesize 50\ncol username  for a15\ncol machine   for a25\ncol program   for a35\ncol status    for a10\ncol logon_time for a25\n\nSELECT sid, serial#, username, machine, program,\n       status,\n       TO_CHAR(logon_time,'YYYY-MM-DD HH24:MI:SS') logon_time,\n       last_call_et seconds_active\nFROM   v$session\nWHERE  sid = &blocking_sid;\n\n-- Kill the blocking session\n-- Format: 'SID,SERIAL#'\nALTER SYSTEM KILL SESSION '&sid,&serial#' IMMEDIATE;\n\n-- If session does not die (OS-level kill needed)\nALTER SYSTEM KILL SESSION '&sid,&serial#' IMMEDIATE;\n-- Get the OS PID\nSELECT spid FROM v$process p, v$session s\nWHERE  p.addr = s.paddr AND s.sid = &sid;\n\n-- Then as root: kill -9 <spid><\/code><\/pre>\n\n\n\n
        \n\n\n\n
        11.5 \u2014 Find and Fix Missing Foreign Key Indexes (Common Deadlock Cause)<\/h4>\n\n\n\n
        \n
        \ud83d\udcdd Why does this cause locking?<\/strong> When a child table has a foreign key to a parent table but NO index on the FK column, Oracle must lock the entire child table during parent table DML operations. This is one of the most common and easily overlooked causes of locking problems.<\/p>\n<\/blockquote>\n\n\n\n
        -- Find foreign keys without corresponding indexes\nset linesize 200\nset pagesize 100\ncol owner       for a20\ncol table_name  for a30\ncol fk_name     for a35\ncol fk_columns  for a50\n\nSELECT c.owner,\n       c.table_name,\n       c.constraint_name fk_name,\n       c.status,\n       cc.column_name    fk_columns\nFROM   dba_constraints  c,\n       dba_cons_columns cc\nWHERE  c.constraint_type = 'R'\nAND    c.owner           = cc.owner\nAND    c.constraint_name = cc.constraint_name\nAND    NOT EXISTS (\n    SELECT 1\n    FROM   dba_ind_columns ic\n    WHERE  ic.table_owner  = c.owner\n    AND    ic.table_name   = c.table_name\n    AND    ic.column_name  = cc.column_name\n    AND    ic.column_position = 1\n)\nORDER BY c.owner, c.table_name;\n\n-- Create index on missing FK column (example)\n-- CREATE INDEX hr.emp_dept_fk_idx ON hr.employees(department_id);<\/code><\/pre>\n\n\n\n
        \n\n\n\n
        12. SGA and PGA Parameter Tuning<\/h3>\n\n\n\n
        \n\n\n\n
        12.1 \u2014 Check Current Memory Configuration<\/h4>\n\n\n\n
        -- Overview of current memory allocation\nset linesize 200\nset pagesize 100\ncol name  for a40\ncol value for a25\n\nSELECT name, value\nFROM   v$parameter\nWHERE  name IN (\n    'memory_target',\n    'memory_max_target',\n    'sga_target',\n    'sga_max_size',\n    'pga_aggregate_target',\n    'pga_aggregate_limit',\n    'db_cache_size',\n    'shared_pool_size',\n    'large_pool_size',\n    'java_pool_size',\n    'streams_pool_size'\n)\nORDER BY name;<\/code><\/pre>\n\n\n\n
        \n\n\n\n
        12.2 \u2014 Check SGA Component Sizing<\/h4>\n\n\n\n
        -- Current SGA component sizes and tuning advisories\nset linesize 200\nset pagesize 100\ncol component    for a35\ncol current_size_mb for 9999999\ncol min_size_mb  for 9999999\ncol max_size_mb  for 9999999\n\nSELECT component,\n       ROUND(current_size\/1024\/1024,0)   current_size_mb,\n       ROUND(min_size\/1024\/1024,0)        min_size_mb,\n       ROUND(max_size\/1024\/1024,0)        max_size_mb\nFROM   v$sga_dynamic_components\nORDER BY current_size DESC;<\/code><\/pre>\n\n\n\n
        \n\n\n\n
        12.3 \u2014 SGA Size Advisory<\/h4>\n\n\n\n
        \n
        \ud83d\udcdd What is SGA advisory?<\/strong> Oracle continuously simulates what the buffer cache hit ratio would be at different SGA sizes. Use this to determine optimal SGA size without guessing.<\/p>\n<\/blockquote>\n\n\n\n
        -- SGA sizing recommendations\nset linesize 200\nset pagesize 100\ncol sga_size_mb     for 9999999\ncol estd_db_time_s  for 9999999\ncol estd_physical_reads for 9999999\ncol db_time_factor  for 999.99\n\nSELECT sga_size\/1024\/1024                   sga_size_mb,\n       estd_db_time                         estd_db_time_s,\n       estd_physical_reads,\n       ROUND(estd_db_time\/\n             (SELECT estd_db_time\n              FROM v$sga_target_advice\n              WHERE sga_size_factor=1.0),2) db_time_factor\nFROM   v$sga_target_advice\nORDER BY sga_size;<\/code><\/pre>\n\n\n\n
        \n\n\n\n
        12.4 \u2014 Buffer Cache Advisory<\/h4>\n\n\n\n
        -- Buffer cache size advisory\nset linesize 200\nset pagesize 100\ncol size_mb            for 9999999\ncol estd_physical_reads for 9999999\ncol estd_pct_prd       for 999.99\n\nSELECT size_for_estimate\/1024\/1024         size_mb,\n       estd_physical_reads,\n       ROUND(estd_physical_read_factor*100,2) estd_pct_prd\nFROM   v$db_cache_advice\nWHERE  name      = 'DEFAULT'\nAND    block_size = (SELECT value FROM v$parameter\n                     WHERE name = 'db_block_size')\nORDER BY size_for_estimate;<\/code><\/pre>\n\n\n\n
        \n\n\n\n
        12.5 \u2014 Shared Pool Advisory<\/h4>\n\n\n\n
        -- Shared pool sizing advisory\nset linesize 200\nset pagesize 100\ncol shared_pool_size_mb for 9999999\ncol estd_lc_time_saved  for 9999999\n\nSELECT shared_pool_size_for_estimate\/1024\/1024  shared_pool_size_mb,\n       estd_lc_size\/1024\/1024                   estd_lc_size_mb,\n       estd_lc_time_saved\nFROM   v$shared_pool_advice\nORDER BY shared_pool_size_for_estimate;<\/code><\/pre>\n\n\n\n
        \n\n\n\n
        12.6 \u2014 PGA Advisory<\/h4>\n\n\n\n
        -- PGA sizing advisory\nset linesize 200\nset pagesize 100\ncol pga_target_mb      for 9999999\ncol estd_pga_cache_hit_pct for 999.99\ncol estd_overalloc_cnt for 9999999\n\nSELECT pga_target_for_estimate\/1024\/1024     pga_target_mb,\n       ROUND(estd_pga_cache_hit_pct,2)        estd_pga_cache_hit_pct,\n       estd_overalloc_cnt\nFROM   v$pga_target_advice\nORDER BY pga_target_for_estimate;<\/code><\/pre>\n\n\n\n
        \n\n\n\n
        12.7 \u2014 Tune Key Parameters<\/h4>\n\n\n\n
        -- After reviewing advisory recommendations, apply changes\nsqlplus \/ as sysdba\n\n-- Increase SGA Target (if advisory shows benefit)\nALTER SYSTEM SET SGA_TARGET=8G SCOPE=BOTH;\n\n-- Increase PGA (if advisory shows over-allocation)\nALTER SYSTEM SET PGA_AGGREGATE_TARGET=2G SCOPE=BOTH;\n\n-- Increase shared pool (if hard parse rate is high)\nALTER SYSTEM SET SHARED_POOL_SIZE=2G SCOPE=BOTH;\n\n-- Tune cursor parameters (reduce hard parses)\n-- session_cached_cursors = number of cursors cached per session\nALTER SYSTEM SET SESSION_CACHED_CURSORS=100 SCOPE=BOTH;\n\n-- open_cursors = max open cursors per session\nALTER SYSTEM SET OPEN_CURSORS=500 SCOPE=BOTH;\n\n-- cursor_sharing = FORCE makes Oracle treat similar SQL as same\n-- Use with caution -- can cause plan instability\n-- ALTER SYSTEM SET CURSOR_SHARING=FORCE SCOPE=BOTH;\n\n-- LOG_BUFFER (increase if log buffer space wait event is high)\n-- Requires restart\nALTER SYSTEM SET LOG_BUFFER=50M SCOPE=SPFILE;<\/code><\/pre>\n\n\n\n
        \n\n\n\n
        12.8 \u2014 Check Parse Statistics<\/h4>\n\n\n\n
        -- Hard vs soft parse ratio (high hard parses = missing bind variables)\nset linesize 200\nset pagesize 50\n\nSELECT s1.value                               total_parses,\n       s2.value                               hard_parses,\n       s1.value - s2.value                    soft_parses,\n       ROUND(s2.value*100\/NULLIF(s1.value,0),2) hard_parse_pct\nFROM   v$sysstat s1, v$sysstat s2\nWHERE  s1.name = 'parse count (total)'\nAND    s2.name = 'parse count (hard)';<\/code><\/pre>\n\n\n\n
        \n
        \ud83d\udcdd Hard parse ratio above 10% is a concern.<\/strong> It indicates applications are not using bind variables and Oracle must parse every SQL from scratch. Fix: ensure applications use bind variables, or set CURSOR_SHARING=FORCE<\/code> as a temporary workaround.<\/p>\n<\/blockquote>\n\n\n\n
        \n\n\n\n
        13. Statspack \u2014 For Non-Enterprise Edition<\/h3>\n\n\n\n
        \n
        \ud83d\udcdd What is Statspack?<\/strong> Statspack is Oracle’s predecessor to AWR. It is available in ALL Oracle editions \u2014 including Standard Edition \u2014 and does not require the Diagnostics Pack license. It provides similar functionality to AWR (snapshots, reports, top SQL) but without the automation of AWR. If you are in a Standard Edition environment, Statspack is your primary performance analysis tool.<\/p>\n<\/blockquote>\n\n\n\n
        \n\n\n\n
        13.1 \u2014 Install Statspack<\/h4>\n\n\n\n
        # Connect to database as SYSDBA from ORACLE_HOME\nsu - oracle\nsqlplus \/ as sysdba<\/code><\/pre>\n\n\n\n
        -- Create PERFSTAT tablespace for Statspack data\n-- Statspack data can be significant -- allocate at least 2GB\nCREATE TABLESPACE perfstat\n    DATAFILE '\/u01\/app\/oracle\/oradata\/ORCL\/perfstat01.dbf'\n    SIZE 2G\n    AUTOEXTEND ON NEXT 500M MAXSIZE 20G;\n\n-- Run Statspack install script\n-- It will prompt for PERFSTAT password and tablespace\n@?\/rdbms\/admin\/spcreate.sql\n\n-- When prompted:\n-- Enter password for PERFSTAT: Perfstat_123\n-- Enter tablespace: PERFSTAT\n-- Enter temp tablespace: TEMP\n\n-- Verify Statspack installed correctly\nCONNECT perfstat\/Perfstat_123\nSELECT * FROM stats$statspack_parameter;<\/code><\/pre>\n\n\n\n
        \n\n\n\n
        13.2 \u2014 Configure Statspack<\/h4>\n\n\n\n
        -- Connect as PERFSTAT\nsqlplus perfstat\/Perfstat_123\n\n-- View default settings\nset linesize 200\nset pagesize 50\ncol name  for a30\ncol value for a20\n\nSELECT name, value\nFROM   stats$statspack_parameter;\n\n-- Change snapshot level (default 5 is fine for most environments)\n-- Level 5 = captures all the major statistics\n-- Level 6 = also captures SQL statistics\n-- Level 7 = also captures segment statistics (more space)\nEXEC STATSPACK.SNAP(i_snap_level=>5);<\/code><\/pre>\n\n\n\n
        \n\n\n\n
        13.3 \u2014 Take a Manual Statspack Snapshot<\/h4>\n\n\n\n
        -- Take a snapshot manually\n-- Run before and after the period you want to analyze\nEXEC STATSPACK.SNAP;\n\n-- Verify snapshot was taken\nset linesize 200\nset pagesize 50\ncol snap_id   for 9999999\ncol snap_time for a25\n\nSELECT snap_id,\n       TO_CHAR(snap_time,'YYYY-MM-DD HH24:MI:SS') snap_time\nFROM   stats$snapshot\nORDER BY snap_id DESC\nFETCH FIRST 10 ROWS ONLY;<\/code><\/pre>\n\n\n\n
        \n\n\n\n
        13.4 \u2014 Schedule Automatic Statspack Snapshots<\/h4>\n\n\n\n
        -- Create a job to take snapshots every 30 minutes\n-- Using DBMS_SCHEDULER (recommended)\nBEGIN\n    DBMS_SCHEDULER.CREATE_JOB(\n        job_name        => 'STATSPACK_SNAPSHOT_JOB',\n        job_type        => 'PLSQL_BLOCK',\n        job_action      => 'STATSPACK.SNAP;',\n        start_date      => SYSTIMESTAMP,\n        repeat_interval => 'FREQ=MINUTELY;INTERVAL=30',\n        enabled         => TRUE,\n        comments        => 'Automatic Statspack snapshot every 30 minutes'\n    );\nEND;\n\/\n\n-- Verify job was created and is running\nSELECT job_name, enabled, state, last_run_duration\nFROM   dba_scheduler_jobs\nWHERE  job_name = 'STATSPACK_SNAPSHOT_JOB';<\/code><\/pre>\n\n\n\n
        \n\n\n\n
        13.5 \u2014 Generate Statspack Report<\/h4>\n\n\n\n
        -- Generate Statspack report between two snapshots\n-- First list available snapshots\nSELECT snap_id,\n       TO_CHAR(snap_time,'YYYY-MM-DD HH24:MI:SS') snap_time\nFROM   stats$snapshot\nORDER BY snap_id DESC;\n\n-- Run the report\n@?\/rdbms\/admin\/spreport.sql\n\n-- You will be prompted for:\n-- Database ID (press Enter for default)\n-- Instance number (press Enter for default)\n-- Begin snapshot ID\n-- End snapshot ID\n-- Report name (e.g., sp_report_20240115.lst)<\/code><\/pre>\n\n\n\n
        \n\n\n\n
        13.6 \u2014 Clean Up Old Statspack Data<\/h4>\n\n\n\n
        -- Statspack data accumulates -- purge old snapshots regularly\n-- Purge snapshots older than 30 days\n\nEXEC STATSPACK.PURGE(\n    i_begin_snap => (\n        SELECT MIN(snap_id)\n        FROM   stats$snapshot\n        WHERE  snap_time < SYSDATE - 30\n    ),\n    i_end_snap => (\n        SELECT MAX(snap_id)\n        FROM   stats$snapshot\n        WHERE  snap_time < SYSDATE - 30\n    ),\n    i_extended_purge => TRUE\n);\n\n-- Or purge to keep only last N days\nEXEC STATSPACK.PURGE(\n    i_num_days => 30   -- keep last 30 days only\n);<\/code><\/pre>\n\n\n\n
        \n\n\n\n
        13.7 \u2014 Uninstall Statspack (If Needed)<\/h4>\n\n\n\n
        -- Uninstall Statspack completely\nsqlplus \/ as sysdba\n\n@?\/rdbms\/admin\/spdrop.sql\n\n-- Then drop the tablespace\nDROP TABLESPACE perfstat\n    INCLUDING CONTENTS AND DATAFILES;<\/code><\/pre>\n\n\n\n
        \n\n\n\n
        14. Performance Tuning Checklist (Quick Assessment)<\/h3>\n\n\n\n
        \n
        \ud83d\udcdd Use this checklist when you get a “database is slow” call. Work through it in order.<\/strong><\/p>\n<\/blockquote>\n\n\n\n
        -- STEP 1: What is the database doing RIGHT NOW?\nSELECT event, wait_class, COUNT(*) cnt\nFROM   v$session\nWHERE  wait_class != 'Idle' AND type = 'USER'\nGROUP BY event, wait_class ORDER BY cnt DESC;\n\n-- STEP 2: Are there any blocking sessions?\nSELECT blocking_session, sid, username, event, seconds_in_wait\nFROM   v$session\nWHERE  blocking_session IS NOT NULL;\n\n-- STEP 3: Is temp or undo full?\nSELECT tablespace_name, ROUND(used_blocks*8192\/1024\/1024,2) used_mb\nFROM   v$temp_space_header;\n\n-- STEP 4: What SQL is consuming most resources right now?\nSELECT sql_id, elapsed_time, executions, cpu_time, buffer_gets\nFROM   v$sql\nWHERE  last_active_time > SYSDATE - 1\/24\nORDER BY elapsed_time DESC\nFETCH FIRST 10 ROWS ONLY;\n\n-- STEP 5: Is the FRA full?\nSELECT name, space_used\/space_limit*100 pct_used\nFROM   v$recovery_file_dest;\n\n-- STEP 6: Any ORA- errors in alert log?\n-- Check alert log: grep ORA- alert_ORCL.log | tail -50<\/code><\/pre>\n\n\n\n
        \n\n\n\n
        15. Quick Reference Card<\/h3>\n\n\n\n
        Task<\/th>Command<\/th><\/tr><\/thead>
        Check active waits now<\/td>SELECT event,wait_class,COUNT(*) FROM v$session WHERE wait_class!='Idle' GROUP BY event,wait_class ORDER BY 3 DESC;<\/code><\/td><\/tr>
        Check blocking sessions<\/td>SELECT blocking_session,sid,username,event,seconds_in_wait FROM v$session WHERE blocking_session IS NOT NULL;<\/code><\/td><\/tr>
        Kill blocking session<\/td>ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;<\/code><\/td><\/tr>
        Top SQL elapsed (AWR)<\/td>Query dba_hist_sqlstat with SUM(elapsed_time_delta)<\/td><\/tr>
        Get full SQL text<\/td>SELECT sql_fulltext FROM v$sql WHERE sql_id='...';<\/code><\/td><\/tr>
        Explain plan<\/td>EXPLAIN PLAN FOR <sql>; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(...));<\/code><\/td><\/tr>
        Actual plan from cursor<\/td>SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('sql_id',NULL,'ALLSTATS LAST'));<\/code><\/td><\/tr>
        Take AWR snapshot<\/td>EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();<\/code><\/td><\/tr>
        Generate AWR report<\/td>@?\/rdbms\/admin\/awrrpt.sql<\/code><\/td><\/tr>
        Generate ASH report<\/td>@?\/rdbms\/admin\/ashrpt.sql<\/code><\/td><\/tr>
        List AWR snapshots<\/td>SELECT snap_id,begin_interval_time FROM dba_hist_snapshot ORDER BY snap_id DESC;<\/code><\/td><\/tr>
        Buffer cache hit ratio<\/td>Query v$sysstat for physical reads \/ consistent gets + db block gets<\/td><\/tr>
        Hard parse ratio<\/td>Query v$sysstat for parse count (hard) \/ parse count (total)<\/td><\/tr>
        Check SGA advisory<\/td>SELECT sga_size\/1024\/1024,estd_db_time FROM v$sga_target_advice;<\/code><\/td><\/tr>
        Check PGA advisory<\/td>SELECT pga_target_for_estimate\/1024\/1024,estd_pga_cache_hit_pct FROM v$pga_target_advice;<\/code><\/td><\/tr>
        Increase SGA<\/td>ALTER SYSTEM SET SGA_TARGET=8G SCOPE=BOTH;<\/code><\/td><\/tr>
        Increase PGA<\/td>ALTER SYSTEM SET PGA_AGGREGATE_TARGET=2G SCOPE=BOTH;<\/code><\/td><\/tr>
        Check undo stats<\/td>SELECT ssolderrcnt,maxquerylen FROM v$undostat ORDER BY begin_time DESC;<\/code><\/td><\/tr>
        Increase undo retention<\/td>ALTER SYSTEM SET UNDO_RETENTION=3600 SCOPE=BOTH;<\/code><\/td><\/tr>
        Check temp usage<\/td>SELECT ROUND(used_blocks*8192\/1024\/1024,2) FROM v$temp_space_header;<\/code><\/td><\/tr>
        Find missing FK indexes<\/td>Query dba_constraints for R type with no matching dba_ind_columns<\/td><\/tr>
        Check fragmented indexes<\/td>ANALYZE INDEX <name> VALIDATE STRUCTURE; SELECT pct_deleted FROM index_stats;<\/code><\/td><\/tr>
        Rebuild index online<\/td>ALTER INDEX <name> REBUILD ONLINE;<\/code><\/td><\/tr>
        Monitor index usage<\/td>ALTER INDEX <name> MONITORING USAGE;<\/code><\/td><\/tr>
        SQL Tuning Advisor<\/td>DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id=>'...')<\/code><\/td><\/tr>
        Accept SQL profile<\/td>DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(task_name=>'...')<\/code><\/td><\/tr>
        Create SPM baseline<\/td>DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id=>'...')<\/code><\/td><\/tr>
        Install Statspack<\/td>@?\/rdbms\/admin\/spcreate.sql<\/code><\/td><\/tr>
        Take Statspack snap<\/td>EXEC STATSPACK.SNAP;<\/code><\/td><\/tr>
        Generate Statspack report<\/td>@?\/rdbms\/admin\/spreport.sql<\/code><\/td><\/tr>
        Purge Statspack<\/td>EXEC STATSPACK.PURGE(i_num_days=>30);<\/code><\/td><\/tr>
        MOS AWR Best Practices<\/td>Doc ID 1502095.1<\/td><\/tr>
        MOS Performance Guide<\/td>Doc ID 1477599.1<\/td><\/tr>
        MOS SQL Tuning Methods<\/td>Doc ID 2118253.1<\/td><\/tr>
        MOS Statspack Guide<\/td>Doc ID 223117.1<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
        \n\n\n\n
        This SOP covers everything you need to diagnose and resolve Oracle Database performance issues without referring to any other source. Always start with wait event analysis before making any changes, always use AWR and ASH to identify root cause with evidence before tuning, never change parameters randomly without data to support the change, and always take an AWR snapshot before and after any tuning change so you can measure whether the change actually helped.<\/p>\n\n\n\n
        <\/p>\n","protected":false},"excerpt":{"rendered":"
        A complete production-ready SOP for Oracle Database real-time performance tuning. Covers AWR, ADDM, ASH report generation and analysis, wait event analysis, SQL tuning with explain plan and SQL profiles, index analysis, undo and temp space issues, locking and blocking sessions, SGA and PGA parameter tuning, and Statspack setup for non-Enterprise Edition \u2014 with real commands, […]<\/p>\n","protected":false},"author":1,"featured_media":5803,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"googlesitekit_rrm_CAowu461DA:productID":"","footnotes":""},"categories":[1534],"tags":[],"class_list":["post-5802","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-oracle-sop"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/5802","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/comments?post=5802"}],"version-history":[{"count":1,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/5802\/revisions"}],"predecessor-version":[{"id":5804,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/5802\/revisions\/5804"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media\/5803"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=5802"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=5802"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/tags?post=5802"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}