| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Why is recursive calls so high
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" <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 - 10:06:22 CST
 |
 |