/* Program : get_table_index_history_info.sql Purpose : Get optimizer statistics and any saved history for a table Author : Daniel W. Fink OptimalDBA.com Created : March 17, 2009 Update : Parameters : &1 owner_table in the format owner.table (not case sensitive) : Example @get_table_index_history_info.sql scott.emp Exit Code : Not Used Comments : Disclaimer : No warranty is provided for any use of the script, statements or logic included. This script, statements and logic are for personal use only and may not be included as part of a commercial product. Please address any comments to script_feedback@optimaldba.com This comment block and all lines above must be included. */ SET VERIFY OFF PAGESIZE 4000 LINESIZE 135 DEFINE owner_table = &1 COLUMN spoolname FORMAT A50 NEW_VALUE spool_name NOPRINT SELECT 'tab_idx_info_hstry_'||UPPER('&&owner_table')||'_'||TO_CHAR(SYSDATE, 'YYYYMMDDhh24miss')||'.log' spoolname FROM dual / SPOOL &&spool_name COLUMN last_analyzed_time FORMAT A17 HEADING 'Analyzed Date' COLUMN tab_degree FORMAT 9999 HEADING 'Deg' COLUMN tab_partitioned FORMAT A4 HEADING 'Prtn' COLUMN tab_num_rows FORMAT 999,999,999 HEADING 'Rows' COLUMN tab_alloc_blocks FORMAT 999,999,999 HEADING 'Allocated|Blocks' COLUMN tab_hwm_blocks FORMAT 999,999,999 HEADING 'HWM|Blocks' COLUMN tab_analyzed_pct FORMAT 999.99 HEADING 'Analyze|Pct' COLUMN tab_avg_space FORMAT 99999 HEADING 'Avg Block|Free Space' COLUMN tab_avg_row_length FORMAT 99999 HEADING 'Avg Row|Length' COLUMN tab_monitoring FORMAT A4 HEADING 'Mntr' COLUMN tab_column_name FORMAT A30 HEADING 'Column Name' COLUMN tab_column_datatype FORMAT A10 HEADING 'Datatype' COLUMN tab_column_nullable FORMAT A4 HEADING 'Null' COLUMN tab_column_numdistinct FORMAT 999,999,999 HEADING 'Distinct|Values' COLUMN tab_column_density FORMAT 9.999999999 HEADING 'Density' COLUMN tab_column_numnulls FORMAT 999,999,999 HEADING 'Number|of Nulls' COLUMN tab_column_histogram FORMAT A3 HEADING 'Hst' COLUMN tab_column_numbuckets FORMAT 999,999 HEADING 'Buckets' COLUMN index_name FORMAT A30 HEADING 'Index Name' COLUMN ind_status FORMAT A7 HEADING 'Status' COLUMN ind_unique FORMAT A3 HEADING 'Unq' COLUMN ind_blevel FORMAT 999 HEADING 'Lvl' COLUMN ind_leafblocks FORMAT 999,999 HEADING 'Leaf Blks' COLUMN ind_numrows FORMAT 999,999,999 HEADING 'Indx Rows' COLUMN ind_distinctkeys FORMAT 999,999,999 HEADING 'Dstnct Keys' COLUMN ind_clufac FORMAT 999,999,999 HEADING 'Clstr Fctr' COLUMN column_name FORMAT A30 HEADING 'Column Name' COLUMN low_value FORMAT A20 HEADING 'Low Value' COLUMN high_value FORMAT A20 HEADING 'High Value' COLUMN tab_column_id NOPRINT PROMPT PROMPT PROMPT ********************************************************************************* PROMPT Table Statistics for &&owner_table PROMPT ********************************************************************************* PROMPT SELECT TO_CHAR(t.last_analyzed, 'YYYY/MM/DD hh24:mi') last_analyzed_time , TO_NUMBER(t.degree) tab_degree , t.partitioned tab_partitioned , t.num_rows tab_num_rows , t.blocks tab_alloc_blocks , (t.blocks - t.empty_blocks) tab_hwm_blocks , ROUND((t.sample_size/DECODE(t.num_rows,0,1,t.num_rows))*100,2) tab_analyzed_pct , t.avg_space tab_avg_space , t.avg_row_len tab_avg_row_length , t.monitoring tab_monitoring FROM dba_tables t WHERE t.owner||'.'||t.table_name = UPPER('&&owner_table') UNION ALL SELECT TO_CHAR(th.analyzetime, 'YYYY/MM/DD hh24:mi') last_analyzed_time , NULL tab_degree , NULL tab_partitioned , th.rowcnt tab_num_rows , th.blkcnt tab_alloc_blocks , NULL tab_hwm_blocks , ROUND((th.samplesize/DECODE(th.rowcnt,0,1,th.rowcnt))*100,2) tab_analyzed_pct , NULL tab_avg_space , th.avgrln tab_avg_row_length , NULL tab_monitoring FROM sys.wri$_optstat_tab_history th WHERE th.obj# = ( SELECT o.object_id FROM dba_objects o WHERE o.owner||'.'||o.object_name = UPPER('&&owner_table') AND o.object_type = 'TABLE' ) ORDER BY last_analyzed_time DESC / PROMPT PROMPT PROMPT ********************************************************************************* PROMPT Column Statistics for &&owner_table PROMPT ********************************************************************************* PROMPT BREAK ON tab_column_name NODUP SKIP 1 SELECT tc.column_name tab_column_name , TO_CHAR(tc.last_analyzed, 'YYYY/MM/DD hh24:mi') last_analyzed_time , tc.data_type tab_column_datatype , DECODE(tc.nullable, 'Y', NULL, tc.nullable) tab_column_nullable , tc.num_distinct tab_column_numdistinct , tc.density tab_column_density , tc.num_nulls tab_column_numnulls , DECODE(tc.histogram,'NONE',NULL,'FREQUENCY','FRQ', 'HEIGHT BALANCED','HGT',tc.histogram) tab_column_histogram , TO_NUMBER(DECODE(tc.num_buckets,1,NULL,tc.num_buckets)) tab_column_numbuckets , tc.column_id tab_column_id FROM dba_tab_columns tc WHERE tc.owner||'.'||tc.table_name = UPPER('&&owner_table') UNION ALL SELECT tc.column_name tab_column_name , TO_CHAR(tch.timestamp#, 'YYYY/MM/DD hh24:mi') last_analyzed_time , NULL tab_column_datatype , NULL tab_column_nullable , tch.distcnt tab_column_numdistinct , tch.density tab_column_density , tch.null_cnt tab_column_numnulls , NULL tab_column_histogram , TO_NUMBER(DECODE(tchh.num_buckets,1,NULL,tchh.num_buckets)) tab_column_numbuckets , tch.intcol# tab_column_id FROM dba_tab_columns tc , sys.wri$_optstat_histhead_history tch , ( SELECT tchh2.intcol# , tchh2.savtime , count(tchh2.bucket) num_buckets FROM sys.wri$_optstat_histgrm_history tchh2 WHERE tchh2.obj# = ( SELECT o.object_id FROM dba_objects o WHERE o.owner||'.'||o.object_name = UPPER('&&owner_table') AND o.object_type = 'TABLE' ) GROUP BY tchh2.intcol# , tchh2.savtime ) tchh WHERE tc.owner||'.'||tc.table_name = UPPER('&&owner_table') AND tch.obj# = ( SELECT o.object_id FROM dba_objects o WHERE o.owner||'.'||o.object_name = UPPER('&&owner_table') AND o.object_type = 'TABLE' ) AND tch.intcol# = tc.column_id AND tch.intcol# = tchh.intcol# (+) AND tch.savtime = tchh.savtime (+) ORDER BY tab_column_id, last_analyzed_time DESC, tab_column_name / PROMPT PROMPT PROMPT ********************************************************************************* PROMPT Index Statistics for &&owner_table PROMPT ********************************************************************************* PROMPT BREAK ON index_name NODUP SKIP 1 ON ind_unique NODUP SELECT i.index_name index_name , TO_CHAR(i.last_analyzed, 'YYYY/MM/DD hh24:mi') last_analyzed_time , i.status ind_status , DECODE(i.uniqueness,'UNIQUE','Y',NULL) ind_unique , i.blevel ind_blevel , i.leaf_blocks ind_leafblocks , i.num_rows ind_numrows , i.distinct_keys ind_distinctkeys , i.clustering_factor ind_clufac FROM dba_indexes i WHERE i.table_owner||'.'||i.table_name = UPPER('&&owner_table') UNION ALL SELECT i.index_name index_name , TO_CHAR(ih.analyzetime, 'YYYY/MM/DD hh24:mi') last_analyzed_time , NULL ind_status , DECODE(i.uniqueness,'UNIQUE','Y',NULL) ind_unique , ih.blevel ind_blevel , ih.leafcnt ind_leafblocks , ih.rowcnt ind_numrows , ih.distkey ind_distinctkeys , ih.clufac ind_clufac FROM dba_indexes i , sys.wri$_optstat_ind_history ih WHERE i.table_owner||'.'||i.table_name = UPPER('&&owner_table') AND ih.obj# = ( SELECT o.object_id FROM dba_objects o WHERE o.owner||'.'||o.object_name = UPPER(i.owner||'.'||i.index_name) AND o.object_type = 'INDEX' ) ORDER BY ind_unique, index_name, last_analyzed_time DESC / PROMPT PROMPT PROMPT ********************************************************************************* PROMPT Index Columns for &&owner_table PROMPT ********************************************************************************* PROMPT BREAK ON index_name NODUP SKIP 1 ON column_name NODUP WITH col_hi_lo_vals AS ( SELECT tc.column_name , tc.data_type , TO_CHAR(tc.last_analyzed, 'YYYY/MM/DD hh24:mi') last_analyzed_time , tc.low_value raw_low_value , tc.high_value raw_high_value , SUBSTR(dump(tc.low_value), (INSTR(dump(tc.low_value),': ')+2)) date_low_val , SUBSTR(dump(tc.high_value), (INSTR(dump(tc.high_value),': ')+2)) date_high_val FROM dba_tab_columns tc WHERE tc.owner||'.'||tc.table_name = UPPER('&&owner_table') UNION ALL SELECT tc.column_name , tc.data_type , TO_CHAR(tch.timestamp#, 'YYYY/MM/DD hh24:mi') last_analyzed_time , tch.lowval raw_low_value , tch.hival raw_high_value , SUBSTR(dump(tch.lowval), (INSTR(dump(tch.lowval),': ')+2)) date_low_val , SUBSTR(dump(tch.hival), (INSTR(dump(tch.hival),': ')+2)) date_high_val FROM dba_tab_columns tc , sys.wri$_optstat_histhead_history tch WHERE tc.owner||'.'||tc.table_name = UPPER('&&owner_table') AND tch.obj# = ( SELECT o.object_id FROM dba_objects o WHERE o.owner||'.'||o.object_name = UPPER('&&owner_table') AND o.object_type = 'TABLE' ) AND tch.intcol# = tc.column_id ), col_hi_lo_vals_translated AS ( SELECT column_name , data_type , last_analyzed_time , CASE when data_type = 'DATE' THEN TO_CHAR(REGEXP_SUBSTR(date_low_val, '[0-9]+',1,1)-100, '09')|| -- low_century TO_CHAR(REGEXP_SUBSTR(date_low_val, '[0-9]+',1,2)-100, '09')|| -- low_year TO_CHAR(REGEXP_SUBSTR(date_low_val, '[0-9]+',1,3),'09')|| -- low_month TO_CHAR(REGEXP_SUBSTR(date_low_val, '[0-9]+',1,4),'09')|| -- low_day TO_CHAR(REGEXP_SUBSTR(date_low_val, '[0-9]+',1,5)-1,'09')|| -- low_hour24 TO_CHAR(REGEXP_SUBSTR(date_low_val, '[0-9]+',1,6)-1,'09')|| -- low_minute TO_CHAR(REGEXP_SUBSTR(date_low_val, '[0-9]+',1,7)-1,'09') -- low_second ELSE NULL END low_date , CASE when data_type = 'DATE' THEN TO_CHAR(REGEXP_SUBSTR(date_high_val, '[0-9]+',1,1)-100, '09')|| -- high_century TO_CHAR(REGEXP_SUBSTR(date_high_val, '[0-9]+',1,2)-100, '09')|| -- high_year TO_CHAR(REGEXP_SUBSTR(date_high_val, '[0-9]+',1,3), '09')|| -- high_month TO_CHAR(REGEXP_SUBSTR(date_high_val, '[0-9]+',1,4), '09')|| -- high_day TO_CHAR(REGEXP_SUBSTR(date_high_val, '[0-9]+',1,5)-1, '09')|| -- high_hour24 TO_CHAR(REGEXP_SUBSTR(date_high_val, '[0-9]+',1,6)-1, '09')|| -- high_minute TO_CHAR(REGEXP_SUBSTR(date_high_val, '[0-9]+',1,7)-1, '09') -- high_second ELSE NULL END high_date , CASE WHEN data_type = 'NUMBER' THEN utl_raw.cast_to_number(raw_low_value) ELSE NULL END low_num , CASE WHEN data_type = 'NUMBER' THEN utl_raw.cast_to_number(raw_high_value) ELSE NULL END high_num , CASE WHEN data_type LIKE '%CHAR%' THEN utl_raw.cast_to_varchar2(raw_low_value) ELSE NULL END low_char , CASE WHEN data_type LIKE '%CHAR%' THEN utl_raw.cast_to_varchar2(raw_high_value) ELSE NULL END high_char FROM col_hi_lo_vals ) SELECT ic.index_name, ic.column_name , chlvt.last_analyzed_time , CASE WHEN chlvt.data_type = 'DATE' THEN TO_CHAR(TO_DATE(REPLACE(chlvt.low_date, ' '), 'YYYYMMDDHH24MISS'), 'MM/DD/YYYY hh24:mi:ss') WHEN chlvt.data_type = 'NUMBER' THEN LPAD(TO_CHAR(chlvt.low_num),20) WHEN chlvt.data_type LIKE '%CHAR%' THEN chlvt.low_char END low_value , CASE WHEN chlvt.data_type = 'DATE' THEN TO_CHAR(TO_DATE(REPLACE(chlvt.high_date, ' '), 'YYYYMMDDHH24MISS'), 'MM/DD/YYYY hh24:mi:ss') WHEN chlvt.data_type = 'NUMBER' THEN LPAD(TO_CHAR(chlvt.high_num),20) WHEN chlvt.data_type LIKE '%CHAR%' THEN chlvt.high_char END high_value FROM dba_ind_columns ic , col_hi_lo_vals_translated chlvt WHERE ic.table_owner||'.'||ic.table_name = UPPER('&&owner_table') AND ic.column_name = chlvt.column_name ORDER BY ic.index_name, ic.column_position, chlvt.last_analyzed_time DESC / SPOOL off