Perforamnce issue... (dbc_min_pct and dbc_max_pct)

[Eric Antunes]

Hi again,

nproc and other parameters are better controlled has a formula because if you change the basic parameters, the depending ones will also get changed. For nproc the formula (20+8*MAXUSERS) is suitable for Oracle OLTP environement. But your value seems enough.

Your db_cache_size is very very big, are you sure you need it this huge?? Try to reduce it to the half and see if you get better performance...

PS: Online JFS enables you to extend online (without rebooting and entering single mode) the volume sizes. It also allows you to use advanced tunning options for file systems such as mincache, etc...

Best Regards,

Eric Antunes

[Eric Antunes]

About your db_cache_size, execute the following SQL statements and check the ratios:

select (bbc.total_waits*100/(cg.value+dbg.value)) "Buff busy ratio ind. <= 0,007"
from v$system_event bbc,
v$sysstat cg, v$sysstat dbg
where bbc.event='buffer busy waits'
and cg.name ='consistent gets'
and dbg.name='db block gets';

select round(((1-(sum(decode(name, 'physical reads', value,0)) /
(sum(decode(name,'db block gets', value,0))+
(sum(decode(name, 'consistent gets', value, 0))))))*100),2)
|| '%' "Buffer Cache Hit Ratio > 95%"
from v$sysstat;

select round(100 * sum(getmisses) / sum(gets), 2) "Misses Pct < 5%"
from v$rowcache
having sum(gets) > 1000;

select (1-(sum(getmisses)/sum(gets))) * 100 "Hit Ratio"
from v$rowcache;

Also this one in sqlplus (in Oracle 8 this was a svrmgrl command only) and post the results here:

SQL> connect / as sysdba;
Connected.
SQL> SHOW SGA;

Best Regards,

Eric Antunes

[unimedriopreto]

Hi Eric.
I'm sending values from sqls that you told me to execute.
I think that returned values are ok.
I'm curious about what you say about db_cache_size. A bigger value will not improve performance?
My buffer cache hit is higth, if I increase db_cache_size, Will not I increase a little more this number? Isn't It better?

Thanks.

[Eric Antunes]

Hi again,

"I'm curious about what you say about db_cache_size. A bigger value will not improve performance?
My buffer cache hit is higth, if I increase db_cache_size, Will not I increase a little more this number? Isn't It better?"

No. There is a point at which you don't get any performance value just increasing the SGA. There is much more things to check than SGA:

- sort_area_size=4194304. You must be aware that this is a EACH SESSION value (that's why you need that huge SGA...)

- Log swichs/checkpoint (what is the rate? Set "log_checkpoint_alert = true" to analyse this in the alert_.log)

- Tablespace Fragmentation (How is it? OEM Tablespace Manager)

- Who are the eavy guys and what are they doing?

"select substr(s.username,1,20) "User Name",
s.osuser "OS User",
s.status "Status",
lockwait "Lock Wait",
substr(s.program,1,30) "Program",
substr(s.machine,1,15) "Machine",
substr(du.user_name,1,20) "Apps User Name",
s.logon_time "Connect Time",
p.program "P Program",
si.physical_reads "Physical Reads",
si.block_gets "Block Gets",
si.consistent_gets "Consistent Gets",
si.block_changes "Block Changes",
si.consistent_changes "Consistent Changes",
s.process "Process PID",
p.spid, p.pid, s.serial#, si.sid
from sys.v_$sess_io si, sys.v_$session s, sys.v_$process p, system.dba_usersess du
where s.username is not null and
si.sid(+)=s.sid
and p.addr(+)=s.paddr
and (si.consistent_gets > 10000 or si.block_gets > 10000)
and du.sid=s.sid
order by si.consistent_gets+si.block_gets desc"

- Chainning? What is the ratio?

- Etc...

Best Regards,

Eric Antunes

[Eric Antunes]

Hi,

From the last query remove the du columns since system.dba_usersess it is one of the customized views I have...

[Hein van den Heuvel]

Eric,

I beg to differ with many, if not most, of your points and approaches. Send me an Email if you want to discuss a little bit,

>> "I'm curious about what you say about db_cache_size. A bigger value will not improve performance?
My buffer cache hit is higth, if I increase db_cache_size, Will not I increase a little more this number? Isn't It better?"

I believe so. Even if your hit rate is 99,9%, but there are still hundreds of reads per second, then possibly more cache is needed and/or better query plans.

For further reading check out documents like: http://www.hotsos.com/e-library/abstract.php?id=6

Eric> No. There is a point at which you don't get any performance value just increasing the SGA.

Hmmm, that's hard to imagine, unless the memory can be used better elsewhere (processes, dbc)

Eric> There is much more things to check than SGA:

Absolutely

Eric> - sort_area_size=4194304. You must be aware that this is a EACH SESSION value (that's why you need that huge SGA...)

Per process yes, from the SGA... NO. From the PGA_aggregates.
Please be sure to read up on the interaction of the individual setting line sort_area_size vs the global pga_aggregate-target.

Eric> - Log swichs/checkpoint (what is the rate? Set "log_checkpoint_alert = true" to analyse this in the alert_.log)

Right. Too often we see very small redo log forcing early checkpoint. But here MTTR is used which does put a slightly different spin on things.

Statspack data would be the best to understand where to focus next, but that quickly turns in to 'real work' vs 'trying to help a little'.

Hope this helps someone somewhere,
Regards,

Hein.

[unimedriopreto]

Hi Hein, before all, I'm very thankfull for your help.

I'd altered dbc_min and max_pct yesterday, before that, vmstat shows that I had 50 Mb of free memory, and swapinfo -tam, shows that I was not using swap, I did not change oracle memory parameters since that.

So, should I increase dbc_min and max_pct, lets talk... to 5 and 10... And increase sga max size to 3Gb? I did that another time and saw that system become using swap. (It's better that oracle's process uses swap than they have a little memory configuration?).

Back to dbc_max and min_pct...
Excuse me for that I wrote... When I openned this thread, sar command shows that %iddle was 10% (90% work). But 2 hours agor I discovered that one bad sql instruction was executing and consuming my server (an update of an application that was bad wrote).
Now, after application's correction, %iddle comes to 65% (sar -ud 3 30), so, I think that dbc_min and max_pct did not influence (too much) in system degradation. I'll increase to 5 and 10% and still looking system performance.

I could increase db_cache_size, but I'm still confusing about swap use for oracle processes.
I could increase pga_aggregate_target too, but I'll need to tune memory between db_cache_size and pga_aggregate_target.

About >> max_dispatchers=100.
My system always use 5 dispatchers, the max_dispatchers parameters will not influence in system performance... Will it??? (ok... if a huge number of connections are made at same tame.... It probably will not occur...).

>> max_shared_servers=50
It is setted to 50 becouse one application has an bug, that causes enqueue... I asked for correction but It not comes until now... so I increase this number to free another applications to run. (it was setted to 20 and my database crashes)...

About add and delete indexes, I'd passed an explanation to applications providers, showing what indexes are needed and what are not used... I'm waiting theyer response...

I'll take a closer look at executions plans.

My biggest doubt is about swap...

Thanks for response.
And excuse me for my long response time.

[unimedriopreto]

Eric this is executions results...
SQL>
SQL> select (bbc.total_waits*100/(cg.value+dbg.value)) "Buff busy ratio ind. <= 0,007"
2 from v$system_event bbc,
3 v$sysstat cg, v$sysstat dbg
4 where bbc.event='buffer busy waits'
5 and cg.name ='consistent gets'
6 and dbg.name='db block gets';

Buff busy ratio ind. <= 0,007
-----------------------------
0,000457823537197533

SQL> ;
SQL>
SQL> select round(((1-(sum(decode(name, 'physical reads', value,0)) /
2 (sum(decode(name,'db block gets', value,0))+
3 (sum(decode(name, 'consistent gets', value, 0))))))*100),2)
4 || '%' "Buffer Cache Hit Ratio > 95%"
5 from v$sysstat;

Buffer Cache Hit Ratio > 95%
-----------------------------------------
96,66%

SQL>
SQL> select round(100 * sum(getmisses) / sum(gets), 2) "Misses Pct < 5%"
2 from v$rowcache
3 having sum(gets) > 1000;

Misses Pct < 5%
---------------
0,75

SQL>
SQL> select (1-(sum(getmisses)/sum(gets))) * 100 "Hit Ratio"
2 from v$rowcache;

Hit Ratio
----------
99,2453743

Attached will be your last requisition about who are executing what....
but I need to do some modifications, becouse I do not have one table.

Tanks...
Jefferson.

[LiPEnS]

Hi
Pleass post result of:
#swlist |grep OnLineJFS
and
#mount -v

Regards

LiPEnS

[Eric Antunes]

Hein,

Be confortable to desagree but then Oracle also desagrees with you until Oracle 8i: read Metalink Note 100709.1 (Top 8 init.ora Parameters Affecting Performance) and tell me something about the recomendations. They are wrong or 9i is a revolution??


About Buffer Cache, better query plans is definitively the way to go. Try to solve with more buffer cache a full scan in a millions records table!

From the above refered note:

"...If the value is set too high, swapping will began to occur on the OS and the
system may come to a halt."


About SGA:

"...We have discussed having the Shared_Pool_Size set too low, but adverse effects
may comes from setting the Shared_Pool_Size too high as well. Having the
shared pool set too large increase the probability of latch contention when the
database needs to find a free peace of memory to load a new statement.
You can query v$sgastat to show the available free memory. This will tell you
memory is being wasted. As an example, let's look at the following problem:

select name, bytes/1024/1024 "Size in MB"
from v$sgastat
where name='free memory';

You should see output similar to the following:

NAME Size in MB
Free memory 39.6002884

What this return would tell you is that there is 39M of free memory in the
shared pool, which would mean that the shared pool is being under utilized.
If the shared pool was 70M, over half of it would be under utilized. This
memory could be allocated elsewhere..."

Elsewhere is of course OS, applications, etc...

About sort_area_size:

Yes, it is outside the SGA but it is memory used by each user process for sorting...

From Note:

"...The SORT_AREA_SIZE is an oft-misunderstood init.ora parameter. Many believe
that this parameter is an amount allocated for the users as a whole in terms
of sorting. Not true, the SORT_AREA_SIZE is what Oracle allocates per
user process for sorting data. Unlike the two previous parameters, the
SORT_AREA_SIZE is outside of the SGA..."

Best Regards,

Eric Antunes