io performance: Oracle Archiver file allocation

[MArtin at unixera dot d]

( Resend.. from HP-UX: WORKLOAD/RESOURCE MANAGEMENT )
Hello all ..

Being a Tru64 knowledgeable, I have a question on :
HOW does HP--UX allocate file space. ?

On a datawarehouse machine , migrating from wildfire (Tru64 ) to Superdome (HP-UX) we see a lot of fluctuation in io performance towards the SAN.
Every 2 to 3 minutes, corresponding to archive file beeing extended by oracle, there is a heavy io load.
The oracle installation extends with one Gbyte at the time.

On tru64 this was never a problem, it seemed like it extended on the fly.

DOES HP-UX do some checking/writing of zeroes, that could cause this io burst at every extension of the archive file ??

IF so, WHAT could be the recommandation to avoid this problem. ?
(oracle , kernel or filesys params)

Best regards
Martin Roende Andersen, denmark

[Michael Schulte zur Sur]

Hi,

do I get you right, every 2-3 minutes a new archive log with 1gb?

Michael

[Volker Borowski]

... or do you mean autoextend of a datafile ? Check your autoallocation clause!

Volker

[MArtin at unixera dot d]

Michael ...

Yes, every 2 to 3 minutes the archive log is extended, due to the amount of data put into the database.
This is a datawarehouse machine.

4 paths into the EVA SAN , and 16-20 CPU's on a superdome to write the data.

regards Martin

[Michael Schulte zur Sur]

Hi Martin,

still wondering. Let me resume:
You have redologs size 1gb.
every 2-3 min you get a switch log and the archiver creates an archivelog of 1gb?

Is this correct? The term extend in the context on archive logging confused me.

2-3 min between log switches is to little. Each time the db has to make a checkpoint, causing a lot of I/O.

can you post show parameter?

Michael

[MArtin at unixera dot d]

I will post them, need info from installation, please hang on ..

regards Martin.
I will clarify
Sorry, yes, log switching..

[MArtin at unixera dot d]

Here it is .. init.ora


as attachment ,,,
regards Martin

[Hein van den Heuvel]

I suspect checkpoint activity drives this behaviour.

( btw... cursor_space_for_time = FALSE
Looks like you have enough memory, switch to true? )

> fast_start_mttr_target integer 300

That setting normally makes Oracle do 'softw checkpoints' unless LOG_CHECKPOINT_INTERVAL is set low which is it not or log_checkpoint_timeout is set low. That last one is default at 1800 second = 1/2 hour.

You have log_checkpoints_to_alert set to true (as IMHO you should), so maybe investigate the alert log at 'funny times' to see if you can correlate it with checkpoints?

How big are the redo log? Are they filling, and thus forcing checkpoints, too quickly?
Judging by your inputs sofar it looks like your redo file sizes should be in the 2 - 5GB space. How many mb/sec to the redo drives?

Your log_buffer is set to 65536000 (65M).
That's impressive and probably overkill, but I don't know whether it would hurt.
The larger TPC benchmarks use smaller numbers. I'd suggest trying 4 - 10 MB but have not strong argument for that.

You have timed_statistics = true. So you are burning CPU to get good insight numbers, are you using those numbers? Are statspacks being generated and analyzed? Share a few top sections here? (Like the first page with overview + top wait and the redo/checkpoint counters?

http://tahiti.oracle.com/pls/db901/db901.drilldown?word=fast_start_mttr_target&book=a90190

btw 2... >> mts_dispatchers =(PROTOCOL=TCP) (SERVICE=DWH01P

Are you really using mts? Is it worth it / required by the usage or perhaps a carried over 'tradition'. Give it a shot without?


hope this helps some,
Hein.

[Michael Schulte zur Sur]

Hi,

I still have to ask. How often do you get a log switch?

Michael

[MArtin at unixera dot d]

Thanks for the drill down Hein ..

I forward this to the DBA at the site , and try to get the answers.

Michael: Same thing , I ask the DBA of how often it log switches, I recall they use the 1800 as standard, but maybe not on this system.

In the mean time, Internal HP communication within the Multivendor Customer Service Engineering, (You might know CV from DK part of MCSE) has found that there is an piece of code in HPUX disk driver that causes DOUBLE work to be done on the archiver disk.

So that path to 11i is awaited.

I post you separately with details from CV

Regards Martin..

[Michael Schulte zur Sur]

Martin,

as soon one redolog is full, oracle creates an archive logfile, regardless of oracle settings. Have a look at the modification dates of the archives. That will give you a picture of how often this happens.
One log per 2-3min is too often because each switchlog requires a checkpoint and forces the database to empty all dirty blocks. If you have another switchlog before the last checkpoint is though, then you have checkpoint timeout and all this is not good for the performance.

So, what can you tell us about the fequency of the switchlogs?

greetings,

Michael

[MArtin at unixera dot d]

Michael ..
It is/was switching every 4 minutes.

Right now things have loosened up a bit , because they have changed blocksize towards the diskarea with archive and redologs, and they have seen improvements.
The recommendation from HP, be posted in a later entry ..

regards MArtin

[MArtin at unixera dot d]

The HP MSE recomandation was to set the filesystem block size to the same as the Oracle blocksize for archivelog.

This has made good improvement for the datawarehouse operation, and the customer is sofar satisfied.

Thanks for all your answers so far..