RE: Measuring PLSQL-SQL context switches

From: Hemant K Chitale <>
Date: Sat, 13 Sep 2008 19:16:25 +0800
Message-Id: <>

Since it it the same SQL statement being executed repeatedly, I do not expect much in terms of SYS recursive SQLs for parsing. Although it would be true that extent allocation (if the SQL is an INSERT) may cause additional recursive calls.
I will be getting a count of 'recursive calls' *before* beginning the particular PLSQL block / stored procedure to "exclude" prior SYS recursives.

Hemant K Chitale

At 01:54 AM Saturday, Yong Huang wrote:
>Mark proposed 'recursive calls' as a metric. That's good. One minor
>problem is that there're two types of recursive SQLs, those you
>write (PL/SQL code in package, procedure, etc. and anonymous blocks,
>and even triggers), and those Oracle writes to get metadata from
>data dictionary. Oracle doesn't make a distinction anywhere. If your
>benchmark has thousands or millions of context switches, Oracle's
>own internal recursive SQLs become negligible. Just don't flush
>shared pool in the middle.
>While you measure 'recursive calls', you can also measure 'recursive
>cpu usage'.
>Yong Huang

Received on Sat Sep 13 2008 - 06:16:25 CDT

Original text of this message