performance and tunning

Posted on 2006-05-21
Last Modified: 2008-03-04
Dear Sir
                 I am confused... Please Help
             I found db_block_size parameter in my database ,the value is 4096.

but when i quired block_id,bytes from user_free_space
The results are confusing for me.Results are

block_id                 bytes        
888222                  24899584
115140                  13299712
102467                  20929782

            I am confused that a how a block of size 4k is going to have such a large ammount of space?? please clearify this scenerio
Best Wishes

Naveen Mathur
Question by:hg1010
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    these are adjacent blocks, starting at the given block_id

    Author Comment

    Its ok, that these are adjacent blocks, starting at the given block_id but how come it possible to have such large values in data blocks ,yet the block size is 4k
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    well, your database seems to have a size of free space of at least 57MB, which are in these 3 parts.
    as your data files are in blocks of 4K, you have size/4K = number of blocks in all these files.
    LVL 16

    Expert Comment

    Try this query

    select block_id,bytes/4096 from user_free_space ;

    LVL 3

    Accepted Solution

    Please read Oracle documentation on USER_FREE_SPACE:

    Column                                         Description
    TABLESPACE_NAME                        Name of the tablespace containing the extent
    FILE_ID                                          File identifier number of the file containing the extent
    BLOCK_ID                                      !!!STARTING!!!  block number of the extent
    BYTES                                           Size of the extent (in bytes)
    BLOCKS                                         Size of the extent (in Oracle blocks)
    RELATIVE_FNO                              Relative file number of the file containing the extent

    block_id is identifier of block, where free space starts.\
    bytes is length of continous free space (free space extent).

    block_id                 bytes        
    888222                  24899584

    block 888222 is the first block of free area of 24899584 bytes,
    or block 888222 is the first of 6079 free blocks (block 888222 and 6078 blocks following),
    or blocks from 888222 until 894301 are free.

    LVL 19

    Expert Comment

    JacekMycha said it well.

    just some high level picture:

    dba_free_space registers information about FREE EXTENTs in each tablespace.

    What is free extent? It is a set of contiguous free blocks, not a single block.

    When Oracle tries to allocate a new extent to an object, it will search for those free extents or contiguous free blocks first, it will try to coalesce those free spaces (not move) or extend the datafile.


    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
    Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now