在实际项目中的SQL优化中，经常需要对SQL涉及的表、视图等信息进行分析收集，包括但不限于表DDL、行数、统计信息、列筛选性、索引等。然而，当SQL较长的时候，面对冗长的SQL与执行计划，同时涉及的对象较多的时候，思路往往因为频繁的表分析操作而经常被打乱；又或者，在遇到自己难以解决的SQL优化的时候，需要收集所有的关于SQL的信息而向上反馈给专家的时候，免不了对所有关于该SQL有用的信息进行收集。因此，基于上述各个情况，本人突发奇想，设计一个SQL脚本，通过提供SQL与对应的模式，自动分析收集并返回涉及该SQL的所有表对象、视图、同义词等对象信息，同时对涉及的表进行全方面信息收集，帮助DBA一次性获取全部信息，从而以更全局的角度分析优化或者向上反馈。
该脚本目前以包的方式实现（也曾考虑其他编程语言获得更高的执行效率，但是最终还是觉得使用脚本更为方便，一方面不依赖任何外部环境，一方面避免了保密性强的项目因传文件流程复杂导致的繁琐，毕竟复复制粘贴即可），使用方法也很简单，执行包内存储过程 call db_tools.sql_analyse(‘模式名’,‘SQL’)即可。
以下是脚本文件：

CREATE OR REPLACE PACKAGE SYSDBA.DB_TOOLS
AS
procedure TAB_RELYING(TAB_OWNER in VARCHAR2(100),TAB_NAME varchar2(200));
procedure SQL_ANALYSE(SQL_SCH VARCHAR2(100),SQL_TXT TEXT);
function tab_count(TAB_OWNER in VARCHAR2(100),TAB_NAME varchar2(200))return bigint;
END DB_TOOLS;
CREATE OR REPLACE PACKAGE BODY  DB_TOOLS
AS
--1、求所有表依赖的存储过程
procedure TAB_RELYING(TAB_OWNER in VARCHAR2(100),TAB_NAME varchar2(200))
   	AS
begin 
select distinct owner,table_name from (select A.OWNER,A.TABLE_NAME,C.OWNER "R_OWNER",C.TABLE_NAME "R_TABLE_NAME" from DBA_CONSTRAINTS A,DBA_CONSTRAINTS C where A.R_OWNER=C.owner and A.R_CONSTRAINT_NAME=C.CONSTRAINT_NAME ) CONS 
CONNECT BY (prior OWNER=R_OWNER and prior TABLE_NAME=R_TABLE_NAME)
START WITH R_owner=TAB_OWNER and R_table_name=TAB_NAME ;
END TAB_RELYING;
--2、查询行数
function tab_count(TAB_OWNER in VARCHAR2(100),TAB_NAME varchar2(200))return bigint
as
v_count bigint;
begin
execute IMMEDIATE 'select count(*) from '||TAB_OWNER||'.'||TAB_NAME into v_count;
return v_count;
end tab_count;
--3、分析SQL使用的子函数
PROCEDURE SQL_ANALYSE(SQL_SCH VARCHAR2(100),SQL_TXT TEXT) 
as 

TMP_V_NAME varchar2(1000) :='TMP_SHOW_ALL_SQL_RELYING'||to_char(sysdate,'YYYYMMDDHH24MISS');
begin
execute IMMEDIATE 'CREATE VIEW '||SQL_SCH||'.'||TMP_V_NAME||' as '||SQL_TXT;

select  /*+case_when_cvt_ifun(64)*/
case when (B.column_id=1 or B.column_id is null) then A."模式名" else null end "模式名",
case when (B.column_id=1 or B.column_id is null) then A."对象名" else null end "对象名",
case when (B.column_id=1 or B.column_id is null) then A."对象类型" else null end "对象类型",
case when (B.column_id=1 or B.column_id is null) then A."对象DDL" else null end "对象DDL",
case when (B.column_id=1 or B.column_id is null) then A."实际行数" else null end "实际行数",
B."列名",B."类型",
B."统计信息行数",B."采样个数",B."基数",
CASE WHEN (B."统计信息行数"=0) then 0 else round(B."基数"/B."统计信息行数" * 100, 2)end  "选择性",
B."空值个数",B."桶数",B."最后收集时间",ind_cols.index_info "列索引信息"
 from (select distinct R_OWNER "模式名",R_NAME "对象名",R_TYPE "对象类型",(select dbms_metadata.get_ddl(R_TYPE,R_NAME,R_OWNER)from dual) "对象DDL",level "LEVEL",
 case when R_TYPE='TABLE' then (select tab_count(R_OWNER,R_NAME)) else null end "实际行数"
  from (
select A.OWNER,A.NAME,A."TYPE",A.REFERENCED_OWNER "R_OWNER",A.REFERENCED_NAME "R_NAME",A.REFERENCED_TYPE "R_TYPE"
from DBA_DEPENDENCIES A
left OUTER join DBA_DEPENDENCIES B
on A.REFERENCED_OWNER=B.OWNER and A.REFERENCED_NAME=B.NAME and A."REFERENCED_TYPE"=B."TYPE"
)
CONNECT BY ((prior R_OWNER="OWNER" or (prior R_OWNER is null and "OWNER" is null)) and prior R_NAME=NAME and prior R_TYPE="TYPE" )
START WITH OWNER=SQL_SCH and NAME=TMP_V_NAME )A
left OUTER join 
(select tab.owner "模式名",
       tab.object_name "表名",
       col.column_name "列名",
       col.DATA_TYPE "类型",
       st.t_total "统计信息行数",
       N_SAMPLE "采样个数",
       st.n_distinct "基数",
       st.n_null "空值个数",
       st.n_buckets "桶数",
       st.last_gathered "最后收集时间",
       col.column_id
  from dba_objects tab
  join DBA_TAB_COLUMNS col
    on tab.owner = col.owner
   and tab.object_name = col.table_name
  join sysstats st
    on st.id = tab.object_id
   and col.column_id = st.colid + 1
where st.t_flag = 'C')B 
on A."模式名"=B."模式名" and A."对象名"=B."表名" 
left join (select table_owner,table_name,dic.column_name,listagg('索引名：'||dic.index_name||'--位置：第'||dic.column_position||'号'||dic.descend,chr(13)) within group(order by dic.column_name  asc) index_info
from dba_ind_columns dic
group by dic.table_owner,dic.table_name,dic.column_name) ind_cols
  	on ind_cols.TABLE_OWNER=A."模式名"
  	and ind_cols.COLUMN_NAME=B."列名"
  	and B."表名"=ind_cols.TABLE_NAME order by "LEVEL",A."模式名",A."对象名",B.column_id
;
execute IMMEDIATE 'drop view '||SQL_SCH||'.'||TMP_V_NAME;
end;
END DB_TOOLS;

下面以一个SQL举例：
假设有视图TEST_V_1，TEST_V_2，视图TEST_V_1依赖表INFORMATION_EXCHANGE_T，DEPT_T，视图TEST_V_2依赖表MAINT_READINGRELATE_T，USER_T，另外还有表USER_EXCHANGE，现在构造SQL（假设模式名为ESMS）：

select count(*) from TEST_V_1 A,TEST_V_2 B,USER_EXCHANGE C where A.ID=B.ID and B.ID=C.ID;

想要一次性分析SQL，希望获取的信息自然是上述提及的所有表的信息，同时视图本身定义也不能缺少，现在执行如下：

call db_tools.SQL_ANALYSE('ESMS','select * from TEST_V_1 A,TEST_V_2 B,USER_EXCHANGE C where A.ID=B.ID and B.ID=C.ID');

返回：

如上图，所有依赖表信息均可被收集。
尽管如此目前该脚本存在以下限制：
1、不支持带？参数执行，如果带参，替换任意参数（数据类型正确即可），甚至替换为NULL亦可。（修改脚本以支持带参或许并非难事，本人时间有限或许后面改进）
2、不建议在Oracle上跑，该脚本收集了实际行数，在Oracle上遇见大表可能卡住
3、目前自己想到的信息如上所示，如果需要其他信息欢迎反馈添加