Oracle 10g SLOW Insert statements 1

[missy8]

Hi

PLEASE PLEASE PLEASE help me I’m really stuck.
We have just upgraded our 9i Oracle DB to 10g.
(This is a copy of our live DB that we are upgrading to test)
There have been no hardware or code changes.
My problem is that some of our code is now running dog slow. For example a 2.5 hour dataload is now taking 9 hours. I’ve gone through the code and this is one of many SQL statements where there seems to be a problem
An example of this is the following SQL statement.

INSERT INTO acc_bal_eur_temp1
SELECT DISTINCT aba.member_id
,-2
,to_date('01-JAN-1900 05:00:00' ,'DD-MON-YYYY HH24:MI:SS')
,-1800
,'050000'
,decode(sign(dd.balance - (decode(substr(crdr ,1 ,2) ,'DR' ,-amount ,amount))) ,1 ,'CR' ,'DR')
,abs(dd.balance - decode(substr(crdr ,1 ,2) ,'DR' ,-amount ,amount))
,0
FROM daily_data dd
,acc_bal_account aba
WHERE dd.accountnum = aba.ac_no
AND aba.ac_type_mid = 'Euro settlement account (EUR)'
AND dd.exactdate = (SELECT MIN(dd1.exactdate)
FROM daily_data dd1
WHERE dd1.accountnum = dd.accountnum)
AND dd.transid = (SELECT MIN(dd2.transid)
FROM daily_data dd2
WHERE dd2.accountnum = dd.accountnum
AND dd2.exactdate = dd.exactdate)
AND dd.id = (SELECT MIN(dd3.id)
FROM daily_data dd3
WHERE dd3.accountnum = dd.accountnum
AND dd3.exactdate = dd.exactdate
AND dd3.transid = dd.transid)
AND (dd.balance - decode(substr(crdr ,1 ,2) ,'DR' ,-amount ,amount)) <> 0
and rownum = 1;

The acc_bal_eur_temp1 table is a small table with no indexes that is completely empty before this insert.
This statement should insert 16 rows.
If I run the Select statement on it’s own it takes 0.54 seconds to run and returns 16 rows.
If I run the insert statement with an AND rownum = 1 parameter the insert takes 0.45 seconds to insert 1 row.
If I run the complete statement it takes 45min.
On the live 9i database this statement takes 36 seconds to run.

I checked the performance in the 10g Enterprise Manager consol and the CPU usage is sky high when running this statement.
I checked the tuning information and the Tuning advisor suggested I analyse the tables.
This had been done on all the tables but I did it again anyway. Still no improvement.

I am completely stumped, is there anything wrong with Insert statements in Oracle 10g and if so what can I do to rectify the situation. If not do I have to go through all 68000 lines of code and change them to enable this 10g upgrade to work.
Is there anything any of you can suggest?

THANKS

 

[Beilstwh]

The problem is not (I suspect) on the insert, it is on the select that is doing the insert. After you upgraded, did you gather new statistics?

Bill
Oracle DBA/Developer
New York State, USA

 

[taupirho]

I suggest you run explain plans on both the 9i and 10g environments and compare. These should be different given the runtime discrepancy and should give you a good pointer as to what is different between the two systems.

 

[missy8]

THANKS Beilstwh and taupirho
Yup we did gather stats straight after the upgrade and once again after not being able to solve the problems.
I have just run the explain plan and they are different. I find this strange since the new database is a copy of the old DB it’s just been upgraded.
My sad realisation is now I have to go through 68000 lines of code and find all the bad insert statements and rewrite them. Since this system is a data warehouse it has a lot of insert statements. But it could be worse.
THANKS for the help I was panicking a bit there.

 

[taupirho]

misys8,

There may be a simple explanation for the difference in explain plans which would save you a lot of work. I'm not an expert on explain plans but I suggest you post both plans here and let some others have a look.

Could be something as simple as using different optimisation modes. Also have you checked you ORA init values as well for consistency too - SGA size, cache size, sort areas, DB block size , log buffer size etc ... - there's a ton of things that could be different between your DB's.

 

[missy8]

Thanks
I have printed out all the params from v$parameters and am going through them at the moment.
Here are the two explain plans. I can only think there must be some setup in 10g that has caused this, hopefully looking through the v$parameters etc will help.
If anyone has any suggestions on what to look at any help would be GREATLY appreciated.
THANKS

Working SQL Explain Plan
Object name Cost Cardinality Bytes
SELECT STATEMENT, GOAL = ALL_ROWS 1068 1 130
SORT UNIQUE 1050 1 130
FILTER
SORT GROUP BY 1050 1 130
FILTER
TABLE ACCESS BY INDEX ROWID DAILY_DATA 167 2436 41412
NESTED LOOPS 1045 1 130
NESTED LOOPS 878 1 113
TABLE ACCESS FULL DAILY_DATA 878 1 63
INDEX UNIQUE SCAN PK_ACC_BAL_ACCOUNT 1 50
INDEX RANGE SCAN I_DAILY_DATA_ACCNUM 10 2436
SORT AGGREGATE 1 34
TABLE ACCESS BY INDEX ROWID DAILY_DATA 9 1 34
INDEX RANGE SCAN I_DAILY_DATA_EXACTDATE 3 23
SORT AGGREGATE 1 39
TABLE ACCESS BY INDEX ROWID DAILY_DATA 9 1 39
INDEX RANGE SCAN I_DAILY_DATA_EXACTDATE 3 23


Slow SQL Explain Plan

Object name Cost Cardinality Bytes
SELECT STATEMENT, GOAL = ALL_ROWS 2449 1 115
HASH UNIQUE 2449 1 115
FILTER
NESTED LOOPS 2440 1 115
NESTED LOOPS 2440 5 370
VIEW VW_SQ_1 2143 99 1782
HASH GROUP BY 2143 99 2574
TABLE ACCESS FULL DAILY_DATA 2112 365212 9495512
TABLE ACCESS BY INDEX ROWID DAILY_DATA 3 1 56
INDEX RANGE SCAN WYLDE_DD_1 2 1
INDEX UNIQUE SCAN PK_ACC_BAL_ACCOUNT 0 1 41
SORT AGGREGATE 1 34
TABLE ACCESS BY INDEX ROWID DAILY_DATA 4 1 34
INDEX RANGE SCAN WYLDE_DD_1 3 1
SORT AGGREGATE 1 39
TABLE ACCESS BY INDEX ROWID DAILY_DATA 4 1 39
INDEX RANGE SCAN WYLDE_DD_1 3 1

 

[dbalearner]

Hi,

Excessice CPU usage normally implies lots of logical I/Os. The slow explain plan uses hashing algorithm whereas the faster one deploys nested loops. Did you increase the buffer cache when you upgraded to 10g?

 

[missy8]

Hi

Yup we did that but it didn’t help.
Over the past few days I’ve been in contact with Oracle. They have confirmed that it is a bug in the optimizer and are working on the problem. Hopefully they will come back to me with some DB param changes.
Thanks for all the suggestions I’ll let you know what Oracles suggestions are.
Cheers

 

[missy8]

Hi

I thought I’d quickly update this post in case anyone is interested in what has happens.
We logged a call with Oracle and although it took awhile they have confirmed that there is a bug in 10g causing this problem. It is relay an intermittent problem as most insert statements are working. We’ve had to roll back to 9i and wait for a patch,

Cheers

 

[SantaMufasa]

...And, Missy, you will favour us with the patch number, right?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[missy8]

AS SOON AS I GET IT

I can assure you I am waiting on tender hooks as this upgrade is now way overdue for us, and is fine on our other database. Intermittent problems are sooooooooo frustrating.

Cheers
M

 

[taupirho]

Missy8,

Sorry to be pedantic but the expression you were looking for is tenterhooks

 

[missy8]

Hey no probs
you're a programmer of course you're pedantic, it’s expected

 

[PootangPie]

missy8,

Have oracle made any progress towards your issue. I'm running into the same problem. Also, can you supply the bug number/note ID so that I can track this on metalink. Thanks.

regards,
Pie

 

[missy8]

Hi

The Service Request number is: 5902944.993
The Bug Ref is: 5660222

Good luck
Muppet

 

[PootangPie]

Thanks missy8... I was able to see your SR on metalink, but can't find the bug number... I'm looking at patch 10.2.0.3 to see if oracle addresses this problem... I'll let you know I can find out anything... Thanks again...

regards,
Pie