CPU_PER_CALL per session?

[shailesh]

Hello,

We are using Oracle 8i on Windows 2000. Can anyone help us to know How to 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

[davey23uk]

you cant with profiles. Might be able to play around with resource manager to do what you want

[shailesh]

Thanks Guys.

We thought CPU_TIME <= ELAPSED_TIME, but 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