| DB performing very slowly, [message #318743] |
Wed, 07 May 2008 16:48  |
me_maddy
Messages: 4
Registered: April 2008 |
Junior 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.
|
|
|
| Re: DB performing very slowly, [message #318744 is a reply to message #318743 ] |
Wed, 07 May 2008 16:53  |
anacedent
Messages: 5027
Registered: July 2005
Location: surf meets turf in 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]
|
|
|
| Re: DB performing very slowly, [message #318747 is a reply to message #318743 ] |
Wed, 07 May 2008 17:07 |
me_maddy
Messages: 4
Registered: April 2008 |
Junior 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.
|
|
|
| Re: DB performing very slowly, [message #318748 is a reply to message #318743 ] |
Wed, 07 May 2008 17:16 |
anacedent
Messages: 5027
Registered: July 2005
Location: surf meets turf in 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.
|
|
|
| Re: DB performing very slowly, [message #318751 is a reply to message #318743 ] |
Wed, 07 May 2008 17:32 |
me_maddy
Messages: 4
Registered: April 2008 |
Junior 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.
|
|
|
| Re: DB performing very slowly, [message #318752 is a reply to message #318743 ] |
Wed, 07 May 2008 17:41 |
anacedent
Messages: 5027
Registered: July 2005
Location: surf meets turf in 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!
|
|
|
|
| Re: DB performing very slowly, [message #318774 is a reply to message #318751 ] |
Wed, 07 May 2008 23:08 |
rleishman
Messages: 2563
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
|
|
|
| Re: DB performing very slowly, [message #319393 is a reply to message #318743 ] |
Sat, 10 May 2008 09:24 |
michael_bialik
Messages: 419
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).
|
|
|
| Re: DB performing very slowly, [message #319970 is a reply to message #318743 ] |
Tue, 13 May 2008 09:28 |
enkhbold
Messages: 36
Registered: July 2005
Location: Mongolia |
Member |
|
|
did you apply any latest patch?
I suggest to apply recommended patch (forex: CPUJAN2008).
|
|
|
]
Members
Search
Help
Register
Login
Home
