Tom,

We are trying to use the concept of connect_time / cpu_session_limit but it is not working for some reason.

Although there is a resource limit on logged on user, the session that is executing a long running query does not stop executing by sniping the session.

The user has following resource limit set. It is meant allow the user to run query for max 2 mins. If it takes more than 2min. it should terminate the session and return error.

ALTER PROFILE CTPROFILE2 LIMIT
SESSIONS_PER_USER Unlimited
CPU_PER_SESSION 12000
CPU_PER_CALL Unlimited
CONNECT_TIME 2
IDLE_TIME 30
LOGICAL_READS_PER_SESSION Unlimited
LOGICAL_READS_PER_CALL Unlimited
COMPOSITE_LIMIT Unlimited
PRIVATE_SGA Unlimited
FAILED_LOGIN_ATTEMPTS Unlimited
PASSWORD_LIFE_TIME Unlimited
PASSWORD_REUSE_TIME Unlimited
PASSWORD_REUSE_MAX Unlimited
PASSWORD_LOCK_TIME Unlimited
PASSWORD_GRACE_TIME Unlimited
PASSWORD_VERIFY_FUNCTION NULL
/

The session is not disconnecting after specified amount of connect time resource limit or cpu session limit. The query keeps on executing and then terminates but the time exceeds intended 2 min limit.

Can you please tell us what is the problem?
How can we set the resource limit so that the total run time (parse, execute, fetch) does not exceed 'n' no. of minutes? We want to set up more than one profile that would limit the user to 1 min., 5 min., 60 min.

Thanks

Now we have changed the profile as follows,

ALTER PROFILE CTPROFILE2 LIMIT
SESSIONS_PER_USER Unlimited
CPU_PER_SESSION 12000
CPU_PER_CALL Unlimited
CONNECT_TIME Unlimited
IDLE_TIME Unlimited
LOGICAL_READS_PER_SESSION Unlimited
LOGICAL_READS_PER_CALL Unlimited
COMPOSITE_LIMIT Unlimited
PRIVATE_SGA Unlimited
FAILED_LOGIN_ATTEMPTS Unlimited
PASSWORD_LIFE_TIME Unlimited
PASSWORD_REUSE_TIME Unlimited
PASSWORD_REUSE_MAX Unlimited
PASSWORD_LOCK_TIME Unlimited
PASSWORD_GRACE_TIME Unlimited
PASSWORD_VERIFY_FUNCTION NULL
/

What we are expecting is that if a query starts at 10:00am, it should complete before 10:02am. Otherwise the query operation (parse, execute and fetch) should stop and session should disconnect

But we still have same problem that query keeps executing after 2min. We tried with CPU_PER_CALL = 12000 also. But it did not solve this problem

Please help.

Thanks

Thanks for your reply.

Yes, the resource_limit = true is set in init.ora

Now we understand that the CPU time is not exactly same as 'watch time'. But then how do we allow user to execute only for 2 min 'watch time'. The reason is that the long running query we are testing takes about 25min 'watch time' before it terminates with 'cpu per session exceeded error' and during that time the server performance is slow. So what we want to achieve here is that if the query is running more than 2 min 'watch time' we want to terminate it after 2 mins. We want to limit the user only on 'time' and not other database resources.

Is there any way to set such a limit? Is there any workaround for this?

Please help.

Thanks

Thanks for your reply. It has been very useful.

Our application requires various report sqls execute on user requests. So to IO or any other resource consumption will vary from one SELECT statement to another. So we are trying the workaround suggested by you.

To get control over the time allowed for a session to execute a query now a 'logon trigger' is created. 

The problem we are facing now is that there is error while re-connecting to the same user in sqlplus (ORA-24315: illegal attribute type). However, in another sqlplus session it allows to login without any error.

The logon script is as follows,

CREATE OR REPLACE TRIGGER SYS.TRIGGER_CTREP_LOGON
 AFTER 
 LOGON
 ON DATABASE
 WHEN (USER LIKE 'CTREP%')
DECLARE
 v_JobNum   NUMBER;
 v_sid        NUMBER;
 v_serial    NUMBER;
 v_cmd      VARCHAR2(300);
 v_interval NUMBER;
 TYPE refcursession IS REF CURSOR;
 cursession refcursession;
BEGIN
 IF USERENV('sessionid') = '1' THEN
    RETURN;
 END IF;
 OPEN cursession FOR 
 'select a.sid,a.serial# from v$session a WHERE audsid = USERENV(''sessionid'')';
 FETCH cursession INTO v_sid,v_serial;
 CLOSE cursession;
 v_cmd := 'sys.sp_ctrep_kill_session(' 
       || trim(to_char(v_sid)) || ',' || trim(to_char(v_serial)) || ');';
 SELECT DECODE(USER,'CTREP1',1,'CTREP2',2,'CTREP3',60,1)
 INTO v_interval
 FROM dual;
 DBMS_JOB.SUBMIT(v_JobNum,v_cmd,
    SYSDATE + v_interval/(24*60),NULL);
 COMMIT;
END;
/

The procedure 'sys.sp_ctrep_kill_session' kills session.

CREATE OR REPLACE PROCEDURE sys.sp_ctrep_kill_session 
(psid IN NUMBER,
 pserial IN NUMBER)
AS
BEGIN
   -- PLLSQL to Log session being killed
     EXECUTE IMMEDIATE 'alter system kill session ''' ||
             psid || ',' || pserial || '''';
   -- PLLSQL to Log session killed successfully
EXCEPTION 
WHEN OTHERS THEN
   -- PLLSQL to Log failure 
   RAISE;
END;
/

This is what we are doing to test the whole thing.
1. Logged in sqlplus as user 'CTREP2'.
2. Start execution of SELECT statement.
3. The 'kill session' job kicks in and the session is killed after 2 mins.
This is copied from Sqlplus.
<========== Start ==========>
ERROR:
ORA-00028: your session has been killed



30 rows selected.

SQL> connect ctrep2/ctrep2@dctia
ERROR:
ORA-01012: not logged on


ERROR:
ORA-24315: illegal attribute type


Warning: You are no longer connected to ORACLE.
<========== End ==========>

After this sqlplus does not log in as any user. Is the kill session through a job causing this? How to we solve this?

 

We are doing this in sqlplus only for testing the trigger/job.

In our application we are using ".NET Oracle Client Provider" to connect and execute the queries. We are using SHARED server connections from client. Also database connectinon pooling is being done at client end.

1.Are there any disadvantages of killing session?
2.What will the side effects of killing session? Will Oracle server gracefully release the resources used on server for the killed sessions?
3.The sessions are marked as status='KILLED'. If number of such session increase in V$SESSION, will it cross the maximum session limit (meaning will it somehow stop allowing other users from loging in) which will be a real show stopper?

Please help.

Thanks
Prashant

I am developing .NET application. Threshold time is 400 ms. I want to stop my query when it reaches threshold limit. It should stop working and should come back to next statement with or without any error messages.

I can set cpu_per_call in profile in unit of 10ms. my questions are:
1. How can I set similer limit for watch time.
2. How can I measure cpu_time used by my query;

Thanks

The correspondance over killing of sessions was indeed a eye opener. It was similar to the one I was wanting to query, and one which I am facing now. It would be of much help if my query is also responded to. We are using an application built in VB. And it has around 100 users using it. At mid part of the day, when all are using it I find that the sessions get full that there is no possibility for a new user to log on. I however, cant understand the concept as to why the sessions keep getting created for a user in a random as to 20 -30 sessions, which on an average should be only 5-6. I have checked my coding of the application but am not able to trace as to why this happens. Could you suggest. It will be of great help

<>
3.The sessions are marked as status='KILLED'. If number of such session increase
in V$SESSION, will it cross the maximum session limit (meaning will it somehow
stop allowing other users from loging in) which will be a real show stopper?
</>

Far ago i hitted this problem while using similar technics.
Consider 'alter system kill session .. IMMEDIATE' - it was right solution in my case.

Hi Tom,

Thanks for answers. We have similar requirement as mentioned by Prashant Mahajan. How can we control resources at session level?

By setting CPU_PER_CALL=1000 in profile and RESOURCE_LIMIT = TRUE we will enforce the same to all sessions of same database user. But how can limit this to specific sessions (Alter session set resource_limit=true) is not there.

Is there any other way using packages/triggers?

Please guide us.

Thanks & Regards,

Shailesh

Thanks Tom,

I have hit the problem, CPU_PER_CALL is set to 2000 (20 seconds) but we couldn't hit "ORA-02393 exceeded call limit on CPU usage", as trace shows following details for statement executed by an application...

In such cases our purpose was not solved, so could you please give us some guidelines to solve such issue like where to concentrate etc.?

SELECT Distinct TblBewegung.FallId As DFallId
FROM TBLFALL, TBLPATIENT, TBLBEWEGUNG
WHERE TBLFALL.FALLART = 'stationaer'
AND TBLFALL.FALLSTARTDATUM >= '06.09.2003'
AND TBLFALL.FALLSTARTDATUM <= '06.09.2005'
AND TblPatient.PatientenId = TblFall.PatientenId
AND TBLFALL.FallId = TblBewegung.FallId
AND TBLBEWEGUNG.STORNIERER IS NULL
and TBLBEWEGUNG.BEWEGUNGSID > 0
AND TBLBEWEGUNG.EINRICHTUNG = '1'
AND TBLFALL.STORNIERER IS NULL AND TBLFALL.FALLID >0
AND TBLFALL.EINRICHTUNG = '1'
AND TBLPATIENT.STORNIERER IS NULL
AND TBLPATIENT.PATIENTENID >0
AND TBLPATIENT.EINRICHTUNG = '1'

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 2.80 114.88 44336 235411 15 26
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 2.81 114.89 44336 235411 15 26

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 19 (SYSADM)

Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
26 SORT (UNIQUE)
105236 NESTED LOOPS
28943 NESTED LOOPS
28944 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'TBLFALL'
57885 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'TBLPATIENT'
57886 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'XPKPATIENT2'
(UNIQUE)
105236 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'TBLBEWEGUNG'
135696 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'XIE1TBLBEWEGUNG'
(NON-UNIQUE)

Thanks & Regards,

Shailesh

Is there anyway to kill a query request if the query takes over a certain amount of time to run?

Does the cost-based optimizer of Oracle 10g or Oracle 11g consider the resource limits specified for the profile associated with a user? For example, if the profile indicates a limit on CPU per session, does this effect the overall execution plan for a SQL statement submitted by that user?

Thanks.