Re: Why is recursive calls so high

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 Thu Feb 19 2004 - 23:38:28 CST