This discussion is archived
9 Replies Latest reply: Jul 18, 2012 3:55 AM by robinsc RSS

What will happen to the data block after data offloading?

user60022 Explorer
Currently Being Moderated
HI All,

Since offloading can filter out the necessary information such as unnecessary columns and rows before they are passed to the database, I want to know what will happen to the data block? Will some new blocks be built at this time,which only contains the useful information?

Best regards,
Leon
  • 1. Re: What will happen to the data block after data offloading?
    933318 Newbie
    Currently Being Moderated
    No new blocks are built during the process.

    Only the required rows and columns are sent from all your storage servers to your compute nodes(Database server).

    These which inturn are put together, just the way it was done using a parrallel query on PGA heap.

    Regards,
    Ruthwik
  • 2. Re: What will happen to the data block after data offloading?
    UweHesse Expert
    Currently Being Moderated
    Keep in mind that a Smart Scan is always a Direct Read , bypassing the Buffer Cache completely.
    You do not modify blocks in order to write them back to datafiles on behalf of a Smart Scan.
    Therefore there is no problem here.

    Kind regards
    Uwe Hesse

    "Don't believe it, test it!"
    http://uhesse.com
  • 3. Re: What will happen to the data block after data offloading?
    AndyKlock Journeyer
    Currently Being Moderated
    Uwe Hesse wrote:
    Keep in mind that a Smart Scan is always a Direct Read , bypassing the Buffer Cache completely.
    That's a really good point. Though, I think a lot of the confusion is when Oracle touts:

    >"One of the unique things the Exadata storage does compared to traditional storage is return only the rows and columns that satisfy the database query rather than the entire table being queried."

    From: http://www.oracle.com/technetwork/database/exadata/exadata-technical-whitepaper-134575.pdf (though I've seen that same language in other places)


    The statement seems to imply that Exadata has the ability to strip out columns from a block, but a block is a block is a block. Offloading is remarkable at filtering out data in the storage layer that ultimately is not needed but if you only need one row in a block that has 100 rows in it, you still get the whole block (and all 100 rows) to be processed by Oracle. The columns portion of the statement is when HCC is used for a table allowing only the blocks containing the column data required for the query, and thus if a block has 1000 column values in it, it will pass all 1000 column values to the instance to be processed.
  • 4. Re: What will happen to the data block after data offloading?
    601262 Explorer
    Currently Being Moderated
    Andy Klock wrote:
    The statement seems to imply that Exadata has the ability to strip out columns from a block, but a block is a block is a block. Offloading is remarkable at filtering out data in the storage layer that ultimately is not needed but if you only need one row in a block that has 100 rows in it, you still get the whole block (and all 100 rows) to be processed by Oracle. The columns portion of the statement is when HCC is used for a table allowing only the blocks containing the column data required for the query, and thus if a block has 1000 column values in it, it will pass all 1000 column values to the instance to be processed.
    These assertions are incorrect.

    Blocks sent to the database grid as a result of a Smart Scan contain only the necessary columns and rows for the db grid to do its processing (after filter restrictions and projection restrictions are applied). These Smart Scan blocks are created at run time by the storage server so they have no bearing on the blocks that reside physically on disk which is why they can not be reused by other queries via the SGA and are read directly into the PGA space.

    --
    Regards,
    Greg Rahn | blog | twitter | linkedin
  • 5. Re: What will happen to the data block after data offloading?
    AndyKlock Journeyer
    Currently Being Moderated
    Thanks Greg, I really appreciate the correction. I think I had been getting hung up on the word block. Even in spite of Uwe's bold Direct Read I completely missed the boat.

    Thanks guys.
  • 6. Re: What will happen to the data block after data offloading?
    user60022 Explorer
    Currently Being Moderated
    Thank you all of you.

    Hi Greg,

    If I understand you correctly, new blocks are generated by cell server on the fly?

    Best regards,
    Leon
  • 7. Re: What will happen to the data block after data offloading?
    912595 Expert
    Currently Being Moderated
    user12064076 wrote:
    Thank you all of you.

    Hi Greg,

    If I understand you correctly, new blocks are generated by cell server on the fly?

    Best regards,
    Leon
    If i understand your question correctly, you want to know what would happen to my Select statement (containing rows/columns) if there are some modified blocks(which contain those rows/columns) in buffer cahce in case of smart scan?
    -My perception to this would be smart scan still be used but if database finds that i need to create a Consistent Read copy of blocks then, database will use normal processing i.e database buffer cache would be used.

    However there are some intelligence involved in checking the Transaction table slots(to check the status of ongoing transactions), so if database finds that transaction is still active then database has to fall back to normal database processing, so in this case you wont get full advantage of smart scan. Maybe some expert here can put more light

    hope this help
  • 8. Re: What will happen to the data block after data offloading?
    robinsc Explorer
    Currently Being Moderated
    as the storage servers use idb to communicate with the compute nodes they only resort to shipping blocks as a fallback case.Under normal circumstances only the required rows and columns would be sent straight to the PGA.
  • 9. Re: What will happen to the data block after data offloading?
    robinsc Explorer
    Currently Being Moderated
    you would see a number of enq: KO - fast object checkpoint as oracle prior to starting a smartscan tries to flush the maximum of the object to disk. after that the db servers do check the buffer cache for blocks that fulfill the required criteria and merge them into the results in the PGA on the fly which is why you will see both a filter as well as a storage predicate in smartscan query explain plans. the filter is for the db nodes where as the predicate is for the storage nodes.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points