8 Replies Latest reply: Oct 9, 2013 3:32 PM by Jonathan Lewis RSS

    Why cell single block physical read

    amin_adatia
      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
          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
            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

              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

                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

                  The quarantine thing was not an issue for us.

                  • 6. Re: Why cell single block physical read
                    amin_adatia

                    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

                      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

                        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