Sergio, good morning!
first I would like to congratulate you by the answer. It was practically an entire article. Altissima quality content.
Good,
answering the questions:
From here, would you know how to say or what has changed? Some suggestions / examples: (I am recent in the company, but from what I realized has not changed virtually nothing at a good time. I also realized that the installation of Oracle in this "server / desktop" was performed with NEXT, Next Finish around a year and half behind. All configuration is standard.
The accesses are internal in ERP written in Delph 3.
- a new system was deployed; .
- Maintenance has been implemented; >> Maintenance has been implemented.
- increased the number of users' accesses; >> Quantity of users access increased, but, I believe it would not be a concern. So large due not to reach at least 30 sessions on the server.
- New resources were added to the application, such as a processing batch routine. >> No new routines have been implemented.
] The query suggested in the Burlean article brought these results:
Free Memory: 614400
<null> / alias: 614400
Another analysis was the code written in Delph and a conversation with the programmers responsible for the maintenance of the system. The application is all written with BID variables.
Some other checks follows:
- indicated that must be greater than 90%, otherwise increase the db_cache_size boot parameter.
Select all
select 100*(1 - (v3.value / (v1.value + v2.value))) "Cache Hit Ratio [%]"
from v$sysstat v1, v$sysstat v2, v$sysstat v3
where
v1.name = 'db block gets' and
v2.name = 'consistent gets' and
v3.name = 'physical reads'
Select all
DB Cache Data 10:00 15:00
DB Cache 15/10/2012 97,8 98,8
DB Cache 16/10/2012 98,6 98
DB Cache 18/10/2012 98,7 98,4
DB Cache 19/10/2012 98,51 97,8
- The waiting number must always be zero. Otherwise, increase the size of the log_redo, until the return of the number to zero. (Red Log Space Requests)
Select all
Select name, value from v$sysstat
Where name in ('redo log space requests', 'redo log space wait time');
Select all
Tempo Espera Redo Log Data 10:00 15:00
Tempo Espera Redo Log 15/10/2012 7 21
Tempo Espera Redo Log 16/10/2012 27 41
Tempo Espera Redo Log 18/10/2012 8 80
Tempo Espera Redo Log 19/10/2012 7 36
- Increase buffer size if it is less than 5,000.
Select all
Select Round(e.value/s.value,5) "Redo Log Ratio"
From v$sysstat s, v$sysstat e
Where s.name = 'redo log space requests'
and e.name = 'redo entries';
- When "Hit Ratio" <99%, Increase Shared_pool_Size
Select all
select sum(pins) "Executions",sum(reloads) "Cache Misses while Executing",round((sum(pins)/(sum(reloads)+sum(pins)))*100,2)
"Hit Ratio, %" from V$LIBRARYCACHE;
Select all
select sum(pins) "Executions",sum(reloads) "Cache Misses while Executing",round((sum(pins)/(sum(reloads)+sum(pins)))*100,2)
"Hit Ratio, %" from V$LIBRARYCACHE;
99]]
Select all
Acesso Cache Banco "Hit Ratio" Data 10:00 15:00
Acesso Cache Banco "Hit Ratio" 15/10/2012 99,99% 100%
Acesso Cache Banco "Hit Ratio" 16/10/2012 99,99 100
Acesso Cache Banco "Hit Ratio" 18/10/2012 99,99 100
Acesso Cache Banco "Hit Ratio" 19/10/2012 99,99 100
- When "Misses ratio"> 1% Increase shared_pool_size
Select all
select sum(pins) "Executions",sum(reloads) "Cache Misses while Executing",round(sum(reloads)/sum(pins)*100,2)
AS "Misses Ratio, %" from V$LIBRARYCACHE;
or [9]]
Select all
Acesso disco "Misses Ratio" Data 10:00 15:00
Acesso disco "Misses Ratio" 15/10/2012 0,734 0,342
Acesso disco "Misses Ratio" 16/10/2012 0,645 0,41
Acesso disco "Misses Ratio" 18/10/2012 0,621 0,37
Acesso disco "Misses Ratio" 19/10/2012 0,606 0,32
I have not yet been able to identify the bottleneck, but one of the checks that calls me a lot of attention is about disk access Although the hit ratio demonstrates me 99.99%
It seems that the recommended is: if number of disc accesses is much higher than zero increase the Sort_AREA_SIZE
[12]]
I am not yet very safe to carry out such modifications.
