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  |
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  |
cookiemonster
Messages: 13962
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 |
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 #489800 is a reply to message #489763] |
Wed, 19 January 2011 04:01 |
cookiemonster
Messages: 13962
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 |
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 |
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 |
 |
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 |
 |
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
|
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
|
|
|
|
Goto Forum:
Current Time: Fri Jan 10 15:50:29 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
