Solved

longer than 5 minutes active running queries.

Posted on 2001-07-05
7
1,262 Views
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.
0
Comment
Question by:sidcap
7 Comments
 
LVL 3

Author Comment

by:sidcap
Comment Utility
I need to log the killed sqls too.
0
 
LVL 9

Expert Comment

by:dbalaski
Comment Utility
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:
SESSIONS_PER_USER  
Specify the number of concurrent sessions to which you want to limit the user.
CPU_PER_SESSION  
Specify the CPU time limit for a session, expressed in hundredth of seconds.
CPU_PER_CALL  
Specify the CPU time limit for a call (a parse, execute, or fetch), expressed in hundredths of seconds.  
CONNECT_TIME  
Specify the total elapsed time limit for a session, expressed in minutes.  
IDLE_TIME  
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.  
LOGICAL_READS_PER_SESSION  
Specify the permitted number of data blocks read in a session, including blocks read from memory and disk.
LOGICAL_READS_PER_CALL  
Specify the permitted the number of data blocks read for a call to process a SQL statement (a parse, execute, or fetch).
PRIVATE_SGA  
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
Library
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!

sincerely,
dBalaski

 

0
 
LVL 2

Expert Comment

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

Cheers
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 2

Expert Comment

by:jammalk
Comment Utility
Oops  I repeated KILL SESSION twice... in the above query.. ! Just type only once..!
0
 
LVL 3

Author Comment

by:sidcap
Comment Utility
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.
0
 
LVL 2

Accepted Solution

by:
jammalk earned 100 total points
Comment Utility
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
CONTEXT  NUMBER  Context
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..!
0
 
LVL 3

Expert Comment

by:UsamaMunir
Comment Utility
Hey SIDCAP!
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
 
-------
SADDR                
SID            
USER_NAME      
ADDRESS        
HASH_VALUE
SQL_TEXT      
---------
against killed session take sql_text and logit
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

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
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.

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

10 Experts available now in Live!

Get 1:1 Help Now