Home » Infrastructure » Unix » Patch Oracle 10.2.0.2 to 10.2.0.4, consume high memory (Oracle 10gR2, UNIX)
Patch Oracle 10.2.0.2 to 10.2.0.4, consume high memory [message #400983] Thu, 30 April 2009 21:43 Go to next message
kay_lee
Messages: 4
Registered: April 2009
Location: earth
Junior Member
Hi all,

Can anyone share any experience on patching Oracle 10.2.0.2 to 10.2.0.4? I have a trouble here...
Looks like something's wrong with my server after the patching.
I follow up all the instruction from the ReadMe file during the patching process.
The Oracle consume a very high memory, more than usual(before patch).
What i suspect is.. the parameter db_file_scattered_read is high, so high...

Can anybody tell me, what to do??

Thanks

Kay
Re: Patch Oracle 10.2.0.2 to 10.2.0.4, consume high memory [message #400984 is a reply to message #400983] Thu, 30 April 2009 22:11 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Can anybody tell me, what to do??
You can start by reading & following Posting Guidelines.
http://www.orafaq.com/forum/t/88153/0/

Please, please, please Read & Follow Posting Guidelines above.

Go to the section labeled "Practice" & do as directed.

Here is what we know: You recently upgraded from 10.2.0.2 to 10.2.0.4


>The Oracle consume a very high memory, more than usual(before patch).
Please quantify.

Other than the observation about "consume a very high memory" what other indications do you have that more problems exist?
Re: Patch Oracle 10.2.0.2 to 10.2.0.4, consume high memory [message #400988 is a reply to message #400984] Thu, 30 April 2009 23:21 Go to previous messageGo to next message
kay_lee
Messages: 4
Registered: April 2009
Location: earth
Junior Member
The memory before patch was only reached highest of 20%, now it's average 40%. I'm using Solaris 10.
The server had more than 5 Oracle instances, all upgraded.
And any query(select, insert, update) gives result slower than before, about 3 times slower, for the same query, usually 5 seconds, now 1 minutes and 30 seconds.
A procedure that used to run in 30 minutes, now at least need 1 hour to complete. Same procedure, same amount of data.
Nothing's wrong with the I/O of the OS.
I've tuned the database, rebuild indexes, analyzed all objects, but it still consume high memory, and works slow.

Here is what i concerned of :

db file scattered read
1st | 2st | %diff
0.00 | 36.15 | 36.15
0.00 | 215.75 | 100.00
0.23 | 1.41 | 513.04

db file sequential read
1st | 2st | %diff
32.00 | 6.84 | -25.17
805.14 | 67.78 | -91.58
0.13 | 0.85 | 553.85

Description :
1st --> before patch
2st --> after patch

ADDITIONAL INFORMATION
----------------------

Wait class "Application" was not consuming significant database time.
Wait class "Commit" was not consuming significant database time.
Wait class "Concurrency" was not consuming significant database time.
Wait class "Configuration" was not consuming significant database time.
CPU was not a bottleneck for the instance.
Wait class "Network" was not consuming significant database time.
Session connect and disconnect calls were not consuming significant database
time.
Hard parsing of SQL statements was not consuming significant database time.

The database's maintenance windows were active during 35% of the analysis
period.

The analysis of I/O performance is based on the default assumption that the
average read time for one database block is 10000 micro-seconds.


Thank you in advance
Re: Patch Oracle 10.2.0.2 to 10.2.0.4, consume high memory [message #400991 is a reply to message #400983] Thu, 30 April 2009 23:39 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
How is it that you did not realize the results before deploying into Production?
Re: Patch Oracle 10.2.0.2 to 10.2.0.4, consume high memory [message #401425 is a reply to message #400991] Mon, 04 May 2009 22:48 Go to previous messageGo to next message
kay_lee
Messages: 4
Registered: April 2009
Location: earth
Junior Member
anyone can help? thx
Re: Patch Oracle 10.2.0.2 to 10.2.0.4, consume high memory [message #401426 is a reply to message #400983] Mon, 04 May 2009 23:01 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/84315/74940/
which have you tried & what were the results
Re: Patch Oracle 10.2.0.2 to 10.2.0.4, consume high memory [message #406390 is a reply to message #400983] Wed, 03 June 2009 15:26 Go to previous messageGo to next message
dswilson
Messages: 4
Registered: June 2009
Junior Member
I'm seeing similar behaviour on a Windows 2003 R2 SP2 machine after applying
10.2.0.4. The database is about 3-4 times slower. This is a development
environment. I've tried similar things as in the original post and nothing has
worked. The upgrade obviously changes something. The question is what and what
needs to be done to fix things.


Thanks,
Dave

[Updated on: Wed, 03 June 2009 15:31]

Report message to a moderator

Re: Patch Oracle 10.2.0.2 to 10.2.0.4, consume high memory [message #406394 is a reply to message #400983] Wed, 03 June 2009 15:40 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
set timing on;
exec dbms_stats.gather_database_stats ();


Can't hurt & just might help.
Re: Patch Oracle 10.2.0.2 to 10.2.0.4, consume high memory [message #406402 is a reply to message #400983] Wed, 03 June 2009 17:39 Go to previous messageGo to next message
dswilson
Messages: 4
Registered: June 2009
Junior Member
That didn't seem to have any effect. I'm going to try and see if
the tuning stuff in EM Console highlights anything.

Thanks,
Dave
Re: Patch Oracle 10.2.0.2 to 10.2.0.4, consume high memory [message #406405 is a reply to message #400983] Wed, 03 June 2009 18:34 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Change the AWR snapshot period to 10 or 15 minutes & then run a report for a single period when performance is poor.

Again, it may or may not provide any additional clues.
Re: Patch Oracle 10.2.0.2 to 10.2.0.4, consume high memory [message #406593 is a reply to message #400983] Thu, 04 June 2009 15:38 Go to previous messageGo to next message
dswilson
Messages: 4
Registered: June 2009
Junior Member
The initial tuning stuff I have outlined memory and IO as issues. I adjusted the SGA and PGA
settings and that is not a problem. I did a complete defrag of the disk to reduce lookup time
in that regard, but once the data is cached in memory that shouldn't come into play.

The application I'm using is web based mapping. A query is done to determine if any object
under the mouse cursor should display a tooltip.

My tooltips (initially only 4 sec on 10.2.0.1) went to about 16-20sec with 10.2.0.4
and to 28 sec while the tuning snapshot was taken.

The only recommendation that came up was in regards to a similar query as before:

select nt_na_link_roundabout_fc5.featid,
nt_na_link_roundabout_fc5.name,
nt_na_link_roundabout_fc5.primaryind,
nt_na_link_roundabout_fc5.url,
nt_na_link_roundabout_fc5.sdf_id,
nt_na_link_roundabout_fc5.sdf_name,
nt_na_link_roundabout_fc5.sdf_url,
nt_na_link_roundabout_fc5.geometry from
nt_na_link_roundabout_fc5 where ( sdo_relate
( nt_na_link_roundabout_fc5.geometry, :spatialfilter1,
'MASK=ANYINTER...

ORA-29900: operator binding does not exist ORA-06553: PLS-306:
wrong number or types of arguments in call to 'SDO_RELATE'


Re: Patch Oracle 10.2.0.2 to 10.2.0.4, consume high memory [message #406594 is a reply to message #400983] Thu, 04 June 2009 15:45 Go to previous messageGo to next message
dswilson
Messages: 4
Registered: June 2009
Junior Member
An additional piece of info. For this run I tried using the 11g instant client instead
of the 10.2.0.4 instant client and that appears to have increased the tooltip
query from 16sec to 28 sec.

Dave
Re: Patch Oracle 10.2.0.2 to 10.2.0.4, consume high memory [message #406881 is a reply to message #406594] Sun, 07 June 2009 01:19 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

Post AWR Report.

BAbu
Re: Patch Oracle 10.2.0.2 to 10.2.0.4, consume high memory [message #407087 is a reply to message #400983] Mon, 08 June 2009 07:11 Go to previous messageGo to next message
WillIm
Messages: 13
Registered: June 2008
Location: Kyrgyzstan, Bishkek
Junior Member
Hmm... I haven't faced such problems after patching 10.2.0.* bases. Are you able to rollback the patch? Do you use 2 ORACLE_HOME's? I always try to use 2 homes in order to decrease downtime in case smth goes wrong.

[Updated on: Mon, 08 June 2009 07:20]

Report message to a moderator

Re: Patch Oracle 10.2.0.2 to 10.2.0.4, consume high memory [message #407350 is a reply to message #407087] Tue, 09 June 2009 19:12 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

>>I haven't faced such problems after patching 10.2.0.* bases. Are you able to rollback the patch? Do you use 2 ORACLE_HOME's

10.2.0.4 Oracle fixed many bugs; that's the reason only database upgrade happen. Why you want to roolback your patch? You need find root cause of your problem. Using staspack/awr report.

Thanks
Re: Patch Oracle 10.2.0.2 to 10.2.0.4, consume high memory [message #407709 is a reply to message #407350] Thu, 11 June 2009 03:54 Go to previous messageGo to next message
WillIm
Messages: 13
Registered: June 2008
Location: Kyrgyzstan, Bishkek
Junior Member
I meant you may downgrade in order to find out possible reasonf that affect your performance. For instance, you go back to 10.2.0.2 and you production DB works on regular basis while you delve into perfromance issues on your test environment.
Re: Patch Oracle 10.2.0.2 to 10.2.0.4, consume high memory [message #417514 is a reply to message #407709] Mon, 10 August 2009 00:34 Go to previous messageGo to next message
kay_lee
Messages: 4
Registered: April 2009
Location: earth
Junior Member
hi, there...

here is the ADDM Report after the patch.

i have done some tuning on my DB but it doesn't seem to help much.

so, can anybody please help me?

thank you in advance
Re: Patch Oracle 10.2.0.2 to 10.2.0.4, consume high memory [message #431401 is a reply to message #400983] Tue, 17 November 2009 04:17 Go to previous messageGo to next message
igtaibo
Messages: 1
Registered: November 2009
Junior Member
Hi!

I don't know if this will help you, but we have had the same problem, and the solution we applied was to run statistics in Oracle after the upgrade.

Hope this helps..
Regards.
Re: Patch Oracle 10.2.0.2 to 10.2.0.4, consume high memory [message #431418 is a reply to message #431401] Tue, 17 November 2009 05:08 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It may be useful, thanks.

Regards
Michel
Previous Topic: Unix code
Next Topic: unix server
Goto Forum:
  


Current Time: Thu Mar 28 06:33:24 CDT 2024