Skip to Main Content
  • Questions

  • Why is plan from xplan different from sql monitor report

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Ken.

Asked: January 30, 2024 - 1:33 am UTC

Last updated: February 06, 2024 - 10:50 am UTC

Version: 12C

Viewed 1000+ times

You Asked

Hi Gurus,



I have below query run in oracle 12c. Not sure why the plan I got from DBMS_XPLAN.DISPLAY_CURSOR different from DBMS_SQLTUNE.report_sql_monitor. below is detail. as you can see for some reason in xplan: INS_PT table was read and joined once, but in monitor report: this table was read and joined twice.

please share your thoughts.



thanks in advance



Plan from sql monitor report:

SQL Monitoring Report

SQL Plan Monitoring Details (Plan Hash Value=2657002414)

===================================================================================================================================================================================================================================================
| 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 |     |   (%)    |            (# 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                       |       1 |   13 |         1 |     +4 |     1 |        1 |       |       |       |       |         |     |          |                                   |
|     9 |    LOAD AS SELECT                                 |                             |         |      |         1 |     +4 |     1 |        2 |       |       |    15 |  15MB |         |     |          |                                   |
|    10 |     HASH UNIQUE                                   |                             |      92 | 125K |         1 |     +4 |     1 |       1M |       |       |       |       |         |     |     0.43 | Cpu (1)                           |
|    11 |      HASH JOIN                                    |                             |    2544 | 125K |         1 |     +4 |     1 |       2M |       |       |       |       |         |     |          |                                   |
|    12 |       JOIN FILTER CREATE                          | :BF0001                     |    2525 | 2827 |         1 |     +4 |     1 |       2M |       |       |       |       |         |     |          |                                   |
|    13 |        PART JOIN FILTER CREATE                    | :BF0000                     |    2525 | 2827 |         3 |     +2 |     1 |       2M |       |       |       |       |         |     |     0.43 | Cpu (1)                           |
|    14 |         PARTITION RANGE SINGLE                    |                             |    2525 | 2827 |         1 |     +4 |     1 |       2M |       |       |       |       |         |     |          |                                   |
|    15 |          TABLE ACCESS STORAGE FULL                | SRC_CUS                     |    2525 | 2827 |         4 |     +1 |     1 |       2M |   502 | 499MB |       |       |  93.57% |     |     0.43 | Cpu (1)                           |
|    16 |           VIEW                                    |                             |       1 |    2 |         1 |     +4 |     1 |        1 |       |       |       |       |         |     |          |                                   |
|    17 |            TABLE ACCESS STORAGE FULL              | SYS_TEMP_0FDA01F2C_F45FB396 |       1 |    2 |         1 |     +4 |     1 |        1 |     1 | 32768 |       |       |         |     |          |                                   |
|    18 |       JOIN FILTER USE                             | :BF0001                     |   57337 | 122K |         2 |     +3 |     1 |       2M |       |       |       |       |         |     |     0.43 | Cpu (1)                           |
|    19 |        PARTITION RANGE JOIN-FILTER                |                             |   57337 | 122K |         1 |     +4 |     1 |       2M |       |       |       |       |         |     |          |                                   |
|    20 |         TABLE ACCESS STORAGE FULL                 | SRC_CUS_PT                  |   57337 | 122K |         1 |     +4 |     1 |       2M |       |       |       |       |         |     |          |                                   |
| -> 21 |    HASH JOIN OUTER                                |                             |       1 | 3475 |       231 |     +6 |     1 |        0 |       |       |       |       |         | 13M |     0.87 | Cpu (2)                           |
|    22 |     NESTED LOOPS OUTER                            |                             |       1 | 3475 |       223 |     +6 |     1 |    40351 |       |       |       |       |         |     |          |                                   |
|    23 |      STATISTICS COLLECTOR                         |                             |         |      |       223 |     +6 |     1 |    40350 |       |       |       |       |         |     |          |                                   |
|    24 |       NESTED LOOPS                                |                             |       1 |  189 |       223 |     +6 |     1 |    40350 |       |       |       |       |         |     |          |                                   |
|    25 |        NESTED LOOPS                               |                             |       1 |  186 |       223 |     +6 |     1 |       1M |       |       |       |       |         |     |          |                                   |
|    26 |         NESTED LOOPS                              |                             |       1 |  186 |       223 |     +6 |     1 |       1M |       |       |       |       |         |     |          |                                   |
|    27 |          VIEW                                     |                             |      92 |    2 |       223 |     +6 |     1 |       1M |       |       |       |       |         |     |          |                                   |
|    28 |           TABLE ACCESS STORAGE FULL               | SYS_TEMP_0FDA01F2D_F45FB396 |      92 |    2 |       225 |     +4 |     1 |       1M |    15 |  15MB |       |       |         |     |          |                                   |
|    29 |          PARTITION RANGE SINGLE                   |                             |       1 |    2 |       223 |     +6 |    1M |       1M |       |       |       |       |         |     |     0.43 | Cpu (1)                           |
|    30 |           TABLE ACCESS BY LOCAL INDEX ROWID       | IN_D                        |       1 |    2 |       224 |     +5 |    1M |       1M |       |       |       |       |         |     |    81.30 | Cpu (187)                         |
|    31 |            INDEX UNIQUE SCAN                      | XPK_IN_D                    |       1 |    1 |       223 |     +6 |    1M |       1M |       |       |       |       |         |     |     3.48 | Cpu (8)                           |
|    32 |             VIEW                                  |                             |       1 |    2 |         1 |     +6 |     1 |        1 |       |       |       |       |         |     |          |                                   |
|    33 |              TABLE ACCESS STORAGE FULL            | SYS_TEMP_0FDA01F2C_F45FB396 |       1 |    2 |         1 |     +6 |     1 |        1 |       |       |       |       |         |     |          |                                   |
|    34 |         PARTITION LIST SINGLE                     |                             |       1 |      |       223 |     +6 |    1M |       1M |       |       |       |       |         |     |          |                                   |
| -> 35 |          INDEX UNIQUE SCAN                        | XPK_INS                     |       1 |      |       231 |     +6 |    1M |       1M |       |       |       |       |         |     |     1.30 | Cpu (3)                           |
|    36 |        PARTITION RANGE SINGLE                     |                             |       1 |    3 |       223 |     +6 |    1M |    40350 |       |       |       |       |         |     |     1.74 | Cpu (4)                           |
|    37 |         TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | INS_B                       |       1 |    3 |       223 |     +6 |    1M |    40350 |       |       |       |       |         |     |     3.48 | Cpu (8)                           |
|    38 |          INDEX RANGE SCAN                         | XIE_INS_B_1                 |       1 |    2 |       223 |     +6 |    1M |     122K |       |       |       |       |         |     |     2.61 | Cpu (6)                           |
|    39 |           VIEW                                    |                             |       1 |    2 |         1 |     +6 |     1 |        1 |       |       |       |       |         |     |          |                                   |
|    40 |            TABLE ACCESS STORAGE FULL              | SYS_TEMP_0FDA01F2C_F45FB396 |       1 |    2 |         1 |     +6 |     1 |        1 |       |       |       |       |         |     |          |                                   |
|    41 |      PARTITION RANGE ITERATOR                     |                             |       1 | 3286 |           |        |       |          |       |       |       |       |         |     |          |                                   |
|<b>    42 |       TABLE ACCESS STORAGE FULL                   | INS_PT                      |       1 | 3286 |           |        |       |          |       |       |       |       |         |     |          |                                   |
| -> 43 |     PARTITION RANGE JOIN-FILTER                   |                             |       1 | 3286 |         9 |   +228 |     1 |      26M |       |       |       |       |         |     |          |                                   |
| -> 44 |      TABLE ACCESS STORAGE FULL                    | INS_PT                      |       1 | 3286 |         9 |   +228 |    27 |      26M | 14686 |  15GB |       |       |  91.49% |  7M |     3.04 | Cpu (4)        </b>                   |
|       |                                                   |                             |         |      |           |        |       |          |       |       |       |       |         |     |          | cell multiblock physical read (2) |
|       |                                                   |                             |         |      |           |        |       |          |       |       |       |       |         |     |          | cell smart table scan (1)         |
===================================================================================================================================================================================================================================================




Plan from xplan



SQL_ID  8cgj2pd23darp, child number 0

-------------------------------------

Plan hash value: 2657002414

---------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                     | Name                        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                              |                             |       |       |   128K(100)|          |       |       |
|   1 |  TEMP TABLE TRANSFORMATION                    |                             |       |       |            |          |       |       |
|   2 |   LOAD AS SELECT                              |                             |       |       |            |          |       |       |
|   3 |    SORT AGGREGATE                             |                             |     1 |    45 |            |          |       |       |
|*  4 |     TABLE ACCESS STORAGE FULL                 | B_CTL               |     1 |    45 |    13  (77)| 00:00:01 |       |       |
|   9 |   LOAD AS SELECT                              |                             |       |       |            |          |       |       |
|  10 |    HASH UNIQUE                                |                             |    92 |  5980 |   125K (68)| 00:00:20 |       |       |
|* 11 |     HASH JOIN                                 |                             |  2544 |   161K|   125K (68)| 00:00:20 |       |       |
|  12 |      JOIN FILTER CREATE                       | :BF0001                     |  2525 | 98475 |  2827  (49)| 00:00:01 |       |       |
|  13 |       PART JOIN FILTER CREATE                 | :BF0000                     |  2525 | 98475 |  2827  (49)| 00:00:01 |       |       |
|  14 |        PARTITION RANGE SINGLE                 |                             |  2525 | 98475 |  2827  (49)| 00:00:01 |   KEY |   KEY |
|* 15 |         TABLE ACCESS STORAGE FULL             | SRC_CUS                     |  2525 | 98475 |  2827  (49)| 00:00:01 |   KEY |   KEY |
|  16 |          VIEW                                 |                             |     1 |     9 |     2   (0)| 00:00:01 |       |       |
|  17 |           TABLE ACCESS STORAGE FULL           | SYS_TEMP_0FDA01F2C_F45FB396 |     1 |     9 |     2   (0)| 00:00:01 |       |       |
|  18 |      JOIN FILTER USE                          | :BF0001                     | 57337 |  1455K|   122K (68)| 00:00:20 |       |       |
|  19 |       PARTITION RANGE JOIN-FILTER             |                             | 57337 |  1455K|   122K (68)| 00:00:20 |:BF0000|:BF0000|
|* 20 |        TABLE ACCESS STORAGE FULL              | SRC_CUS_PT                  | 57337 |  1455K|   122K (68)| 00:00:20 |:BF0000|:BF0000|
|* 21 |   HASH JOIN OUTER                             |                             |     1 |   196 |  3475  (52)| 00:00:01 |       |       |
|  22 |    NESTED LOOPS                               |                             |     1 |   162 |   189   (0)| 00:00:01 |       |       |
|  23 |     NESTED LOOPS                              |                             |     1 |   104 |   186   (0)| 00:00:01 |       |       |
|  24 |      NESTED LOOPS                             |                             |     1 |    89 |   186   (0)| 00:00:01 |       |       |
|  25 |       VIEW                                    |                             |    92 |  1196 |     2   (0)| 00:00:01 |       |       |
|  26 |        TABLE ACCESS STORAGE FULL              | SYS_TEMP_0FDA01F2D_F45FB396 |    92 |   828 |     2   (0)| 00:00:01 |       |       |
|  27 |       PARTITION RANGE SINGLE                  |                             |     1 |    76 |     2   (0)| 00:00:01 |   KEY |   KEY |
|* 28 |        TABLE ACCESS BY LOCAL INDEX ROWID      | IN_D                        |     1 |    76 |     2   (0)| 00:00:01 |   KEY |   KEY |
|* 29 |         INDEX UNIQUE SCAN                     | XPK_IN_D                    |     1 |       |     1   (0)| 00:00:01 |   KEY |   KEY |
|  30 |          VIEW                                 |                             |     1 |     9 |     2   (0)| 00:00:01 |       |       |
|  31 |           TABLE ACCESS STORAGE FULL           | SYS_TEMP_0FDA01F2C_F45FB396 |     1 |     9 |     2   (0)| 00:00:01 |       |       |
|  32 |      PARTITION LIST SINGLE                    |                             |     1 |    15 |     0   (0)|          |   KEY |   KEY |
|* 33 |       INDEX UNIQUE SCAN                       | XPK_INS                     |     1 |    15 |     0   (0)|          |   109 |   109 |
|  34 |     PARTITION RANGE SINGLE                    |                             |     1 |    58 |     3   (0)| 00:00:01 |   KEY |   KEY |
|* 35 |      TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| INS_B                       |     1 |    58 |     3   (0)| 00:00:01 |   KEY |   KEY |
|* 36 |       INDEX RANGE SCAN                        | XIE_INS_B_1                 |     1 |       |     2   (0)| 00:00:01 |   KEY |   KEY |
|  37 |        VIEW                                   |                             |     1 |     9 |     2   (0)| 00:00:01 |       |       |
|  38 |         TABLE ACCESS STORAGE FULL             | SYS_TEMP_0FDA01F2C_F45FB396 |     1 |     9 |     2   (0)| 00:00:01 |       |       |
|  39 |    PARTITION RANGE JOIN-FILTER                |                             |     1 |    34 |  3286  (55)| 00:00:01 |:BF0000|:BF0000|
|* 40 |     TABLE ACCESS STORAGE FULL                 | INS_PT                      |     1 |    34 |  3286  (55)| 00:00:01 |:BF0000|:BF0000|
---------------------------------------------------------------------------------------------------------------------------------------------


query:
WITH
    PR AS (SELECT MAX (P_DT) AS PRDT FROM B_CTL),
    SR_PT AS (SELECT DISTINCT IND_KEY
           FROM SRC_CUS, SRC_CUS_PT
          WHERE 1 = 1 AND SRC_CUS.SRC_KEY = SRC_CUS_PT.SRC_KEY),  INST_D  AS (SELECT *
           FROM IN_D, INS
          WHERE     IN_D.F_DATE = (SELECT PRDT FROM PR)
                AND IN_D.I_KEY = INS.I_KEY)
SELECT *
  FROM INST_D,
       INS_BALANCE,
       SR_PT,
       INS_PT,
       (SELECT * FROM SR_PT WHERE 1 = 2) DUMMY
WHERE     1 = 1
       AND INS_BALANCE.F_DATE = (SELECT PRDT FROM PR)
       AND INS_BALANCE.IND_KEY = INST_D.IND_KEY
       AND INS_PT.IND_KEY(+) = INST_D.IND_KEY
       AND INS_PT.F_DATE(+) = INST_D.F_DATE
       AND SR_PT.IND_KEY = INST_D.IND_KEY
       AND  INST_D.IND_KEY = DUMMY.IND_KEY


and Connor said...

To see "Starts" in display_plan, you'd need to gather_plan_statistics hint. Then you would see the alignment.

For example, (I'm using hints here to force a full table scan multiple times via a nested loop)

SQL> select /*+ leading(d) use_nl(e) full(d) full(e) */ e.ename, d.dname
  2  from emp e, dept d
  3  where d.deptno = e.deptno;

ENAME      DNAME
---------- --------------
CLARK      ACCOUNTING
KING       ACCOUNTING
MILLER     ACCOUNTING
SMITH      RESEARCH
JONES      RESEARCH
SCOTT      RESEARCH
ADAMS      RESEARCH
FORD       RESEARCH
ALLEN      SALES
WARD       SALES
MARTIN     SALES
BLAKE      SALES
TURNER     SALES
JAMES      SALES

14 rows selected.

SQL>
SQL> select * from dbms_xplan.display_cursor();

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  6h7ayupfarfhy, child number 0
-------------------------------------
select /*+ leading(d) use_nl(e) full(d) full(e) */ e.ename, d.dname
from emp e, dept d where d.deptno = e.deptno

Plan hash value: 4192419542

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |    10 (100)|          |
|   1 |  NESTED LOOPS      |      |    14 |   308 |    10   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |     4 |    52 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| EMP  |     4 |    36 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("D"."DEPTNO"="E"."DEPTNO")


21 rows selected.

SQL>
SQL> select /*+ leading(d) use_nl(e) full(d) full(e) gather_plan_statistics */ e.ename, d.dname
  2  from emp e, dept d
  3  where d.deptno = e.deptno;

ENAME      DNAME
---------- --------------
CLARK      ACCOUNTING
KING       ACCOUNTING
MILLER     ACCOUNTING
SMITH      RESEARCH
JONES      RESEARCH
SCOTT      RESEARCH
ADAMS      RESEARCH
FORD       RESEARCH
ALLEN      SALES
WARD       SALES
MARTIN     SALES
BLAKE      SALES
TURNER     SALES
JAMES      SALES

14 rows selected.

SQL>
SQL> select * from dbms_xplan.display_cursor(format=>'ALLSTATS LAST');

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  0aq8k2bphz1qn, child number 0
-------------------------------------
select /*+ leading(d) use_nl(e) full(d) full(e) gather_plan_statistics
*/ e.ename, d.dname from emp e, dept d where d.deptno = e.deptno

Plan hash value: 4192419542

-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |     14 |00:00:00.01 |      32 |
|   1 |  NESTED LOOPS      |      |      1 |     14 |     14 |00:00:00.01 |      32 |
|   2 |   TABLE ACCESS FULL| DEPT |      1 |      4 |      4 |00:00:00.01 |       7 |
|*  3 |   TABLE ACCESS FULL| EMP  |      4 |      4 |     14 |00:00:00.01 |      25 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("D"."DEPTNO"="E"."DEPTNO")


21 rows selected.

SQL>
SQL>


Rating

  (4 ratings)

Comments

Ken han, February 05, 2024 - 9:28 am UTC

Sorry Connor, Perhaps I didn't explain my question clearly.
If you read last 3 lines of both xplan and sql monitor outputs, you’ll see in sql monitor output, InS_PT was read and joined twice, in Xplan output, it was read and join once. In query, this table is used once. My question is why these two output are different?
Chris Saxon
February 06, 2024 - 10:50 am UTC

The query uses an adaptive plan. This lets the optimizer switch between nested loops or a hash join at runtime. This often leads to a table appearing twice in the plan. It's only accessed once depending on the join the database uses.

You can see this from the STATISTICS COLLECTOR operation at line 23 in the SQL monitor report. This line is missing from the DBMS_XPlan report. So you're seeing the final plan after the database has chosen which join method to use.

Assuming both plans are from the same child cursor as Narendra notes, you can view the full adaptive plan by calling DBMS_XPlan with the +ADAPTIVE option:

e.g.

select * from dbms_xplan.display_cursor( ..., format => 'ALLSTATS LAST +ADAPTIVE');

Re: Ken Han

Narendra, February 05, 2024 - 1:35 pm UTC

Ken,

You might want to check and compare the child number and corresponding full plan hash value used by SQL Monitoring report as well as DISPLAY_CURSOR.
Your DISPLAY_CURSOR appears to show plan for child number 0. What is the child number used by SQL Monitoring report?

A reader, February 05, 2024 - 2:12 pm UTC

thanks Narendra,

in my original post, there are sql ID and plan hash value, these values in both plan are same.
SQL Plan Monitoring Details (Plan Hash Value=2657002414)
SQL_ID 8cgj2pd23darp, child number 0
Plan hash value: 2657002414

Re: ken han

Narendra, February 05, 2024 - 5:48 pm UTC

Ken,

I am asking about child number and full plan hash value comparison
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 latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Hints

Need more information on hints? Check out the complete hint descriptions