-

sql_perf_analyzer.sh


#!/bin/bash

# Filename: sql_perf_analyzer.sh

# Description: Intelligent SQL Performance Diagnostic Toolkit (Shell Wrapper)

# Author: ChatGPT Toolkit for Oracle


# === Setup Variables ===

SCRIPT_DIR=$(dirname "$0")

SQL_DIR="$SCRIPT_DIR/sql"

LOG_DIR="$SCRIPT_DIR/output/logs"

REPORT_DIR="$SCRIPT_DIR/output/reports"

DATE_TAG=$(date '+%Y%m%d_%H%M%S')

LOG_FILE="$LOG_DIR/sql_perf_analysis_$DATE_TAG.log"


mkdir -p "$LOG_DIR" "$REPORT_DIR"


# === Prompt Inputs ===

echo "Enter SQL_ID to diagnose: "

read SQL_ID


echo "Enter GOOD timeframe (start time YYYY-MM-DD HH24:MI): "

read GOOD_START


echo "Enter GOOD timeframe (end time YYYY-MM-DD HH24:MI): "

read GOOD_END


echo "Enter BAD timeframe (start time YYYY-MM-DD HH24:MI): "

read BAD_START


echo "Enter BAD timeframe (end time YYYY-MM-DD HH24:MI): "

read BAD_END


echo "Output format? (1=Text, 2=HTML, 3=Both): "

read FORMAT


# === Begin Logging ===

echo "[INFO] Starting SQL Performance Diagnostics..." | tee -a "$LOG_FILE"

echo "[INFO] SQL_ID: $SQL_ID" | tee -a "$LOG_FILE"

echo "[INFO] GOOD: $GOOD_START to $GOOD_END" | tee -a "$LOG_FILE"

echo "[INFO] BAD : $BAD_START to $BAD_END" | tee -a "$LOG_FILE"


# === Run Diagnostic SQL Scripts ===

for script in \

  check_plan_flips.sql \

  compare_plan_steps.sql \

  analyze_wait_events.sql \

  check_object_stats.sql \

  run_advisors.sql \

  suggest_baseline.sql

  do

  echo "[INFO] Running $script ..." | tee -a "$LOG_FILE"


  sqlplus -s / as sysdba <<EOF >> "$LOG_FILE"

  SET DEFINE ON

  DEFINE sql_id='$SQL_ID'

  DEFINE good_start="$GOOD_START"

  DEFINE good_end="$GOOD_END"

  DEFINE bad_start="$BAD_START"

  DEFINE bad_end="$BAD_END"

  SPOOL "$REPORT_DIR/${script%.sql}_$DATE_TAG.txt"

  @"$SQL_DIR/$script"

  SPOOL OFF

  EXIT

EOF


done


# === Wrap-up ===

echo "[INFO] Analysis Complete. Reports saved under $REPORT_DIR" | tee -a "$LOG_FILE"

echo "[INFO] Log saved to $LOG_FILE"


if [ "$FORMAT" = "2" ] || [ "$FORMAT" = "3" ]; then

  echo "[INFO] Generating HTML Summary..." | tee -a "$LOG_FILE"

  echo "<html><head><title>SQL Performance Report</title></head><body>" > "$REPORT_DIR/final_report_$DATE_TAG.html"

  for f in "$REPORT_DIR"/*_$DATE_TAG.txt; do

    echo "<h2>Report: $(basename "$f")</h2><pre>" >> "$REPORT_DIR/final_report_$DATE_TAG.html"

    cat "$f" >> "$REPORT_DIR/final_report_$DATE_TAG.html"

    echo "</pre><hr>" >> "$REPORT_DIR/final_report_$DATE_TAG.html"

  done

  echo "</body></html>" >> "$REPORT_DIR/final_report_$DATE_TAG.html"

fi


exit 0

=============================


-- Script: analyze_wait_events.sql

-- Purpose: Analyze wait events for a given SQL_ID during bad execution timeframe using ASH

-- RAC-aware, CDB/PDB-compliant


SET LINES 200 PAGES 1000 TRIMSPOOL ON

SET VERIFY OFF


COLUMN inst_id FORMAT 999

COLUMN event FORMAT A50

COLUMN wait_class FORMAT A20

COLUMN total_secs FORMAT 999999


PROMPT Analyzing wait events for SQL_ID: &sql_id during BAD timeframe...


SELECT ash.inst_id,

       ash.event,

       ash.wait_class,

       COUNT(*) * 10 AS total_secs

  FROM gv$active_session_history ash

 WHERE ash.sql_id = '&sql_id'

   AND ash.sample_time BETWEEN TO_DATE('&bad_start', 'YYYY-MM-DD HH24:MI')

                           AND TO_DATE('&bad_end', 'YYYY-MM-DD HH24:MI')

   AND ash.wait_class NOT IN ('Idle')

 GROUP BY ash.inst_id, ash.event, ash.wait_class

 ORDER BY total_secs DESC;


PROMPT Wait event analysis completed.

=======================

-- Script: compare_plan_steps.sql
-- Purpose: Compare execution plans from good and bad timeframes for a SQL_ID
-- RAC-aware, PDB-aware

SET LINES 200 PAGES 1000 TRIMSPOOL ON
SET VERIFY OFF

COLUMN inst_id FORMAT 999
COLUMN id FORMAT 999
COLUMN operation FORMAT A30
COLUMN options FORMAT A20
COLUMN object_name FORMAT A30
COLUMN object_type FORMAT A20
COLUMN cost FORMAT 999999
COLUMN cardinality FORMAT 9999999
COLUMN bytes FORMAT 9999999

PROMPT Fetching GOOD plan steps...
SELECT inst_id, id, plan_hash_value, operation, options,
       object_name, object_type, cost, cardinality, bytes
  FROM   gv$sql_plan
 WHERE  sql_id = '&sql_id'
   AND  plan_hash_value = (
          SELECT plan_hash_value FROM (
            SELECT plan_hash_value,
                   COUNT(*) AS freq
              FROM dba_hist_sqlstat h
              JOIN dba_hist_snapshot s
                ON h.snap_id = s.snap_id AND h.dbid = s.dbid
             WHERE h.sql_id = '&sql_id'
               AND s.begin_interval_time BETWEEN TO_DATE('&good_start','YYYY-MM-DD HH24:MI') AND TO_DATE('&good_end','YYYY-MM-DD HH24:MI')
             GROUP BY plan_hash_value
             ORDER BY freq DESC
          ) WHERE ROWNUM = 1
        );

PROMPT -------------------------------
PROMPT Fetching BAD plan steps...
SELECT inst_id, id, plan_hash_value, operation, options,
       object_name, object_type, cost, cardinality, bytes
  FROM   gv$sql_plan
 WHERE  sql_id = '&sql_id'
   AND  plan_hash_value = (
          SELECT plan_hash_value FROM (
            SELECT plan_hash_value,
                   COUNT(*) AS freq
              FROM dba_hist_sqlstat h
              JOIN dba_hist_snapshot s
                ON h.snap_id = s.snap_id AND h.dbid = s.dbid
             WHERE h.sql_id = '&sql_id'
               AND s.begin_interval_time BETWEEN TO_DATE('&bad_start','YYYY-MM-DD HH24:MI') AND TO_DATE('&bad_end','YYYY-MM-DD HH24:MI')
             GROUP BY plan_hash_value
             ORDER BY freq DESC
          ) WHERE ROWNUM = 1
        );

PROMPT Plan comparison completed.

=====================

-- Script: check_plan_flips.sql
-- Purpose: Detect execution plan changes (plan flips) for a given SQL_ID over time
-- RAC-aware and CDB/PDB compliant

SET VERIFY OFF
SET LINES 200 PAGES 1000 TRIMSPOOL ON
COLUMN inst FORMAT 999
COLUMN plan_hash_value FORMAT 9999999999
COLUMN first_seen FORMAT A20
COLUMN last_seen FORMAT A20

PROMPT Checking for plan changes for SQL_ID: &sql_id

SELECT s.inst_id AS inst,
       p.plan_hash_value,
       MIN(s.begin_interval_time) AS first_seen,
       MAX(s.end_interval_time) AS last_seen,
       COUNT(*) AS snap_count
  FROM   dba_hist_sqlstat p
  JOIN   dba_hist_snapshot s
         ON p.snap_id = s.snap_id AND p.dbid = s.dbid AND p.instance_number = s.instance_number
 WHERE  p.sql_id = '&sql_id'
 GROUP BY s.inst_id, p.plan_hash_value
 ORDER BY first_seen;

PROMPT Plan flip analysis completed.

===========================

-- Script: check_object_stats.sql
-- Purpose: Check stats, status, and staleness for tables, partitions, indexes used in a SQL_ID
-- RAC-aware, CDB/PDB-compliant

SET VERIFY OFF
SET LINES 200 PAGES 1000 TRIMSPOOL ON

PROMPT Identifying objects used in SQL_ID &sql_id ...

-- Create temporary table for object list (for reuse)
CREATE GLOBAL TEMPORARY TABLE sql_obj_list (
  object_owner VARCHAR2(128),
  object_name  VARCHAR2(128),
  object_type  VARCHAR2(128)
) ON COMMIT PRESERVE ROWS;

-- Extract objects from GV$SQL_PLAN
INSERT INTO sql_obj_list
SELECT DISTINCT object_owner, object_name, object_type
FROM   gv$sql_plan
WHERE  sql_id = '&sql_id'
  AND  object_name IS NOT NULL;

COMMIT;

-- Check table stats
PROMPT ---- Table Statistics and Status ----
SELECT t.owner,
       t.table_name,
       t.last_analyzed,
       s.stale_stats,
       s.num_rows,
       s.blocks,
       s.avg_row_len,
       s.global_stats,
       s.user_stats
FROM   sql_obj_list o
JOIN   dba_tables t ON t.owner = o.object_owner AND t.table_name = o.object_name
LEFT JOIN dba_tab_statistics s ON s.owner = t.owner AND s.table_name = t.table_name;

-- Check index stats
PROMPT ---- Index Statistics and Status ----
SELECT i.owner,
       i.index_name,
       i.status,
       i.last_analyzed,
       i.blevel,
       i.leaf_blocks,
       i.distinct_keys,
       i.clustering_factor
FROM   sql_obj_list o
JOIN   dba_indexes i ON i.table_owner = o.object_owner AND i.table_name = o.object_name;

-- Check partitions if any
PROMPT ---- Partition Stats (if any) ----
SELECT table_owner, table_name, partition_name, last_analyzed, num_rows, blocks
FROM   dba_tab_partitions
WHERE  table_owner IN (SELECT DISTINCT object_owner FROM sql_obj_list)
AND    table_name IN (SELECT DISTINCT object_name FROM sql_obj_list);

-- Cleanup
TRUNCATE TABLE sql_obj_list;
DROP TABLE sql_obj_list;

PROMPT Object statistics check completed.

========================

-- Script: run_advisors.sql
-- Purpose: Run Access, Undo, and SQL Tuning Advisor for a given SQL_ID
-- RAC-aware, CDB/PDB-safe, must be run with SYS or tuning privileges

SET SERVEROUTPUT ON
SET VERIFY OFF
SET LINES 200 PAGES 1000

PROMPT Starting advisory diagnostics for SQL_ID: &sql_id

DECLARE
  l_sql_id        VARCHAR2(20) := '&sql_id';
  l_task_name     VARCHAR2(30);
  l_task_desc     VARCHAR2(100);
BEGIN
  -- SQL Tuning Advisor
  l_task_name := 'SQL_TUNE_' || l_sql_id;
  l_task_desc := 'Tuning SQL ' || l_sql_id;

  BEGIN
    dbms_sqltune.drop_tuning_task(l_task_name);
  EXCEPTION WHEN OTHERS THEN NULL;
  END;

  l_task_name := dbms_sqltune.create_tuning_task(
                   sql_id       => l_sql_id,
                   scope        => 'COMPREHENSIVE',
                   time_limit   => 1800,
                   task_name    => l_task_name,
                   description  => l_task_desc);

  dbms_sqltune.execute_tuning_task(task_name => l_task_name);

  dbms_output.put_line('Tuning task created and executed: ' || l_task_name);
  dbms_output.put_line('View with: SELECT * FROM TABLE(DBMS_SQLTUNE.REPORT_TUNING_TASK(''' || l_task_name || '''));');

EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('Error running tuning advisor: ' || SQLERRM);
END;
/

PROMPT --- Access Advisor Suggestion via SQL Tuning Set ---
DECLARE
  sts_name   VARCHAR2(30) := 'STS_' || '&sql_id';
  task_name  VARCHAR2(30) := 'ACC_ADV_' || '&sql_id';
BEGIN
  -- Clean up
  BEGIN dbms_sqltune.drop_sqlset(sts_name); EXCEPTION WHEN OTHERS THEN NULL; END;
  BEGIN dbms_advisor.delete_task(task_name); EXCEPTION WHEN OTHERS THEN NULL; END;

  -- Create SQL Tuning Set and load SQL
  dbms_sqltune.create_sqlset(sts_name);
  dbms_sqltune.capture_cursor_cache_sqlset(
    sqlset_name => sts_name,
    basic_filter => 'sql_id = ''' || '&sql_id' || '''',
    time_limit => 30);

  -- Run Access Advisor
  dbms_advisor.quick_tune(
    advisor_name => 'Access Advisor',
    task_name    => task_name,
    attr1        => sts_name);

  dbms_output.put_line('Access Advisor Task: ' || task_name);
  dbms_output.put_line('View: SELECT * FROM DBA_ADVISOR_RECOMMENDATIONS WHERE task_name = ''' || task_name || '''');

EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('Access Advisor Error: ' || SQLERRM);
END;
/

PROMPT --- Undo Advisory (if needed) ---
SELECT name, value, isdefault
  FROM v$parameter
 WHERE name IN ('undo_retention','undo_tablespace');

SELECT BEGIN_TIME, UNDOTSN, UNDOBLKS, TXNCOUNT, MAXQUERYLEN
  FROM   v$undostat
 WHERE  BEGIN_TIME BETWEEN TO_DATE('&bad_start','YYYY-MM-DD HH24:MI') AND TO_DATE('&bad_end','YYYY-MM-DD HH24:MI')
 ORDER BY BEGIN_TIME;

PROMPT Advisory diagnostics completed.

========================

-- Script: suggest_baseline.sql
-- Purpose: Check for existing SQL Baselines for a SQL_ID and optionally create one for good plan
-- RAC-aware, CDB/PDB-safe

SET SERVEROUTPUT ON
SET VERIFY OFF
SET LINES 200 PAGES 1000

PROMPT Checking existing baselines for SQL_ID: &sql_id

SELECT sql_handle, plan_name, enabled, accepted, fixed
  FROM dba_sql_plan_baselines
 WHERE sql_text LIKE (SELECT sql_fulltext FROM gv$sql WHERE sql_id = '&sql_id' AND ROWNUM = 1);

PROMPT Attempting to capture good plan baseline...

DECLARE
  l_sql_id       VARCHAR2(20) := '&sql_id';
  l_phv          NUMBER;
  l_cnt          NUMBER;
BEGIN
  SELECT plan_hash_value INTO l_phv
    FROM (
           SELECT plan_hash_value, COUNT(*) freq
             FROM dba_hist_sqlstat h
             JOIN dba_hist_snapshot s
               ON h.snap_id = s.snap_id AND h.dbid = s.dbid
            WHERE h.sql_id = l_sql_id
              AND s.begin_interval_time BETWEEN TO_DATE('&good_start','YYYY-MM-DD HH24:MI')
                                           AND TO_DATE('&good_end','YYYY-MM-DD HH24:MI')
            GROUP BY plan_hash_value
            ORDER BY freq DESC
         ) WHERE ROWNUM = 1;

  SELECT COUNT(*) INTO l_cnt
    FROM dba_sql_plan_baselines
   WHERE sql_text LIKE (SELECT sql_fulltext FROM gv$sql WHERE sql_id = l_sql_id AND ROWNUM = 1)
     AND plan_hash_value = l_phv;

  IF l_cnt = 0 THEN
    DBMS_OUTPUT.put_line('No baseline found for best plan. Capturing now...');
    DBMS_SPM.load_plans_from_cursor_cache(sql_id => l_sql_id, plan_hash_value => l_phv);
  ELSE
    DBMS_OUTPUT.put_line('Baseline for best plan already exists.');
  END IF;

EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.put_line('Error creating baseline: ' || SQLERRM);
END;
/

PROMPT Baseline check completed.


Popular posts from this blog

-
Image
How to shutdown a Single Instance Database in Oracle 19c Overview Shutdown is an operation performed when a command is issued by the Administrator to bring down the Database or when it receives a signal to bring down the Database incase of a failure/malfunction or due to SHUTDOWN ABORT comm and execution. The options used by t he Administrator to bring down the Database is called as Shutdown Modes and the Database Server follows a sequence to shutdown a Database . Let's check how does that happen. Concepts The following are the shutdown Modes. Shutdown Modes A database administrator with SYSDBA or SYSOPER privileges can shut down the database using the SQL*Plus SHUTDOWN command or Enterprise Manager. The SHUTDOWN command has the following options that determine the shutdown behavior. Now let's look into each SHUTDOWN option in detail. SHUTDOWN ABORT  This mode is used for emergency situations, such as when shutdown with "IMMEDIATE" option doesn't work. This mode o...
Read more
-
How to startup a Single Instance Database in Oracle 19c Overview An Oracle Database goes through various stages until it is made available to the users. They are categorized into the below stages. Concepts The following are the stages of the startup sequence. Nomount   The instance is started, but is not yet associated with a database. The stages are as follows:  1. Searches for a server parameter file in a platform-specific default location and, if not found, for a text initialization parameter file (specifying STARTUP with the SPFILE or PFILE parameters overrides the default behavior)  2. Reads the parameter file to determine the values of initialization parameters  3. Allocates the SGA based on the initialization parameter settings  4. Starts the Oracle background processes  5. Opens the alert log and trace files and writes all explicit parameter settings to the alert log in valid parameter syntax At this stage, no database is associated with the instanc...
Read more
-
 SET LINES 150 SET PAGES 1000 SET VERIFY OFF SET FEEDBACK OFF SET HEADING ON SET TRIMSPOOL ON SET LONG 10000 SET LONGCHUNKSIZE 10000 PROMPT =================================================== PROMPT       Oracle RAC Session Monitor - Full Version PROMPT =================================================== -- Step 1: Input Method PROMPT Choose input method: PROMPT Enter 'U' for Username or 'S' for SID/SERIAL# ACCEPT input_method CHAR DEFAULT 'U' -- Variables Initialization DEFINE sid = '' DEFINE serial = '' DEFINE instid = 1 DEFINE username = '' DEFINE sqlid = '' PROMPT -- Step 2: If Username mode, accept username and list sessions PROMPT Enter Username (leave blank if using SID/SERIAL#): ACCEPT username CHAR COLUMN inst_id_col NEW_VALUE instid COLUMN sid_col NEW_VALUE sid COLUMN serial_col NEW_VALUE serial SELECT inst_id, sid, serial#, status, program, module, sql_id, event FROM gv$session WHERE username = UPPER('&username...
Read more