Este artigo tem como objetivo demonstrar os 50 Top Comandos SQL ordenados por Buffer Gets no banco de dados Oracle.

Este script é muito útil para verificar quais comandos utiliza mais buffer cache.
Segue o script abaixo.

SELECT *
FROM (SELECT SQL_FULLTEXT, BUFFER_GETS
FROM V$SQL
ORDER BY BUFFER_GETS DESC)
WHERE ROWNUM <= 50;

Vamos executá-lo no ambiente de banco de dados Oracle.

SQL> set lines 500
SQL> set pages 500
SQL> SELECT *
FROM (SELECT SQL_FULLTEXT, BUFFER_GETS
        FROM V$SQL
       ORDER BY BUFFER_GETS DESC)
WHERE ROWNUM <= 50;  2    3    4    5

SQL_FULLTEXT                                                                     BUFFER_GETS
-------------------------------------------------------------------------------- -----------
call dbms_stats.gather_database_stats_job_proc (  )                                   178628
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_       23665
select /*+ rule */ bucket, endpoint, col#, epvalue from histgrm$ where obj#=:1 a       15013
SELECT SPARE4 FROM SYS.OPTSTAT_HIST_CONTROL$ WHERE SNAME = :B1                         12696
select intcol#,nvl(pos#,0),col#,nvl(spare1,0) from ccol$ where con#=:1                 10585
select obj#,type#,ctime,mtime,stime, status, dataobj#, flags, oid$, spare1, spar       10244
select position#,sequence#,level#,argument,type#,charsetid,charsetform,propertie       10073
select pos#,intcol#,col#,spare1,bo#,spare2,spare3 from icol$ where obj#=:1              8988
select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.property,i.pctf        7523
select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#, nvl(proper        6966
select job, nvl2(last_date, 1, 0) from sys.job$ where (((:1 <= next_date) and (n        6069
select con#,type#,condlength,intcols,robj#,rcon#,match#,refact,nvl(enabled,0),ro        5999
select order#,columns,types from access$ where d_obj#=:1                                5500
insert into histgrm$(obj#,intcol#,row#,bucket,endpoint,col#,epvalue)values(:1,:2        5440
select name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2,nvl(sc        4164
select t.ts#,t.file#,t.block#,nvl(t.bobj#,0),nvl(t.tab#,0),t.intcols,nvl(t.cluco        4151
insert into "SYS"."ALERT_QT"  (q_name, msgid, corrid, priority, state, delay, ex        3312
select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname from obj$         3221
select /*+ index(idl_ub1$ i_idl_ub11) +*/ piece#,length,piece from idl_ub1$ wher        3073
delete from histgrm$ where obj#=:1 and intcol#=:2 and row#=:3                           2990
SELECT COUNT(UNQ) UNQ, COUNT(PFX) PFX FROM (SELECT /*+ first_rows(1) leading(cc)        2709
select procedure#,procedurename,properties,itypeobj# from procedureinfo$ where o        2472
select col#,intcol#,toid,version#,packed,intcols,intcol#s,flags, synobj#, nvl(ty        2308
select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,NVL(lis        2104
select /*+ index(idl_ub2$ i_idl_ub21) +*/ piece#,length,piece from idl_ub2$ wher        1979
select /*+ index(idl_sb4$ i_idl_sb41) +*/ piece#,length,piece from idl_sb4$ wher        1878
select con#,obj#,rcon#,enabled,nvl(defer,0),spare2,spare3 from cdef$ where robj#        1685
select intcol#, toid, version#, intcols, intcol#s, flags, synobj# from subcoltyp        1485
select l.col#, l.intcol#, l.lobj#, l.ind#, l.ts#, l.file#, l.block#, l.chunk, l.        1415
select intcol#,type,flags,lobcol,objcol,extracol,schemaoid,  elemnum from opqtyp        1316
SELECT SU.NAME, SO.NAME, A.STATSTYPE#, C.INTCOL# FROM ASSOCIATION$ A, OBJ$ O, US        1265
select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2))from objauth$ w        1264
SELECT T.PROPERTY FROM SYS.USER$ U, SYS.OBJ$ O, SYS.TAB$ T WHERE U.NAME = :B2 AN        1186
select col#,intcol#,reftyp,stabid,expctoid from refcon$ where obj#=:1 order by i        1146
select /*+ index(idl_char$ i_idl_char1) +*/ piece#,length,piece from idl_char$ w        1145
select u.name, o.name, a.interface_version#, o.obj#      from association$ a, us        1132
select col#, grantee#, privilege#,max(mod(nvl(option$,0),2)) from objauth$ where        1130
select procedure#,entrypoint# from procedureplsql$ where obj#=:1 order by proced        1072
select col#,intcol#,ntab# from ntab$ where obj#=:1 order by intcol# asc                 1063
update tab$ set ts#=:2,file#=:3,block#=:4,bobj#=decode(:5,0,null,:5),tab#=decode        1020
select audit$,options from procedure$ where obj#=:1                                      919
select bo#, intcol# from icoldep$ where obj#=:1                                          843
 begin      dbms_aqadm_sys.remove_all_nondurablesub(:1, :2);   end;                      777
select col#,intcol#,charsetid,charsetform from col$ where obj#=:1 order by intco         764
insert into sys.wri$_optstat_histhead_history (obj#,intcol#,savtime,flags, null_         758
update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:6,cache=         684
INSERT INTO RECENT_RESOURCE_INCARNATIONS$ ( RESOURCE_TYPE, RESOURCE_ID, RESOURCE         674
delete from dependency$ where d_obj#=:1                                                  670
select increment$,minvalue,maxvalue,cycle#,order$,cache,highwater,audit$,flags f         596
select obj# from oid$ where user#=:1 and oid$=:2                                         578

50 rows selected.

SQL>

Pronto obtivemos os comandos top sql por buffer gets.

Autor: Maycon Tomiasi

Formado em Tecnologia da Informação na FIPP (Faculdade de Informática de Presidente Prudente), Analista DBA Oracle pela Teiko Soluções em Tecnologia da Informação, residente em Blumenau/ SC, Certificado OCP 10g/11g/12c, OCS 11g Implementation, OCE 11g Performance Tuning, OCE 11g RAC & GRID e OPN Specialist. Conhecimentos em PHP.

• 
•