Home » RDBMS Server » Performance Tuning » Help Tuning slow query (Oracle 12c)
Help Tuning slow query [message #662051] Sat, 15 April 2017 09:16 Go to next message
James_s
Messages: 6
Registered: April 2017
Junior Member
Please, I need your help to tune this query as it is very slow. My task is to improve the performance. At the moment, it is taking about 30 minutes to run. I need help interpreting the explain plan. Many thanks in advence
--------
SELECT brsf_JOB_FAMILY_PATHWAY_LEVEL(app.appointment_code)               AS FAM  
      ,DECODE('2', '1', org.team_area  
                  ,NULL)                                                 AS AREA  
      ,org.body_position                                                 AS body_pos  
      ,org.full_name  
      ,peo.surname || ', ' || peo.initials                               AS NAME  
      ,peo.payroll_number  
      ,DECODE(BRSF_SES_IND(peo.person_code),   
              NULL, BRSF_SES_IND(peo.person_code,TO_DATE('01-AUG-2016', 'DD-MON-YYYY'))  
             ,BRSF_SES_IND(peo.person_code, TO_DATE('01-AUG-2016', 'DD-MON-YYYY')), BRSF_SES_IND(peo.person_code)  
             ,BRSF_SES_IND(peo.person_code, TO_DATE('01-AUG-2016', 'DD-MON-YYYY')) ||  
                                            ' at revision date, ' ||  
                                            BRSF_SES_IND(peo.person_code) || ' now') AS SES  
      ,app.pos_post_number                                               AS post_number  
      ,app.appointment_code  
      ,sev.start_date                                                    AS salev_date  
      ,org.team_area  
      ,BRSF_APPT_GRADE(app.appointment_code,      sev.start_date)        AS grade_name  
      ,BRSF_APPT_HOURS(app.appointment_code,      sev.start_date)        AS hours_per_week  
      ,BRSF_APPT_SALARY(app.appointment_code,     sev.start_date)        AS salary  
      ,BRSF_APPT_FTE_SALARY(app.appointment_code, sev.start_date)        AS fte_salary  
      ,DECODE(BRSF_APPT_SPINAL_IND(app.appointment_code, sev.start_date)  
             ,'N', 'NON-SPINAL'  
             ,'Y', 'Spinal')                                             AS spinal_ind  
      ,gra.psp_category || gra.psp_group                                 AS staffcat  
  FROM hes_people              peo  
      ,hes_periods_of_service  POS  
      ,hes_appointments        app  
      ,brs_post_org_histories  poh  
      ,hes_organisation_units  org  
      ,brsv_salary_events      sev  
      ,hes_app_grade_histories agh  
      ,hes_grades              gra  
 WHERE peo.person_code        = pos.per_person_code  
   AND pos.period_of_ser_code = app.pes_period_of_ser_code  
   AND app.pos_post_number    = poh.post_number  
   AND app.appointment_code   = agh.app_appointment_code  
   AND sev.appointment_code   = app.appointment_code  
   AND poh.organisation_code  = org.organisation_code  
   AND poh.start_date         = (SELECT MAX(poh2.start_date)  
                                   FROM brs_post_org_histories poh2  
                                  WHERE poh2.post_number = poh.post_number  
                                    AND poh2.start_date <= sev.start_date)  
   AND agh.gra_grade_code     = gra.grade_code  
   AND TO_DATE('01-AUG-2016', 'DD-MON-YYYY') BETWEEN pos.start_date  
                                                 AND pos.end_date  
   AND sev.start_date BETWEEN gra.start_date  
                          AND gra.end_date  
   AND sev.start_date BETWEEN agh.effective_start_date  
                          AND agh.effective_end_date  
   AND sev.start_date BETWEEN app.start_date  
                          AND app.end_date  
   AND app.end_date          >= TO_DATE('01-AUG-2016', 'DD-MON-YYYY')  
   AND app.pos_post_number   != 'Z1'  
   AND (  
        sev.start_date >= TO_DATE('01-AUG-2016', 'DD-MON-YYYY')  
        OR  
        sev.start_date = (SELECT MAX(sev2.start_date)  
                            FROM brsv_salary_events sev2  
                           WHERE sev2.appointment_code = sev.appointment_code  
                             AND sev2.start_date < TO_DATE('01-AUG-2016', 'DD-MON-YYYY'))  
       )  
   AND (  
        (  
         'Y' = 'Y'  
         AND  
         BRSF_APPT_SPINAL_IND(app.appointment_code, sev.start_date) = 'Y'  
        )  
        OR  
        (  
         'N' = 'Y'  
         AND  
         BRSF_APPT_SPINAL_IND(app.appointment_code, sev.start_date) = 'N'  
        )  
       )  
   AND (  
        org.team_area IN ('A')  
        OR 'null' IN ('A')  
       )  
 ORDER BY 1 ASC  
         ,2 ASC  
         ,3 ASC  
         ,4 ASC  
         ,5 ASC  
         ,6 ASC  
         ,7 ASC  
         ,8 ASC  
         ,9 ASC  
         ,10 DESC;

Explain plan


 
---------------------------------------------------------------------------------------------------------------------------  
| Id  | Operation                                                  | Name                        | Rows  | Bytes | Cost  |  
---------------------------------------------------------------------------------------------------------------------------  
|  0 | SELECT STATEMENT                                          |                              |    1 |  185 | 14207 |  
|  1 |  SORT ORDER BY                                            |                              |    1 |  185 | 14207 |  
|  2 |  FILTER                                                  |                              |      |      |      |  
|  3 |    NESTED LOOPS                                            |                              |    1 |  185 | 14203 |  
|  4 |    NESTED LOOPS                                          |                              |    1 |  185 | 14203 |  
|  5 |      HASH JOIN                                            |                              |    1 |  166 | 14202 |  
|  6 |      NESTED LOOPS                                        |                              |    1 |  166 | 14202 |  
|  7 |        STATISTICS COLLECTOR                                |                              |      |      |      |  
|  8 |        HASH JOIN                                          |                              |    1 |  145 | 14199 |  
|  9 |          NESTED LOOPS                                      |                              |    1 |  145 | 14199 |  
|  10 |          STATISTICS COLLECTOR                            |                              |      |      |      |  
|  11 |            NESTED LOOPS                                    |                              |    1 |  128 | 14197 |  
|  12 |            NESTED LOOPS                                  |                              |    1 |    87 | 14196 |  
|  13 |              NESTED LOOPS                                  |                              |    1 |    70 | 14193 |  
|  14 |              HASH JOIN                                    |                              |    1 |    48 | 14190 |  
|  15 |                TABLE ACCESS FULL                          | HES_APPOINTMENTS            |  8056 |  204K|  603 |  
|  16 |                VIEW                                        | BRSV_SALARY_EVENTS          |  424K|  9119K| 13581 |  
|  17 |                SORT UNIQUE                                |                              |  424K|    10M| 13581 |  
|  18 |                  UNION-ALL                                |                              |      |      |      |  
|  19 |                  HASH JOIN                                |                              |  5502 |  231K|  896 |  
|  20 |                    NESTED LOOPS                            |                              |  5502 |  231K|  896 |  
|  21 |                    NESTED LOOPS                          |                              |      |      |      |  
|  22 |                      STATISTICS COLLECTOR                  |                              |      |      |      |  
|  23 |                      HASH JOIN                            |                              |  5502 |  188K|  335 |  
|  24 |                        NESTED LOOPS                        |                              |  5502 |  188K|  335 |  
|  25 |                        STATISTICS COLLECTOR              |                              |      |      |      |  
|  26 |                          TABLE ACCESS FULL                | HES_SPINAL_INCREMENT_MAPS    |  1443 | 17316 |    7 |  
|  27 |                        TABLE ACCESS BY INDEX ROWID BATCHED| HES_APP_GRADE_INCR_HISTORIES |    4 |    92 |  326 |  
|  28 |                          BITMAP CONVERSION TO ROWIDS      |                              |      |      |      |  
|  29 |                          BITMAP AND                      |                              |      |      |      |  
|  30 |                            BITMAP CONVERSION FROM ROWIDS  |                              |      |      |      |  
|  31 |                            SORT ORDER BY                  |                              |      |      |      |  
|  32 |                              INDEX RANGE SCAN              | BRSI_AGIH_PRIME              |  361 |      |    9 |  
|  33 |                            BITMAP CONVERSION FROM ROWIDS  |                              |      |      |      |  
|  34 |                            INDEX RANGE SCAN              | BRSI_AGIH_GRADECODE          |  361 |      |    71 |  
|  35 |                        TABLE ACCESS FULL                  | HES_APP_GRADE_INCR_HISTORIES | 91342 |  2051K|  326 |  
|  36 |                      INDEX UNIQUE SCAN                    | HES_AGH_PK                  |      |      |      |  
|  37 |                    TABLE ACCESS BY INDEX ROWID            | HES_APP_GRADE_HISTORIES      |    1 |    8 |  560 |  
|  38 |                    TABLE ACCESS FULL                      | HES_APP_GRADE_HISTORIES      |  104K|  812K|  560 |  
|  39 |                  HASH JOIN                                |                              | 28942 |  2148K|  920 |  
|  40 |                    TABLE ACCESS FULL                      | HES_APP_GRADE_HISTORIES      |  104K|  812K|  560 |  
|  41 |                    HASH JOIN                              |                              | 28942 |  1921K|  358 |  
|  42 |                    INDEX FAST FULL SCAN                  | BRSI_PRH_SPINE_POINT_START  |  7009 |  157K|    18 |  
|  43 |                    HASH JOIN                              |                              | 87637 |  3851K|  335 |  
|  44 |                      TABLE ACCESS FULL                    | HES_SPINAL_INCREMENT_MAPS    |  1443 | 31746 |    7 |  
|  45 |                      TABLE ACCESS FULL                    | HES_APP_GRADE_INCR_HISTORIES | 91342 |  2051K|  326 |  
|  46 |                  HASH JOIN                                |                              |  121K|  2248K|  747 |  
|  47 |                    NESTED LOOPS                            |                              |  121K|  2248K|  747 |  
|  48 |                    STATISTICS COLLECTOR                  |                              |      |      |      |  
|  49 |                      TABLE ACCESS FULL                    | HES_APP_GRADE_HISTORIES      |  104K|  812K|  560 |  
|  50 |                    INDEX RANGE SCAN                      | BRSI_AGHW_PRIME              |    1 |    11 |  184 |  
|  51 |                    INDEX FAST FULL SCAN                    | BRSI_AGHW_PRIME              |  121K|  1301K|  184 |  
|  52 |                  HASH JOIN                                |                              | 91342 |  1694K|  691 |  
|  53 |                    TABLE ACCESS FULL                      | HES_APP_GRADE_HISTORIES      |  104K|  812K|  560 |  
|  54 |                    INDEX FAST FULL SCAN                    | BRSI_AGIH_GHC_START          | 91342 |  981K|  128 |  
|  55 |                  HASH JOIN                                |                              |  127K|  2372K|  731 |  
|  56 |                    NESTED LOOPS                            |                              |  127K|  2372K|  731 |  
|  57 |                    STATISTICS COLLECTOR                  |                              |      |      |      |  
|  58 |                      TABLE ACCESS FULL                    | HES_APP_GRADE_HISTORIES      |  104K|  812K|  560 |  
|  59 |                    INDEX RANGE SCAN                      | BRSI_SALHISTS_PRIME          |    1 |    11 |  168 |  
|  60 |                    INDEX FAST FULL SCAN                    | BRSI_SALHISTS_PRIME          |  127K|  1373K|  168 |  
|  61 |                  HASH JOIN                                |                              | 49691 |  1698K|  566 |  
|  62 |                    NESTED LOOPS                            |                              | 49691 |  1698K|  566 |  
|  63 |                    NESTED LOOPS                          |                              |      |      |      |  
|  64 |                      STATISTICS COLLECTOR                  |                              |      |      |      |  
|  65 |                      HASH JOIN                            |                              |  587 |  8218 |    4 |  
|  66 |                        INDEX FULL SCAN                    | BRSI_PSHH_UI                |    83 |  747 |    1 |  
|  67 |                        TABLE ACCESS FULL                  | HES_GRADES                  |  330 |  1650 |    3 |  
|  68 |                      INDEX RANGE SCAN                      | BRSI_AGH_GRADECODE          |      |      |      |  
|  69 |                    TABLE ACCESS BY INDEX ROWID            | HES_APP_GRADE_HISTORIES      |    85 |  1785 |  560 |  
|  70 |                    TABLE ACCESS FULL                      | HES_APP_GRADE_HISTORIES      |  104K|  2133K|  560 |  
|  71 |              TABLE ACCESS BY INDEX ROWID                  | HES_PERIODS_OF_SERVICE      |    1 |    22 |    2 |  
|  72 |                INDEX UNIQUE SCAN                          | HES_PES_PRIME                |    1 |      |    1 |  
|  73 |              TABLE ACCESS BY INDEX ROWID BATCHED          | BRS_POST_ORG_HISTORIES      |    1 |    17 |    3 |  
|  74 |              INDEX RANGE SCAN                            | BRSI_POH_POST_START          |    1 |      |    2 |  
|  75 |                SORT AGGREGATE                              |                              |    1 |    13 |      |  
|  76 |                FIRST ROW                                  |                              |    1 |    13 |    3 |  
|  77 |                  INDEX RANGE SCAN (MIN/MAX)                | BRSI_POH_POST_START          |    1 |    13 |    3 |  
|  78 |            TABLE ACCESS BY INDEX ROWID                    | HES_ORGANISATION_UNITS      |    1 |    41 |    1 |  
|  79 |              INDEX UNIQUE SCAN                            | HES_ORG_PRIME                |    1 |      |    0 |  
|  80 |          TABLE ACCESS BY INDEX ROWID                      | HES_PEOPLE                  |    1 |    17 |    2 |  
|  81 |            INDEX UNIQUE SCAN                              | HES_PER_PRIME                |    1 |      |    1 |  
|  82 |          TABLE ACCESS FULL                                | HES_PEOPLE                  |    1 |    17 |    2 |  
|  83 |        TABLE ACCESS BY INDEX ROWID BATCHED                | HES_APP_GRADE_HISTORIES      |    1 |    21 |    3 |  
|  84 |        INDEX RANGE SCAN                                  | BRSI_AGH_APP_START          |    1 |      |    2 |  
|  85 |      TABLE ACCESS FULL                                    | HES_APP_GRADE_HISTORIES      |    1 |    21 |    3 |  
|  86 |      INDEX UNIQUE SCAN                                    | HES_GRA_PRIME                |    1 |      |    0 |  
|  87 |    TABLE ACCESS BY INDEX ROWID                            | HES_GRADES                  |    1 |    19 |    1 |  
|  88 |    SORT AGGREGATE                                          |                              |    1 |    22 |      |  
|  89 |    VIEW                                                  | BRSV_SALARY_EVENTS          |  8557 |  183K|  736 |  
|  90 |      SORT UNIQUE                                          |                              |  8557 |  292K|  736 |  
|  91 |      UNION-ALL                                            |                              |      |      |      |  
|  92 |        NESTED LOOPS                                        |                              |    3 |    57 |    8 |  
|  93 |        TABLE ACCESS BY INDEX ROWID BATCHED                | HES_APP_GRADE_HISTORIES      |    2 |    16 |    4 |  
|  94 |          INDEX RANGE SCAN                                  | BRSI_AGH_APP_START          |    2 |      |    3 |  
|  95 |        INDEX RANGE SCAN                                  | BRSI_AGHW_PRIME              |    1 |    11 |    2 |  
|  96 |        NESTED LOOPS                                        |                              |    5 |    95 |    8 |  
|  97 |        TABLE ACCESS BY INDEX ROWID BATCHED                | HES_APP_GRADE_HISTORIES      |    2 |    16 |    4 |  
|  98 |          INDEX RANGE SCAN                                  | BRSI_AGH_APP_START          |    2 |      |    3 |  
|  99 |        INDEX RANGE SCAN                                  | BRSI_AGIH_GHC_START          |    2 |    22 |    2 |  
| 100 |        NESTED LOOPS                                        |                              |    3 |    57 |    8 |  
| 101 |        TABLE ACCESS BY INDEX ROWID BATCHED                | HES_APP_GRADE_HISTORIES      |    2 |    16 |    4 |  
| 102 |          INDEX RANGE SCAN                                  | BRSI_AGH_APP_START          |    2 |      |    3 |  
| 103 |        INDEX RANGE SCAN                                  | BRSI_SALHISTS_PRIME          |    1 |    11 |    2 |  
| 104 |        HASH JOIN                                          |                              |  8545 |  292K|    10 |  
| 105 |        HASH JOIN                                          |                              |    99 |  1386 |    4 |  
| 106 |          INDEX SKIP SCAN                                  | BRSI_PSHH_UI                |    83 |  747 |    1 |  
| 107 |          TABLE ACCESS FULL                                | HES_GRADES                  |  330 |  1650 |    3 |  
| 108 |        TABLE ACCESS BY INDEX ROWID BATCHED                | HES_APP_GRADE_HISTORIES      |  104K|  2148K|    4 |  
| 109 |          INDEX RANGE SCAN                                  | BRSI_AGH_APP_START          |    2 |      |    3 |  
| 110 |        HASH JOIN                                          |                              |    1 |    30 |  696 |  
| 111 |        TABLE ACCESS BY INDEX ROWID BATCHED                | HES_APP_GRADE_HISTORIES      |    2 |    16 |    4 |  
| 112 |          INDEX RANGE SCAN                                  | BRSI_AGH_APP_START          |    2 |      |    3 |  
| 113 |        VIEW                                              | VW_JF_SET$8DB8F874          | 33940 |  729K|  692 |  
| 114 |          SORT UNIQUE                                      |                              | 33940 |  2076K|  692 |  
| 115 |          UNION-ALL                                        |                              |      |      |      |  
| 116 |            HASH JOIN                                      |                              |  5501 |  188K|  335 |  
| 117 |            TABLE ACCESS FULL                              | HES_SPINAL_INCREMENT_MAPS    |  1443 | 17316 |    7 |  
| 118 |            TABLE ACCESS FULL                              | HES_APP_GRADE_INCR_HISTORIES | 88382 |  1985K|  327 |  
| 119 |            HASH JOIN                                      |                              | 28439 |  1888K|  357 |  
| 120 |            HASH JOIN                                      |                              |  4913 |  215K|    26 |  
| 121 |              TABLE ACCESS FULL                            | HES_SPINAL_INCREMENT_MAPS    |  1443 | 31746 |    7 |  
| 122 |              INDEX FAST FULL SCAN                          | BRSI_PRH_SPINE_POINT_START  |  6888 |  154K|    18 |  
| 123 |            TABLE ACCESS FULL                              | HES_APP_GRADE_INCR_HISTORIES | 88382 |  1985K|  327 |  
---------------------------------------------------------------------------------------------------------------------------  
  
Note  
-----  
  - 'PLAN_TABLE' is old version  
*BlackSwan added code tags {} Please do so yourself in the future.
How to use {code} tags and make your code easier to read
  • Attachment: qry1.sql
    (Size: 4.26KB, Downloaded 56 times)

[Updated on: Sat, 15 April 2017 11:07] by Moderator

Report message to a moderator

Re: Help Tuning slow query [message #662053 is a reply to message #662051] Sat, 15 April 2017 11:10 Go to previous messageGo to next message
BlackSwan
Messages: 25716
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide

How many of the full table scans can be eliminated by adding new indexes?
Re: Help Tuning slow query [message #662055 is a reply to message #662051] Sat, 15 April 2017 11:23 Go to previous messageGo to next message
John Watson
Messages: 7150
Registered: January 2010
Location: Global Village
Senior Member
You are getting an adaptive plan, so there is no reason to assume that the plan shown is the plan that will be used.
You need to drop your plan_table and re-create it as the correct version (or just drop it, and use the supplied plan_table public synonym) and then explain your statement with the format=>'adaptive' argument to see both plans that are being considered. Then you need to run the statement with the gather_plan_Statistics hint, and extract the actual plan used from the library cache.
Re: Help Tuning slow query [message #662060 is a reply to message #662051] Sun, 16 April 2017 18:52 Go to previous messageGo to next message
mikek
Messages: 29
Registered: January 2017
Junior Member
Information is needed to proceed with Analysis. Other then the names of the Database Objects this Query appears
as part of a larger query with the very same Code and with Performance issues. My guess is that this is a
Application/DB from a third party vendor. If so do you have any support agreement with them? If so it maybe
their issue to resolve.

1. Are the Database Stats current for the DB Configuration?

2. DDL with Index listing for the Tables accessed while performing the SQL

HES_PEOPLE
HES_PERIODS_OF_SERVICE
HES_APPOINTMENTS
BRS_POST_ORG_HISTORIES 
HES_ORGANISATION_UNITS
BRSV_SALARY_EVENTS 
HES_APP_GRADE_HISTORIES
HES_GRADES
HES_ SPINAL_INCREMENT_MAPS 
HES_ APP_GRADE_INCR_HISTORIES

The table/View? BRSV_SALARY_EVENTS appears to be fairly large and has higher cost associated to it.
Please give more information about this item. The Query is primarily accessing 2 columns "appointment_code
and start_date" which are being used extensively in the Function Calls.


3. Function Calls (The Query uses several Function Calls.)

BRSF_SES_IND
BRSF_APPT_SPINAL_IND
BRSF_JOB_FAMILY_PATHWAY_LEVEL
BRSF_APPT_GRADE
BRSF_APPT_HOURS
BRSF_APPT_SALARY
BRSF_APPT_FTE_SALARY

Would like the DDL for Analysis. Particularly interested in the Functions that access the following
tables. They show up frequently in the Explain Plan with Higher Cost Associated with them.

HES_APP_GRADE_HISTORIES
HES_SPINAL_INCREMENT_MAPS 
HES_APP_GRADE_INCR_HISTORIES
Re: Help Tuning slow query [message #662091 is a reply to message #662051] Mon, 17 April 2017 16:59 Go to previous messageGo to next message
mikek
Messages: 29
Registered: January 2017
Junior Member
Hi,

With some information from a different Forum the issue is with the "BRSV_SALARY_EVENTS" View
that is very complex View with many Queries that are Combined together using a Union Clause.
The processing of this view is causing the performance issues in your query. More information
and some suggested resolutions can be found at the following site. The post is a 2 pages long
and the resolution information is on the 2nd Page.

https://community.oracle.com/thread/4036713

Have a Great Day, MK
Re: Help Tuning slow query [message #662098 is a reply to message #662091] Tue, 18 April 2017 00:33 Go to previous messageGo to next message
Michel Cadot
Messages: 65201
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Thanks for the feedback.

Re: Help Tuning slow query [message #662099 is a reply to message #662091] Tue, 18 April 2017 01:16 Go to previous messageGo to next message
James_s
Messages: 6
Registered: April 2017
Junior Member
Thanks, will follow your advice
Re: Help Tuning slow query [message #662100 is a reply to message #662053] Tue, 18 April 2017 01:17 Go to previous message
James_s
Messages: 6
Registered: April 2017
Junior Member
Thanks
Previous Topic: Number of values in Not in list
Next Topic: Same query runs way better in 10g than in 12c
Goto Forum:
  


Current Time: Tue Nov 21 08:05:36 CST 2017

Total time taken to generate the page: 0.01894 seconds