Home » RDBMS Server » Performance Tuning » Performance issue in query (Oracle 10 G)
| Performance issue in query [message #553113] |
Wed, 02 May 2012 00:21  |
rangan.s
Messages: 75
Registered: February 2008
Location: chennai
|
Member |
|
|
Hi,
I am trying to execute the following query . Its taking more than 10 mins to execute.
pls help me how to reduce the execution time.
SELECT ic.corn_id "CORN_ID", trace_id.ixml_id "TRACE_ID",
trx.trx_status "STATUS_OF_CLAIM",
trx.trx_complet_tm "PROCESS_DATE",
ic.send_id "SENDER_ID", ic.send_nm "SENDER_NAME",
ic.test_ind "TEST_PROD_INDICATOR",
ic.ch_status_agn "CLEARING_HOUSE_STATUS",
ic.ac_status_agn "AC_STATUS",
trx.transaction_type_id "CLAIM_TYPE",
subscriber.nmfirst "SUBSCRIBER_FIRST_NAME",
subscriber.nmlast "SUBSCRIBER_LAST_NAME",
subscriber.nmmid "SUBSCRIBER_MID_NAME",
subscriber.ixml_id "SUBSCRIBER_ID",
subscriber.group_nbr "SUBSCRIBER_GROUP_NO",
subscriber.group_nm "SUBSCRIBER_GROUP_NAME",
subscriber.birth_dt "SUBSCRIBER_DOB",
subscriber.gender_cd "SUBSCRIBER_GENDER",
customer.customer_id "PAYER_ID",
customer.customer_nm "PAYER_NAME",
pat_name.nmfirst "PATIENT_FIRST_NAME",
pat_name.nmlast "PATIENT_LAST_NAME",
pat_name.nmmid "PATIENT_MID_NAME",
patient.ixml_id "PATIENT_ID",
patient.birth_dt "PATIENT_DOB",
ic.patcntrl_nbr "PATIENT_CONTROL_NO",
ic.total_charge_amt "TOTAL_CLAIM_AMOUNT",
ic.created_on, 'ADVCLM', ic.claim_id "CLAIM_ID"
FROM ixml_claim ic,
trx,
ixml_id trace_id,
(SELECT ixml_id, ixml_patient.claim_id, name_id,
gender_cd, birth_dt
FROM ixml_patient,
ixml_patient_id_assoc ipa,
ixml_id
WHERE ixml_patient.patient_agn = ipa.patient_agn
AND ipa.id_agn = ixml_id.id_agn
AND ixml_id.primary_attribute_value IN
('MI', 'ZZ', 'SY', 'EI')) patient,
ixml_name pat_name,
(SELECT MAX
(NVL (customer_status_until_dt,
TO_DATE ('01-JAN-2900')
)
) "CUSTOMER_EFFECTIVE_DT",
a.customer_agn, customer_nm, customer_id
FROM (SELECT customer_id, customer_agn,
customer_nm
FROM customer
WHERE
customer_agn=15395
AND (par_flg, customer_id) IN (
SELECT MAX (par_flg),
customer_id
FROM customer
GROUP BY customer_id
)) a,
customer_status b
WHERE a.customer_agn = b.customer_agn
GROUP BY a.customer_agn, customer_nm, customer_id) customer,
( SELECT subr.name_id ,subr.nmfirst,subr.nmlast,subr.nmmid,subr.claim_id ,ixmlid.ixml_id,
subr.group_nbr , subr.group_nm, subr.birth_dt,
subr.gender_cd , subr.subscriber_agn
FROM
(SELECT sub.name_id, NAME.nmfirst, NAME.nmlast,
NAME.nmmid, sub.claim_id,
sub.group_nbr, sub.group_nm, sub.birth_dt,
sub.gender_cd , sub.subscriber_agn
FROM ixml_subscriber sub,
ixml_name NAME
WHERE sub.name_id = NAME.name_id
AND sub.s_seq_nbr = 1 ) subr
JOIN
(select subscriber_agn, max(ixml_id) ixml_id,claim_id
from (SELECT sia.subscriber_agn,
ID.ixml_id ixml_id, id.claim_id
FROM ixml_subscriber_id_assoc sia,
ixml_id ID,
ixml_subscriber sub
WHERE sia.id_agn = ID.id_agn
AND sia.subscriber_agn = sub.subscriber_agn
AND id.claim_id = sub.claim_id
AND id.claim_id = 166245
AND ID.primary_attribute_type_id(+) = 1
AND ( primary_attribute_value(+) = 'MI'
OR primary_attribute_value(+) = 'ZZ'
OR primary_attribute_value(+) = 'SY'
OR primary_attribute_value(+) = 'EI'
))
group by subscriber_agn,claim_id) ixmlid
ON
subr.subscriber_agn = ixmlid.subscriber_agn ) subscriber
WHERE ic.trx_agn = trx.trx_agn
AND ic.claim_id = patient.claim_id(+)
AND trx.customer_agn = customer.customer_agn(+)
AND patient.name_id = pat_name.name_id(+)
AND ic.claim_id = trace_id.claim_id(+)
AND trace_id.primary_attribute_value(+) = 'D9'
AND ic.claim_id = subscriber.claim_id(+)
Execution plan
Plan
SELECT STATEMENT ALL_ROWSCost: 103,573 Bytes: 500,823,388 Cardinality: 740,863
52 HASH JOIN RIGHT OUTER Cost: 103,573 Bytes: 500,823,388 Cardinality: 740,863
18 VIEW ADVCLM. Cost: 10 Bytes: 301 Cardinality: 1
17 NESTED LOOPS
15 NESTED LOOPS Cost: 10 Bytes: 95 Cardinality: 1
13 NESTED LOOPS Cost: 8 Bytes: 78 Cardinality: 1
10 VIEW ADVCLM. Cost: 7 Bytes: 40 Cardinality: 1
9 HASH GROUP BY Cost: 7 Bytes: 50 Cardinality: 1
8 NESTED LOOPS
6 NESTED LOOPS Cost: 6 Bytes: 50 Cardinality: 1
4 NESTED LOOPS Cost: 4 Bytes: 23 Cardinality: 1
2 TABLE ACCESS BY GLOBAL INDEX ROWID TABLE ADVCLM.IXML_SUBSCRIBER Cost: 2 Bytes: 11 Cardinality: 1 Partition #: 11
1 INDEX RANGE SCAN INDEX ADVCLM.IDX_XML_SUBSCRIBER_CLAIM Cost: 1 Cardinality: 1
3 INDEX RANGE SCAN INDEX ADVCLM.IDX_SUBSCRIBER_ID_AGN Cost: 2 Bytes: 12 Cardinality: 1
5 INDEX UNIQUE SCAN INDEX (UNIQUE) ADVCLM.XPKIXML_ID Cost: 1 Cardinality: 1
7 TABLE ACCESS BY GLOBAL INDEX ROWID TABLE ADVCLM.IXML_ID Cost: 2 Bytes: 27 Cardinality: 1 Partition #: 15
12 TABLE ACCESS BY GLOBAL INDEX ROWID TABLE ADVCLM.IXML_SUBSCRIBER Cost: 1 Bytes: 38 Cardinality: 1 Partition #: 16
11 INDEX UNIQUE SCAN INDEX (UNIQUE) ADVCLM.XPKIXML_SUBSCRIBER Cost: 0 Cardinality: 1
14 INDEX UNIQUE SCAN INDEX (UNIQUE) ADVCLM.XPKIXML_NAME Cost: 1 Cardinality: 1
16 TABLE ACCESS BY GLOBAL INDEX ROWID TABLE ADVCLM.IXML_NAME Cost: 2 Bytes: 17 Cardinality: 1 Partition #: 19
51 HASH JOIN RIGHT OUTER Cost: 103,559 Bytes: 277,823,625 Cardinality: 740,863
30 VIEW ADVCLM. Cost: 7 Bytes: 92 Cardinality: 1
29 HASH GROUP BY Cost: 7 Bytes: 35 Cardinality: 1
28 NESTED LOOPS Cost: 7 Bytes: 35 Cardinality: 1
23 NESTED LOOPS Cost: 4 Bytes: 33 Cardinality: 1
20 TABLE ACCESS BY INDEX ROWID TABLE ADVCLM.CUSTOMER Cost: 2 Bytes: 24 Cardinality: 1
19 INDEX UNIQUE SCAN INDEX (UNIQUE) ADVCLM.XPKCUSTOMER Cost: 1 Cardinality: 1
22 TABLE ACCESS BY INDEX ROWID TABLE ADVCLM.CUSTOMER_STATUS Cost: 2 Bytes: 9 Cardinality: 1
21 INDEX RANGE SCAN INDEX ADVCLM.IDX_CUST_STAT_CUSTOMER_AGN Cost: 1 Cardinality: 1
27 VIEW PUSHED PREDICATE VIEW SYS.VW_NSO_1 Cost: 3 Bytes: 2 Cardinality: 1
26 FILTER
25 SORT AGGREGATE Bytes: 8 Cardinality: 1
24 INDEX SKIP SCAN INDEX ADVCLM.IDX_CUSTOMER_PAR_FLG_CUST_ID Cost: 3 Bytes: 8 Cardinality: 1
50 HASH JOIN Cost: 103,548 Bytes: 209,664,229 Cardinality: 740,863
32 PARTITION RANGE ALL Cost: 14,436 Bytes: 104,099,533 Cardinality: 2,539,013 Partition #: 34 Partitions accessed #1 - #202
31 TABLE ACCESS FULL TABLE ADVCLM.TRX Cost: 14,436 Bytes: 104,099,533 Cardinality: 2,539,013 Partition #: 34 Partitions accessed #1 - #202
49 HASH JOIN OUTER Cost: 75,945 Bytes: 179,288,846 Cardinality: 740,863
46 HASH JOIN RIGHT OUTER Cost: 52,233 Bytes: 166,694,175 Cardinality: 740,863
41 VIEW ADVCLM. Cost: 25,942 Bytes: 40,580,984 Cardinality: 654,532
40 HASH JOIN Cost: 25,942 Bytes: 35,999,260 Cardinality: 654,532
34 PARTITION RANGE ALL Cost: 3,715 Bytes: 17,544,000 Cardinality: 731,000 Partition #: 40 Partitions accessed #1 - #202
33 TABLE ACCESS FULL TABLE ADVCLM.IXML_PATIENT Cost: 3,715 Bytes: 17,544,000 Cardinality: 731,000 Partition #: 40 Partitions accessed #1 - #202
39 HASH JOIN Cost: 19,997 Bytes: 20,290,492 Cardinality: 654,532
36 PARTITION RANGE ALL Cost: 3,425 Bytes: 7,600,923 Cardinality: 690,993 Partition #: 43 Partitions accessed #1 - #202
35 TABLE ACCESS FULL TABLE ADVCLM.IXML_PATIENT_ID_ASSOC Cost: 3,425 Bytes: 7,600,923 Cardinality: 690,993 Partition #: 43 Partitions accessed #1 - #202
38 PARTITION RANGE ALL Cost: 15,059 Bytes: 13,090,640 Cardinality: 654,532 Partition #: 45 Partitions accessed #1 - #202
37 TABLE ACCESS FULL TABLE ADVCLM.IXML_ID Cost: 15,059 Bytes: 13,090,640 Cardinality: 654,532 Partition #: 45 Partitions accessed #1 - #202
45 HASH JOIN RIGHT OUTER Cost: 19,026 Bytes: 120,760,669 Cardinality: 740,863
42 INDEX FAST FULL SCAN INDEX ADVCLM.IDX_PRIMARY_ATTR_CLAIM_IXML_ID Cost: 7,747 Bytes: 3,109,027 Cardinality: 163,633
44 PARTITION RANGE ALL Cost: 6,357 Bytes: 106,684,272 Cardinality: 740,863 Partition #: 49 Partitions accessed #1 - #202
43 TABLE ACCESS FULL TABLE ADVCLM.IXML_CLAIM Cost: 6,357 Bytes: 106,684,272 Cardinality: 740,863 Partition #: 49 Partitions accessed #1 - #202
48 PARTITION RANGE ALL Cost: 9,282 Bytes: 103,897,489 Cardinality: 6,111,617 Partition #: 51 Partitions accessed #1 - #202
47 TABLE ACCESS FULL TABLE ADVCLM.IXML_NAME Cost: 9,282 Bytes: 103,897,489 Cardinality: 6,111,617 Partition #: 51 Partitions accessed #1 - #202
Thanks
[Updated on: Wed, 02 May 2012 01:03] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: Performance issue in query [message #553497 is a reply to message #553113] |
Sat, 05 May 2012 07:04  |
martijn
Messages: 286
Registered: December 2006
Location: Netherlands
|
Senior Member |
|
|
I can not read the explain plan very well, but I see a few Full Table Scans in (at least) /ad/the last few rows of the plan.
For example the very last row is a FTS with a cardinality of over 6M. Maybe that is just what is needed but I can also imagine that an index would be of help.
|
|
|
|
| Re: Performance issue in query [message #553798 is a reply to message #553113] |
Tue, 08 May 2012 09:52 |
 |
LNossov
Messages: 318
Registered: July 2011
Location: Germany
|
Senior Member |
|
|
If you still need a solution, do the following please:
1. make the following settings
set linesize 1000
set pagesize 1000
2. then run
alter session set statistics_level=all;
3. then run your sql,
4. after that run the following select:
select plan_table_output from table ( sys.dbms_xplan.display_cursor( '','','ADVANCED ALLSTATS LAST'));
5. upload the last formatted output.
|
|
|
|
Goto Forum:
Current Time: Wed Feb 19 14:29:26 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
