analysse

WITH stats_summary AS (

    SELECT 

        owner,

        COUNT(*) AS analyzed_tables,                  -- tables with any stats

        COUNT(CASE WHEN last_analyzed > SYSDATE - 2 

                   THEN 1 END) AS recent_tables,      -- tables analyzed in last 2 days

        MAX(last_analyzed) AS last_analyzed_max

    FROM all_tab_statistics

    WHERE last_analyzed IS NOT NULL

      AND owner NOT LIKE 'SYS%'

      AND owner NOT LIKE 'APEX%'

      AND owner NOT IN ('SYSTEM','OUTLN','DBSNMP','CTXSYS','MDSYS','XDB')  -- typical exclusions

    GROUP BY owner

),

total_tables_per_schema AS (

    SELECT 

        owner,

        COUNT(*) AS total_tables

    FROM all_tables

    WHERE owner NOT LIKE 'SYS%'

      AND owner NOT LIKE 'APEX%'

      AND owner NOT IN ('SYSTEM','OUTLN','DBSNMP','CTXSYS','MDSYS','XDB')

    GROUP BY owner

)

SELECT 

    s.owner                          AS schema_name,

    t.total_tables,

    s.analyzed_tables,

    s.recent_tables,

    ROUND(s.analyzed_tables * 100 / NULLIF(t.total_tables, 0), 1) AS pct_analyzed_overall,

    ROUND(s.recent_tables  * 100 / NULLIF(t.total_tables, 0), 1) AS pct_analyzed_recent,

    TO_CHAR(s.last_analyzed_max, 'DD-MON-YYYY HH24:MI:SS') AS last_analyzed_max

FROM stats_summary s

JOIN total_tables_per_schema t ON s.owner = t.owner

WHERE s.last_analyzed_max > SYSDATE - 2          -- only schemas touched in last 2 days

ORDER BY s.last_analyzed_max DESC;

prmp

 You are an expert Oracle database performance analyst with deep knowledge of Automatic Workload Repository (AWR) reports. I will provide you with the content of an AWR report (extracted from a PDF or text file). Your task is to thoroughly analyze the report, identify any performance issues or bottlenecks, and provide step-by-step debugging recommendations to resolve them.

First, parse the key sections of the AWR report, including but not limited to:

• Report Header (DB Name, Instance, Snapshot Interval, Elapsed Time, DB Time)
• Load Profile (e.g., Parses, Executes, Transactions per second)
• Instance Efficiency Percentages (e.g., Buffer Hit %, Library Hit %, Soft Parse %)
• Top Timed Foreground Events (e.g., CPU time, db file sequential read, log file sync)
• Wait Class Breakdown
• SQL Statistics (Top SQL by Elapsed Time, CPU Time, Buffer Gets, Executions)
• Instance Activity Stats (e.g., user commits, redo size)
• Tablespace I/O Stats
• Advisory Statistics (e.g., Buffer Cache, PGA, SGA advice)
• Any RAC-specific sections if applicable (e.g., Global Cache stats)

For each relevant section:

1. Summarize the key metrics and highlight any abnormalities (e.g., high wait times >10% of DB time, low hit ratios <90%, excessive parses).
2. Identify potential issues, such as:
   • CPU bottlenecks (high CPU usage without corresponding waits).
   • I/O issues (slow reads/writes, high physical I/O).
   • Locking/contention problems (enq: waits, latch misses).
   • SQL inefficiencies (poorly optimized queries, missing indexes).
   • Memory shortages (frequent swapping, undersized SGA/PGA).
   • Network or log-related delays.
   • Overall workload spikes during the snapshot period.
3. Provide root cause analysis based on correlations across sections (e.g., link high waits to specific SQL IDs).
4. Suggest actionable debugging steps and fixes, prioritized by impact:
   • Query tuning (e.g., add indexes, rewrite SQL, use hints).
   • Configuration changes (e.g., increase buffer cache, adjust parameters like cursor_sharing).
   • Monitoring tools (e.g., run ADDM, ASH reports, or trace specific sessions).
   • Hardware/resource upgrades if indicated.
   • Best practices for prevention.

Output your analysis in a structured format:

• Summary: High-level overview of the report's health (e.g., good/fair/poor performance).
• Key Issues: Bullet list of top 5-10 problems with severity (low/medium/high).
• Detailed Analysis: Section-by-section breakdown.
• Recommendations: Numbered list of steps to debug and resolve, with expected outcomes.
• Follow-up Questions: Any clarifying questions for more context (e.g., DB version, workload type).

Be objective, data-driven, and use evidence from the report. If the report is incomplete or unclear, note that and request more details.

USERS

SELECT sid, serial#, username, osuser, program, sql_id, event, wait_time, seconds_in_wait

FROM v$session

WHERE username IN ('APPS', 'APPLSYSPUB')  -- or the user logging in

  AND status = 'ACTIVE'

  AND program LIKE '%JDBC%'  -- AccessGate uses JDBC

ORDER BY seconds_in_wait DESC;