OraFAQ Forum: Performance Tuning » How to capture the problem with some sql timeout

Home » RDBMS Server » Performance Tuning » How to capture the problem with some sql timeout (Oracle 10.2.0.4, AIX 5.3 64bit)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to capture the problem with some sql timeout [message #571528]

Mon, 26 November 2012 21:01

snowball
Messages: 229
Registered: April 2006
Location: China

Senior Member

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 #571529 is a reply to message #571528]

Mon, 26 November 2012 21:07

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

enable SQL_TRACE to capture problematic SQL & from which you can produce EXPLAIN PLAN

Report message to a moderator

Re: How to capture the problem with some sql timeout [message #571533 is a reply to message #571529]

Mon, 26 November 2012 21:24

snowball
Messages: 229
Registered: April 2006
Location: China

Senior Member

Hi, BlackSwan
The problem is there're thounds of sessions, we don't really know which session at which time will timeout. Global sql trace will cost a lot of space and IOs. So it might be a little difficulty to do this and also the risky of this tracing.

Thanks very much.

Report message to a moderator

Re: How to capture the problem with some sql timeout [message #571537 is a reply to message #571533]

Mon, 26 November 2012 22:28

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>So it might be a little difficulty to do this and also the risky of this tracing.
SQL_TRACE will produce the desired results.
The trace files can be discarded immediately after you decide that they contain no needed details.

It depends upon how badly a solution is required, or if it is decided to live with it going unresolved.

Do you have any better alternative to finding the root cause?
If so, ignore my suggestion & proceed to do it faster, cheaper or easier than what I proposed.

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

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

Report message to a moderator

Previous Topic:	TKprof generation for short duration
Next Topic:	Query rewrite - high temp utilization

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Nov 24 11:13:26 CST 2024