| sga,pga parameter resizing [message #523077] |
Thu, 15 September 2011 08:44  |
dbaoracleinin
Messages: 19
Registered: November 2010
Location: abc
|
Junior Member |
|
|
Hello Guys,
I gone throught docs,forums etc for sga,pga parameter resizing
some doubts are unclear
1)what is ROT for increasing sga and pga in manual configurations
(no sga_target)
2)for shared pool
advisory is enough for indicating resizing
(v$..shared_pool_advice)
or other method
3)when execute to parse ratio in awr is low,and soft parse
high,application code issue but what can be done at db side
increasing session_cache_cursor can help? if yes how to decide
how much to increase and any negative impact
Thanks in advance
|
|
|
|
|
|
| Re: sga,pga parameter resizing [message #525852 is a reply to message #523800] |
Thu, 06 October 2011 04:42  |
 |
LNossov
Messages: 318
Registered: July 2011
Location: Germany
|
Senior Member |
|
|
It isn't strange, because your question is a little bit too common. Configuration of sga and pga depends from many factors and isn't normally a big problem for experienced dba. I would not try to clarify such questions in forum, because this needs a long discussion and nobody has normally time for that. I think, it is a reason, why your question is still without any answer.
In forums you should ask about solution for your concrete problems. For education purposes you can read documentation, books and attend courses.
I can answer briefly your third question. Yes, increasing of session_cached_cursors could help. Don't set this parameter too high, because
- it can cause a fragmentation in shared pool and
- increase a parsing time by searching an appropriate cursor in a very big session cache.
Normally values between 20 (default for 10g) and 200 would be big enough. In some situation (workarounds for bugs, etc.) this value could be increased up to 400 but I would not advise to set this parameter much higher. You can check the number of opened cursors per session in your application for estimation of value for this parameter.
[Updated on: Thu, 06 October 2011 06:16] Report message to a moderator |
|
|
|
|
|
|
|
| Re: sga,pga parameter resizing [message #526694 is a reply to message #523800] |
Wed, 12 October 2011 09:34 |
dbaoracleinin
Messages: 19
Registered: November 2010
Location: abc
|
Junior Member |
|
|
Thanks for reply
for same purpose for analysing if more sga/pga required
I need help for interpreteting below
1)
select
PGA_TARGET_FOR_ESTIMATE,
PGA_TARGET_FACTOR,
ADVICE_STATUS,
ESTD_PGA_CACHE_HIT_PERCENTAGE
from v$pga_target_advice order by PGA_TARGET_FOR_ESTIMATE;
output of this query shows after particular value of
PGA_TARGET_FOR_ESTIMATE ,ESTD_PGA_CACHE_HIT_PERCENTAGE doesnt change ,should we consider this as optimum value of pga which we can set
2) select * from v$pgastat;
gives values of col as below-
aggregate PGA target parameter aggregate PGA auto target global memory bound
total PGA inuse
total PGA allocated
maximum PGA allocated
total freeable PGA memory
process count
max processes count
PGA memory freed back to OS total PGA used for auto workareas
maximum PGA used for auto workareas
total PGA used for manual workareas
maximum PGA used for manual workareas
over allocation count
extra bytes read/written cache hit percentage
recompute count (total)
out of this which is useful to check and what it means like total pga in use,does it shows current/dyanamic pga in use
if so i could see very less value compare to total pga available
does it means my pga is oversized or not required to be increased
3) As per one of oracle docs i am selecting v$event_histogram
for event ='enq: TX - row lock contention'
what does bucket(wait_time_milli) and wait_count "signifies"
4) first few rows of v$latch shows as below
is anything worrying in this ( if wait_time in microseconds
shared pool latch doesnt seems to be much) and what does
slave class create means ,do need to do anything abt it
NAME WAIT_TIME
-------------------------------------------------- ----------
slave class create 169434266
shared pool 26710142
cache buffers chains 24554819
process allocation 23033021
cache buffer handles 13813326
session allocation 7335203
library cache lock 6088370
library cache 4057734
5)sga is showing 24% free and shared pool showing 60 mb free out of 700 mb total size
by querying v$sgastat
what does it signifies
Thanks again
and
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
