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've been working as an Oracle database administrator and PeopleSoft administrator since 1994. I'm very interested in Oracle performance tuning.
This entry was posted in Uncategorized. Bookmark the permalink.

2 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.

Leave a Reply