Re: Recursive SQL

From: Finn Jorgensen <finn.oracledba_at_gmail.com>
Date: Fri, 19 Sep 2008 15:45:48 -0400
Message-ID: <74f79c6b0809191245t27665d07lbe60c8d20349aa96@mail.gmail.com>


I've seen this in a Streams configuration for a Financial database before. The fix was to delete the stats on those tables and then lock the stats. Like this :

connect / as sysdba

execute dbms_stats.delete_table_stats(ownname => 'SYS', tabname => 'CDEF$');
execute dbms_stats.delete_table_stats(ownname => 'SYS', tabname => 'CON$');
execute dbms_stats.delete_table_stats(ownname => 'SYS', tabname => 'USER$');
execute dbms_stats.lock_table_stats(ownname => 'SYS', tabname => 'CDEF$');
execute dbms_stats.lock_table_stats(ownname => 'SYS', tabname => 'CON$');
execute dbms_stats.lock_table_stats(ownname => 'SYS', tabname => 'USER$');

Finn

On Fri, Sep 19, 2008 at 2: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
>

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Sep 19 2008 - 14:45:48 CDT

Original text of this message