Home » RDBMS Server » Performance Tuning » URGENT:-Performance Tuning.
icon9.gif  URGENT:-Performance Tuning. [message #120180] Wed, 18 May 2005 03:40 Go to next message
milind_sri
Messages: 70
Registered: February 2005
Location: Pune
Member

Hi Gurus,

This is the SQL which takes a very long time to execute. The execution plan I think is optimal and using proper indexes. The tables are all analysed. I cant understand the flaws in this SQL is that why it is taking so much time. I have tried and executed the sub-queries individualy they run fine in seconds but the whole query takes so much time. Why? I need your suggestions on this query that what changes in this query can help me to execute it faster.

select ch.CT_REFERENCE CTChannelRef,
ch.CHANNEL_NAME ChannelName,
ch.PARTNER_REFERENCE PartnerChannelRef,
bn.BUS_NAME ServedName,
ws.DEFENDANT_NAME Defendant,
(select j.juris_name
from arv_jurisdiction j
where j.juris_id = ws.REP_JURIS_ID) ServedJurisdiction,
decode(ws.IS_CONSOLIDATED,
'Y',
'Consolidated',
'N',
DECODE(ws.IS_BANKRUPTCY,
'Y',
'Bankruptcy',
'N',
'Standard')) CaseType,
(select arpkg_usop.GetLawSuitType(ws.lawsuit_type_cd) from dual) LawsuitType,
(select arpkg_usop.GetLawSuitSubType(ws.lawsuit_subtype_cd) from dual) LawsuitSubType,
ws.WORKSHEET_ID LogId,
ws.IMAGE_DOC_ID ImageDocId,
ws.IS_ONE_XMITTAL_PER_CASE SeparateTransmittalPerCase,
ws.IS_MUltIPLE IsMultiple,
ws.LAST_MODIFIED_DATE LastModifiedDate
from arv_sop_worksheet ws,
arv_usop_subscription sub,
ARV_BUSINESS_NAME bn,
arv_usop_channel ch
where ch.CHANNEL_ID = sub.CHANNEL_ID
--and trunc(ch.access_start_date) <= trunc(sysdate)
--and (trunc(ch.access_end_date) >= trunc(sysdate) or trunc(ch.access_end_date) is null)
and ws.STATUS_CD = 33003
and (trunc(ws.RECEIVED_DATE) < trunc(SYSDATE) or ws.IS_LOGPOSTED = 'Y')
and exists
(select 'X'
from arv_sop_action_item ai
where ws.WORKSHEET_ID = ai.WORKSHEET_ID
and ai.ACTION_ITEM_STATUS_CD in (38002, 38003, 38008, 38010, 38011))
and ws.Bus_Name_Id = bn.BUS_NAME_ID
and bn.ENTITY_ID = sub.object_id
and ws.LAST_MODIFIED_DATE between to_date('1/1/1992','dd/mm/yyyy') and to_date('16/5/2005','dd/mm/yyyy')
and sub.channel_id = 1020000111
and OBJECT_TYPE_CD = 11
union
select ch.CT_REFERENCE CTChannelRef,
ch.CHANNEL_NAME ChannelName,
ch.PARTNER_REFERENCE PartnerChannelRef,
bn.BUS_NAME ServedName,
ws.DEFENDANT_NAME Defendant,
(select j.juris_name
from arv_jurisdiction j
where j.juris_id = ws.REP_JURIS_ID) ServedJurisdiction,
decode(ws.IS_CONSOLIDATED,
'Y',
'Consolidated',
'N',
DECODE(ws.IS_BANKRUPTCY,
'Y',
'Bankruptcy',
'N',
'Standard')) CaseType,
(select arpkg_usop.GetLawSuitType(ws.lawsuit_type_cd) from dual) LawsuitType,
(select arpkg_usop.GetLawSuitSubType(ws.lawsuit_subtype_cd) from dual) LawsuitSubType,
ws.WORKSHEET_ID LogId,
ws.IMAGE_DOC_ID ImageDocId,
ws.IS_ONE_XMITTAL_PER_CASE SeparateTransmittalPerCase,
ws.IS_MUltIPLE IsMultiple,
ws.LAST_MODIFIED_DATE LastModifiedDate
from arv_sop_worksheet ws,
arv_usop_subscription sub,
ARV_BUSINESS_NAME bn,
arv_usop_channel ch
where ch.CHANNEL_ID = sub.CHANNEL_ID
--and trunc(ch.access_start_date) <= trunc(sysdate)
--and (trunc(ch.access_end_date) >= trunc(sysdate) or trunc(ch.access_end_date) is null)
and ws.STATUS_CD = 33003
and (trunc(ws.RECEIVED_DATE) < trunc(SYSDATE) or ws.IS_LOGPOSTED = 'Y')
and exists
(select 'X'
from arv_sop_action_item ai
where ws.WORKSHEET_ID = ai.WORKSHEET_ID
and ai.ACTION_ITEM_STATUS_CD in (38002, 38003, 38008, 38010, 38011))
and ws.Bus_Name_Id = bn.BUS_NAME_ID
and bn.entity_id in (select entity_id
from arv_affl_membership
where affl_id = sub.object_id)
and ws.LAST_MODIFIED_DATE between to_date('1/1/1992','dd/mm/yyyy') and to_date('16/5/2005','dd/mm/yyyy')
and sub.channel_id = 1020000111
and OBJECT_TYPE_CD = 31
union
select ch.CT_REFERENCE CTChannelRef,
ch.CHANNEL_NAME ChannelName,
ch.PARTNER_REFERENCE PartnerChannelRef,
bn.BUS_NAME ServedName,
ws.DEFENDANT_NAME Defendant,
(select j.juris_name
from arv_jurisdiction j
where j.juris_id = ws.REP_JURIS_ID) ServedJurisdiction,
decode(ws.IS_CONSOLIDATED,
'Y',
'Consolidated',
'N',
DECODE(ws.IS_BANKRUPTCY,
'Y',
'Bankruptcy',
'N',
'Standard')) CaseType,
(select arpkg_usop.GetLawSuitType(ws.lawsuit_type_cd) from dual) LawsuitType,
(select arpkg_usop.GetLawSuitSubType(ws.lawsuit_subtype_cd) from dual) LawsuitSubType,
ws.WORKSHEET_ID LogId,
ws.IMAGE_DOC_ID ImageDocId,
ws.IS_ONE_XMITTAL_PER_CASE SeparateTransmittalPerCase,
ws.IS_MUltIPLE IsMultiple,
ws.LAST_MODIFIED_DATE LastModifiedDate
from arv_sop_worksheet ws,
arv_usop_subscription sub,
ARV_BUSINESS_NAME bn,
arv_usop_channel ch
where ch.CHANNEL_ID = sub.CHANNEL_ID
--and trunc(ch.access_start_date) <= trunc(sysdate)
--and (trunc(ch.access_end_date) >= trunc(sysdate) or trunc(ch.access_end_date) is null)
and ws.STATUS_CD = 33003
and (trunc(ws.RECEIVED_DATE) < trunc(SYSDATE) or ws.IS_LOGPOSTED = 'Y')
and exists
(select 'X'
from arv_sop_action_item ai
where ws.WORKSHEET_ID = ai.WORKSHEET_ID
and ai.ACTION_ITEM_STATUS_CD in (38002, 38003, 38008, 38010, 38011))
and ws.Bus_Name_Id = bn.BUS_NAME_ID
and bn.entity_id in
(select entity_id
from arv_subgroup_membership
where subgroup_id = sub.object_id)
and ws.LAST_MODIFIED_DATE between to_date('1/1/1992','dd/mm/yyyy') and to_date('16/5/2005','dd/mm/yyyy')
and sub.channel_id = 1020000111
and OBJECT_TYPE_CD = 32;

Explain Plan:-
SELECT STATEMENT, GOAL = FIRST_ROWS 1225 244 60905
SORT UNIQUE 1225 244 60905
UNION-ALL
TABLE ACCESS BY INDEX ROWID ARROW TSOP_ACTION_ITEM 2 1 9
NESTED LOOPS 10 1 239
NESTED LOOPS 9 1 230
NESTED LOOPS 4 1 121
NESTED LOOPS 3 1 81
TABLE ACCESS BY INDEX ROWID ARROW TUSOP_CHANNEL 2 1 68
INDEX UNIQUE SCAN ARROW TUSOP_CHANNEL_PK 1
INDEX RANGE SCAN ARROW TUSOP_SUBSCRIPTION_UX1 1 1 13
INDEX RANGE SCAN ARROW BUS_NAME_ENTITYID_NDX 1 2 80
TABLE ACCESS BY INDEX ROWID ARROW TSOP_WORKSHEET 6 1 109
INDEX RANGE SCAN ARROW SOP_WORKSHEET_BUS_NM_PNDX 377
INDEX RANGE SCAN ARROW TSOP_ACTION_ITEM_WID_PNDX 1
TABLE ACCESS BY INDEX ROWID ARROW TSOP_ACTION_ITEM 2 1 9
NESTED LOOPS 411 84 20916
NESTED LOOPS 398 66 15840
NESTED LOOPS 11 69 9039
NESTED LOOPS 4 39 3549
NESTED LOOPS 3 1 81
TABLE ACCESS BY INDEX ROWID ARROW TUSOP_CHANNEL 2 1 68
INDEX UNIQUE SCAN ARROW TUSOP_CHANNEL_PK 1
INDEX RANGE SCAN ARROW TUSOP_SUBSCRIPTION_UX1 1 1 13
INDEX RANGE SCAN ARROW AFFL_MEMBERSHIP_NDX 1 65 650
INDEX RANGE SCAN ARROW BUS_NAME_ENTITYID_NDX 1 2 80
TABLE ACCESS BY INDEX ROWID ARROW TSOP_WORKSHEET 6 1 109
INDEX RANGE SCAN ARROW SOP_WORKSHEET_BUS_NM_PNDX 377
INDEX RANGE SCAN ARROW TSOP_ACTION_ITEM_WID_PNDX 1
TABLE ACCESS BY INDEX ROWID ARROW TSOP_ACTION_ITEM 2 1 9
NESTED LOOPS 771 159 39750
NESTED LOOPS 746 125 30125
NESTED LOOPS 18 130 17160
NESTED LOOPS 4 73 6716
NESTED LOOPS 3 1 81
TABLE ACCESS BY INDEX ROWID ARROW TUSOP_CHANNEL 2 1 68
INDEX UNIQUE SCAN ARROW TUSOP_CHANNEL_PK 1
INDEX RANGE SCAN ARROW TUSOP_SUBSCRIPTION_UX1 1 1 13
INDEX RANGE SCAN ARROW SUBGROUP_MEM_SGID_PNDX 1 122 1342
INDEX RANGE SCAN ARROW BUS_NAME_ENTITYID_NDX 1 2 80
TABLE ACCESS BY INDEX ROWID ARROW TSOP_WORKSHEET 6 1 109
INDEX RANGE SCAN ARROW SOP_WORKSHEET_BUS_NM_PNDX 377
INDEX RANGE SCAN ARROW TSOP_ACTION_ITEM_WID_PNDX 1

Any help is appreciated.

Thanks in advance.

Milind.
Re: URGENT:-Performance Tuning. [message #120223 is a reply to message #120180] Wed, 18 May 2005 09:36 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Please repost your query using proper (easy to read and follow) indentation. Use the CODE tags. What version are you?

And you are doing a lot of nested loops, which implies that your optimizer mode needs to be set to all_rows. And do you have enough memory in pga_agg_target (or hash_area and sort_area size)? Also, what kinds of indexes are you using on your columns (btree, bitmap?).

And how big is each table involved in the query? And how many total rows are you planning on getting? Indexes are not always good for large quantities of data.

And how exactly did you gather statistics? Did you either compute or get a sufficient sample size? Did you use histograms? Did you cascade true for the indexes?

I haven't read your query yet, but from scrolling through it, it looks possibly repetitive. If so, use the with clause, and use some inline views. And use union all if that will work.
Re: URGENT:-Performance Tuning. [message #120229 is a reply to message #120223] Wed, 18 May 2005 09:54 Go to previous messageGo to next message
milind_sri
Messages: 70
Registered: February 2005
Location: Pune
Member

Hi Smartin,

Thanks for your reply. I am sending you the details as desired by you. Kindly, help me in this case.

1.Please repost your query using proper (easy to read and follow) indentation. Use the CODE tags. What version are you?

Sorry,I am not clear on how to use code tags. My version is Oracle 9.2.0.5 and O.S. is windows 2003.

2.And do you have enough memory in pga_agg_target (or hash_area and sort_area size)? Also, what kinds of indexes are you using on your columns (btree, bitmap?).

The PGA_AGGREGATE_TARGET is 1024 MB and I am using B-Tree indexes.

3.And how big is each table involved in the query? And how many total rows are you planning on getting? Indexes are not always good for large quantities of data.

The tables which are involved are holding large amount of data.i.e millions of records. The query fetches 200000 rows.

4.And how exactly did you gather statistics? Did you either compute or get a sufficient sample size? Did you use histograms? Did you cascade true for the indexes?

I gather statistics using DBMS package i.e. gather schema stats.No, I dont use Histograms. I am not aware of Cascade true for Indexes.

5.And use union all if that will work.

Actually, I cant use UNION ALL in this case.

Waiting for your reply.

Milind.
Re: URGENT:-Performance Tuning. [message #120262 is a reply to message #120180] Wed, 18 May 2005 11:03 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
If you surround some text, like your query, in
 and 
tags, it will preserve the indentation and formatting of your query. Try it out and use preview message to test if it worked.

What is your optimizer_mode? Set it to all_rows.

dbms_stats.gather_schema_stats(owner,cascade=>true,method_opt=>'for all columns size 250');

Throw a degree=>2 or degree=>4 in there if you want to run the stats operation in parallel. And use an estimate percent if you want to as well.

Gather histograms and index statistics and generate and post your new explain plan along with your query. And run it again and time it and let us know how long it takes.

Re: URGENT:-Performance Tuning. [message #120336 is a reply to message #120262] Thu, 19 May 2005 03:39 Go to previous messageGo to next message
milind_sri
Messages: 70
Registered: February 2005
Location: Pune
Member

Thanks a lot Smartin.

Your suggestions worked for me. Actually, after implementing your suggestions I created a Materialized view and then executed the query. The query gave the response in 38 seconds.

Thanks a lot. You are doing a job on this forum.

Milind.
Re: URGENT:-Performance Tuning. [message #120399 is a reply to message #120180] Thu, 19 May 2005 08:25 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Cool, glad it worked out for you.
Re: URGENT:-Performance Tuning. [message #122525 is a reply to message #120399] Tue, 07 June 2005 00:49 Go to previous messageGo to next message
nabeelkhan
Messages: 73
Registered: May 2005
Location: Kuwait
Member

smartin wrote on Thu, 19 May 2005 16:25

Cool, glad it worked out for you.



I liked your approach towards the problem, This reminds me of my old friend Smile You Get 5 out of 5...

By the way Materialized view can't be taken for granted to provide better performance aren't they?

Quote:

Gather histograms and index statistics and generate and post your new explain plan along with your query. And run it again and time it and let us know how long it takes.



I would have loved to see the explain plan after hitograms and index stats.
Re: URGENT:-Performance Tuning. [message #122628 is a reply to message #120180] Tue, 07 June 2005 12:15 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Yeah I'm guessing it was the stats, The MV is kind of cheating in a way with regards to tuning the original query. Actually one place I've been wanting an opportunity to use an MV is to simply make tables skinnier. No sums or aggregates, just reduce a 50 column table to 10 colums to make a FTS faster.

Anyone know a reason why not to gather histograms? Unless I learn otherwise, I'm not seeing any harm in gathering them with the stats, and they have all the potential in the world to help the CBO make better decisions. A win-win. I think I actually view them as if they were the stats, it is like in my mind if you haven't done a cascade true and at least histograms on indexed columns then you haven't really gathered stats.
Re: URGENT:-Performance Tuning. [message #122652 is a reply to message #122628] Tue, 07 June 2005 15:28 Go to previous message
skempins
Messages: 16
Registered: June 2005
Location: Florida
Junior Member

smartin wrote on Tue, 07 June 2005 12:15

Anyone know a reason why not to gather histograms?



I don't know how this really translates to a real world situation but here is what Oracle documentation has to say about histograms:
Quote:

Histograms can affect performance and should be used only when they substantially improve query plans. Histogram statistics data is persistent, so the space required to save the data depends on the sample size. In general, create histograms on columns that are used frequently in WHERE clauses of queries and have a highly skewed data distribution. For uniformly distributed data, the cost-based optimizer can make fairly accurate guesses about the cost of executing a particular statement without the use of histograms.

Histograms, like all other optimizer statistics, are static. They are useful only when they reflect the current data distribution of a given column. (The data in the column can change as long as the distribution remains constant.) If the data distribution of a column changes frequently, you must recompute its histogram frequently.

Histograms are not useful for columns with the following characteristics:

All predicates on the column use bind variables.
The column data is uniformly distributed.
The column is unique and is used only with equality predicates.


I also read that if you use the DBMS_STATS package, Oracle will determine whether or not to use histograms.
Previous Topic: Stubborn Query
Next Topic: forced view
Goto Forum:
  


Current Time: Tue Jan 19 21:15:44 CST 2021