Originally posted by cchiara
The time it took for the query to finish is 24 seconds. Initially we set the CPU_PER_CALL to 500 which is 5 seconds (just for testing purpose). Also, set RESOURCE_LIMIT = TRUE in session level. The query did NOT terminate at all after 5 secs. Why?
Because your query might have spend only 0.5 seconds of CPU time and lost the remaining 23.5 seconds for I/O and other operations. Elapsed time of the query tels you nothing about the actual CPU time it used! You should set tracing on for that query and then analyzing the trace file with tkprof - this will tell you ther actual CPU cost.

Honestly I dont belive that by using CPU_PER_CALL limit you can actualy filter out queries that are longer than 2 minutes. At least not with any acceptable precision or reliability. There could be queries that last 5 minutes, eating only 5 CPU seconds, and there could be queries that lasts for 20 seconds that will consume 5 CPU seconds. So if you set the limit of 5 CPU seconds per call you'll terminate the querie that would only need couple of seconds more to finish, but you will not terminate the query that will take 5 minutes to finish.