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  |
 |
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 #568404 is a reply to message #568403] |
Thu, 11 October 2012 06:04  |
|
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 |
 |
Michel Cadot
Messages: 54246
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
|
|
|
|
Goto Forum:
Current Time: Sat May 25 11:17:43 CDT 2013
Total time taken to generate the page: 0.23632 seconds
|
] 
Members
Search
Help
Register
Login
Home
