Database Dictionary View Query
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Database Dictionary View Query

  1. #1
    Join Date
    Nov 2001
    Location
    Sheffield, England, UK
    Posts
    78

    Database Dictionary View Query

    All,

    I have been writing some table/index analysis scripts recently, and am now looking to make them backward compatible with Server version 7.

    I am just curious about data available within the DBA_TABLES view.

    In my current report, I look at how much free space is available on a table segment's freelist by calculating:

    DBA_TABLES.AVG_SPACE_FREELIST_BLOCKS x
    DBA_TABLES.NUM_FREELIST_BLOCKS

    Unfortunately, in the V7 dictionary this information is not available.

    Does anyone know if it is calculable from data stored elsewhere in the dictionary, or via a PL/SQL util package?

    Furthermore, if we try looking at the number of blocks visible in DBA_SEGMENTS and DBA_TABLES, I get the followig results:

    'blocks' assigned under DBA_SEGMENTS differs from 'blocks' in DBA_TABLES for the same segment. Neither does it appear to add up to 'blocks' + 'empty_blocks'.

    Anyone any enlightenment to shed on any of the above?

    :-)

    - Tony

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    the data in dba_tables is gathered from statistics periodically collected through "Analyze table ..." or "DBMS_STATS ..." etc.

    Size according to DBA_SEGMENTS is correct all the time.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Nov 2001
    Location
    Sheffield, England, UK
    Posts
    78
    Hmmm,

    It seems a little silly to have snapshot or estimation values in DBA_TABLES when the correct value is obtainable all the time from DBA_SEGMENTS.

    I thought about whether it was gathered from an analyze on my way home from work... but figured Oracle would be a bit more intelligent than that...

    Hey-ho. Perhaps there is a method in that madness.

    Thanks for the info.

    - Tony.

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by AJW_ID01
    Hey-ho. Perhaps there is a method in that madness.
    Of course there is. BLOCKS columns in DBA_SEGMENTS and DBA_TABLES counts two totaly different things, hence the numbers in those two columns are different.

    BLOCKS in DBA_SEGMENTS shows the number of database blocks uccupied by table segment (the sum of blocks occupied by all table extents. It doesn't matter if there was ever any data in those blocks, it simply shows how much space was grabbed by that segment up untill now.

    BLOCKS in DBA_TABLES shows number of database blocks *below the high watter mark* (in other words, number of blocks from the beginning of the segment up untill the last block that ever contained any row data). And as slimdave said, this column(s) in DBA_TABLES are populated during analyze process, because even if BLOCKS in DBA_SEGMENTS remain unchanged, the BLOCKS in DBA_TABLES may be changing all the time.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Nov 2001
    Location
    Sheffield, England, UK
    Posts
    78
    This is what I initially thought would be the case.

    DBA_SEGMENTS reporting blocks in all allocated extents.
    DBA_TABLES reporting the overall usage over time of the blocks in those extents.

    Then, of course, I became confused as to why:

    BLOCKS (which I believed would be those below HWM) and
    EMPTY_BLOCKS (which I believed would be those above HWM)

    did not seemingly add up to the total within blocks from all extents.

    I looked at various different table segments, from a test (non-changing) environment, immediately after a full analyze.

    Is EMPTY_BLOCKS referring to something completely different?

    On the other issue, is there any way from the V7 Server to calculate the number of blocks on the freelist, and the avg space available within them (as contained within the V8 DBA_TABLES dictionary view)?

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by AJW_ID01
    Then, of course, I became confused as to why:

    BLOCKS (which I believed would be those below HWM) and
    EMPTY_BLOCKS (which I believed would be those above HWM)

    did not seemingly add up to the total within blocks from all extents.
    If you add BLOCKS and AMPTY_BLOCKS from DBA_TABLES and compare this to blocks in DBA_SEGMENTS, what is the difference? AFAIK, the difference between those two numbers shopuld be 1 block (which, if I'm not mistaken, is the segment header block, which is not counted neither in BLOCKS nor in EMPTY_BLOCKS).

    So, yes, BLOCKS shows the number of blocks below (and including the one with) HWM, while EMPTY_BLOCKS shows the number of blocks above HWM. The sum of those two numbers should be equal to BLOCKS.DBA_SEGMENTS-1.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #7
    Join Date
    Nov 2001
    Location
    Sheffield, England, UK
    Posts
    78
    Ah,

    The differences I was getting were somewhat less marginal than 1 block.

    I'll go and take a look at everything again.

    Thanks for all the comments.

    :-)

    - Tony.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width