Finding bad SQL's !!! urgent help plz !!!

[Striker99]

All,

At irregular intervals i have some SQL's which takes around 60-300 secconds to execute. Usually, the same SQL takes less than 30 seconds to execute. But at irregular intervals it takes more time. Is there a way i can identify the BAD SQL's at the end of the day. Or could you please advice me what might be causing this. I am using oracle 7.3.4 release.
Are there any standard scripts i can use to identify these kind of problems.

It is very important for me, if you can help asap plz.

Thanks a million,
striker99

 

[lfoata]

Could be many thing, for examples :
- Busy spimdles
- Shared spool size too small
- Updates running at the time of the query
- Locks on objects

You can take a look at v$sql to find tout what was running at the same time as your request and then debug. Too bad I.T. is not cash business

Luc Foata
Unix sysadmin, Oracle DBA

 

[Striker99]

Thanks Luc,

The problem is i do not know what time the query would take more time than usual to run. As you said it would have been easy to monitor if i know what time this was happening.

cheers,
striker99

 

[cwong5]

Hum. If you are executing over an ODBC connection, you can set it up to ask for return of query longer than a certain time. That's only if you are connecting via ODBC.

 

[rcurva]

You can do this by enabling Trace at session level. Then TKPROF all traces produced for the day, afterwards, review the explained output to see which time and which SQL is taking the longest time.

To enable trace at session level;
DBMS_SESSION.SET_SQL_TRACE(TRUE);

To disable;
DBMS_SESSION.SET_SQL_TRACE(FALSE);

Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War