{"id":4190,"date":"2025-06-11T10:03:39","date_gmt":"2025-06-11T10:03:39","guid":{"rendered":"https:\/\/w3buddy.com\/?post_type=cposts&p=4190"},"modified":"2025-06-11T10:03:40","modified_gmt":"2025-06-11T10:03:40","slug":"generate-awr-ash-addm-reports-from-sql-prompt","status":"publish","type":"cposts","link":"https:\/\/w3buddy.com\/blog\/notes\/oracle-dba-d2d-tasks\/generate-awr-ash-addm-reports-from-sql-prompt\/","title":{"rendered":"Generate AWR, ASH, ADDM Reports from SQL Prompt"},"content":{"rendered":"\n

Oracle provides built-in tools to generate performance reports like AWR<\/strong>, AWRDD<\/strong>, ASH<\/strong>, and ADDM<\/strong>. These reports help identify resource issues, long-running queries, and system bottlenecks.<\/p>\n\n\n\n

This guide shows how to run each report directly from the SQL prompt<\/strong> using Oracle-provided scripts.<\/p>\n\n\n\n

1. AWR Report (Automatic Workload Repository)<\/h3>\n\n\n\n

The AWR report<\/strong> gives a snapshot of performance statistics between two time intervals. Use it to find I\/O, CPU, or wait bottlenecks.<\/p>\n\n\n\n

Generate AWR Report<\/strong><\/p>\n\n\n\n

-- Run AWR report script\n@?\/rdbms\/admin\/awrrpt.sql<\/code><\/code><\/pre>\n\n\n\n
\ud83d\udccc You\u2019ll be prompted for:<\/p>\n\n\n\n
    \n
  • DBID or default<\/strong><\/li>\n\n\n\n
  • Snapshot begin\/end IDs<\/strong><\/li>\n\n\n\n
  • Report format (HTML or TEXT)<\/strong><\/li>\n\n\n\n
  • Report file name<\/strong><\/li>\n<\/ul>\n\n\n\n
    2. AWRDD Report (AWR Daily)<\/h3>\n\n\n\n
    The AWRDD report<\/strong> summarizes a full day\u2019s performance using AWR snapshots.<\/p>\n\n\n\n
    Generate AWRDD Report<\/strong><\/p>\n\n\n\n
    -- Run AWR daily report script\n@?\/rdbms\/admin\/awrddrpt.sql<\/code><\/code><\/pre>\n\n\n\n
    \ud83d\udccc You’ll enter:<\/p>\n\n\n\n
      \n
    • Snapshot dates<\/strong><\/li>\n\n\n\n
    • Report format<\/strong><\/li>\n\n\n\n
    • Output file name<\/strong><\/li>\n<\/ul>\n\n\n\n
      3. ASH Report (Active Session History)<\/h3>\n\n\n\n
      The ASH report<\/strong> captures active session details within a specific time range. It\u2019s ideal for spotting spikes or unusual activity.<\/p>\n\n\n\n
      Generate ASH Report<\/strong><\/p>\n\n\n\n
      -- Run ASH report script\n@?\/rdbms\/admin\/ashrpt.sql<\/code><\/code><\/pre>\n\n\n\n
      \ud83d\udccc You’ll be asked to enter:<\/p>\n\n\n\n
        \n
      • Begin time<\/strong> (e.g., 10\/06\/25 14:00:00<\/code>)<\/li>\n\n\n\n
      • Duration in minutes<\/strong> (e.g., 30<\/code>)<\/li>\n\n\n\n
      • Report file name<\/strong> (e.g., ORCL_ashrpt_30min.html<\/code>)<\/li>\n<\/ul>\n\n\n\n
        4. ADDM Report (Automatic Diagnostic Monitor)<\/h3>\n\n\n\n
        The ADDM report<\/strong> provides root cause analysis<\/strong> and suggestions to improve performance using past AWR data.<\/p>\n\n\n\n
        Generate ADDM Report<\/strong><\/p>\n\n\n\n
        -- Run ADDM report script\n@?\/rdbms\/admin\/addmrpt.sql<\/code><\/code><\/pre>\n\n\n\n
        \ud83d\udccc Prompts include:<\/p>\n\n\n\n
          \n
        • Begin and End snapshot IDs<\/strong><\/li>\n\n\n\n
        • Report format<\/strong><\/li>\n\n\n\n
        • Output file name<\/strong><\/li>\n<\/ul>\n\n\n\n
          Summary Table<\/h3>\n\n\n\n
          Report<\/th>Script<\/th>Use Case<\/th><\/tr><\/thead>
          AWR<\/strong><\/td>@awrrpt.sql<\/code><\/td>General performance between snapshots<\/td><\/tr>
          AWRDD<\/strong><\/td>@awrddrpt.sql<\/code><\/td>Daily performance summary<\/td><\/tr>
          ASH<\/strong><\/td>@ashrpt.sql<\/code><\/td>Session activity in real-time<\/td><\/tr>
          ADDM<\/strong><\/td>@addmrpt.sql<\/code><\/td>Root cause + recommendations<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
          \ud83d\udccc Conclusion<\/h3>\n\n\n\n
          These reports provide critical insights<\/strong> into database health and workload trends. By running them from the SQL prompt, you can analyze system behavior without needing any extra tools.<\/p>\n","protected":false},"excerpt":{"rendered":"
          Oracle provides built-in tools to generate performance reports like AWR, AWRDD, ASH, and ADDM. These reports help identify resource issues, long-running queries, and system bottlenecks. This guide shows how to run each report directly from the SQL prompt using Oracle-provided scripts. 1. AWR Report (Automatic Workload Repository) The AWR report gives a snapshot of performance […]<\/p>\n","protected":false},"template":"","meta":{"googlesitekit_rrm_CAowu461DA:productID":""},"categories":[952,984],"class_list":["post-4190","cposts","type-cposts","status-publish","hentry","category-notes","category-oracle-dba-d2d-tasks"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/cposts\/4190","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/cposts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/cposts"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=4190"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=4190"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}