| |
 |
|
Monitoring STATSPACK
table extents
Oracle Tips by Burleson Consulting
|
Here is a handy script for monitoring the
number of extents for all STATSPACK tables. The script is called
sp_extents.sql and you can use this script to see the space used
by all STATSPACK tables.
This script has many lines that are commented
out of the output so that you can choose which columns from
Remote DBA_tables and Remote DBA_segments that you wish to see. In most
cases, this report will show you which tables are extending rapidly
and consuming an inordinate amount of room in your perfstat
tablespace.
sp_extents.sql
--
sp_extents.sql - Lists all STATSPACK tables & size data
-- Written 7/16/01 by Donald Burleson
set pause off;
set echo off;
set linesize 150;
set pages 999;
column c1 heading "Tablespace";
column c2 heading "Owner";
column c3 heading "Table" format a30;
column c4 heading "Size (KB)" format 999,999;
column c5 heading "Alloc. Ext" format 999;
column c6 heading "Max Ext";
column c7 heading "Init Ext (KB)";
column c8 heading "Next Ext (KB)";
column c9 heading "% Inc";
column c10 heading "% Free";
column c11 heading "% Used";
column c12 heading "Num_rows" format 999,999,999;
column c13 heading "avg_row_len" format 999,999,999;
column c14 heading "Chains" format 999,999,999;
break on c1 skip 2 on c2 skip 2
ttitle "Fragmented Tables";
select
-- substr(seg.tablespace_name,1,10) c1,
-- substr(tab.owner,1,10) c2,
substr(tab.table_name,1,30) c3,
seg.bytes/1024 c4,
seg.extents c5,
-- tab.max_extents c6,
-- tab.initial_extent/1024 c7,
-- tab.next_extent/1024 c8
-- tab.pct_increase c9,
-- tab.pct_free c10,
-- tab.pct_used c11
tab.num_rows c12,
tab.avg_row_len c13
-- tab.chain_cnt c14
from sys.Remote DBA_segments seg,
sys.Remote DBA_tables tab
where seg.tablespace_name = tab.tablespace_name
and seg.owner = tab.owner
and seg.segment_name = tab.table_name
and tab.table_name like 'STATS%'
order by 4 desc
;
Here is a sample listing from this script.
Table Size (KB) Alloc. Ext Num_rows
row_len
------------------------------ --------- ---------- ------------
--------
STATS$SQL_SUMMARY 941,040 89
1,373,381 274
STATS$PARAMETER 54,080 52 971,514
54
STATS$SYSSTAT 23,920 23
451,774 50
STATS$LATCH 20,800 20
301,849 67
STATS$SYSTEM_EVENT 5,200 5
84,961 47
STATS$SGASTAT_SUMMARY 3,120 3
77,769 37
STATS$ENQUEUESTAT 2,080 2
46,759 26
STATS$ROWCACHE_SUMMARY 3,120 3
45,977 63
STATS$FILESTATXS 4,160 4
37,981 84
STATS$BG_EVENT_SUMMARY 2,080 2
37,969 47
STATS$LATCH_MISSES_SUMMARY 2,080 2
29,367 55
STATS$WAITSTAT 1,040 1
27,986 35
STATS$VMSTAT 10,240 1
27,964 42
STATS$LIBRARYCACHE 1,040 1
15,992 55
STATS$ROLLSTAT 1,040 1
7,996 67
STATS$SNAPSHOT 1,040 1
1,999 53
STATS$BUFFER_POOL_STATISTICS 1,040 1
1,999 87
STATS$IDX_STATS 1,040 1
1,144 84
STATS$TAB_STATS 1,040 1
1,028 78
STATS$BUFFER_POOL 1,040 1
32 30
STATS$SGAXS 1,040 1
32 57
STATS$IDLE_EVENT 80 1
14 22
STATS$LEVEL_DESCRIPTION 80 1
3 145
STATS$DATABASE_INSTANCE 80 1
1 38
STATS$STATSPACK_PARAMETER 80 1
1 46
STATS$LATCH_CHILDREN 1,040 1
0 0
STATS$SESSION_EVENT 1,040 1
0 0
STATS$SESSTAT 1,040 1
0 0
This is an excerpt from "Oracle9i
High Performance tuning with STATSPACK" by Oracle Press.
 |
If you like Oracle tuning, you may enjoy the new book "Oracle
Tuning: The Definitive Reference", over 900 pages
of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
 |
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today. |
 |
|
