SELECT sql_id,
count(*)
FROM v$active_session_history
WHERE sample_time > sysdate - 10/1440
AND session_type <> 'BACKGROUND'
GROUP BY sql_id
ORDER BY count(*) DESC;
SQL_ID COUNT(*)
------------- ----------
gkhffs7nr887f 3
17523pja0qf64 1
SELECT sql_text FROM v$sqlarea WHERE sql_id = '&sql_id';
or
Dump the Contents of ASH or Rolling buffer to a trace file created in User Dump Destination
ALTER SESSION SET EVENTS ‘immediate trace name ashdump, Level 10’;
OR
SQL> oradebug setmypid
SQL> oradebug unlimit
SQL> oradebug dump ashdump 10 -- This will dump last 10 minute content
SQL> oradebug tracefile_name
Sunday 3 July 2011
Oracle session connection status
Oracle session connection status...
http://anuj-singh.blogspot.com/2011/07/oracle-top-sql-spent-more-on-cpuwaitio.html
--For Rac col NAME for a20 select distinct inst_id, decode(nvl(to_char(s.sid),-1),-1,'DISCONNECTED','CONNECTED') "STATUS", topsession.session_id "SESSION_ID", u.name "NAME", topsession.program "PROGRAM", max(topsession.CPU) "CPU", max(topsession.WAITING) "WAITING", max(topsession.IO) "IO", max(topsession.TOTAL) "TOTAL" from ( select ash.inst_id, ash.session_id, ash.session_serial#, ash.user_id, ash.program, sum(decode(ash.session_state,'ON CPU',1,0)) "CPU", sum(decode(ash.session_state,'WAITING',1,0)) - sum(decode(ash.session_state,'WAITING', decode(en.wait_class,'User I/O',1, 0 ), 0)) "WAITING" , sum(decode(ash.session_state,'WAITING', decode(en.wait_class,'User I/O',1, 0 ), 0)) "IO" , sum(decode(session_state,'ON CPU',1,1)) "TOTAL" from gv$active_session_history ash, v$event_name en where en.event# = ash.event# group by inst_id,session_id,user_id,session_serial#,program order by sum(decode(session_state,'ON CPU',1,1)) ) topsession, v$session s, user$ u where u.user# =topsession.user_id /* outer join to v$session because the session might be disconnected */ and topsession.session_id = s.sid (+) and topsession.session_serial# = s.serial# (+) and u.name not in ('SYS','SYSTEM') ---<<<<<<<<<<<<<<<<<<<<<-------- and status!='DISCONNECTED' group by inst_id,topsession.session_id, topsession.session_serial#, topsession.user_id, topsession.program, s.username,s.sid,s.paddr,u.name order by max(topsession.TOTAL) desc -- ===================================== --for Single Instance col NAME for a20 col STATUS for a15 col PROGRAM for a50 select distinct decode(nvl(to_char(s.sid),-1),-1,'DISCONNECTED','CONNECTED') "STATUS", topsession.session_id "SESSION_ID", u.name "NAME", topsession.program "PROGRAM", max(topsession.CPU) "CPU", max(topsession.WAITING) "WAITING", max(topsession.IO) "IO", max(topsession.TOTAL) "TOTAL" from ( select ash.session_id, ash.session_serial#, ash.user_id, ash.program, sum(decode(ash.session_state,'ON CPU',1,0)) "CPU", sum(decode(ash.session_state,'WAITING',1,0)) - sum(decode(ash.session_state,'WAITING', decode(en.wait_class,'User I/O',1, 0 ), 0)) "WAITING" , sum(decode(ash.session_state,'WAITING', decode(en.wait_class,'User I/O',1, 0 ), 0)) "IO" , sum(decode(session_state,'ON CPU',1,1)) "TOTAL" from v$active_session_history ash, v$event_name en where en.event# = ash.event# group by session_id,user_id,session_serial#,program order by sum(decode(session_state,'ON CPU',1,1)) ) topsession, v$session s, user$ u where u.user# =topsession.user_id and /* outer join to v$session because the session might be disconnected */ topsession.session_id = s.sid (+) and topsession.session_serial# = s.serial# (+) and u.name not in ('SYS','SYSTEM') ---<<<<<<<<<<<<<<<<<<<<<-------- and status!='DISCONNECTED' ---<<<<<<<<<<<<<<<<<<<<<-------- group by topsession.session_id, topsession.session_serial#, topsession.user_id, topsession.program, s.username,s.sid,s.paddr,u.name order by max(topsession.TOTAL) desc
set linesize 300
col KILL for a18
col NAME for a20
col PROGRAM for a50
select
distinct
--inst_id,
'''' || topsession.SESSION_ID || ',' || topsession.session_serial# ||',@'|| topsession.inst_id||'''' kill,
decode(nvl(to_char(s.sid),-1),-1,'DISCONNECTED','CONNECTED') "STATUS",
topsession.session_id "SESSION_ID",
u.name "NAME",
topsession.program "PROGRAM",
max(topsession.CPU) "CPU",
max(topsession.WAITING) "WAITING",
max(topsession.IO) "IO",
max(topsession.TOTAL) "TOTAL"
from ( select
ash.inst_id,
ash.session_id,
ash.session_serial#,
ash.user_id,
ash.program,
sum(decode(ash.session_state,'ON CPU',1,0)) "CPU",
sum(decode(ash.session_state,'WAITING',1,0)) - sum(decode(ash.session_state,'WAITING',decode(en.wait_class,'User I/O',1, 0 ), 0)) "WAITING" ,
sum(decode(ash.session_state,'WAITING',decode(en.wait_class,'User I/O',1, 0 ), 0)) "IO" ,sum(decode(session_state,'ON CPU',1,1)) "TOTAL"
from gv$active_session_history ash, v$event_name en
where en.event# = ash.event#
group by ash.inst_id,session_id,user_id,session_serial#,program
order by sum(decode(session_state,'ON CPU',1,1)) ) topsession, v$session s, user$ u
where u.user# =topsession.user_id
/* outer join to v$session because the session might be disconnected */
and topsession.session_id = s.sid (+)
and topsession.session_serial# = s.serial# (+)
and u.name not in ('SYS','SYSTEM') ---<<<<<<<<<<<<<<<<<<<<<--------
and status!='DISCONNECTED'
--group by inst_id,topsession.session_id, topsession.session_serial#, topsession.user_id, topsession.program, s.username,s.sid,s.paddr,u.name
group by '''' || topsession.SESSION_ID || ',' || topsession.session_serial# ||',@'|| topsession.inst_id||'''',topsession.session_id, topsession.session_serial#, topsession.user_id, topsession.program, s.username,s.sid,s.paddr,u.name
order by max(topsession.TOTAL) desc
Oracle Top sql spent more on cpu/wait/io
Oracle Top sql spent more on cpu/wait/io ?
ASH top sqldefine 1='30' ---- minute
SET LINESIZE 200
SET PAGESIZE 200
COLUMN sample_time FORMAT a11
COLUMN metric_name FORMAT a11
COLUMN active_sessions FORMAT 999,999.9999
WITH
cpu AS
(
SELECT TO_CHAR(end_time, 'hh24:mi:ss') sample_time
, DECODE(metric_name, 'CPU Usage Per Sec', 'CPU') metric_name
, ROUND(value/1e2, 4) active_sessions
FROM v$sysmetric_history
WHERE metric_name = 'CPU Usage Per Sec'
AND group_id = 2
AND end_time >= SYSDATE - INTERVAL '60' MINUTE
ORDER BY sample_time
),
user_io AS
(
SELECT TO_CHAR(wcmh.end_time, 'hh24:mi:ss') sample_time
, swc.wait_class metric_name
, ROUND(wcmh.time_waited/wcmh.intsize_csec, 4) active_sessions
FROM v$waitclassmetric_history wcmh
, v$system_wait_class swc
WHERE wcmh.wait_class_id = swc.wait_class_id
AND swc.wait_class = 'User I/O'
AND wcmh.end_time >= SYSDATE - INTERVAL '&1' MINUTE
ORDER BY sample_time
),
wait AS
(
SELECT TO_CHAR(wcmh.end_time, 'hh24:mi:ss') sample_time
, 'Wait' metric_name
, SUM(ROUND(wcmh.time_waited/wcmh.intsize_csec, 4)) active_sessions
FROM v$waitclassmetric_history wcmh
, v$system_wait_class swc
WHERE wcmh.wait_class_id = swc.wait_class_id
AND (swc.wait_class NOT IN ('Idle', 'User I/O'))
AND wcmh.end_time >= SYSDATE - INTERVAL '&1' MINUTE
GROUP BY TO_CHAR(wcmh.end_time, 'hh24:mi:ss')
ORDER BY sample_time
)
SELECT * FROM cpu
UNION ALL
SELECT * FROM user_io
UNION ALL
SELECT * FROM wait
;
SAMPLE_TIME METRIC_NAME ACTIVE_SESSIONS
----------- ----------- ---------------
08:17:54 CPU 1.6762
08:18:54 CPU 1.7645
08:19:54 CPU 1.6364
08:20:55 CPU 1.5903
set linesiz 300 pagesize 300
col sql for a50
col SAMPLE_TIME for a10
col EVENT for a30
col kill for a17
select
'''' || ash.SESSION_ID || ',' || ash.session_serial# ||',@'|| ash.inst_id||'''' KILL,
ash.con_id,
TO_CHAR(ash.SAMPLE_TIME,'HH24:MI') SAMPLE_TIME,
ash.sql_id,
TOP_LEVEL_SQL_ID,
SUBSTR(sa.sql_text,1,50) as SQL,
ash.EVENT,
ROUND(AVG(ash.TIME_WAITED),2) as AVG_TIME_WAIT,
COUNT(*) as INSTANCES
from
GV$ACTIVE_SESSION_HISTORY ash
left join v$sqlarea sa on sa.sql_id = ash.sql_id
where 1=1
and ash.sample_time>sysdate - interval '1' minute
-- AND ash.sample_time BETWEEN (SYSDATE-5/1440) AND SYSDATE
-- AND ash.sample_time BETWEEN '24-NOV-22 01.00PM' AND '24-NOV-22 01.30PM'
AND ash.SESSION_STATE = 'WAITING'
group by
'''' || ash.SESSION_ID || ',' || ash.session_serial# ||',@'|| ash.inst_id,
ash.con_id,
TO_CHAR(ash.SAMPLE_TIME,'HH24:MI'),
ash.sql_id,
TOP_LEVEL_SQL_ID,
SUBSTR(sa.sql_text,1,50),
ash.EVENT
order by
6 DESC;
set linesize 200 pagesize 200 select ash.inst_id, ash.sql_id, ash.session_id, ash.session_serial#, ash.user_id, ash.program, sum(decode(ash.session_state,'ON CPU',1,0)) "CPU", sum(decode(ash.session_state,'WAITING',1,0)) - sum(decode(ash.session_state,'WAITING', decode(en.wait_class,'User I/O',1, 0 ), 0)) "WAITING" , sum(decode(ash.session_state,'WAITING',decode(en.wait_class,'User I/O',1, 0 ), 0)) "IO" , sum(decode(session_state,'ON CPU',1,1)) "TOTAL" from gv$active_session_history ash, v$event_name en where en.event# = ash.event# and SQL_ID is not NULL and ash.user_id in (select USER_ID from dba_users where username not in ( 'SYS','SYSTEM','DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM','SYSDG','SYSBACKUP','GSMCATUSER','SYSKM' ,'OJVMSYS','DVSYS') ) -- and ash.session_type = 'BACKGROUND' -- and ash.session_type = 'FOREGROUND' -- and TO_CHAR(ash.sample_time) > '01-AUG-15 08.36.09.094 AM' and user_id!=0 --and ash.session_state = 'WAITING' group by ash.inst_id,sql_id,session_id,user_id,session_serial#,program order by sum(decode(session_state,'ON CPU',1,1)) desc /
=============================
set linesize 500
select
stat.sql_id sql_id ,
stat.con_id,
-- sql_id,
replace (cast (dbms_lob.substr (text.sql_text, 100) as varchar (100)), chr (10), '') sql_text,
--
executions executions,
--
round(elapsed_time / 1000000 , 3) seconds_elapsed,
round(cpu_time / 1000000 , 3) seconds_cpu_elapsed,
--
round(elapsed_time / executions / 1000000 , 3) seconds_elapsed_per_exec,
round(cpu_time / executions / 1000000 , 3) seconds_cpu_elapsed_per_exec,
round(iowait_time / executions / 1000000 , 3) seconds_iowait_ela_per_exec,
--
disk_reads disk_reads,
buffer_gets buffer_gets,
writes writes,
parses parses,
sorts sorts
from
( select --- {
stat.sql_id sql_id,stat.con_id ,
sum (stat.executions_delta ) executions,
sum (stat.elapsed_time_delta ) elapsed_time,
sum (stat.cpu_time_delta ) cpu_time,
sum (stat.iowait_delta ) iowait_time,
sum (stat.disk_reads_delta ) disk_reads,
sum (stat.buffer_gets_delta ) buffer_gets,
sum (stat.direct_writes_delta ) writes,
sum (stat.parse_calls_delta ) parses,
sum (stat.sorts_delta ) sorts
from
dba_hist_sqlstat stat where snap_id in (
------------- See script find_snap_ids.sql
select snap_id from dba_hist_ash_snapshot
where 1=1
and end_interval_time > sysdate - 3/24 and -- first snap
begin_interval_time < sysdate
-- last snap
-------------
)
group by
stat.sql_id,stat.con_id
) stat --- }
join dba_hist_sqltext text on stat.sql_id = text.sql_id
where
executions > 0
order by
seconds_elapsed desc;
====
-- without con_id
set linesize 700
col SQL_TEXT for a50 wrap
select
stat.sql_id sql_id ,
-- stat.con_id,
-- sql_id,
replace (cast (dbms_lob.substr (text.sql_text, 100) as varchar (100)), chr (10), '') sql_text,
--
executions executions,
--
round(elapsed_time / 1000000 , 3) seconds_elapsed,
round(cpu_time / 1000000 , 3) seconds_cpu_elapsed,
--
round(elapsed_time / executions / 1000000 , 3) seconds_elapsed_per_exec,
round(cpu_time / executions / 1000000 , 3) seconds_cpu_elapsed_per_exec,
round(iowait_time / executions / 1000000 , 3) seconds_iowait_ela_per_exec,
--
disk_reads disk_reads,
buffer_gets buffer_gets,
writes writes,
parses parses,
sorts sorts
from
( select --- {
stat.sql_id sql_id,
--stat.con_id ,
sum (stat.executions_delta ) executions,
sum (stat.elapsed_time_delta ) elapsed_time,
sum (stat.cpu_time_delta ) cpu_time,
sum (stat.iowait_delta ) iowait_time,
sum (stat.disk_reads_delta ) disk_reads,
sum (stat.buffer_gets_delta ) buffer_gets,
sum (stat.direct_writes_delta ) writes,
sum (stat.parse_calls_delta ) parses,
sum (stat.sorts_delta ) sorts
from
dba_hist_sqlstat stat where snap_id in (
------------- See script find_snap_ids.sql
select snap_id from dba_hist_ash_snapshot
where 1=1
and end_interval_time > sysdate - 3/24 and -- first snap
begin_interval_time < sysdate
-- last snap
-------------
)
group by
stat.sql_id
--,stat.con_id
) stat --- }
join dba_hist_sqltext text on stat.sql_id = text.sql_id
where
executions > 0
order by
seconds_elapsed desc;
====http://anuj-singh.blogspot.com/2021/05/sql-history.htmlgv$sqlarea to get the sql_text for a sql_id. SELECT sql_text FROM gv$sqlarea WHERE sql_id = '&sql_id'; -- Shared Pool alter session set "_rowsource_execution_statistics" = TRUE; define sql_id='8payvrbu7460q' col PLAN_TABLE_OUTPUT for a200 SELECT * FROM TABLE(dbms_xplan.display_cursor(sql_id => '&sql_id', cursor_child_no => 0, FORMAT => 'ADVANCED +ROWS +BYTES +COST +PARALLEL +PARTITION +IOSTATS +MEMSTATS +ALIAS +PEEKED_BINDS +OUTLINE +PREDICATE -PROJECTION +REMOTE +NOTE')); -- AWR set pagesize 80 define sql_id='8payvrbu7460q' select * from table(dbms_xplan.display_awr(sql_id=>'&sql_id', format=>'ALLSTATS LAST +cost +bytes +outline +PEEKED_BINDS +adaptive'));
Oracle Index rebuild script
Index validate
Fast Index Rebuild.
Execute on DBMS_SQL must be granted from SYS
validate structure command obtains a dml enqueue lock on the table that the index
SQL> select distinct INDEX_TYPE from dba_indexes ;
INDEX_TYPE
---------------------------
IOT - TOP
LOB
FUNCTION-BASED NORMAL
FUNCTION-BASED DOMAIN
BITMAP
NORMAL
CLUSTER
DOMAIN
Set serveroutput on size 100000
DECLARE
vOwner dba_indexes.owner%TYPE; /* Index Owner */
vIdxName dba_indexes.index_name%TYPE; /* Index Name */
vAnalyze VARCHAR2(100); /* String of Analyze Stmt */
vCursor NUMBER; /* DBMS_SQL cursor */
vNumRows INTEGER; /* DBMS_SQL return rows */
vHeight index_stats.height%TYPE; /* Height of index tree */
vLfRows index_stats.lf_rows%TYPE; /* Index Leaf Rows */
vDLfRows index_stats.del_lf_rows%TYPE; /* Deleted Leaf Rows */
vDLfPerc NUMBER; /* Del lf Percentage */
vMaxHeight NUMBER; /* Max tree height */
vMaxDel NUMBER; /* Max del lf percentage */
CURSOR cGetIdx IS SELECT owner,index_name FROM dba_indexes WHERE OWNER NOT LIKE 'SYS%' and INDEX_TYPE='NORMAL' ; ------???????????
BEGIN
/* Define maximums. This section can be customized. */
vMaxHeight := 3 ;
vMaxDel := 20 ;
/* For every index, validate structure */
OPEN cGetIdx;
LOOP
FETCH cGetIdx INTO vOwner,vIdxName;
EXIT WHEN cGetIdx%NOTFOUND;
/* Open DBMS_SQL cursor */
vCursor := DBMS_SQL.OPEN_CURSOR;
/* Set up dynamic string to validate structure */
vAnalyze := 'ANALYZE INDEX ' || vOwner || '.' || vIdxName || ' VALIDATE STRUCTURE';
DBMS_SQL.PARSE(vCursor,vAnalyze,DBMS_SQL.NATIVE);
vNumRows := DBMS_SQL.EXECUTE(vCursor);
/* Close DBMS_SQL cursor */
DBMS_SQL.CLOSE_CURSOR(vCursor);
/* Does index need rebuilding? */
/* If so, then generate command */
SELECT height,lf_rows,del_lf_rows INTO vHeight,vLfRows,vDLfRows FROM INDEX_STATS;
IF vDLfRows = 0 THEN /* handle case where div by zero */
vDLfPerc := 0;
ELSE
vDLfPerc := (vDLfRows / vLfRows) * 100;
END IF;
IF (vHeight > vMaxHeight) OR (vDLfPerc > vMaxDel) THEN
DBMS_OUTPUT.PUT_LINE('ALTER INDEX ' || vOwner || '.' || vIdxName || ' REBUILD;');
END IF;
END LOOP;
CLOSE cGetIdx;
END;
==============
or
drop table
create table anuj_index_stats as select * from index_stats 1=2;
delete from anuj_index_stats;
BEGIN
FOR x IN (SELECT index_name FROM user_indexes WHERE index_type = 'NORMAL') LOOP
EXECUTE IMMEDIATE 'analyze index ' || x.index_name || ' validate structure';
EXECUTE IMMEDIATE 'insert into aux_index_stats select * from index_stats';
COMMIT;
END LOOP;
END;
/
=
validate structure command obtains a dml enqueue lock on the table and correspond index
First rule : If the index has height greater than 4 , rebuild the index
Second rule : { (del_lf_rows/lf_rows)*100 as ratio } if this ratio above 20% this
means high number of deletes or updates have occurred to the index
column.
SELECT name,height,lf_rows,del_lf_rows,(del_lf_rows/lf_rows)*100 as ratio
FROM INDEX_STATS;
Fast Index Rebuild.
Execute on DBMS_SQL must be granted from SYS
validate structure command obtains a dml enqueue lock on the table that the index
SQL> select distinct INDEX_TYPE from dba_indexes ;
INDEX_TYPE
---------------------------
IOT - TOP
LOB
FUNCTION-BASED NORMAL
FUNCTION-BASED DOMAIN
BITMAP
NORMAL
CLUSTER
DOMAIN
Set serveroutput on size 100000
DECLARE
vOwner dba_indexes.owner%TYPE; /* Index Owner */
vIdxName dba_indexes.index_name%TYPE; /* Index Name */
vAnalyze VARCHAR2(100); /* String of Analyze Stmt */
vCursor NUMBER; /* DBMS_SQL cursor */
vNumRows INTEGER; /* DBMS_SQL return rows */
vHeight index_stats.height%TYPE; /* Height of index tree */
vLfRows index_stats.lf_rows%TYPE; /* Index Leaf Rows */
vDLfRows index_stats.del_lf_rows%TYPE; /* Deleted Leaf Rows */
vDLfPerc NUMBER; /* Del lf Percentage */
vMaxHeight NUMBER; /* Max tree height */
vMaxDel NUMBER; /* Max del lf percentage */
CURSOR cGetIdx IS SELECT owner,index_name FROM dba_indexes WHERE OWNER NOT LIKE 'SYS%' and INDEX_TYPE='NORMAL' ; ------???????????
BEGIN
/* Define maximums. This section can be customized. */
vMaxHeight := 3 ;
vMaxDel := 20 ;
/* For every index, validate structure */
OPEN cGetIdx;
LOOP
FETCH cGetIdx INTO vOwner,vIdxName;
EXIT WHEN cGetIdx%NOTFOUND;
/* Open DBMS_SQL cursor */
vCursor := DBMS_SQL.OPEN_CURSOR;
/* Set up dynamic string to validate structure */
vAnalyze := 'ANALYZE INDEX ' || vOwner || '.' || vIdxName || ' VALIDATE STRUCTURE';
DBMS_SQL.PARSE(vCursor,vAnalyze,DBMS_SQL.NATIVE);
vNumRows := DBMS_SQL.EXECUTE(vCursor);
/* Close DBMS_SQL cursor */
DBMS_SQL.CLOSE_CURSOR(vCursor);
/* Does index need rebuilding? */
/* If so, then generate command */
SELECT height,lf_rows,del_lf_rows INTO vHeight,vLfRows,vDLfRows FROM INDEX_STATS;
IF vDLfRows = 0 THEN /* handle case where div by zero */
vDLfPerc := 0;
ELSE
vDLfPerc := (vDLfRows / vLfRows) * 100;
END IF;
IF (vHeight > vMaxHeight) OR (vDLfPerc > vMaxDel) THEN
DBMS_OUTPUT.PUT_LINE('ALTER INDEX ' || vOwner || '.' || vIdxName || ' REBUILD;');
END IF;
END LOOP;
CLOSE cGetIdx;
END;
==============
or
drop table
create table anuj_index_stats as select * from index_stats 1=2;
delete from anuj_index_stats;
BEGIN
FOR x IN (SELECT index_name FROM user_indexes WHERE index_type = 'NORMAL') LOOP
EXECUTE IMMEDIATE 'analyze index ' || x.index_name || ' validate structure';
EXECUTE IMMEDIATE 'insert into aux_index_stats select * from index_stats';
COMMIT;
END LOOP;
END;
/
=
validate structure command obtains a dml enqueue lock on the table and correspond index
First rule : If the index has height greater than 4 , rebuild the index
Second rule : { (del_lf_rows/lf_rows)*100 as ratio } if this ratio above 20% this
means high number of deletes or updates have occurred to the index
column.
SELECT name,height,lf_rows,del_lf_rows,(del_lf_rows/lf_rows)*100 as ratio
FROM INDEX_STATS;
Oracle select can generate undo
Oracle select generates undo in "for update"
SQL> select used_ublk from v$transaction;
no rows selected
SQL> create table anuj as select * from dba_objects;
Table created.
SQL> select used_ublk from v$transaction;
no rows selected
SQL> set timing on
SQL> begin
for i in ( select * from anuj for update )
loop
null;
end loop;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:03.98
SQL> select used_ublk from v$transaction;
USED_UBLK
----------
840
Elapsed: 00:00:00.03
because of "for update" clause, Oracle modify data block to place lock info . any block change generate undo .
SQL> select used_ublk from v$transaction;
no rows selected
SQL> create table anuj as select * from dba_objects;
Table created.
SQL> select used_ublk from v$transaction;
no rows selected
SQL> set timing on
SQL> begin
for i in ( select * from anuj for update )
loop
null;
end loop;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:03.98
SQL> select used_ublk from v$transaction;
USED_UBLK
----------
840
Elapsed: 00:00:00.03
because of "for update" clause, Oracle modify data block to place lock info . any block change generate undo .
Subscribe to:
Posts (Atom)
