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
This wait event also could be due to chained rows in the table
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.
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
Glad that it was helpful to you.
Pingback: AWRs cell single block physical read | IT Remote
Hi ,
Wait event occur cell single block physical read during the delete statement. How to resolve it.
I’m not sure about this one. Does the table have indexes? Thanks for your comment.
yes it has indexes in it and doing delete operations
I am not sure how the smart scans work with deletes. I would have to test it to know.
Thank you for your comment.
Bobby
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.
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.
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,
I’m assuming this is the same as the previous comment. Thanks!
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.
Thanks for your comment. According to WordPress this is one of my most viewed posts and I am not sure why.
Bobby