根据sql_id查看所涉及的表和索引的统计信息

REM col child_number new_value child_number noprint 
REM /* and p.child_number = nvl(&child_number,0) */

PROMPT 
PROMPT 查询SQL_ID对应的表的统计信息
set define on verify off feedback on
define sql_id=&sql_id
col sql_id new_value sql_id noprint

set line 200
set pagesize 30
col owner for a10
col table_name for a30
col num_rows for 9999999999
col blocks for 9999999999
col empty_blocks for 99999999
col avg_row_len for 99999
col est_M for 999999
col partitioned for a3
col degree for a10
col last_analyzed for a22

with tmp_tab as 
(select distinct s.owner,s.table_name from dba_tables s, gv$sql_plan g
where s.owner=g.object_owner and s.table_name=g.object_name
and g.sql_id =trim('&sql_id') and (g.object_type like 'TABLE%' OR g.object_type like '%VIEW%')
union
select distinct i.table_owner owner,i.table_name table_name from dba_indexes i , gv$sql_plan g
where i.owner=g.object_owner and i.index_name=g.object_name
and g.sql_id = trim('&sql_id') and g.object_type like 'INDEX%')
select s.owner,s.table_name,s.num_rows,s.blocks,s.empty_blocks,s.avg_row_len,round(s.num_rows * s.avg_row_len / 1024 /1024 / 0.9) est_M,s.partitioned,s.degree,to_char(s.last_analyzed,'yyyymmdd hh24:mi:ss') as last_analyzed
from tmp_tab t,dba_tables s
where t.owner=s.owner and t.table_name=s.table_name
order by s.owner,s.table_name,s.num_rows desc;

PROMPT
PROMPT 查询SQL_ID的对应的表的列统计信息
set line 200
set pagesize 30
col owner for a10
col table_name for a30
col index_name for a30
col column_name for a24
col num_distinct for 9999999999
col num_nulls for 999999999
col histogram for a15
col last_analyzed for a22
col column_position for 99

with tmp_col as 
  (select /*+ rule */ distinct s.owner,s.table_name,s.column_name,s.num_distinct,s.num_nulls,s.histogram,s.last_analyzed 
from dba_tab_col_statistics s,gv$sql_plan g
where s.table_name = g.object_name and s.owner = g.object_owner
and g.sql_id =trim('&sql_id')
union
  select /*+ rule */ distinct s.owner,s.table_name,s.column_name,s.num_distinct,s.num_nulls,s.histogram,s.last_analyzed 
from dba_tab_col_statistics s 
where (s.owner ,s.table_name) in 
  (select i.table_owner,i.table_name 
     from dba_indexes i,gv$sql_plan g 
     where i.index_name = g.object_name and i.owner = g.object_owner
     and g.sql_id =trim('&sql_id')))
select distinct t.owner,t.table_name,t.column_name,t.num_distinct,t.num_nulls,t.histogram,t.last_analyzed,i.index_name,i.column_position
from tmp_col t,gv$sql_plan g,dba_ind_columns i
where g.sql_id =trim('&sql_id') and g.child_number = (select max(child_number) from gv$sql_plan p where p.sql_id =trim('&sql_id'))
and (instr(g.access_predicates,to_char('"'||t.column_name||'"')) > 0 or instr(g.filter_predicates,to_char('"'||t.column_name||'"'))>0)
and t.owner = i.table_owner(+) and t.table_name = i.table_name(+) and t.column_name = i.column_name(+)
order by t.owner,t.table_name,t.num_distinct desc;

PROMPT
PROMPT 查询SQL_ID对应的表上的索引信息
col table_owner for a10
col table_name for a30
col index_name for a30
col column_name for a30
col column_position for 99

with tmp_idx as
  (select distinct s.owner,s.table_name from dba_tables s, gv$sql_plan g
  where s.owner=g.object_owner and s.table_name=g.object_name
  and g.sql_id =trim('&sql_id') and (g.object_type like 'TABLE%' OR g.object_type like '%VIEW%')
union
  select distinct i.table_owner owner,i.table_name table_name from dba_indexes i , gv$sql_plan g
  where i.owner=g.object_owner and i.index_name=g.object_name
  and g.sql_id = trim('&sql_id') and g.object_type like 'INDEX%')
select i.table_owner,i.table_name,i.index_name,i.column_name,i.column_position
from tmp_idx t,dba_ind_columns i
where t.owner=i.table_owner and t.table_name=i.table_name
order by i.table_owner,i.table_name,i.index_name,i.column_position;


REM undefine sql_id
undefine sql_id