Difference in temp table insert performance

  • From: Pap <oracle.developer35@xxxxxxxxx>
  • To: Oracle L <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 7 Jan 2022 22:45:04 +0530

Hello Listers, Its version 11.2.0.4 of Oracle. And is planned to move to
19C soon.

For one of the third party applications , we see direct path insert into
the global temporary temp table is taking significant time. Below is sql
monitor from two of the queries , both of them are loading data into global
temporary tables and in the first case it's inserting ~500million and in
second case it's inserting ~700million rows. But what we see is even the
first case global temporary table holds no indexes in it , it's still
showing ~84% of the activity in the data load step(plan_line_id - 1). And
the activity section in the sql monitor showing significant time samples
for 'direct path read temp' and 'direct path write temp'.

In the second case it's inserting ~747million rows but is not spending that
much time in the data load part i.e. plan_line_id-1 and also i am not
seeing those 'direct path read temp' and 'direct path write temp' samples
there. Even this global temporary has 3- indexes in it.

So we wanted to understand what must be causing this ? and if we could make
the data load into the global temporary table faster in the first case? In
the second case I understand it's the HASH join part where we are spending
a lot of time as it spills to temp and we may not have much option at hand
but we were expecting at least the data load should not take this amount of
time.


Global Stats
=========================================================================================================================
| Elapsed |   Cpu   |    IO    | Application | Concurrency | Cluster  |
Buffer | Read | Read  | Write | Write |  Cell   |
| Time(s) | Time(s) | Waits(s) |  Waits(s)   |  Waits(s)   | Waits(s) |
 Gets  | Reqs | Bytes | Reqs  | Bytes | Offload |
=========================================================================================================================
|    8310 |    5655 |     2655 |        0.02 |        0.00 |     0.01 |
209M |   2M | 922GB |  666K | 146GB |  17.36% |
=========================================================================================================================

SQL Plan Monitoring Details (Plan Hash Value=3240266454)
============================================================================================================================================================================================================================
| Id |            Operation             |         Name          |  Rows   |
Cost |   Time    | Start  | Execs |   Rows   | Read | Read  | Write | Write
|  Cell   |  Mem  | Activity |           Activity Detail           |
|    |                                  |                       | (Estim) |
     | Active(s) | Active |       | (Actual) | Reqs | Bytes | Reqs  | Bytes
| Offload | (Max) |   (%)    |             (# samples)             |
============================================================================================================================================================================================================================
|  0 | INSERT STATEMENT                 |                       |         |
     |      7764 |    +21 |     1 |        1 |      |       |       |
|         |       |     0.16 | Cpu (10)                            |
|    |                                  |                       |         |
     |           |        |       |          |      |       |       |
|         |       |          | enq: US - contention (1)            |
|    |                                  |                       |         |
     |           |        |       |          |      |       |       |
|         |       |          | reliable message (2)                |
|  1 |   LOAD AS SELECT                 |                       |         |
     |      8275 |    +21 |     1 |        1 | 336K |  66GB |  666K | 146GB
|         |  543K |    84.93 | Cpu (4325)                          |
|    |                                  |                       |         |
     |           |        |       |          |      |       |       |
|         |       |          | direct path read temp (1277)        |
|    |                                  |                       |         |
     |           |        |       |          |      |       |       |
|         |       |          | direct path write temp (1266)       |
|  2 |    FILTER                        |                       |         |
     |      5380 |    +21 |     1 |     545M |      |       |       |
|         |       |     0.01 | Cpu (1)                             |
|  3 |     HASH JOIN                    |                       |       1 |
 45M |      5398 |     +3 |     1 |     545M |      |       |       |
|         |   21M |     2.94 | Cpu (238)                           |
|  4 |      PARTITION RANGE ITERATOR    |                       |     761 |
 441 |         1 |     +3 |     1 |     270K |      |       |       |
|         |       |          |                                     |
|  5 |       TABLE ACCESS STORAGE FULL  | TSF                   |     761 |
 441 |         1 |     +3 |     2 |     270K |      |       |       |
|         |    7M |          |                                     |
|  6 |      HASH JOIN                   |                       |    3187 |
 45M |      5398 |     +3 |     1 |     557M |      |       |       |
|         |    2M |     4.07 | Cpu (329)                           |
|  7 |       TABLE ACCESS STORAGE FULL  | TTNI                  |      20 |
  19 |         1 |     +3 |     1 |     2430 |      |       |       |
|         |    1M |          |                                     |
|  8 |       PARTITION RANGE ITERATOR   |                       |    177M |
 45M |      5398 |     +3 |     1 |       4G |      |       |       |
|         |       |          |                                     |
|  9 |        TABLE ACCESS STORAGE FULL | TSFE                  |    177M |
 45M |      5400 |     +1 |    32 |       4G |   1M | 852GB |       |
|  38.65% |    7M |     7.89 | Cpu (565)                           |
|    |                                  |                       |         |
     |           |        |       |          |      |       |       |
|         |       |          | cell single block physical read (1) |
|    |                                  |                       |         |
     |           |        |       |          |      |       |       |
|         |       |          | cell smart table scan (72)          |
============================================================================================================================================================================================================================



**********************


Global Stats
=========================================================================================================================
| Elapsed |   Cpu   |    IO    | Application | Concurrency | Cluster  |
Buffer | Read | Read  | Write | Write |  Cell   |
| Time(s) | Time(s) | Waits(s) |  Waits(s)   |  Waits(s)   | Waits(s) |
 Gets  | Reqs | Bytes | Reqs  | Bytes | Offload |
=========================================================================================================================
|    7620 |    4071 |     3523 |        0.10 |          13 |       12 |
2G |   6M |   1TB |    2M | 274GB |   8.26% |
=========================================================================================================================

SQL Plan Monitoring Details (Plan Hash Value=2073160275)
================================================================================================================================================================================================================================================
| Id |              Operation              |              Name
 |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Read | Read  |
Write | Write |  Cell   |  Mem  | Temp  | Activity |           Activity
Detail           |
|    |                                     |
 | (Estim) |      | Active(s) | Active |       | (Actual) | Reqs | Bytes |
Reqs  | Bytes | Offload | (Max) | (Max) |   (%)    |             (#
samples)             |
================================================================================================================================================================================================================================================
|  0 | INSERT STATEMENT                    |
 |         |      |      6995 |   +625 |     1 |        1 |      |       |
      |       |         |       |       |     1.27 | gc buffer busy acquire
(3)          |
|    |                                     |
 |         |      |           |        |       |          |      |       |
      |       |         |       |       |          | gc current block busy
(9)           |
|    |                                     |
 |         |      |           |        |       |          |      |       |
      |       |         |       |       |          | buffer busy waits (11)
             |
|    |                                     |
 |         |      |           |        |       |          |      |       |
      |       |         |       |       |          | enq: SS - contention
(6)            |
|    |                                     |
 |         |      |           |        |       |          |      |       |
      |       |         |       |       |          | Cpu (5)
              |
|    |                                     |
 |         |      |           |        |       |          |      |       |
      |       |         |       |       |          | DFS lock handle (23)
             |
|    |                                     |
 |         |      |           |        |       |          |      |       |
      |       |         |       |       |          | cell single block
physical read (2) |
|    |                                     |
 |         |      |           |        |       |          |      |       |
      |       |         |       |       |          | local write wait (32)
              |
|    |                                     |
 |         |      |           |        |       |          |      |       |
      |       |         |       |       |          | read by other session
(3)           |
|  1 |   LOAD AS SELECT                    |
 |         |      |      6749 |   +871 |     1 |        1 |      |       |
 497K | 121GB |         |  543K |       |     9.24 | Cpu (678)
              |
|    |                                     |
 |         |      |           |        |       |          |      |       |
      |       |         |       |       |          | direct path write temp
(8)          |
|  2 |    FILTER                           |
 |         |      |      6749 |   +871 |     1 |     747M |      |       |
      |       |         |       |       |     0.07 | Cpu (5)
              |
|  3 |     HASH JOIN                       |
 |       1 |  22M |      7617 |     +3 |     1 |     747M |      |       |
      |       |         |    2M |       |     1.51 | Cpu (112)
              |
|  4 |      VIEW                           | index$_join$_006
|     110 |    2 |         1 |     +3 |     1 |      112 |      |       |
    |       |         |       |       |          |
            |
|  5 |       HASH JOIN                     |
 |         |      |         1 |     +3 |     1 |      112 |      |       |
      |       |         |    2M |       |          |
              |
|  6 |        INDEX STORAGE FAST FULL SCAN | MMC_IX1
 |     110 |    1 |         1 |     +3 |     1 |      112 |      |       |
      |       |         |       |       |          |
              |
|  7 |        INDEX STORAGE FAST FULL SCAN | MMC_IX2
 |     110 |    1 |         1 |     +3 |     1 |      112 |      |       |
      |       |         |       |       |          |
              |
|  8 |      NESTED LOOPS                   |
 |       1 |  22M |      6749 |   +871 |     1 |     747M |      |       |
      |       |         |       |       |     0.39 | Cpu (29)
             |
|  9 |       NESTED LOOPS                  |
 |       1 |  22M |      6749 |   +871 |     1 |     747M |      |       |
      |       |         |       |       |     0.55 | Cpu (41)
             |
| 10 |        HASH JOIN                    |
 |       1 |  22M |      7620 |     +0 |     1 |     747M |   5M | 623GB |
   1M | 153GB |         |  675M |  175G |    69.88 | Cpu (1845)
             |
|    |                                     |
 |         |      |           |        |       |          |      |       |
      |       |         |       |       |          | direct path read temp
(3275)        |
|    |                                     |
 |         |      |           |        |       |          |      |       |
      |       |         |       |       |          | direct path write temp
(71)         |
| 11 |         HASH JOIN                   |
 |       1 |  22M |       503 |     +1 |     1 |     540M |      |       |
      |       |         |    2M |       |     2.41 | Cpu (179)
              |
| 12 |          TABLE ACCESS STORAGE FULL  | TTFXI
 |      20 |   19 |         1 |     +3 |     1 |     6199 |      |       |
      |       |         |    1M |       |          |
              |
| 13 |          PARTITION RANGE ITERATOR   |
 |     93M |  22M |       501 |     +3 |     1 |       4G |      |       |
      |       |         |       |       |          |
              |
| 14 |           TABLE ACCESS STORAGE FULL | TSFEF
 |     93M |  22M |       501 |     +3 |    32 |       4G | 582K | 458GB |
      |       |  75.25% |    7M |       |     1.08 | Cpu (77)
             |
|    |                                     |
 |         |      |           |        |       |          |      |       |
      |       |         |       |       |          | cell smart table scan
(3)           |
| 15 |         TABLE ACCESS STORAGE FULL   | TTF
 |       1 |   19 |       367 |   +503 |     1 |     747M | 238K | 136GB |
      |       |  25.37% |    7M |       |     0.90 | Cpu (60)
             |
|    |                                     |
 |         |      |           |        |       |          |      |       |
      |       |         |       |       |          | cell smart table scan
(7)           |
| 16 |        INDEX UNIQUE SCAN            | TMFI_PK
 |       1 |    1 |      6749 |   +871 |  747M |     747M |  110 | 880KB |
      |       |         |       |       |     8.70 | Cpu (646)
              |
| 17 |       TABLE ACCESS BY INDEX ROWID   | TMFI
|       1 |    2 |      6749 |   +871 |  747M |     747M |   16 | 128KB |
    |       |         |       |       |     4.01 | Cpu (298)
            |
================================================================================================================================================================================================================================================

Other related posts: