Home » RDBMS Server » Performance Tuning » How to capture the problem with some sql timeout (Oracle 10.2.0.4, AIX 5.3 64bit)
How to capture the problem with some sql timeout [message #571528] |
Mon, 26 November 2012 21:01 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
snowball
Messages: 229 Registered: April 2006 Location: China
|
Senior Member |
![xueyuan.luo@gmail.com](/forum/theme/orafaq/images/google.png)
|
|
Hi, gurus
We met this problem:
During the same period every month, we have a OLAP action on a Oracle RAC(2 nodes) 10.2.0.4 ,at the same time, the OLTP will keep on.
Now we're facing this problem, when a OLAP action processing serveral days, some sqls related to some bussiness services will timeout(this timeout means the bussiness service time required those sqls return result within 10s), but now some sqls return result after severial minues.
I have carefully reivew those timemout sqls, but I find those sql randomly appeared at different client sides and the timeout sqls also random, but related to the bussiness services. The explain of this sql is good, this sql can return result immediate(less than 1s) without oltp action.
I have check the AWR, ASH reports during issue happened, but at the same time, I don't think the wait events will be affected by the timeout sqls. Because at the same time, some long running , high cost olap sqls are running.
I managed to do the sql trace on some clients, but failed to tracing, because eventhough they're timeout, but after developers and me realized, the sql may be already done.
I've been exhausted to design a way to capture those timeout sqls since it's not a common issue and hard to reproduce at regular time. Any good idea ?
Thanks very much.
[Updated on: Mon, 26 November 2012 21:02] Report message to a moderator
|
|
|
|
|
|
Re: How to capture the problem with some sql timeout [message #571543 is a reply to message #571533] |
Tue, 27 November 2012 01:11 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.gravatar.com/avatar/4004f0c5de86ec701ba9dc62d0649413?s=64&d=mm&r=g) |
pvsarat
Messages: 10 Registered: October 2012 Location: CHENNAI
|
Junior Member |
|
|
Hi,
I suggest please take those sql which are taking more time, and execute from your own account and trace that account.
Here is the example
You can trace your account with below query with username
Find your sid,serial,username with the query:
============================================
select substr(s.sid,1,4)||','||substr(s.serial#,1,6) "SID,Serial#",p.spid,
substr(s.username,1,10) "User",substr(s.osuser,1,10) "OS Name",status
from v$session s,v$process p where s.username is not null and s.username not in ('SYSTEM','SYS') and p.addr=s.paddr order by s.osuser asc
Tace a session:
==============
EXECUTE DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION ('sid','serial#',TRUE)
TK proof:
=========
tkprof <trace file>.trc <desired>.txt explain=system/pwd
|
|
|
Goto Forum:
Current Time: Sat Sep 07 19:27:48 CDT 2024
|