RE: performance issue after upgrade to 10.2.0.5

Morning HS,

>> I've got problem with merge statement and it has appeared 
>> exactly after upgrade to 10.2.0.5 
>> Execution time before upgrade was about 1-1.5h and now it takes >4h. 
Sounds close to a problem I had after upgrading from 10.2.0.3 to
10.2.0.5. A job that ran overnight in 1 hour (or so) started to take
over 7 hours. The test team had tested everything on the test system and
pronounced it fit, but of course they didn't test this particular job.
We had to roll the changes back.

I've had an SR with Oracle since June. :-( It seems I've found a bug in
Oracle 10.2.0.5. The bug number is 11689699 in case you are able to look
at it - but don't expect much. :-(

Is there a database link involved in the MVIEW by any chance? IN my
case, there was a correlated sub-query running at the far end of a
database link. On 10.2.0.3 it showed up as an execution plan of "1.
remote" - everything ran at the remote end.

After the upgrade the plan changed to running some stuff remote and some
stuff locally then a big amalgamation of the results. Unfortunately it
ran a single remote SQL that retrieved 37,000 rows then for each of
those rows, ran three separate remote calls and then collected all the
results together.

Nasty!

>> Execution plan didn't change
Mine did! Unfortunately - from this in 10.2.0.3:

--------------------------------------------------------------
| Id | Operation | Name | Cost (%CPU)| Inst |IN-OUT|
--------------------------------------------------------------
| 0 | SELECT STATEMENT | | 0 (0)| | |
| 1 | REMOTE | | | NFCDD1 | R->S |
--------------------------------------------------------------


to this in 10.2.0.5:

------------------------------------------------------------------------
-----------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst
|IN-OUT|
------------------------------------------------------------------------
-----------------------------
| 0 | SELECT STATEMENT | | 162K| 7309K| 1034 (1)| 00:00:13 | | |
| 1 | SORT AGGREGATE | | 1 | 26 | | | | |
| 2 | REMOTE | ASSET_INSPECTION | 1 | 26 | 3 (0)| 00:00:01 | NFCDD1 |
R->S |
|* 3 | FILTER | | | | | | | |
| 4 | REMOTE | ASSET_INSPECTION | 1 | 155 | 3 (0)| 00:00:01 | NFCDD1 |
R->S |
| 5 | SORT AGGREGATE | | 1 | 26 | | | | |
| 6 | REMOTE | ASSET_INSPECTION | 1 | 26 | 3 (0)| 00:00:01 | NFCDD1 |
R->S |
| 7 | REMOTE | STRUCTURE | 162K| 7309K| 1034 (1)| 00:00:13 | NFCDD1 |
R->S |
------------------------------------------------------------------------
-----------------------------

Apologies in advance for what Outlook will probably do with the above. 

Ok, My problem is not exactly yours, but it just *might* be related.


Cheers,
Norm.

Norman Dunbar
[Soon to be Ex-]Contract Senior Oracle DBA
Capgemini Database Team (EA)
Internal : 7 28 2051
External : 0113 231 2051


Information in this message may be confidential and may be legally privileged. 
If you have received this message by mistake, please notify the sender 
immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should 
still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under 
the Freedom of Information Act, Data Protection Act or for litigation.  Email 
messages and attachments sent to or from any Environment Agency address may 
also be accessed by someone other than the sender or recipient, for business 
purposes.

If we have sent you information and you wish to use it please read our terms 
and conditions which you can get by calling us on 08708 506 506.  Find out more 
about the Environment Agency at www.environment-agency.gov.uk
--
http://www.freelists.org/webpage/oracle-l


Other related posts: