Why is recursive calls so high

Shan R Shanmuganathan

unread,

Feb 19, 2004, 8:42:06 PM2/19/04

to

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

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

Ron

unread,

Feb 20, 2004, 12:38:28 AM2/20/04

to

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" <sh...@srinidhi.net> wrote in message
news:408f220d.04021...@posting.google.com...

Shan R Shanmuganathan

unread,

Feb 20, 2004, 8:42:43 AM2/20/04

to

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" <sup...@dbainfopower.com> wrote in message news:<2cednZPpRpR...@comcast.com>...

Shan R Shanmuganathan

unread,

Feb 20, 2004, 11:06:22 AM2/20/04

to

Of course, I should have queried v$sqlarea by first_load_time.

Here is the first few seconds of v$sqlarea on startup:
SQL> l
1 select first_load_time, substr(sql_text,1,240) from v$sqlarea
2 where first_load_time < '2004-02-20/01:22:00'
3* order by 1
SQL> /

FIRST_LOAD_TIME
-------------------
SUBSTR(SQL_TEXT,1,240)
--------------------------------------------------------------------------------
2004-02-20/01:21:26
ALTER DATABASE MOUNT

2004-02-20/01:21:26
ALTER SESSION SET NLS_LANGUAGE= 'AMERICAN' NLS_TERRITORY= 'AMERICA' NLS_CURRENCY
= '$' NLS_ISO_CURRENCY= 'AMERICA' NLS_NUMERIC_CHARACTERS= '.,' NLS_CALENDAR= 'GR
EGORIAN' NLS_DATE_FORMAT= 'DD-MON-RR' NLS_DATE_LANGUAGE= 'AMERICAN' NLS_SORT= '

2004-02-20/01:21:30
ALTER DATABASE OPEN

2004-02-20/01:21:31
select migrated from MIGRATE$

2004-02-20/01:21:31
select TOTAL from SYS.ID_GENS$

2004-02-20/01:21:31
select OPEN_MODE from v$database

2004-02-20/01:21:31
select ts# from ts$ where name=:1

2004-02-20/01:21:31
select user#,type# from user$ where name=:1

2004-02-20/01:21:31
select value$ from sys.props$ where name = :1

2004-02-20/01:21:31
select metadata from kopm$ where name='DB_FDO'

2004-02-20/01:21:31
select value from gv$parameter where name = 'undo_retention' and INST_ID <> :1

2004-02-20/01:21:31
select col#,intcol#,ntab# from ntab$ where obj#=:1 order by intcol# asc

2004-02-20/01:21:31
select charsetid, charsetform from col$ where obj# = :1 and col# = :2

2004-02-20/01:21:31
select con#,obj#,rcon#,enabled,nvl(defer,0) from cdef$ where robj#=:1

2004-02-20/01:21:31
select pos#,intcol#,col#,spare1,bo#,spare2 from icol$ where obj#=:1

2004-02-20/01:21:31
select value$ from props$ where name='DEFAULT_TEMP_TABLESPACE'

2004-02-20/01:21:31
SELECT NULL FROM OBJ$ WHERE NAME='MAP_FILE$' AND OWNER# = 0

2004-02-20/01:21:31
select order#,columns,types from access$ where d_obj#=:1

2004-02-20/01:21:31
select intcol#,nvl(pos#,0),col# from ccol$ where con#=:1

2004-02-20/01:21:31
select l.col#, l.intcol#, l.lobj#, l.ind#, l.ts#, l.file#, l.block#, l.chunk, l.
pctversion$, l.flags, l.property, l.retention, l.freepools from lob$ l where l.o
bj# = :1 order by l.intcol# asc

2004-02-20/01:21:31
select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,NVL(lis
ts,65535),NVL(groups,65535),cachehint,hwmincr, NVL(spare1,0) from seg$ where ts#
=:1 and file#=:2 and block#=:3

2004-02-20/01:21:31
select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#, d_owner#,
nvl(property,0),subname from dependency$,obj$ where d_obj#=:1 and p_obj#=obj#(+)
order by order#

2004-02-20/01:21:31
select ts#,file#,block#,cols,nvl(size$,-1),pctfree$,pctused$,initrans,maxtrans,h
ashkeys,func,extind,avgchn,nvl(degree,1),nvl(instances,1),nvl(flags,0) from clu$
where obj#=:1

2004-02-20/01:21:31
select col#, grantee#, privilege#,max(mod(nvl(option$,0),2)) from objauth$ where
obj#=:1 and col# is not null group by privilege#, col#, grantee# order by col#,
grantee#

2004-02-20/01:21:31
select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2))from objauth$ w
here obj#=:1 group by grantee#,privilege#,nvl(col#,0) order by grantee#

2004-02-20/01:21:31
update undo$ set name=:2,file#=:3,block#=:4,status$=:5,user#=:6,undosqn=:7,xacts
qn=:8,scnbas=:9,scnwrp=:10,inst#=:11,ts#=:12,spare1=:13 where us#=:1

2004-02-20/01:21:31
select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname,o.dataobj#
,o.flags from obj$ o where o.obj#=:1

2004-02-20/01:21:31
select rowcnt,blkcnt,empcnt,avgspc,chncnt,avgrln,nvl(degree,1), nvl(instances,1)
from tab$ where obj# = :1

2004-02-20/01:21:31
select increment$,minvalue,maxvalue,cycle#,order$,cache,highwater,audit$,flags f
rom seq$ where obj#=:1

2004-02-20/01:21:31
select col#,intcol#,reftyp,stabid,expctoid from refcon$ where obj#=:1 order by i
ntcol# asc

"Ron" <sup...@dbainfopower.com> wrote in message news:<2cednZPpRpR...@comcast.com>...

Frank van Bortel

unread,

Feb 20, 2004, 2:32:20 PM2/20/04

to

Shan R Shanmuganathan wrote:
> Of course, I should have queried v$sqlarea by first_load_time.
>
> Here is the first few seconds of v$sqlarea on startup:
> SQL> l
> 1 select first_load_time, substr(sql_text,1,240) from v$sqlarea
> 2 where first_load_time < '2004-02-20/01:22:00'
> 3* order by 1
> SQL> /
>
> FIRST_LOAD_TIME
> -------------------
> SUBSTR(SQL_TEXT,1,240)
> --------------------------------------------------------------------------------
> 2004-02-20/01:21:26
> ALTER DATABASE MOUNT

OK - so it's mounting/opening.

>
> 2004-02-20/01:21:26
> ALTER SESSION SET NLS_LANGUAGE= 'AMERICAN' NLS_TERRITORY= 'AMERICA' NLS_CURRENCY
> = '$' NLS_ISO_CURRENCY= 'AMERICA' NLS_NUMERIC_CHARACTERS= '.,' NLS_CALENDAR= 'GR
> EGORIAN' NLS_DATE_FORMAT= 'DD-MON-RR' NLS_DATE_LANGUAGE= 'AMERICAN' NLS_SORT= '
>

A session get's it's parameters set.
Your's, probably, logged on as sysdba.

> 2004-02-20/01:21:30
> ALTER DATABASE OPEN

Opening.

>
> 2004-02-20/01:21:31
> select migrated from MIGRATE$
>

Was I migrated - am I still in migration state?

> 2004-02-20/01:21:31
> select TOTAL from SYS.ID_GENS$
>
> 2004-02-20/01:21:31
> select OPEN_MODE from v$database
>
> 2004-02-20/01:21:31
> select ts# from ts$ where name=:1

Where's me tablespaces?

>
> 2004-02-20/01:21:31
> select user#,type# from user$ where name=:1
>
> 2004-02-20/01:21:31
> select value$ from sys.props$ where name = :1
>
> 2004-02-20/01:21:31
> select metadata from kopm$ where name='DB_FDO'
>
> 2004-02-20/01:21:31
> select value from gv$parameter where name = 'undo_retention' and INST_ID <> :1
>
> 2004-02-20/01:21:31
> select col#,intcol#,ntab# from ntab$ where obj#=:1 order by intcol# asc
>
> 2004-02-20/01:21:31
> select charsetid, charsetform from col$ where obj# = :1 and col# = :2
>
> 2004-02-20/01:21:31
> select con#,obj#,rcon#,enabled,nvl(defer,0) from cdef$ where robj#=:1
>
> 2004-02-20/01:21:31
> select pos#,intcol#,col#,spare1,bo#,spare2 from icol$ where obj#=:1
>
> 2004-02-20/01:21:31
> select value$ from props$ where name='DEFAULT_TEMP_TABLESPACE'
>
> 2004-02-20/01:21:31
> SELECT NULL FROM OBJ$ WHERE NAME='MAP_FILE$' AND OWNER# = 0
>
> 2004-02-20/01:21:31
> select order#,columns,types from access$ where d_obj#=:1
>

bladibla
It's just opening, and doing some householding
--

Regards,
Frank van Bortel

Ron

unread,

Feb 24, 2004, 1:58:32 AM2/24/04

to

Hello Shan,

Number of executions per statement would help too.

Regards,

Ron
DBA Infopower
http://www.dbainfopower.com
Standard disclaimer:
http://www.dbainfopower.com/dbaip_advice_disclaimer.html

"Shan R Shanmuganathan" <sh...@srinidhi.net> wrote in message

news:408f220d.04022...@posting.google.com...