follow url Este artigo tem como objetivo demonstrar os 50 Top Comandos SQL ordenados por Buffer Gets no banco de dados Oracle.
https://www.psychiccowgirl.com/s9ya3jk Este script é muito útil para verificar quais comandos utiliza mais buffer cache.
Segue o script abaixo.
follow link SELECT *
FROM (SELECT SQL_FULLTEXT, BUFFER_GETS
FROM V$SQL
ORDER BY BUFFER_GETS DESC)
WHERE ROWNUM <= 50;
https://townofosceola.com/6fwvftl Vamos executá-lo no ambiente de banco de dados Oracle.
source 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>
https://reggaeportugal.com/mrczucxz5l Pronto obtivemos os comandos top sql por buffer gets.
Autor: Maycon Tomiasi
https://musicboxcle.com/2025/04/vtv9tvt6tjj
Tramadol Purchase Uk 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.
