Home » RDBMS Server » Performance Tuning » Same query runs way better in 10g than in 12c (Oracle 10g, 12c)
Same query runs way better in 10g than in 12c [message #662985] Thu, 18 May 2017 06:12 Go to next message
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 #662995 is a reply to message #662985] Thu, 18 May 2017 06:55 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
Same posted at OTN: https://community.oracle.com/thread/4047013
Re: Same query runs way better in 10g than in 12c [message #662996 is a reply to message #662995] Thu, 18 May 2017 06:59 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
why not simply put a hint on the select of

/*+ INDEX (OH PK_ORDHEAD) */

and see what happens instead of crippling the database. especially since you said the other queries were faster

[Updated on: Thu, 18 May 2017 07:00]

Report message to a moderator

Re: Same query runs way better in 10g than in 12c [message #663013 is a reply to message #662996] Thu, 18 May 2017 07:43 Go to previous messageGo to next message
Asfakul
Messages: 43
Registered: July 2014
Member
After putting the hint , it sees improvement. But not as good as in 10g. Thanks.
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 Go to previous messageGo to next message
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 #663051 is a reply to message #662985] Fri, 19 May 2017 16:05 Go to previous messageGo to next message
mikek
Messages: 29
Registered: January 2017
Junior Member
Some suggestions. When diagnosing such an issue I follow a routine.

1. Check the Database Stats fro the Tables and their Indexes.

2. Make sure that the there are indexes to support the Foreign Key Relationships in the Query
for the detail table of the master-detail join relationship. Based on what has been posted so
far this is my suspicion.

3. Gather information.
- Table, Index, Constraint DDL for the tables in the query.
- Complete Explain Plans. You posted most of the plan, but not all of it. The "Predicate Information"
is very helpful in reviewing a execution plan.
- Some times a physical model of the query can be helpful.

4. Review gathered information and plan next steps.


I would focus on the current 12c environment because reverting back to 10g may no longer be a option.
The explain plans are different because the Optimizer are different versions. Please Post the
the DDL, some sample data, and the full explain plan.
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 Go to previous message
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.
Previous Topic: cannot see a-rows while querying dbms_xplan.display_cursor
Next Topic: PLS-00201: identifier 'DBMS_AWR.ENABLE_AWR' must be declared
Goto Forum:
  


Current Time: Fri Mar 29 01:46:55 CDT 2024