meta_with_sqlid
emcs
47次下载
346次浏览
2019-07-24
4.5

脚本内容

--输入SQL_ID, 获取该SQL相关的表及索引、栏位等信息 COL TABLE_NAME ON for a28 COL index_name ON for a28 COL column_name ON for a16 COL POS FOR 99 COL tBlk for 9EEEE COL rRow for 9EEEE COL iBlk for 9EEEE COL iKey for 9EEEE COL cKey for 9EEEE COL cnull for 9EEEE break on table_name with table_list as (select /*+ materialize */ OBJECT_OWNER table_owner,OBJECT_NAME table_name from dba_hist_sql_plan where sql_id ='&sql_id' and object_type like 'TABLE%' union select TABLE_OWNER,TABLE_NAME table_name from dba_hist_sql_plan sql,dba_indexes i where sql_id ='&sql_id' and object_type like 'INDEX%' and i.owner = sql.object_owner and i.index_name = sql.object_name ),t as (select /*+ materialize */ owner,t.table_name,num_rows,last_analyzed ,blocks from dba_tables t, table_list p where t.owner = p.table_owner and t.table_name = p.table_name ) , ic as (select /*+ materialize */ ic.table_owner,ic.table_name,column_name,index_owner,index_name,column_position from dba_ind_columns ic, table_list p where ic.table_owner = p.table_owner and ic.table_name = p.table_name ) , tc as (select /*+ materialize */table_owner,tc.table_name,column_name,num_distinct,num_nulls from dba_tab_columns tc, table_list p where tc.owner = p.table_owner and tc.table_name = p.table_name union all select table_owner,table_name,column_name,null,null from ic where column_name like 'SYS_NC%' ) , i as (select /*+ materialize */i.table_owner,i.table_name,index_name,owner,distinct_keys,leaf_blocks from dba_indexes i, table_list p where i.table_owner = p.table_owner and i.table_name = p.table_name ) select /*+ordered use_hash(t tc)*/ owner||'.'||table_name table_name , blocks tBlk , num_rows rRow , index_name , leaf_blocks iBlk , distinct_keys iKey , column_position pos , column_name , num_distinct cKey , num_nulls cnull , last_analyzed from ( select /*+ordered use_hash(t tc)*/ t.owner, t.table_name , t.num_rows, t.blocks, i.index_name , i.distinct_keys , i.leaf_blocks, ic.column_position, tc.column_name, tc.num_distinct , tc.num_nulls , t.last_analyzed from t , tc , ic, i where tc.table_owner = t.owner and tc.table_name = t.table_name and ic.table_owner(+) = tc.table_owner and ic.table_name(+) = tc.table_name and ic.column_name(+)=tc.column_name and i.table_owner(+) = ic.table_owner and i.table_name(+) = ic.table_name and i.index_name(+)=ic.index_name order by t.owner,t.table_name,i.index_name,column_position,num_distinct desc ) /

评论

贡献排行榜