vol_maxio Effect on large "dd" Operations and Database Performance

[Alzhy]

HP-UX 11.11 default is 256. We're users of VxVM for DB Storage where we have eitehr EVA or XP 12000 Storage. The latter we use 8-way stripes of 32 to 64 K width.

We recently discovered that increasing vol_maxio from 256 to 16384 allowed us a 5 to 8 fold fold increase in dd throughput using 1MB blocks. Our dd-writes appear to have jumped from doing 256K write blocks to 2MB Write blocks.

Will this cause certain Oracle/DB Transactions to improve? Will it affect certain OLTP like transactions?

[Eric Antunes]

The answer is No. OLTP transactions happens in buffer cache, not I/O related.

Datawarehouse may benefit.

[Alzhy]

Eric, what about OLTP Transactions that behave like batch? Our "OLTP" environment during the day always feel the burden of excess baggage processing that seems to behave like batch. Theer are bursts that each read or write as I have observed them from the volume manager layer (after the kernel paramater change) approaches 2MB...

We're still gathering comparative stats before and after the change. BUt the dd performance alone is amazing! We use dd a lot to perform hotbackups..

[Steven E. Protter]

Shalom,

I don't think this will help at all.

Oracle does as much work as it can in the SGA. Changing the SGA settings will have the greatest impact on ORacle performance.

Once outside the SGA, Oracle does not respond to changes in the buffer cache for databases on cooked filesystems. Bill Hassell mentioned to me that on 11.23 HP-UX a larger buffer cache has improved oracle performance on databases on cooked filesystems.

I've found changing this setting has in the lab had no impact on OLTP Oracle databases.

SEP

[Eric Antunes]

Hi Nelson,

From my experience there are "only" 3 possible reasons for OLTP transactions behaving like batch. I'm going to point them in the order I think they should be addressed :

1 - In a Cost Based Optimizer, like the ones in RDBMS 9i onwards, the absence of statistics is ugly for the overall performance:

select dt.* from dba_tables dt
where owner not in ('SYS','SYSTEM')
and not exists( select 1
from dba_tab_col_statistics dtcs
where dtcs.table_name = dt.table_name)
order by dt.owner
2 - Bad SQL. Running statspack will identify the heavy ones, if they exists;

3 - Tablespace fragmentation. I'm still struggling with this one... :-(

[Volker Borowski]

Well,

at least I do not think it will hurt.
And your backups might run like hell.

Volker

[TwoProc]

Nelson,

I'd bet that if you increased the multiblock read count in Oracle it would benefit - HOWEVER, this pretty much would only be fun for testing as increasing the multiblock read count increases the servers affection for running full table scans for most anything/everytyhing. As someone once said, for data warehouse applications maybe, but maybe not for OLTP.

[Eric Antunes]

Hi Nelson,

The previous sql about statistics isn't the better one...

Instead run this 2 to check for table and indexes without estimated statistics:

/*** Tables ***/

select table_name, owner, num_rows, blocks, avg_row_len,
to_char(last_analyzed, 'mm/dd/yyyy hh24:mi:ss')
from dba_tables
where owner not in ('SYS','SYSTEM')
and last_analyzed is null;


/*** Indexes ***/


select index_name, owner, num_rows, distinct_keys, leaf_blocks, clustering_factor, blevel, avg_leaf_blocks_per_key
from dba_indexes
where owner not in ('SYS','SYSTEM')
order by index_name;

Here pay attention to high clustering_factor/num_rows ratios because the higher this ratio, the less likely the optimizer is to select this index.

To give you an idea, I have a Materialized view with 330K rows and a clustering factor of 67% and it takes 3 days to refresh!! I'm struggling with it...

Finally, I agree with TwoProc about db_file_multiblock_read_count: 8 or 16 at maximum is the best for most OLTP environments.

Best Regards,

Eric Antunes

[Alzhy]

Greetings...

A week into this change on one server. The change is dramatic during onlines (where we have lots or batch-like processes going on) but more during batch.

Our DFMR is set at 8. Do you think there's still good increasing this value to 16?

These DB is about 1.5 TB large, on 16 cpu x 128GB RAM. Cooked Filesystems but with DirectIO enabled (db_writers = 1, and surprisingly disk_asynh_io=true???! coz our DBA sez it will just be ignord..).. 22+GB of SGA...

I attach our entire Oracle param file if any of you see something of interest.

Thanks!

[Alzhy]

Forgot the attachment..

Here it is.. our Oracle Param File