kill Oracle query process that runs too long.

nope, not at all

a dba would look at WHY it is running so long,
then check if it's possible to tune the statement by adding indexes first
if so, depending on the agreements ..., adds the index or gives advice on which index to add

if not, then the dba looks if rewriting the statement gives better performance
and passes the rewritten statement on to the team

> finding the owner of the statement is another matter
usually... explaining why a certain statement performs badly allows developers to build better queries

"nope, not at all

a dba would look at WHY it is running so long,
then check if it's possible to tune the statement by adding indexes first"

I knew, but kill it first and that's why I said email that out, as we can have something to follow up. I just I am doing a SP in MS SQL that, as right nwo we have Oracel query from the Oracle gateway that , from time to time that, it will make the query rendering engine stop working !

But when we know it, the application running in MS SQL already dead, so we should have something CHECK and DO ahead, then find out the problem after we back to office. Then once we got the message showing what is the query and we will route the message the necessary party for them to fix their......!!

that's why I need it.

"if not, then the dba looks if rewriting the statement gives better performance
and passes the rewritten statement on to the team"

yeah, but this is some king of prst task for the MONITORING.

or how can you know something abnormal was happened during your sleep time and KILLED your system alreayd ?

> how can you know something abnormal was happened
logging
every developers language has the capability of catching errors and logging them

even oracle database has an alert.log

if the developers think this logging is unimportant then you now have proof of the opposite
no program is infallable if you use an AK47 on it ...

MSSQL ...
anything left in DBA_2PC_PENDING ?

"logging
every developers language has the capability of catching errors and logging them"

but I am not developer, I am infrastructuer DBA.

"MSSQL ...
anything left in DBA_2PC_PENDING ? "

no. we just have one case and the email does help as we then follow the action immediately once we back to office.

ok, assume that alert log is very good (where is it?), then it is the only view to watch the slow query out ?

a little confused at this side
> but I am not developer, I am infrastructure DBA.

for MSSQL or Oracle ?

there is 2 things i see going on while
someone is doing a query from oracle to mssql by gateway
1: it's a long running query
2: sometimes it fails

1: for the long running query > this is for an oracle tuning dba
check the execution plan, check where there are missing indexes, add them
check if rewriting the query solves the "time" problem, etc, etc, etc ...

2: for sometimes it fails
did the oracle or mssql go down, was it reachable all the time, network failures ?
problem with the memory allocation ?

> based on those 2 above scenario, the next thing i guess is sending a mail with the analysis and possible solution,
but for this, you need a cause first

"a little confused at this side
> but I am not developer, I am infrastructure DBA.

for MSSQL or Oracle ?"

MS SQL and MYSQL

": it's a long running query"

it is not, we found a problems that it seems if the DB_link is not close each time it calles to the MSSQL side, the FETCH+APIxxxxxx statementb will appear on the MS SQL side and it can fill up the storage of MS SQL server. then MS SQL stop working.

"1: for the long running query > this is for an oracle tuning dba
check the execution plan, check where there are missing indexes, add them
check if rewriting the query solves the "time" problem, etc, etc, etc ..."

yes, we are doing it.

"did the oracle or mssql go down, was it reachable all the time, network failures ?
problem with the memory allocation ?"

disk space problem!

ok, but we should go back at the beginning, any tools to find out abnormal running query, like many hours already but usaully it should run15 minutues only !

in MS SQL, we have tools like that which I am running for a while and perfect!

any tools to find out slow query ? we still didn'ot go there yet.

SELECT * FROM
(SELECT
    sql_fulltext,
    sql_id,
    child_number,
    disk_reads,
    executions,
    first_load_time,
    last_load_time
FROM    v$sql
ORDER BY elapsed_time DESC)
WHERE ROWNUM < 10
/

Select allOpen in new window

?

stackoverflow.com/questions/316812/top-5-time-consuming-sql-queries-in-oracle

tools ? yeah sure. ... sorry about the upcoming advertising ... :)

i work together with a freelance oracle tuning dba
he has an own custom built oracle monitor for tuning
it's a great tool to find all the troubles, but it's not free
if you don't have any tools yet (and enterprise manager is way too expensive)
if you're interested i can give a few screen shots
and possibly send a test version

all it needs is a windows server to put it on, and a user on the oracle database
(with connect and select_catalog_role priviliges)
after a few minutes it will be able to give graphs on the waiters and what the most resource consuming queries are.
this monitor will show what the worst queries are

tuning those queries is not automatic and requires interpretation by someone with oracle tuning knowledge

here are a few screenshots

the crossdb feature lets you see at a glance where tuning is needed

regmigrant,

"If you follow the above advice to reach a root cause indication then consider monitoring for that cause happening in future"

That;s why I am asking for a tools/method to find it out first and then analysis, the script I am showing also means that I will just then built a SP to find out which query has been running more than 30 minutes and then email it out, then kill it.

The SP and then will be schedule to run every 30 minutes to find out if everything is under control.

So that script making sense to find out the first list of query that running slow and then start to look at the index?

Geert Gruwez,

are you a sales person ? what is the link ?

yeah, I can find a lot of tools like this, like Toad for oracle and we can consider that tools too ! application manager from managengine can also provide simple tools,

but what I needs is a real time monitoring tools.

> application manager from managengine
i have that too, but it's no good for tuning
besides ... it causes problem if you don't use ASM
check the amount of times you can find connection lost in the trace files

there is no link at all for the tool, it's not public either
he doesn't have a marketing department yet ...
he sent a powerpoint representation for the people intrested:

ITSS-overview.zip

one thing, is that mean:

  select 
   blocking_session, 
   sid, 
   serial#, 
   wait_class,

   seconds_in_wait
from 
   v$session

where 
   blocking_session is not NULL

order by 
   blocking_session;

Select allOpen in new window

can find real time query problem then V$sql, which show only cached result ?

"i have that too, but it's no good for tuning"

but a good high level tools to find out problem first, right?

"besides ... it causes problem if you don't use ASM"

cause what problem? we don't add the ASM monitor and it still have problem? What version are you using ? did you talk to them ?

"check the amount of times you can find connection lost in the trace files"

What is this mean? still talking about application manager?

> high level tools
well yeah, for things like it's up or down, tablespace full, and ... er
that's about it

thanks for your option on application manager and I will ask them if the current version still have the same problem.

"https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=86jd55i1m_9&_afrLoop=6751577159215 "

it seems bring me to other page which I dont' understand.

I've requested that this question be deleted for the following reason:

The question has either no comments or not enough useful information to be called an "answer".