RE: performance issue after upgrade to 10.2.0.5
Date: Fri, 26 Aug 2011 09:53:16 +0100
Message-ID: <919FC80F27E0C6428106496EDF92A752135CDC00_at_EXCCLUS05.PRODDS.NTNL>
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-lReceived on Fri Aug 26 2011 - 03:53:16 CDT