Re: Why is recursive calls so high

Hello Ron.

I looked at all the sql from v$sqlarea. It had 30 entries corresponding to 30 user calls; but nothing suspicious. It had alter database statements from the startup and rest was select from varoius data dictionary tables. Unfortunately I cannot bounce the database now to get the list. I will post it when I bounce next.

Thanks,
Shan R Shanmuganathan

"Ron" <support_at_dbainfopower.com> wrote in message news:<2cednZPpRpRbBqjdRVn-tw_at_comcast.com>...
> Hello Shan,
>
> Can you query v$sql to check what is the sql is?
>
> Regards,
>
> Ron
> DBA Infopower
> http://www.dbainfopower.com
> Standard disclaimer:
> http://www.dbainfopower.com/dbaip_advice_disclaimer.html
>
>
> "Shan R Shanmuganathan" <shan_at_srinidhi.net> wrote in message
> news:408f220d.0402191742.181e7b2e_at_posting.google.com...
> > Lately the ratio of user calls/recursive calls had fallen below 1 and
> > in fact approaching 0.1. I have been trying hard to find the root
> > cause without any success.
> >
> > This is 9.2.0.3 running a datawarehouse application. The tablespaces
> > are mostly locally managed with some dictionary managed. The
> > application uses lot of bit map indexes. One recent change was to go
> > to unto tablespace from traditional rollback segments.
> >
> > While investigating extent management, specifically temp and undo, I
> > came across the fact that recursive calls are so high even on an
> > instance that was just started with no users connected and with no
> > rollback segment (other than SYSTEM) active; turing off auto undo
> > management did not change the result either.
> >
> > Anybody know why recursive calls is so high right from startup??
> >
> > 27> sqlplus /nolog
> >
> > SQL*Plus: Release 9.2.0.3.0 - Production on Thu Feb 19 19:00:47 2004
> >
> > Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
> >
> > SQL> connect / as sysdba
> > Connected to an idle instance.
> > SQL> startup
> > ORACLE instance started.
> >
> > Total System Global Area 7586425800 bytes
> > Fixed Size 752584 bytes
> > Variable Size 1140850688 bytes
> > Database Buffers 6442450944 bytes
> > Redo Buffers 2371584 bytes
> > Database mounted.
> > Database opened.
> > SQL> select * from v$sysstat where name in ( 'user calls', 'recursive
> > calls');
> >
> > STATISTIC# NAME
> > ---------- ---------------------------------------------------------------
> -
> > CLASS VALUE
> > ---------- ----------
> > 6 user calls
> > 1 30
> >
> > 7 recursive calls
> > 1 11136
> >
> >
> > Thanks,
> > Shan R Shanmuganathan
Received on Fri Feb 20 2004 - 07:42:43 CST