SQL Execution & the Oracle Optimizer

-- Check when stats were last gathered — NULLs = NEVER analyzed!
SELECT owner, table_name, num_rows, blocks,
       last_analyzed,
       ROUND(SYSDATE - last_analyzed, 0) AS days_since_analyzed
FROM   dba_tables
WHERE  owner = 'APPS'
ORDER BY last_analyzed ASC NULLS FIRST;

-- Find tables with stale stats (>10% rows changed since last analyze)
SELECT owner, table_name, num_rows,
       stale_stats, last_analyzed
FROM   dba_tab_statistics
WHERE  owner = 'APPS'
AND    stale_stats = 'YES'
ORDER BY num_rows DESC;

-- Gather stats with histogram on a skewed column (e.g. STATUS)
EXEC DBMS_STATS.GATHER_TABLE_STATS(
  ownname    => 'APPS',
  tabname    => 'ORDERS',
  method_opt => 'FOR COLUMNS STATUS SIZE AUTO',
  cascade    => TRUE
);

-- Method 1: From cursor cache — ALLSTATS LAST shows actual row counts
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(
  sql_id          => 'abc123def456',
  cursor_child_no => NULL,
  format          => 'ALLSTATS LAST +PREDICATE +COST'
));

-- Method 2: EXPLAIN PLAN (estimated only — no runtime stats)
EXPLAIN PLAN FOR
SELECT o.order_id, c.name, SUM(oi.qty)
FROM   orders o
JOIN   customers c  ON c.id = o.customer_id
JOIN   order_items oi ON oi.order_id = o.order_id
WHERE  o.status = 'OPEN'
GROUP BY o.order_id, c.name;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'ALL'));

-- Method 3: For a currently running session
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(
  sql_id => (SELECT sql_id FROM v$session WHERE sid = 142)
));

-- Check column stats for predicate columns — look for missing histograms
SELECT column_name, num_distinct, num_nulls,
       density, histogram, num_buckets,
       last_analyzed
FROM   dba_tab_col_statistics
WHERE  table_name = 'ORDERS'
AND    owner      = 'APPS'
AND    column_name IN ('STATUS','CUSTOMER_ID','ORDER_DATE');

-- Histogram type meanings:
-- NONE        = no histogram (CBO assumes uniform distribution)
-- FREQUENCY   = one bucket per distinct value (low cardinality, skewed)
-- TOP-FREQUENCY = top N values tracked (12c+)
-- HEIGHT BALANCED = N buckets, equal rows per bucket
-- HYBRID      = combines frequency + height balanced (12c+, best)

-- Gather with histogram for skewed column:
EXEC DBMS_STATS.GATHER_TABLE_STATS(
  ownname    => 'APPS',
  tabname    => 'ORDERS',
  method_opt => 'FOR COLUMNS STATUS SIZE AUTO, ORDER_DATE SIZE AUTO',
  cascade    => TRUE
);

-- System-wide hard parse rate (target: hard/total < 5%)
SELECT
  hp.value                                         AS hard_parses,
  tp.value                                         AS total_parses,
  ROUND(hp.value * 100 / NULLIF(tp.value, 0), 1)  AS hard_parse_pct
FROM   v$sysstat hp, v$sysstat tp
WHERE  hp.name = 'parse count (hard)'
AND    tp.name = 'parse count (total)';

-- Find top hard-parse offenders
SELECT sql_id, parse_calls, executions,
       ROUND(parse_calls / NULLIF(executions,0), 2) AS parse_per_exec,
       SUBSTR(sql_text, 1, 80)                       AS sql_snippet
FROM   v$sqlarea
WHERE  parse_calls > 500
ORDER BY parse_calls DESC
FETCH FIRST 20 ROWS ONLY;

-- The smoking gun: SQL with literals — same query, different WHERE values
-- force_matching_signature groups SQL that are identical except for literals
SELECT force_matching_signature,
       COUNT(DISTINCT sql_id)  AS variant_count,
       SUM(parse_calls)         AS total_parses,
       MIN(SUBSTR(sql_text,1,100)) AS sample_sql
FROM   v$sqlarea
WHERE  parsing_schema_name NOT IN ('SYS','SYSTEM')
GROUP BY force_matching_signature
HAVING   COUNT(DISTINCT sql_id) > 50
ORDER BY total_parses DESC
FETCH FIRST 10 ROWS ONLY;

-- Find SQL with multiple child cursors (each child = a different plan)
SELECT sql_id,
       COUNT(*)         AS child_cursors,
       SUM(executions)  AS total_executions
FROM   v$sql
WHERE  parsing_schema_name NOT IN ('SYS','SYSTEM')
GROUP BY sql_id
HAVING   COUNT(*) > 3
ORDER BY child_cursors DESC
FETCH FIRST 20 ROWS ONLY;

-- See WHY each child cursor has a different plan
SELECT child_number,
       executions,
       rows_processed,
       is_bind_sensitive,   -- Y = diff binds could give diff plans
       is_bind_aware,       -- Y = adaptive cursor sharing active
       is_shareable
FROM   v$sql
WHERE  sql_id = '&sql_id'
ORDER BY child_number;

-- Compare plans for two different child cursors
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', 0, 'BASIC ROWS'));
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', 1, 'BASIC ROWS'));

-- Step 1: Create and execute a tuning task
DECLARE
  l_task VARCHAR2(30);
BEGIN
  l_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(
    sql_id     => 'abc123def456',
    scope      => DBMS_SQLTUNE.SCOPE_COMPREHENSIVE,
    time_limit => 120,          -- seconds
    task_name  => 'TUNE_ORDER_QUERY'
  );
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'TUNE_ORDER_QUERY');
END;
/

-- Step 2: Read the recommendations
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNE_ORDER_QUERY') FROM DUAL;

-- Step 3: Accept a SQL Profile if recommended
EXEC DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(
  task_name => 'TUNE_ORDER_QUERY',
  replace   => TRUE
);

-- Load the current good plan from cursor cache into SPM
DECLARE
  l_plans PLS_INTEGER;
BEGIN
  l_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
    sql_id          => 'abc123def456',
    plan_hash_value => 1234567890   -- from V$SQL
  );
  DBMS_OUTPUT.PUT_LINE('Plans loaded: ' || l_plans);
END;
/

-- Check what baselines exist
SELECT sql_handle, plan_name, enabled, accepted, fixed,
       elapsed_time, executions
FROM   dba_sql_plan_baselines
WHERE  sql_text LIKE '%orders%'
ORDER BY last_executed DESC;

-- Disable a baseline (stop using it without deleting)
DECLARE
  l_cnt PLS_INTEGER;
BEGIN
  l_cnt := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
    sql_handle  => '&sql_handle',
    plan_name   => '&plan_name',
    attribute_name  => 'ENABLED',
    attribute_value => 'NO'
  );
END;
/

-- STEP 1: Find the SQL
SELECT sql_id, sql_text
FROM   v$sql
WHERE  sql_text LIKE '%orders%status%'
AND    parsing_schema_name = 'APPS'
ORDER BY elapsed_time DESC
FETCH FIRST 5 ROWS ONLY;

-- STEP 2: Check execution stats
SELECT sql_id, executions,
       ROUND(elapsed_time/1e6/NULLIF(executions,0), 2)  AS avg_ela_secs,
       ROUND(cpu_time/1e6/NULLIF(executions,0), 2)    AS avg_cpu_secs,
       ROUND(buffer_gets/NULLIF(executions,0))          AS avg_lios,
       ROUND(disk_reads/NULLIF(executions,0))           AS avg_pios,
       ROUND(rows_processed/NULLIF(executions,0))        AS avg_rows
FROM   v$sql
WHERE  sql_id = '&sql_id';

-- STEP 3: Pull the execution plan with actual runtime stats
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(
  '&sql_id', NULL, 'ALLSTATS LAST +PREDICATE +COST'
));

-- STEP 4: Check statistics freshness on tables in the plan
SELECT table_name, num_rows, last_analyzed,
       ROUND(SYSDATE - last_analyzed) AS days_old
FROM   dba_tables
WHERE  table_name IN ('ORDERS','CUSTOMERS','ORDER_ITEMS')
AND    owner = 'APPS';

-- STEP 5: Check column stats on predicate columns
SELECT column_name, num_distinct, num_nulls, density,
       histogram, num_buckets
FROM   dba_tab_col_statistics
WHERE  table_name = 'ORDERS'
AND    owner = 'APPS'
AND    column_name IN ('STATUS','ORDER_DATE','CUSTOMER_ID');