Home » RDBMS Server » Performance Tuning » New predicate increasing response time from 30 sec to 6 min (Oracle 10.2.0.3.0)
New predicate increasing response time from 30 sec to 6 min [message #347419] Thu, 11 September 2008 10:17 Go to next message
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 #347423 is a reply to message #347419] Thu, 11 September 2008 10:45 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Is the column properly indexed?
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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
Previous Topic: Index not being used in Oracle 10g
Next Topic: Interpreting Histograms
Goto Forum:
  


Current Time: Sun Dec 04 00:46:01 CST 2016

Total time taken to generate the page: 0.07319 seconds