Extent Utilization

Posted by S33 on 20-Dec-2017 13:57

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

All Replies

Posted by George Potemkin on 20-Dec-2017 14:10

How much db and bi is in use (Dmitri Levin)

proora.com/.../dbuse.html

Posted by Paul Koufalis on 20-Dec-2017 14:11

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

Posted by ChUIMonster on 20-Dec-2017 15:27

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% │

Posted by ChUIMonster on 20-Dec-2017 15:30

Some day maybe Communities will support the posting of technically oriented content...

Posted by cjbrandt on 20-Dec-2017 16:04

Try the following command on a test db.  It might accomplish what you need.

proutil -C dbname -C iostats

Posted by 2356 on 28-Dec-2017 09:36

Tried proutil -C dbname -C iostats > Could not recognized? modified the command to > proutil dbname -C iostats...

This thread is closed