Statspack - top queries are database internal queries
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: MTDBPROD Instance: mtdbprod 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
0