Oracle Database Discussions

Dear all,

A particular module of my application is giving some problem. I traced the session and found following result using TKPROF.
The query in bold is causing issue. Can you advice me what to do.


************************************************************************************************

TKPROF: Release 9.2.0.1.0 - Production on Thu Oct 4 11:11:02 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Trace file: D:\hrms_ora_3064.trc
Sort options: default

********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************

select ts#,file#,block#,nvl(bobj#,0),nvl(tab#,0),intcols,nvl(clucols,0),
audit$,flags,pctfree$,pctused$,initrans,maxtrans,rowcnt,blkcnt,empcnt,
avgspc,chncnt,avgrln,analyzetime, samplesize,cols,property,nvl(degree,1),
nvl(instances,1),avgspc_flb,flbcnt,kernelcols,nvl(trigflag, 0),nvl(spare1,0)
,nvl(spare2,0),spare4,spare6
from
tab$ where obj#=:1


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 5 0.00 0.00 0 0 0 0
Execute 9 0.03 0.34 0 0 0 0
Fetch 9 0.00 0.00 0 27 0 9
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 23 0.03 0.35 0 27 0 9

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
2 TABLE ACCESS CLUSTER TAB$
2 INDEX UNIQUE SCAN I_OBJ# (object id 3)

********************************************************************************

select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#,
sample_size, minimum, maximum, distcnt, lowval, hival, density, col#,
spare1, spare2, avgcln
from
hist_head$ where obj#=:1 and intcol#=:2


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 131 0.00 0.00 0 0 0 0
Fetch 131 0.00 0.11 4 392 0 129
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 264 0.00 0.11 4 392 0 129

Misses in library cache during parse: 0
Optimizer goal: RULE
Parsing user id: SYS (recursive depth: 2)
********************************************************************************

select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags, i.property,
i.pctfree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.distkey, i.lblkkey,
i.dblkkey,i.clufac,i.cols,i.analyzetime,i.samplesize,i.dataobj#,
nvl(i.degree,1),nvl(i.instances,1),i.rowcnt,mod(i.pctthres$,256),
i.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl(c.deferrable#+c.valid#,0),
nvl(i.spare1,i.intcols),i.spare4,spare2,spare6, decode(i.pctthres$,null,
null, mod(trunc(i.pctthres$/256),256))
from
ind$ i, (select enabled, min(cols) unicols, min(to_number(bitand(defer,1)))
deferrable#, min(to_number(bitand(defer,4))) valid# from cdef$ where obj#=
:1 and enabled > 1 group by enabled) c where i.obj#=c.enabled(+) and i.bo#=
:1


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4 0.00 0.00 0 0 0 0
Execute 13 0.00 0.29 0 81 0 0
Fetch 34 0.00 0.00 0 78 0 21
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 51 0.00 0.29 0 159 0 21

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 2)

Rows Row Source Operation
------- ---------------------------------------------------
16 HASH JOIN OUTER
16 TABLE ACCESS CLUSTER IND$
10 INDEX UNIQUE SCAN I_OBJ# (object id 3)
2 VIEW
2 SORT GROUP BY
2 TABLE ACCESS CLUSTER CDEF$
10 INDEX UNIQUE SCAN I_COBJ# (object id 30)

********************************************************************************

select pos#,intcol#,col#,spare1,bo#,spare2
from
icol$ where obj#=:1


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4 0.00 0.00 0 0 0 0
Execute 21 0.00 0.10 1 21 0 0
Fetch 48 0.00 0.00 0 96 0 27
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 73 0.00 0.10 1 117 0 27

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 2)

Rows Row Source Operation
------- ---------------------------------------------------
24 TABLE ACCESS BY INDEX ROWID ICOL$
24 INDEX RANGE SCAN I_ICOL1 (object id 40)

********************************************************************************

select name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2,
nvl(scale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180,scale,181,scale,182,
scale,183,scale,231,scale,0),null$,fixedstorage,nvl(deflength,0),default$,
rowid,col#,property, nvl(charsetid,0),nvl(charsetform,0),spare1,spare2,
nvl(spare3,0)
from
col$ where obj#=:1 order by intcol#


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4 0.00 0.00 0 0 0 0
Execute 13 0.00 0.00 0 20 0 0
Fetch 90 0.00 0.00 0 44 0 77
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 107 0.00 0.00 0 64 0 77

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 2)

Rows Row Source Operation
------- ---------------------------------------------------
71 SORT ORDER BY
71 TABLE ACCESS CLUSTER COL$
10 INDEX UNIQUE SCAN I_OBJ# (object id 3)

********************************************************************************

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#


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4 0.00 0.43 1 145 0 0
Execute 4 0.00 0.00 0 0 0 0
Fetch 4 0.00 0.00 0 8 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 12 0.00 0.43 1 153 0 0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
0 SORT ORDER BY
0 NESTED LOOPS OUTER
0 TABLE ACCESS BY INDEX ROWID DEPENDENCY$
0 INDEX RANGE SCAN I_DEPENDENCY1 (object id 127)
0 TABLE ACCESS BY INDEX ROWID OBJ$
0 INDEX UNIQUE SCAN I_OBJ1 (object id 36)

********************************************************************************

select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname,
o.dataobj#,o.flags
from
obj$ o where o.obj#=:1


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.00 0.00 0 0 0 0
Execute 13 0.00 0.00 0 0 0 0
Fetch 13 0.00 0.00 0 39 0 13
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 29 0.00 0.00 0 39 0 13

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 2)

Rows Row Source Operation
------- ---------------------------------------------------
2 TABLE ACCESS BY INDEX ROWID OBJ$
2 INDEX UNIQUE SCAN I_OBJ1 (object id 36)

********************************************************************************

select order#,columns,types
from
access$ where d_obj#=:1


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4 0.00 0.00 0 0 0 0
Execute 4 0.00 0.00 0 0 0 0
Fetch 4 0.00 0.00 0 8 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 12 0.00 0.00 0 8 0 0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS BY INDEX ROWID ACCESS$
0 INDEX RANGE SCAN I_ACCESS1 (object id 129)

********************************************************************************

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#


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 5 0.00 0.00 0 0 0 0
Execute 5 0.00 0.00 0 0 0 0
Fetch 5 0.00 0.00 0 10 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 15 0.00 0.00 0 10 0 0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
0 SORT GROUP BY
0 TABLE ACCESS BY INDEX ROWID OBJAUTH$
0 INDEX RANGE SCAN I_OBJAUTH1 (object id 108)

********************************************************************************

select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2))
from
objauth$ where obj#=:1 group by grantee#,privilege#,nvl(col#,0) order by
grantee#


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 5 0.00 0.00 0 0 0 0
Execute 5 0.00 0.00 0 0 0 0
Fetch 5 0.00 0.00 0 10 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 15 0.00 0.00 0 10 0 0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
0 SORT GROUP BY
0 TABLE ACCESS BY INDEX ROWID OBJAUTH$
0 INDEX RANGE SCAN I_OBJAUTH1 (object id 108)

********************************************************************************

select col#,intcol#,toid,version#,packed,intcols,intcol#s,flags, synobj#,
nvl(typidcol#, 0)
from
coltype$ where obj#=:1 order by intcol# desc


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4 0.00 0.00 0 0 0 0
Execute 4 0.00 0.00 0 0 0 0
Fetch 8 0.00 0.00 0 12 0 4
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 16 0.00 0.00 0 12 0 4

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT ORDER BY
1 TABLE ACCESS CLUSTER COLTYPE$
1 INDEX UNIQUE SCAN I_OBJ# (object id 3)

********************************************************************************

select intcol#, toid, version#, intcols, intcol#s, flags, synobj#
from
subcoltype$ where obj#=:1 order by intcol# asc


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4 0.00 0.00 0 3 0 0
Execute 4 0.00 0.00 0 0 0 0
Fetch 4 0.00 0.00 0 12 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 12 0.00 0.00 0 15 0 0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
0 SORT ORDER BY
0 TABLE ACCESS CLUSTER SUBCOLTYPE$
1 INDEX UNIQUE SCAN I_OBJ# (object id 3)

********************************************************************************

select col#,intcol#,ntab#
from
ntab$ where obj#=:1 order by intcol# asc


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4 0.00 0.00 0 0 0 0
Execute 4 0.00 0.00 0 0 0 0
Fetch 4 0.00 0.00 0 12 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 12 0.00 0.00 0 12 0 0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
0 SORT ORDER BY
0 TABLE ACCESS CLUSTER NTAB$
1 INDEX UNIQUE SCAN I_OBJ# (object id 3)

********************************************************************************

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.obj# = :1 order by l.intcol# asc


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4 0.00 0.00 0 0 0 0
Execute 4 0.00 0.00 0 0 0 0
Fetch 8 0.00 0.00 0 12 0 4
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 16 0.00 0.00 0 12 0 4

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT ORDER BY
1 TABLE ACCESS CLUSTER LOB$
1 INDEX UNIQUE SCAN I_OBJ# (object id 3)

********************************************************************************

select col#,intcol#,reftyp,stabid,expctoid
from
refcon$ where obj#=:1 order by intcol# asc


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4 0.00 0.00 0 21 0 0
Execute 4 0.00 0.00 0 0 0 0
Fetch 4 0.00 0.00 0 12 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 12 0.00 0.00 0 33 0 0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
0 SORT ORDER BY
0 TABLE ACCESS CLUSTER REFCON$
1 INDEX UNIQUE SCAN I_OBJ# (object id 3)

********************************************************************************

select col#,intcol#,charsetid,charsetform
from
col$ where obj#=:1 order by intcol# asc


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4 0.00 0.00 0 0 0 0
Execute 4 0.00 0.00 0 0 0 0
Fetch 12 0.00 0.00 0 12 0 8
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 20 0.00 0.00 0 12 0 8

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
2 SORT ORDER BY
2 TABLE ACCESS CLUSTER COL$
1 INDEX UNIQUE SCAN I_OBJ# (object id 3)

********************************************************************************

select intcol#,type,flags,lobcol,objcol,extracol,schemaoid, elemnum
from
opqtype$ where obj# = :1 order by intcol# asc


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4 0.00 0.00 0 0 0 0
Execute 4 0.00 0.00 0 0 0 0
Fetch 4 0.00 0.00 0 12 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 12 0.00 0.00 0 12 0 0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
0 SORT ORDER BY
0 TABLE ACCESS CLUSTER OPQTYPE$
1 INDEX UNIQUE SCAN I_OBJ# (object id 3)

********************************************************************************

select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,
NVL(lists,65535),NVL(groups,65535),cachehint,hwmincr, NVL(spare1,0)
from
seg$ where ts#=:1 and file#=:2 and block#=:3


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 5 0.00 0.00 0 0 0 0
Execute 5 0.00 0.00 0 0 0 0
Fetch 5 0.00 0.00 0 15 0 5
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 15 0.00 0.00 0 15 0 5

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS CLUSTER SEG$
1 INDEX UNIQUE SCAN I_FILE#_BLOCK# (object id 9)

********************************************************************************

select u.name,o.name, t.update$, t.insert$, t.delete$, t.enabled
from
obj$ o,user$ u,trigger$ t where t.baseobject=:1 and t.obj#=o.obj# and
o.owner#=u.user# order by o.obj#


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4 0.00 0.00 0 0 0 0
Execute 4 0.00 0.00 0 0 0 0
Fetch 4 0.00 0.00 0 4 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 12 0.00 0.00 0 4 0 0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
0 SORT ORDER BY
0 NESTED LOOPS
0 NESTED LOOPS
0 TABLE ACCESS BY INDEX ROWID TRIGGER$
0 INDEX RANGE SCAN I_TRIGGER1 (object id 130)
0 TABLE ACCESS BY INDEX ROWID OBJ$
0 INDEX UNIQUE SCAN I_OBJ1 (object id 36)
0 TABLE ACCESS CLUSTER USER$
0 INDEX UNIQUE SCAN I_USER# (object id 11)

********************************************************************************

select parttype, partcnt, partkeycols, flags, defts#, defpctfree, defpctused,
definitrans, defmaxtrans, deftiniexts, defextsize, defminexts, defmaxexts,
defextpct, deflists, defgroups, deflogging, spare1, mod(spare2, 256)
subparttype, mod(trunc(spare2/256), 256) subpartkeycols,
mod(trunc(spare2/65536), 65536) defsubpartcnt, mod(trunc(spare2/4294967296),
256) defhscflags
from
partobj$ where obj# = :1


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4 0.00 0.00 0 16 0 0
Execute 4 0.01 0.00 0 0 0 0
Fetch 4 0.00 0.00 0 4 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 12 0.01 0.00 0 20 0 0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS BY INDEX ROWID PARTOBJ$
0 INDEX UNIQUE SCAN I_PARTOBJ$ (object id 227)

********************************************************************************

select con#,obj#,rcon#,enabled,nvl(defer,0)
from
cdef$ where robj#=:1


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 5 0.00 0.00 0 0 0 0
Execute 5 0.00 0.00 0 0 0 0
Fetch 5 0.00 0.00 0 10 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 15 0.00 0.00 0 10 0 0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS BY INDEX ROWID CDEF$
0 INDEX RANGE SCAN I_CDEF3 (object id 52)

********************************************************************************

select con#,type#,condlength,intcols,robj#,rcon#,match#,refact,nvl(enabled,0),
rowid,cols,nvl(defer,0),mtime,nvl(spare1,0)
from
cdef$ where obj#=:1


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 5 0.00 0.00 0 0 0 0
Execute 5 0.00 0.00 0 0 0 0
Fetch 10 0.00 0.00 0 20 0 5
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 20 0.00 0.00 0 20 0 5

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS CLUSTER CDEF$
1 INDEX UNIQUE SCAN I_COBJ# (object id 30)

********************************************************************************

select intcol#,nvl(pos#,0),col#
from
ccol$ where con#=:1


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 5 0.00 0.00 0 0 0 0
Execute 5 0.00 0.00 0 0 0 0
Fetch 10 0.00 0.00 0 20 0 5
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 20 0.00 0.00 0 20 0 5

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID CCOL$
1 INDEX RANGE SCAN I_CCOL1 (object id 54)

********************************************************************************

select metadata
from
kopm$ where name='DB_FDO'


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.01 2 2 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.01 2 2 0 1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID KOPM$
1 INDEX UNIQUE SCAN I_KOPM1 (object id 351)

********************************************************************************

select u.name, o.name, a.interface_version#
from
association$ a, user$ u, obj$ o where a.obj# = :1
and a.property = :2
and a.statstype# = o.obj# and
u.user# = o.owner#


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 1 16 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.00 0.00 1 16 0 2

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
1 NESTED LOOPS
1 NESTED LOOPS
1 TABLE ACCESS FULL ASSOCIATION$
1 TABLE ACCESS BY INDEX ROWID OBJ$
1 INDEX UNIQUE SCAN I_OBJ1 (object id 36)
1 TABLE ACCESS CLUSTER USER$
1 INDEX UNIQUE SCAN I_USER# (object id 11)

********************************************************************************

select user#
from
sys.user$ where name = 'OUTLN'


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 2 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 2 0 1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 2)

Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID USER$
1 INDEX UNIQUE SCAN I_USER1 (object id 44)

********************************************************************************

declare
sel number;

begin
:1 := "CTXSYS"."TEXTOPTSTATS".ODCIStatsSelectivity(
sys.ODCIPREDINFO('CTXSYS',
'CTX_CONTAINS',
'TEXTCONTAINS',
32),
sel,
sys.ODCIARGDESCLIST(sys.ODCIARGDESC(3, NULL, NULL, NULL, NULL, NULL), sys.ODCIARGDESC(5, NULL, NULL, NULL, NULL, NULL), sys.ODCIARGDESC(2, 'CO_MA_COMPANY', 'CRMADMIN', '"COMPANY_NAME"', NULL, NULL), sys.ODCIARGDESC(3, NULL, NULL, NULL, NULL, NULL)),
0,
NULL
, NULL, 'SBI',
sys.ODCIENV(1, 0));
if sel IS NULL then
:2 := -1;
else
:2 := sel;
end if;
exception
when others then
raise;
end;

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 2 0 0
Execute 1 0.00 0.01 2 60 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.01 2 62 0 1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 78 (recursive depth: 1)
********************************************************************************

select /*+ ORDERED */ i.idx_name, u2.name
from
sys.user$ u, sys.obj$ o, ctxsys.dr$index i, sys.user$ u2 where i.idx_owner#
= u2.user# and i.idx_text_name = ltrim(rtrim(upper('"COMPANY_NAME"'),'"')
,'"') and i.idx_table# = o.obj# and o.type# = 2 and o.name =
'CO_MA_COMPANY' and o.owner# = u.user# and u.name = 'CRMADMIN'


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 18 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.00 0.00 0 18 0 2

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 33 (recursive depth: 2)

Rows Row Source Operation
------- ---------------------------------------------------
1 NESTED LOOPS
1 NESTED LOOPS
1 NESTED LOOPS
1 TABLE ACCESS BY INDEX ROWID USER$
1 INDEX UNIQUE SCAN I_USER1 (object id 44)
1 TABLE ACCESS BY INDEX ROWID OBJ$
1 INDEX RANGE SCAN I_OBJ2 (object id 37)
1 TABLE ACCESS BY INDEX ROWID DR$INDEX
1 INDEX RANGE SCAN DRC$IDX_COLUMN (object id 26444)
1 TABLE ACCESS CLUSTER USER$
1 INDEX UNIQUE SCAN I_USER# (object id 11)

********************************************************************************

declare
cost sys.ODCICost := sys.ODCICost(NULL, NULL, NULL, NULL);

begin
:1 := "CTXSYS"."TEXTOPTSTATS".ODCIStatsFunctionCost(
sys.ODCIFuncInfo('CTXSYS',
'CTX_CONTAINS',
'TEXTCONTAINS',
2),
cost,
sys.ODCIARGDESCLIST(sys.ODCIARGDESC(2, 'CO_MA_COMPANY', 'CRMADMIN', '"COMPANY_NAME"', NULL, NULL), sys.ODCIARGDESC(3, NULL, NULL, NULL, NULL, NULL))
, NULL, 'SBI',
sys.ODCIENV(1, 0));
if cost.CPUCost IS NULL then
:2 := -1;
else
:2 := cost.CPUCost;
end if;
if cost.IOCost IS NULL then
:3 := -1;
else
:3 := cost.IOCost;
end if;
if cost.NetworkCost IS NULL then
:4 := -1;
else
:4 := cost.NetworkCost;
end if;
exception
when others then
raise;
end;

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 48 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 48 0 1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 78 (recursive depth: 1)
********************************************************************************

SELECT id ,companyName,record_id,sector_name,industry_name
FROM
(SELECT COMPANY_CODE id,COMPANY_NAME companyName,RECORD_ID record_id,
SECTOR_ID sector_name,INDUSTRY_ID industry_name,COMPANY_STATUS
COMPANY_STATUS FROM co_ma_company ) where COMPANY_STATUS='Active' and
(contains(companyName,'SBI')>0 OR sector_name = ' ' OR id in (select
company_code from co_ma_company_industry where industry_id like ' '))


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.06 0.20 24 198 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 82.68 83.23 92 1579473 0 16
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 82.75 83.44 116 1579671 0 16

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 78
********************************************************************************

select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, spare1,
spare2
from
obj$ where owner#=:1 and name=:2 and namespace=:3 and(remoteowner=:4 or
remoteowner is null and :4 is null)and(linkname=:5 or linkname is null and
:5 is null)and(subname=:6 or subname is null and :6 is null)


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 2 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.01 0.00 0 2 0 0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS BY INDEX ROWID OBJ$
0 INDEX RANGE SCAN I_OBJ2 (object id 37)

********************************************************************************

BEGIN :p := CTX_QUERY.PREFERENCE;END;


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 33 (recursive depth: 1)
********************************************************************************

SELECT IDX_DOCID_COUNT
FROM
DR$INDEX WHERE IDX_ID = :idxid


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 2 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 2 0 1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 33 (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID DR$INDEX
1 INDEX UNIQUE SCAN DRC$IDX_KEY (object id 26442)

********************************************************************************

SELECT /*+ INDEX(i) */ TOKEN_FIRST,TOKEN_LAST,TOKEN_COUNT,ROWID
FROM
"CRMADMIN"."DR$CO_MA_COMPANY_NAME_IDX$I" i WHERE TOKEN_TEXT = :word AND
TOKEN_TYPE = :wtype ORDER BY TOKEN_TEXT, TOKEN_TYPE, TOKEN_FIRST


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.04 1 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.04 1 3 0 1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 33 (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
1 INDEX RANGE SCAN DR$CO_MA_COMPANY_NAME_IDX$X (object id 80820)

********************************************************************************

SELECT DOCID
FROM
"CRMADMIN"."DR$CO_MA_COMPANY_NAME_IDX$K" WHERE TEXTKEY = :rid


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 16429 0.84 0.71 0 0 0 0
Fetch 16429 0.29 0.31 0 32858 0 16429
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 32859 1.14 1.02 0 32858 0 16429

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 33 (recursive depth: 1)
********************************************************************************

SELECT TOKEN_INFO
FROM
"CRMADMIN"."DR$CO_MA_COMPANY_NAME_IDX$I" WHERE ROWID = :rid


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 1 1 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 1 1 0 1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 33 (recursive depth: 1)



********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.06 0.20 24 198 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 82.68 83.23 92 1579473 0 16
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 82.75 83.44 116 1579671 0 16

Misses in library cache during parse: 1


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 110 0.01 0.47 1 187 0 0
Execute 16716 0.89 1.51 3 230 0 3
Fetch 16865 0.29 0.49 9 33773 0 16745
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 33691 1.20 2.47 13 34190 0 16748

Misses in library cache during parse: 34

10 user SQL statements in session.
101 internal SQL statements in session.
111 SQL statements in session.
********************************************************************************
Trace file: D:\hrms_ora_3064.trc
Trace file compatibility: 9.00.01
Sort options: default

1 session in tracefile.
10 user SQL statements in trace file.
101 internal SQL statements in trace file.
111 SQL statements in trace file.
36 unique SQL statements in trace file.
34499 lines in trace file.
***********************************************************************************************

Thanx in advance