{"id":4323,"date":"2025-06-19T00:15:01","date_gmt":"2025-06-19T00:15:01","guid":{"rendered":"https:\/\/w3buddy.com\/?post_type=cposts&#038;p=4323"},"modified":"2026-09-26T13:07:21","modified_gmt":"2026-09-26T07:37:21","slug":"table_stats_sqlid_v1-sql","status":"publish","type":"post","link":"https:\/\/w3buddy.com\/blog\/table_stats_sqlid_v1-sql\/","title":{"rendered":"table_stats_sqlid_v1.sql"},"content":{"rendered":"\n<pre class=\"wp-block-code\"><code>-------------------------------------------------------------------------------\n-- Script:     TABLE_STATS_SQLID_v1.sql\n-- Purpose:    Analyzes tables used in a specific SQL ID's execution plan and\n--             reports key statistics including number of rows, histograms,\n--             and last analyzed time.\n--\n-- Description:\n--             This script extracts all tables (and index-related tables) used\n--             in a SQL ID from GV$SQL_PLAN, checks for column histograms,\n--             and joins with DBA_TABLES to provide stats overview.\n--\n-- Usage:\n--             Run in SQL*Plus or SQL Developer.\n--             Pass the SQL ID as a parameter:\n--               @TABLE_STATS_SQLID_v1.sql &lt;sql_id>\n--\n-- Output:\n--             A list of tables with:\n--               - Owner\n--               - Table name\n--               - Number of rows\n--               - Number of histogram-based columns\n--               - Last analyzed timestamp\n--\n-- Example:\n--             @TABLE_STATS_ANALYSIS_BY_SQLID_v1.sql 9gkzfh4q0d12a\n--\n--             Output:\n--             OWNER      TABLE_NAME     NUM_ROWS   HISTOGRAMS   LAST_ANALYZED\n--             ---------- -------------- ---------- ------------ -------------------\n--             HR         EMPLOYEES      107        2            18-JUN-25 10:45:00\n--             HR         JOBS           19         1            17-JUN-25 08:30:00\n--\n-- Author:     W3Buddy\n-- Version:    v1.0\n-- Date:       2025-06-18\n-------------------------------------------------------------------------------\n\nCLEAR BREAKS\nCOL COLUMN_NAME    FOR A30\nCOL LAST_ANALYZED  FOR A20\nCOL OWNER          FOR A20\nCOL TABLE_NAME     FOR A50\nSET VERIFY OFF\nUNDEFINE SQL_ID\nDEFINE SQL_ID = &amp;1\nSELECT \n  SUBSTR(tab_stat.tb, 1, INSTR(tab_stat.tb, '.', 1, 1) - 1) AS OWNER,\n  SUBSTR(tab_stat.tb, INSTR(tab_stat.tb, '.', 1, 1) + 1) AS TABLE_NAME,\n  tab_stat.num_rows,\n  NVL(hist.histograms, 0) AS histograms,\n  tab_stat.last_analyzed\nFROM (\n  SELECT owner || '.' || table_name AS tb,\n         COUNT(*) AS histograms\n  FROM dba_tab_columns\n  WHERE num_buckets > 1\n  GROUP BY owner || '.' || table_name\n) hist\nFULL OUTER JOIN (\n  SELECT DISTINCT owner || '.' || table_name AS tb,\n                  num_rows,\n                  last_analyzed\n  FROM dba_tables\n  WHERE (owner, table_name) IN (\n    SELECT DISTINCT object_owner, object_name\n    FROM gv$sql_plan\n    WHERE sql_id = '&amp;SQL_ID'\n  )\n  UNION ALL\n  SELECT DISTINCT owner || '.' || table_name AS tb,\n                  num_rows,\n                  last_analyzed\n  FROM dba_tables\n  WHERE (owner, table_name) IN (\n    SELECT owner, table_name\n    FROM dba_indexes\n    WHERE (owner, index_name) IN (\n      SELECT DISTINCT object_owner, object_name\n      FROM gv$sql_plan\n      WHERE sql_id = '&amp;SQL_ID'\n    )\n  )\n) tab_stat\nON hist.tb = tab_stat.tb\nORDER BY tab_stat.num_rows DESC;<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"googlesitekit_rrm_CAowu461DA:productID":"","footnotes":""},"categories":[1225,992],"tags":[],"class_list":["post-4323","post","type-post","status-publish","format-standard","hentry","category-database","category-performance-tuning"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/4323","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/comments?post=4323"}],"version-history":[{"count":2,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/4323\/revisions"}],"predecessor-version":[{"id":4330,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/4323\/revisions\/4330"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=4323"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=4323"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/tags?post=4323"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}