Home » RDBMS Server » Performance Tuning » Please help me tune this sql (Oracle 10g)
Please help me tune this sql [message #489652] Tue, 18 January 2011 06:10 Go to next message
manoveg
Messages: 4
Registered: October 2008
Junior Member
Please help me reduce the execution time of below mentioned sql. Currently it is taking 13 hours. I have attached the execution plan .

select /*+ ordered */
DISTINCT pac.ROWID row_id, pac.assignment_id,
                   pac.assignment_action_id, ppe.person_id, ppe.full_name,
                   ppe.order_name, ppe.title,
                      SUBSTR (ppe.first_name, 1, 1)
                   || ' '
                   || SUBSTR (ppe.middle_names, 1, 1) initials,
                   ppe.last_name, paa.assignment_number,
                   NVL (ppo.NAME, pjob.NAME) position_title,
                   pay_au_soe_pkg.get_salary (paa.pay_basis_id, paa.assignment_id, rppa.date_earned) salary,
                   hlo.location_id, hlo.location_code location_code,
                   paa.internal_address_line,
                   ppe.expense_check_send_to_address, hou.business_group_id,
                   hoi.org_information3 registered_employer,
                   rppa.payroll_id payroll_id, pac.payroll_action_id,
                   ptp.time_period_id time_period_id,
                   ptp.period_num period_number,
                   ptp.start_date period_start_date,
                   ptp.end_date period_end_date,
                   NVL (rppa.pay_advice_date, ptp.pay_advice_date) pay_advice_date,
                   rppa.date_earned date_earned,
                   rpac.assignment_action_id run_assignment_action_id,
                   rpac.payroll_action_id run_payroll_action_id,
                   hoi.org_information12 abn, ppg.segment1, ppg.segment2,
                   ppg.segment3, ppg.segment4, ppg.segment5, ppg.segment6,
                   ppg.segment7, ppg.segment8, ppg.segment9, ppg.segment10,
                   ppg.segment11, ppg.segment12, ppg.segment13, ppg.segment14,
                   ppg.segment15, ppg.segment16, ppg.segment17, ppg.segment18,
                   ppg.segment19, ppg.segment20, ppg.segment21, ppg.segment22,
                   ppg.segment23, ppg.segment24, ppg.segment25, ppg.segment26,
                   ppg.segment27, ppg.segment28, ppg.segment29, ppg.segment30
from
       pay_payroll_actions ppa,
       pay_assignment_actions pac,
       per_all_assignments_f paa,
       per_people_f ppe,
       hr_locations hlo,
       per_positions ppo,
       per_jobs pjob,
       pay_people_groups ppg,
       pay_payroll_actions rppa,
       pay_assignment_actions rpac,
       per_time_periods ptp,
       pay_action_interlocks pai,
       hr_soft_coding_keyflex hsc,
       hr_all_organization_units hou,
       hr_organization_information hoi
where
       ppe.person_id = paa.person_id
and    paa.location_id = hlo.location_id(+)
and    paa.position_id = ppo.position_id(+)
and    paa.job_id = pjob.job_id(+)
and    paa.people_group_id = ppg.people_group_id(+)
and    pac.action_status = 'C'
and    paa.assignment_id = pac.assignment_id
and    pac.payroll_action_id = ppa.payroll_action_id
and    ppa.action_type IN ('U', 'P')
and    ppa.action_status = 'C'
and    pac.assignment_id = rpac.assignment_id
and    rpac.payroll_action_id = rppa.payroll_action_id
and    ppa.effective_date >= rppa.effective_date
and    ppa.payroll_id = rppa.payroll_id
and    rppa.payroll_id = ptp.payroll_id
and    rppa.date_earned BETWEEN ptp.start_date           AND ptp.end_date
and    rppa.date_earned BETWEEN paa.effective_start_date AND paa.effective_end_date
and    rppa.date_earned BETWEEN ppe.effective_start_date AND ppe.effective_end_date
and    pac.assignment_action_id = pai.locking_action_id
and    rpac.assignment_action_id = pai.locked_action_id
and    pai.ROWID =
           (SELECT SUBSTR (MAX( LPAD(paa_locked.action_sequence,15,0) || pai_locked.ROWID), -18) latest_act
            FROM   pay_assignment_actions paa_locked,
                   pay_action_interlocks pai_locked
            WHERE  pai_locked.locking_action_id = pac.assignment_action_id
            AND pai_locked.locked_action_id = paa_locked.assignment_action_id)
and    paa.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
and    TO_NUMBER (hsc.segment1) = hou.organization_id
and    hou.organization_id = hoi.organization_id
and    hoi.org_information_context = 'AU_LEGAL_EMPLOYER'




CM: added [code] tags, please do so yourself next time.

[Updated on: Tue, 18 January 2011 08:11] by Moderator

Report message to a moderator

Re: Please help me tune this sql [message #489675 is a reply to message #489652] Tue, 18 January 2011 08:10 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
1) Can you please read and follow the orafaq forum guide - especially the part on how to format your post.
2) Can you please use the following to generate explain plan and then post the results inline with [code] tags, it's easier to read and a lot of people won't download attachments.
SQL> explain plan for select * from dual;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3543395131

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

8 rows selected.

SQL> 

3) Lose the distinct, any query that selects a rowid gives distinct output by definition.
4) What does the plan look like without the ordered hint?
Re: Please help me tune this sql [message #489760 is a reply to message #489675] Tue, 18 January 2011 22:34 Go to previous messageGo to next message
manoveg
Messages: 4
Registered: October 2008
Junior Member
Sorry for not following formatting guidelines.

Explain plan with ordered hint is as follows :-

1	 
2	--------------------------------------------------------------------------------------------------------------------
3	| Id  | Operation                                 | Name                           | Rows  | Bytes |TempSpc| Cost  |
4	--------------------------------------------------------------------------------------------------------------------
5	|   0 | SELECT STATEMENT                          |                                |     1 |   504 |       | 11456 |
6	|   1 |  TABLE ACCESS BY INDEX ROWID              | HR_ORGANIZATION_INFORMATION    |     1 |    19 |       |     2 |
7	|   2 |   NESTED LOOPS                            |                                |     1 |   504 |       | 11451 |
8	|   3 |    NESTED LOOPS                           |                                |     1 |   485 |       | 11449 |
9	|   4 |     NESTED LOOPS                          |                                |     1 |   479 |       | 11448 |
10	|   5 |      NESTED LOOPS                         |                                |     1 |   473 |       | 11447 |
11	|   6 |       NESTED LOOPS                        |                                |     1 |   457 |       | 11446 |
12	|   7 |        NESTED LOOPS                       |                                |     1 |   428 |       | 11444 |
13	|   8 |         HASH JOIN                         |                                |     1 |   414 |       | 11441 |
14	|   9 |          HASH JOIN OUTER                  |                                |   378 |   144K|       | 11254 |
15	|  10 |           HASH JOIN OUTER                 |                                |   378 |   130K|       | 11250 |
16	|  11 |            NESTED LOOPS OUTER             |                                |   378 |   115K|       | 11208 |
17	|  12 |             HASH JOIN OUTER               |                                |   378 | 67284 |       | 11208 |
18	|  13 |              HASH JOIN                    |                                |   378 | 59346 |       | 11200 |
19	|  14 |               TABLE ACCESS FULL           | PER_ALL_PEOPLE_F               |   282 | 21714 |       |  1567 |
20	|  15 |               HASH JOIN                   |                                | 83265 |  6505K|  3272K|  9630 |
21	|  16 |                HASH JOIN                  |                                | 66944 |  2484K|       |  8281 |
22	|  17 |                 TABLE ACCESS FULL         | PAY_PAYROLL_ACTIONS            |  2464 | 39424 |       |   183 |
23	|  18 |                 TABLE ACCESS FULL         | PAY_ASSIGNMENT_ACTIONS         |  1550K|    32M|       |  8069 |
24	|  19 |                TABLE ACCESS FULL          | PER_ALL_ASSIGNMENTS_F          | 95662 |  3923K|       |  1219 |
25	|  20 |              VIEW                         | HR_LOCATIONS                   |     3 |    63 |       |     7 |
26	|  21 |               NESTED LOOPS                |                                |     3 |    90 |       |     7 |
27	|  22 |                TABLE ACCESS FULL          | HR_LOCATIONS_ALL               |     2 |    12 |       |     5 |
28	|  23 |                TABLE ACCESS BY INDEX ROWID| HR_LOCATIONS_ALL_TL            |     2 |    48 |       |     1 |
29	|  24 |                 INDEX UNIQUE SCAN         | HR_LOCATIONS_ALL_TL_PK         |     1 |       |       |       |
30	|  25 |             VIEW PUSHED PREDICATE         | PER_POSITIONS                  |     1 |   135 |       |       |
31	|  26 |              NESTED LOOPS                 |                                |     1 |   165 |       |     1 |
32	|  27 |               TABLE ACCESS BY INDEX ROWID | PER_ALL_POSITIONS              |     1 |    26 |       |     1 |
33	|  28 |                INDEX UNIQUE SCAN          | PER_POSITIONS_PK               |     1 |       |       |       |
34	|  29 |               TABLE ACCESS BY INDEX ROWID | HR_ALL_POSITIONS_F_TL          |     1 |   139 |       |       |
35	|  30 |                INDEX UNIQUE SCAN          | HR_ALL_POSITIONS_F_TL_PK       |     1 |       |       |       |
36	|  31 |            TABLE ACCESS FULL              | PER_JOBS                       | 10053 |   392K|       |    40 |
37	|  32 |           TABLE ACCESS FULL               | PAY_PEOPLE_GROUPS              |    31 |  1209 |       |     2 |
38	|  33 |          TABLE ACCESS FULL                | PAY_PAYROLL_ACTIONS            | 54216 |  1164K|       |   183 |
39	|  34 |         TABLE ACCESS BY INDEX ROWID       | PAY_ASSIGNMENT_ACTIONS         |     1 |    14 |       |     3 |
40	|  35 |          INDEX RANGE SCAN                 | PAY_ASSIGNMENT_ACTIONS_N4      |     1 |       |       |     2 |
41	|  36 |        TABLE ACCESS BY INDEX ROWID        | PER_TIME_PERIODS               |     2 |    58 |       |     2 |
42	|  37 |         INDEX RANGE SCAN                  | PER_TIME_PERIODS_N50           |     2 |       |       |     1 |
43	|  38 |       TABLE ACCESS BY USER ROWID          | PAY_ACTION_INTERLOCKS          |     1 |    16 |       |     1 |
44	|  39 |        SORT AGGREGATE                     |                                |     1 |    28 |       |       |
45	|  40 |         NESTED LOOPS                      |                                |     1 |    28 |       |     5 |
46	|  41 |          INDEX RANGE SCAN                 | PAY_ACTION_INTERLOCKS_PK       |     1 |    16 |       |     3 |
47	|  42 |          TABLE ACCESS BY INDEX ROWID      | PAY_ASSIGNMENT_ACTIONS         |     1 |    12 |       |     2 |
48	|  43 |           INDEX UNIQUE SCAN               | PAY_ASSIGNMENT_ACTIONS_PK      |     1 |       |       |     1 |
49	|  44 |      TABLE ACCESS BY INDEX ROWID          | HR_SOFT_CODING_KEYFLEX         |     1 |     6 |       |     1 |
50	|  45 |       INDEX UNIQUE SCAN                   | HR_SOFT_CODING_KEYFLEX_PK      |     1 |       |       |       |
51	|  46 |     TABLE ACCESS BY INDEX ROWID           | HR_ALL_ORGANIZATION_UNITS      |     1 |     6 |       |     1 |
52	|  47 |      INDEX UNIQUE SCAN                    | HR_ORGANIZATION_UNITS_PK       |     1 |       |       |       |
53	|  48 |    INDEX RANGE SCAN                       | HR_ORGANIZATION_INFORMATIO_FK2 |     1 |       |       |     1 |
54	--------------------------------------------------------------------------------------------------------------------
55	 
56	Note
57	-----
58	   - 'PLAN_TABLE' is old version
59	   - cpu costing is off (consider enabling it)


Explain plan without Ordered hint is as follows

1	 
2	--------------------------------------------------------------------------------------------------------------------

--
3	| Id  | Operation                                   | Name                           | Rows  | Bytes |TempSpc| Cost  

|
4	--------------------------------------------------------------------------------------------------------------------

--
5	|   0 | SELECT STATEMENT                            |                                |     1 |   504 |       |  3837 

|
6	|   1 |  NESTED LOOPS OUTER                         |                                |     1 |   504 |       |  3832 

|
7	|   2 |   NESTED LOOPS                              |                                |     1 |   483 |       |  3831 

|
8	|   3 |    NESTED LOOPS                             |                                |     1 |   467 |       |  3830 

|
9	|   4 |     NESTED LOOPS                            |                                |     1 |   453 |       |  3827 

|
10	|   5 |      HASH JOIN                              |                                |   267 |   112K|       |  3026 

|
11	|   6 |       TABLE ACCESS BY INDEX ROWID           | PER_TIME_PERIODS               |     2 |    58 |       |     2 

|
12	|   7 |        NESTED LOOPS                         |                                |    22 |  9130 |       |  2842 

|
13	|   8 |         NESTED LOOPS OUTER                  |                                |    10 |  3860 |       |  2822 

|
14	|   9 |          HASH JOIN OUTER                    |                                |    10 |  3460 |       |  2812 

|
15	|  10 |           MERGE JOIN                        |                                |    10 |  3070 |       |  2809 

|
16	|  11 |            SORT JOIN                        |                                |    31 |  8835 |       |  2348 

|
17	|  12 |             HASH JOIN                       |                                |    31 |  8835 |       |  2300 

|
18	|  13 |              TABLE ACCESS FULL              | PER_ALL_PEOPLE_F               |   282 | 21714 |       |  1567 

|
19	|  14 |              NESTED LOOPS OUTER             |                                |  6807 |  1382K|       |   732 

|
20	|  15 |               NESTED LOOPS                  |                                |  6807 |   485K|       |   732 

|
21	|  16 |                HASH JOIN                    |                                |     1 |    31 |       |    19 

|
22	|  17 |                 HASH JOIN                   |                                |   107 |  2675 |       |    16 

|
23	|  18 |                  TABLE ACCESS BY INDEX ROWID| HR_ORGANIZATION_INFORMATION    |   107 |  2033 |       |     7 

|
24	|  19 |                   INDEX RANGE SCAN          | HR_ORGANIZATION_INFORMATIO_FK1 |   107 |       |       |     1 

|
25	|  20 |                  TABLE ACCESS FULL          | HR_ALL_ORGANIZATION_UNITS      |  1699 | 10194 |       |     8 

|
26	|  21 |                 TABLE ACCESS FULL           | HR_SOFT_CODING_KEYFLEX         |    18 |   108 |       |     2 

|
27	|  22 |                TABLE ACCESS BY INDEX ROWID  | PER_ALL_ASSIGNMENTS_F          |  5979 |   245K|       |   713 

|
28	|  23 |                 INDEX RANGE SCAN            | PER_ASSIGNMENTS_F_FK17         |  5979 |       |       |    10 

|
29	|  24 |               VIEW PUSHED PREDICATE         | PER_POSITIONS                  |     1 |   135 |       |       

|
30	|  25 |                NESTED LOOPS                 |                                |     1 |   165 |       |     1 

|
31	|  26 |                 TABLE ACCESS BY INDEX ROWID | PER_ALL_POSITIONS              |     1 |    26 |       |     1 

|
32	|  27 |                  INDEX UNIQUE SCAN          | PER_POSITIONS_PK               |     1 |       |       |       

|
33	|  28 |                 TABLE ACCESS BY INDEX ROWID | HR_ALL_POSITIONS_F_TL          |     1 |   139 |       |       

|
34	|  29 |                  INDEX UNIQUE SCAN          | HR_ALL_POSITIONS_F_TL_PK       |     1 |       |       |       

|
35	|  30 |            FILTER                           |                                |       |       |       |       

|
36	|  31 |             SORT JOIN                       |                                | 54216 |  1164K|  3848K|   462 

|
37	|  32 |              TABLE ACCESS FULL              | PAY_PAYROLL_ACTIONS            | 54216 |  1164K|       |   183 

|
38	|  33 |           TABLE ACCESS FULL                 | PAY_PEOPLE_GROUPS              |    31 |  1209 |       |     2 

|
39	|  34 |          TABLE ACCESS BY INDEX ROWID        | PER_JOBS                       |     1 |    40 |       |     1 

|
40	|  35 |           INDEX UNIQUE SCAN                 | PER_JOBS_PK                    |     1 |       |       |       

|
41	|  36 |         INDEX RANGE SCAN                    | PER_TIME_PERIODS_N50           |     2 |       |       |     1 

|
42	|  37 |       TABLE ACCESS FULL                     | PAY_PAYROLL_ACTIONS            |  2464 | 39424 |       |   183 

|
43	|  38 |      TABLE ACCESS BY INDEX ROWID            | PAY_ASSIGNMENT_ACTIONS         |     1 |    22 |       |     3 

|
44	|  39 |       INDEX RANGE SCAN                      | PAY_ASSIGNMENT_ACTIONS_N4      |     1 |       |       |     2 

|
45	|  40 |     TABLE ACCESS BY INDEX ROWID             | PAY_ASSIGNMENT_ACTIONS         |     1 |    14 |       |     3 

|
46	|  41 |      INDEX RANGE SCAN                       | PAY_ASSIGNMENT_ACTIONS_N4      |     1 |       |       |     2 

|
47	|  42 |    TABLE ACCESS BY USER ROWID               | PAY_ACTION_INTERLOCKS          |     1 |    16 |       |     1 

|
48	|  43 |     SORT AGGREGATE                          |                                |     1 |    28 |       |       

|
49	|  44 |      NESTED LOOPS                           |                                |     1 |    28 |       |     5 

|
50	|  45 |       INDEX RANGE SCAN                      | PAY_ACTION_INTERLOCKS_PK       |     1 |    16 |       |     3 

|
51	|  46 |       TABLE ACCESS BY INDEX ROWID           | PAY_ASSIGNMENT_ACTIONS         |     1 |    12 |       |     2 

|
52	|  47 |        INDEX UNIQUE SCAN                    | PAY_ASSIGNMENT_ACTIONS_PK      |     1 |       |       |     1 

|
53	|  48 |   VIEW PUSHED PREDICATE                     | HR_LOCATIONS                   |     1 |    21 |       |     1 

|
54	|  49 |    NESTED LOOPS                             |                                |     2 |    60 |       |     2 

|
55	|  50 |     TABLE ACCESS BY INDEX ROWID             | HR_LOCATIONS_ALL               |     1 |     6 |       |     1 

|
56	|  51 |      INDEX UNIQUE SCAN                      | HR_LOCATIONS_PK                |     1 |       |       |       

|
57	|  52 |     TABLE ACCESS BY INDEX ROWID             | HR_LOCATIONS_ALL_TL            |     2 |    48 |       |     1 

|
58	|  53 |      INDEX UNIQUE SCAN                      | HR_LOCATIONS_ALL_TL_PK         |     1 |       |       |       

|
59	--------------------------------------------------------------------------------------------------------------------

--
60	 
61	Note
62	-----
63	   - 'PLAN_TABLE' is old version
64	   - cpu costing is off (consider enabling it)


Re: Please help me tune this sql [message #489763 is a reply to message #489760] Tue, 18 January 2011 22:49 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>pay_payroll_actions ppa,
above exists in FROM clause, but contributes NO data to the SELECT clause.
also doing FTS against PPA; which is 1 of the larger tables
With proper indexing and subordinating into WHERE clause might improve performance
Re: Please help me tune this sql [message #489781 is a reply to message #489652] Wed, 19 January 2011 01:59 Go to previous messageGo to next message
michael_bialik
Messages: 621
Registered: July 2006
Senior Member
63	   - 'PLAN_TABLE' is old version


It means that you are using OLD version of PLAN_TABLE, so you are missing o LOT of useful information in EXPLAIN.

Drop your plan table and create a correct one.

[Updated on: Wed, 19 January 2011 01:59]

Report message to a moderator

Re: Please help me tune this sql [message #489800 is a reply to message #489763] Wed, 19 January 2011 04:01 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
BlackSwan wrote on Wed, 19 January 2011 04:49
>pay_payroll_actions ppa,
above exists in FROM clause, but contributes NO data to the SELECT clause.


One of these days you're actually going to explain why removing linking tables from the main part of the query is likely to improve anything.
Re: Please help me tune this sql [message #489907 is a reply to message #489800] Wed, 19 January 2011 21:31 Go to previous messageGo to next message
manoveg
Messages: 4
Registered: October 2008
Junior Member
Hi ,

I have removed the ordered hint from the sql and have observed drastic improvement in execution time.
I am not sure whether query will behave same in production instance . Right Now I have tested in development instance.
Please advice.
Re: Please help me tune this sql [message #489931 is a reply to message #489907] Thu, 20 January 2011 02:13 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
The only way to answer that is to test it.

Questions I'd be asking if I were you are why that hint was there in the first place and are development and production mirrors or not. If they are not, you're going to struggle to develop tuning solutions which are 100% reliable.
Re: Please help me tune this sql [message #540264 is a reply to message #489652] Sat, 21 January 2012 06:05 Go to previous messageGo to next message
sivanekkalapudi
Messages: 1
Registered: January 2012
Junior Member

You can try by putting following hints.

/*+ INDEX_JOIN(PAI) */
/*+ NO_CPU_COSTING */
/*+ USE_NL(HOI,HOU,HSC,PAI,PTP,RPAC,RPPA,PPG,PJOB,PPO) ORDERED */
/*+ NO_USE_NL(HOI) */
/*+ NO_USE_NL(HOI,HOU,HSC) */

Try it and let me know if you need any asssitance
Re: Please help me tune this sql [message #540284 is a reply to message #540264] Sat, 21 January 2012 09:18 Go to previous message
Michel Cadot
Messages: 67948
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why? Anyone that uses hints without knowing why is just stupid.
So don't let us stupid, explain you say to use them and especially these ones.

Regards
Michel
Previous Topic: Problem Index with IN Statement (merged)
Next Topic: CBO Query re-write
Goto Forum:
  


Current Time: Wed Sep 22 21:24:37 CDT 2021