Prev: Oracle Error: Cusor invalidated due to Parallel Execution
Next: Oracle export error
From: Mladen Gogala on 3 Mar 2006 22:42
On Fri, 03 Mar 2006 06:53:27 -0800, cpiodumpv wrote:

> Excellent response if there is undo I couldnt' wrap my mind around what
> the undo was.
>
> I agree. In the future I should not quote experts but prove these
> things to myself.
>
> Regards

Good way to measure it is to look into V$TRANSACTION and observe the
USED_UBLK column. This column reports the number of UNDO blocks used
by the particular transaction. You can join V$TRANSACTION to V$SESSION
which has TADDR column to get even more information. My favorite
join goes something like this:

SELECT sess.username,
sess.sid,
sess.serial# serial,
p.spid "System PID",
sess.PROGRAM,
sess.osuser,
sess.machine,
t.used_ublk "Undo blocks",
t.status "Trans. Status",
to_char(logon_time, ' MM/DD/YYYY HH24:MI') "Logged In"
FROM v$session sess,
v$transaction t,
v$process p
WHERE sess.saddr = t.ses_addr
AND sess.paddr = p.addr
ORDER BY t.used_ublk DESC

This was formatted by SQL*Developer (formerly Raptor)
--
http://www.mgogala.com

From: cpiodumpv on 4 Mar 2006 19:49
You read my mind..... My next question was where do I find the amount
of undo generated by a transaction. This query is very helpful thank
you.

Allow me to check my understanding thus far. There is undo generated
to protect the data dictionary i.e moving the high water mark or
altering the data structure of an index. This undo in turn generates
it's own redo; thus, the only reason there is redo generated is to
protect the undo. Correct?

In the below scenario why does the "create table as" statement not
generate undo?

Thanks in advance.

chotaboy(a)test>drop table t;

Table dropped.

chotaboy(a)test>create table t nologging as select * from all_objects;

Table created.

chotaboy(a)test>host cat get_undo.sql
column username format a10

select sess.username,
t.used_ublk "Undo blocks",
t.status "Trans. Status"
FROM v$session sess,
v$transaction t,
v$process p
WHERE sess.saddr = t.ses_addr
AND sess.paddr = p.addr
ORDER BY t.used_ublk DESC
/

chotaboy(a)test>@get_undo

no rows selected

chotaboy(a)test>alter table t nologging;

Table altered.

chotaboy(a)test>truncate table t;

Table truncated.

chotaboy(a)test>insert into t select * from all_objects;

11632 rows created.

chotaboy(a)test>@get_undo

USERNAME Undo blocks Trans. Status
---------- ----------- ------------------------------------------------
CHOTABOY 6 ACTIVE

chotaboy(a)test>

From: Mladen Gogala on 4 Mar 2006 20:24
On Sat, 04 Mar 2006 16:49:42 -0800, cpiodumpv wrote:

> In the below scenario why does the "create table as" statement not
> generate undo?

It does, but you cannot see it, as it is no longer active
when it finishes. DDL statements do implicit commit, so
you can't ever see it as active after it's finished. The
problem with my query is that it shows only the current transactions,
ones pointed to by sessions in V$SESSION table. If the transaction is
over, and the session that issued it now points to another transaction,
you will see nothing on the output of the query. Also, you cannot see any
information about finished transactions as you don't have V$SESSION
history. The only way to observe the finished transactions is auditing and
it will not tell you how many undo blocks did the transaction consume. As
of 10g you can catch SQL and bind variable values, even without DBMS_FGA,
but you can't see how many undo blocks did it consume at any given point
in time.

My query is intended for monitoring transaction rollback and undo
consumption. If you want to study the execution path of DDL statements,
enable the event 10046, level 12 and execute your DDL with tracing
enabled.

--
http://www.mgogala.com

From: cpiodumpv on 5 Mar 2006 11:43
good stuff thanks.

could you point me to information on SQL*Developer (formerly Raptor)

Regards

From: Mladen Gogala on 6 Mar 2006 19:09
On Sun, 05 Mar 2006 08:43:21 -0800, cpiodumpv wrote:

> good stuff thanks.
>
> could you point me to information on SQL*Developer (formerly Raptor)
>
> Regards

http://technet.oracle.com

--
http://www.mgogala.com

First  |  Prev  | 
Pages: 1 2 3
Prev: Oracle Error: Cusor invalidated due to Parallel Execution
Next: Oracle export error