kill Oracle query process that runs too long.

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.
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Geert GOracle dbaCommented:
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
marrowyungSenior Technical architecture (Data)Author Commented:
"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
Geert GOracle dbaCommented:
> 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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

marrowyungSenior Technical architecture (Data)Author Commented:
"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
Geert GOracle dbaCommented:
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
marrowyungSenior Technical architecture (Data)Author Commented:
"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
marrowyungSenior Technical architecture (Data)Author Commented:
any tools to find out slow query ? we still didn'ot go there yet.
0
marrowyungSenior Technical architecture (Data)Author Commented:
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
regmigrantCommented:
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
Geert GOracle dbaCommented:
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
marrowyungSenior Technical architecture (Data)Author Commented:
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
Geert GOracle dbaCommented:
> 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
marrowyungSenior Technical architecture (Data)Author Commented:
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
marrowyungSenior Technical architecture (Data)Author Commented:
"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
Geert GOracle dbaCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Geert GOracle dbaCommented:
> high level tools
well yeah, for things like it's up or down, tablespace full, and ... er
that's about it
0
marrowyungSenior Technical architecture (Data)Author Commented:
thanks for your option on application manager and I will ask them if the current version still have the same problem.
0
marrowyungSenior Technical architecture (Data)Author Commented:
0
Steve WalesSenior Database AdministratorCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.