how to identify tables that waste too much space
|
4453
2006-OCT-13 14:46:00
|
|
Moderator
|
|
|
Registered On: Mar 2006
Total Posts: 289
|
|
PROBLEM:
how to identify tables that waste too much space (rows have been deleted extents stay):
SOLUTION:
s_tab_waste.sql is a script to monitor wasted space using num_rows*avg_row_len method:
15:13:03 SYSTEM@OSID:dbhost> set echo on
15:13:03 SYSTEM@OSID:dbhost> set feed on
15:13:03 SYSTEM@OSID:dbhost> set lines 132
15:13:03 SYSTEM@OSID:dbhost> set trims off
15:13:03 SYSTEM@OSID:dbhost>
15:13:03 SYSTEM@OSID:dbhost> drop table dba_segments_dump;
Table dropped.
15:13:05 SYSTEM@OSID:dbhost>
15:13:05 SYSTEM@OSID:dbhost> create global temporary table dba_segments_dump
15:13:05 2 as select * from dba_segments
15:13:05 3 where 1=2;
Table created.
15:13:05 SYSTEM@OSID:dbhost>
15:13:05 SYSTEM@OSID:dbhost> insert into dba_segments_dump
15:13:05 2 select * from dba_segments;
45456 rows created.
15:13:46 SYSTEM@OSID:dbhost>
15:13:46 SYSTEM@OSID:dbhost>
15:13:46 SYSTEM@OSID:dbhost> select
15:13:46 2 t.owner
15:13:46 3 , t.table_name
15:13:46 4 , t.num_rows
15:13:46 5 , t.avg_row_len
15:13:46 6 , round((t.num_rows*t.avg_row_len/1024)) est_could_be_kb
15:13:46 7 , s.bytes/1024 curr_kb
15:13:46 8 , round((s.bytes-(t.num_rows*t.avg_row_len))/1024) est_wasted_kb
15:13:46 9 from dba_tables t
15:13:46 10 , dba_segments_dump s
15:13:46 11 where t.table_name=s.segment_name
15:13:46 12 and t.owner = s.owner
15:13:46 13 and s.owner!='SYS'
15:13:46 14 and (s.bytes-(t.num_rows*t.avg_row_len)) >= 100*1024*1024
15:13:46 15 order by (s.bytes-(t.num_rows*t.avg_row_len)) desc;
OWNER TABLE_NAME NUM_ROWS AVG_ROW_LEN EST_COULD_BE_KB CURR_KB EST_WASTED_KB
------------------------------ ------------------------------ ---------- ----------- --------------- ---------- -------------
MRP MLOG$_MRP_SCHEDULE_DATES 19530 39 744 1453096 1452352
ONT OE_PROCESSING_MSGS 0 24 0 1187920 1187920
ONT OE_PROCESSING_MSGS_TL 0 16 0 1054800 1054800
BOM CST_STANDARD_COSTS 28788760 89 2502148 3335144 832996
XQA XQA_RESULTS 10160945 87 863283 1486392 623109
BOM CST_ELEMENTAL_COSTS 18747600 74 1354807 1901216 546409
QA QA_RESULTS 14514603 243 3444383 3983280 538897
QA QA_ACTION_LOG 16119590 109 1715855 2189112 473257
AR AR_JOURNAL_INTERIM_ALL 1420140 188 260729 733224 472495
XONT XONT_SALES_ORDER_ACTIVITY 4418975 184 794035 1239040 445005
PO PO_INTERFACE_ERRORS 242230 298 70493 506144 435651
OWNER TABLE_NAME NUM_ROWS AVG_ROW_LEN EST_COULD_BE_KB CURR_KB EST_WASTED_KB
------------------------------ ------------------------------ ---------- ----------- --------------- ---------- -------------
MRP MRP_BIS_INV_DETAIL 1381620 71 95796 491568 395772
INV MTL_DEMAND 188034 197 36175 393608 357433
MRP MRP_GROSS_REQUIREMENTS 646198 100 63105 384176 321071
INV MLOG$_MTL_SYSTEM_ITEMS_B 11300 70 772 286760 285988
BOM BOM_EXPLOSION_TEMP 1375920 192 257985 539800 281815
QA QA_INTERFACE_ERRORS 45210 106 4680 284040 279360
INV MTL_MATERIAL_TRANSACTIONS 3694398 252 909168 1180864 271696
INV MTL_TRANSACTION_ACCOUNTS 5368150 116 608111 860776 252665
MRP MRP_BOM_COMPONENTS 232660 95 21585 271112 249527
APPLSYS FND_CONCURRENT_REQUESTS 26234 486 12451 256000 243549
MRP MRP_SYSTEM_ITEMS 90160 223 19634 253424 233790
OWNER TABLE_NAME NUM_ROWS AVG_ROW_LEN EST_COULD_BE_KB CURR_KB EST_WASTED_KB
------------------------------ ------------------------------ ---------- ----------- --------------- ---------- -------------
MRP MRP_ASSEMBLY_OPERATIONS 0 24 0 210160 210160
APPLSYS FND_LOGINS 81120 43 3406 204712 201306
INV MTL_DEMAND_INTERFACE 3660 68 243 176368 176125
ONT OE_ORDER_LINES_ALL 951195 628 583350 757840 174490
MRP MRP_SCHEDULE_CONSUMPTIONS 1857800 75 136069 309040 172971
OE SO_EXCEPTIONS 8710 83 706 173328 172622
QA QA_RESULTS_INTERFACE 33167 266 8616 173720 165104
APPS ACTIS_TEMP 411225 99 39757 203776 164019
MRP MRP_RECOMMENDATIONS 228944 191 42703 202504 159801
ONT OE_LINE_ACKS 404440 936 369683 521296 151613
MRP MRP_ATP_SCHEDULE_TEMP 1452150 184 260933 404464 143531
OWNER TABLE_NAME NUM_ROWS AVG_ROW_LEN EST_COULD_BE_KB CURR_KB EST_WASTED_KB
------------------------------ ------------------------------ ---------- ----------- --------------- ---------- -------------
GL GL_JE_LINES 3850930 187 703246 844384 141138
XDW XDW_ALL_SHPD_DETAIL 90650 626 55417 194560 139143
MRP MRP_FORECAST_UPDATES 1338560 127 166013 285816 119803
AP AP_AE_LINES_ALL 2777490 218 591302 701376 110074
MRP MRP_FORECAST_DATES 1229050 119 142829 250936 108107
MRP MRP_SCHEDULE_DATES 97940 124 11860 117480 105620
ONT MLOG$_OE_ORDER_LINES_ALL 0 8 0 102440 102440
40 rows selected.
15:13:51 SYSTEM@OSID:dbhost>
15:13:51 SYSTEM@OSID:dbhost> commit;
Commit complete.
15:13:51 SYSTEM@OSID:dbhost>
15:13:51 SYSTEM@OSID:dbhost>
15:13:51 SYSTEM@OSID:dbhost>
##
## Lets see what can be done to ont.OE_PROCESSING_MSGS as an example:
##
## First I'll analyze the table to get up to data stats:
##
15:13:51 SYSTEM@OSID:dbhost> analyze table ont.OE_PROCESSING_MSGS compute statistics;
Table analyzed.
15:29:48 SYSTEM@OSID:dbhost>
15:32:22 SYSTEM@OSID:dbhost> desc ont.OE_PROCESSING_MSGS
Name Null? Type
----------------------------------------- -------- ----------------------------
TRANSACTION_ID NOT NULL NUMBER
REQUEST_ID NUMBER
ENTITY_CODE VARCHAR2(30)
ENTITY_ID NUMBER
HEADER_ID NUMBER
LINE_ID NUMBER
ORIGINAL_SYS_DOCUMENT_REF VARCHAR2(50)
ORIGINAL_SYS_DOCUMENT_LINE_REF VARCHAR2(50)
SOURCE_DOCUMENT_ID NUMBER
SOURCE_DOCUMENT_LINE_ID NUMBER
ORDER_SOURCE_ID NUMBER
SOURCE_DOCUMENT_TYPE_ID NUMBER
ATTRIBUTE_CODE VARCHAR2(30)
PROGRAM_UPDATE_DATE DATE
PROGRAM_APPLICATION_ID NUMBER
PROGRAM_ID NUMBER
LAST_UPDATE_LOGIN NUMBER
LAST_UPDATED_BY NOT NULL NUMBER
LAST_UPDATE_DATE NOT NULL DATE
CREATED_BY NOT NULL NUMBER
CREATION_DATE NOT NULL DATE
CONSTRAINT_ID NUMBER
PROCESS_ACTIVITY NUMBER
NOTIFICATION_FLAG VARCHAR2(1)
ENTITY_REF VARCHAR2(50)
CHANGE_SEQUENCE VARCHAR2(50)
ORIG_SYS_SHIPMENT_REF VARCHAR2(50)
TYPE VARCHAR2(30)
MESSAGE_SOURCE_CODE VARCHAR2(30)
LANGUAGE VARCHAR2(4)
MESSAGE_TEXT VARCHAR2(2000)
15:33:04 SYSTEM@OSID:dbhost> -- check my estimates
15:33:14 SYSTEM@OSID:dbhost> --
15:33:14 SYSTEM@OSID:dbhost> select
15:33:15 2 t.num_rows
15:33:15 3 , t.avg_row_len
15:33:15 4 , round((t.num_rows*t.avg_row_len/1024)) est_could_be_kb
15:33:15 5 , s.bytes/1024 curr_kb
15:33:15 6 , round((s.bytes-(t.num_rows*t.avg_row_len))/1024) est_wasted_kb
15:33:15 7 from dba_tables t
15:33:15 8 , dba_segments s
15:33:15 9 where t.table_name=s.segment_name
15:33:15 10 and t.owner = s.owner
15:33:15 11 and t.table_name='OE_PROCESSING_MSGS'
15:33:15 12 /
NUM_ROWS AVG_ROW_LEN EST_COULD_BE_KB CURR_KB EST_WASTED_KB
---------- ----------- --------------- ---------- -------------
51 84 4 1187920 1187916
1 row selected.
15:33:43 SYSTEM@OSID:dbhost>
15:33:43 SYSTEM@OSID:dbhost> -- so estimate is that this table will
15:35:10 SYSTEM@OSID:dbhost> -- become 4KB -- that's ofcourse not
15:35:23 SYSTEM@OSID:dbhost> -- going to happen, lets see how far
15:35:34 SYSTEM@OSID:dbhost> -- am I off:
15:35:38 SYSTEM@OSID:dbhost>
15:36:02 SYSTEM@OSID:dbhost> select owner,index_name,status
15:36:07 2 from dba_indexes
15:36:09 3 where table_name='OE_PROCESSING_MSGS';
OWNER INDEX_NAME STATUS
------------------------------ ------------------------------ --------
ONT OE_PROCESSING_MSGS_N2 VALID
ONT OE_PROCESSING_MSGS_N3 VALID
ONT OE_PROCESSING_MSGS_N1 VALID
3 rows selected.
15:36:12 SYSTEM@OSID:dbhost>
15:36:34 SYSTEM@OSID:dbhost> alter table ont.OE_PROCESSING_MSGS move;
Table altered.
15:37:44 SYSTEM@OSID:dbhost> select owner,index_name,status
15:37:50 2 from dba_indexes
15:37:52 3 where table_name='OE_PROCESSING_MSGS';
OWNER INDEX_NAME STATUS
------------------------------ ------------------------------ --------
ONT OE_PROCESSING_MSGS_N2 UNUSABLE
ONT OE_PROCESSING_MSGS_N3 UNUSABLE
ONT OE_PROCESSING_MSGS_N1 UNUSABLE
3 rows selected.
15:38:11 SYSTEM@OSID:dbhost> alter index ont.OE_PROCESSING_MSGS_N2 rebuild;
Index altered.
15:38:54 SYSTEM@OSID:dbhost> alter index ont.OE_PROCESSING_MSGS_N3 rebuild;
Index altered.
15:39:04 SYSTEM@OSID:dbhost> alter index ont.OE_PROCESSING_MSGS_N1 rebuild;
Index altered.
15:39:13 SYSTEM@OSID:dbhost> select owner,index_name,status
15:39:55 2 from dba_indexes
15:39:57 3 where table_name='OE_PROCESSING_MSGS';
OWNER INDEX_NAME STATUS
------------------------------ ------------------------------ --------
ONT OE_PROCESSING_MSGS_N2 VALID
ONT OE_PROCESSING_MSGS_N3 VALID
ONT OE_PROCESSING_MSGS_N1 VALID
3 rows selected.
15:39:59 SYSTEM@OSID:dbhost> analyze table ont.OE_PROCESSING_MSGS compute statistics;
Table analyzed.
15:40:16 SYSTEM@OSID:dbhost> select
15:40:31 2 t.num_rows
15:40:31 3 , t.avg_row_len
15:40:31 4 , round((t.num_rows*t.avg_row_len/1024)) est_could_be_kb
15:40:31 5 , s.bytes/1024 curr_kb
15:40:31 6 , round((s.bytes-(t.num_rows*t.avg_row_len))/1024) est_wasted_kb
15:40:31 7 from dba_tables t
15:40:31 8 , dba_segments s
15:40:31 9 where t.table_name=s.segment_name
15:40:31 10 and t.owner = s.owner
15:40:31 11 and t.table_name='OE_PROCESSING_MSGS';
NUM_ROWS AVG_ROW_LEN EST_COULD_BE_KB CURR_KB EST_WASTED_KB
---------- ----------- --------------- ---------- -------------
51 84 4 48 44
1 row selected.
15:40:46 SYSTEM@OSID:dbhost>
CONCLUSION:
This is not bad at all for an almost empty table that is ...
Before rebuild
--------------
EST_COULD_BE_KB = 4
CURR_KB = 1187920
After rebuild
-------------
EST_COULD_BE_KB = 4
CURR_KB = 48
Reclamed space = 1187920-48 = 1187872 KB [1.1 GB]