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 |
----------------------------------------------------------------------------------------------------------------------------------
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.