Re: Recursive SQL

With difficulty, it's looking up constraint information. You could of course trace user sessions to see where this is recursively called (inserts/updates/deletes most likely) but before you do any of that I'd suggest that you investigate the elapsed time figure, to consider whether you actually care.

On Fri, Sep 19, 2008 at 7:08 PM, Shivaswamy / ಶಿವಸ್ವಾಮಿ < shivaswamykr_at_gmail.com> wrote:

> Hello.
>
> This one recursive SQL I find, is a big one in the Top 10 SQL on ouur
> database, taking over a billion buffer gets. In one particular hour, I find
> this SQL responsible for nearly 15% of Total. It executed nearly 157K times.
>
> select c.name, u.name from con$ c, cdef$ cd, user$ u where c.co
> n# = cd.con# and cd.enabled = :1 and c.owner# = u.user#
>
> The question I have is, how I can tie this to a User SQL? Your input
> appreciated.
> Thanks,
> Shiva
>

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info
--
http://www.freelists.org/webpage/oracle-l