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" <shan_at_srinidhi.net> wrote in message
news:408f220d.0402200806.7df07410_at_posting.google.com...
> 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 Tue Feb 24 2004 - 00:58:32 CST
