cell single block physical read

We starting seeing a ton of these waits on an Exadata system:

cell single block physical read

Normally the predominant wait is

cell smart table scan

Simple table full scans were doing the single block reads, whereas they normally would do smart scans.

Oracle support had us run this command to identify cell servers with “quarantined” plans:

cellcli -e list quarantine

They identified a cell server with several of these plans and had us run this command in cellcli:

drop quarantine all

Ideally you would patch up your cell servers, etc to the current release and this would reduce the number of quarantined plans.

Oracle note 1349167.1 contains an example of why a cell server would crash and cause a plan to be quarantined.

Evidently if you have a certain number of quarantined plans (6 I think) this fact becomes “visible” to the database servers and they start using single block reads instead of the multiblock smart scans.  Dropping the quarantined plans reduces the number of plans below the threshhold and the database goes back to doing smart scans.

– Bobby

About Bobby

I live in Chandler, Arizona with my wife and three daughters. I work for US Foods, the second largest food distribution company in the United States. I have worked in the Information Technology field since 1989. I have a passion for Oracle database performance tuning because I enjoy challenging technical problems that require an understanding of computer science. I enjoy communicating with people about my work.
This entry was posted in Uncategorized. Bookmark the permalink.

15 Responses to cell single block physical read

  1. vnmaster says:

    This wait event also could be due to chained rows in the table

  2. Bobby says:

    vnmaster – Thanks for your comment. I haven’t gotten very deep into the various reasons why you get the cell single block physical reads. The post was about a very specific case where performance drastically changed and the waits went from smart scans to single block reads. But, we still get a bunch of the single block reads even with normal performance and I’m not sure why. I think part of it has to do with compression and some of the blocks not having the same type of compression as others or some of the rows within the block but I’m not sure. It could be that row migration or chained rows could cause it but I’m pretty sure there are other things that can cause single block reads. I guess we need to build a test case to prove it out.

  3. Anonymous says:

    Bobby –

    Thanks a ton for posting what you learned. My experience in recent days has been that Oracle MOS tends to treat “cell single block physical read” as a SQL tuning problem, without raising the possibility of the root cause being at the exadata level. Your article provided the key to additional investigation, which in turn enabled us to put out the fire this morning when one of our big ERPs started to go up in smoke. Thank you!

    Gus

    Dave Gustafson
    Database Administrator
    Land O’Lakes, Inc.
    dbgustafson@landolakes.com

  4. Pingback: AWRs cell single block physical read | IT Remote

  5. Anonymous says:

    Hi ,

    Wait event occur cell single block physical read during the delete statement. How to resolve it.

  6. Patel says:

    I am running into the similar problem. On exadata, while doing insert via sql loader even if I do single row insert, the session takes forever to complete and also makes three indexes on the table unusable. How should I approach that? Thanks in advance.

    • Bobby says:

      My guess is that you are using interval partitioning and that the insert statement is creating a new partition. That could cause all global indexes to become unusable. Thanks for your comments.

  7. Patel says:

    I am running into a similar problem while I am inserting into a table via sql loader on exadata, evenif I insert a single row, the session is taking forever to comeback. There are three indexes on the table. How should I approach the situation? In the long ops, I see “cell single block physical read”. The table in question is very huge – 1.5 TB and has 1.5 billion rows.

    Thanks,

  8. Derya says:

    Thanks for this great share. I just want to add something here. Better not to drop the quarantine without looking into it.
    list quarantine detail
    It happened to me that, I had also seen the cell single block physical reads in one database and when I checked the details of one quarantine, it is for another database at all.

Leave a Reply to Patel Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.