Effect of Waits

[vijay8282]

Hi

I ran utlbstat/estat utility.

If i go to the waits statistics i can se good no of waits for following
Count Wait Time Avg

SQL*Net message from client 4146925 61225252 14.76
SQL*Net more data to client 999874 6427 .01
log file switch completion 449 41941 93.41

Acc to oracle these are idle events and can be skipped.

But i can see lot of waittime in this, maximum infact.

So do they mean that this wait time does not contribute to database performance at all.

Help Me.

Vijay

[chao_ping]

The first wait event is an idle event, and it can be ignored.
But the second, means that oracle is waiting on the network, to transfer data to client, you should take a look at this event, maybe transfer data in batch.
As for the third,not quite understand the detail meaning. I never see such events in my system, and the document did not say much about it.Maybe your disk io is slow, or much ckpt data to write to disk?

[AndyEPS]

Vijay

The log file switch completion wait event usually occurs when a transaction is trying to write redo information but the is insufficient space in the current log file for this. The process signals the LGWR (log writer) and waits until the LGWR has finished the task of switching to a new log. Once a new log is active, the original process can continue by writing redo to the newly activated log.

Another cause of log switching could be code issuing the SQL ALTER SYSTEM SWITCH LOGFILE (but this is "speciality" SQL and not something that goes into everyday code).

If you have performance problems then you might want to investigate tuning the logging. If you're using Oracle 7 try turning the checkpoint process on (init.ora parameter checkpoint_process=TRUE) as this reduces the burden of updating all data files with the checkpoint info that occurs when a log switch occurs.

Andy

[TomazZ]

Maybe log writer is waiting for next redo log to get archived.
If this is the case, create more redo log groups.

Tomaz

[vijay8282]

Hi,

I hv 4 redologgroups, File size is 20MB.

I am running Oracle 8i. So CKPT is ON by default.

No one is issuing ALTER SYSTEM SWITCH LOGFILE.

Can I conclude that these waits are not giving any bottleneck ?


Bye
Vijay

[TomazZ]

To exclude archiving contention (if you are in ARCHIVELOG mode, of course) you can check connected as sysdba with 'arhive log list' command.
'Next log sequence to archive' should not be equal to 'Oldest online log sequence'.

Waits on my busiest database are 5 times lower. But it doesn't necessarily mean anything.
You could be allright.

Tomaz