The good news is he promised he will open source it in someday.
Purpose:
Display table and its index statistics by specify Owner and Table_Name.
It can help you investigate the SQL query optimizer issue, cooperate with 10053 trace tool. And it can do more...
Reference:
http://www.ningoo.net/html/2009/how_to_get_extent_id_from_rowid.html
Here is the script:
/*
-- File name: table.sql
-- Goal: display table and index statistics by specify Owner and Table_Name
--
-- Author: Yi (Charlie) Zhu
-- update: 29-Jan-2009
-- Blog: http://mujiang.blogspot.com/
--
-- Usage: @table [owner] [table_name]
29-Jan-2009: Charlie: Show popular value chart for height balance histogram
Reference
--
http://www.ningoo.net/html/2009/how_to_get_extent_id_from_rowid.html
*/
define owner='&1'
define table_name='&2'
set serveroutput off
set timing off
set verify off
set linesize 200
set define &
COLUMN SEGMENT_NAME FORMAT A40 HEADING 'Segment Name'
COLUMN SEGMENT_TYPE FORMAT A25 HEADING 'Segment Type'
COLUMN SEGMENT_SUBTYPE FORMAT A25 HEADING 'Segment SubType'
COLUMN Tablespace_Name FORMAT A30 HEADING 'Tablespace_Name'
COLUMN Partition_NAME FORMAT A25 HEADING 'Partition Name'
COLUMN subpartition_name FORMAT A22 HEADING 'SubPartition Name'
COLUMN INDEX_NAME FORMAT A30 HEADING 'INDEX Name'
COLUMN COLUMN_NAME FORMAT A30 HEADING 'COLUMN NAME'
COLUMN DATA_TYPE FORMAT A14 HEADING 'DATA TYPE'
COLUMN LOW_VALUE FORMAT A14 HEADING 'LOW_VALUE'
COLUMN HIGH_VALUE FORMAT A14 HEADING 'HIGH_VALUE'
COLUMN owner FORMAT A14
COLUMN table_name FORMAT A30
COLUMN HISTOGRAM FORMAT A9
COLUMN endpoint_actual_value format A30
COLUMN BLOCKS FORMAT 999,999,999 HEADING 'Blocks'
COLUMN MBytes FORMAT 999,999,999 HEADING 'MBytes'
COLUMN TOTAL_BYTES FORMAT 999,999 HEADING 'TotByte_MB'
COLUMN extents FORMAT 999,999
COLUMN LAST_ANALYZED FORMAT A11 HEADING 'LAST|ANALYZED'
COLUMN pop_chart FORMAT A11 HEADING 'Pop Chart'
COLUMN unf FORMAT 999,999.99 HEADING 'unf_K'
COLUMN fs1 FORMAT 999,999.99 HEADING 'fs1_K'
COLUMN fs2 FORMAT 999,999.99 HEADING 'fs2_K'
COLUMN fs3 FORMAT 999,999.99 HEADING 'fs3_K'
COLUMN fs4 FORMAT 999,999.99 HEADING 'fs4_K'
COLUMN full FORMAT 999,999.99 HEADING 'full_K'
COLUMN total_blocks FORMAT 999,999.99 HEADING 'total_blocks_K'
COLUMN unused_blocks FORMAT 999,999.99 HEADING 'unused_blocks_K'
COLUMN unfb FORMAT 999,999 HEADING 'unfb_M'
COLUMN fs1b FORMAT 999,999 HEADING 'fs1b_M'
COLUMN fs2b FORMAT 999,999 HEADING 'fs2b_M'
COLUMN fs3b FORMAT 999,999 HEADING 'fs3b_M'
COLUMN fs4b FORMAT 999,999 HEADING 'fs4b_M'
COLUMN fullb FORMAT 999,999 HEADING 'fullb_M'
COLUMN total_bytes FORMAT 999,999 HEADING 'total_bytes_M'
COLUMN unused_bytes FORMAT 999,999 HEADING 'unused_bytes_M'
COLUMN last_extf FORMAT 999,999 HEADING 'last_extf_id'
COLUMN last_extb FORMAT 9999999999 HEADING 'last_extb_id'
COLUMN lastusedblock/1024 FORMAT 9999999999 HEADING 'lastusedblock'
col num_rows new_value l_num_rows;
spool table_info.log
prompt table info
SELECT owner,table_name,pct_free,num_rows,blocks,chain_cnt,
avg_row_len,to_char(last_analyzed,'yyyy-mon-dd') last_analyzed,buffer_pool
FROM dba_tables
WHERE owner = (Upper('&&owner'))
and table_name IN (Upper('&&table_name'))
ORDER BY blocks desc;
prompt table stats
SELECT partition_name,subpartition_name,num_rows, blocks, empty_blocks, avg_space, chain_cnt, avg_row_len
FROM dba_tab_statistics
WHERE owner = (Upper('&&owner'))
and table_name IN (Upper('&&table_name'))
order by table_name,partition_position;
-- IOT come with Primary Key index name
select owner||'.'||segment_name segment_name,partition_name,round(bytes/1024/1024) mbytes,
blocks,extents,segment_type,tablespace_name
from dba_segments
WHERE owner = (Upper('&&owner'))
and segment_name IN (Upper('&&table_name'))
;
prompt column data type
SELECT --OWNER,TABLE_NAME,COLUMN_ID,
COLUMN_NAME,DATA_TYPE,DATA_LENGTH,DATA_PRECISION
FROM dba_tab_cols
WHERE owner = (Upper('&&owner'))
and TABLE_NAME IN (Upper('&&table_name'))
order by owner,table_name,column_name
;
prompt column stats
SELECT
column_name AS "NAME",
num_distinct AS "#DST",
low_value,
high_value,
density AS "DENS",
num_nulls AS "#NULL",
avg_col_len AS "AVGLEN",
histogram,
num_buckets AS "#BKT"
FROM dba_tab_col_statistics
WHERE owner = (Upper('&&owner'))
and TABLE_NAME IN (Upper('&&table_name'))
order by column_name
;
prompt column frequency histogram
SELECT column_name, endpoint_value, endpoint_actual_value, endpoint_number,
endpoint_number - lag(endpoint_number,1,0) OVER (partition by column_name ORDER BY endpoint_number) AS frequency
FROM dba_tab_histograms
WHERE owner = (Upper('&&owner'))
and table_name = (Upper('&&table_name'))
and column_name in
(select column_name FROM dba_tab_col_statistics
where owner = (Upper('&&owner'))
and TABLE_NAME IN (Upper('&&table_name'))
and histogram = 'FREQUENCY'
)
ORDER BY column_name, endpoint_number;
prompt column height-balanced histogram
SELECT column_name, endpoint_value, endpoint_number
, Max(endpoint_number) OVER (partition by column_name) Max_EPN
, endpoint_number - lag(endpoint_number,1,0) OVER (partition by column_name ORDER BY endpoint_number) AS popular_count
, &l_num_rows * (endpoint_number - lag(endpoint_number,1,0) OVER (partition by column_name ORDER BY endpoint_number))
/(Max(endpoint_number) OVER (partition by column_name)) AS Est_Card
, rpad('.',endpoint_number - lag(endpoint_number,1,0) OVER (partition by column_name ORDER BY endpoint_number) - 1,'x') pop_chart
FROM dba_tab_histograms
WHERE owner = (Upper('&&owner'))
and table_name = (Upper('&&table_name'))
and column_name in
(select column_name FROM dba_tab_col_statistics
where owner = (Upper('&&owner'))
and TABLE_NAME IN (Upper('&&table_name'))
and histogram = 'HEIGHT BALANCED'
)
ORDER BY column_name, endpoint_number;
prompt Index name,physical size,including columns and selectivity
SELECT m.INDEX_NAME,COLUMN_NAME,
COLUMN_POSITION,DISTINCT_KEYS,LEAF_BLOCKS,NUM_ROWS, COLUMN_LENGTH
FROM DBA_INDEXES m,dba_ind_columns d
WHERE m.owner = (Upper('&&owner'))
AND m.INDEX_NAME = d.INDEX_NAME
AND m.OWNER = d.TABLE_OWNER
AND m.TABLE_NAME = d.TABLE_NAME
AND m.TABLE_NAME IN (Upper('&&table_name'))
ORDER BY index_name,column_position;
prompt index stats
SELECT
index_name,
blevel,
leaf_blocks AS leaf_blks,
distinct_keys AS dst_keys,
num_rows,
clustering_factor AS clust_fact,
avg_leaf_blocks_per_key AS leaf_per_key,
avg_data_blocks_per_key AS data_per_key
FROM dba_ind_statistics
WHERE owner = (Upper('&&owner'))
AND TABLE_OWNER = (Upper('&&owner'))
AND TABLE_NAME IN (Upper('&&table_name'));
VARIABLE total_blocks NUMBER
VARIABLE total_bytes NUMBER
VARIABLE unused_blocks NUMBER
VARIABLE unused_bytes NUMBER
VARIABLE last_extf NUMBER
VARIABLE last_extb NUMBER
VARIABLE lastusedblock NUMBER
begin
DBMS_SPACE.UNUSED_SPACE(Upper('&&owner'), Upper('&&table_name'), 'TABLE',:total_blocks,
:total_bytes,:unused_blocks,:unused_bytes,:last_extf,
:last_extb,:lastusedblock);
end;
/
select
:total_bytes /1024/1024 total_bytes ,
:unused_bytes /1024/1024 unused_bytes ,
:total_blocks /1024 total_blocks ,
:unused_blocks/1024 unused_blocks,
:lastusedblock/1024 lastusedblock ,
:last_extf last_extf ,
:last_extb last_extb
from dual;
variable unf number;
variable unfb number;
variable fs1 number;
variable fs1b number;
variable fs2 number;
variable fs2b number;
variable fs3 number;
variable fs3b number;
variable fs4 number;
variable fs4b number;
variable full number;
variable fullb number;
-- shows the space usage of data blocks under the segment High Water Mark
begin
dbms_space.space_usage(Upper('&&owner'),Upper('&&table_name'),
'TABLE',
:unf,:unfb,
:fs1,:fs1b,
:fs2,:fs2b,
:fs3,:fs3b,
:fs4,:fs4b,
:full,:fullb);
end;
/
select
:unf /1024 unf
,:fs1 /1024 fs1
,:fs2 /1024 fs2
,:fs3 /1024 fs3
,:fs4 /1024 fs4
,:full /1024 full
,:unfb /1024/1024 unfb
,:fs1b /1024/1024 fs1b
,:fs2b /1024/1024 fs2b
,:fs3b /1024/1024 fs3b
,:fs4b /1024/1024 fs4b
,:fullb /1024/1024 fullb
from dual;
spool off
/*
-- unf: unformatted_blocks
print unf ;
print unfb ;
print fs4 ;
print fs4b;
print fs3 ;
print fs3b;
print fs2 ;
print fs2b;
print fs1 ;
print fs1b;
print full;
print fullb;
-- Returns the list of segments that are associated with the object
select * from Table(
DBMS_SPACE.OBJECT_DEPENDENT_SEGMENTS(
objowner => Upper('&&owner'),
objname => Upper('&&table_name'),
partname => Null,
objtype => 1)
);
SQL> print
TOTAL_BLOCKS TOTAL_BYTES UNUSED_BLOCKS UNUSED_BYTES LASTEXTF LAST_EXTB LASTUSEDBLOCK
------------ ----------- ------------- ------------ ---------- ---------- -------------
1536 12582912 0 0 5 1545 128
UNF UNFB
---------- ----------
0 0
FreeSpace1: 0 to 25% free space
FreeSpace2: 25 to 50% free space
...
FS1 FS2 FS3 FS4 FULL
---------- ---------- ---------- ---------- ----------
386 25 16 945 132
FS1B FS2B FS3B FS4B FULLB
---------- ---------- ---------- ---------- ----------
3162112 204800 131072 7741440 1081344
*/
No comments:
Post a Comment