Home » RDBMS Server » Performance Tuning » DB performing very slowly, (10g/Solaris)
DB performing very slowly, [message #318743] Wed, 07 May 2008 16:48 Go to next message
me_maddy
Messages: 31
Registered: April 2008
Member
Hello Everybody,

We have one of our database(1.8 TB size) which is performing very slowly,

cache hit ratio= 28.7%

After checking I found the db_cache_size=32M and shared_pool_size=400M.

I changed the values dynamically for db_cache_size=4G and shared_pool_size=4G.
but still cache hit ratio= 28.7% and performance is very poor.

Oracle: 10.2.0.3.0
OS: Solaris 10 SPARC 64

Please suggest, this is really a crunch situattion.

Regards,
Maddy.

Report message to a moderator

Re: DB performing very slowly, [message #318744 is a reply to message #318743] Wed, 07 May 2008 16:53 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 of the above suggestions/ideas have you done & what were the results?

Buffer Cache Hit Ratio (BCHR) is a mythical indicator of performance.
In and by itself, it is a meaningless number.
Pl/SQL code exists that allows you to specify & obtain any BCHR you desire.

You've asked, "How do I tune a poorly performing application?".
The answer is "You tune one SQL statement at a time.".

Only rarely is there is single magic pill that makes everything OK after taking it.

[Updated on: Wed, 07 May 2008 16:57] by Moderator

Report message to a moderator

Re: DB performing very slowly, [message #318747 is a reply to message #318743] Wed, 07 May 2008 17:07 Go to previous messageGo to next message
me_maddy
Messages: 31
Registered: April 2008
Member
Thanks for your reply Anacedent.

Actually this is a newly created, only 2 weeks old server.the database was earlier running on hpux and now migrated to solaris server.

Please suggest which parameters should we tune or look after.

Thanks.

Report message to a moderator

Re: DB performing very slowly, [message #318748 is a reply to message #318743] Wed, 07 May 2008 17:16 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Actually this is a newly created, only 2 weeks old server.the database was earlier running on hpux and now migrated to solaris server.

has DBMS_STATS.GATHER_DATABASE_STATS been run against the Solaris DB?

>Please suggest which parameters should we tune or look after.
Only rarely is there is single magic pill that makes everything OK after taking it & there is NO _make_sql_faster parameter.

Report message to a moderator

Re: DB performing very slowly, [message #318751 is a reply to message #318743] Wed, 07 May 2008 17:32 Go to previous messageGo to next message
me_maddy
Messages: 31
Registered: April 2008
Member
Hi

We have taken schema level statistics on solaris, not database level statistics.

Shall we go ahead and perform database level statistics using DBMS_STATS.GATHER_DATABASE_STATS.

Moreover optimizer_mode= ALL_ROWS.
Do we need to change it to choose.

Appreciate your response in this regard.

Thanks.

Report message to a moderator

Re: DB performing very slowly, [message #318752 is a reply to message #318743] Wed, 07 May 2008 17:41 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Shall we go ahead and perform database level statistics using DBMS_STATS.GATHER_DATABASE_STATS.
It can't hurt, but likely won't help.


>Moreover optimizer_mode= ALL_ROWS.
Why? Based upon what benchmarks?

>Do we need to change it to choose.
Why choose? Based upon what benchmarks?

If you really don't know what SQL is slow, you are simply shooting in the dark & hoping to get lucky that you find a "solution" before making a bad situation worse.

1)Run STATPACK/AWR for no longer than 15 minutes.
2)Identify the SQL statements consuming the most resources.
3)Analyze these SQL statements to see if/how they can be made faster.
4)Place the improved SQL into production
5) GOTO #1 & repeat this loop until you have Happy Users!

Report message to a moderator

Re: DB performing very slowly, [message #318753 is a reply to message #318743] Wed, 07 May 2008 18:28 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Choose any hit ratio

http://www.oracledba.co.uk/tips/choose.htm

Report message to a moderator

Re: DB performing very slowly, [message #318774 is a reply to message #318751] Wed, 07 May 2008 23:08 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
me_maddy wrote on Thu, 08 May 2008 08:32

Moreover optimizer_mode= ALL_ROWS.
Do we need to change it to choose.


You don't use CHOOSE in 10g any more. CHOOSE used to choose between ALL_ROWS and RULE, but now RULE is deprecated, therefore so is CHOOSE.
Ross Leishman

Report message to a moderator

Re: DB performing very slowly, [message #319393 is a reply to message #318743] Sat, 10 May 2008 09:24 Go to previous messageGo to next message
michael_bialik
Messages: 621
Registered: July 2006
Senior Member
Are you really need 4Gb for shared pool? or are you doing this out of desperation?

Check and tune your SQL statements (but you already got that advice).

Report message to a moderator

Re: DB performing very slowly, [message #319970 is a reply to message #318743] Tue, 13 May 2008 09:28 Go to previous message
enkhbold
Messages: 36
Registered: July 2005
Location: Mongolia
Member
did you apply any latest patch?

I suggest to apply recommended patch (forex: CPUJAN2008).

Report message to a moderator

Previous Topic: Regd: Tuning required
Next Topic: oracle data base in multi application server and multi sites
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Jun 27 20:17:06 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.