(I shortened the conversation, but tried not to change the content):
We get an
ORA-02393: exceeded call limit on CPU usagealmost immediatedly after executing the following stmt:
...Could you pls adjust the limits?
After some hours of meetings I replied:
These limits where introduced in test to find exactly those statements which are running too slow for an OLTP application. Can we help you in tuning the statement?
I already exchanged details on the issue with [another DBA] and found the cause of the 'longer than usual' execution time (index missing ;-)
Nonetheless 100ms max exec time is a bit too strict for a dev platform imho - but as the ... is going to be replaced soon we won't request any changes to a system/db in the last chapter of its life cycle.
I did not reply yet. Just try to explain my world.
Is a CPU_PER_CALL limit really needed on a test system?
In theory: no.
If the developers implement a perfect code instrumentation, they would know the runtime of every statement. In test it would be evaluated for a proper per-statement performance as well as for an over all performance.
But if they would do so, I would never have received this email. So the world is not perfect. The code isn't, also.
Are 100ms enough time? seems really tough!
Yes, they are.
Even on an 8 years old UltraSparc III+ you can do a lot of CPU cycles within 100ms. And only CPU is count there, no time for IO or other WAITs.
But the biggest argument for this limit: I never got any complaint about it by a good statement. Only by those which needed urgent suport, anyhow.