SCRIPTS FOR ROLLBACK
Who is Using Which Rollback?
Description: Sometimes we need (or want) to know who is using rollback and how much they are using. This script will show you who is using which rbs, how much rollback they are using, their sid/serial, and, optionally, when their session began and when they were last active in the database.
SELECT r.name rbs,nvl(s.username, 'None') oracle_user,s.osuser client_user,p.username unix_user,to_char(s.sid)||','||to_char(s.serial#) as sid_serial,
p.spid unix_pid,
--TO_CHAR(s.logon_time, 'mm/dd/yy hh24:mi:ss') as login_time,TO_CHAR(sysdate - (s.last_call_et) / 86400,'mm/dd/yy hh24:mi:ss') as last_txn,
t.used_ublk * TO_NUMBER(x.value)/1024 as undo_kb
FROM v$process p,v$rollname r,v$session s,v$transaction t,v$parameter x
WHERE s.taddr = t.addr AND s.paddr = p.addr(+) AND r.usn = t.xidusn(+) AND x.name = 'db_block_size'
ORDER BY r.name;
Tablespace Free Space Report
select total.tablespace_name sname,total.bytes/1048576 ssize,count(free.bytes) extents,max(free.bytes)/1048576 mbytes,
sum (free.bytes)/1048576 tbytes,(total.bytes - sum (free.bytes))/1048576 ubytes, round(sum(free.bytes)/total.bytes * 100) pct
from sys.tfview total,sys.dba_free_space free
where total.tablespace_name = free.tablespace_name
group by total.tablespace_name, total.bytes
Write a Message to the Alert Log
If you want to write a message to the alert log, you can use the undocumented KSDWRT procedure of the DBMS_SYSTEM package.
This procedure has two parameters, the first one must be "2" to write to the alert file, the second one is the message you want to write.
Here is an example:
execute sys.dbms_system.ksdwrt(2,to_char(sysdate)|| ' -- ');
Description: Finds the two highest salaries from table "emp".
Code:
select a.empno,a.sal from emp a where 2>(select count(*) from emp where sal>a.sal);
Description: Finds the two lowest salaries from table "emp".
select a.empno,a.sal from emp a where 2>(select count(*) from emp where sa
MEMORY TUNING
Description: Computer TP rate to get a better idea on sizing memory objectsSELECT SUM(s.value/(86400*(SYSDATE - TO_DATE(i.VALUE,'J')))) "tps"
FROM V$SYSSTAT s, V$INSTANCE i
WHERE s.NAME in ('user commits','transaction rollbacks') AND i.KEY = 'STARTUP TIME - JULIAN';
Description: This script reports how many hours it has been since the rollback segments wrapped
select n.name,round(24*(sysdate-to_date(i1.value||' '||i2.value,'j SSSSS')) /(s.writes/s.rssize),1) "Hours"
from v$instance i1,v$instance i2,v$rollname n,v$rollstat s
where i1.key = 'STARTUP TIME - JULIAN' and i2.key = 'STARTUP TIME - SECONDS' and n.usn = s.usn and s.status = 'ONLINE'
Description: When you connect to sqlplus, you see the the following sql prompt
The following code works on Oracle 8i (8.1.5, 8.1.6, 8.1.7).You have to insert the following line of code in glogin.sql which is usually found in $ORACLE_HOME/sqlplus/admin
Select SYS_CONTEXT('USERENV','SESSION_USER' ) Usr From Dual;
The following does not work in 8.1.5 but works in 8.1.6 or above
Select SYS_CONTEXT('USERENV','DB_NAME') DBNAME From Dual;
If you are using 8.1.5, use this .
Select Global_Name DBNAME from Global_Name;
Please note that this method will work only when you make a new sql plus session because when you make a new sql plus session, then only glogin.sql is executed.
Description: This script will show you the user's OS name, Username in the database and the SQL Text they are running
SELECT osuser, username, sql_text from v$session a, v$sqltext b where a.sql_address =b.address order by address, piece
Description: In N tier structure it is really hard to track user session specially in a web enviroment,
All users connect to database using the same application , so all requests are going to database on behalf of one oracle user. then how can you track actions of one ENDUSER (application user). In the v$session, there are serval columns you can use to distinguish ENDUSER such as Moule,Action and Client info. for example, you can use dbms_application_info.set_client_info('client info') to set client info accroding to user's ip or loginname in your application, then it is easy to find out what sql statement was execute by this client use following script
select s.sid, s.username, s.program, oc.sql_text
from v$session s, v$open_cursor oc
where s.saddr=oc.saddr and s.sid=oc.sid
and s.client_info='client_info';
if you want to capture all sql in real time, then there is a tool Oracle Session Manager in http://www.wangz.net can help you do it.
Description: This script will display the estimated work and time remaining for long operations (i.e. over 10 minutes).
select sid, message || '(' || time_remaining || ')' "Long Ops"
from v$session_longops
where time_remaining > 600;
Description: The output of this script will display all sql statements in the shared pool that are doing full table scans.
--run this as sys in SQL worksheet
create table full_sql (sql_text varchar2(1000), executions number);
create or replace procedure p_findfullsql as
v_csr number;
v_rc number;
v_string varchar2(2000);
v_count number;
cursor c1 is select sql_text,executions from v$sqlarea where lower(sql_text) like '%select%';
begin
for x1 in c1 loop
delete from plan_table ;
Begin
v_Csr := DBMS_SQL.OPEN_CURSOR;
v_string := 'explain plan for ' ;
v_string := v_string||x1.sql_text ;
DBMS_SQL.PARSE(v_csr, v_string, DBMS_SQL.V7);
v_rc := DBMS_SQL.EXECUTE(v_csr);
DBMS_SQL.CLOSE_CURSOR(v_csr);
Exception
when others then
null;
End ;
select count(*) into v_count from plan_table where options like '%FULL%' and operation like '%TABLE%' ;
if v_count > 0 then
insert into full_sql(sql_text,executions) values (x1.sql_text, x1.executions) ;
end if;
end loop ;
commit;
end ;
/
execute p_findfullsql ;
select * from full_sql;
drop table full_sql;
Description: An interesting script submitted by one of our readers to size the SGA
Code:
set serverout on
DECLARE
l_uplift CONSTANT NUMBER := 0.3;
/* i.e. 30% above calculated */
l_numusers NUMBER DEFAULT 50;
/* Change ths to a predicted number existing database */
l_avg_uga NUMBER;
l_max_uga NUMBER;
l_sum_sql_shmem NUMBER;
l_sum_obj_shmem NUMBER;
l_total_avg NUMBER;
l_total_max NUMBER;
BEGIN
dbms_output.enable(20000);
IF ( l_numusers = 0) THEN
SELECT sessions_highwater
INTO l_numusers
FROM v$license;
dbms_output.put_line('Maximum concurrent users on this database = '||TO_CHAR(l_numusers));
ELSE
dbms_output.put_line('Calculating SGA for = '||TO_CHAR(l_numusers)||' concurrent users');
END IF;
dbms_output.new_line;
SELECT
avg(value)*l_numusers
,max(value)*l_numusers
INTO l_avg_uga, l_max_uga
FROM v$sesstat s, v$statname n
WHERE s.statistic# = n.statistic#
AND n.name = 'session uga memory max';
SELECT sum(sharable_mem) INTO l_sum_sql_shmem FROM v$sqlarea;
SELECT sum(sharable_mem) INTO l_sum_obj_shmem FROM v$db_object_cache;
l_total_avg := l_avg_uga + l_sum_sql_shmem + l_sum_obj_shmem;
l_total_max := l_max_uga + l_sum_sql_shmem + l_sum_obj_shmem;
dbms_output.put_line('Recommended Shared_pool size between :' || TO_CHAR(ROUND(l_total_avg + (l_total_avg * l_uplift), 0) ) ||' and '
|| TO_CHAR(ROUND(l_total_max + (l_total_max * l_uplift), 0) ) ||' bytes');
dbms_output.put_line('Recommended Shared_pool size between :' || TO_CHAR(ROUND((l_total_avg + (l_total_avg * l_uplift)) /(1024*1024), 0) )
||' and '|| TO_CHAR(ROUND((l_total_max + (l_total_max * l_uplift )) /1024*1024) ,0) ) ||' M bytes');
end;
/
Description: This script uses the new way of calculating DB_Block Buffer Efficiency. Logical Read: consistent gets + db block gets
Hit-Ratio: (logical reads - physical reads) / (logical reads)
Our Aim: OLTP >= 95%, DSS/Batch >= 85%
Solution: enlarge block buffers, tune SQL, check appropriateness of indexes
Code:
select name, ((consistent_gets + db_block_gets) - physical_reads) /
(consistent_gets + db_block_gets) * 100 "Hit Ratio%"
from v$buffer_pool_statistics
where physical_reads > 0;
Description: Haven't you ever thought there should be an easier way to do the EXPLAIN PLAN and TKPROF statistics than to edit your
queries to add the commands (like EXPLAIN PLAN SET...), or to have to find or write a script that automates this? It should
be an automatic part of SQL*Plus. Well, as of SQL*Plus 3.3 it is!! The command is called 'SET AUTOTRACE ON'!
Code:
The SET AUTOTRACE Command
In SQL*Plus 3.3 there is a little known command (at least I didn't know about it until recently) called SET AUTOTRACE. It is
documented in the newest SQL*Plus document set, but who really reads the whole document set for changes? Well I did not. It
is very simple to use. Just type the command:
SET AUTOTRACE ON
And then run your select statement. Example:
SQL> SET AUTOTRACE ON
SQL> SELECT d.deptno, d.dname, e.empno, e.ename
2 FROM dept d, emp e
3 WHERE d.deptno = e.deptno
4 /
DEPTNO DNAME EMPNO ENAME
---------- -------------- ---------- ----------
10 ACCOUNTING 7839 KING
.
.
30 SALES 7900 JAMES
30 SALES 7521 WARD
14 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 MERGE JOIN
2 1 SORT (JOIN)
3 2 TABLE ACCESS (FULL) OF 'EMP'
4 1 SORT (JOIN)
5 4 TABLE ACCESS (FULL) OF 'DEPT'
Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
2 consistent gets
0 physical reads
0 redo size
670 bytes sent via SQL*Net to client
376 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
14 rows processed
There are also some other options, for example there is a TRACEONLY option which supresses the SQL output. See the
SQL*Plus 3.3 manual for a full description.
Some setup issues:
If you go off and try this on your instance, you may run into some problems. There are a few setup steps that need to be taken to
make this work:
1.Make sure you have access to PLAN_TABLE. If you don't, create it using utlxplan.sql (It should be in a directory like
$ORACLE_HOME/rdbms73/admin/) and make sure you have access to it from the user you are using to tune the SQL.
2.You also need to create the PLUSTRACE role, and grant it to the desired users. The script to create this role is in:
$ORACLE_HOME/plus33/Plustrce.sql
It has to be run from SYS in order to have the correct security access. Then grant the role to the desired users or ROLEs.
Description: Find the top 20 longest running processes in unix. Useful for high CPU bound systems with large number of users. Script also identifies processes without a oracle session.
Code:
#!/bin/ksh
#
# Find Highest CPU used Oracle processes and get the Username
# and SID from oracle
# Only 3 character SIDNAME is displayed - Adjust the script according to your need.
#
date
echo " Top 20 CPU Utilized Session from `hostname`"
echo " ============================================"
echo "O/S Oracle Session Session Serial UNIX Login Ora CPU Time"
echo "ID User ID Status ID No ID MMDD:HHMISS SID Used"
echo "-------- ----------- -------- ------- ------- ------- ----------- --- --------"
ps -ef|grep LOCAL|cut -c1-15,42-79|sort -rn +2 | head -20 | while read LINE
do
SIDNAME=`echo $LINE | awk '{ print $4 }' | cut -c7-14`
CPUTIME=`echo $LINE | awk '{ print $3 }'`
UNIXPID=`echo $LINE | awk '{ print $2 }'`
#echo $SIDNAME $CPUTIME $UNIXPID
export ORACLE_SID=$SIDNAME
SIDNAME=`echo $ORACLE_SID | cut -c4-6`
export ORACLE_HOME=`/dba_script/bin/find_ohome.sh ${ORACLE_SID}`
export SHLIB_PATH=$ORACLE_HOME/lib:/usr/lib
export TMPDIR=/tmp
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export SQLPLUS="$ORACLE_HOME/bin/sqlplus -s / "
$SQLPLUS >> $wlogfile <
set pages 0 lines 80 trims on echo off verify off pau off
column pu format a8 heading 'O/S|ID' justify left
column su format a11 heading 'Oracle|User ID' justify left
column stat format a8 heading 'Session|Status' justify left
column ssid format 999999 heading 'Session|ID' justify right
column sser format 999999 heading 'Serial|No' justify right
column spid format 999999 heading 'UNIX|ID' justify right
column ltime format a11 heading 'Login|Time' justify right
select p.username pu,
s.username su,
s.status stat,
s.sid ssid,
s.serial# sser,
lpad(p.spid,7) spid,
to_char(s.logon_time, 'MMDD:HH24MISS') ltime,
'$SIDNAME $CPUTIME'
from v\$process p,
v\$session s
where p.addr=s.paddr
and p.spid=$UNIXPID
union all
select a.username, 'Kill Me', 'NoOracle', a.pid, a.serial#,
lpad(a.spid,7) spid, 'KILL UNIXID', '$SIDNAME $CPUTIME'
from v\$process a
where a.spid = $UNIXPID
and not exists (select 1 from v\$session s
where a.addr=s.paddr);
EOF
done
echo "-------- ----------- -------- ------- ------- ------- ----------- --- --------"
date
#
# End of Script
Description: Monitor and Verify DEAD Locks: Holding and Waiting Sessions
Code:
Doc
Verify DEAD LOCK situation
Script: waiters.sql
Date: 07/1999
Revision:
#
set lines 80 echo on ver off timing on term on pages 60 feed on head on
spool DEAD_LOCK_WAITERS.LST
col " " for A25
col "Holding Session Info" for A25
col "Waiting Session Info" for A25
select --+ ORDERED
'Session ID' || CHR(10) ||
'Mode Held' || CHR(10) ||
'Lock Type' || CHR(10) ||
'Mode Requested' || CHR(10) ||
'Lock ID 1' || CHR(10) ||
'Lock ID 2' " "
-------------------------------- END of Header
,
HH.session_id || CHR(10) ||
HH.mode_held || CHR(10) ||
HH.lock_type || CHR(10) ||
HH.mode_requested || CHR(10) ||
HH.lock_id1 || CHR(10) ||
HH.lock_id2 "Holding Session Info"
------------------------------ END of Holding Session
,
Ww.session_id || CHR(10) ||
WW.mode_held || CHR(10) ||
Ww.lock_type || CHR(10) ||
Ww.mode_requested || CHR(10) ||
Ww.lock_id1 || CHR(10) ||
Ww.lock_id2 "Waiting Session Info"
------------------------------ END of Waiting Session
from
-----------------------------------------------------
(
select /*+ RULE */ *
from SYS.dba_locks
where blocking_others = 'Blocking' and
mode_held != 'None' and
mode_held != 'Null'
) HH,
-----------------------------------------------------
(
select /*+ RULE */ *
from SYS.dba_locks
where mode_requested != 'None'
) WW
-----------------------------------------------------
where WW.lock_type = HH.lock_type and
WW.lock_id1 = HH.lock_id1 and
WW.lock_id2 = HH.lock_id2
;
spool off
Description: This is possibly one of the most useful tuning scripts available. The V$SYSTEM_EVENT dynamic performance view is the highest-level view of the "Session Wait Interface". Information in this view is cumulative since the database instance was started, and one can get a very good idea of what types of contention a database instance is (or is not) experiencing by monitoring this view.
Code:
/**********************************************************************
* File: systemevent.sql
* Type: SQL*Plus script
* Description:
* This is possibly one of the most useful tuning scripts
* available.
*
* The V$SYSTEM_EVENT dynamic performance view is the highest-level
* view of the "Session Wait Interface". Information in this view
* is cumulative since the database instance was started, and one
* can get a very good idea of what types of contention a database
* instance is (or is not) experiencing by monitoring this view.
*
* NOTE: this script sorts output by the TIME_WAITED column in the
* V$SYSTEM_EVENT view. If the Oracle initialization parameter
* TIMED_STATISTICS is not set to TRUE, then the TIME_WAITED
* column will not be populated.
*
* Please *disregard* the advice of people who insist that turning
* off TIMED_STATISTICS is somehow a performance boost. Whatever
* performance overhead that might be incurred is more than
* compensated for by the incredible tuning information that
* results...
*
*********************************************************************/
set echo off feedback off timing off pause off verify off
set pagesize 100 linesize 500 trimspool on trimout on
col event format a26 truncate heading "Event Name"
col total_waits format 999,990.00 heading "Total|Waits|(in 1000s)"
col total_timeouts format 999,990.00 heading "Total|Timeouts|(in 1000s)"
col time_waited format 999,990.00 heading "Time|Waited|(in Hours)"
col pct_significant format 90.00 heading "% of|Concern"
col average_wait format 990.00 heading "Avg|Wait|(Secs)"
col instance new_value V_INSTANCE noprint
select lower(replace(t.instance,chr(0),
)) instance
from sys.v_$thread t,
sys.v_$parameter p
where p.name = 'thread'
and t.thread# = to_number(decode(p.value,'0','1',p.value));
col total_time_waited new_value V_TOTAL_TIME_WAITED noprint
select sum(time_waited) total_time_waited
from sys.v_$system_event
where event not in ('SQL*Net message from client',
'rdbms ipc message',
'slave wait',
'pmon timer',
'smon timer',
'rdbms ipc reply',
'SQL*Net message to client',
'SQL*Net break/reset to client',
'inactive session',
'Null event')
/
select event,
(total_waits / 1000) total_waits,
(total_timeouts / 1000) total_timeouts,
(time_waited / 360000) time_waited,
decode(event,
'SQL*Net message from client', 0,
'rdbms ipc message', 0,
'slave wait', 0,
'pmon timer', 0,
'smon timer', 0,
'rdbms ipc reply', 0,
'SQL*Net message to client', 0,
'SQL*Net break/reset to client', 0,
'inactive session', 0,
'Null event', 0,
(time_waited / &&V_TOTAL_TIME_WAITED)*100) pct_significant,
(average_wait / 100) average_wait
from sys.v_$system_event
where (time_waited/360000) >= 0.01
order by pct_significant desc, time_waited desc
spool sysevent_&&V_INSTANCE
/
spool off
