Unes quantes queries per trobar bottlenecks a Oracle

Totes les queries que es mostren a continuació fan servir les "taules" gv$ que son per instal·lacions en RAC, si teniu versions anteriors substituir gv$ per v$

Tampoc he provat amb diferents versions d'Oracle, a mi em funciona amb Oracle 10gR2

Bloquejos

Query per mirar bloquejos, diu el sql_text que esta bloquejant, pot servir com a pista per eliminar colls d'ampolla provocats per bloquejos.

select t.SQL_TEXT, t.LAST_ACTIVE_TIME, c.owner, c.object_name
, DECODE(l.block, 0, 'Not Blocking', 1, 'Blocking', 2, 'Global') STATUS
, DECODE(a.locked_mode, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TO_CHAR(lmode)) MODE_HELD
, DECODE(l.type, 'RT','Redo Log Buffer', 'TD','Dictionary', 'TM','DML', 'TS','Temp Segments', 'TX','Transaction', 'UL','User', 'RW','Row Wait', l.type) LOCK_TYPE
, c.object_type, s.sid, s.serial#, s.status, s.osuser, s.machine, s.logon_time
from gv$sqlarea t
, gv$process p
, gv$lock l
, gv$locked_object a
, gv$session s
, dba_objects c
where s.sid = a.session_id
and a.object_id = c.object_id
and p.addr = s.paddr
and t.ADDRESS=s.SQL_ADDRESS
and (a.object_id = l.id1)
order by s.inst_id, s.username, s.logon_time


Long Ops

Permet veure un històric de sessions que han trigat molt i el seu sql associat, la taula dba_hist_sqltext ha estat tot un descobriment perquè pots mirar sqls històrics que han provocat colls d'ampolla o fins i tot casques de Base de Dades...

select lo.START_TIME, to_char(substr(st.sql_text, 1, 3000)) sql, lo.opname, lo.target, lo.TOTALWORK
from dba_hist_sqltext st
, gv$session_longops lo
where (lo.target_desc is null or lo.TARGET_DESC not like '%$%')
and lo.username!='SYS'
and lo.SQL_ID=st.sql_id
order by lo.start_time desc


FULL SCANS històrics

Aquesta query busca a l'històric de explain plans (un altre descobriment la dba_hist_sql_plan) i treu una estadística dels objectes sobre els que s'han fet mes full scans

select dhsp.object_owner, dhsp.object_name, count(*) num, sum(dhsp.cpu_cost) sum_cpu_cost, min(timestamp) primera_execucio, max(timestamp) ultima_execucio
from Dba_Hist_Sql_Plan dhsp
where dhsp.operation = 'TABLE ACCESS'
and dhsp.options='FULL'
and dhsp.object_owner!='SYS'
and dhsp.object_owner!='SYSTEM'
and dhsp.object_owner!='SYSMAN'
group by dhsp.object_owner, dhsp.object_name
order by count(*) desc, sum(dhsp.cpu_cost) desc
--order by timestamp desc


Full scans històrics amb la query que provoca el full scan

Igual que l'anterior pero et diu la query que esta provocant el full scan en questio

select to_char(substr(dhst.sql_text, 1, 3000)) sql, dhsp.object_owner, dhsp.object_name, count(*)
from dba_hist_sqltext dhst
, Dba_Hist_Sql_Plan dhsp
where dhsp.operation = 'TABLE ACCESS'
and dhsp.options='FULL'
and dhsp.object_owner!='SYS'
and dhsp.object_owner!='SYSTEM'
and dhsp.object_owner!='SYSMAN'
and dhsp.sql_id=dhst.sql_id
group by to_char(substr(dhst.sql_text, 1, 3000)), dhsp.object_owner, dhsp.object_name
order by count(*) desc


Com sempre dic, la millor eina per un DBA cada vegada que esta buscant taules de diccionari noves de versió a versió o busca a cegues informació útil que tingui emmagatzemada internament el gestor de BD, es fer la següent query:


select * from dict;


Que treu totes les taules del diccionari d'Oracle amb una descripció.

Gracies a aquestes queries, al suport del nostre estimat DBA (Manel Palop) i a algun cop de mà extern de Nucli Experts, hem pogut passar de dos penjades de base de dades per setmana a que funcioni el sistema com una seda.

Evidentment, el problema era del desenvolupador (perque no dir-ho, meu !) que havia provocat contenció en una taula per un error de programació.

Però si no fas servir una base de dades perquè la tens ? Per això existeix el Tuning, no ?

:)