| New predicate increasing response time from 30 sec to 6 min [message #347419] |
Thu, 11 September 2008 10:17  |
arunprasad_bh
Messages: 32
Registered: June 2007
|
Member |
|
|
|
I have a query from business objects. It is having joins on 15 tables and gives result in less than 30 sec. But when we add an extra condition on a very small table (1636 records), the response time shoots to 6 min. The cost after adding the new condition is 3228 as opposed to 5428 before this condition. Still the response time shoots to 6min. How to resolve this issue?
|
|
|
|
|
|
| Re: New predicate increasing response time from 30 sec to 6 min [message #347435 is a reply to message #347423] |
Thu, 11 September 2008 11:45  |
macdba
Messages: 27
Registered: May 2005
Location: US
|
Junior Member |
|
|
Compare the explain plan of the new query with that of old one.
Also if you are putting any hint in the sql statement, then the cost showed at the plan level does not seem to be always accurate. So compare the explain plan. Check the indexes, statistics, introduce an hint if the new table has index on it.
regards
--Mak
|
|
|
|
| Re: New predicate increasing response time from 30 sec to 6 min [message #347442 is a reply to message #347419] |
Thu, 11 September 2008 12:00 |
harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts
|
Senior Member |
|
|
Can you send the full plan before and after the criteria additon?
To add good advice :
| Quote: |
Is the column properly indexed?
|
I say also - determine if an index serves a purpose first
(and is not the problem root!), and then that it is correct for what you want to achieve.
I see the massive cascade of nested loops - 12 deep - which
requires multiplying the row num of each level in the plan
(dont be fooled by the row count of 1 listed at each step
- Oracle sometimes represents each range scan from the
perspective of one record and not the number of times the loop will be performed nor the number of records expected back at each level)
Leave cost out of the picture - if the access methods changed
between queries then they are different queries and that figure
absolutely does not represent anything.
Example: I will join some big tables at my company using hash and nested loops to show cost difference vs performance diff
sorry for format probs, its the cost i only want to show
select /*+ use_hash (d e o) NO_index */
d.edp_claim_id from
edp_hcfa_claim e,
edp_hcfa_claim_detail d,
efp_match_oldclaim o
where e.edp_claim_id = d.edp_claim_id and
d.rims_claim_number = o.ID_CLAIM;
Operation Object Name Rows Bytes Cost
SELECT STATEMENT Optimizer Mode=CHOOSE 408 K [B]1582[/B] HASH JOIN 408 K 12 M 1582 INDEX FAST FULL SCAN MPIEFP_DEV.EDP_HCFA_CLAIM_PK 173 K 1 M 52
HASH JOIN 408 K 10 M 1243
INDEX FAST FULL SCAN MPIEFP_DEV.SYS_C00116075 305 K 1 M 66
TABLE ACCESS FULL MPIEFP_DEV.EDP_HCFA_CLAIM_DETAIL 399 K 7 M 899
select /*+ index(e) */ edp_claim_id
from edp_hcfa_claim e
where exists
(select 1 from edp_hcfa_claim_detail D
where d.edp_claim_id = e.edp_claim_id and
exists
(select /*+ USE_NL leading(D) */ 1 from efp_match_oldclaim o where d.RIMS_CLAIM_NUMBER = o.ID_CLAIM));
Operation Object Name Rows Bytes Cost
SELECT STATEMENT Optimizer Mode=CHOOSE 8 K [B]53[/B]
INDEX FULL SCAN MPIEFP_DEV.EDP_HCFA_CLAIM_PK [B]8k[/B] 59 K 529
NESTED LOOPS 4 104 2
TABLE ACCESS BY INDEX ROWID MPIEFP_DEV.EDP_HCFA_CLAIM_DETAIL 4 80 1
INDEX RANGE SCAN MPIEFP_DEV.INDX_HCDTL_CLMID 4 3
INDEX UNIQUE SCAN MPIEFP_DEV.SYS_C00116075
1 6
The nested loop query will not finish (could be days, weeks)
The hash join version - few hours tops
Cost of hash join 1582
Cost of NL 59!
So its apples to oranges and the NL plan row count is not
representative of number of rows read in the execution,
which would be number of recs in top master table (about 400K) times avg detail recs per master rec and table o -
millions and millions of records will be read.
----
| Quote: | if you could submit the explain plan text in full for before and after (with the query) I think we can prove our culprit pretty quickly.
(of course the first question is always are the table stats fresh)
|
Best Regards,
|
|
|
|
| Re: New predicate increasing response time from 30 sec to 6 min [message #347569 is a reply to message #347442] |
Fri, 12 September 2008 03:24 |
arunprasad_bh
Messages: 32
Registered: June 2007
|
Member |
|
|
Hi,
The issue here is when I am adding a new predicate, some big tables are getting full table scans (which are using indexes before), one more table is using bit and and bit or (i.e. bit map idexes). I can resolve the issue by using hints, but the solution with out hints would be ideal as this query is fired from Business Objects. I am trying to understand how to avoid change of plan. The new condition is not related to the tables for which the access path is changing. Any ideas? The origina plan is attached now.
|
|
|
|
| Re: New predicate increasing response time from 30 sec to 6 min [message #347670 is a reply to message #347419] |
Fri, 12 September 2008 11:18 |
harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts
|
Senior Member |
|
|
Hi,
Can u provide the actual query SQL?
The one thing that sticks out between plans is the MERGE JOIN CARTESIAN - i'd double check the join fields on the new table.
But I need to match these steps with the statements, and understand the table relationships. the screen shots of the plans are truncated in the images i opened. The before image shows up to
INDEX UNIQUE SCAN GRW OWNDER.PK_GCRS SID
and the after ends at the Bitmap Index Single Value line.
Thanx
Harry
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
