Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Reading Row Counts on an Explain Plan Clarify.

Tater_SaladMar 26 2018 — edited Apr 2 2018

I'm focusing on learning how to read an explain plan and from all the documentation I've poured through, I'm amazed at how little information is given to explaining some of the basic things on the explain plan diagram, like how to interpret what each returned row value means. I understand some of this now but I'm trying to get more of an algorythmic way of reading row returns.

The plan below is an example of a bad query BTW that was recently tuned by an ARMY of "able minded" SQL Pros here on OTN. I'm using the bad plan because It'll better help me understand how this all works.

Just tell me if I'm right. Since these are E-ROWS the below are, of course, all Estimates.

Line 7 -  Executed 758 times. Full Scans the table TEMP_NAVRECON_ENTITY  and returned accesses 758 rows.

Line 8 - Executed 3257k times. Full Scans the table PROFIT_LOSS_LEDGER_ACTIVITY and returned accesses 3257k rows.

Line 6 - The hash join of the 2 tables above excecutes 1924k times and returned accesses 1924k rows.

Line 13 - Executed 1 times. Full Scans the table REAL_TIME_LEDGER_ACTIVITY and returned accesses 1 row?

Line 14 - Executed 891 times. Index Range Scans UK_STAR_PROFILE_GROUPS and returned accesses 891 rows.

Line 15 - Executed 1 time. Index Rowid Scans STAR_PROFILE_GROUPS and returned accesses 1 row?

Line 16 - Executed 758 times again. Full table scans TEMP_NAVRECON_ENTITY  and returned accesses 758 rows.

Line 12 - Executed 891 times. Loops the below INDENTED processes 891 times. *** I really want to understand this one.**** ( is Line 12 executing the 891 steps of Line                 14, 891 times ((So 891 * 891))  or is Line 14 being executed 891 by the directio0n of the loop on Line 12.

Line 11 - Executed 1 time. Executes everything indented below it once.

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

| Id  | Operation                          | Name                        | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|

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

|   0 | SELECT STATEMENT                   |                             |      1 |        |       |       |   172K(100)|          |      1 |00:00:17.25 |     749K|    754K|  13741 |       |       |          |         |

|   1 |  SORT AGGREGATE                    |                             |      1 |      1 |       |       |            |          |      1 |00:00:17.25 |     749K|    754K|  13741 |       |       |          |         |

|   2 |   VIEW                             |                             |      1 |   1924K|       |       |   172K  (2)| 00:34:33 |   2157K|00:00:17.14 |     749K|    754K|  13741 |       |       |          |         |

|   3 |    SORT UNIQUE                     |                             |      1 |   1924K|   165M|   200M|   172K  (2)| 00:34:33 |   2157K|00:00:16.90 |     749K|    754K|  13741 |   120M|  3732K|   97M (1)|     108K|

|   4 |     UNION-ALL                      |                             |      1 |        |       |       |            |          |   2157K|00:00:10.68 |     749K|    740K|      0 |       |       |          |         |

|*  5 |      FILTER                        |                             |      1 |        |       |       |            |          |   2157K|00:00:09.64 |     740K|    740K|      0 |       |       |          |         |

|*  6 |       HASH JOIN                    |                             |      1 |   1924K|   165M|       |   131K  (3)| 00:26:15 |   2157K|00:00:09.35 |     740K|    740K|      0 |  1817K|  1817K| 1541K (0)|         |

|   7 |        TABLE ACCESS FULL           | TEMP_NAVRECON_ENTITY        |      1 |    758 |  7580 |       |     2   (0)| 00:00:01 |    758 |00:00:00.01 |       4 |      0 |      0 |       |       |          |         |

|*  8 |        TABLE ACCESS FULL           | PROFIT_LOSS_LEDGER_ACTIVITY |      1 |   3257K|   248M|       |   131K  (3)| 00:26:14 |   3173K|00:00:08.21 |     740K|    740K|      0 |       |       |          |         |

|   9 |      HASH GROUP BY                 |                             |      1 |      1 |   142 |       |  1811   (2)| 00:00:22 |      0 |00:00:00.05 |    9249 |      0 |      0 |   791K|   791K|          |         |

|* 10 |       HASH JOIN SEMI               |                             |      1 |      1 |   142 |       |  1809   (2)| 00:00:22 |      0 |00:00:00.05 |    9249 |      0 |      0 |   798K|   798K|  171K (0)|         |

|  11 |        NESTED LOOPS                |                             |      1 |      1 |   132 |       |  1806   (2)| 00:00:22 |      0 |00:00:00.05 |    9249 |      0 |      0 |       |       |          |         |

|  12 |         NESTED LOOPS               |                             |      1 |    891 |   132 |       |  1806   (2)| 00:00:22 |      0 |00:00:00.05 |    9249 |      0 |      0 |       |       |          |         |

|* 13 |          TABLE ACCESS FULL         | REAL_TIME_LEDGER_ACTIVITY   |      1 |      1 |    86 |       |  1777   (2)| 00:00:22 |      0 |00:00:00.05 |    9249 |      0 |      0 |       |       |          |         |

|* 14 |          INDEX RANGE SCAN          | UK_STAR_PROFILE_GROUPS      |      0 |    891 |       |       |     3   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |      0 |      0 |       |       |          |         |

|* 15 |         TABLE ACCESS BY INDEX ROWID| STAR_PROFILE_GROUPS         |      0 |      1 |    46 |       |    29   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |      0 |      0 |       |       |          |         |

|  16 |        TABLE ACCESS FULL           | TEMP_NAVRECON_ENTITY        |      0 |    758 |  7580 |       |     2   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |      0 |      0 |       |       |          |         |

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

If you need the query that goes along with this then:

SELECT                                                    

      COUNT (*)

  FROM (

    SELECT /*+ gather_plan_statistics*/ m.portfolio_acct,

               m.asset_id,

               NULL,

               m.acct_dt,

               balance,

               TRIM (long_desc),

               TRIM (m.open_evt_id)

          FROM usbank.profit_loss_ledger_activity m

         WHERE     m.portfolio_acct IN (SELECT entity_id

                                          FROM usbank.temp_navrecon_entity)

               AND m.acct_dt BETWEEN TO_DATE ( :p_start_date, 'YYYYMMDD')

                                 AND TO_DATE ( :p_end_date, 'YYYYMMDD')

        UNION

        SELECT m.portfolio_acct,

               m.asset_id,

               NULL,

               m.acct_dt,

               balance,

               TRIM (long_desc),

               TRIM (m.open_evt_id)

          FROM (  SELECT m.portfolio_acct,

                         m.asset_id,

                         m.acct_dt,

                         SUM (amount) balance,

                         TRIM (spg.section) long_desc,

                         m.open_evt_id

                    FROM usbank.real_time_ledger_activity m,

                         rulesdbo.star_profile_groups spg

                   WHERE     TRIM (m.ledger_acct) = spg.line_account

                         AND m.acct_dt = TRUNC (SYSDATE)

                         AND spg.profile_name = 'USB_PNL'

                         AND spg.action = 'GROUP'

                GROUP BY m.portfolio_acct,

                         m.asset_id,

                         spg.section,

                         m.open_evt_id,

                         m.acct_dt) m

         WHERE m.portfolio_acct IN (SELECT entity_id

                                      FROM usbank.temp_navrecon_entity));

This post has been answered by Jonathan Lewis on Mar 27 2018
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 30 2018
Added on Mar 26 2018
24 comments
2,457 views