Home » SQL & PL/SQL » SQL & PL/SQL » Function very slow after Oracle upgrade (New version 10.2.0.5.0 on Windows box)
Function very slow after Oracle upgrade [message #568378] Thu, 11 October 2012 04:34 Go to next message
norty303
Messages: 3
Registered: October 2012
Location: East Sussex, England
Junior Member
Hi All

First of all, greetings to everyone (as it's my first post!) and apologies if this is in the wrong section, I'm not yet sure if this is code or DB configuration related (as you'll see...)

So, to the issue:

We have a function that is called in various other PL/SQL packages, and performance has always been very good.
On 29th Sept we upgraded our db to 10.2.0.5.0 and since then, a package that calls the function has gone from ~4mins, to ~2.5hrs to run.
In PL/SQL Developer, a simple select that calls the function has gone from ~0.5secs to retrieve the first 100 rows, to ~12secs.

I ran a profile of the main package, which highlighted the where the bottleneck was (a fetch from an explicit cursor). Running an explain plan on the cursor SQL doesn't really show up anything untoward.

However, I found that if I subtly changed the cursor SQL, (so that it did the same thing, but was written differently), it fixed the performance problems.

where  ade_start_date between cpDate-cpDays and cpDate-1
/*and    ade_start_date < cpDate 
and    ade_start_date >= (cpDate-cpDays)*/


From this, we thought that there may have been a bad cached execution plan which the change of code forced a recalculation of.
However, about 2 hours later, the changed code ran slowly again.
So a further subtle change was made, which fixed the issue again. Until this morning, when it was running slowly again.

This feels like it is CBO/stats related potentially, but is out of my area of knowledge unfortunately.

We have our DBA investigating this, but there may be things I can test to narrow down the possibilities in the meantime.

Many thanks for any assistance
Re: Function very slow after Oracle upgrade [message #568381 is a reply to message #568378] Thu, 11 October 2012 04:45 Go to previous messageGo to next message
Michel Cadot
Messages: 57613
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For any performances question, please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

Regards
Michel
Re: Function very slow after Oracle upgrade [message #568401 is a reply to message #568381] Thu, 11 October 2012 05:32 Go to previous messageGo to next message
norty303
Messages: 3
Registered: October 2012
Location: East Sussex, England
Junior Member
I think the most relevant info here is a trace, but unfortunately I don't have rights to access the trace tables in order to generate it or the db server to access the output file. I'm currently waiting on the DBA to do this.

The explain plan results for the cursor sql are below
			                                   Cost	Cardinality	Bytes
SELECT STATEMENT, GOAL = ALL_ROWS			        7       1	38
 FILTER					
  FILTER					
   TABLE ACCESS BY INDEX ROWID	CFPROD	O_AGREEMENT_DETAILS	7	1	38
    INDEX RANGE SCAN	CFPROD	O_ADE2	                        3	5	
  INDEX RANGE SCAN	BUSOBJ_USER	ESCC_RAP_REF_PK	        2	1	27
  INDEX RANGE SCAN	BUSOBJ_USER	ESCC_RAP_REF_PK	        2	1	27
  INDEX RANGE SCAN	BUSOBJ_USER	ESCC_RAP_REF_PK	        2	1	27


Re: Function very slow after Oracle upgrade [message #568403 is a reply to message #568401] Thu, 11 October 2012 05:42 Go to previous messageGo to next message
Michel Cadot
Messages: 57613
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The plan you show seems to indicate a query that is almost instantaneous, it can't be the root of your problem unless you execute it thousand times.

I oftem see that the best way to get the part of code that causes performances problems (when code is quite complex) is to use DBMS_PROFILER.

Regards
Michel
Re: Function very slow after Oracle upgrade [message #568404 is a reply to message #568403] Thu, 11 October 2012 06:04 Go to previous messageGo to next message
norty303
Messages: 3
Registered: October 2012
Location: East Sussex, England
Junior Member
Hi Michel

Yes, I agree. I mentioned in my first post that it was the profiler that showed where the problem was. It identified the fetch of the cursor as being the part that was taking the time. (First few lines of profiler output, sorted by Total_Time desc). Note that the second most costly fetch of cClient contains a call to the offending function, so they are related.

Unit                    Line    Total_Time      Occurences  text
ESCC_RAP_UTIL	        846	1,477,138	151595	    fetch cDLE_LifeLine into v_ade_id;
ESCC_RAP_A_EXTRACT	992	1,220,201	21972	    fetch cClient into rClient;
ESCC_RAP_UTIL	        837	74,020	        241455	    v_check_dle_ll := get_rap_ref_value('IS_CLIENT_NEW_CHECK_DLE_LL');
ESCC_RAP_A_EXTRACT	906	20,424	        21971	    select per_id,
ESCC_RAP_A_EXTRACT	1006	10,372	        34202	    fetch cAss into rAss;
ESCC_RAP_A_EXTRACT	175	9,178	        13977	    fetch cEvent into rEvent;
ESCC_RAP_A_EXTRACT	120	8,783	        129663	    fetch cBICA into dRefDate;


However, I don't think the problem is SQL tuning, as the sql that was running slowly, runs quickly after it has been amended (for a while). The significant part that seems to dictate performance seems to be that the SQL has changed slightly. E.g. I can change the sql (which I posted in the first post) back and forth, and I can get bad performance from both. This is why I think it is execution plan/stats related.

Unfortunately, I think the thing which is going to show the real problem is the trace, which I can't run.

Thanks

[Updated on: Thu, 11 October 2012 06:09]

Report message to a moderator

Re: Function very slow after Oracle upgrade [message #568416 is a reply to message #568404] Thu, 11 October 2012 06:42 Go to previous message
Michel Cadot
Messages: 57613
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think you well circumscribe the problem and determine how you can investigate deeper but if you can't activate a trace from your session I unfortunately don't see how we can get it.

Can you check the stats are up to date?
Can you execute the queries and get the plans using dbms_xplan.display% functions?
For instance:
<query1>
select * from table(dbms_xplan.display_cursor(NULL, NULL, 'allstats last outline'));
<query2>
select * from table(dbms_xplan.display_cursor(NULL, NULL, 'allstats last outline'));

Regards
Michel
Previous Topic: Export table data into text file through procedure/package (8 merged)
Next Topic: how to convert date to specific format?
Goto Forum:
  


Current Time: Sun Apr 20 05:01:08 CDT 2014

Total time taken to generate the page: 0.07601 seconds