Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


longer than 5 minutes active running queries.

Posted on 2001-07-05
Medium Priority
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 ;

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.


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 300 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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
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.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

636 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