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
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 Go to previous messageGo to next message
BlackSwan
Messages: 22711
Registered: January 2009
Senior Member
enable SQL_TRACE to capture problematic SQL & from which you can produce EXPLAIN PLAN
Re: How to capture the problem with some sql timeout [message #571533 is a reply to message #571529] Mon, 26 November 2012 21:24 Go to previous messageGo to next message
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.
Re: How to capture the problem with some sql timeout [message #571537 is a reply to message #571533] Mon, 26 November 2012 22:28 Go to previous messageGo to next message
BlackSwan
Messages: 22711
Registered: January 2009
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.
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
pvsarat
Messages: 8
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

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


Current Time: Wed Aug 27 20:06:55 CDT 2014

Total time taken to generate the page: 0.37920 seconds