DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 30

Thread: PUZZLE: empty blocks (the T-Shirt I've never got)

  1. #1
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Here is a puzzle for everyone. The question (or better the answer to it) probably doesn't have any real-life value, it is more like an academic one. But as the question sounds realy like a very basic one it is kind of surprising that it can't be answered. Or to be more accurate, the correct (I belive) answer is: IT CAN'T BE DONE.

    So here goes the question:

    I have table TAB1, nothing special about it, no partitioning, no IOT, no longs,lobs etc in it, not clustered, not a nested table, not a temporary table, pretty standard (default) storage parameters. What I would like to know about this table is the following:


    *Exactly* how many blocks in this table are empty, meaning they do not contain any table data?

    I'm not interested in the number of blocks above the HWM, which obviously all are empty. I wan't to know about the empty blocks below the HWM. And I wan't a general answer, that is valid in all circumstances, provided the table is a "normal" one (no special datatypes and/or tabletypes, as described above).

    As I've said, AFAIK the correct answer is: "There is no way you could get the exact number of empty datablocks in a table".

    Anybody who think he can give a different/better answer is warmy invited to join the discussion. Although I'm not giving away any T-Shirts .
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  2. #2
    Join Date
    Nov 2000
    Posts
    212
    I believe you can just dump all blocks and see what is inside. To dump blocks:
    alter system dump datafile &m_file block min &m_block block max &m_block

    -------------------------------------------------------------------
    "All problems can be solved just by scanning all possible solutions in search of the correct one" - one of my teachers.

  3. #3
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Jurij,

    Here is a way you could at least grap a book on oracle performance tuning.

    http://www.quest.com/newsletter/v2i6/puzzles.asp



    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    LND, if I would be giving T-Shirts you would defenitely got one. Yes, this is the only possible way of doing this, and I've simply forgotten to add this part to the question (I swear I was going to do so when I was writing the post, I had simply forgotten): ".... short of dumping all the datablocks and inspecting them one after another".

    In other words, can it be done with any query, pacakge, procedure, standard utility etc...
    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 2000
    Posts
    212
    what about debuging(i.e. to trace or to look into sql area) of SMON or whatever which is responsible for collating empty extents and finding what tables it looks for?

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by sambavan
    Jurij,

    Here is a way you could at least grap a book on oracle performance tuning.

    http://www.quest.com/newsletter/v2i6/puzzles.asp
    Hey, Sam, do you think I haven't tried allready? The puzzle was published quite some time ago
    http://www.quest.com/newsletter/v2i7/puzzles.asp

    but I newer got a book!!! The puzzle article was removed in a next couple of isues of the newsletter, so no answers (if there were any) were published either.

    Am I the unlucky one or what? Oracle Support owe me a T-Shirt, Quuest owe me a book, but I don't think I'll get either of them ever.
    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 2000
    Posts
    212
    one more thought: what about making full scan on the table (w/o indexes and rollback segments and with db buffers empty) and looking into v$...stat how many blocks are accessed?

  8. #8
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Question to LND,

    How would you get an empty db_buffer at any point?

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  9. #9
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by LND
    what about debuging(i.e. to trace or to look into sql area) of SMON or whatever which is responsible for collating empty extents and finding what tables it looks for?
    LND, you are missing a point here. We are talking about a table, not a tablespace. There is no coalescing of free blocks/extents inside a table.

    Once more, in a simple words:

    I have a table of , let's say, 100 blocks. I fill all the blocks with the table data. Then I delete 90% of all rows. So there is a very high probability that most of table blocks are totaly empty. The question is: how may of them?
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  10. #10
    Join Date
    Jun 2001
    Posts
    29

    Simple Solution?

    Select the distinct block ids from all the rowids in the table. Compare this with the blocks allocated to all of the extents (from dba_extents). A quick bit of subtraction will tell you which blocks are empty.

    That's the idea...the actual code to perform this magic...let me work on that!
    Daniel W. Fink
    oracledba@ix.netcom.com

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