How can I tell how much of the database extents are in use? It looks like proutil -C dbanalys gives me info by area, not extent... For example, if I have quantity 5 of 1Gb extents in an area, and 2 Gb of data, can I tell if it's all packed into the first 2 extents or if it is spread around? (11.7 on RHEL)
Thanks -- Jim
How much db and bi is in use (Dmitri Levin)
A bit of math will do it. Dividing the fixed extent sizes by the block size will give you the number of blocks in each physical file. DBAnalys will tell you how many active blocks there are.
WIth your example, each 1 GB extent has 262,144 4K blocks. DBAnalys tells you the block high water mark. Divide by 262,144 and you'll know what file new blocks will be formatted in and hence which files are already being used.
Paul
You could ask ProTop.... (the "a" command):
┌─────────────────────────────────────────────────────────────────────── Storage Areas ────────────────────────────────────────────────────────────────────────┐ │ # BX Area Name Allocated Variable Tot GB Free GB %Allo v %LastX BSZ RPB CSZ #Tbls #Idxs #LOBs #Exts Var? * Max% Bug% │ │ ─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────── │ │ > 18 B1 misc64_dat 15.63 19.36 34.98 0.00 224% 100% 8 64 64 68 0 0 2 yes 0.000% 0.000% │ │ 151 B1 ar-trans-log_idx 1.95 1.08 3.04 0.00 155% 100% 8 1 64 0 5 0 2 yes 0.000% 0.019% │ │ 145 B1 so-pick-log_idx 1.95 0.91 2.86 0.00 146% 100% 8 1 64 0 5 0 2 yes 0.000% 0.017% │ │ 153 B1 so-pack-log_idx 1.95 0.62 2.57 0.00 132% 100% 8 1 64 0 5 0 2 yes 0.000% 0.016% │ │ 6 B1 Schema Area 0.15 0.02 0.17 0.00 116% 100% 8 64 1 0 0 0 4 yes 0.000% 0.000% │ │ 117 B1 customer-log_idx 3.91 0.12 4.03 0.00 103% 100% 8 1 64 0 2 0 2 yes 0.000% 0.025% │ │ 104 B1 so-trans-d_dat 31.25 0.70 31.95 0.00 102% 100% 8 32 512 1 0 0 3 yes 0.000% 0.000% │ │ 201 B1 audit_idx 3.91 0.08 3.99 0.00 102% 100% 8 1 512 0 0 0 2 yes 0.000% 0.000% │ │ 134 B1 price-cp_dat 7.81 0.00 7.82 0.23 97% 97% 8 128 512 1 0 0 2 yes 0.000% 0.000% │ │ 21 B1 misc128_idx 31.25 0.00 31.25 2.37 92% 85% 8 1 64 0 772 0 3 yes 0.000% 0.176% │ │ 115 B1 im-trans_idx 3.91 0.00 3.91 0.30 92% 92% 8 1 64 0 10 0 2 yes 0.000% 0.022% │ │ 149 B1 im-trans-x_idx 1.95 0.00 1.95 0.17 91% 91% 8 1 64 0 2 0 2 yes 0.000% 0.011% │ │ 125 B1 so-pack-d_idx 1.95 0.00 1.95 0.18 91% 91% 8 1 64 0 9 0 2 yes 0.000% 0.011% │ │ 130 B1 so-trans-s_dat 7.81 0.00 7.82 0.83 89% 89% 8 64 512 1 0 0 2 yes 0.000% 0.000% │ │ 135 B1 price-cp_idx 7.81 0.00 7.81 0.87 89% 78% 8 1 64 0 7 0 4 yes 0.000% 0.042% │ │ 200 B1 audit_dat 15.63 0.00 15.63 1.75 89% 89% 8 128 512 0 0 0 2 yes 0.000% 0.000% │ │ 159 B1 dwh_idx 3.91 0.00 3.91 0.44 89% 77% 8 1 64 0 2 0 3 yes 0.000% 0.021% │ │ 168 B1 so-pack-mh10-d-prod_dat 3.91 0.00 3.91 0.45 89% 89% 8 128 512 1 0 0 2 yes 0.000% 0.000% │ │ 16 B1 misc32_dat 7.81 0.00 7.81 1.02 87% 87% 8 32 64 8 0 0 2 yes 0.000% 0.000% │ │ 160 B1 so-pack_dat 3.91 0.00 3.91 0.52 87% 87% 8 32 512 1 0 0 2 yes 0.000% 0.000% │ │ 146 B1 s_crm-valid-queue-log_dat 15.63 0.00 15.63 2.11 87% 73% 8 8 512 1 0 0 3 yes 0.000% 0.000% │ │ 107 B1 prod-track-summ_idx 11.72 0.00 11.72 1.72 85% 85% 8 1 64 0 14 0 2 yes 0.000% 0.061% │ │ 132 B1 so-pick-d-prod_dat 7.81 0.00 7.82 1.19 85% 85% 8 64 512 1 0 0 2 yes 0.000% 0.000% │ │ 126 B1 im-trans-value_dat 7.81 0.00 7.82 1.24 84% 84% 8 128 512 1 0 0 2 yes 0.000% 0.000% │ │ 166 B1 comm-trans_dat 3.91 0.00 3.91 0.68 83% 83% 8 128 512 1 0 0 2 yes 0.000% 0.000% │ │ 116 B1 customer-log_dat 15.63 0.00 15.63 2.79 82% 82% 8 128 512 1 0 0 2 yes 0.000% 0.000% │ │ 20 B1 misc128_dat 46.88 0.00 46.88 8.56 82% 45% 8 128 64 400 0 1 4 yes 0.000% 0.000% │ │ 167 B1 comm-trans_idx 1.95 0.00 1.95 0.37 81% 81% 8 1 64 0 5 0 2 yes 0.000% 0.010% │ │ 141 B1 so-pick-prod_idx 1.95 0.00 1.95 0.39 80% 80% 8 1 64 0 3 0 2 yes 0.000% 0.010% │ │ 100 B1 prod-track_dat 62.50 0.00 62.50 12.61 80% 19% 8 64 512 1 0 0 5 yes 0.000% 0.000% │ │ 163 B1 prod-exp-loc-ql_idx 1.95 0.00 1.95 0.41 79% 79% 8 1 64 0 4 0 2 yes 0.000% 0.009% │ │ 127 B1 im-trans-value_idx 1.95 0.00 1.95 0.42 79% 79% 8 1 64 0 4 0 2 yes 0.000% 0.009% │ │ 161 B1 so-pack_idx 1.95 0.00 1.95 0.42 78% 78% 8 1 64 0 18 0 2 yes 0.000% 0.009% │ │ 22 B1 misc256_dat 15.63 0.00 15.63 3.54 77% 77% 8 256 64 166 0 0 2 yes 0.000% 0.000% │ │ 101 B1 prod-track_idx 15.63 0.00 15.63 4.07 74% 74% 8 1 64 0 4 0 2 yes 0.000% 0.071% │ │ 105 B1 so-trans-d_idx 15.63 0.00 15.63 4.32 72% 72% 8 1 64 0 10 0 2 yes 0.000% 0.069% │ │ 170 B1 cust-purch-d_dat 3.91 0.00 3.91 1.19 70% 70% 8 128 512 1 0 0 2 yes 0.000% 0.000% │ │ 102 B1 so-trans-log_dat 46.88 0.00 46.88 14.52 69% 7% 8 128 512 1 0 0 4 yes 0.000% 0.000% │ │ 158 B1 dwh_dat 7.81 0.00 7.82 2.45 69% 69% 8 128 512 1 0 0 2 yes 0.000% 0.000% │ │ 8 B1 unused_dat 1.95 0.00 1.95 0.64 67% 67% 8 64 8 324 0 0 2 yes 0.000% 0.000% │ │ 140 B1 so-pick-prod_dat 7.81 0.00 7.82 2.68 66% 66% 8 128 512 1 0 0 2 yes 0.000% 0.000% │ │ 148 B1 im-trans-x_dat 7.81 0.00 7.82 2.71 65% 65% 8 128 512 1 0 0 2 yes 0.000% 0.000% │ │ 136 B1 ship-debit-batch-log_dat 7.81 0.00 7.82 2.74 65% 65% 8 128 512 1 0 0 2 yes 0.000% 0.000% │ │ 110 B1 im-trans-prod_dat 15.63 0.00 15.63 5.75 63% 63% 8 128 512 1 0 0 2 yes 0.000% 0.000% │ │ 128 B1 price-cp-r_dat 15.63 0.00 15.63 5.77 63% 26% 8 64 512 1 0 0 3 yes 0.000% 0.000% │ │ 139 B1 price-lp-r_idx 3.91 0.00 3.91 1.49 62% 62% 8 1 64 0 2 0 2 yes 0.000% 0.015% │ │ 106 B1 prod-track-summ_dat 31.25 0.00 31.25 11.93 62% 24% 8 64 512 1 0 0 3 yes 0.000% 0.000% │ │ 144 B1 so-pick-log_dat 7.81 0.00 7.82 3.01 62% 62% 8 128 512 1 0 0 2 yes 0.000% 0.000% │ │ 123 B1 ar-trans_idx 3.91 0.00 3.91 1.53 61% 22% 8 1 64 0 11 0 4 yes 0.000% 0.015% │ │ 142 B1 so-pick-d_dat 7.81 0.00 7.82 3.06 61% 61% 8 64 512 1 0 0 2 yes 0.000% 0.000% │ │ 155 B1 po-trans-d-prod_idx 1.95 0.00 1.95 0.77 61% 61% 8 1 64 0 2 0 2 yes 0.000% 0.007% │ │ 154 B1 po-trans-d-prod_dat 7.81 0.00 7.82 3.08 61% 61% 8 128 512 1 0 0 2 yes 0.000% 0.000% │ │ 113 B1 so-trans_idx 3.91 0.00 3.91 1.54 60% 60% 8 1 64 0 16 0 2 yes 0.000% 0.014% │ │ 172 B1 im-trans-prod-custom_dat 3.91 0.00 3.91 1.56 60% 60% 8 128 512 1 0 0 2 yes 0.000% 0.000% │ │ 120 B1 prod-exp-loc-d_dat 15.63 0.00 15.63 6.35 59% 59% 8 128 512 1 0 0 2 yes 0.000% 0.000% │ │ 112 B1 so-trans_dat 15.63 0.00 15.63 6.36 59% 59% 8 32 512 1 0 0 2 yes 0.000% 0.000% │ │ 171 B1 cust-purch-d_idx 1.95 0.00 1.95 0.80 59% 59% 8 1 64 0 6 0 2 yes 0.000% 0.007% │ │ 111 B1 im-trans-prod_idx 7.81 0.00 7.81 3.29 58% 16% 8 1 64 0 11 0 3 yes 0.000% 0.028% │ │ 19 B1 misc64_idx 7.81 0.00 7.81 3.29 58% 16% 8 1 64 0 211 0 3 yes 0.000% 0.028% │ │ 143 B1 so-pick-d_idx 3.91 0.00 3.91 1.66 57% 57% 8 1 64 0 5 0 2 yes 0.000% 0.014% │
Some day maybe Communities will support the posting of technically oriented content...
Try the following command on a test db. It might accomplish what you need.
proutil -C dbname -C iostats
Tried proutil -C dbname -C iostats > Could not recognized? modified the command to > proutil dbname -C iostats...