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.
01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 | SQL> set lines 500SQL> set pages 500SQL> SELECT *FROM (SELECT SQL_FULLTEXT, BUFFER_GETS FROM V$SQL ORDER BY BUFFER_GETS DESC)WHERE ROWNUM <= 50; 2 3 4 5SQL_FULLTEXT BUFFER_GETS-------------------------------------------------------------------------------- -----------call dbms_stats.gather_database_stats_job_proc ( ) 178628select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_ 23665select /*+ rule */ bucket, endpoint, col#, epvalue from histgrm$ where obj#=:1 a 15013SELECT SPARE4 FROM SYS.OPTSTAT_HIST_CONTROL$ WHERE SNAME = :B1 12696select intcol#,nvl(pos#,0),col#,nvl(spare1,0) from ccol$ where con#=:1 10585select obj#,type#,ctime,mtime,stime, status, dataobj#, flags, oid$, spare1, spar 10244select position#,sequence#,level#,argument,type#,charsetid,charsetform,propertie 10073select pos#,intcol#,col#,spare1,bo#,spare2,spare3 from icol$ where obj#=:1 8988select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.property,i.pctf 7523select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#, nvl(proper 6966select job, nvl2(last_date, 1, 0) from sys.job$ where (((:1 <= next_date) and (n 6069select con#,type#,condlength,intcols,robj#,rcon#,match#,refact,nvl(enabled,0),ro 5999select order#,columns,types from access$ where d_obj#=:1 5500insert into histgrm$(obj#,intcol#,row#,bucket,endpoint,col#,epvalue)values(:1,:2 5440select name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2,nvl(sc 4164select t.ts#,t.file#,t.block#,nvl(t.bobj#,0),nvl(t.tab#,0),t.intcols,nvl(t.cluco 4151insert into "SYS"."ALERT_QT" (q_name, msgid, corrid, priority, state, delay, ex 3312select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname from obj$ 3221select /*+ index(idl_ub1$ i_idl_ub11) +*/ piece#,length,piece from idl_ub1$ wher 3073delete from histgrm$ where obj#=:1 and intcol#=:2 and row#=:3 2990SELECT COUNT(UNQ) UNQ, COUNT(PFX) PFX FROM (SELECT /*+ first_rows(1) leading(cc) 2709select procedure#,procedurename,properties,itypeobj# from procedureinfo$ where o 2472select col#,intcol#,toid,version#,packed,intcols,intcol#s,flags, synobj#, nvl(ty 2308select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,NVL(lis 2104select /*+ index(idl_ub2$ i_idl_ub21) +*/ piece#,length,piece from idl_ub2$ wher 1979select /*+ index(idl_sb4$ i_idl_sb41) +*/ piece#,length,piece from idl_sb4$ wher 1878select con#,obj#,rcon#,enabled,nvl(defer,0),spare2,spare3 from cdef$ where robj# 1685select intcol#, toid, version#, intcols, intcol#s, flags, synobj# from subcoltyp 1485select l.col#, l.intcol#, l.lobj#, l.ind#, l.ts#, l.file#, l.block#, l.chunk, l. 1415select intcol#,type,flags,lobcol,objcol,extracol,schemaoid, elemnum from opqtyp 1316SELECT SU.NAME, SO.NAME, A.STATSTYPE#, C.INTCOL# FROM ASSOCIATION$ A, OBJ$ O, US 1265select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2))from objauth$ w 1264SELECT T.PROPERTY FROM SYS.USER$ U, SYS.OBJ$ O, SYS.TAB$ T WHERE U.NAME = :B2 AN 1186select col#,intcol#,reftyp,stabid,expctoid from refcon$ where obj#=:1 order by i 1146select /*+ index(idl_char$ i_idl_char1) +*/ piece#,length,piece from idl_char$ w 1145select u.name, o.name, a.interface_version#, o.obj# from association$ a, us 1132select col#, grantee#, privilege#,max(mod(nvl(option$,0),2)) from objauth$ where 1130select procedure#,entrypoint# from procedureplsql$ where obj#=:1 order by proced 1072select col#,intcol#,ntab# from ntab$ where obj#=:1 order by intcol# asc 1063update tab$ set ts#=:2,file#=:3,block#=:4,bobj#=decode(:5,0,null,:5),tab#=decode 1020select audit$,options from procedure$ where obj#=:1 919select bo#, intcol# from icoldep$ where obj#=:1 843 begin dbms_aqadm_sys.remove_all_nondurablesub(:1, :2); end; 777select col#,intcol#,charsetid,charsetform from col$ where obj#=:1 order by intco 764insert into sys.wri$_optstat_histhead_history (obj#,intcol#,savtime,flags, null_ 758update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:6,cache= 684INSERT INTO RECENT_RESOURCE_INCARNATIONS$ ( RESOURCE_TYPE, RESOURCE_ID, RESOURCE 674delete from dependency$ where d_obj#=:1 670select increment$,minvalue,maxvalue,cycle#,order$,cache,highwater,audit$,flags f 596select obj# from oid$ where user#=:1 and oid$=:2 57850 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.
