longer than 5 minutes active running queries.

Posted on 2001-07-05
Last Modified: 2007-11-27
Hi colleagues:

What should I do in order to find those sessions running for more than 5 minutes ?

I need to trap these and kill them.

Thanks in advance, SIDCAP.
Question by:sidcap
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Author Comment

ID: 6257159
I need to log the killed sqls too.

Expert Comment

ID: 6257255
Two options:

1)  set up profiles and grant them to the user:
Profiles are a set of limits on database resources.
Some of the resource parameters are:
Specify the number of concurrent sessions to which you want to limit the user.
Specify the CPU time limit for a session, expressed in hundredth of seconds.
Specify the CPU time limit for a call (a parse, execute, or fetch), expressed in hundredths of seconds.  
Specify the total elapsed time limit for a session, expressed in minutes.  
Specify the permitted periods of continuous inactive time during a session, expressed in minutes. Long-running queries and other operations are not subject to this limit.  
Specify the permitted number of data blocks read in a session, including blocks read from memory and disk.
Specify the permitted the number of data blocks read for a call to process a SQL statement (a parse, execute, or fetch).
Specify the amount of private space a session can allocate in the shared pool of the system global area (SGA), expressed in bytes.

Another option to look into would be using
Database Resource Manager  (new in oracle 8i)
It is described in the Oracle8i Administrator's Guide
Release 2 (8.1.6) Part Number A76956-01
chapter 25:  The Database Resource Manager

Here is a clip:
What is the Database Resource Manager?
Typically, when database resource allocation decisions are left to the operating system, you may encounter the following problems:
*  Excessive overhead
*  Excessive overhead results from operating system context switching between Oracle servers when the number of servers is high.
*  Inefficient scheduling
*  The operating system de-schedules Oracle servers while they hold latches, which is inefficient.
*  Poor allocation of resources
*  The operating system fails to allocate CPU resources appropriately among tasks of varying importance.
*  Inability to manage database-specific resources, such as parallel slaves and active sessions

Oracle's Database Resource Manager helps to overcome these problems by allowing the database more control over how machine resources are allocated.

Specifically, using the Database Resource Manager, you can:
* Guarantee certain users a minimum amount of processing resources regardless of the load on the system and the number of users.
* Distribute available processing resources by allocating percentages of CPU time to different users and applications. In a data warehouse, a higher percentage may be given to ROLAP applications than to batch jobs.
* Limit the degree of parallelism that a set of users can use.
* Configure an instance to use a particular method of allocating resources. A DBA can dynamically change the method, for example, from a daytime setup to a nighttime setup, without having to shut down and restart the instance.
* The elements of Oracle's database resource management, which you define through the Database Resource

Hope these suggestions help!




Expert Comment

ID: 6257282
Spool the output of the following query & run it with ALTER SYSTEM privileges.
select 'alter system kill session kill session ' || '''' ||
 sid || ',' || serial# || '''' || ';'
 from v$session
where (sysdate - logon_time)*24*60 > 5 ;
If you would like to see the sessions that are running for more than 5 minutes, use the following query:

select sid, serial#, to_char(sysdate,'HH24:MI:SS') "CURRENT",
to_char(logon_time,'HH24:MI:SS') "LOGON",
(sysdate - logon_time)*24*60 from v$session
 where (sysdate - logon_time)*24*60 > 5 ;

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Expert Comment

ID: 6257290
Oops  I repeated KILL SESSION twice... in the above query.. ! Just type only once..!

Author Comment

ID: 6257355
Thanks jammalk, but v$session.logon_time does not tell me that there is a query actually running.

a session may change from INACTIVE to ACTIVE and then INACTIVE again, but logon_time never changes.

So, (sysdate - logon_time)*24*60 > 5 does not necessary imply that there is actually a 5 minutes running query.

thanks anyway, sidcap.

Accepted Solution

jammalk earned 100 total points
ID: 6257412
OK...! You mentioned in your query to kill those session running for more than 5 minutes..! If u want sesisons that are running transactions for more than 5 minutes.. , then...follow the following procedure..!

If you want to find out the sessions that have long running transactions (in your case more than 5 min).., use the view V$SESSION_LONGOPS.

Column  Datatype  Description  
SID     NUMBER    Session identifier
SERIAL# NUMBER    Session serial number
OPNAME  VARCHAR2(64) Brief description of the operation
TARGET  VARCHAR2(64) The object on which the operation is carried out
TARGET_DESC  VARCHAR2(32)  Description of the target
SOFAR    NUMBER  The units of work done so far
TOTALWORK  NUMBER   The total units of work
UNITS  VARCHAR2(32)  The units of measurement
START_TIME  DATE  The starting time of operation
LAST_UPDATE_TIME  DATE  Time when statistics last updated
TIME_REMAINING  NUMBER  Estimate (in seconds) of time remaining for the operation to complete.
ELAPSED_SECONDS   NUMBER  The number of elapsed seconds from the start of operations
MESSAGE  VARCHAR2(512)  Statistics summary message
USERNAME  VARCHAR2(30)  User ID of the user performing the operation.
SQL_ADDRESS  RAW(4) Used with the value of the SQL_HASH_VALUE column to identify the SQL statement associated with the operation.
SQL_HASH_VALUE  NUMER  Used with the value of the SQL_ADDRESS column to identify the SQL statement associated with the operation.
QCSID  NUMBER  Session identifier of the parallel coordinator.
I think you can use the columns, SID, SERIAL#, START_TIME to get the desired result...!

But, to make use of these view, there are some restrictions:
1. Must be using Cost-based optimizer.
2. Set the parameters, TIMED_STATISTICS or SQL_TRACE parameter to TRUE.
3. Gather statistics for your objects with the ANALYZE statement or the DBMS_STATS package.

This view displays the status of various operations that run for longer than 6 seconds (in absolute time). These operations currently include many backup and recovery functions, statistics gathering, and query execution (this is what u need) , and more operations are added for every Oracle release.

You can modify the previously mentioned query with this V$SESSION_LONGOPS and get the results..u want..!

Expert Comment

ID: 6268388
v$session_Longops does the trick alright

Now for ur second question
if u want to record the Killed sql queries

use V$Open_cursor

it contains following columns
against killed session take sql_text and logit

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction A previously published article on Experts Exchange ("Joins in Oracle", makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

733 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