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 ?

:)