table_stats_sqlid_v1.sql

ADVERTISEMENT

-------------------------------------------------------------------------------
-- Script:     TABLE_STATS_SQLID_v1.sql
-- Purpose:    Analyzes tables used in a specific SQL ID's execution plan and
--             reports key statistics including number of rows, histograms,
--             and last analyzed time.
--
-- Description:
--             This script extracts all tables (and index-related tables) used
--             in a SQL ID from GV$SQL_PLAN, checks for column histograms,
--             and joins with DBA_TABLES to provide stats overview.
--
-- Usage:
--             Run in SQL*Plus or SQL Developer.
--             Pass the SQL ID as a parameter:
--               @TABLE_STATS_SQLID_v1.sql <sql_id>
--
-- Output:
--             A list of tables with:
--               - Owner
--               - Table name
--               - Number of rows
--               - Number of histogram-based columns
--               - Last analyzed timestamp
--
-- Example:
--             @TABLE_STATS_ANALYSIS_BY_SQLID_v1.sql 9gkzfh4q0d12a
--
--             Output:
--             OWNER      TABLE_NAME     NUM_ROWS   HISTOGRAMS   LAST_ANALYZED
--             ---------- -------------- ---------- ------------ -------------------
--             HR         EMPLOYEES      107        2            18-JUN-25 10:45:00
--             HR         JOBS           19         1            17-JUN-25 08:30:00
--
-- Author:     W3Buddy
-- Version:    v1.0
-- Date:       2025-06-18
-------------------------------------------------------------------------------

CLEAR BREAKS
COL COLUMN_NAME    FOR A30
COL LAST_ANALYZED  FOR A20
COL OWNER          FOR A20
COL TABLE_NAME     FOR A50
SET VERIFY OFF
UNDEFINE SQL_ID
DEFINE SQL_ID = &1
SELECT 
  SUBSTR(tab_stat.tb, 1, INSTR(tab_stat.tb, '.', 1, 1) - 1) AS OWNER,
  SUBSTR(tab_stat.tb, INSTR(tab_stat.tb, '.', 1, 1) + 1) AS TABLE_NAME,
  tab_stat.num_rows,
  NVL(hist.histograms, 0) AS histograms,
  tab_stat.last_analyzed
FROM (
  SELECT owner || '.' || table_name AS tb,
         COUNT(*) AS histograms
  FROM dba_tab_columns
  WHERE num_buckets > 1
  GROUP BY owner || '.' || table_name
) hist
FULL OUTER JOIN (
  SELECT DISTINCT owner || '.' || table_name AS tb,
                  num_rows,
                  last_analyzed
  FROM dba_tables
  WHERE (owner, table_name) IN (
    SELECT DISTINCT object_owner, object_name
    FROM gv$sql_plan
    WHERE sql_id = '&SQL_ID'
  )
  UNION ALL
  SELECT DISTINCT owner || '.' || table_name AS tb,
                  num_rows,
                  last_analyzed
  FROM dba_tables
  WHERE (owner, table_name) IN (
    SELECT owner, table_name
    FROM dba_indexes
    WHERE (owner, index_name) IN (
      SELECT DISTINCT object_owner, object_name
      FROM gv$sql_plan
      WHERE sql_id = '&SQL_ID'
    )
  )
) tab_stat
ON hist.tb = tab_stat.tb
ORDER BY tab_stat.num_rows DESC;

ADVERTISEMENT