Which query/process is using most of the UNDO tablespace?

Some very nice scripts found at:

http://www.dbaref.com/home/dba-routine-tasks/findingwhatsconsumingthemostundo


SQL> select s.sql_text from v$sql s, v$undostat u
where u.maxqueryid=s.sql_id;

You can also use following SQL to find out most undo used by a session for a currently executing transaction.

SQL> select s.sid,s.username,t.used_urec,t.used_ublk
from v$session s, v$transaction t
where s.saddr = t.ses_addr
order by t.used_ublk desc;

To find out which session is currently using the most UNDO,

SQL>select s.sid, t.name, s.value
from v$sesstat s, v$statname t
where s.statistic#=t.statistic#
and t.name='undo change vector size'

and s.value > 0
order by s.value;

SQL>select sql.sql_text, t.used_urec records, t.used_ublk blocks,
(t.used_ublk*8192/1024) kb from v$transaction t,
v$session s, v$sql sql
where t.addr=s.taddr
and s.sql_id = sql.sql_id
and s.username ='&USERNAME';

Which objects are loaded in the buffer cache?

A very nice article:

http://www.dba-oracle.com/art_builder_buffer.htm

The script to use is:

/******************************************************************
--   Contents of Data Buffers
******************************************************************/

set pages 999
set lines 92

ttitle 'Contents of Data Buffers'

drop table t1;

create table t1 as
select
   o.owner          owner,
   o.object_name    object_name,
   o.subobject_name subobject_name,
   o.object_type    object_type,
   count(distinct file# || block#)         num_blocks
from
   dba_objects  o,
   v$bh         bh
where
   o.data_object_id  = bh.objd
and
   o.owner not in ('SYS','SYSTEM')
and
   bh.status != 'free'
group by
   o.owner,
   o.object_name,
   o.subobject_name,
   o.object_type
order by
   count(distinct file# || block#) desc
;

column c0 heading "Owner"                                    format a12
column c1 heading "Object|Name"                              format a30
column c2 heading "Object|Type"                              format a8
column c3 heading "Number of|Blocks in|Buffer|Cache"         format 99,999,999
column c4 heading "Percentage|of object|blocks in|Buffer"    format 999
column c5 heading "Buffer|Pool"                              format a7
column c6 heading "Block|Size"                               format 99,999


select * from
(
select
   t1.owner                                          c0,
   object_name                                       c1,
   case when object_type = 'TABLE PARTITION' then 'TAB PART'
        when object_type = 'INDEX PARTITION' then 'IDX PART'
        else object_type end c2,
   sum(num_blocks)                                     c3,
   buffer_pool                                       c5
from
   t1,
   dba_segments s
where
   s.segment_name = t1.object_name
and
   s.owner = t1.owner
and
   s.segment_type = t1.object_type
and
   nvl(s.partition_name,'-') = nvl(t1.subobject_name,'-')
group by
   t1.owner,
   object_name,
   object_type,
   buffer_pool
having
   sum(num_blocks) > 10
order by
   sum(num_blocks) desc
)
where rownum < 20
;

Oracle: parallel query historical data

We know the SQL_ID of the query, at which time it ran and we need to know how many parallel processes were dedicated to our query?

The query below will do it:


select sql_id, px_servers_execs_total, px_servers_execs_delta
from dba_hist_sqlstat  
 where sql_id = '85kha1rsqy8wc'
 and snap_id between 81399 and 81400
   ;

SQL_ID        PX_SERVERS_EXECS_TOTAL PX_SERVERS_EXECS_DELTA
------------- ---------------------- ----------------------
85kha1rsqy8wc                    863                      0
85kha1rsqy8wc                    895                     32