Re: Indirect use of cursors

On 31 Oct 2005 05:40:59 -0800, "Tarby777" <nick_williamson_at_mentorg.com> wrote:

>Hi all,
>
>I've been asked to check whether increased cursor usage in an in-house
>9iR1 database has been caused by the recent introduction of some PL/SQL
>triggers.
>
>The triggers typically fire on insert and/or update, and they don't
>explicitly use cursors *at all*; they select, update, insert, return an
>error status and so on, but there's zero use of cursors as I understand
>them - no DECLARE CURSOR statements, for example.
>
>According to the output of a diagnostic script, some of the tables
>associated with the triggers are grabbing (and more importantly, not
>releasing, cursors). Is it possible that the triggers can be to blame?
>I plan to run some processes with and without the triggers activated,
>and to take a look at v$open_cursor, but I'd be interested to know if
>anyone has encountered a similar situation already...
>
>TIA
>Nick

1 triggers are not compiled
2 consequently they could result into recursive sql (sql on the datadictionary). Obviously, that recursive sql is also using cursors 3 Likely those cursors are not released, as the next time they simply would need to be re-opened only.
4 any select ... into results into an *implicit* cursor. Your statement about your code not using cursors at all might not be accurate.

--
Sybrand Bakker, Senior Oracle DBA