I've been laughing because I live 1 block from Moscone Center. It was closer for me to walk between the conference and my house than it was to walk between some of the sessions. Today, I saw the other side of that coin. I got ordered back to work and missed the last day of the conference, (and Chen Shapira's presentation!!!)
What was going on at work? Not much - as in not what should have been. I like looking at the performance monitor on Enterprise Manager for a quick glance at what is going on. It wasn't a pretty picture.
Clicking on the 'Blocking Sessions' tab I saw that that the log writer session was blocking various other sessions.
I went into the alert log and was pointed to a log writer trace file. Inside the trace file I found
*** 2008-09-25 15:28:24.239
Warning: log write time 15460ms, size 6999KB
*** 2008-09-25 15:28:24.836
Warning: log write time 590ms, size 6898KB
*** 2008-09-25 15:28:29.852
Warning: log write time 5020ms, size 6999KB
I looked at metalink and got
" The above warning messages has been introduced in 10.2.0.4 patchset. This warning message will be generated only if the log write time is more than 500 ms and it will be written to the lgwr trace file .
"These messages are very much expected in 10.2.0.4 database in case the log write is more than 500 ms. This is a warning which means that the write process is not as fast as it intented to be . So probably you need to check if the disk is slow or not or for any potential OS causes. "
We just upgraded to 10.2.0.4. Our storage is across the great divide at another company. We are often short of answers other than, "Everything is configured correctly". With quite a bit of work we have gotten a pair of LUNS allocated for redo logs.
As a test, I moved the redo logs from the SAN to a NSF drive - NOT one that should be allocated to redo. Here was the immediate result:
The log writer waits stopped. Compliments from management. A request from management to storage management to move the entire database to this kind of storage, everyone is happy
Tonight I looked at the trace file:
*** 2008-09-25 22:53:34.154
Warning: log write time 750ms, size 0KB
*** 2008-09-25 22:53:35.943
Warning: log write time 1770ms, size 28KB
*** 2008-09-25 22:53:39.889
Warning: log write time 940ms, size 0KB
Log writer is taking forever, and it isn't even doing anything!
To be continued..... and detective suggestions welcome!
A few basic notes on Oracle Database Administration.
- ▼ September (5)