vol_maxio Effect on large "dd" Operations and Database Performance

[Volker Borowski]

Hi Nelson,

well a few things are remarkable.
They might as well be good adjusted for your system, but I throw in my thoughts anyway.

log_buffer looks rather small to me.
We had a change on a 16TB DB on Friday to
bump this one up from 8MB to 28 MB.
I think 512K is way too low. Change it to 2M as a starter and watch v$session for "log buffer" waitevents to change. Value must be a multiple of 512!

You have some duplicates:
disk_async_io (which is "false" as the second value !)
optimizer features_enable (which is 9.x as the second value)

I think with autmatic pga_ enabled, sort_area_size should not be set. Not sure, what happens if both are set. I would recomend to use automatic pga_.

4,5GB for shared pool is quite a lot. If you have tons of stored procedures an lots of non-bind-variable-statements, you'll probably ok.

db_cache_size...
since you have a lot of RAM, I'd recommend to bump this one up beyond "real" 16GB (not 16000000000), cut down shared pool in case you can not spend the RAM. Both are dynamic within the borders of sga_max_size, so you can downsize shared pool and increase db_cache_size "on the fly" without bouncing the DB.
I do not find the reference right now, may be I can deliver this a bit later, at least I'll try.
There are a couple of internal parameters, that depend on db_cache_size like internal number ob allocation latches and else. These values increase if you raise db_cachesize above certain thresholds, an I mind to remember the 16GB was such.

optimizer_index_cost_adj with 1 can be discussed. In SAP Environment (OLTP) this one is recomended to be between 10 and 20.
Twist this one with care. Quite a lot of execution plans may change all at once if you twist this screw.

As you intend to provide better big-io performance, db_file_multiblock_read_count might be set to a higher value (16 ?) to tell oracle about you better capabilities to do large ios. Carefull: increasing this one tends to tell Oracle to favor full table scans, esp. for small tables. That might not be a fault, if the io can really been done fast.

If you have such a big db_cache, check, if you really need oracles db_cache_advisor.
That is a good tool to monitor, when something changes, but after things are set up and stable, that guy is costy.
Check out db_cache_advise.
I'll tend to set this one to "READY" so it is possible to activate it, if needed.
If set to OFF and the db is started with this setting, it might not be possible to activate it without restart. "READY" allocates the memory that this funtion needs, but disables the statistic collection for the advisor.

Good luck
Volker

[Eric Antunes]

Hi Nelson,

Yes, Oracle will ignore disk_asynch_io=TRUE if you don't have raw devices (TRUE is the default).

As Volker already said, there are some duplicates (I don't know how Oracle handles this) that your DBA should eliminate in the init.ora.

Now, adding some considerations:

parallel_max_servers should be at maximum x*2*, since you have 16, it should be decreased to 32.

About optimizer_index_cost_adj and optimizer_index_caching, Oracle recommends to remove them from the init.ora for 9i and 10g, at least for their apps 11i (I don't have them in mine init.ora).

fast_start_mttr_target is to low and thus generating more checkpoints: Oracle recommends 1800 for EBS 11i which is the value I also have.

pga_aggregate_target is way too low: it is set to 640M in my little 100Gb database... Start with 1Gb and monitor the SQL workarea histogram in statspack or EM: multipass executions must be avoided.

Finally, you can see what are the most waited non-idle events in your database with the following sql:

select *
from v$system_event
where event not like 'rdbms%'
and event not like 'SQL%'
and event not like '%time%'
and event not like '%pipe get%'
and event not like '%dle%'
and event not like '%queue%'
and event not like 'jobq%'
order by time_waited desc

Best Regards,

Eric Antunes

[Volker Borowski]

Hi once again,

I found my Reference for the number of latches. It was SAP-Note 767414.

#Latches =
Next largest power of 2
(Size of the buffer pool/1048576)

which results in the latches being doubled at 16,515625 GB :-( not 16 GB.

Buffer Pool >= 8875147264 Byte -> 16384 Latches
Buffer Pool >= 17733517312 Byte -> 32768 Latches

So in your case, since you are close to the 16GB, I would tend to go for the double number of latches, even if you do not need 1G of the space in general.

Volker