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 | % Event | P1 Value, P2 Value, P3 Value | % Activity | Parameter 1 | Parameter 2 | Parameter 3 |
|---|
| cell single block physical read | 37.45 | "1141149241","3623262296","8192" | 1.72 | cellhash# | diskhash# | bytes |
cell single block physical read | 37.44816586921850079744816586921850079745 | "1796605293","1614600483","8192" | 1.69 | cellhash# | diskhash# | bytes |
cell single block physical read | 37.44816586921850079744816586921850079745 | "1796605293","1070539091","8192" | 1.63 | cellhash# | diskhash# | bytes |
Top SQL with Top Events
| SQL ID | FullPlanhash | Planhash | Sampled # of Executions | % Activity | Event | % Event | Top Row Source | % RwSrc | SQL Text | Container Name |
|---|
| 0cb9s2ypsczyr | 2918804874 | 407744455 | 1 | 100.00 | CPU + Wait for CPU | 62.33 | LOAD TABLE CONVENTIONAL | 62.04 | INSERT /*+ NOAPPEND */ INTO "C... | ORAP08 |
0cb9s2ypsczyr | 2918804874 | 407744455 | 1 | 100 | cell single block physical read | 37.45 | LOAD TABLE CONVENTIONAL | 37.45 | | ORAP08 |
Back to Top SQL
Back to Top
Top SQL with Top Row Sources
| SQL ID | FullPlanHash | PlanHash | Sampled # of Executions | % Activity | Row Source | % RwSrc | Top Event | % Event | SQL Text | Container Name |
|---|
| 0cb9s2ypsczyr | 2918804874 | 407744455 | 1 | 100.00 | LOAD TABLE CONVENTIONAL | 99.71 | CPU + Wait for CPU | 62.04 | INSERT /*+ 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 | % Activity | Event | % Event | Object Name (Type) | Tablespace | Container Name |
|---|
| 568680 | 36.62 | cell single block physical read | 36.46 | CLARITY.MYC_PT_USER_ACCSS (TABLE) | EPIC_REPORTING | ORAP08 |
568680 | 36.61881977671451355661881977671451355662 | cell single block physical read | 36.46 | CLARITY.MYC_PT_USER_ACCSS (TABLE) | UNDOTBS2 | ORAP08 |
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.