| log file sync puzzle [message #64158] |
Thu, 23 December 2004 09:16 |
Jim Korinko
Messages: 1
Registered: December 2004
|
Junior Member |
|
|
Running Oracle 9.2.5 on AIX box (V5.2). Constant log file sync waits. SQL query on parameters is: select name, value from v$parameter
where name like 'log_c%' or name like 'fast_start%';
NAME VALUE
----------------------------- --------------------
log_checkpoint_interval 0
log_checkpoint_timeout 1800
fast_start_io_target 0
fast_start_mttr_target 300
log_checkpoints_to_alert FALSE
fast_start_parallel_rollback LOW
6 rows selected.
log_buffer 524288
redo log info:SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------------- ---------- --- ---------------- ------------- ---------------
1 1 15101 104,857,600 2 NO CURRENT 601437537 23-DEC-04
2 1 15099 104,857,600 2 YES INACTIVE 601429261 23-DEC-04
3 1 15098 104,857,600 2 YES INACTIVE 601364460 23-DEC-04
4 1 15100 104,857,600 2 YES ACTIVE 601437496 23-DEC-04
5 1 15097 104,857,600 2 YES INACTIVE 601300943 23-DEC-04
SQl used to see what some sessions are doing from site:select sid, event, time_waited/100
from v$session_event
order by sid, time_waited desc
/
results from (7) SID's from query above:
SID EVENT TIME_WAITED/100
---------- -------------------------------------------- ---------------
61 SQL*Net message from client 9128.6
log file sync .23
SQL*Net message to client .22
db file sequential read .15
latch free .05
SQL*Net more data from client 0
68 SQL*Net message from client 49072.73
db file sequential read 480.92
db file scattered read 193.03
log file sync 13.94
direct path write 11.6
direct path read 9.68
latch free .43
enqueue .15
SQL*Net break/reset to client .13
SQL*Net message to client .08
buffer busy waits .02
SQL*Net more data to client 0
77 SQL*Net message from client 2880.33
db file sequential read 3
log file sync .65
db file scattered read .07
latch free 0
SQL*Net message to client 0
80 SQL*Net message from client 8371.18
latch free .72
SQL*Net message to client .24
db file sequential read .09
81 SQL*Net message from client 30012.3
db file sequential read .86
SQL*Net message to client .23
latch free .13
db file scattered read .01
82 SQL*Net message from client 12675.57
log file sync 3.84
db file sequential read 3.3
db file scattered read .01
SQL*Net message to client .01
latch free 0
84 SQL*Net message from client 11962.55
SQL*Net more data from client 73.87
log file sync 41.21
latch free .07
log file switch completion .07
db file sequential read .04
SQL*Net message to client .03
buffer busy waits 0
SQL*Net more data to client 0
*************************************************
As seen, not all these sessions have log file sync. I realize the db file sequential are lack of index usage. (Cust. has another vendor for appl, can't see the logic on SQL code in SNAPS). From other notes I reviewed I would like to do following init.ora parameter changes: increase log_buffer to 1 Meg from 512K, set log_checkpoint_timeout to 0 from 1800,not sure of what to do with log_checkpoint_interval I find this in Oracle doc: Specifying a value of 0 (zero) for LOG_CHECKPOINT_INTERVAL has the same effect as setting the parameter to infinity and causes the parameter to be ignored. Only nonzero values of this parameter are considered meaningful. Also can you suggest what I could try for the fast_start_io_target now at 0 and the fast_start_mttr_target now at 300 in accordance with the changes to changes I propose above regarding log_buffer and log_checkpoint_timeout? I have learned that too many changes at same time will do more harm than good. At the same time customer only allows changes once per week to their web application. So, I am asking what would the suggestion be as to which parameters would bring the most relief ? Thank you for reviewing my request.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
