| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> OLTP slow response time, any ideas?
Hi all,
I've got an OLTP application on an Oracle9i (02) database. The problem
is that the Oracle istance is often performing slowly also with simple
queries, no matter if those queries are run against heavy accessed
tables (with about 20 transaction/sec on xa protocol) or other rarely
accessed tables.
The sensation is that the whole system is slow.
Of course I noticed the problem 'cause of slow response time for users
of our OLTP application, so at first I run some query to view if dml
operations were getting blocked each other, but I found only normal
blocking activity (we're working with xa, so some minor blocking issue
can be easily noticed) and user's query being blocking only for
maximum half a second.
I run a query like this multiple times:
SELECT
s.username, --who
s.user#, --UserID
s.sid, --Session
s.serial#,
s.status,
s.type,
s.program,
s.module,
s.action,
s.client_info,
s.sql_address,
s.sql_hash_value,
s.logon_time,
s.lockwait,
s.terminal,
(select sw.wait_time from v$session_wait sw where sw.sid =
s.sid) as waiting_time, -- Waiting time if current blocking session
is itself waiting
sq.sql_text
FROM v$session s, v$sqlarea sq
WHERE s.sid IN
(
SELECT DISTINCT
l.sid
FROM v$lock l
WHERE l.lmode > 0 -- only blocking queries
AND l.block > 0 -- track locks waiting for locks
)
and I've got always results like these:
SQL> / no rows selected
SQL> / no rows selected
SQL> / no rows selected
SQL> / no rows selected
SQL> / USERNAME USER# SID STATUS TYPE WAITING_TIME SQL_TEXT --------- ------ --- ------ ---- ------------
SPORTEL 50 65 INACTIVE USER 0 begin :1 :=
JAVA_XA.xa_commit_new
(:2,:3,:4,:5);
end;
SQL> / no rows selected
SQL> / no rows selected
SQL> / no rows selected
SQL> / no rows selected
So, I've started looking at event tables and I've found many timeouts for some events log related. I've written this query:
select
((se.TOTAL_TIMEOUTS/se.TOTAL_WAITS)*100) as Tot_percent,
se.*
from
v$system_event se
order by
Tot_percent DESC;
and got these (truncated) results:
TOT_PERCENT EVENT TOT_WAITS TOT_TIMEOUTSTIME_WAITED
--------- ---------------------------------------------------------------- 100 control file heartbeat 1 1 399 100 write complete waits 106 106 10630 100 index block split 1 1 1 99,9950001 undo segment extension 100002 99997 56 99,9746863 pmon timer 27653 27646 8378040 98,7829615 buffer deadlock 493 487 3 91,6666667 checkpoint completed 12 11 5972 77,974026 log file switch completion 15400 12008 1300899 74,0045279 log file parallel write 172707 127811 285327 62,2282609 smon timer 368 229 7777064 62,0620754 log buffer space 7507 4659 544457 54,3048208 rdbms ipc message 443828 241020 43923609 47,2259191 db file parallel write 24044 11355 1952606 38,2131622 enqueue 86779 33161 8786893 34,2451301 log file sync 353551 121074 14530550 22,5343114 latch free 42260 9523 100308 18,0847379 buffer busy waits 17324 3133 391062 5,93189964 library cache load lock 5580 331 129425 5,67282322 rdbms ipc reply 758 43 10625 1,83486239 row cache lock 109 2 1298 ,709677419 LGWR wait for redo copy 1550 11 72 0 wait list latch free 6 0 6
Of course I've noticed that too many log-related operations have got timeout problems, but I cannot understand why. Here is how my system paramas regarding the log are set:
log_archive_start TRUE log_archive_dest /redo_arch/ora06e log_archive_duplex_dest log_archive_max_processes 2 log_archive_min_succeed_dest 1 standby_archive_dest ?/dbs/arch log_archive_trace 0 fal_server fal_client log_archive_format log%t_%s.arc log_buffer 1310720 log_checkpoint_interval 0 log_checkpoint_timeout 0 archive_lag_target 0 log_parallelism 1 db_files 200 db_file_multiblock_read_count 16 read_only_open_delayed FALSE cluster_database FALSE parallel_server FALSE fast_start_io_target 0 fast_start_mttr_target 0
Do you have any idea?
Thank you
Arag Received on Wed Jan 29 2003 - 10:11:56 CST
 |
 |