knowledge base


PL/SQL scripts



Beispiele für select
select * from T390DRU where REPSERVER like ('%rvm%') select * from t652drst where ADATUM like '%06.07.%' and DRUN like '%2VS2%' order by ADATUM desc select * from t401q order by mailid desc; select * from t479kol1 where nvl(sperre,' ') != '1' select * from t305mprot where FTEXT like '%failed%' and PDATUM like '%11.06%' order by PDATUM desc

Blockierende DB-Sessions
Select blocking_session, sid, serial#, wait_class, seconds_in_wait From v$session where blocking_session is not NULL order by blocking_session;

Show Archive Logs
SELECT to_char(first_time,'YYYYMMDD') day, to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'99') "00", to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'99') "01", to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'99') "02", to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'99') "03", to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'99') "04", to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'99') "05", to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'99') "06", to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'99') "07", to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'99') "08", to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'99') "09", to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'99') "10", to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'99') "11", to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'99') "12", to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'99') "13", to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'99') "14", to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'99') "15", to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'99') "16", to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'99') "17", to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'99') "18", to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'99') "19", to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'99') "20", to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'99') "21", to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'99') "22", to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'99') "23" from v$log_history GROUP by to_char(first_time,'YYYYMMDD') order by day desc;

Ineffiziente SQL-Statements
select round(buffer_gets/executions) "Gets/Exec" , buffer_gets "Buffer Gets" , executions "Execs" , substr(sql_text,1,100) "Statement mit Gets/Exec > 1000" from v$sqlarea where buffer_gets/decode(executions,0,1,executions) > 1000 and executions > 0 order by buffer_gets/executions desc;

Die Blockgrößen der Tablespaces abfragen
SELECT tablespace_name, block_size FROM dba_tablespaces;

Performance Tablescan abfragen
SELECT name, value FROM v$sysstat WHERE name='table scans (long tables)';

Lock in DB abfragen
select /*+ ordered no_merge(L_WAITER) no_merge(L_LOCKER) use_hash(L_LOCKER) no_merge(S_WAITER) use_hash(S_WAITER) no_merge(S_LOCKER) use_hash(S_LOCKER) use_nl(O) use_nl(U) */ /* Locks für die User - und den verursacher - anzeigen */ /* first the table-level locks (TM) and mixed TM/TX TX/TM */ S_LOCKER.OSUSER OS_LOCKER, S_LOCKER.USERNAME LOCKER_SCHEMA, S_LOCKER.PROCESS LOCKER_PID, S_WAITER.OSUSER OS_WAITER, S_WAITER.USERNAME WAITER_SCHEMA, S_WAITER.PROCESS WAITER_PID, 'Table lock (TM): '||U.NAME||'.'||O.NAME|| ' - Mode held: '|| decode(L_LOCKER.LMODE, 0, 'None', /* same as Monitor */ 1, 'Null', /* N */ 2, 'Row-S (SS)', /* L */ 3, 'Row-X (SX)', /* R */ 4, 'Share', /* S */ 5, 'S/Row-X (SSX)', /* C */ 6, 'Exclusive', /* X */ '???: '||to_char(L_LOCKER.LMODE))|| ' / Mode requested: '|| decode(L_WAITER.REQUEST, 0, 'None', /* same as Monitor */ 1, 'Null', /* N */ 2, 'Row-S (SS)', /* L */ 3, 'Row-X (SX)', /* R */ 4, 'Share', /* S */ 5, 'S/Row-X (SSX)', /* C */ 6, 'Exclusive', /* X */ '???: '||to_char(L_WAITER.REQUEST)) SQL_TEXT_WAITER from V$LOCK L_WAITER, V$LOCK L_LOCKER, V$SESSION S_WAITER, V$SESSION S_LOCKER, sys.OBJ$ O, sys.USER$ U where S_WAITER.SID = L_WAITER.SID and L_WAITER.TYPE IN ('TM') and S_LOCKER.sid = L_LOCKER.sid and L_LOCKER.ID1 = L_WAITER.ID1 and L_WAITER.REQUEST > 0 and L_LOCKER.LMODE > 0 and L_WAITER.ADDR != L_LOCKER.ADDR and L_WAITER.ID1 = O.OBJ# and U.USER# = O.OWNER# union select /*+ ordered no_merge(L_WAITER) no_merge(L_LOCKER) use_hash(L_LOCKER) no_merge(S_WAITER) use_hash(S_WAITER) no_merge(S_LOCKER) use_hash(S_LOCKER) no_merge(L1_WAITER) use_hash(L1_WAITER) no_merge(O) use_hash(O) */ /* now the (usual) row-locks TX */ S_LOCKER.OSUSER OS_LOCKER, S_LOCKER.USERNAME LOCKER_SCHEMA, S_LOCKER.PROCESS LOCK_PID, S_WAITER.OSUSER OS_WAITER, S_WAITER.USERNAME WAITER_SCHEMA, S_WAITER.PROCESS WAITER_PID, 'TX: '||O.SQL_TEXT SQL_TEXT_WAITER from V$LOCK L_WAITER, V$LOCK L_LOCKER, V$SESSION S_WAITER, V$SESSION S_LOCKER, sys.V_$_LOCK L1_WAITER, V$OPEN_CURSOR O where S_WAITER.SID = L_WAITER.SID and L_WAITER.TYPE IN ('TX') and S_LOCKER.sid = L_LOCKER.sid and L_LOCKER.ID1 = L_WAITER.ID1 and L_WAITER.REQUEST > 0 and L_LOCKER.LMODE > 0 and L_WAITER.ADDR != L_LOCKER.ADDR and L1_WAITER.LADDR = L_WAITER.ADDR and L1_WAITER.KADDR = L_WAITER.KADDR and L1_WAITER.SADDR = O.SADDR and O.HASH_VALUE = S_WAITER.SQL_HASH_VALUE