| Same query runs way better in 10g than in 12c [message #662985] |
Thu, 18 May 2017 06:12  |
 |
Asfakul
Messages: 43
Registered: July 2014
|
Member |
|
|
We recently migrated from 10g to 12c , while doing so most of the queries saw improvement but some queries took a performance hit. like going from 17 minutes to more than 2 hours. Stats are regularly updated too. Since we didn't have much time on our hand we used OPTIMIZER_FEATURES_ENABLE hint to run those using 10g optimizer.
But I really like to know how to troubleshoot in such a case, whereas DB remains but queries perform differently.
PLAN in 12c
Plan hash value: 2179967023
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 19M(100)| |
| 1 | HASH GROUP BY | | 1331 | 170K| | 19M (20)| 00:12:55 |
|* 2 | HASH JOIN | | 57G| 6968G| | 16M (3)| 00:10:46 |
| 3 | VIEW | VW_GBF_15 | 1053 | 66339 | | 5 (20)| 00:00:01 |
| 4 | HASH GROUP BY | | 1053 | 15795 | | 5 (20)| 00:00:01 |
|* 5 | FILTER | | | | | | |
| 6 | NESTED LOOPS | | 1141 | 17115 | | 4 (0)| 00:00:01 |
|* 7 | TABLE ACCESS FULL| CLASS | 1141 | 12551 | | 4 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN| PK_FDT_CATEGORY | 1 | 4 | | 0 (0)| |
|* 9 | HASH JOIN | | 61G| 3919G| 513M| 16M (2)| 00:10:37 |
| 10 | TABLE ACCESS FULL | ORDHEAD | 24M| 233M| | 136K (1)| 00:00:06 |
|* 11 | HASH JOIN | | 3821M| 206G| 10M| 3247K (1)| 00:02:07 |
|* 12 | TABLE ACCESS FULL | SHIPMENT | 345K| 6750K| | 78661 (2)| 00:00:04 |
|* 13 | HASH JOIN | | 588M| 20G| 3952K| 1760K (1)| 00:01:09 |
|* 14 | TABLE ACCESS FULL | DESC_LOOK | 161K| 2052K| | 310 (2)| 00:00:01 |
| 15 | TABLE ACCESS FULL | SHIPSKU | 588M| 13G| | 725K (2)| 00:00:29 |
------------------------------------------------------------------------------------------------------------------
PLAN in 10g10g
Plan hash value: 985652515
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 111K(100)| |
| 1 | HASH GROUP BY | | 1331 | 107K| | 111K (1)| 00:22:19 |
|* 2 | FILTER | | | | | | |
|* 3 | HASH JOIN | | 301K| 23M| | 111K (1)| 00:22:19 |
| 4 | INDEX FULL SCAN | PK_FDT_CATEGORY | 61 | 244 | | 1 (0)| 00:00:01 |
|* 5 | HASH JOIN | | 301K| 22M| | 111K (1)| 00:22:19 |
|* 6 | TABLE ACCESS FULL | CLASS | 1002 | 11022 | | 4 (0)| 00:00:01 |
|* 7 | HASH JOIN | | 374K| 24M| 3952K| 111K (1)| 00:22:19 |
|* 8 | TABLE ACCESS FULL | DESC_LOOK | 161K| 2052K| | 309 (2)| 00:00:04 |
| 9 | TABLE ACCESS BY INDEX ROWID | SHIPSKU | 67 | 1675 | | 5 (0)| 00:00:01 |
| 10 | NESTED LOOPS | | 374K| 19M| | 109K (1)| 00:21:58 |
| 11 | NESTED LOOPS | | 5545 | 162K| | 89723 (1)| 00:17:57 |
|* 12 | TABLE ACCESS FULL | SHIPMENT | 5570 | 108K| | 78580 (2)| 00:15:43 |
| 13 | TABLE ACCESS BY INDEX ROWID| ORDHEAD | 1 | 10 | | 2 (0)| 00:00:01 |
|* 14 | INDEX UNIQUE SCAN | PK_ORDHEAD | 1 | | | 1 (0)| 00:00:01 |
|* 15 | INDEX RANGE SCAN | PK_SHIPSKU | 67 | | | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------
SELECT
DL .DEPT AS DEPT,
CL.PDC_CATEGORY,
'A' AS STATUS,
TO_DATE ('30-Apr-17') AS FIRST_DATE,
SUM(CASE
WHEN OH.order_type='N/B' THEN SS.QTY_RECEIVED * SS.UNIT_COST
ELSE 0 END) AS NB_COST,
SUM(CASE
WHEN OH.order_type='N/B' THEN SS.QTY_RECEIVED * SS.UNIT_RETAIL
ELSE 0 END) AS NB_RETAIL,
SUM(CASE
WHEN OH.order_type !='N/B' THEN SS.QTY_RECEIVED * SS.UNIT_COST
ELSE 0 END) AS BASIC_COST,
SUM(CASE
WHEN OH.order_type !='N/B' THEN SS.QTY_RECEIVED * SS.UNIT_RETAIL
ELSE 0 END) AS BASIC_RETAIL
FROM SHIPSKU SS,
SHIPMENT SH,
ORDHEAD OH,
DESC_LOOK DL,
CLASS CL,
PDT_CATEGORY FC
WHERE DL.DEPT < 80
AND DL.DEPT NOT IN
(25, 26, 18, 33, 40, 24)
--AND OH.ORDER_TYPE <> 'N/B'
AND SH.RECEIVE_DATE BETWEEN TO_DATE('30-Apr-17')
AND TO_DATE('27-May-17')
AND SH.ORDER_NO IS NOT NULL
AND OH.ORDER_NO = SH.ORDER_NO ---and oh.ORDER_NO in (5900319)
AND SH.SHIPMENT = SS.SHIPMENT
AND DL.SKU = SS.SKU
AND DL.DEPT = CL.DEPT
AND DL.CLASS = CL.CLASS
AND CL.FDC_CATEGORY = FC.CATEGORY
GROUP BY DL .DEPT,CL.PDC_CATEGORY;
|
|
|
|
|
|
|
|
|
|
| Re: Same query runs way better in 10g than in 12c [message #663020 is a reply to message #662985] |
Thu, 18 May 2017 08:46  |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
Not that it has any effect on your query speed or plan, but TO_DATE requires a format mask. Simply putting in a string to TO_DATE, you are lucky it worked for you, plain lucky.
SQL> select to_date('30-Apr-17') from dual
2 /
TO_DATE('3
----------
0030-04-17
|
|
|
|
|
|
| Re: Same query runs way better in 10g than in 12c [message #663154 is a reply to message #663051] |
Tue, 23 May 2017 14:35 |
 |
LNossov
Messages: 318
Registered: July 2011
Location: Germany
|
Senior Member |
|
|
Could you please provide the following information:
1. parameter settings in 10 and in 12
2. execution plans with predicates and if possible with runtime statistics (argument format of dbms_xplan.display_cursor => 'ADVANCED ALLSTATS LAST').
For generations of these statistics use hint gather_plan_statistics or parameter setting statistics_level=all (alter session set statistics_level=all)
Differences between two plans
P1. group by placement in 12 (VW_GBF_15). One can discard this feature with "_optimizer_group_by_placement"=false. But you have to clarify firstly P2.
P2. a very big difference in cardinality estimation of FTS on SHIPMENT (345K in 12, 5570 in 10). Check please your optimizer statistics.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
