-- =============================================================================
-- PERISCUBE LTD. | PRINCIPAL ARCHITECTURE & FINOPS DIAGNOSTIC PACK
-- File       : 01_smart_scan_and_spill_audit.sql
-- Platform   : Oracle Exadata 19c / 23ai RAC, Autonomous Database & ODI 12c
-- Author     : Principal Architects / Engineers (Periscube Ltd.)
-- Entity     : Periscube Ltd. (Registered in England & Wales, No. 07691565)
-- Website    : https://periscube.com/offers/exadata-finops-auditor/
-- License    : Free Permission Diagnostic Asset ($0) - Read-Only Execution
-- =============================================================================
-- SAFETY & SOVEREIGNTY GUARANTEE:
--   1. 100% READ-ONLY: Executes SELECT statements exclusively against Oracle
--      dynamic performance views (GV$) and data dictionary views (DBA_).
--   2. ZERO PRODUCTION IMPACT: Acquires zero DDL/DML locks, modifies zero
--      parameters, and executes in under 12 seconds with DOP capped at 1.
--   3. ZERO PII EXPOSURE: Never queries application tables or customer rows.
-- =============================================================================

SET LINESIZE 240
SET PAGESIZE 1000
SET TRIMOUT ON
SET TRIMSPOOL ON
SET VERIFY OFF
SET FEEDBACK ON

PROMPT =========================================================================
PROMPT [01/05] EXADATA SMART SCAN OFFLOAD EFFICIENCY & INTERCONNECT RATIO BY NODE
PROMPT Target Threshold: Smart Scan Offload Efficiency >= 75.0%
PROMPT =========================================================================

WITH cell_stats AS (
    SELECT
        inst_id,
        MAX(CASE WHEN name = 'cell physical IO interconnect bytes' THEN value ELSE 0 END) AS interconnect_bytes,
        MAX(CASE WHEN name = 'cell physical IO bytes eligible for predicate offload' THEN value ELSE 0 END) AS offload_eligible_bytes,
        MAX(CASE WHEN name = 'cell physical IO interconnect bytes returned by smart scan' THEN value ELSE 0 END) AS smart_scan_returned_bytes,
        MAX(CASE WHEN name = 'physical read total bytes' THEN value ELSE 0 END) AS total_phys_read_bytes,
        MAX(CASE WHEN name = 'cell flash cache read hits' THEN value ELSE 0 END) AS flash_cache_hits,
        MAX(CASE WHEN name = 'physical read total IO requests' THEN value ELSE 0 END) AS total_read_iops
    FROM gv$sysstat
    WHERE name IN (
        'cell physical IO interconnect bytes',
        'cell physical IO bytes eligible for predicate offload',
        'cell physical IO interconnect bytes returned by smart scan',
        'physical read total bytes',
        'cell flash cache read hits',
        'physical read total IO requests'
    )
    GROUP BY inst_id
)
SELECT
    inst_id AS rac_node,
    ROUND(total_phys_read_bytes / POWER(1024, 4), 2) AS total_phys_read_tb,
    ROUND(offload_eligible_bytes / POWER(1024, 4), 2) AS offload_eligible_tb,
    ROUND(interconnect_bytes / POWER(1024, 4), 2) AS interconnect_rx_tb,
    ROUND(
        CASE
            WHEN offload_eligible_bytes > 0
            THEN (1 - (smart_scan_returned_bytes / offload_eligible_bytes)) * 100
            ELSE 0
        END, 2
    ) AS smart_scan_reduction_pct,
    ROUND(
        CASE
            WHEN total_read_iops > 0
            THEN (flash_cache_hits / total_read_iops) * 100
            ELSE 0
        END, 2
    ) AS flash_cache_hit_pct,
    CASE
        WHEN offload_eligible_bytes = 0 THEN 'CRITICAL: SMART SCAN DISABLED / NO OFFLOAD'
        WHEN (1 - (smart_scan_returned_bytes / NULLIF(offload_eligible_bytes, 0))) * 100 < 60 THEN 'CRITICAL: SEVERE INTERCONNECT FLOODING (<60%)'
        WHEN (1 - (smart_scan_returned_bytes / NULLIF(offload_eligible_bytes, 0))) * 100 < 75 THEN 'WARNING: SUB-OPTIMAL CELL OFFLOAD (<75%)'
        ELSE 'HEALTHY: OPTIMAL EXADATA OFFLOAD (>=75%)'
    END AS diagnostic_verdict
FROM cell_stats
ORDER BY inst_id;

PROMPT =========================================================================
PROMPT [02/05] TOP 20 SMART SCAN BYPASS OFFENDERS & PREDICATE WRAPPER FORENSICS
PROMPT Detects high-I/O queries pulling un-offloaded blocks across RAC fabric
PROMPT =========================================================================

SELECT * FROM (
    SELECT
        s.inst_id,
        s.sql_id,
        s.plan_hash_value,
        s.parsing_schema_name AS schema_name,
        s.executions,
        ROUND(s.elapsed_time / 1e6 / GREATEST(s.executions, 1), 2) AS avg_ela_sec,
        ROUND(s.physical_read_bytes / POWER(1024, 3), 2) AS phys_read_gb,
        ROUND(s.io_cell_offload_eligible_bytes / POWER(1024, 3), 2) AS offload_elig_gb,
        ROUND(s.io_cell_offload_returned_bytes / POWER(1024, 3), 2) AS cell_returned_gb,
        ROUND(
            CASE
                WHEN s.physical_read_bytes > 0
                THEN (s.io_cell_offload_eligible_bytes / s.physical_read_bytes) * 100
                ELSE 0
            END, 1
        ) AS offload_elig_pct,
        SUBSTR(REGEXP_REPLACE(s.sql_text, '\s+', ' '), 1, 95) AS sql_snippet
    FROM gv$sql s
    WHERE s.physical_read_bytes > 10 * POWER(1024, 3) -- > 10 GB physical reads
      AND s.parsing_schema_name NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'AUDSYS')
    ORDER BY (s.physical_read_bytes - s.io_cell_offload_eligible_bytes) DESC
)
WHERE ROWNUM <= 20;

PROMPT =========================================================================
PROMPT [03/05] IMPLICIT TYPE CONVERSION & NON-SARGABLE PREDICATE WRAPPERS
PROMPT Identifies SYS_OP_C2C, NVL(), TRUNC(), and TO_CHAR() blinding Storage Indexes
PROMPT =========================================================================

SELECT * FROM (
    SELECT
        p.inst_id,
        p.sql_id,
        p.plan_hash_value,
        p.object_owner,
        p.object_name,
        p.operation || ' ' || p.options AS access_path,
        CASE
            WHEN p.filter_predicates LIKE '%INTERNAL_FUNCTION%' THEN 'IMPLICIT_TYPE_CAST_OR_IN_LIST'
            WHEN p.filter_predicates LIKE '%SYS_OP_C2C%' THEN 'CHARSET_CONVERSION_INDEX_BYPASS'
            WHEN p.filter_predicates LIKE '%NVL(%' OR p.filter_predicates LIKE '%COALESCE(%' THEN 'NULL_WRAPPER_STORAGE_INDEX_BYPASS'
            WHEN p.filter_predicates LIKE '%TRUNC(%' OR p.filter_predicates LIKE '%TO_CHAR(%' THEN 'DATE_FUNCTION_PARTITION_PRUNE_BYPASS'
            ELSE 'OTHER_FILTER_WRAPPER'
        END AS anti_pattern_type,
        SUBSTR(p.filter_predicates, 1, 110) AS offending_predicate
    FROM gv$sql_plan p
    WHERE p.operation = 'TABLE ACCESS'
      AND p.options LIKE '%FULL%'
      AND p.object_owner NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'XDB')
      AND (
          p.filter_predicates LIKE '%INTERNAL_FUNCTION%'
          OR p.filter_predicates LIKE '%SYS_OP_C2C%'
          OR p.filter_predicates LIKE '%NVL(%'
          OR p.filter_predicates LIKE '%TRUNC(%'
          OR p.filter_predicates LIKE '%TO_CHAR(%'
      )
    ORDER BY p.cost DESC NULLS LAST
)
WHERE ROWNUM <= 25;

PROMPT =========================================================================
PROMPT [04/05] TEMPORARY TABLESPACE SPILLS & HASH JOIN WORKAREA THRASHING
PROMPT Quantifies multi-pass disk spills starving nightly ELT and morning BI
PROMPT =========================================================================

SELECT * FROM (
    SELECT
        w.inst_id,
        w.sql_id,
        w.operation_type,
        w.policy,
        w.total_executions,
        w.optimal_executions,
        w.onepass_executions,
        w.multipasses_executions,
        ROUND(w.max_tempseg_size / POWER(1024, 3), 2) AS peak_temp_spill_gb,
        ROUND(w.last_tempseg_size / POWER(1024, 3), 2) AS last_temp_spill_gb,
        CASE
            WHEN w.multipasses_executions > 0 THEN 'CRITICAL: MULTI-PASS TEMP SPILL THRASHING'
            WHEN w.max_tempseg_size > 25 * POWER(1024, 3) THEN 'WARNING:SINGLE-QUERY SPILL > 25 GB'
            ELSE 'MODERATE TEMP SPILL'
        END AS spill_severity
    FROM gv$sql_workarea w
    WHERE w.max_tempseg_size > POWER(1024, 3) -- > 1 GB temp spill
    ORDER BY w.max_tempseg_size DESC
)
WHERE ROWNUM <= 20;

PROMPT =========================================================================
PROMPT [05/05] ACTIVE DBRM RESOURCE PLAN & UNINDEXED FOREIGN KEY LOCK RISKS
PROMPT Audits workload isolation between ODI/dbt batch and executive BI
PROMPT =========================================================================

SELECT
    inst_id,
    name AS active_resource_plan,
    is_top_plan,
    cpu_managed
FROM gv$rsrc_plan
ORDER BY inst_id;

SELECT * FROM (
    SELECT
        c.owner,
        c.table_name,
        c.constraint_name AS unindexed_fk_constraint,
        LISTAGG(cc.column_name, ', ') WITHIN GROUP (ORDER BY cc.position) AS fk_columns,
        'CRITICAL: TM ENQUEUE LOCK & FULL SCAN RISK ON PARENT DELETE/UPDATE' AS risk_diagnostic
    FROM dba_constraints c
    JOIN dba_cons_columns cc
      ON c.owner = cc.owner
     AND c.constraint_name = cc.constraint_name
    WHERE c.constraint_type = 'R'
      AND c.owner NOT IN (
          'SYS', 'SYSTEM', 'MDSYS', 'CTXSYS', 'XDB', 'WMSYS',
          'OLAPSYS', 'ORDDATA', 'GSMADMIN_INTERNAL', 'DBSNMP'
      )
      AND NOT EXISTS (
          SELECT 1
          FROM dba_ind_columns ic
          WHERE ic.table_owner = c.owner
            AND ic.table_name = c.table_name
            AND ic.column_name = cc.column_name
            AND ic.column_position = cc.position
      )
    GROUP BY c.owner, c.table_name, c.constraint_name
    ORDER BY c.owner, c.table_name
)
WHERE ROWNUM <= 30;

PROMPT =========================================================================
PROMPT AUDIT COMPLETE. To submit sanitized output for a 5-Day Principal Bottleneck
PROMPT Diagnostic (£2,500 flat fee, 3x ROI refund guarantee):
PROMPT https://periscube.com/offers/5-day-exadata-diagnostic/
PROMPT =========================================================================