12 Replies Latest reply on Aug 21, 2018 3:40 PM by Lothar Flatz

    Exadata INSERT and massive "cell single block physical reads" on UNDO tablespace?

    Bob Bryla

      For simple INSERTs on table with no contention or locks from other sessions -- first, a general question -- the INSERT will of course generate UNDO, but while the INSERT is running, why would it need to read UNDO?

       

      In several different Exadata environments that I work with -- same code base -- on a random basis for a small subset of tables, the "cell single block physical read" against UNDO accounts for I/O that's 10x or 100x the size of the table+index itself. When INSERT is killed, rolled back, and re-run, it runs in a fraction of the time (45 minutes instead of 10+ hours). The ASH report doesn't show the writes, they are really a very small percentage of the overall I/O:

       

      Top Event P1/P2/P3 Values

      Event% EventP1 Value, P2 Value, P3 Value% ActivityParameter 1Parameter 2Parameter 3
      cell single block physical read37.45"1141149241","3623262296","8192"1.72cellhash#diskhash#bytes
      "1796605293","1614600483","8192"1.69
      "1796605293","1070539091","8192"1.63

      Top SQL with Top Events

      SQL IDFullPlanhashPlanhashSampled # of Executions% ActivityEvent% EventTop Row Source% RwSrcSQL TextContainer Name
      0cb9s2ypsczyr29188048744077444551100.00CPU + Wait for CPU62.33LOAD TABLE CONVENTIONAL62.04INSERT /*+ NOAPPEND */ INTO "C...ORAP08
      cell single block physical read37.45LOAD TABLE CONVENTIONAL37.45

      Back to Top SQL
      Back to Top

      Top SQL with Top Row Sources

      SQL IDFullPlanHashPlanHashSampled # of Executions% ActivityRow Source% RwSrcTop Event% EventSQL TextContainer Name
      0cb9s2ypsczyr29188048744077444551100.00LOAD TABLE CONVENTIONAL99.71CPU + Wait for CPU62.04INSERT /*+ NOAPPEND */ INTO "C...ORAP08

       

      Top DB Objects

      • With respect to Application, Cluster, User I/O, buffer busy waits and In-Memory DB events only.
      • Tablespace name is not available for reports generated from the root PDB of a consolidated database.
      Object ID% ActivityEvent% EventObject Name (Type)TablespaceContainer Name
      56868036.62cell single block physical read36.46CLARITY.MYC_PT_USER_ACCSS (TABLE)EPIC_REPORTINGORAP08
      cell single block physical read36.46UNDOTBS2ORAP08

       

       

      I can't find a "bad" SQL Monitor report in AWR; only the short ones are being monitored.

       

      What else can I do to monitor what is going on with all the UNDO activity? Again, there is no other session with any transactions open on the table, much less any share locks or any activity at all.