Home » RDBMS Server » Performance Tuning » Tune long running query (Oracle 10.2.0.4.0, SunOS)
|
|
|
|
| Re: Tune long running query [message #534300 is a reply to message #534296] |
Mon, 05 December 2011 02:55   |
Database admin
Messages: 365
Registered: September 2006
Location: india
|
Senior Member |
 
|
|
Michel,
Thanks for your advice.
Quote:
SELECT NVL(SUM (CLAIM_DEALER_TOT_AMT_LOC), 0), NVL(SUM (CLAIM_DEALER_TOT_AMT_REG), 0), NVL(SUM (CLAIM_DEALER_TOT_AMT_GLOB), 0), NVL(SUM (FLAG_COUNT), 0), COUNT(CLAIM_NO) FROM(SELECT CV.CLAIM_DEALER_TOT_AMT_LOC, CV.CLAIM_DEALER_TOT_AMT_REG, CV.CLAIM_DEALER_TOT_AMT_GLOB, dbo.f_get_user_trans_flags_cnt (CV.claim_no, 'ZZPKMK') "FLAG_COUNT", CV.CLAIM_NO, CV.active_version_indicator, CV.repairing_business_unit_id, CV.process_status, CV.process_datetime, CV.repairing_service_agent_id_ic, ch.claim_type, CV.change_by_toolkit_ind, CH.LABOR_OPERATION_CD, CH.SERIALIZED_PART_ID, CH.VIN, CV.JOB_CARD_NO, CV.REPAIR_COMPLETION_DATE, CH.JOB_CARD_OPEN_DATE, CH.TECHNICIAN_NO, CH.CLAIM_SUBMISSION_DATE, CH.PARTS_RETURN_PROJECT_ID, CH.PARTS_RETURN_CREATE_DATE, CH.CAUSAL_PART_NO, CV.REPAIR_GROUP, CV.WEEK_ENDING, CV.INVOICE_NUMBER FROM CLAIM_HEADER CH, CLAIM_VERSION CV, (SELECT * FROM claim_reassign A WHERE ASSIGNED_DATE IN (SELECT MAX(ASSIGNED_DATE) FROM claim_reassign B WHERE B.claim_no = A.claim_no)) CR, (SELECT BUSINESS_UNIT_ID, SERVICE_AGENT_ID, USER_ID FROM DATA_SCOPE WHERE USER_ID = 'ZZPKMK') DS WHERE DS.SERVICE_AGENT_ID = CV.REPAIRING_SERVICE_AGENT_ID_IC AND DS.BUSINESS_UNIT_ID = CV.REPAIRING_BUSINESS_UNIT_ID AND CR.CLAIM_NO (+)= CV.CLAIM_NO AND CH.CLAIM_NO = CV.CLAIM_NO)CV
Explain plan for the above query
Execution Plan
Id Operation Name Rows Bytes TempSpc Cost (%CPU) Time Pstart Pstop TQ IN-OUT PQ Distrib
0 SELECT STATEMENT 2152 (100)
1 SORT AGGREGATE 1 94
2 PX COORDINATOR
3 PX SEND QC (RANDOM) :TQ10002 1 94 Q1,02 P->S QC (RAND)
4 SORT AGGREGATE 1 94 Q1,02 PCWP
5 HASH JOIN RIGHT OUTER 17M 1582M 2152 (3) 00:00:26 Q1,02 PCWP
6 BUFFER SORT Q1,02 PCWC
7 PX RECEIVE 194K 4946K 1216 (3) 00:00:15 Q1,02 PCWP
8 PX SEND BROADCAST :TQ10000 194K 4946K 1216 (3) 00:00:15 S->P BROADCAST
9 VIEW 194K 4946K 1216 (3) 00:00:15
10 HASH JOIN 194K 10M 7080K 1216 (3) 00:00:15
11 INDEX FAST FULL SCAN XPKCLAIM_REASSIGN 219K 4501K 158 (4) 00:00:02
12 VIEW VW_SQ_1 194K 6659K 267 (4) 00:00:04
13 HASH GROUP BY 194K 6468K 17M 267 (4) 00:00:04
14 INDEX FULL SCAN XPKCLAIM_REASSIGN 219K 7288K 267 (4) 00:00:04
15 TABLE ACCESS BY GLOBAL INDEX ROWID CLAIM_VERSION 205 9020 2 (0) 00:00:01 ROW L ROW L Q1,02 PCWP
16 NESTED LOOPS 17M 1145M 915 (1) 00:00:11 Q1,02 PCWP
17 BUFFER SORT Q1,02 PCWC
18 PX RECEIVE Q1,02 PCWP
19 PX SEND ROUND-ROBIN :TQ10001 S->P RND-ROBIN
20 INDEX RANGE SCAN XPKDATA_SCOPE 1756 42144 6 (0) 00:00:01
21 INDEX RANGE SCAN CLAIM_VERSION_SA_BU_PS_ACI_IDX 721 0 (0) Q1,02 PCWP
I hope it is clear now.
|
|
|
|
|
|
|
|
| Re: Tune long running query [message #534322 is a reply to message #534300] |
Mon, 05 December 2011 05:45 |
 |
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
|
Senior Member |
|
|
SELECT Nvl(SUM (claim_dealer_tot_amt_loc), 0),
Nvl(SUM (claim_dealer_tot_amt_reg), 0),
Nvl(SUM (claim_dealer_tot_amt_glob), 0),
Nvl(SUM (flag_count), 0),
COUNT(claim_no)
FROM (SELECT cv.claim_dealer_tot_amt_loc,
cv.claim_dealer_tot_amt_reg,
cv.claim_dealer_tot_amt_glob,
dbo.F_get_user_trans_flags_cnt (cv.claim_no, 'ZZPKMK')
"FLAG_COUNT",
cv.claim_no,
cv.active_version_indicator,
cv.repairing_business_unit_id,
cv.process_status,
cv.process_datetime,
cv.repairing_service_agent_id_ic,
ch.claim_type,
cv.change_by_toolkit_ind,
ch.labor_operation_cd,
ch.serialized_part_id,
ch.vin,
cv.job_card_no,
cv.repair_completion_date,
ch.job_card_open_date,
ch.technician_no,
ch.claim_submission_date,
ch.parts_return_project_id,
ch.parts_return_create_date,
ch.causal_part_no,
cv.repair_group,
cv.week_ending,
cv.invoice_number
FROM claim_header ch,
claim_version cv,
(SELECT *
FROM claim_reassign a
WHERE assigned_date IN (SELECT MAX(assigned_date)
FROM claim_reassign b
WHERE b.claim_no = a.claim_no)) cr,
(SELECT business_unit_id,
service_agent_id,
user_id
FROM data_scope
WHERE user_id = 'ZZPKMK') ds
WHERE ds.service_agent_id = cv.repairing_service_agent_id_ic
AND ds.business_unit_id = cv.repairing_business_unit_id
AND cr.claim_no (+) = cv.claim_no
AND ch.claim_no = cv.claim_no)cv |
|
|
|
|
|
| Re: Tune long running query [message #534330 is a reply to message #534323] |
Mon, 05 December 2011 06:24 |
cookiemonster
Messages: 13962
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
|
That query is selecting a lot of columns that it's not using in the outer select. Removing them all, as well as the cr inline view, will probably help.
|
|
|
|
|
|
|
|
|
|
| Re: Tune long running query [message #534343 is a reply to message #534336] |
Mon, 05 December 2011 07:33 |
Database admin
Messages: 365
Registered: September 2006
Location: india
|
Senior Member |
|
|
I explored this time and learnt how to use the SQL formatter.
Quote:SELECT Nvl(SUM (claim_dealer_tot_amt_loc), 0),
Nvl(SUM (claim_dealer_tot_amt_reg), 0),
Nvl(SUM (claim_dealer_tot_amt_glob), 0),
Nvl(SUM (flag_count), 0),
COUNT(claim_no)
FROM (SELECT cv.claim_dealer_tot_amt_loc,
cv.claim_dealer_tot_amt_reg,
cv.claim_dealer_tot_amt_glob,
dbo.F_get_user_trans_flags_cnt (cv.claim_no, 'ZZPKMK')
"FLAG_COUNT",
cv.claim_no,
cv.active_version_indicator,
cv.repairing_business_unit_id,
cv.process_status,
cv.process_datetime,
cv.repairing_service_agent_id_ic,
ch.claim_type,
cv.change_by_toolkit_ind,
ch.labor_operation_cd,
ch.serialized_part_id,
ch.vin,
cv.job_card_no,
cv.repair_completion_date,
ch.job_card_open_date,
ch.technician_no,
ch.claim_submission_date,
ch.parts_return_project_id,
ch.parts_return_create_date,
ch.causal_part_no,
cv.repair_group,
cv.week_ending,
cv.invoice_number
FROM claim_header ch,
claim_version cv,
(SELECT *
FROM claim_reassign a
WHERE assigned_date IN (SELECT MAX(assigned_date)
FROM claim_reassign b
WHERE b.claim_no = a.claim_no)) cr,
(SELECT business_unit_id,
service_agent_id,
user_id
FROM data_scope
WHERE user_id = 'ZZPKMK') ds
WHERE ds.service_agent_id = cv.repairing_service_agent_id_ic
AND ds.business_unit_id = cv.repairing_business_unit_id
AND cr.claim_no (+) = cv.claim_no
AND ch.claim_no = cv.claim_no)cv
Explain plan for the above query
--------------------
Operation NAME ROWS bytes tempspc cost (%cpu) TIME pstart pstop tq IN-OUT pq distrib
SELECT statement 2152 (100)
sort aggregate 1 94
px coordinator
px send qc (random) :TQ10002 1 94 q1,02 p->s qc (rand)
sort aggregate 1 94 q1,02 pcwp
hash join right outer 17m 1582m 2152 (3) 00:00:26 q1,02 pcwp
buffer sort q1,02 pcwc
px receive 194k 4946k 1216 (3) 00:00:15 q1,02 pcwp
px send broadcast :TQ10000 194k 4946k 1216 (3) 00:00:15 s->p broadcast
VIEW 194k 4946k 1216 (3) 00:00:15
hash join 194k 10m 7080k 1216 (3) 00:00:15
INDEX fast full scan xpkclaim_reassign 219k 4501k 158 (4) 00:00:02
VIEW vw_sq_1 194k 6659k 267 (4) 00:00:04
hash GROUP BY 194k 6468k 17m 267 (4) 00:00:04
INDEX full scan xpkclaim_reassign 219k 7288k 267 (4) 00:00:04
TABLE access BY global INDEX ROWID claim_version 205 9020 2 (0) 00:00:01 ROW l ROW l q1,02 pcwp
nested loops 17m 1145m 915 (1) 00:00:11 q1,02 pcwp
buffer sort q1,02 pcwc
px receive q1,02 pcwp
px send round-robin :TQ10001 s->p rnd-robin
INDEX RANGE scan xpkdata_scope 1756 42144 6 (0) 00:00:01
INDEX RANGE scan claim_version_sa_bu_ps_aci_idx 721 0 (0) q1,02 pcwp
Can you provide the solution for this long running query?
I wasted my time in following your guidelines.
Anyways Thanks Ram ..
Thanks
|
|
|
|
|
|
| Re: Tune long running query [message #534347 is a reply to message #534343] |
Mon, 05 December 2011 07:46 |
cookiemonster
Messages: 13962
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
balaji14 wrote on Mon, 05 December 2011 13:33
I wasted my time in following your guidelines.
You wasted your time? What about our time? We don't get paid to help you out, we're volunteers with day jobs, and you're not the only one asking for help.
The guidelines exist so that people can post information about problems in a way that everyone can follow, if you don't format an unreadable query we have to and we do have other things to do.
Do the following:
1) Follow my advice above about removing columns that aren't actually being used.
2) Post the revised query, formatted in code tags, not quote tags. Code tags give a monospaced font that makes code easier to read, that's why we tell people to use them.
3) Get the explain for the revised query using the following method:
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>
Then post it here in code tags. The one you've provided above is unreadable.
|
|
|
|
| Re: Tune long running query [message #534429 is a reply to message #534347] |
Tue, 06 December 2011 00:46 |
Database admin
Messages: 365
Registered: September 2006
Location: india
|
Senior Member |
|
|
Can you tell me how to find the columns that it's not using in the outer select as well as the cr inline view ?
SELECT Nvl(SUM (claim_dealer_tot_amt_loc), 0),
Nvl(SUM (claim_dealer_tot_amt_reg), 0),
Nvl(SUM (claim_dealer_tot_amt_glob), 0),
Nvl(SUM (flag_count), 0),
COUNT(claim_no)
FROM (SELECT cv.claim_dealer_tot_amt_loc,
cv.claim_dealer_tot_amt_reg,
cv.claim_dealer_tot_amt_glob,
dbo.F_get_user_trans_flags_cnt (cv.claim_no, 'ZZPKMK')
"FLAG_COUNT",
cv.claim_no,
cv.active_version_indicator,
cv.repairing_business_unit_id,
cv.process_status,
cv.process_datetime,
cv.repairing_service_agent_id_ic,
ch.claim_type,
cv.change_by_toolkit_ind,
ch.labor_operation_cd,
ch.serialized_part_id,
ch.vin,
cv.job_card_no,
cv.repair_completion_date,
ch.job_card_open_date,
ch.technician_no,
ch.claim_submission_date,
ch.parts_return_project_id,
ch.parts_return_create_date,
ch.causal_part_no,
cv.repair_group,
cv.week_ending,
cv.invoice_number
FROM claim_header ch,
claim_version cv,
(SELECT *
FROM claim_reassign a
WHERE assigned_date IN (SELECT MAX(assigned_date)
FROM claim_reassign b
WHERE b.claim_no = a.claim_no)) cr,
(SELECT business_unit_id,
service_agent_id,
user_id
FROM data_scope
WHERE user_id = 'ZZPKMK') ds
WHERE ds.service_agent_id = cv.repairing_service_agent_id_ic
AND ds.business_unit_id = cv.repairing_business_unit_id
AND cr.claim_no (+) = cv.claim_no
AND ch.claim_no = cv.claim_no)cv
Explain plan for the above query.
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
plan hash VALUE: 2733119754
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------
| id | operation | NAME
| ROWS | bytes |tempspc| cost (%cpu)| TIME | pstart| pstop | tq |IN-ou
t| pq distrib |
--------------------------------------------------------------------------------
plan_table_output
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------
| 0 | SELECT statement |
| 1 | 94 | | 2183 (3)| 00:00:27 | | | |
| |
| 1 | sort aggregate |
| 1 | 94 | | | | | | |
| |
plan_table_output
--------------------------------------------------------------------------------
| 2 | px coordinator |
| | | | | | | | |
| |
| 3 | px send qc (random) | :TQ10002
| 1 | 94 | | | | | | q1,02 | p->s
| qc (rand) |
| 4 | sort aggregate |
| 1 | 94 | | | | | | q1,02 | pcwp
| |
plan_table_output
--------------------------------------------------------------------------------
|* 5 | hash join right outer |
| 20m| 1868m| | 2183 (3)| 00:00:27 | | | q1,02 | pcwp
| |
| 6 | buffer sort |
| | | | | | | | q1,02 | pcwc
| |
| 7 | px receive |
| 195k| 4957k| | 1219 (3)| 00:00:15 | | | q1,02 | pcwp
plan_table_output
--------------------------------------------------------------------------------
| |
| 8 | px send broadcast | :TQ10000
| 195k| 4957k| | 1219 (3)| 00:00:15 | | | | s->p
| broadcast |
| 9 | VIEW |
| 195k| 4957k| | 1219 (3)| 00:00:15 | | | |
| |
|* 10 | hash join |
plan_table_output
--------------------------------------------------------------------------------
| 195k| 10m| 7096k| 1219 (3)| 00:00:15 | | | |
| |
| 11 | INDEX fast full scan | xpkclaim_reassign
| 219k| 4511k| | 158 (4)| 00:00:02 | | | |
| |
| 12 | VIEW | vw_sq_1
| 195k| 6673k| | 268 (4)| 00:00:04 | | | |
| |
plan_table_output
--------------------------------------------------------------------------------
| 13 | hash GROUP BY |
| 195k| 6482k| 17m| 268 (4)| 00:00:04 | | | |
| |
| 14 | INDEX full scan | xpkclaim_reassign
| 219k| 7304k| | 268 (4)| 00:00:04 | | | |
| |
| 15 | TABLE access BY global INDEX ROWID| claim_version
| 243 | 10692 | | 2 (0)| 00:00:01 | ROWID | ROWID | q1,02 | pcwp
| |
plan_table_output
--------------------------------------------------------------------------------
| 16 | nested loops |
| 20m| 1351m| | 939 (1)| 00:00:12 | | | q1,02 | pcwp
| |
| 17 | buffer sort |
| | | | | | | | q1,02 | pcwc
| |
| 18 | px receive |
| | | | | | | | q1,02 | pcwp
plan_table_output
--------------------------------------------------------------------------------
| |
| 19 | px send round-robin | :TQ10001
| | | | | | | | | s->p
| rnd-robin |
|* 20 | INDEX RANGE scan | xpkdata_scope
| 1754 | 42096 | | 7 (0)| 00:00:01 | | | |
| |
|* 21 | INDEX RANGE scan | claim_version_sa_bu_ps_aci_idx
plan_table_output
--------------------------------------------------------------------------------
| 726 | | | 0 (0)| 00:00:01 | | | q1,02 | pcwp
| |
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------
predicate information (IDENTIFIED BY operation id):
---------------------------------------------------
plan_table_output
--------------------------------------------------------------------------------
5 - access("CR"."CLAIM_NO"(+)="CV"."CLAIM_NO")
10 - access("ASSIGNED_DATE"="VW_COL_1" AND "CLAIM_NO"="A"."CLAIM_NO")
20 - access("USER_ID"='ZZPKMK')
21 - access("SERVICE_AGENT_ID"="CV"."REPAIRING_SERVICE_AGENT_ID_IC" AND "BUSIN
ESS_UNIT_ID"="CV"."REPAIRING_BUSINESS_UNIT_ID")
note
-----
- SQL profile "SYS_SQLPROF_014d024b44550000" used FOR this statement
I hope the format is clear now.
Thanks
[Updated on: Tue, 06 December 2011 00:53] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: Tune long running query [message #534448 is a reply to message #534429] |
Tue, 06 December 2011 03:57 |
cookiemonster
Messages: 13962
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
balaji14 wrote on Tue, 06 December 2011 06:46
Can you tell me how to find the columns that it's not using in the outer select as well as the cr inline view ?
By reading it.
Look at each column in the inner select. See if it's name appears in the outer select, either in the actual select part or the where clause, if it's not mentioned then it's not used.
There's nothing complicated about this.
As for the explain plan - it is still hard to read. Use set lines in sqlplus to ensure that each line isn't split into two, or even worse three, which is what happened above.
EDIT: extended last sentence.
[Updated on: Tue, 06 December 2011 04:44] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Jan 10 15:59:57 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
