Hello,
I have Oracle Standard Edition Database 11g Release 11.2.0.3.0 - 64bit Production
OS: Oracle Linux Server release 6.8
In my Statspack report I noticed that first top queries (Ordered by CPU Time, Elapsed time, by get etc) are Oracle internal queries. For example:
CPU CPU per Elapsed Old
Time (s) Executions Exec (s) %Total Time (s) Buffer Gets Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
15829.68 67,656,420 0.00 383.9 20188.91 609,221,947 3694268570
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.samp
lesize,i.dataobj#,nvl(i.degree,1),nvl(i.instances,1),i.rowcnt,mo
3173.37 292,986,357 0.00 77.0 3785.93 1,259,475,664 2030260024
select pos#,intcol#,col#,spare1,bo#,spare2,spare3 from icol$ whe
re obj#=:1
2751.32 61,730,658 0.00 66.7 3033.21 941,465,387 947984246
select con#,type#,condlength,intcols,robj#,rcon#,match#,refact,n
vl(enabled,0),rowid,cols,nvl(defer,0),mtime,nvl(spare1,0),spare2
,spare3 from cdef$ where obj#=:1
1686.31 19,861,934 0.00 40.9 2099.10 94,863,448 839312984
select name,intcol#,segcol#,type#,length,nvl(precision#,0),decod
e(type#,2,nvl(scale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180
,scale,181,scale,182,scale,183,scale,231,scale,0),null$,fixedsto
rage,nvl(deflength,0),default$,rowid,col#,property, nvl(charseti
1343.65 88,964,442 0.00 32.6 2683.42 266,918,498 2926525797
select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#
,iniexts,NVL(lists,65535),NVL(groups,65535),cachehint,hwmincr, N
VL(spare1,0),NVL(scanhint,0),NVL(bitmapranges,0) from seg$ where
ts#=:1 and file#=:2 and block#=:3
........
SQL ordered by Elapsed time for DB: ***** Instance: ***** Snaps: 309391
-> Total DB Time (s): 10,216
-> Captured SQL accounts for 12.3% of Total DB Time
-> SQL reported below exceeded 1.0% of Total DB Time
Elapsed Elap per CPU Old
Time (s) Executions Exec (s) %Total Time (s) Physical Reads Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
20188.91 67,656,420 0.00 197.6 15829.68 3,557,825 3694268570
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.samp
lesize,i.dataobj#,nvl(i.degree,1),nvl(i.instances,1),i.rowcnt,mo
3785.93 292,986,357 0.00 37.1 3173.37 318,284 2030260024
select pos#,intcol#,col#,spare1,bo#,spare2,spare3 from icol$ whe
re obj#=:1
3033.21 61,730,658 0.00 29.7 2751.32 80,629 947984246
select con#,type#,condlength,intcols,robj#,rcon#,match#,refact,n
vl(enabled,0),rowid,cols,nvl(defer,0),mtime,nvl(spare1,0),spare2
,spare3 from cdef$ where obj#=:1
2683.42 88,964,442 0.00 26.3 1343.65 1,918,993 2926525797
select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#
,iniexts,NVL(lists,65535),NVL(groups,65535),cachehint,hwmincr, N
VL(spare1,0),NVL(scanhint,0),NVL(bitmapranges,0) from seg$ where
ts#=:1 and file#=:2 and block#=:3
2099.10 19,861,934 0.00 20.5 1686.31 275,966 839312984
select name,intcol#,segcol#,type#,length,nvl(precision#,0),decod
e(type#,2,nvl(scale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180
,scale,181,scale,182,scale,183,scale,231,scale,0),null$,fixedsto
rage,nvl(deflength,0),default$,rowid,col#,property, nvl(charseti
.......
SQL ordered by Gets DB/Inst: **/** Snaps: 309391-309401
-> End Buffer Gets Threshold: 10000 Total Buffer Gets: 522,570,453
-> Captured SQL accounts for 24.6% of Total Buffer Gets
-> SQL reported below exceeded 1.0% of Total Buffer Gets
CPU Elapsd Old
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
1,259,475,664 292,986,357 4.3 271.8 3173.37 3785.93 2030260024
select pos#,intcol#,col#,spare1,bo#,spare2,spare3 from icol$ whe
re obj#=:1
941,465,387 61,730,658 15.3 203.1 2751.32 3033.21 947984246
select con#,type#,condlength,intcols,robj#,rcon#,match#,refact,n
vl(enabled,0),rowid,cols,nvl(defer,0),mtime,nvl(spare1,0),spare2
,spare3 from cdef$ where obj#=:1
609,221,947 67,656,420 9.0 131.5 ######## 20188.91 3694268570
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.samp
lesize,i.dataobj#,nvl(i.degree,1),nvl(i.instances,1),i.rowcnt,mo
266,918,498 88,964,442 3.0 57.6 1343.65 2683.42 2926525797
select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#
,iniexts,NVL(lists,65535),NVL(groups,65535),cachehint,hwmincr, N
VL(spare1,0),NVL(scanhint,0),NVL(bitmapranges,0) from seg$ where
ts#=:1 and file#=:2 and block#=:3
So numbers are pretty high and I didn't see that few months ago. I didn't find anything useful in Oracle Support. Could someone have idea what is that or what should I look?