Auxiliary Storage Quality Information
1.The following table shows the top tables (max. 40) that have an average record length of less than 4000 bytes, more than 100 chained rows in total, and a number of chained rows that is over 5% of the total number of rows in the table (based on the available CBO statistics).
| Owner | Table Name | Rows | Average Row Length | Chain Count | Chained % | Last Anaylsis Date | PCTFREE | PCTUSED |
| SAPBWP | DYNPSOURCE | 38,295 | 3,360 | 33,204 | 87 | 14-May-08 | 10 | |
| SAPCRP | DYNPSOURCE | 44,564 | 3,026 | 35,326 | 79 | 21-Nov-09 | 10 | |
| SAPCRP | REPOLOAD | 117,761 | 2,818 | 79,333 | 67 | 15-Oct-08 | 10 | |
| SAPCRP | TMSBUFTXT | 1,580 | 72 | 826 | 52 | 22-Oct-08 | 10 | |
| SAPBWP | TMSBUFTXT | 4,130 | 71 | 2,150 | 52 | 1-Oct-08 | 10 | |
| SAPBWP | DYNPLOAD | 14,926 | 1,479 | 6,115 | 41 | 31-Oct-07 | 10 | |
| SAPBWP | REPOLOAD | 111,987 | 1,785 | 42,412 | 38 | 15-Oct-08 | 10 | |
| SAPCRP | DYNPLOAD | 19,284 | 1,353 | 6,907 | 36 | 15-Oct-08 | 10 | |
| SAPBWP | D345T | 1,252 | 6,149 | 356 | 28 | 15-Oct-08 | 10 | |
| SAPCRP | D345T | 1,518 | 5,882 | 371 | 24 | 15-Oct-08 | 10 | |
| SAPCRP | ZDP_SALESGRAPH | 551 | 63 | 106 | 19 | 17-Oct-07 | 10 | |
| SAPBWP | /OSP/T_RAUI_TPBT | 48 | 197 | 6 | 13 | 24-Oct-07 | 10 | |
| SAPBWP | T002X | 560 | 32 | 67 | 12 | 24-Oct-07 | 10 | |
| SAPBWP | T002TX | 1,120 | 30 | 130 | 12 | 24-Oct-07 | 10 | |
| SAPCRP | T002TX | 1,680 | 29 | 188 | 11 | 21-Jun-06 | 10 | |
| SAPBWP | /BI0/TPROD_HIER | 291,554 | 41 | 30,444 | 10 | 8-Jan-10 | 10 | |
| SAPCRP | TBTCO | 40,510 | 295 | 1,285 | 3 | 27-Sep-06 | 10 | |
| SAPBWP | /BIC/DZC_OPP5 | 154,378 | 51 | 3,752 | 2 | 10-Apr-09 | 0 | |
| SAPCRP | DYNPTXTLD | 593 | 384 | 10 | 2 | 22-Oct-08 | 10 | |
| SAPBWP | SWDSPROPTS | 293 | 272 | 3 | 1 | 24-Oct-07 | 10 | |
| SAPBWP | SPROXLPT | 102 | 274 | 1 | 1 | 24-Oct-07 | 10 | |
| SAPBWP | MEMGMT_PARA_TEXT | 3,800 | 142 | 29 | 1 | 24-Oct-07 | 10 | |
| SAPCRP | SMODBLTXT | 4,270,283 | 458 | 30,469 | 1 | 12-Jun-09 | 10 | |
| SAPBWP | TBTCO | 54,051 | 299 | 333 | 1 | 24-Oct-07 | 10 | |
| SAPCRP | SOES | 317,730 | 167 | 1,791 | 1 | 17-Dec-08 | 10 | |
| SAPCRP | /SSF/PTAB | 12,869 | 731 | 54 | 0 | 27-Mar-09 | 10 | |
| SAPBWP | /SSF/PTAB | 12,067 | 632 | 48 | 0 | 27-Mar-09 | 10 | |
| SAPBWP | /BIC/DZC_OPP4 | 847,272 | 72 | 3,295 | 0 | 26-Dec-09 | 0 | |
| SAPBWP | SOES | 3,658 | 166 | 12 | 0 | 13-Nov-09 | 10 | |
| SAPBWP | /BIC/TZ_BWPROD | 2,250,224 | 58 | 6,682 | 0 | 13-Aug-08 | 10 | |
| SAPBWP | D346T | 589 | 1,035 | 1 | 0 | 15-Oct-08 | 10 | |
| SAPBWP | /BIC/AZO_OPPPR00 | 35,643 | 54 | 35 | 0 | 30-Oct-09 | 0 | |
| SAPBWP | SDBAD | 1,046 | 90 | 1 | 0 | 19-Nov-08 | 10 | |
| SAPCRP | SMOMAKT | 556,141 | 185 | 455 | 0 | 26-Dec-07 | 10 | |
| SAPCRP | USR12 | 9,029 | 128 | 7 | 0 | 27-Mar-09 | 10 | |
| SAPCRP | SDBAD | 67,774 | 148 | 36 | 0 | 22-Jan-10 | 10 | |
| SAPECPDB | KMC_DBRM_CONTENT | 5,481 | 638 | 2 | 0 | 29-Aug-09 | 10 | |
| SAPBWP | /BIC/TZ_BPMAIN | 299,586 | 121 | 109 | 0 | 11-Sep-09 | 10 | |
| SAPCRP | ADRC | 282,402 | 363 | 89 | 0 | 31-Jul-09 | 10 | |
| SAPCRP | SMODBLBIN | 7,769 | 139 | 2 | 0 | 27-Feb-08 | 10 | |
| SAPCRP | SMOAUSPH | 139,127 | 89 | 33 | 0 | 24-Jan-10 | 10 | |
| SAPCRP | SMOCATGT | 120,987 | 141 | 28 | 0 | 27-Sep-06 | 10 | |
| SAPBWP | /BIC/TZ_SUBD | 20,483 | 147 | 4 | 0 | 2-Oct-09 | 10 | |
| SAPCRP | ADRP | 226,725 | 219 | 25 | 0 | 15-Jan-10 | 10 | |
| SAPBWP | RSMONICDP | 792,212 | 92 | 71 | 0 | 17-Oct-09 | 10 | |
| SAPCRP | SMOMVKE | 265,200 | 273 | 14 | 0 | 16-Jan-08 | 10 | |
| SAPCRP | CDBD_ADDREXT | 293,714 | 150 | 12 | 0 | 31-Jul-09 | 1 |
Action: Reorganization
Heavy chaining is performance-relevant and requires more disk space. The bigger the table, the more important it is to carry out reorganization. Reorganization is most effective if the average row length is small (compared to a block size of 8 KB).
Query Script
set pages 9999;
set linesize 200;
column c1 heading "Owner" format a9;
column c2 heading "Table" format a20;
column c3 heading "Rows" format 999,999,999;
column c4 heading "Average Row Length" format 99,999;
column c5 heading "Chain Count" format 999,999,999;
column c6 heading "Chained %" format 99;
column c7 heading "Last anaylisis Date" format a20;
column c8 heading "PCTFREE" format 99;
column c9 heading "PCTUSED" format 99;
set heading off;
select 'Tables with migrated/chained rows and no RAW columns.' from dual;
set heading on;
select
owner c1, table_name c2, num_rows c3, avg_row_len c4, chain_cnt c5, chain_cnt/num_rows*100 c6,
last_analyzed c7,pct_free c8, pct_used c9
from dba_tables where owner not in ('SYS','SYSTEM')
and table_name not in (select table_name from dba_tab_columns where data_type in ('RAW','LONG RAW') )
and chain_cnt > 0
order by c6 desc;
---------------------------------------------------------------
2.The following table shows the top "regular" (not partitioned, empty, index organized) tables (max. 40) that have more than 1000 blocks with the lowest storage quality (based on the available CBO statistics).
| Owner | Table Name | Rows | Average Row Length | Blocks | Kb Used | Kb Needed | Kb Wasted |
| SAPCRP | TST03 | 83,070 | 7,793 | 1,606,050 | 11,563,560 | 632,192 | 10,931,368 |
| SAPCRP | SMW3_BDOC2 | 469,781 | 1,892 | 442,462 | 3,185,726 | 867,994 | 2,317,733 |
| SAPBWP | BALDAT | 460,202 | 535 | 348,719 | 2,510,777 | 240,438 | 2,270,339 |
| SAPCRP | SWFREVTLOG | 0 | 0 | 256,534 | 1,847,045 | 0 | 1,847,045 |
| SAPCRP | SMW3_BDOC5 | 843,849 | 132 | 246,052 | 1,771,574 | 108,777 | 1,662,797 |
| SAPCRP | /1CRMGC/HTPASRRL | 127,891 | 93 | 142,731 | 1,027,663 | 11,615 | 1,016,048 |
| SAPBWP | /BIC/AZO_SAL0500 | 10,676,096 | 420 | 606,060 | 4,848,480 | 4,378,868 | 469,613 |
| SAPCRP | SMW3_BDOC | 833,127 | 307 | 71,167 | 512,402 | 249,775 | 262,627 |
| SAPBWP | /BIC/AZO_S1600 | 16,196,700 | 113 | 254,997 | 2,039,976 | 1,787,331 | 252,645 |
| SAPBWP | /BIC/AZO_M01300 | 23,803,100 | 102 | 326,846 | 2,614,768 | 2,371,012 | 243,756 |
| SAPBWP | /BIC/AZO_S1000 | 12,460,200 | 296 | 478,092 | 3,824,736 | 3,601,777 | 222,959 |
| SAPCRP | BALHDR | 132,855 | 278 | 35,851 | 258,127 | 36,068 | 222,059 |
| SAPBWP | /BIC/AZO_S1100 | 10,961,400 | 324 | 459,073 | 3,672,584 | 3,468,255 | 204,329 |
| SAPCRP | DDNTF_CONV_UC | 168,340 | 20 | 28,600 | 205,920 | 3,288 | 202,632 |
| SAPBWP | BALHDR | 94,814 | 294 | 31,509 | 226,865 | 27,222 | 199,643 |
| SAPCRP | SMOHMSGQ | 0 | 0 | 26,925 | 193,860 | 0 | 193,860 |
| SAPBWP | RSRWBSTORE | 85,650 | 35 | 26,423 | 190,246 | 2,927 | 187,318 |
| SAPBWP | /BIC/AZO_S0100 | 9,680,600 | 292 | 367,977 | 2,943,816 | 2,760,484 | 183,332 |
| SAPBWP | /BIC/AZO_ALLST00 | 12,159,700 | 102 | 172,888 | 1,383,104 | 1,211,220 | 171,884 |
| SAPCRP | CRMD_ORDER_INDEX | 10,964,310 | 257 | 404,120 | 2,909,664 | 2,751,785 | 157,879 |
| SAPBWP | /BIC/AZO_STK0200 | ######## | 112 | 273,404 | 2,187,232 | 2,033,916 | 153,316 |
| SAPCRP | ZCST_BILL_DTL | ######## | 50 | 369,058 | 2,657,218 | 2,513,911 | 143,307 |
| SAPCRP | BALDAT | 98,976 | 438 | 25,650 | 184,680 | 42,335 | 142,345 |
| SAPBWP | /BIC/AZO_S1300 | ######## | 200 | 312,486 | 2,499,888 | 2,363,887 | 136,001 |
| SAPBWP | /BIC/AZO_SAWRK00 | ######## | 102 | 178,748 | 1,429,984 | 1,296,970 | 133,014 |
| SAPCRP | SMOFCMPDAT | 0 | 0 | 16,217 | 116,762 | 0 | 116,762 |
| SAPCRP | SMO9_KYTBL | 1,857 | 131 | 16,088 | 115,834 | 238 | 115,596 |
| SAPBWP | /BIC/AZO_STK0300 | ######## | 95 | 174,425 | 1,395,400 | 1,281,285 | 114,115 |
| SAPCRP | ARFCSDATA | 419,227 | 1,847 | 120,000 | 864,000 | 756,164 | 107,836 |
| SAPCRP | SMOHSITEQ | 0 | 0 | 14,177 | 102,074 | 0 | 102,074 |
| SAPBWP | DDNTF_CONV_UC | 113,000 | 18 | 14,435 | 103,932 | 1,986 | 101,946 |
| SAPBWP | /BIC/AZO_STK0100 | ######## | 68 | 127,498 | 1,019,984 | 918,080 | 101,904 |
| SAPBWP | /BIC/AZO_S1400 | ######## | 115 | 167,634 | 1,341,072 | 1,239,597 | 101,475 |
| SAPCRP | SMOFDSTATB | 43,037 | 182 | 14,941 | 107,575 | 7,649 | 99,926 |
| SAPCRP | CDBD_STATUS | 6,265,403 | 140 | 132,238 | 952,114 | 856,598 | 95,516 |
| SAPBWP | /BI0/ACRM_ACTI00 | 1,391,000 | 766 | 141,438 | 1,131,504 | 1,040,533 | 90,971 |
| SAPCRP | TRFCQDATA | 71,165 | 1,844 | 30,000 | 216,000 | 128,153 | 87,847 |
| SAPCRP | SMW0REL | 171,480 | 90 | 13,921 | 100,231 | 15,071 | 85,160 |
| SAPCRP | ARFCSSTATE | 331,849 | 424 | 30,000 | 216,000 | 137,406 | 78,594 |
| SAPCRP | SMOFCMPHD | 0 | 0 | 10,605 | 76,356 | 0 | 76,356 |
Action: Reorganization
The last column (Kb Wasted) shows how much disk space can be recovered through reorganization.
Query Script
set pages 9999;
set linesize 200;
set heading on;
column c1 heading "Owner" format a9;
column c2 heading "Table Name" format a20;
column c3 heading "Rows" format 999,999,999;
column c4 heading "Average Row Length" format 99,999;
column c5 heading "Blocks " format 999,999,999;
column c6 heading "Kb Used" format 999,999,999;
column c7 heading "Kb Needed" format 999,999,999;
column c8 heading "Kb Wasted" format 999,999,999;
SELECT * FROM
(select
owner c1,
table_name c2,
num_rows c3,
avg_row_len c4,
blocks c5,
(blocks*8)-(blocks*8*pct_free/100) c6,
num_rows*avg_row_len/1024 c7,
((blocks*8)-(blocks*8*pct_free/100))-(num_rows*avg_row_len/1024) c8
from dba_tables
where
owner not in ('SYS','SYSTEM')
and blocks > 10000
and ((blocks*8)-(blocks*8*pct_free/100))-(num_rows*avg_row_len/1024) is not null
and ((blocks*8)-(blocks*8*pct_free/100))-(num_rows*avg_row_len/1024) >= 0
order by c8 desc
)
WHERE ROWNUM <=40;
---------------------------------------------------------------
3.The following table shows the top "type normal" indexes (max. 40) that have more than 1000 blocks with the lowest storage quality (based on the available CBO statistics).
## Order by Poor quality (Storage Quality %) show 40 rows
| Owner.Index Name | Rows | Average Row Length | Leaf Blocks | Kb Used | Kb Needed | Storage Quality (%) |
| SAPBWP.RSEUINC~1 | 882 | 17 | 448 | 3,239 | 15 | 0 |
| SAPBWP.SKWF_TRANS~0 | 15 | 84 | 368 | 2,661 | 1 | 0 |
| SAPCRP.SMO9_KYTBL~0 | 2,214 | 75 | 15,807 | 114,280 | 166 | 0 |
| SAPCRP.SWFREVTLOG~Z01 | 1 | 13 | 13,321 | 96,307 | 0 | 0 |
| SAPCRP./1CRMGC/HTOPPRRL~0 | 624 | 77 | 3,497 | 25,282 | 48 | 0 |
| SAPCRP.COMM_CATEGORYDEL~0 | 2 | 28 | 722 | 5,220 | 0 | 0 |
| SAPCRP./1CRMGC/HTOPPRRLSI | 553 | 40 | 2,214 | 16,007 | 22 | 0 |
| SAPCRP.SWFREVTLOG~0 | 1 | 9 | 68,996 | 498,820 | 0 | 0 |
| SAPBWP.SDBAD~0 | 1,038 | 33 | 483 | 3,492 | 34 | 1 |
| SAPBWP.RSEUINC~0 | 685 | 39 | 726 | 5,249 | 27 | 1 |
| SAPCRP./1CRMGC/HTPASRRL~0 | 131,930 | 77 | 163,949 | 1,185,302 | 10,159 | 1 |
| SAPCRP./1CRMGC/HTPASRRLSI | 120,909 | 40 | 89,110 | 644,239 | 4,836 | 1 |
| SAPCRP.SMO5_BUFHDR~0 | 8,349 | 67 | 6,760 | 48,873 | 559 | 1 |
| SAPBWP.RSERRORLOG~0 | 642 | 66 | 663 | 4,793 | 42 | 1 |
| SAPBWP./BI0/ICRM_MKTELM~0 | 2,797 | 27 | 775 | 5,603 | 76 | 1 |
| SAPBWP./BI0/ICRM_MKTELM00 | 2,797 | 17 | 683 | 4,938 | 48 | 1 |
| SAPBWP.RSERRORLOG~001 | 618 | 49 | 550 | 3,976 | 30 | 1 |
| SAPBWP.RSERRORHEAD~001 | 614 | 40 | 212 | 1,533 | 25 | 2 |
| SAPBWP.RSERRORHEAD~0 | 626 | 56 | 300 | 2,169 | 35 | 2 |
| SAPBWP.RSERRORHEAD~002 | 614 | 40 | 212 | 1,533 | 25 | 2 |
| SAPBWP.BALDAT~0 | 263,374 | 47 | 59,601 | 430,897 | 12,379 | 3 |
| SAPBWP.RSTSODSREQUEST~0 | 3,143 | 59 | 868 | 6,275 | 185 | 3 |
| SAPBWP.RSTSODSREQUESTPG~0 | 10,571 | 45 | 1,958 | 14,156 | 476 | 3 |
| SAPBWP.BALHDR~2 | 57,446 | 34 | 9,272 | 67,034 | 1,953 | 3 |
| SAPCRP.SMW0REL~001 | 120,365 | 30 | 11,501 | 83,149 | 3,611 | 4 |
| SAPCRP.SMW3_BDOC5~0 | 1,003,535 | 77 | 245,633 | 1,775,853 | 77,272 | 4 |
| SAPBWP.BALHDR~3 | 71,353 | 44 | 8,882 | 64,214 | 3,140 | 5 |
| SAPCRP.BALHDR~2 | 85,918 | 34 | 8,306 | 60,050 | 2,921 | 5 |
| SAPCRP.SMW0REL~003 | 159,985 | 34 | 15,349 | 110,969 | 5,439 | 5 |
| SAPCRP.SMOFDSTATB~0 | 45,969 | 109 | 13,295 | 96,119 | 5,011 | 5 |
| SAPCRP.TRFCQIN~2 | 22,086 | 38 | 2,260 | 16,339 | 839 | 5 |
| SAPCRP.TRFCQIN~6 | 21,633 | 20 | 1,200 | 8,676 | 433 | 5 |
| SAPCRP.TRFCQIN~5 | 21,440 | 44 | 2,500 | 18,074 | 943 | 5 |
| SAPCRP.TST03~0 | 82,844 | 34 | 7,973 | 57,642 | 2,817 | 5 |
| SAPBWP./BI0/IPROD_HIER~00 | 102,953 | 18 | 5,130 | 37,088 | 1,853 | 5 |
| SAPCRP.TRFCQIN~4 | 21,075 | 76 | 3,600 | 26,027 | 1,602 | 6 |
| SAPCRP.TRFCQIN~1 | 21,075 | 58 | 2,850 | 20,605 | 1,222 | 6 |
| SAPCRP.TRFCQIN~3 | 22,236 | 38 | 1,890 | 13,664 | 845 | 6 |
| SAPCRP.SMW3_BDOC~1 | 506,798 | 20 | 22,208 | 160,557 | 10,136 | 6 |
| SAPCRP.TRFCQIN~0 | 21,727 | 90 | 4,600 | 33,257 | 1,955 | 6 |
Action: Reorganization
This is also performance-relevant if these indexes are used to retrieve data in index range scans. The difference between KB used and KB required shows how much disk space can be recovered through reorganization.
Query Script
set pages 9999;
set linesize 200;
set heading on;
column c1 heading "Owner.Index Name" format a30;
column c2 heading "Rows" format 999,999,999;
column c3 heading "Average Row Length" format 99,999;
column c4 heading "Leaf Blocks " format 999,999,999;
column c5 heading "Kb Used" format 999,999,999;
column c6 heading "Kb Needed" format 999,999,999,999;
column c7 heading "Storage Quality (%)" format 999,999;
SELECT * FROM
(SELECT
SUBSTR(T.OWNER, 1, 20) ||'.'|| SUBSTR(I.INDEX_NAME, 1, 20) C1,
I.NUM_ROWS C2,
SUM(TC.AVG_COL_LEN + 1) + 7 C3,
I.LEAF_BLOCKS C4,
ROUND(I.LEAF_BLOCKS * (8079 - 23 * I.INI_TRANS) *(1 - I.PCT_FREE / 100) / 1000, 0) C5,
ROUND((SUM(TC.AVG_COL_LEN + 1) + 7) * I.NUM_ROWS /1000, 0) C6,
ROUND((SUM(TC.AVG_COL_LEN + 1) + 7) * I.NUM_ROWS /(I.LEAF_BLOCKS*(8079 - 23 * I.INI_TRANS)*(1 - I.PCT_FREE / 100)) * 100, 0) C7
FROM DBA_TABLES T, DBA_INDEXES I,DBA_TAB_COLUMNS TC,DBA_IND_COLUMNS IC
WHERE T.TABLE_NAME = TC.TABLE_NAME AND
T.OWNER = TC.OWNER AND
I.INDEX_NAME = IC.INDEX_NAME AND
I.OWNER = IC.INDEX_OWNER AND
TC.TABLE_NAME = IC.TABLE_NAME AND
TC.OWNER = IC.INDEX_OWNER AND
TC.COLUMN_NAME = IC.COLUMN_NAME AND
I.INDEX_TYPE = 'NORMAL' AND
I.OWNER LIKE 'SAP%' AND
I.LEAF_BLOCKS > 200
GROUP BY T.TABLE_NAME, T.OWNER, I.OWNER,I.NUM_ROWS, I.LEAF_BLOCKS,I.INDEX_NAME, I.INI_TRANS, I.PCT_FREE
ORDER BY 7)
WHERE ROWNUM <=40;
## Order by Poor space (MB Recover space after reorganize) show 80 rows
| Owner.Index Name | Rows | Average Row Length | Leaf Blocks | Kb Used | Kb Needed | Storage Quality (%) | MB Recover Space After Reorg |
| SAPCRP.ZCST_BILL_DTL~0 | 64,638,190 | 47 | 656,925 | 4,638 | 2,967 | 64 | 2,967 |
| SAPBWP./BIC/AZO_M01300~0 | 24,960,485 | 79 | 353,972 | 2,499 | 1,926 | 77 | 1,926 |
| SAPBWP./BIC/AZO_S0100~0 | 14,215,097 | 123 | 251,638 | 1,777 | 1,707 | 96 | 1,707 |
| SAPBWP./BIC/AZO_S1000~0 | 13,565,569 | 123 | 286,756 | 2,025 | 1,629 | 80 | 1,629 |
| SAPBWP.RSMONMESS~0 | 20,892,710 | 76 | 394,629 | 2,786 | 1,551 | 56 | 1,551 |
| SAPBWP./BIC/AZO_S1100~0 | 12,161,933 | 123 | 259,181 | 1,830 | 1,461 | 80 | 1,461 |
| SAPBWP./BIC/AZO_S1300~0 | 11,966,700 | 104 | 173,400 | 1,224 | 1,215 | 99 | 1,215 |
| SAPBWP./BIC/AZO_SAL0500~0 | 10,704,570 | 100 | 153,369 | 1,083 | 1,045 | 97 | 1,045 |
| SAPCRP./1CRMGC/HTOCLRRL~0 | 13,901,234 | 77 | 290,026 | 2,048 | 1,045 | 51 | 1,045 |
| SAPBWP./BIC/AZO_STK0200~0 | 18,730,277 | 49 | 158,454 | 1,119 | 896 | 80 | 896 |
| SAPBWP./BIC/AZO_CHSLB00~0 | 13,667,995 | 65 | 170,810 | 1,206 | 868 | 72 | 868 |
| SAPCRP.ZCST_BILL_DTL~Z01 | 54,857,041 | 16 | 153,371 | 1,083 | 857 | 79 | 857 |
| SAPBWP./BIC/AZO_CHSAL00~0 | 13,085,173 | 66 | 123,815 | 874 | 843 | 96 | 843 |
| SAPBWP./BIC/AZO_SAWRK00~0 | 13,059,930 | 66 | 123,565 | 872 | 842 | 96 | 842 |
| SAPBWP.ZCST_BILL_DTL~0 | 17,152,275 | 47 | 192,891 | 1,362 | 787 | 58 | 787 |
| SAPBWP.RSMONMESS~RID | 14,562,565 | 52 | 205,434 | 1,450 | 740 | 51 | 739 |
| SAPCRP.CRM_JCDS~0 | 17,467,193 | 38 | 123,460 | 872 | 648 | 74 | 648 |
| SAPCRP./1CRMGC/HTOCHRRL~0 | 8,005,275 | 77 | 157,321 | 1,111 | 602 | 54 | 602 |
| SAPCRP./1CRMGC/HTCAPRRL~0 | 7,877,997 | 77 | 156,215 | 1,103 | 592 | 54 | 592 |
| SAPBWP./BIC/AZO_STK0100~0 | 14,054,338 | 43 | 107,223 | 757 | 590 | 78 | 590 |
| SAPBWP.RSMONMESS~AU1 | 14,606,143 | 41 | 201,029 | 1,419 | 585 | 41 | 585 |
| SAPBWP.D010TAB~0 | 13,301,161 | 45 | 145,947 | 1,030 | 585 | 57 | 584 |
| SAPBWP./BIC/AZO_STK0300~0 | 13,823,092 | 43 | 107,084 | 756 | 580 | 77 | 580 |
| SAPBWP./BIC/AZO_S1600~0 | 15,956,040 | 37 | 77,600 | 548 | 577 | ### | 576 |
| SAPBWP.D010TAB~1 | 12,153,890 | 45 | 131,780 | 930 | 534 | 57 | 534 |
| SAPBWP./BIC/AZO_SAL0400~0 | 10,006,078 | 52 | 81,749 | 577 | 508 | 88 | 508 |
| SAPBWP./BIC/AZO_SAL0100~0 | 10,001,888 | 52 | 76,125 | 537 | 508 | 95 | 508 |
| SAPBWP./BIC/AZO_SAL0200~0 | 10,001,100 | 52 | 76,119 | 537 | 508 | 95 | 508 |
| SAPBWP./BIC/AZO_S1400~0 | 10,880,586 | 45 | 74,833 | 528 | 478 | 91 | 478 |
| SAPCRP.CRMD_ORDER_INDEXRF | 10,965,251 | 43 | 101,138 | 714 | 460 | 64 | 460 |
| SAPCRP.CRMD_ORDER_INDEXZ2 | 10,965,833 | 42 | 64,578 | 456 | 450 | 99 | 450 |
| SAPCRP.CRMD_ORDER_INDEXZ3 | 10,964,830 | 42 | 65,956 | 466 | 450 | 97 | 450 |
| SAPCRP.ZMCO_STATS | 8,896,352 | 51 | 105,030 | 742 | 443 | 60 | 443 |
| SAPCRP.CRMD_ORDER_INDEXDW | 10,964,882 | 41 | 99,820 | 705 | 439 | 62 | 439 |
| SAPCRP.CRM_JEST~0 | 12,941,851 | 34 | 81,270 | 574 | 430 | 75 | 430 |
| SAPBWP./BIC/AZO_ALLST00~0 | 12,837,796 | 34 | 66,102 | 467 | 426 | 91 | 426 |
| SAPBWP./BIC/AZO_S1100~Z01 | 10,596,498 | 41 | 87,664 | 619 | 424 | 69 | 424 |
| SAPCRP.CCMLOG~0 | 8,851,924 | 49 | 120,381 | 850 | 424 | 50 | 424 |
| SAPCRP.CRMD_LINK~0 | 8,709,409 | 45 | 72,070 | 509 | 383 | 75 | 383 |
| SAPCRP.ZCST_FUNCT_DTL~0 | 8,716,423 | 44 | 92,365 | 652 | 375 | 57 | 374 |
| SAPCRP.CRMD_ORDER_INDEXZ0 | 10,966,294 | 34 | 72,994 | 515 | 364 | 71 | 364 |
| SAPCRP./1CRMGC/HTOCLRRLSI | 9,251,019 | 40 | 117,853 | 832 | 361 | 43 | 361 |
| SAPCRP.CRMD_PARTNER~FCT | 6,955,525 | 53 | 78,753 | 556 | 360 | 65 | 360 |
| SAPBWP./BIC/SZ_DLVNO~0 | 20,124,479 | 18 | 90,770 | 641 | 354 | 55 | 354 |
| SAPCRP.STXL~0 | 5,221,215 | 68 | 61,718 | 436 | 347 | 80 | 347 |
| SAPCRP.D010TAB~0 | 7,332,970 | 48 | 52,770 | 373 | 344 | 92 | 344 |
| SAPCRP.CDBD_STATUS~0 | 7,603,327 | 44 | 85,405 | 603 | 327 | 54 | 327 |
| SAPCRP.STXH~0 | 5,382,543 | 62 | 57,482 | 406 | 326 | 80 | 326 |
| SAPCRP.CRMD_ORDER_INDEX~0 | 10,827,782 | 30 | 78,732 | 556 | 317 | 57 | 317 |
| SAPCRP.CRMD_ORDER_INDEXPH | 10,966,093 | 29 | 75,507 | 533 | 311 | 58 | 311 |
| SAPCRP.D010TAB~1 | 6,447,630 | 48 | 61,440 | 434 | 302 | 70 | 302 |
| SAPCRP.SWPNODELOG~0 | 4,894,473 | 63 | 68,538 | 484 | 301 | 62 | 301 |
| SAPCRP.CDBD_STATUS~Y02 | 6,624,698 | 44 | 58,020 | 410 | 285 | 69 | 285 |
| SAPCRP.SMODBLTXT~0 | 4,934,261 | 59 | 52,773 | 373 | 284 | 76 | 284 |
| SAPCRP.CDBD_STATUS~Y01 | 6,436,421 | 44 | 55,144 | 389 | 277 | 71 | 276 |
| SAPCRP.CRMD_ORDER_INDEXIT | 10,966,036 | 25 | 54,172 | 382 | 268 | 70 | 268 |
| SAPCRP.CRMD_ORDER_INDEXHD | 10,965,889 | 25 | 55,569 | 392 | 268 | 68 | 268 |
| SAPBWP.RSMONMESS~AXX | 14,282,893 | 19 | 102,345 | 723 | 265 | 37 | 265 |
| SAPBWP.D010INC~0 | 4,042,546 | 67 | 60,766 | 429 | 265 | 62 | 264 |
| SAPCRP.MWLOB_MIG_KEYS~0 | 5,022,113 | 53 | 48,087 | 340 | 260 | 77 | 260 |
| SAPBWP.RSMONMESS~AXY | 14,694,855 | 18 | 148,374 | 1,048 | 258 | 25 | 258 |
| SAPCRP.CDBD_STATUS~Z01 | 6,588,330 | 40 | 53,360 | 377 | 257 | 68 | 257 |
| SAPCRP.CRMD_AUSP_HIST~0 | 4,047,240 | 65 | 52,548 | 371 | 257 | 69 | 257 |
| SAPBWP.RSHIENODETMP~0 | 5,477,166 | 47 | 69,295 | 489 | 251 | 51 | 251 |
| SAPCRP.CCMLOGD~0 | 10,173,589 | 25 | 65,227 | 461 | 248 | 54 | 248 |
| SAPCRP.CRMD_PARTNER~0 | 9,064,310 | 28 | 63,439 | 448 | 248 | 55 | 248 |
| SAPCRP.CRMD_ORDER_INDEXBO | 10,966,381 | 23 | 59,228 | 418 | 246 | 59 | 246 |
| SAPBWP.EDIDS~0 | 4,093,444 | 61 | 41,194 | 291 | 244 | 84 | 244 |
| SAPCRP.SRRELROLES~0 | 3,955,049 | 62 | 213,009 | 1,504 | 239 | 16 | 239 |
| SAPCRP.CRMD_LINK~SET | 8,540,354 | 28 | 57,322 | 405 | 234 | 58 | 233 |
| SAPBWP./BIC/AZO_S1600~010 | 15,893,294 | 15 | 49,070 | 346 | 233 | 67 | 233 |
| SAPCRP.CRM_JEST~I | 12,310,755 | 19 | 77,487 | 547 | 228 | 42 | 228 |
| SAPCRP./1CRMGC/HTOCHRRLSI | 5,761,530 | 40 | 68,689 | 485 | 225 | 46 | 225 |
| SAPBWP./BIC/SZ_DOCNO~0 | 10,563,531 | 21 | 47,789 | 337 | 217 | 64 | 217 |
| SAPBWP./BI0/ASAL_DS010001 | 2,003,537 | 109 | 42,933 | 303 | 213 | 70 | 213 |
| SAPCRP.SMONOTE~Y01 | 3,451,832 | 63 | 38,748 | 274 | 212 | 78 | 212 |
| SAPCRP.CRM_JSTO~0 | 7,583,972 | 28 | 42,702 | 301 | 207 | 69 | 207 |
| SAPBWP.D010INC~1 | 3,129,970 | 67 | 40,630 | 287 | 205 | 71 | 205 |
| SAPCRP.SMONOTE~0 | 4,756,619 | 44 | 42,298 | 299 | 204 | 68 | 204 |
| SAPCRP.CCMLOG~DAT | 7,497,761 | 27 | 56,845 | 401 | 198 | 49 | 198 |
set pages 9999;
set linesize 255;
set heading on;
column c1 heading "Owner.Index Name" format a25;
column c2 heading "Rows" format 999,999,999;
column c3 heading "Avg Row Len" format 9,999;
column c4 heading "Leaf Blk " format 9,999,999;
column c5 heading "MB Used" format 999,999;
column c6 heading "MB Needed" format 999,999;
column c7 heading "Storage QA(%)" format 999;
column c8 heading "MB Recover Space After Reorg" format 999,999;
SELECT * FROM
(SELECT
SUBSTR(T.OWNER, 1, 20) ||'.'|| SUBSTR(I.INDEX_NAME, 1, 20) C1,
I.NUM_ROWS C2,
SUM(TC.AVG_COL_LEN + 1) + 7 C3,
I.LEAF_BLOCKS C4,
(ROUND(I.LEAF_BLOCKS * (8079 - 23 * I.INI_TRANS) * (1 - I.PCT_FREE / 100) / 1000, 0))/1024 C5,
(ROUND((SUM(TC.AVG_COL_LEN + 1) + 7) * I.NUM_ROWS /1000, 0))/1024 C6,
ROUND((SUM(TC.AVG_COL_LEN + 1) + 7) * I.NUM_ROWS /(I.LEAF_BLOCKS*(8079 - 23 * I.INI_TRANS)*(1 - I.PCT_FREE / 100)) * 100, 0) C7,
(ROUND((SUM(TC.AVG_COL_LEN + 1) + 7) * I.NUM_ROWS /1000, 0)- ROUND((SUM(TC.AVG_COL_LEN + 1) + 7) * I.NUM_ROWS /(I.LEAF_BLOCKS*(8079 - 23 * I.INI_TRANS)*(1 - I.PCT_FREE / 100)) * 100, 0))/1024 C8
FROM DBA_TABLES T, DBA_INDEXES I,DBA_TAB_COLUMNS TC,DBA_IND_COLUMNS IC
WHERE T.TABLE_NAME = TC.TABLE_NAME AND
T.OWNER = TC.OWNER AND
I.INDEX_NAME = IC.INDEX_NAME AND
I.OWNER = IC.INDEX_OWNER AND
TC.TABLE_NAME = IC.TABLE_NAME AND
TC.OWNER = IC.INDEX_OWNER AND
TC.COLUMN_NAME = IC.COLUMN_NAME AND
I.INDEX_TYPE = 'NORMAL' AND
I.OWNER LIKE 'SAP%' AND
I.LEAF_BLOCKS > 200 AND
TC.AVG_COL_LEN is not null AND
I.PCT_FREE is not null AND
I.INI_TRANS is not null
GROUP BY T.TABLE_NAME, T.OWNER, I.OWNER,I.NUM_ROWS, I.LEAF_BLOCKS,I.INDEX_NAME, I.INI_TRANS, I.PCT_FREE
ORDER BY 6 desc)
WHERE ROWNUM <=80;
0 comments:
Post a Comment