This discussion is archived
8 Replies Latest reply: Oct 9, 2013 1:32 PM by Jonathan Lewis RSS

Why cell single block physical read

amin_adatia Newbie
Currently Being Moderated
Yesterday and for part of today, I was able to insert into a table using hint /*+ APPEND NOLOGGING IGNORE_ROW_ON_DUPKEY() */ and I was getting a rate of about a million rows in 700K rows/minute. Today I get a wait event "cell single block physical read" and the job has gone to LaLa land.

I removed the IGNORE_ROW_ON_DUPKEY hint and replaced with SELECT DISTINCT. No improvement.

Exadata V2 => 11.2.0.3

What should I be looking for?
  • 1. Re: Why cell single block physical read
    792819 Newbie
    Currently Being Moderated
    Looks like you're doing insert into table as select col1,col2.....from tab, here the select statement is doing index scan to pull the required data and that's the reason you're seeing wait event "single block physical read". If this is the case I would suggest you add full hint in you're select statement and see how the inserts goes..
  • 2. Re: Why cell single block physical read
    amin_adatia Newbie
    Currently Being Moderated
    Whatever the select is doing now is not what was happening for the other 30 times. So why the sudden change in the way the selct would work? Similar number of records in the source table ( range from 500K to 1200K). The select is
    select /*+ parallel (a,16) no_index(a,i1) no_index(a,i2) */
    x,y,z,(select x from t2 where a.x = t2.x and a.y = t2.y and a.z = t2.z), 'Y'
    from a
    where a.x = :b
    order by x,y,z
    /
    Edited by: amin_adatia on Apr 9, 2013 7:55 PM
  • 3. Re: Why cell single block physical read
    b7a09810-27d0-4003-b67b-c4a1a24a3c25 Newbie
    Currently Being Moderated

    It could be a possible issue with the cell server and crashes that have made it stop using the smart scan feature. Take a look at this article:

    Possible quarantine issue on a cell server…

    http://www.bobbydurrettdba.com/2012/03/09/cell-single-block-physical-read/

     

    cellcli -e list quarantine

  • 4. Re: Why cell single block physical read
    robinsc Explorer
    Currently Being Moderated

    It could also be that sufficient quantity of table a is in the buffer cache that the optimizer is not choosing a full table scan.In such a case there might be some other index on a which is suboptimal but is chosen as you have prevented all the optimal indexes to be used. Try to get the runtime plan from the cursor cache to see what plan the optimizer has chosen.

  • 5. Re: Why cell single block physical read
    amin_adatia Newbie
    Currently Being Moderated

    The quarantine thing was not an issue for us.

  • 6. Re: Why cell single block physical read
    amin_adatia Newbie
    Currently Being Moderated

    In some cases we found that adding hint NO_INDEX helped but not when we had Text Indexes which we had to then allow with INDEX hint. In other cases we had to gather statistics for each partition as the data got loaded before doing any "query" (actually whatever Oracle Text does when building an Index) on that partition. We were finally able to get down to 10-15 minutes to Text Index a partition with about 1.5 million rows. This met our data migration data load assembly-line schedule!

  • 7. Re: Why cell single block physical read
    Edward Whalen Oracle ACE
    Currently Being Moderated

    Could you show the execution plan for this operation?  Also, are you using HCC on any of the tables involved in this operation?  You could be running into a fragmentation issue do to the APPEND hint.

  • 8. Re: Why cell single block physical read
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated

    amin_adatia wrote:

     

    Whatever the select is doing now is not what was happening for the other 30 times. So why the sudden change in the way the selct would work? Similar number of records in the source table ( range from 500K to 1200K). The select is
    select /*+ parallel (a,16) no_index(a,i1) no_index(a,i2) */ x,y,z,(select x from t2 where a.x = t2.x and a.y = t2.y and a.z = t2.z), 'Y' from a where a.x = :b order by x,y,z /
    Edited by: amin_adatia on Apr 9, 2013 7:55 PM

     

    Since you've got an inline scalar subquery which isn't an aggregate and looks as if it's high precision and ought to run with an indexed access path, a simple explanation would be that yesterday the data needed by that part of the query was well-cached, but today it isn't.

     

    Do you have any AWR content that could show you the execution paths from yesterday and today ?

     

    Regards

    Jonathan Lewis

Legend

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