Skip to Main Content
  • Questions

  • Why the query read bytes so big in SQL monitoring report

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Ken.

Asked: July 21, 2020 - 5:42 pm UTC

Last updated: July 22, 2024 - 1:13 pm UTC

Version: 12.1 C

Viewed 10K+ times! This question is

You Asked

Hi Gurus,
Below query run very long, after checking with SQL Plan Monitoring Details, i found oracle read one table with huge "read bytes". the table has 688k records, but the read bytes is 298TB. could you please take a look this. what causes the issue? 

WITH PRD
     AS (SELECT  PRDDT,PRDE_PRDDTFROM B_CTL)
SELECT *
  FROM INS_DEL   A,INS_BAL  B,
       (SELECT *FROM INS_DEL WHERE     SRC_CD = 'ABC' AND EFF_DT = (SELECT PRDE_PRDDT FROM PRD)) C,
       INS  D,
       (SELECT * FROM INS_PT WHERE     SRC_CD = 'ABC' AND EFF_DT = (SELECT PRDDT FROM PRD)) E
 WHERE     A.SRC_CD = 'ABC'
       AND A.EFF_DT = (SELECT PRDDT FROM PRD)
       AND D.SRC_SYS_CD = 'ABC'
       AND D.INS_KEY = A.INS_KEY
       AND C.SRC_CD(+) = 'ABC'
       AND A.INS_KEY = C.INS_KEY(+)
       AND (   A.LOAN_CD NOT IN ('1002')
            OR D.ACCT_NMBR IS NOT NULL)
       AND E.INS_DEL_KEY(+) = A.INS_DEL_KEY
       AND (    (    (   A.INS_OPEN_DATE <= (SELECT PRDDT FROM PRD) OR A.T_S_DT <= (SELECT PRDDT FROM PRD)) AND A.INS_S_CD IN ('O', 'OP')) AND NOT EXISTS (SELECT 1 FROM INS_DEL D WHERE     D.SRC_CD = 'ABC' AND D.EFF_DT = (SELECT PRDE_PRDDT FROM PRD) AND D.INS_KEY = A.INS_KEY AND D.O_B_AMT >= A.O_B_AMT  AND B.SRC_CD = 'ABC' AND B.EFF_DT = (SELECT PRDDT FROM PRD)  AND A.INS_DEL_KEY = B.INS_DEL_KEY AND A.T_M_DT > A.EFF_DT  AND EXISTS (SELECT 1 FROM SRC_CUST, SRC_CUST_PT  WHERE     SRC_CUST.INS_DEL_KEY = A.INS_DEL_KEY AND SRC_CUST.SRC_CD = 'ABC'AND SRC_CUST_PT.SRC_CD = 'ABC'AND SRC_CUST_PT.EFF_DT IN (SELECT PRDDT FROM PRD)  AND SRC_CUST.SRC_SYS_CUST_KEY =SRC_CUST_PT.SRC_SYS_CUST_KEY )                    
Global Stats
=========================================================================================================
| Elapsed |   Cpu   |    IO    | Application | Concurrency | Cluster  | Buffer | Read | Read  |  Cell   |
| Time(s) | Time(s) | Waits(s) |  Waits(s)   |  Waits(s)   | Waits(s) |  Gets  | Reqs | Bytes | Offload |
=========================================================================================================
|  105316 |   86788 |    18063 |         463 |        0.21 |     1.29 |    17G | 388M | 298TB |  74.16% |
=========================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=408419089)
=====================================================================================================================================================================================================================================================================
| Id    |                     Operation                     |            Name             |  Rows   | Cost  |   Time    | Start  | Execs |   Rows   | Read | Read  | Write | Write |  Cell   | Mem  | Activity |             Activity Detail             | PRDogress |
|       |                                                   |                             | (Estim) |       | Active(s) | Active |       | (Actual) | Reqs | Bytes | Reqs  | Bytes | Offload |      |   (%)    |               (# samples)               |          |
=====================================================================================================================================================================================================================================================================
|     0 | SELECT STATEMENT                                  |                             |         |       |           |        |     1 |          |      |       |       |       |         |      |          |                                         |          |
|     1 |   TEMP TABLE TRANSFORMATION                       |                             |         |       |           |        |     1 |          |      |       |       |       |         |      |          |                                         |          |
|     2 |    LOAD AS SELECT                                 |                             |         |       |         1 |     +4 |     1 |        2 |      |       |     1 | 32768 |         |      |          |                                         |          |
|     3 |     SORT AGGREGATE                                |                             |       1 |       |         1 |     +4 |     1 |        1 |      |       |       |       |         |      |          |                                         |          |
|     4 |      TABLE ACCESS STORAGE FULL                    | B_CTL               |     992 |     9 |         1 |     +4 |     1 |      992 |      |       |       |       |         |      |          |                                         |          |
|     5 |    FILTER                                         |                             |         |       |           |        |     1 |          |      |       |       |       |         |      |          |                                         |          |
|     6 |     HASH JOIN OUTER                               |                             |       1 | 47422 |           |        |     1 |          |      |       |       |       |         | 232K |          |                                         |          |
|     7 |      NESTED LOOPS OUTER                           |                             |       1 | 46013 |           |        |     1 |          |      |       |       |       |         |      |          |                                         |          |
|     8 |       NESTED LOOPS                                |                             |       1 | 46009 |           |        |     1 |          |      |       |       |       |         |      |          |                                         |          |
|     9 |        HASH JOIN                                  |                             |       1 | 46006 |           |        |     1 |          |      |       |       |       |         |      |          |                                         |          |
|    10 |         NESTED LOOPS                              |                             |       1 | 46006 |           |        |     1 |          |      |       |       |       |         |      |          |                                         |          |
|    11 |          STATISTICS COLLECTOR                     |                             |         |       |           |        |     1 |          |      |       |       |       |         |      |          |                                         |          |
|    12 |           NESTED LOOPS                            |                             |       1 | 46004 |           |        |     1 |          |      |       |       |       |         |      |          |                                         |          |
|    13 |            VIEW                                   | VW_SQ_1                     |       1 | 46002 |           |        |     1 |          |      |       |       |       |         |      |          |                                         |          |
| -> 14 |             HASH UNIQUE                           |                             |       1 | 46002 |    103861 |     +4 |     1 |        0 |      |       |       |       |         |  21M |     0.00 | Cpu (1)                                 |          |
| -> 15 |              NESTED LOOPS SEMI                    |                             |       1 | 46001 |    103861 |     +4 |     1 |     688K |      |       |       |       |         |      |     0.00 | Cpu (1)                                 |          |
| -> 16 |               NESTED LOOPS                        |                             |      39 |  1646 |    103861 |     +4 |     1 |       1M |      |       |       |       |         |      |     0.00 | Cpu (1)                                 |          |
|    17 |                MERGE JOIN CARTESIAN               |                             |       1 |     4 |         1 |     +4 |     1 |        1 |      |       |       |       |         |      |          |                                         |          |
|    18 |                 VIEW                              |                             |       1 |     2 |         1 |     +4 |     1 |        1 |      |       |       |       |         |      |          |                                         |          |
| -> 19 |                  TABLE ACCESS STORAGE FULL        | SYS_TEMP_0FDA01601_A41017EE |       1 |     2 |    103861 |     +4 |     1 |        1 |    1 | 32768 |       |       |         |      |          |                                         |          |
|    20 |                 BUFFER SORT                       |                             |       1 |     4 |         1 |     +4 |     1 |        1 |      |       |       |       |         |      |          |                                         |          |
|    21 |                  VIEW                             |                             |       1 |     2 |         1 |     +4 |     1 |        1 |      |       |       |       |         |      |          |                                         |          |
|    22 |                   TABLE ACCESS STORAGE FULL       | SYS_TEMP_0FDA01601_A41017EE |       1 |     2 |         1 |     +4 |     1 |        1 |      |       |       |       |         |      |          |                                         |          |
| -> 23 |                PARTITION RANGE ITERATOR           |                             |      39 |  1642 |    103861 |     +4 |     1 |       1M |      |       |       |       |         |      |          |                                         |          |
| -> 24 |                 TABLE ACCESS STORAGE FULL         | SRC_CUST                    |      39 |  1642 |    103861 |     +4 |     1 |       1M |  413 | 386MB |       |       |  87.52% |   7M |     0.00 | Cpu (3)                                 |          |
| -> 25 |               PARTITION RANGE ITERATOR            |                             |    777K |  1137 |    103861 |     +4 |    1M |     688K |      |       |       |       |         |      |     0.01 | Cpu (10)                                |          |
| -> 26 |                TABLE ACCESS STORAGE FULL          | SRC_CUST_PT                 |    777K |  1137 |    103864 |     +1 |    1M |     688K | 388M | 298TB |       |       |  74.16% |   7M |    99.98 | enq: KO - fast object checkpoint (432)  |     100% |
|       |                                                   |                             |         |       |           |        |       |          |      |       |       |       |         |      |          | gc cr disk read (1)                     |          |
|       |                                                   |                             |         |       |           |        |       |          |      |       |       |       |         |      |          | Cpu (84390)                             |          |
|       |                                                   |                             |         |       |           |        |       |          |      |       |       |       |         |      |          | gcs drm freeze in enter server mode (3) |          |
|       |                                                   |                             |         |       |           |        |       |          |      |       |       |       |         |      |          | reliable message (1185)                 |          |
|       |                                                   |                             |         |       |           |        |       |          |      |       |       |       |         |      |          | cell single block physical read (101)   |          |
|       |                                                   |                             |         |       |           |        |       |          |      |       |       |       |         |      |          | cell smart table scan (17590)           |          |
|    27 |            PARTITION RANGE SINGLE                 |                             |       1 |     2 |           |        |       |          |      |       |       |       |         |      |          |                                         |          |
|    28 |             TABLE ACCESS BY LOCAL INDEX ROWID     | INS_DEL                     |       1 |     2 |           |        |       |          |      |       |       |       |         |      |          |                                         |          |
|    29 |              INDEX UNIQUE SCAN                    | XPK_INS_DEL                 |       1 |     1 |           |        |       |          |      |       |       |       |         |      |          |                                         |          |
|    30 |               VIEW                                |                             |       1 |     2 |           |        |       |          |      |       |       |       |         |      |          |                                         |          |
|    31 |                TABLE ACCESS STORAGE FULL          | SYS_TEMP_0FDA01601_A41017EE |       1 |     2 |           |        |       |          |      |       |       |       |         |      |          |                                         |          |
|    32 |              VIEW                                 |                             |       1 |     2 |           |        |       |          |      |       |       |       |         |      |          |                                         |          |
|    33 |               TABLE ACCESS STORAGE FULL           | SYS_TEMP_0FDA01601_A41017EE |       1 |     2 |           |        |       |          |      |       |       |       |         |      |          |                                         |          |
|    34 |              VIEW                                 |                             |       1 |     2 |           |        |       |          |      |       |       |       |         |      |          |                                         |          |
|    35 |               TABLE ACCESS STORAGE FULL           | SYS_TEMP_0FDA01601_A41017EE |       1 |     2 |           |        |       |          |      |       |       |       |         |      |          |                                         |          |
|    36 |          PARTITION LIST SINGLE                    |                             |       1 |     2 |           |        |       |          |      |       |       |       |         |      |          |                                         |          |
|    37 |           TABLE ACCESS BY LOCAL INDEX ROWID       | INS                         |       1 |     2 |           |        |       |          |      |       |       |       |         |      |          |                                         |          |
|    38 |            INDEX UNIQUE SCAN                      | XPK_INS                     |       1 |     1 |           |        |       |          |      |       |       |       |         |      |          |                                         |          |
|    39 |         PARTITION LIST SINGLE                     |                             |       1 |     2 |           |        |       |          |      |       |       |       |         |      |          |                                         |          |
|    40 |          TABLE ACCESS STORAGE FULL                | INS                         |       1 |     2 |           |        |       |          |      |       |       |       |         |      |          |                                         |          |
|    41 |        PARTITION RANGE SINGLE                     |                             |       1 |     3 |           |        |       |          |      |       |       |       |         |      |          |                                         |          |
|    42 |         TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | INS_BAL                     |       1 |     3 |           |        |       |          |      |       |       |       |         |      |          |                                         |          |
|    43 |          INDEX RANGE SCAN                         | XIE_INS_BAL_1               |       1 |     2 |           |        |       |          |      |       |       |       |         |      |          |                                         |          |
|    44 |           VIEW                                    |                             |       1 |     2 |           |        |       |          |      |       |       |       |         |      |          |                                         |          |
|    45 |            TABLE ACCESS STORAGE FULL              | SYS_TEMP_0FDA01601_A41017EE |       1 |     2 |           |        |       |          |      |       |       |       |         |      |          |                                         |          |
|    46 |       PARTITION RANGE SINGLE                      |                             |       1 |     4 |           |        |       |          |      |       |       |       |         |      |          |                                         |          |
|    47 |        VIEW PUSHED PRDEDICATE                     |                             |       1 |     4 |           |        |       |          |      |       |       |       |         |      |          |                                         |          |
|    48 |         TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | INS_DEL                     |       1 |     4 |           |        |       |          |      |       |       |       |         |      |          |                                         |          |
|    49 |          INDEX RANGE SCAN                         | XFK_INS_DEL_1               |       1 |     3 |           |        |       |          |      |       |       |       |         |      |          |                                         |          |
|    50 |           VIEW                                    |                             |       1 |     2 |           |        |       |          |      |       |       |       |         |      |          |                                         |          |
|    51 |            TABLE ACCESS STORAGE FULL              | SYS_TEMP_0FDA01601_A41017EE |       1 |     2 |           |        |       |          |      |       |       |       |         |      |          |                                         |          |
|    52 |      PARTITION RANGE SINGLE                       |                             |    725K |  1406 |           |        |       |          |      |       |       |       |         |      |          |                                         |          |
|    53 |       VIEW                                        |                             |    725K |  1406 |           |        |       |          |      |       |       |       |         |      |          |                                         |          |
|    54 |        TABLE ACCESS STORAGE FULL                  | INS_PT                      |    725K |  1404 |           |        |       |          |      |       |       |       |         |      |          |                                         |          |
|    55 |         VIEW                                      |                             |       1 |     2 |           |        
|    56 |          TABLE ACCESS STORAGE FULL                | SYS_TEMP_0FDA01601_A41017EE |       1 |     2 |           |                
|    57 |     PARTITION RANGE SINGLE                        |                             |       1 |     4 |           |          
|    58 |      TABLE ACCESS BY LOCAL INDEX ROWID BATCHED    | INS_DEL                     |       1 |     4 |

and Connor said...

Let's focus on a couple of elements

====================================================================================================================================================================
| Id    |                     Operation                     |            Name             |  Rows   | Cost  |   Time    | Start  | Execs |   Rows   | Read | Read  |
|       |                                                   |                             | (Estim) |       | Active(s) | Active |       | (Actual) | Reqs | Bytes |
====================================================================================================================================================================


| -> 15 |              NESTED LOOPS SEMI                    |                             |       1 | 46001 |    103861 |     +4 |     1 |     688K |      |       |
| -> 16 |               NESTED LOOPS                        |                             |      39 |  1646 |    103861 |     +4 |     1 |       1M |      |       |

| -> 25 |               PARTITION RANGE ITERATOR            |                             |    777K |  1137 |    103861 |     +4 |    1M |     688K |      |       |
| -> 26 |                TABLE ACCESS STORAGE FULL          | SRC_CUST_PT                 |    777K |  1137 |    103864 |     +1 |    1M |     688K | 388M | 298TB |
|       |                                                   |                             |         |       |           |        |       |          |      |       |


Line 16 - we *thought* we would get 39 rows, we actually got 1million rows back!

So we were planning on doing just 39 passes through various partitions of SRC_CUST_PT, but in reality we are doing 1million (plus). This is why you've read 300TB of data - you're read partition(s) of SRC_CUST_PT millions of times. You might to look at why the stats are so far off, but a quick fix look at potentially hinting (and then using SPM) to convert the join between SRC_CUST and SRC_CUST_PT to a hash join rather than a nested loop.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, July 23, 2020 - 1:51 pm UTC

thanks Connor,
below is similar query, which actual row is 18, but read requ's is 48M, read byte is 32TB. what's read requ mean at here?

Global Stats
=========================================================================================================
| Elapsed |   Cpu   |    IO    | Application | Concurrency | Cluster  | Buffer | Read | Read  |  Cell   |
| Time(s) | Time(s) | Waits(s) |  Waits(s)   |  Waits(s)   | Waits(s) |  Gets  | Reqs | Bytes | Offload |
=========================================================================================================
|   27209 |   11167 |    15307 |          29 |        0.00 |      706 |     1G |  48M |  28TB |  71.51% |
=========================================================================================================

SQL Plan Monitoring Details (Plan Hash Value=4181821043)
================================================================================================================================================================================================================================================================
| Id    |                  Operation                  |            Name             |  Rows   | Cost  |   Time    | Start  | Execs |   Rows   | Read  | Read  | Write | Write |  Cell   | Mem  | Activity |             Activity Detail             | Progress |
|       |                                             |                             | (Estim) |       | Active(s) | Active |       | (Actual) | Reqs  | Bytes | Reqs  | Bytes | Offload |      |   (%)    |               (# samples)               |          |
================================================================================================================================================================================================================================================================
|     0 | SELECT STATEMENT                            |                             |         |       |           |        |     1 |          |       |       |       |       |         |      |          |                                         |          |
|     1 |   TEMP TABLE TRANSFORMATION                 |                             |         |       |           |        |     1 |          |       |       |       |       |         |      |          |                                         |          |
|     2 |    LOAD AS SELECT                           |                             |         |       |         1 |     +3 |     1 |        2 |       |       |     1 | 32768 |         |      |          |                                         |          |
|     3 |     SORT AGGREGATE                          |                             |       1 |       |         1 |     +3 |     1 |        1 |       |       |       |       |         |      |          |                                         |          |
|     4 |      TABLE ACCESS STORAGE FULL              | B_CTL                       |    1106 |     9 |         1 |     +3 |     1 |      663 |       |       |       |       |         |      |          |                                         |          |
|     5 |    SORT ORDER BY                            |                             |       2 | 11294 |     21441 |     +3 |     1 |        0 |       |       |       |       |         | 886K |          |                                         |          |
|     6 |     UNION-ALL                               |                             |         |       |     21441 |     +3 |     1 |     5237 |       |       |       |       |         |      |          |                                         |          |
|     7 |      FILTER                                 |                             |         |       |     21441 |     +3 |     1 |     5237 |       |       |       |       |         |      |          |                                         |          |
|     8 |       NESTED LOOPS                          |                             |       1 |  3923 |     21441 |     +3 |     1 |     5254 |       |       |       |       |         |      |          |                                         |          |
|     9 |        NESTED LOOPS                         |                             |       1 |  3923 |     21441 |     +3 |     1 |    11634 |       |       |       |       |         |      |          |                                         |          |
|    10 |         MERGE JOIN CARTESIAN                |                             |       1 |  3921 |     21441 |     +3 |     1 |    11634 |       |       |       |       |         |      |          |                                         |          |
|    11 |          PARTITION RANGE SINGLE             |                             |       1 |  3919 |     21441 |     +3 |     1 |    11635 |       |       |       |       |         |      |          |                                         |          |
| -> 12 |           TABLE ACCESS STORAGE FULL         | INS_BAL                     |       1 |  3919 |     26224 |     +3 |     1 |    11635 | 10713 |  10GB |       |       |  99.98% |   7M |          |                                         |          |
|    13 |            VIEW                             |                             |       1 |     2 |         1 |     +3 |     1 |        1 |       |       |       |       |         |      |          |                                         |          |
|    14 |             TABLE ACCESS STORAGE FULL       | SYS_TEMP_0FD9D66A4_5019F60F |       1 |     2 |         1 |     +3 |     1 |        1 |     1 | 32768 |       |       |         |      |          |                                         |          |
|    15 |          BUFFER SORT                        |                             |       1 |     2 |     21441 |     +3 | 11635 |    11634 |       |       |       |       |         |      |          |                                         |          |
|    16 |           VIEW                              |                             |       1 |     2 |         1 |     +3 |     1 |        1 |       |       |       |       |         |      |          |                                         |          |
|    17 |            TABLE ACCESS STORAGE FULL        | SYS_TEMP_0FD9D66A4_5019F60F |       1 |     2 |         1 |     +3 |     1 |        1 |       |       |       |       |         |      |          |                                         |          |
|    18 |         PARTITION RANGE SINGLE              |                             |       1 |     1 |     21441 |     +3 | 16516 |    11634 |       |       |       |       |         |      |          |                                         |          |
|    19 |          INDEX UNIQUE SCAN                  | PK_INS_D                    |       1 |     1 |     26224 |     +3 | 16516 |    11634 |  4670 | 146MB |       |       |         |      |     0.01 | gc cr multi block request (1)           |          |
|       |                                             |                             |         |       |           |        |       |          |       |       |       |       |         |      |          | cell list of blocks physical read (1)   |          |
|    20 |           VIEW                              |                             |       1 |     2 |         1 |     +3 |     1 |        1 |       |       |       |       |         |      |          |                                         |          |
|    21 |            TABLE ACCESS STORAGE FULL        | SYS_TEMP_0FD9D66A4_5019F60F |       1 |     2 |         1 |     +3 |     1 |        1 |       |       |       |       |         |      |          |                                         |          |
|    22 |        TABLE ACCESS BY LOCAL INDEX ROWID    | INS_D                       |       1 |     2 |     26224 |     +3 | 19328 |     5254 |  7521 | 235MB |       |       |         |      |     0.21 | gc current grant busy (2)               |          |
|       |                                             |                             |         |       |           |        |       |          |       |       |       |       |         |      |          | log file switch completion (5)          |          |
|       |                                             |                             |         |       |           |        |       |          |       |       |       |       |         |      |          | cell single block physical read (45)    |          |
|    23 |         VIEW                                |                             |       1 |     2 |         1 |     +3 |     1 |        1 |       |       |       |       |         |      |          |                                         |          |
|    24 |          TABLE ACCESS STORAGE FULL          | SYS_TEMP_0FD9D66A4_5019F60F |       1 |     2 |         1 |     +3 |     1 |        1 |       |       |       |       |         |      |          |                                         |          |
|    25 |       PARTITION RANGE SINGLE                |                             |       1 |  1718 |     18056 |   +119 |  5255 |       17 |       |       |       |       |         |      |          |                                         |          |
| -> 26 |        TABLE ACCESS STORAGE FULL FIRST ROWS | SRC_CUST                    |       1 |  1718 |     26227 |     +3 |  5255 |       17 |   48M |  28TB |       |       |  71.51% |   7M |    99.79 | enq: KO - fast object checkpoint (22)   |     100% |
|       |                                             |                             |         |       |           |        |       |          |       |       |       |       |         |      |          | gc cr block 2-way (17)                  |          |
|       |                                             |                             |         |       |           |        |       |          |       |       |       |       |         |      |          | gc cr disk read (672)                   |          |
|       |                                             |                             |         |       |           |        |       |          |       |       |       |       |         |      |          | gc cr request (8)                       |          |
|       |                                             |                             |         |       |           |        |       |          |       |       |       |       |         |      |          | Cpu (9233)                              |          |
|       |                                             |                             |         |       |           |        |       |          |       |       |       |       |         |      |          | gcs drm freeze in enter server mode (1) |          |
|       |                                             |                             |         |       |           |        |       |          |       |       |       |       |         |      |          | reliable message (18)                   |          |
|       |                                             |                             |         |       |           |        |       |          |       |       |       |       |         |      |          | resmgr:cpu quantum (30)                 |          |
|       |                                             |                             |         |       |           |        |       |          |       |       |       |       |         |      |          | cell single block physical read (14130) |          |
|       |                                             |                             |         |       |           |        |       |          |       |       |       |       |         |      |          | cell single block read request (16)     |          |
|       |                                             |                             |         |       |           |        |       |          |       |       |       |       |         |      |          | cell smart table scan (990)             |          |
|       |                                             |                             |         |       |           |        |       |          |       |       |       |       |         |      |          | read by other session (1)               |          |

Connor McDonald
July 24, 2020 - 3:40 am UTC

"Read Requests" 48 million of them.

Activity in SQL Monitoring Report vs ASH

Narendra, July 15, 2024 - 10:25 am UTC

Hello Chris/Connor,

Hope you are doing well.
I am getting somewhat confused about data displayed in SQL Monitoring (Active) report as compared to ASH data for individual SQL executions. I am trying to investigate occasional slow performance of a SQL in a 19c database and started by looking at active SQL Monitoring reports for "Good" and "Bad" executions. Both reports suggested that most of the time is being spent on a specific step in execution plan with 70% value in Activity column.
However, when I queries ASH data for the same SQL execution, it is reporting most data captured for a completely different step in the execution plan.
How is this discrepancy possible? What am I doing wrong?

Thanks in advance,
Chris Saxon
July 22, 2024 - 1:13 pm UTC

¯\_(ツ)_/¯

There are many ways to get ASH and SQL monitoring reports; so if you are doing something wrong we've got very little to work on here...
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.