Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Ken.

Asked: February 06, 2018 - 4:21 am UTC

Last updated: February 07, 2018 - 11:19 am UTC

Version: 11g

Viewed 1000+ times

You Asked

Hi Gurus,

I have a query it runs really long. after adding hint use_hash, it runs less than one minutes.
data in table: det ---239968 bal -- -239968 inst -- 244713 PT_INST --168745
the query as below:

below 3 plans are 1, using hash hint, the query runs less than 1 minute
2. without hint, 3rd minute the plan looks like, 3, without hint, 7th minutes plan looks like,
as you can see, without hint, the A-rows is huge number 45 millions and 68 millions respectively. max record count in table is 244K roughly.
I am wondering what happens in oracle for the nest loop outer join, why the A-rows number is huge?
could anybody explain how the query be executed without hint? 

  SELECT *
    FROM PT_INST A,
         M_ID M,
         (SELECT *
            FROM INSTB,
                 DET DT,
                 BAL BL
           WHERE     B.SRC_SYS = 'EST'
                 AND DT.SRC_SYS_CD = 'EST'
                 AND DT.RUN_DT = DATE '2018-02-02'
                 AND DT.INST_KEY = B.INST_KEY
                 AND BL.SRC_SYS_CD = 'EST'
                 AND BL.RUN_DT = DATE '2018-02-02'
                 AND DT.DET_KEY = BL.DET_KEY
                 AND BL.AMT_TYPE = 'ABAL') B
   WHERE     A.SRC_SYS_CD = 'DELA'
         AND A.RUN_DT = DATE '2018-02-02'
         AND A.SRC_SYS = B.SRC_SYS(+)
         AND CASE
                WHEN A.SRC_SYS IN ('TSS')
                THEN
                   TRIM (A.INST_ACCT_NMBR_CHAR)
                ELSE
                   LTRIM (TRIM (A.INST_ACCT_NMBR_CHAR), 0)
             END = B.INST_ACCT_NMBR(+)
         AND A.SRC_SYS = 'EST'
         AND A.SRC_SYS = M.SRC_SYS(+)
GROUP BY A.SRC_SYS_CD


below is plan using hash hint
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name               | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    |      1 |        |       | 83188 (100)|          |       |       |      1 |00:00:08.80 |   23835 |  10665 |       |       |          |
|   1 |  HASH GROUP BY              |                    |      1 |      4 |   392 | 83188  (13)| 00:01:51 |       |       |      1 |00:00:08.80 |   23835 |  10665 |   669K|   669K|  342K (0)|
|*  2 |   HASH JOIN OUTER           |                    |      1 |   1342 |   128K| 83187  (13)| 00:01:51 |       |       |    239K|00:00:08.44 |   23835 |  10665 |    13M|  1902K|   17M (0)|
|*  3 |    HASH JOIN OUTER          |                    |      1 |     28 |  1120 | 79126  (13)| 00:01:46 |       |       |    168K|00:00:06.09 |   10632 |  10612 |    10M|  2054K|   17M (0)|
|   4 |     PARTITION RANGE SINGLE  |                    |      1 |     28 |   812 | 79119  (13)| 00:01:46 |    74 |    74 |    168K|00:00:05.81 |   10616 |  10598 |       |       |          |
|   5 |      PARTITION LIST SINGLE  |                    |      1 |     28 |   812 | 79119  (13)| 00:01:46 |     1 |     1 |    168K|00:00:05.81 |   10616 |  10598 |       |       |          |
|*  6 |       TABLE ACCESS FULL     |          PT_INST   |      1 |     28 |   812 | 79119  (13)| 00:01:46 |   221 |   221 |    168K|00:00:05.81 |   10616 |  10598 |       |       |          |
|*  7 |     TABLE ACCESS FULL       |   M_ID             |      1 |      1 |    11 |     6   (0)| 00:00:01 |       |       |      1 |00:00:00.02 |      16 |     14 |       |       |          |
|   8 |    VIEW                     |                    |      1 |   4871 |   275K|  4061   (4)| 00:00:06 |       |       |    239K|00:00:01.56 |   13203 |     53 |       |       |          |
|*  9 |     HASH JOIN               |                    |      1 |   4871 |   537K|  4061   (4)| 00:00:06 |       |       |    239K|00:00:01.56 |   13203 |     53 |  8868K|  2757K|   12M (0)|
|* 10 |      HASH JOIN              |                    |      1 |   4827 |   296K|  3504   (2)| 00:00:05 |       |       |    239K|00:00:00.67 |   11590 |     17 |  8645K|  2824K|   12M (0)|
|  11 |       PARTITION RANGE SINGLE|                    |      1 |   4827 |   141K|  1832   (2)| 00:00:03 |    74 |    74 |    239K|00:00:00.25 |    6082 |     17 |       |       |          |
|  12 |        PARTITION LIST SINGLE|                    |      1 |   4827 |   141K|  1832   (2)| 00:00:03 |   KEY |   KEY |    239K|00:00:00.25 |    6082 |     17 |       |       |          |
|* 13 |         TABLE ACCESS FULL   |       BAL          |      1 |   4827 |   141K|  1832   (2)| 00:00:03 |  5996 |  5996 |    239K|00:00:00.01 |    6082 |     17 |       |       |          |
|  14 |       PARTITION RANGE SINGLE|                    |      1 |    238K|  7675K|  1660   (2)| 00:00:03 |    74 |    74 |    239K|00:00:00.24 |    5508 |      0 |       |       |          |
|  15 |        PARTITION LIST SINGLE|                    |      1 |    238K|  7675K|  1660   (2)| 00:00:03 |   KEY |   KEY |    239K|00:00:00.01 |    5508 |      0 |       |       |          |
|* 16 |         TABLE ACCESS FULL   |       DET          |      1 |    238K|  7675K|  1660   (2)| 00:00:03 |  6119 |  6119 |    239K|00:00:00.01 |    5508 |      0 |       |       |          |
|  17 |      PARTITION LIST SINGLE  |                    |      1 |    246K|    11M|   545  (11)| 00:00:01 |   KEY |   KEY |    244K|00:00:00.26 |    1613 |     36 |       |       |          |
|  18 |       TABLE ACCESS FULL     |       INST         |      1 |    246K|    11M|   545  (11)| 00:00:01 |   123 |   123 |    244K|00:00:00.02 |    1613 |     36 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 
   2 - access("A"."SRC_SYS"="B"."SRC_SYS" AND "B"."INST_ACCT_NMBR"=CASE "A"."SRC_SYS" WHEN 'TSS' THEN 
              TRIM("A"."INST_ACCT_NMBR_CHAR") ELSE LTRIM(TRIM("A"."INST_ACCT_NMBR_CHAR"),'0') END )
   3 - access("A"."SRC_SYS"="M"."SRC_SYS")
   6 - filter(("A"."SRC_SYS"='EST' AND "A"."RUN_DT"=TO_DATE(' 2018-02-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
   7 - filter("M"."SRC_SYS"='EST')
   9 - access("DT"."INST_KEY"="B"."INST_KEY")
  10 - access("DT"."DET_KEY"="BL"."DET_KEY")
  13 - filter(("BL"."AMT_TYPE"='ABAL' AND "BL"."RUN_DT"=TO_DATE(' 2018-02-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
  16 - filter("DT"."RUN_DT"=TO_DATE(' 2018-02-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

without hint 4 minutes plan

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name               | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    |      1 |        |       | 79127 (100)|          |       |       |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|   1 |  HASH GROUP BY              |                    |      1 |      4 |   388 | 79127  (13)| 00:01:46 |       |       |      0 |00:00:00.01 |       0 |      0 |   700K|   700K|          |
|   2 |   NESTED LOOPS OUTER        |                    |      1 |   1342 |   127K| 79126  (13)| 00:01:46 |       |       |    195 |00:04:37.12 |    2452K|    683 |       |       |          |
|*  3 |    HASH JOIN OUTER          |                    |      1 |     28 |  1120 | 79126  (13)| 00:01:46 |       |       |    186 |00:00:01.26 |   10623 |    683 |    10M|  2054K|   17M (0)|
|   4 |     PARTITION RANGE SINGLE  |                    |      1 |     28 |   812 | 79119  (13)| 00:01:46 |    74 |    74 |    168K|00:00:01.01 |   10616 |    681 |       |       |          |
|   5 |      PARTITION LIST SINGLE  |                    |      1 |     28 |   812 | 79119  (13)| 00:01:46 |     1 |     1 |    168K|00:00:01.01 |   10616 |    681 |       |       |          |
|*  6 |       TABLE ACCESS FULL     | PT_INST            |      1 |     28 |   812 | 79119  (13)| 00:01:46 |   221 |   221 |    168K|00:00:01.01 |   10616 |    681 |       |       |          |
|*  7 |     TABLE ACCESS FULL       | M_ID               |      1 |      1 |    11 |     6   (0)| 00:00:01 |       |       |      1 |00:00:00.01 |       7 |      2 |       |       |          |
|*  8 |    VIEW PUSHED PREDICATE    |                    |    186 |     49 |  2793 |     0   (0)|          |       |       |    195 |00:04:35.99 |    2442K|      0 |       |       |          |
|*  9 |     HASH JOIN               |                    |    186 |   4871 |   537K|  4061   (4)| 00:00:06 |       |       |     44M|00:03:51.18 |    2454K|      0 |  8868K|  2757K|   13M (0)|
|* 10 |      HASH JOIN              |                    |    186 |   4827 |   296K|  3504   (2)| 00:00:05 |       |       |     44M|00:02:40.31 |    2155K|      0 |  8645K|  2824K|   13M (0)|
|  11 |       PARTITION RANGE SINGLE|                    |    186 |   4827 |   141K|  1832   (2)| 00:00:03 |    74 |    74 |     44M|00:00:44.67 |    1131K|      0 |       |       |          |
|  12 |        PARTITION LIST SINGLE|                    |    186 |   4827 |   141K|  1832   (2)| 00:00:03 |   KEY |   KEY |     44M|00:00:00.04 |    1131K|      0 |       |       |          |
|* 13 |         TABLE ACCESS FULL   | BAL                |    186 |   4827 |   141K|  1832   (2)| 00:00:03 |  5996 |  5996 |     44M|00:00:00.02 |    1131K|      0 |       |       |          |
|  14 |       PARTITION RANGE SINGLE|                    |    186 |    238K|  7675K|  1660   (2)| 00:00:03 |    74 |    74 |     44M|00:00:44.63 |    1024K|      0 |       |       |          |
|  15 |        PARTITION LIST SINGLE|                    |    186 |    238K|  7675K|  1660   (2)| 00:00:03 |   KEY |   KEY |     44M|00:00:44.63 |    1024K|      0 |       |       |          |
|* 16 |         TABLE ACCESS FULL   | DET                |    186 |    238K|  7675K|  1660   (2)| 00:00:03 |  6119 |  6119 |     44M|00:00:00.01 |    1024K|      0 |       |       |          |
|  17 |      PARTITION LIST SINGLE  |                    |    186 |    246K|    11M|   545  (11)| 00:00:01 |   KEY |   KEY |     45M|00:00:00.02 |     298K|      0 |       |       |          |
|* 18 |       TABLE ACCESS FULL     | INST               |    186 |    246K|    11M|   545  (11)| 00:00:01 |   123 |   123 |     45M|00:00:00.02 |     298K|      0 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


without hint 7 minutes plan

---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name               | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   |
----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    |      1 |        |       | 79127 (100)|          |      0 |00:00:00.01 |
|   1 |  HASH GROUP BY              |                    |      1 |      4 |   388 | 79127  (13)| 00:01:46 |      0 |00:00:00.01 |
|   2 |   NESTED LOOPS OUTER        |                    |      1 |   1342 |   127K| 79126  (13)| 00:01:46 |    299 |00:07:02.21 |
|*  3 |    HASH JOIN OUTER          |                    |      1 |     28 |  1120 | 79126  (13)| 00:01:46 |    286 |00:00:00.41 |
|   4 |     PARTITION RANGE SINGLE  |                    |      1 |     28 |   812 | 79119  (13)| 00:01:46 |    168K|00:00:00.16 |
|   5 |      PARTITION LIST SINGLE  |                    |      1 |     28 |   812 | 79119  (13)| 00:01:46 |    168K|00:00:00.16 |
|*  6 |       TABLE ACCESS FULL     | PT_INST            |      1 |     28 |   812 | 79119  (13)| 00:01:46 |    168K|00:00:00.16 |
|*  7 |     TABLE ACCESS FULL       | M_ID               |      1 |      1 |    11 |     6   (0)| 00:00:01 |      1 |00:00:00.01 |
|*  8 |    VIEW PUSHED PREDICATE    |                    |    286 |     49 |  2793 |     0   (0)|          |    299 |00:07:02.03 |
|*  9 |     HASH JOIN               |                    |    286 |   4871 |   537K|  4061   (4)| 00:00:06 |     <b>68M</b>|00:05:54.58 |
|* 10 |      HASH JOIN              |                    |    286 |   4827 |   296K|  3504   (2)| 00:00:05 |     <b>68M</b>|00:04:05.82 |
|  11 |       PARTITION RANGE SINGLE|                    |    286 |   4827 |   141K|  1832   (2)| 00:00:03 |     68M|00:01:08.65 |
|  12 |        PARTITION LIST SINGLE|                    |    286 |   4827 |   141K|  1832   (2)| 00:00:03 |     68M|00:00:00.02 |
|* 13 |         TABLE ACCESS FULL   | BAL                |    286 |   4827 |   141K|  1832   (2)| 00:00:03 |     68M|00:00:00.02 |
|  14 |       PARTITION RANGE SINGLE|                    |    286 |    238K|  7675K|  1660   (2)| 00:00:03 |     68M|00:01:08.65 |
|  15 |        PARTITION LIST SINGLE|                    |    286 |    238K|  7675K|  1660   (2)| 00:00:03 |     68M|00:01:08.65 |
|* 16 |         TABLE ACCESS FULL   | DET                |    286 |    238K|  7675K|  1660   (2)| 00:00:03 |     68M|00:00:00.02 |
|  17 |      PARTITION LIST SINGLE  |                    |    286 |    246K|    11M|   545  (11)| 00:00:01 |     69M|00:00:00.01 |
|* 18 |       TABLE ACCESS FULL     | INST               |    286 |    246K|    11M|   545  (11)| 00:00:01 |     69M|00:00:00.01 | 
----------------------------------------------------------------------------------------------------------------------------------


and Chris said...

In a nested loop, for each row returned by the first table, the database queries the second table.

In your third plan, the "HASH JOIN OUTER" at step three returns 286 rows (from the A-rows column). It passes the values from this to the "VIEW PUSHED PREDICATE" at step 8. 286 times.

From your first plan, you can see that the hash join below this at step 9 returns 239K rows.

239,000 * 286 = 63,354,000

So there are your 68 million rows!

So how can you fix it?

One way is with SQL Plan Management. You can use SQL Profiles to help fix optimizer estimates. Or baselines to lock the query to a specific plan.

Read more about this at:

http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-sql-plan-management-11gr2-133099.pdf

Another thing to do is look at the stats for the tables. The optimizer estimates the full scan of pt_inst at step 6 will return just 28 rows. But it actually gets 168,000!

When there is a big difference between estimated and actual rows, the optimizer is likely to choose the "wrong" plan.

Rating

  (2 ratings)

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

Comments

great explanation

Ken han, February 06, 2018 - 6:06 pm UTC

Thanks Chris for your clear explanation.
I have one more question.
for nest looping, each time it calls the inline view, the view need to run once? or oracle will cache the view result (hash join) and loop the cache?
Chris Saxon
February 07, 2018 - 11:19 am UTC

Do you mean: are the results of the view cached? And on each iteration of the nested loop these fetched instead of executing the whole query to generate the results?

In your case no. You know this because actual rows for all steps in the subplan below the view ~ starts * rows in the table.

In some cases it can though. In particular, if when using the with clause you can use the materialize hint to "force" the optimizer to store the intermediate results. It can also choose to do this itself in some cases.

https://oracle-base.com/articles/misc/with-clause#materialize-hint

one more question

Ken han, February 06, 2018 - 6:09 pm UTC

one more question, in the first and second plan, there is column "READ", what's this column mean?
Chris Saxon
February 07, 2018 - 10:53 am UTC

Reads are disk reads.

Most of the execution stats come from V$SQL_PLAN_STATISTICS, so look there for a description of the columns:

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/refrn/V-SQL_PLAN_STATISTICS.html#GUID-983DE0B1-1824-4A03-9C96-DCAFF5662B1A
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

Performance

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