Solved

kill Oracle query process that runs too long.

Posted on 2014-03-02
20
2,612 Views
Last Modified: 2014-06-08
Dear all,

From DB point of view, if a query running abnormally long, DBA would like to kill that process and email the query out which long running query has been killed to the develper and DBA.

Please share your experience.
0
Comment
Question by:marrowyung
20 Comments
 
LVL 36

Expert Comment

by:Geert Gruwez
Comment Utility
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
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
"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 ?
0
 
LVL 36

Expert Comment

by:Geert Gruwez
Comment Utility
> 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 ?
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
"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 ?
0
 
LVL 36

Expert Comment

by:Geert Gruwez
Comment Utility
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
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
"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!
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
any tools to find out slow query ? we still didn'ot go there yet.
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
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
/

Open in new window


?

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

Assisted Solution

by:regmigrant
regmigrant earned 50 total points
Comment Utility
That will give you a snapshot of the longest 10 queries in the cache at the time you execute it but what you seem to be looking for is a way of monitoring all queries, getting an average time and then checking to see if that's exceeded.

Whilst entirely possible this level of monitoring is likely to have its own impacts and would need a support system to react to it.Part of the test/release process could include this kind of intensive performance monitoring and dba feedback but once its in production you should be using more holistic approaches unless performance degrades sharply

If you follow the above advice to reach a root cause indication then consider monitoring for that cause happening in future rather than relying on a query running for a long time, killing it and then finding out it was caused by disk space which is much more readily monitored and can be handled outside the DB service.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 36

Expert Comment

by:Geert Gruwez
Comment Utility
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
top waiting queries todaytop events todaytop buffergets todaydeadlocks across all monitored databases
the crossdb feature lets you see at a glance where tuning is needed
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
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.
0
 
LVL 36

Expert Comment

by:Geert Gruwez
Comment Utility
> 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
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
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;

Open in new window


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

Author Comment

by:marrowyung
Comment Utility
"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?
0
 
LVL 36

Accepted Solution

by:
Geert Gruwez earned 450 total points
Comment Utility
in version 10/11 of application manager there are different tabs
> sessions / locks / asm

the tab ASM shows the data returned by queries on v$asm table which are active by default install
if asm is not installed and a query is launched on a v$asm table then an oracle trace file will be generated with "Failure to make connection" error
> basically the same as what oracle_ocm jobs do with default install of 10/11
https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=86jd55i1m_9&_afrLoop=6751577159215
0
 
LVL 36

Expert Comment

by:Geert Gruwez
Comment Utility
> high level tools
well yeah, for things like it's up or down, tablespace full, and ... er
that's about it
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
thanks for your option on application manager and I will ask them if the current version still have the same problem.
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
0
 
LVL 22

Expert Comment

by:Steve Wales
Comment Utility
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".
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now