Question Bi Hwm/logical Size

Hello All,

I want to know the exact size of BI file (not physical). For ex: If BI file size is 7 GB and we disconnect the user with long running transaction from long time then the BI file size still remains 7 GB but actual/logical size of BI should get decreased to some value.


I have gone through couple of knowledgebase articles regarding this and found below one relevant where Rob Fitzpatrick has specified the reason that how can we check the actual/logical size of BI file


Help BI architecture


@Rob Fitzpatrick: Logical size of BI file can be determine by: (the number of active BI clusters * the BI cluster size). BI cluster size is available under BI log option of Promon (Like Before-image cluster size: 16384 kb (16777216 bytes) but how can I check the number of active BI clusters and how to calculate these values together.


I think HWM of BI or actual/logical size of BI can be determined by using VST tables. Kindly share if there is any relevant code regarding the same.


Regards,
Learner
 
Last edited:

Rob Fitzpatrick

ProgressTalk.com Sponsor
Try this:
Code:
find dictdb._dbstatus no-lock.
display "BI size (MB): " integer( _dbstatus-bisize * _dbstatus-biblksize / 1048576 )
 
Hello @Rob Fitzpatrick

Thanks for your reply!!!

I tried this query but its value seems same as physical file value. Its output is 7,696 where physical file size is also 7.6 GB.

Kindly suggest.

Regards,
 

RealHeavyDude

Well-Known Member
Unfortunately, as far as I know, the number of BI clusters in use you see in PROMON itself is a calculated value and is not available on any VST.

You need to calculate it yourself by identifying the oldest and the newest BI cluster where the cluster number is the _Trans-Counter field in the _Trans table.
In order for the logic to work a transaction must be active!

  1. Oldest BI cluster number: Look for the ACTIVE _Trans with the lowest _Trans-Counter.
  2. Newest BI cluster: Look for the transaction that is linked to your connection ( _Trans._Trans-UsrNum = _MyConnection._MyConn-UserId ).
That's why you need to scope the logic into a transaction to ensure that, a) there is a transaction active, b) your transaction identifies the newest BI cluster. Me, I built this based on suggesstions by the guru of gurus himself - Gus.

If someone else has a better solution please let me know.

Hope that helps.

Heavy Regards, RealHeavyDude.
 
Hello @RealHeavyDude, Thanks for your reply!

There is one active transaction from last one and half hour and one transaction that I have initiated. Oldest _Trans-Counter (from _trans table) is 54376 and new transaction number that is linked with my transaction (_Trans._Trans-UsrNum = _MyConnection. MyConn-UserId) is also 54376.

Kindly suggest that how to calculate the logical size of BI file (MB/GB) with these values if both values are same or different.

Below is the BI Log information:

Status: BI Log
Before-image cluster age time: 0 seconds
Before-image block size: 8192 bytes
Before-image cluster size: 16384 kb (16777216 bytes)
Number of before-image extents: 4
Before-image log size (kb): 5406824
Bytes free in current cluster: 3820234 (23 %)
Last checkpoint was at: 06/09/17 10:10
Number of BI buffers: 25
Full buffers: 0

One more thing, if transaction is showing as Active then what should be the checkpoint value because last checkpoint (mentioned above) is 10:10 but transaction is active from 13:28, so I think last checkpoint value should be after 13:28.


Oldest Active Transaction:


Code:
Usr Name     Type       Login time     Tx start time  Trans id Trans State

   21          SELF/ABL  06/09/17   13:08   06/09/17  13:09  263252106089  Active

Kindly suggest.


Regards,
 

RealHeavyDude

Well-Known Member
In order to get the number of BI clusters in use you need to substract the oldest from the newest cluster and add 1. Then you need to multiply the number of BI clusters in use with the cluster size. Voila, you have the size of the BI that is in use. If your BI has a fixed size or you use a BI threshold you can then calculate the utilization. I think you should be able to do the math.

Checkpoints are not tied to transactions - they are tied to BI clusters. Whenever a BI cluster fills up the database performs a checkpoint operation. Therefore, transactions can have notes in serveral BI clusters. Usually you want to be transactions as short and atomic as possible so that as few as possible BI clusters are in use. This is why long running transactions have the potential to exhaust your BI and ultimately crash your database (when the BI is exhausted and the database can not write to it anymore). And, this is why long running transactions should be avoided and usually are caused by bad code.

The transaction you mention, that is active for over 1 1/2 hour is a bad transaction. You should inspect the code or have the code inspected by the developer.

Heavy Regards, RealHeavyDude.
 
Thanks for this prompt response RHD!!!

So here, number of BI clusters in use will be 54376 – 54376 + 1, will be 1.
BI size in use = 1 * 16384 kb (cluster size)= 16384 KB (actual Bi size).

Is this correct?

Regards
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
The minimum size of the BI cluster ring is always 4 clusters.

Are you planning to use this information to determine what value you need to use with a subsequent proutil bigrow command? What is your objective?
 

TomBascom

Curmudgeon
It is something of a "worst case" but ProTop finds the oldest and newest active transaction and determines the difference in "in use clusters" from that. If there are no active transactions then there are no clusters in use. If the oldest cluster is the same as the newest cluster then there is 1 cluster "in use". Multiplying by cluster size gives "mb in use". Testing seems to confirm that this approach is "good enough" to provide warnings of excessive bi growth and to estimate things like "percentage towards bithold". It is sometimes a bit of an over-estimate but it is more informative than just looking at the bi file size from an OS perspective.

There are new VST metrics coming in OE12 that will explicitly expose the amount of bi space in use. (PUG hasn't posted the slides from Rich Banville yet so the details are a bit fuzzy...)
 
Top