================================================================================
RUNBOOK : Stats refresh + Cursor purge + SQL Tuning Advisor
SQL_ID : cytp5j8ddvg1p
PDB : CONF03 (Oracle 19c, 19.32)
Run as : SYSDBA / DBA user
================================================================================
SEQUENCE SUMMARY
----------------
Phase 0 : Prepare session
Phase 1 : Capture baseline (BEFORE any change)
Phase 2 : Statistics refresh
Phase 3 : Check active sessions and decide (wait / cancel / kill)
Phase 4 : Purge the cursor
Phase 5 : Validate new plan after next execution
Phase 6 : SQL Tuning Advisor
Phase 7 : Implement findings (only with approval) + rollback
Phase 8 : Close out and document
NOTE: Placeholders in <angle brackets> must be replaced before running.
================================================================================
PHASE 0 : PREPARE THE SESSION
================================================================================
ALTER SESSION SET CONTAINER = CONF03;
SHOW CON_NAME
SET TIMING ON LINESIZE 250 PAGESIZE 200 LONG 1000000 LONGCHUNKSIZE 1000000
SPOOL /tmp/cytp5j8ddvg1p_tuning.log
================================================================================
PHASE 1 : CAPTURE BASELINE (BEFORE ANY CHANGE)
================================================================================
-- 1.1 Current cursors in memory
SELECT child_number, plan_hash_value, executions,
ROUND(elapsed_time/NULLIF(executions,0)/1e6,2) sec_per_exec,
ROUND(buffer_gets/NULLIF(executions,0)) gets_per_exec,
ROUND(disk_reads/NULLIF(executions,0)) reads_per_exec,
users_executing, last_active_time
FROM v$sql
WHERE sql_id = 'cytp5j8ddvg1p'
ORDER BY last_active_time DESC;
-- 1.2 Parent cursor and version count
SELECT RAWTOHEX(address) address, hash_value, version_count, executions, users_executing
FROM v$sqlarea
WHERE sql_id = 'cytp5j8ddvg1p';
-- 1.3 Current execution plan
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('cytp5j8ddvg1p', NULL, 'TYPICAL'));
-- 1.4 Historical performance from AWR
-- (If no rows inside the PDB, run from CDB$ROOT - AWR may be CDB-level only)
SELECT s.snap_id,
TO_CHAR(sn.begin_interval_time,'DD-MON HH24:MI') begin_time,
s.plan_hash_value,
s.executions_delta execs,
ROUND(s.elapsed_time_delta/NULLIF(s.executions_delta,0)/1e6,2) sec_per_exec,
ROUND(s.buffer_gets_delta/NULLIF(s.executions_delta,0)) gets_per_exec
FROM dba_hist_sqlstat s
JOIN dba_hist_snapshot sn
ON sn.snap_id = s.snap_id
AND sn.dbid = s.dbid
AND sn.instance_number = s.instance_number
WHERE s.sql_id = 'cytp5j8ddvg1p'
ORDER BY s.snap_id;
-- Save all Phase 1 output. This is the "before" evidence.
================================================================================
PHASE 2 : STATISTICS
================================================================================
-- 2.1 Tables used by this SQL and their stats status
WITH plan_objs AS (
SELECT DISTINCT object_owner, object_name, object_type
FROM v$sql_plan
WHERE sql_id = 'cytp5j8ddvg1p' AND object_owner IS NOT NULL
), tabs AS (
SELECT object_owner owner, object_name table_name
FROM plan_objs WHERE object_type LIKE 'TABLE%'
UNION
SELECT i.table_owner, i.table_name
FROM plan_objs p
JOIN dba_indexes i ON i.owner = p.object_owner AND i.index_name = p.object_name
WHERE p.object_type LIKE 'INDEX%'
)
SELECT s.owner, s.table_name, s.num_rows, s.last_analyzed, s.stale_stats, s.stattype_locked
FROM dba_tab_statistics s
JOIN tabs t ON s.owner = t.owner AND s.table_name = t.table_name
WHERE s.partition_name IS NULL
ORDER BY s.last_analyzed NULLS FIRST;
-- Flag: STALE_STATS = YES, LAST_ANALYZED old/NULL, STATTYPE_LOCKED not null.
-- 2.2 Dictionary stats [DONE - Elapsed 02:01:27.56]
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
-- 2.3 Fixed object stats [DONE - Elapsed 00:05:13.74]
EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
-- 2.4 Application table stats - ONLY for tables flagged in 2.1
-- Skip tables with locked stats unless owner approves unlocking.
-- If EBS database (use FND_STATS, not DBMS_STATS):
EXEC FND_STATS.GATHER_TABLE_STATS(ownname => '<OWNER>', tabname => '<TABLE>', percent => 10, degree => 4, cascade => TRUE);
-- If NOT EBS:
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => '<OWNER>', tabname => '<TABLE>', cascade => TRUE, degree => 4);
-- 2.5 Verify refresh
SELECT 'DICTIONARY' type, MAX(last_analyzed) last_analyzed
FROM dba_tab_statistics WHERE owner = 'SYS' AND object_type = 'TABLE'
UNION ALL
SELECT 'FIXED', MAX(last_analyzed)
FROM dba_tab_statistics WHERE object_type = 'FIXED TABLE';
-- Re-run 2.1: flagged tables should show today's date and STALE_STATS = NO.
-- NOTE: In 19c, stats gathering uses rolling invalidation (AUTO_INVALIDATE).
-- Existing cursors keep the OLD plan -> that is why Phase 4 purge is needed.
================================================================================
PHASE 3 : CHECK ACTIVE SESSIONS AND DECIDE
================================================================================
-- 3.1 Who is running the SQL now
SELECT inst_id, sid, serial#, username, module, action, program,
sql_child_number, sql_exec_start,
ROUND(last_call_et/60,1) mins_in_call, event, blocking_session
FROM gv$session
WHERE sql_id = 'cytp5j8ddvg1p' AND status = 'ACTIVE';
-- Last seen: SID 1730 (serial# 53354, child 147), SID 2462 (serial# 3052, child 302)
-- NOTE: 147 and 302 are CHILD CURSOR numbers, not SIDs.
-- 3.2 (EBS only) Map sessions to concurrent requests
SELECT r.request_id, p.user_concurrent_program_name, r.phase_code, r.status_code,
r.actual_start_date, s.sid, s.serial#
FROM gv$session s
JOIN apps.fnd_concurrent_requests r ON r.oracle_session_id = s.audsid
JOIN apps.fnd_concurrent_programs_tl p
ON p.concurrent_program_id = r.concurrent_program_id
AND p.application_id = r.program_application_id
AND p.language = 'US'
WHERE s.sql_id = 'cytp5j8ddvg1p';
-- 3.3 DECISION
-- a) Normal runtime -> WAIT. Re-run 3.1 every few minutes until 0 rows.
-- b) Stuck + owner approves -> CANCEL concurrent request from EBS Requests screen.
-- c) No app cancel possible
-- + owner approves -> KILL database session:
ALTER SYSTEM KILL SESSION '<sid>,<serial#>,@<inst_id>' IMMEDIATE;
-- Record the decision and who approved it.
-- Killing is NOT required for the purge. Default recommendation: WAIT.
================================================================================
PHASE 4 : PURGE THE CURSOR
================================================================================
-- 4.1 Confirm nothing is executing it (must return 0)
SELECT COUNT(*) active_sessions
FROM gv$session
WHERE sql_id = 'cytp5j8ddvg1p' AND status = 'ACTIVE';
-- 4.2 Verify hash value (expect 450739253)
SELECT DBMS_UTILITY.SQLID_TO_SQLHASH('cytp5j8ddvg1p') hash FROM dual;
-- 4.3 Purge (reads the CURRENT address itself - no stale value)
SET SERVEROUTPUT ON
DECLARE
l_handle VARCHAR2(100);
BEGIN
SELECT RAWTOHEX(address) || ',' || hash_value
INTO l_handle
FROM v$sqlarea
WHERE sql_id = 'cytp5j8ddvg1p';
DBMS_OUTPUT.PUT_LINE('Purging: ' || l_handle);
DBMS_SHARED_POOL.PURGE(l_handle, 'C');
DBMS_OUTPUT.PUT_LINE('Purge call completed.');
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Cursor not in shared pool - nothing to purge.');
END;
/
-- Manual equivalent (only if address in 1.2 still matches):
-- EXEC DBMS_SHARED_POOL.PURGE('070001022BE31768,450739253','C');
-- 4.4 Confirm purge
SELECT child_number, plan_hash_value, users_executing, last_load_time
FROM v$sql
WHERE sql_id = 'cytp5j8ddvg1p';
-- No rows -> purge worked.
-- Old children remain -> they were pinned; wait for sessions to finish, repeat 4.3.
================================================================================
PHASE 5 : VALIDATE NEW PLAN (after the SQL runs again)
================================================================================
-- 5.1 Re-run query 1.1 and compare with baseline:
-- plan_hash_value, sec_per_exec, gets_per_exec, recent last_load_time.
-- 5.2 New plan
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('cytp5j8ddvg1p', NULL, 'TYPICAL'));
-- 5.3 Watch child cursor build-up (PQ mismatch seen in V$SQL_SHARED_CURSOR)
SELECT version_count FROM v$sqlarea WHERE sql_id = 'cytp5j8ddvg1p';
-- If version_count climbs quickly toward hundreds, check parallel degree:
SELECT owner, table_name, degree FROM dba_tables
WHERE (owner, table_name) IN (('<OWNER>','<TABLE>'))
UNION ALL
SELECT owner, index_name, degree FROM dba_indexes
WHERE table_owner = '<OWNER>' AND table_name = '<TABLE>';
================================================================================
PHASE 6 : SQL TUNING ADVISOR
================================================================================
-- 6.1a Create task from CURSOR CACHE (if SQL is back in memory)
DECLARE
l_task VARCHAR2(64);
BEGIN
l_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_id => 'cytp5j8ddvg1p',
scope => 'COMPREHENSIVE',
time_limit => 1800,
task_name => 'STA_cytp5j8ddvg1p',
description => 'STA after stats refresh and cursor purge');
END;
/
-- 6.1b OR create task from AWR (if SQL not in memory)
-- Find snapshot range first:
SELECT snap_id, begin_interval_time
FROM dba_hist_snapshot
ORDER BY snap_id DESC FETCH FIRST 48 ROWS ONLY;
DECLARE
l_task VARCHAR2(64);
BEGIN
l_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(
begin_snap => <begin_snap>,
end_snap => <end_snap>,
sql_id => 'cytp5j8ddvg1p',
scope => 'COMPREHENSIVE',
time_limit => 1800,
task_name => 'STA_cytp5j8ddvg1p');
END;
/
-- 6.2 Execute task
EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'STA_cytp5j8ddvg1p');
-- 6.3 Check status
SELECT task_name, status, execution_start, execution_end
FROM dba_advisor_log
WHERE task_name = 'STA_cytp5j8ddvg1p';
-- 6.4 Report
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('STA_cytp5j8ddvg1p') FROM dual;
-- Note each finding (stats / index / SQL profile / restructure) with estimated benefit.
================================================================================
PHASE 7 : IMPLEMENT (ONLY WITH APPROVAL)
================================================================================
-- 7.1 Accept SQL profile (if recommended)
EXEC DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(task_name => 'STA_cytp5j8ddvg1p', name => 'PROF_cytp5j8ddvg1p', force_match => TRUE);
-- 7.2 Verify profile in use
SELECT name, status, created FROM dba_sql_profiles WHERE name = 'PROF_cytp5j8ddvg1p';
SELECT child_number, plan_hash_value, sql_profile
FROM v$sql WHERE sql_id = 'cytp5j8ddvg1p';
-- Repeat Phase 5 to confirm improvement.
-- 7.3 ROLLBACK (if worse)
EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'PROF_cytp5j8ddvg1p');
-- Index recommendations -> separate change request; create and test in QA first.
================================================================================
PHASE 8 : CLOSE OUT
================================================================================
-- 8.1 Drop tuning task (ONLY after report is saved)
EXEC DBMS_SQLTUNE.DROP_TUNING_TASK('STA_cytp5j8ddvg1p');
-- 8.2 Stop log
SPOOL OFF
-- 8.3 Write-up checklist
-- [ ] Stats timings: dictionary 02:01:27.56, fixed objects 00:05:13.74, tables (2.4)
-- [ ] Session decision (wait / cancel / kill) and approver
-- [ ] Purge time and result
-- [ ] Before / after: plan_hash_value, sec_per_exec, gets_per_exec, version_count
-- [ ] STA findings and what was applied / rejected
-- [ ] Rollback steps kept ready
================================================================================
END OF RUNBOOK
================================================================================
No comments:
Post a Comment