Saturday, August 23, 2025

REAL TIME PERFORMANCE TUNING

 

PLAN CHANGE:
=============

SET SERVEROUTPUT ON;
SET LINESIZE 200;
SET PAGESIZE 1000;
SET LONG 1000000;

DECLARE
  v_sql_id VARCHAR2(13) := '&sql_id'; -- Replace with your SQL_ID
  v_start_date VARCHAR2(20) := '&start_date'; -- Format: DD-MON-YYYY
  v_end_date VARCHAR2(20) := '&end_date'; -- Format: DD-MON-YYYY
  v_dbid NUMBER;
  
  CURSOR plan_change_cur IS
    SELECT 
      ss.snap_id,
      ss.instance_number,
      ss.dbid,
      TO_CHAR(ss.begin_interval_time, 'DD-MON-YYYY HH24:MI') AS begin_interval_time,
      s.sql_id,
      s.plan_hash_value,
      NVL(s.executions_delta, 0) AS executions,
      ROUND((s.elapsed_time_delta / DECODE(NVL(s.executions_delta, 0), 0, 1, s.executions_delta)) / 1000000, 3) AS avg_etime,
      ROUND((s.buffer_gets_delta / DECODE(NVL(s.executions_delta, 0), 0, 1, s.executions_delta)), 1) AS avg_lio
    FROM 
      dba_hist_sqlstat s
      JOIN dba_hist_snapshot ss ON ss.snap_id = s.snap_id 
        AND ss.instance_number = s.instance_number
    WHERE 
      s.sql_id = v_sql_id
      AND ss.begin_interval_time >= TO_DATE(v_start_date, 'DD-MON-YYYY')
      AND ss.end_interval_time <= TO_DATE(v_end_date, 'DD-MON-YYYY') + 1
      AND s.executions_delta > 0
    ORDER BY 
      ss.snap_id, ss.instance_number;

  v_prev_plan_hash NUMBER := 0;
  v_prev_dbid NUMBER;
  v_plan_change_detected BOOLEAN := FALSE;
  v_report CLOB;
BEGIN
  -- Fetch DBID (assuming consistent across snapshots; take first one)
  SELECT dbid INTO v_dbid FROM dba_hist_snapshot WHERE ROWNUM = 1;

  DBMS_OUTPUT.PUT_LINE('SNAP_ID | INSTANCE | BEGIN_INTERVAL_TIME      | SQL_ID        | PLAN_HASH_VALUE | EXECUTIONS | AVG_ETIME (sec) | AVG_LIO');
  DBMS_OUTPUT.PUT_LINE('--------|----------|-------------------------|---------------|-----------------|------------|-----------------|---------');

  FOR rec IN plan_change_cur LOOP
    -- Check for plan change
    IF v_prev_plan_hash != rec.plan_hash_value AND v_prev_plan_hash != 0 THEN
      v_plan_change_detected := TRUE;
      DBMS_OUTPUT.PUT_LINE('*** PLAN CHANGE DETECTED *** from ' || v_prev_plan_hash || ' to ' || rec.plan_hash_value || 
                           ' at SNAP_ID ' || rec.snap_id || ' on ' || rec.begin_interval_time);
      
      -- Automated comparison using DBMS_XPLAN.COMPARE_PLANS (19c+)
      BEGIN
        v_report := DBMS_XPLAN.COMPARE_PLANS(
                      reference_plan    => DBMS_XPLAN.AWR_PLAN_OBJECT(v_sql_id, v_prev_dbid, NULL, v_prev_plan_hash),
                      compare_plan_list => DBMS_XPLAN.PLAN_OBJECT_LIST(DBMS_XPLAN.AWR_PLAN_OBJECT(v_sql_id, rec.dbid, NULL, rec.plan_hash_value)),
                      type              => 'TEXT',
                      level             => 'TYPICAL',
                      section           => 'ALL'
                    );
        DBMS_OUTPUT.PUT_LINE(CHR(10) || 'Plan Comparison Report:' || CHR(10) || v_report);
      EXCEPTION
        WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE('Comparison not available (pre-19c or error): ' || SQLERRM);
          -- Fallback: Display both plans
          DBMS_OUTPUT.PUT_LINE(CHR(10) || 'Previous Plan (HASH: ' || v_prev_plan_hash || '):');
          FOR plan_line IN (SELECT plan FROM TABLE(DBMS_XPLAN.DISPLAY_WORKLOAD_REPOSITORY(v_sql_id, v_prev_plan_hash))) LOOP
            DBMS_OUTPUT.PUT_LINE(plan_line.plan);
          END LOOP;
          DBMS_OUTPUT.PUT_LINE(CHR(10) || 'New Plan (HASH: ' || rec.plan_hash_value || '):');
          FOR plan_line IN (SELECT plan FROM TABLE(DBMS_XPLAN.DISPLAY_WORKLOAD_REPOSITORY(v_sql_id, rec.plan_hash_value))) LOOP
            DBMS_OUTPUT.PUT_LINE(plan_line.plan);
          END LOOP;
      END;
    END IF;

    -- Output the record
    DBMS_OUTPUT.PUT_LINE(
      RPAD(rec.snap_id, 8) || '|' ||
      RPAD(rec.instance_number, 10) || '|' ||
      RPAD(rec.begin_interval_time, 25) || '|' ||
      RPAD(rec.sql_id, 15) || '|' ||
      RPAD(rec.plan_hash_value, 17) || '|' ||
      RPAD(rec.executions, 12) || '|' ||
      RPAD(rec.avg_etime, 16) || '|' ||
      rec.avg_lio
    );

    v_prev_plan_hash := rec.plan_hash_value;
    v_prev_dbid := rec.dbid;
  END LOOP;

  IF NOT v_plan_change_detected THEN
    DBMS_OUTPUT.PUT_LINE('No plan changes detected for SQL_ID ' || v_sql_id || ' in the specified period.');
  END IF;

  -- Optional: Raw differences via DBA_HIST_SQL_PLAN (for manual review)
  -- Uncomment if needed for deeper analysis
  /*
  DBMS_OUTPUT.PUT_LINE(CHR(10) || 'Raw Plan Differences (Operations in Prev but not New):');
  SELECT * FROM (
    SELECT * FROM dba_hist_sql_plan WHERE sql_id = v_sql_id AND plan_hash_value = v_prev_plan_hash
    MINUS
    SELECT * FROM dba_hist_sql_plan WHERE sql_id = v_sql_id AND plan_hash_value = rec.plan_hash_value
  );
  */

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

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

SELECT plan_hash_value,
       sql_id,
       sql_text,
       SUM(rows_processed) AS total_rows_processed,
       COUNT(*) AS execution_count
FROM v$sql
WHERE executions > 0
GROUP BY plan_hash_value, sql_id, sql_text
ORDER BY total_rows_processed DESC;

SELECT
    plan_hash_value,
    sql_id,
    sql_text,
    rows_processed AS total_rows_processed_lifetime, -- This is the cumulative total
    executions AS total_executions,
    CASE
        WHEN executions > 0 THEN ROUND(rows_processed / executions)
        ELSE 0
    END AS avg_rows_per_execution
FROM
    v$sql
WHERE
    executions > 0
ORDER BY
    avg_rows_per_execution DESC;



SELECT plan_hash_value,
       sql_id,
       SUM(rows_processed_total) AS total_rows_processed,
       SUM(executions_total) AS execution_count
FROM dba_hist_sqlstat
WHERE parsing_schema_name NOT IN ('SYS', 'SYSTEM')
  AND snapshot_id IN (
    SELECT snapshot_id
    FROM dba_hist_snapshot
    WHERE begin_interval_time >= TO_DATE('2025-08-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
      AND end_interval_time <= TO_DATE('2025-08-23 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
  )
GROUP BY plan_hash_value, sql_id
ORDER BY total_rows_processed DESC;




WITH FileLines AS (
  SELECT text, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS line_number,
         COUNT(*) OVER () AS total_lines
  FROM TABLE(rdsadmin.rds_file_util.read_text_file('OPatch', 'lsinv.txt'))
),
Last50Lines AS (
  SELECT text, line_number
  FROM FileLines
  WHERE line_number > total_lines - 50
),
BugLines AS (
  SELECT line_number
  FROM Last50Lines
  WHERE UPPER(text) LIKE '%12345678%'
)
SELECT l.text, l.line_number
FROM Last50Lines l
JOIN BugLines b
  ON l.line_number BETWEEN b.line_number - 5 AND b.line_number + 5
ORDER BY l.line_number;

SELECT
    plan_hash_value,
    sql_id,
    rows_processed_total AS total_rows_processed_lifetime,
    executions_total AS total_executions,
    CASE
        WHEN executions_total > 0 THEN ROUND(rows_processed_total / executions_total)
        ELSE 0
    END AS avg_rows_per_execution
FROM dba_hist_sqlstat
WHERE executions_total > 0
  AND parsing_schema_name NOT IN ('SYS', 'SYSTEM')
  AND snapshot_id IN (
    SELECT snapshot_id
    FROM dba_hist_snapshot
    WHERE begin_interval_time >= TO_DATE('2025-08-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
      AND end_interval_time <= TO_DATE('2025-08-23 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
  )
ORDER BY avg_rows_per_execution DESC;
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Type id :
Enter mode (REAL_TIME or AWR): AWR
Enter type (ID or TEXT): ID
Enter SQL_ID or SQL_TEXT: 7x8y9z0a1b2c3
Enter generate plan display statements? (Y/N): Y
Type Text
Enter mode (REAL_TIME or AWR): AWR
Enter type (ID or TEXT): TEXT
Enter SQL_ID or SQL_TEXT: SELECT * FROM employees
Enter generate plan display statements? (Y/N): Y

SET SERVEROUTPUT ON SIZE UNLIMITED
ACCEPT mode PROMPT 'Enter mode (REAL_TIME or AWR): '
ACCEPT type PROMPT 'Enter type (ID or TEXT): '
ACCEPT identifier PROMPT 'Enter SQL_ID or SQL_TEXT: '
ACCEPT generate_plans PROMPT 'Generate plan display statements? (Y/N): '

BEGIN
  DECLARE
    v_mode VARCHAR2(10) := UPPER('&mode');
    v_type VARCHAR2(4) := UPPER('&type');
    v_identifier VARCHAR2(4000) := '&identifier';
    v_generate_plans CHAR(1) := UPPER('&generate_plans');
    v_sql_id VARCHAR2(13);
    v_best_phv NUMBER;
    v_worst_phv NUMBER;
    v_best_elapsed NUMBER := 999999999;
    v_worst_elapsed NUMBER := 0;
    v_initial_phv NUMBER;
    v_initial_time DATE;
    v_first_change_time DATE;
    v_sql_id_exists NUMBER;
  BEGIN
    -- Validate mode
    IF v_mode NOT IN ('REAL_TIME', 'AWR') THEN
      DBMS_OUTPUT.PUT_LINE('Error: Invalid mode specified (must be REAL_TIME or AWR).');
      RETURN;
    END IF;
    
    -- Validate type
    IF v_type NOT IN ('ID', 'TEXT') THEN
      DBMS_OUTPUT.PUT_LINE('Error: Invalid type specified (must be ID or TEXT).');
      RETURN;
    END IF;
    
    -- Determine SQL_ID
    BEGIN
      IF v_type = 'ID' THEN
        v_sql_id := v_identifier;
      ELSIF v_type = 'TEXT' THEN
        IF v_mode = 'REAL_TIME' THEN
          SELECT sql_id INTO v_sql_id 
          FROM v$sql 
          WHERE sql_text LIKE '%' || v_identifier || '%' 
          AND ROWNUM = 1;
        ELSIF v_mode = 'AWR' THEN
          SELECT sql_id INTO v_sql_id 
          FROM dba_hist_sqltext 
          WHERE DBMS_LOB.SUBSTR(sql_text, 4000, 1) LIKE '%' || v_identifier || '%' 
          AND ROWNUM = 1;
          DBMS_OUTPUT.PUT_LINE('Note: SQL_TEXT search in AWR mode is limited to the first 4000 characters of the statement.');
        END IF;
      END IF;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Error: No SQL_ID found for the provided identifier.');
        RETURN;
    END;
    
    -- Validate SQL_ID existence
    IF v_mode = 'REAL_TIME' THEN
      SELECT COUNT(*) INTO v_sql_id_exists
      FROM v$sql
      WHERE sql_id = v_sql_id;
    ELSIF v_mode = 'AWR' THEN
      SELECT COUNT(*) INTO v_sql_id_exists
      FROM dba_hist_sqlstat
      WHERE sql_id = v_sql_id;
    END IF;
    
    IF v_sql_id_exists = 0 THEN
      DBMS_OUTPUT.PUT_LINE('Error: SQL_ID ' || v_sql_id || ' not found in ' || v_mode || ' data.');
      RETURN;
    END IF;
    
    DBMS_OUTPUT.PUT_LINE(' ');
    DBMS_OUTPUT.PUT_LINE('=== Plan Change Analysis for SQL_ID: ' || v_sql_id || ' ===');
    DBMS_OUTPUT.PUT_LINE('Mode: ' || v_mode || ' | Input Type: ' || v_type);
    
    IF v_mode = 'REAL_TIME' THEN
      -- History from V$SQL
      DBMS_OUTPUT.PUT_LINE(' ');
      DBMS_OUTPUT.PUT_LINE('PHV History (per child cursor):');
      FOR rec IN (
        SELECT plan_hash_value, child_number, 
               TO_DATE(first_load_time, 'YYYY-MM-DD/HH24:MI:SS') first_load_time,
               TO_DATE(last_load_time, 'YYYY-MM-DD/HH24:MI:SS') last_load_time,
               last_active_time,
               executions,
               NVL(elapsed_time / NULLIF(executions, 0) / 1000000, 0) avg_elapsed_sec,
               NVL(buffer_gets / NULLIF(executions, 0), 0) avg_buffer_gets,
               NVL(disk_reads / NULLIF(executions, 0), 0) avg_disk_reads
        FROM v$sql
        WHERE sql_id = v_sql_id
        ORDER BY first_load_time
      ) LOOP
        DBMS_OUTPUT.PUT_LINE('PHV: ' || rec.plan_hash_value || ' | Child: ' || rec.child_number || 
                             ' | First Load: ' || TO_CHAR(rec.first_load_time, 'YYYY-MM-DD HH24:MI:SS') ||
                             ' | Last Load: ' || TO_CHAR(rec.last_load_time, 'YYYY-MM-DD HH24:MI:SS') ||
                             ' | Last Active: ' || TO_CHAR(rec.last_active_time, 'YYYY-MM-DD HH24:MI:SS') ||
                             ' | Executions: ' || rec.executions ||
                             ' | Avg Elapsed (sec): ' || ROUND(rec.avg_elapsed_sec, 4) ||
                             ' | Avg Buffer Gets: ' || ROUND(rec.avg_buffer_gets) ||
                             ' | Avg Disk Reads: ' || ROUND(rec.avg_disk_reads));
        
        -- Update best/worst based on avg_elapsed_sec (only if executions > 0)
        IF rec.executions > 0 THEN
          IF rec.avg_elapsed_sec < v_best_elapsed THEN
            v_best_elapsed := rec.avg_elapsed_sec;
            v_best_phv := rec.plan_hash_value;
          END IF;
          IF rec.avg_elapsed_sec > v_worst_elapsed THEN
            v_worst_elapsed := rec.avg_elapsed_sec;
            v_worst_phv := rec.plan_hash_value;
          END IF;
        END IF;
      END LOOP;
      
      -- Determine initial PHV and first change time
      BEGIN
        SELECT plan_hash_value, TO_DATE(first_load_time, 'YYYY-MM-DD/HH24:MI:SS')
        INTO v_initial_phv, v_initial_time
        FROM v$sql
        WHERE sql_id = v_sql_id
        ORDER BY TO_DATE(first_load_time, 'YYYY-MM-DD/HH24:MI:SS')
        FETCH FIRST 1 ROW ONLY;
        
        DBMS_OUTPUT.PUT_LINE(' ');
        DBMS_OUTPUT.PUT_LINE('Initial PHV: ' || v_initial_phv || ' at ' || TO_CHAR(v_initial_time, 'YYYY-MM-DD HH24:MI:SS'));
        
        SELECT MIN(TO_DATE(first_load_time, 'YYYY-MM-DD/HH24:MI:SS'))
        INTO v_first_change_time
        FROM v$sql
        WHERE sql_id = v_sql_id AND plan_hash_value != v_initial_phv;
        
        IF v_first_change_time IS NOT NULL THEN
          DBMS_OUTPUT.PUT_LINE('First PHV change date: ' || TO_CHAR(v_first_change_time, 'YYYY-MM-DD HH24:MI:SS'));
        ELSE
          DBMS_OUTPUT.PUT_LINE('No plan changes detected.');
        END IF;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          DBMS_OUTPUT.PUT_LINE('No data available in V$SQL for this SQL_ID.');
      END;
      
      DBMS_OUTPUT.PUT_LINE('Best PHV (lowest avg elapsed): ' || v_best_phv || ' with ' || ROUND(v_best_elapsed, 4) || ' sec');
      DBMS_OUTPUT.PUT_LINE('Worst PHV (highest avg elapsed): ' || v_worst_phv || ' with ' || ROUND(v_worst_elapsed, 4) || ' sec');
      
      IF v_generate_plans = 'Y' THEN
        DBMS_OUTPUT.PUT_LINE(' ');
        DBMS_OUTPUT.PUT_LINE('Generated plan display statements (run these separately):');
        DBMS_OUTPUT.PUT_LINE('SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(''' || v_sql_id || ''', (SELECT child_number FROM v$sql WHERE sql_id = ''' || v_sql_id || ''' AND plan_hash_value = ' || v_best_phv || ' AND ROWNUM = 1), ''ALLSTATS LAST'')); -- For best PHV');
        DBMS_OUTPUT.PUT_LINE('SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(''' || v_sql_id || ''', (SELECT child_number FROM v$sql WHERE sql_id = ''' || v_sql_id || ''' AND plan_hash_value = ' || v_worst_phv || ' AND ROWNUM = 1), ''ALLSTATS LAST'')); -- For worst PHV');
      END IF;
      
    ELSIF v_mode = 'AWR' THEN
      -- History from DBA_HIST_SQLSTAT
      DBMS_OUTPUT.PUT_LINE(' ');
      DBMS_OUTPUT.PUT_LINE('Detailed Snapshot History:');
      FOR rec IN (
        SELECT ss.snap_id, TO_CHAR(ss.begin_interval_time, 'YYYY-MM-DD HH24:MI') begin_time,
               s.plan_hash_value, s.executions_delta executions,
               NVL(s.elapsed_time_delta / NULLIF(s.executions_delta, 0) / 1000000, 0) avg_elapsed_sec,
               NVL(s.buffer_gets_delta / NULLIF(s.executions_delta, 0), 0) avg_buffer_gets,
               NVL(s.disk_reads_delta / NULLIF(s.executions_delta, 0), 0) avg_disk_reads
        FROM dba_hist_sqlstat s
        JOIN dba_hist_snapshot ss ON s.snap_id = ss.snap_id
        WHERE s.sql_id = v_sql_id
        ORDER BY ss.begin_interval_time
      ) LOOP
        DBMS_OUTPUT.PUT_LINE('Snap ID: ' || rec.snap_id || ' | Begin Time: ' || rec.begin_time ||
                             ' | PHV: ' || rec.plan_hash_value ||
                             ' | Executions: ' || rec.executions ||
                             ' | Avg Elapsed (sec): ' || ROUND(rec.avg_elapsed_sec, 4) ||
                             ' | Avg Buffer Gets: ' || ROUND(rec.avg_buffer_gets) ||
                             ' | Avg Disk Reads: ' || ROUND(rec.avg_disk_reads));
      END LOOP;
      
      -- Aggregated per PHV
      DBMS_OUTPUT.PUT_LINE(' ');
      DBMS_OUTPUT.PUT_LINE('Aggregated PHV History:');
      FOR rec IN (
        SELECT plan_hash_value,
               MIN(ss.begin_interval_time) first_appear_time,
               SUM(s.elapsed_time_delta) / NULLIF(SUM(s.executions_delta), 0) / 1000000 overall_avg_elapsed,
               SUM(s.executions_delta) total_executions
        FROM dba_hist_sqlstat s
        JOIN dba_hist_snapshot ss ON s.snap_id = ss.snap_id
        WHERE s.sql_id = v_sql_id
        GROUP BY plan_hash_value
        ORDER BY MIN(ss.begin_interval_time)
      ) LOOP
        DBMS_OUTPUT.PUT_LINE('PHV: ' || rec.plan_hash_value || 
                             ' | First Appeared: ' || TO_CHAR(rec.first_appear_time, 'YYYY-MM-DD HH24:MI:SS') ||
                             ' | Total Executions: ' || rec.total_executions ||
                             ' | Overall Avg Elapsed (sec): ' || ROUND(rec.overall_avg_elapsed, 4));
        
        -- Update best/worst (only if total_executions > 0)
        IF rec.total_executions > 0 THEN
          IF rec.overall_avg_elapsed < v_best_elapsed THEN
            v_best_elapsed := rec.overall_avg_elapsed;
            v_best_phv := rec.plan_hash_value;
          END IF;
          IF rec.overall_avg_elapsed > v_worst_elapsed THEN
            v_worst_elapsed := rec.overall_avg_elapsed;
            v_worst_phv := rec.plan_hash_value;
          END IF;
        END IF;
      END LOOP;
      
      -- Determine first change time
      DECLARE
        v_count INTEGER := 0;
      BEGIN
        FOR rec IN (
          SELECT MIN(ss.begin_interval_time) min_time
          FROM dba_hist_sqlstat s
          JOIN dba_hist_snapshot ss ON s.snap_id = ss.snap_id
          WHERE s.sql_id = v_sql_id
          GROUP BY plan_hash_value
          ORDER BY min_time
        ) LOOP
          v_count := v_count + 1;
          IF v_count = 1 THEN
            v_initial_time := rec.min_time;
          ELSIF v_count = 2 THEN
            v_first_change_time := rec.min_time;
            EXIT;
          END IF;
        END LOOP;
        
        IF v_first_change_time IS NOT NULL THEN
          DBMS_OUTPUT.PUT_LINE('First PHV change date: ' || TO_CHAR(v_first_change_time, 'YYYY-MM-DD HH24:MI:SS'));
        ELSE
          DBMS_OUTPUT.PUT_LINE('No plan changes detected.');
        END IF;
      END;
      
      DBMS_OUTPUT.PUT_LINE('Best PHV (lowest overall avg elapsed): ' || v_best_phv || ' with ' || ROUND(v_best_elapsed, 4) || ' sec');
      DBMS_OUTPUT.PUT_LINE('Worst PHV (highest overall avg elapsed): ' || v_worst_phv || ' with ' || ROUND(v_worst_elapsed, 4) || ' sec');
      
      IF v_generate_plans = 'Y' THEN
        DBMS_OUTPUT.PUT_LINE(' ');
        DBMS_OUTPUT.PUT_LINE('Generated plan display statements (run these separately):');
        DBMS_OUTPUT.PUT_LINE('SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR(''' || v_sql_id || ''', ' || v_best_phv || ', NULL, ''ALL'')); -- For best PHV');
        DBMS_OUTPUT.PUT_LINE('SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR(''' || v_sql_id || ''', ' || v_worst_phv || ', NULL, ''ALL'')); -- For worst PHV');
      END IF;
    END IF;
  END;
END;
/



```sql
SET SERVEROUTPUT ON SIZE UNLIMITED
ACCEPT sql_id PROMPT 'Enter SQL_ID: '

BEGIN
  DECLARE
    v_sql_id VARCHAR2(13) := '&sql_id';
    v_task_name VARCHAR2(30);
    v_task_status VARCHAR2(30);
  BEGIN
    -- Validate sql_id
    IF v_sql_id IS NULL OR LENGTH(TRIM(v_sql_id)) != 13 THEN
      DBMS_OUTPUT.PUT_LINE('Error: Invalid SQL_ID provided. It must be a 13-character string.');
      RETURN;
    END IF;

    -- Submit SQL Tuning Advisor Task
    DBMS_OUTPUT.PUT_LINE(' ');
    DBMS_OUTPUT.PUT_LINE('=== SQL Tuning Advisor Analysis for SQL_ID: ' || v_sql_id || ' ===');
    v_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
        sql_id => v_sql_id,
        task_name => 'TUNE_' || v_sql_id || '_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS'),
        scope => DBMS_SQLTUNE.SCOPE_COMPREHENSIVE,
        time_limit => 3600, -- 1 hour
        description => 'SQL Tuning Task for sql_id ' || v_sql_id
    );
    DBMS_OUTPUT.PUT_LINE('SQL Tuning Task Created: ' || v_task_name);

    -- Execute the tuning task
    DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => v_task_name);

    -- Wait for task completion (poll status)
    LOOP
      SELECT status INTO v_task_status
      FROM dba_advisor_tasks
      WHERE task_name = v_task_name;

      EXIT WHEN v_task_status IN ('COMPLETED', 'INTERRUPTED', 'ERROR');
      DBMS_LOCK.SLEEP(10); -- Wait 10 seconds before checking again
    END LOOP;

    DBMS_OUTPUT.PUT_LINE('SQL Tuning Task Status: ' || v_task_status);

    -- Display tuning recommendations
    DBMS_OUTPUT.PUT_LINE(' ');
    DBMS_OUTPUT.PUT_LINE('SQL Tuning Advisor Recommendations:');
    DBMS_OUTPUT.PUT_LINE(DBMS_SQLTUNE.REPORT_TUNING_TASK(
        task_name => v_task_name,
        type => 'TEXT',
        level => 'TYPICAL',
        section => 'ALL'
    ));
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Error in SQL Tuning Advisor process: ' || SQLERRM);
  END;
END;
/

SELECT task_name, status, error_message
FROM dba_advisor_log
WHERE task_name = 'TUNE_&sql_id_%';


```
```sql

Script 1: Create, Submit, and View SQL Tuning Task Recommendations

This script creates a SQL Tuning Advisor task, submits it with a 2-hour timeout, and displays recommendations. It includes validation to prevent ORA-13780.


SET SERVEROUTPUT ON SIZE UNLIMITED
ACCEPT sql_id PROMPT 'Enter SQL_ID: '

BEGIN
  DECLARE
    v_sql_id VARCHAR2(13) := '&sql_id';
    v_task_name VARCHAR2(30);
    v_task_status VARCHAR2(30);
    v_sql_id_exists NUMBER;
  BEGIN
    -- Validate sql_id
    IF v_sql_id IS NULL OR LENGTH(TRIM(v_sql_id)) != 13 THEN
      DBMS_OUTPUT.PUT_LINE('Error: Invalid SQL_ID provided. It must be a 13-character string.');
      RETURN;
    END IF;

    -- Check if sql_id exists in V$SQL or DBA_HIST_SQLTEXT
    SELECT COUNT(*)
    INTO v_sql_id_exists
    FROM (
      SELECT sql_id FROM v$sql WHERE sql_id = v_sql_id
      UNION
      SELECT sql_id FROM dba_hist_sqltext WHERE sql_id = v_sql_id
    );

    IF v_sql_id_exists = 0 THEN
      DBMS_OUTPUT.PUT_LINE('Error: SQL_ID ' || v_sql_id || ' not found in V$SQL or DBA_HIST_SQLTEXT. ORA-13780: SQL statement does not exist.');
      DBMS_OUTPUT.PUT_LINE('Please verify the SQL_ID or execute the SQL statement to ensure it is captured in V$SQL or AWR.');
      RETURN;
    END IF;

    -- Submit SQL Tuning Advisor Task
    DBMS_OUTPUT.PUT_LINE(' ');
    DBMS_OUTPUT.PUT_LINE('=== SQL Tuning Advisor Analysis for SQL_ID: ' || v_sql_id || ' ===');
    v_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
        sql_id => v_sql_id,
        task_name => 'TUNE_' || v_sql_id || '_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS'),
        scope => DBMS_SQLTUNE.SCOPE_COMPREHENSIVE,
        time_limit => 7200, -- 2 hours
        description => 'SQL Tuning Task for sql_id ' || v_sql_id
    );
    DBMS_OUTPUT.PUT_LINE('SQL Tuning Task Created: ' || v_task_name);

    -- Execute the tuning task
    DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => v_task_name);

    -- Wait for task completion (poll status)
    LOOP
      SELECT status INTO v_task_status
      FROM dba_advisor_tasks
      WHERE task_name = v_task_name;

      EXIT WHEN v_task_status IN ('COMPLETED', 'INTERRUPTED', 'ERROR');
      DBMS_LOCK.SLEEP(10); -- Wait 10 seconds before checking again
    END LOOP;

    DBMS_OUTPUT.PUT_LINE('SQL Tuning Task Status: ' || v_task_status);

    -- Display tuning recommendations
    DBMS_OUTPUT.PUT_LINE(' ');
    DBMS_OUTPUT.PUT_LINE('SQL Tuning Advisor Recommendations:');
    DBMS_OUTPUT.PUT_LINE(DBMS_SQLTUNE.REPORT_TUNING_TASK(
        task_name => v_task_name,
        type => 'TEXT',
        level => 'TYPICAL',
        section => 'ALL'
    ));
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Error in SQL Tuning Advisor process: ' || SQLERRM);
      IF SQLERRM LIKE '%ORA-13780%' THEN
        DBMS_OUTPUT.PUT_LINE('ORA-13780: SQL statement does not exist. Ensure the SQL_ID exists in V$SQL or DBA_HIST_SQLTEXT.');
      END IF;
  END;
END;
/
```

```sql

Script 2: Create SQL Profile for SQL_ID and Best PHV

This script creates a SQL Profile, either by accepting a recommended profile from the tuning task (if &task_name is provided) or by manually creating one for the provided best_phv. It includes validation to ensure sql_id and best_phv exist.


SET SERVEROUTPUT ON SIZE UNLIMITED
ACCEPT sql_id PROMPT 'Enter SQL_ID: '
ACCEPT best_phv PROMPT 'Enter Best PHV (from previous analysis, or press Enter to skip manual SQL Profile creation): '
ACCEPT task_name PROMPT 'Enter SQL Tuning Task Name (from previous script, or press Enter to skip recommended profile): '

BEGIN
DECLARE
v_sql_id VARCHAR2(13) := '&sql_id';
v_best_phv NUMBER := TO_NUMBER(NULLIF('&best_phv', ''));
v_task_name VARCHAR2(30) := NULLIF('&task_name', '');
v_sql_profile_name VARCHAR2(30);
v_sql_text CLOB;
v_sql_id_exists NUMBER;
v_phv_exists NUMBER;
BEGIN
-- Validate sql_id
IF v_sql_id IS NULL OR LENGTH(TRIM(v_sql_id)) != 13 THEN
DBMS_OUTPUT.PUT_LINE('Error: Invalid SQL_ID provided. It must be a 13-character string.');
RETURN;
END IF;

-- Check if sql_id exists in V$SQL or DBA_HIST_SQLTEXT
SELECT COUNT(*)
INTO v_sql_id_exists
FROM (
SELECT sql_id FROM v$sql WHERE sql_id = v_sql_id
UNION
SELECT sql_id FROM dba_hist_sqltext WHERE sql_id = v_sql_id
);

IF v_sql_id_exists = 0 THEN
DBMS_OUTPUT.PUT_LINE('Error: SQL_ID ' || v_sql_id || ' not found in V$SQL or DBA_HIST_SQLTEXT.');
RETURN;
END IF;

-- Check for existing SQL Profile or accept recommended profile
IF v_task_name IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE('=== Checking for SQL Profile from Tuning Task: ' || v_task_name || ' ===');
BEGIN
SELECT name INTO v_sql_profile_name
FROM dba_sql_profiles
WHERE task_id = (SELECT task_id FROM dba_advisor_tasks WHERE task_name = v_task_name)
AND status = 'ENABLED'
AND ROWNUM = 1;

DBMS_OUTPUT.PUT_LINE('SQL Profile Already Exists: ' || v_sql_profile_name);
EXCEPTION
WHEN NO_DATA_FOUND THEN
-- Accept SQL Profile if recommended
FOR rec IN (
SELECT object_name
FROM dba_advisor_recommendations r
JOIN dba_advisor_objects o ON r.rec_id = o.object_id
WHERE r.task_name = v_task_name
AND o.type = 'SQL PROFILE'
) LOOP
DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(
task_name => v_task_name,
object_name => rec.object_name,
name => 'PROFILE_' || v_sql_id || '_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS'),
force_match => TRUE
);
DBMS_OUTPUT.PUT_LINE('SQL Profile Created: PROFILE_' || v_sql_id || '_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS'));
END LOOP;
IF SQL%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE('No SQL Profile recommended by the tuning task.');
END IF;
END;
END IF;

-- If no SQL Profile was created and best_phv is provided, create one manually
IF v_best_phv IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE('=== Attempting to Create SQL Profile for Best PHV: ' || v_best_phv || ' ===');

-- Validate best_phv
SELECT COUNT(*)
INTO v_phv_exists
FROM v$sql
WHERE sql_id = v_sql_id
AND plan_hash_value = v_best_phv
UNION
SELECT COUNT(*)
FROM dba_hist_sql_plan
WHERE sql_id = v_sql_id
AND plan_hash_value = v_best_phv;

IF v_phv_exists = 0 THEN
DBMS_OUTPUT.PUT_LINE('Error: Best PHV ' || v_best_phv || ' not found for SQL_ID ' || v_sql_id || ' in V$SQL or DBA_HIST_SQL_PLAN.');
RETURN;
END IF;

BEGIN
-- Get the sql_text (try V$SQL first, then DBA_HIST_SQLTEXT)
BEGIN
SELECT sql_text INTO v_sql_text
FROM v$sql
WHERE sql_id = v_sql_id
AND plan_hash_value = v_best_phv
AND ROWNUM = 1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
SELECT sql_text INTO v_sql_text
FROM dba_hist_sqltext
WHERE sql_id = v_sql_id
AND ROWNUM = 1;
END;

-- Create SQL Profile using plan from V$SQL_PLAN or DBA_HIST_SQL_PLAN
DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
sql_text => v_sql_text,
profile => (SELECT NVL(
(SELECT attribute_value
FROM v$sql_plan
WHERE sql_id = v_sql_id
AND plan_hash_value = v_best_phv
AND attribute_name = 'plan'
AND ROWNUM = 1),
(SELECT other_xml
FROM dba_hist_sql_plan
WHERE sql_id = v_sql_id
AND plan_hash_value = v_best_phv
AND other_xml IS NOT NULL
AND ROWNUM = 1)
) AS profile
FROM dual),
name => 'PROFILE_BEST_' || v_sql_id || '_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS'),
category => 'DEFAULT',
force_match => TRUE
);
DBMS_OUTPUT.PUT_LINE('SQL Profile Created for Best PHV: PROFILE_BEST_' || v_sql_id || '_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS'));
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No plan details found for best PHV ' || v_best_phv || '. SQL Profile not created.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error creating SQL Profile for best PHV: ' || SQLERRM);
END;
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error in SQL Profile creation process: ' || SQLERRM);
END;
END;
/
```

Changes Made to Address ORA-13780 and Improve Robustness

  1. Enhanced SQL_ID Validation (Both Scripts):
    • Added checks to verify sql_id exists in V$SQL or DBA_HIST_SQLTEXT before proceeding.
    • If not found, provides a specific error message with guidance to prevent ORA-13780.
  2. Extended Timeout (Script 1):
    • Maintained time_limit => 7200 (2 hours) to reduce the likelihood of timeouts, as per your previous request.
  3. Improved PHV Handling (Script 2):
    • Added validation to check if best_phv exists in V$SQL or DBA_HIST_SQL_PLAN.
    • Modified SQL Profile creation to try V$SQL for sql_text first, then fall back to DBA_HIST_SQLTEXT if needed.
    • Used NVL to select plan details from V$SQL_PLAN or DBA_HIST_SQL_PLAN (using other_xml for AWR plans), ensuring compatibility with both REAL_TIME and AWR modes.
  4. Error Handling:
    • Script 1 catches ORA-13780 specifically and provides guidance.
    • Script 2 includes detailed error messages for missing sql_id, best_phv, or plan details.

Usage Instructions

Script 1: Create, Submit, and View SQL Tuning Task Recommendations

  1. Save and Run:
    • Save as sql_tuning_task.sql.
    • Execute in SQL*Plus or SQL Developer with SET SERVEROUTPUT ON.
    • Example input:
      text
      Enter SQL_ID: 7x8y9z0a1b2c3
  2. Output:
    • Validates sql_id and aborts with a message if not found.
    • Displays task name (e.g., TUNE_7x8y9z0a1b2c3_20250823_144022).
    • Shows task status (COMPLETED, INTERRUPTED, or ERROR).
    • Prints SQL Tuning Advisor recommendations.
  3. Note the Task Name:
    • Copy the task name for use in Script 2 if you want to accept a recommended SQL Profile.
  4. Troubleshooting:
    • If ORA-13780 persists, verify sql_id:
      sql
      SELECT sql_id, DBMS_LOB.SUBSTR(sql_text, 100, 1) AS sql_text_snippet
      FROM v$sql
      WHERE sql_id = '&sql_id'
      UNION
      SELECT sql_id, DBMS_LOB.SUBSTR(sql_text, 100, 1)
      FROM dba_hist_sqltext
      WHERE sql_id = '&sql_id';
    • If not found, execute the SQL statement to reload it into V$SQL or check AWR snapshots.

Script 2: Create SQL Profile

  1. Save and Run:
    • Save as create_sql_profile.sql.
    • Execute in SQL*Plus or SQL Developer with SET SERVEROUTPUT ON.
    • Example inputs:
      text
      Enter SQL_ID: 7x8y9z0a1b2c3
      Enter Best PHV (from previous analysis, or press Enter to skip manual SQL Profile creation): 1234567890
      Enter SQL Tuning Task Name (from previous script, or press Enter to skip recommended profile): TUNE_7x8y9z0a1b2c3_20250823_144022
      or, if skipping recommended profile:
      text
      Enter SQL_ID: 7x8y9z0a1b2c3
      Enter Best PHV (from previous analysis, or press Enter to skip manual SQL Profile creation): 1234567890
      Enter SQL Tuning Task Name (from previous script, or press Enter to skip recommended profile):
  2. Output:
    • Validates sql_id and best_phv.
    • Checks for an existing or recommended SQL Profile (if task_name is provided).
    • Creates a manual SQL Profile for best_phv if provided and no recommended profile was created.
    • Reports errors (e.g., missing sql_id, best_phv, or plan details).
  3. Verify SQL Profile:
    • Check created profiles:
      sql
      SELECT name, status, created
      FROM dba_sql_profiles
      WHERE sql_id = '&sql_id';

Additional Notes

  • Preventing ORA-13780:
    • The updated scripts check sql_id existence in both V$SQL and DBA_HIST_SQLTEXT, reducing the chance of ORA-13780.
    • If the error persists, ensure the SQL statement is executed recently (for V$SQL) or captured in AWR snapshots (for DBA_HIST_SQLTEXT).
  • Timeout Handling:
    • Script 1 uses time_limit => 7200 (2 hours). If timeouts still occur, increase to 10800 (3 hours) or higher:
      sql
      time_limit => 10800
    • Check session timeout settings (IDLE_TIME):
      sql
      SELECT profile, resource_name, limit
      FROM dba_profiles
      WHERE resource_name = 'IDLE_TIME';
      sql
      ALTER SESSION SET resource_limit = FALSE;
  • AWR Plans:
    • Script 2 now supports DBA_HIST_SQL_PLAN for best_phv plans, making it more robust for AWR mode.
  • Permissions:
    • Requires ADMINISTER SQL TUNING SET or DBA role for DBMS_SQLTUNE and DBMS_LOCK.
    • Verify Diagnostics and Tuning Pack:
      sql
      SELECT parameter, value
      FROM v$option
      WHERE parameter IN ('Oracle Diagnostics Pack', 'Oracle Tuning Pack');
  • Monitoring Performance:
    • After creating a SQL Profile, verify its impact:
      sql
      SELECT sql_id, plan_hash_value, executions, elapsed_time / NULLIF(executions, 0) / 1000000 AS avg_elapsed_sec
      FROM v$sql
      WHERE sql_id = '&sql_id'
      AND executions > 0;
  • Plain SQL Alternative:
    • A pure plain SQL version for these tasks is limited due to the procedural nature of DBMS_SQLTUNE. If needed, I can provide a partial plain SQL version (similar to the earlier response), but it will require manual steps for task execution and profile creation.

Testing Recommendations

  • Test Environment: Use a non-production database to avoid unintended changes.
  • Verify Inputs:
    • Ensure &sql_id exists in V$SQL or DBA_HIST_SQLTEXT.
    • Use best_phv from prior analysis (e.g., from the plan change analysis script).
    • Provide task_name from Script 1 if checking for recommended profiles.
  • Check AWR Snapshots:
    • If using AWR mode, ensure the SQL is in DBA_HIST_SQLTEXT and snapshots are recent.
  • Handle Timeouts:
    • If the task times out, check DBA_ADVISOR_LOG and retry with a higher time_limit.

If you encounter further issues (e.g., persistent ORA-13780, timeouts, or need a plain SQL version), please provide details (e.g., Oracle version, exact error stack, or input values), and I’ll tailor the solution further!

Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)