Index monitoring - v$object_usage returns no rows

Cause: v$object_usage view is specific to the currently connected user.

Solution:  Either connect as the object owner, or create the following view as sys

create or replace view V$ALL_OBJECT_USAGE
  (OWNER,
   INDEX_NAME,
   TABLE_NAME,
   MONITORING,
   USED,
   START_MONITORING,
   END_MONITORING
  )
  as
  select u.name,
         io.name,
         t.name,
         decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),
         decode(bitand(ou.flags, 1), 0, 'NO', 'YES'),
         ou.start_monitoring, 
         ou.end_monitoring
    from sys.user$ u,
         sys.obj$ io, 
         sys.obj$ t, 
         sys.ind$ i, 
         sys.object_usage ou
   where i.obj# = ou.obj#
     and io.obj# = ou.obj#
     and t.obj# = i.bo#
     and u.user# = io.owner#
/

Tablespace usage over time

If you have OEM, this repository query may be very helpful:

SELECT KEY_VALUE Tablespace_Name,
       ROLLUP_TIMESTAMP Sample_date,
       METRIC_COLUMN metric,
       AVERAGE
  FROM sysman.MGMT$METRIC_DAILY
 WHERE metric_name='tbspAllocation'
   and TARGET_NAME='<dbname>' 
   and KEY_VALUE = '<tablespacename>'
 ORDER BY 1,2,3;

How to Trace SQL in the Current Session

Trace SQL in the current session:

alter session set timed_statistics=true;

alter session set max_dump_file_size=unlimited;

alter session set tracefile_identifier='20Jul2012_special_trace_1';​

alter session set events '10046 TRACE NAME CONTEXT FOREVER,level 12';

..Run some things..

select 'close cursor' from dual; --to dump the rowsource information
alter session set events '10046 trace name context off';

Trace file will be in diag/admin/db_name/instance_name/trace

How FULL are the BLOCKS in my TABLE?

Table Block Space Usage:

set serveroutput on size 100000
declare
 v_unformatted_blocks number;
 v_unformatted_bytes number;
 v_fs1_blocks number;
 v_fs1_bytes number;
 v_fs2_blocks number;
 v_fs2_bytes number;
 v_fs3_blocks number;
 v_fs3_bytes number;
 v_fs4_blocks number;
 v_fs4_bytes number;
 v_full_blocks number;
 v_full_bytes number;
 begin
  dbms_space.space_usage (
   '&TABLEOWNER',        --object owner
   '&TABLENAME',         --object name
   'TABLE',              --object type TABLE, INDEX, or "TABLE PARTITION" 
   v_unformatted_blocks,
   v_unformatted_bytes,
   v_fs1_blocks,
   v_fs1_bytes,
   v_fs2_blocks,
   v_fs2_bytes,
   v_fs3_blocks,
   v_fs3_bytes,
   v_fs4_blocks,
   v_fs4_bytes,
   v_full_blocks,
   v_full_bytes
--'&PARTITIONNAME',
);
  dbms_output.put_line('Unformatted Blocks = '||v_unformatted_blocks);
  dbms_output.put_line('FS1 Blocks   = '||v_fs1_blocks);
  dbms_output.put_line('FS2 Blocks   = '||v_fs2_blocks);
  dbms_output.put_line('FS3 Blocks   = '||v_fs3_blocks);
  dbms_output.put_line('FS4 Blocks   = '||v_fs4_blocks);
  dbms_output.put_line('Full Blocks  = '||v_full_blocks);
 end;
/

Sample Output:

Unformatted Blocks = 16
FS1 Blocks   = 42  <--- 0-25% full
FS2 Blocks   = 31  <-- 25-50% full
FS3 Blocks   = 35  <-- 50-75% full
FS4 Blocks   = 4651 <- 75-99% full
Full Blocks  = 99448

 

Shrinking options:

-- Enable row movement.
ALTER TABLE scott.emp ENABLE ROW MOVEMENT;

-- Recover space and amend the high water mark (HWM).
ALTER TABLE scott.emp SHRINK SPACE;

-- Recover space, but don't amend the high water mark (HWM).
ALTER TABLE scott.emp SHRINK SPACE COMPACT;

-- Recover space for the object and all dependant objects.
ALTER TABLE scott.emp SHRINK SPACE CASCADE;

Manually Run Stats Gathering Job

Run Stats Gathering Job:
exec DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC()

Notes:
The GATHER_DATABASE_STATS_JOB_PROC procedure collects statistics on database objects when the object has no previously gathered statistics or the existing statistics are stale because the underlying object has been modified significantly (more than 10% of the rows).

The DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC is an internal procedure, but its operates in a very similar fashion to the DBMS_STATS.GATHER_DATABASE_STATS procedure using the GATHER AUTO option. 

The primary difference is that the DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC procedure prioritizes the database objects that require statistics, so that those objects which most need updated statistics are processed first. 

This ensures that the most-needed statistics are gathered before the maintenance window closes.

Database Uptime

Database Uptime (11g):

col d_name heading 'Database' format a8
col v_logon_time heading 'Startup'
col dh_uptime heading 'Uptime' format a30

select upper(sys_context('USERENV','DB_NAME')) d_name,
       to_char(logon_time,'DD-MON-YYYY hh24:mi:ss') v_logon_time,
       to_char(trunc(sysdate-logon_time,0))||' days, '||trunc(((sysdate-logon_time)-floor(sysdate-logon_time))*24)||' Hours' dh_uptime
  from sys.v_$session
 where sid=1 /* pmon session */
/

Sample Output:

Database Startup              Uptime
-------- -------------------- ------------------------------
MYDB     05-JUL-2012 21:42:24 32 days, 13 Hours

Top Waits by Object

SQL to Identify Objects Creating Cluster-wide bottlenecks (in the past 24 hours)
set linesize 140
set pagesize 50
col sample_time format a26
col event format a30
col object format a45
--col num_sql heading '# SQL' format 9,999
select
       ash.sql_id,
--       count(distinct ash.sql_id) Num_SQL,
       ash.event,
       ash.current_obj#,
       o.object_type,
       o.owner||'.'||o.object_name||'.'||o.subobject_name object,
       count(*)
  from gv$active_session_history ash,
       all_objects o
 where ash.current_obj# = o.object_id
   and ash.current_obj# != -1
   and ash.event is not null
   and ash.sample_time between  sysdate - 1 and sysdate
--   and ash.sample_time between  sysdate - 4 and sysdate - 3
--   and to_date ('24-SEP-2010 14:28:00','DD-MON-YYYY HH24:MI:SS') and to_date ('24-SEP-2010 14:29:59','DD-MON-YYYY HH24:MI:SS')
 group by
       ash.sql_id,
       ash.event,
       ash.current_obj#,
       o.object_type,
       o.owner||'.'||o.object_name||'.'||o.subobject_name
having count(*) > 20
 order by count(*) desc
/
exit
/

Track RMAN Job Process via gv$session_longops

Nice way to track RMAN Channel worker progress

Also useful: watch -n 10 sqlplus -s usr/pwd [this script].sql 

set linesize 120
column pct_done format '999.99'
column opname format a35
column time_left format a15
column started format a15
select
  sid,
  opname,
  to_char(start_time,'DD-MON HH24:MI') started,
  round(totalwork-sofar) blocks_left,
 (sofar/totalwork) * 100 pct_done,
  to_char(to_date(time_remaining,'sssss'),'hh24:mi:ss') time_left
from
   gv$session_longops
where
   totalwork > sofar
AND
   opname NOT LIKE '%aggregate%'
AND
   opname like 'RMAN%'
order by 2;
exit;

Historical Blocking Locks

To Investigate Recent Blocking Locks (after the dust settles)

set pagesize 50
set linesize 120
col sql_id format a15
col inst_id format '9'
col sql_text format a50
col module format a10
col blocker_ses format '999999'
col blocker_ser format '999999'

------------------------------------------------------------------
--IN CHRONOLOGICAL ORDER (which is probably what you want anyways)
------------------------------------------------------------------
 SELECT distinct
        a.sql_id ,
        to_char(a.sql_exec_start,'DD-Mon HH24:MI') sql_start,
        a.inst_id,
        a.blocking_session blocker_ses,
        a.blocking_session_serial# blocker_ser,
        a.user_id,
        s.sql_text,
        a.module
 FROM  GV$ACTIVE_SESSION_HISTORY a,
       gv$sql s
 where a.sql_id=s.sql_id
   and blocking_session is not null
   and a.user_id <> 0 --  exclude SYS user
   and a.sample_time > sysdate - 1
 order by sql_start

Query ASM Disks and Diskgroups

List all ASM devices:
/etc/init.d/oracleasm querydisk -d `/etc/init.d/oracleasm listdisks -d` |
 cut -f2,10,11 -d" " | perl -pe 's/"(.*)".*\[(.*), *(.*)\]/$1 $2 $3/g;'


List all AVAILABLE ASM disks:
col path format a20
col header_status format a13
col os_mb format 999,999,999 heading 'Size (MB)'
SELECT inst_id, 
       path, 
       header_status, 
       os_mb 
  FROM GV$ASM_DISK 
 WHERE header_status in ('FORMER','PROVISIONED')
 ORDER BY path,
          inst_id;

Sample Output:
INST_ID  PATH                 HEADER_STATUS    Size (MB)
-------- -------------------- ------------- ------------
       1 ORCL:DISK25          FORMER             524,294
       2 ORCL:DISK25          FORMER             524,294
       1 ORCL:DISK26          FORMER             524,294
       2 ORCL:DISK26          FORMER             524,294
       1 ORCL:DISK30          FORMER             524,294
       2 ORCL:DISK30          FORMER             524,294
       1 ORCL:DISK33          PROVISIONED        524,294
       2 ORCL:DISK33          PROVISIONED        524,294


List All ASM DISKGROUPS:
set pagesize 60
set linesize 132
column aa format 99999 heading "DiskGroup"
column ab format a15 heading "DiskGroup"
column ac format a20 heading "Disk"
column ad format a15 heading "DiskGroup State"
column ae format a15 heading "Disk State"
break on ab skip 1
select substr(to_char(a.group_number),1,5) aa, substr(a.name,1,15) ab, substr(b.name,1,20) ac , b.total_mb, b.free_mb, a.state ad, b.state ae from
v$asm_diskgroup a, v$asm_disk b
where a.group_number = b.group_number
--and a.group_number = 4
order by 2,3
/

Sample Output:
DiskG DiskGroup       Disk              TOTAL_MB    FREE_MB DiskGroup State Disk State
----- --------------- --------------- ---------- ---------- --------------- ---------------
1     DATA            DATA1               517893     183780 MOUNTED         NORMAL
1                     DATA2               517893     183783 MOUNTED         NORMAL
1                     DATA3               517893     183781 MOUNTED         NORMAL
1                     DATA4               517893     183781 MOUNTED         NORMAL
1                     DATA5               517893     183780 MOUNTED         NORMAL
2     FRA             FRA1                517893     426005 MOUNTED         NORMAL
3     VOTING          VOTING                8631       8235 MOUNTED         NORMAL


Rebalance Operations:
11g
select inst_id, 
       operation, 
       state, 
       power, 
       sofar, 
       est_work, 
       est_rate, 
       est_minutes 
  from gv$asm_operation 
 order by inst_id, state
/

12c
select inst_id, 
       pass,
       state, 
       power, 
       sofar, 
       est_work, 
       est_rate, 
       est_minutes 
  from gv$asm_operation 
 order by inst_id, state
/

12c added a "COMPACT" pass to improve disk seek performance.

Sample Output:
   INST_ID OPERA STAT      POWER      SOFAR   EST_WORK   EST_RATE EST_MINUTES
---------- ----- ---- ---------- ---------- ---------- ---------- -----------
         1 REBAL RUN           5     314121     314121       1029           0
         1 REBAL WAIT          5
         2 REBAL RUN           5       9724     188000       1289         138
         2 REBAL WAIT          5

Query Disk Compatibility:
col COMPATIBILITY form a10
col DATABASE_COMPATIBILITY form a10
col NAME form a20
select group_number, name, compatibility, database_compatibility from v$asm_diskgroup;

Additional reference:
How To Gather & Backup ASM/ACFS Metadata In A Formatted Manner version 10.1, 10.2, 11.1, 11.2 and 12.1? (Doc ID 470211.1)

ASM Diskgroup Space Used / Free

SQL:
set linesize 140
col group_number heading 'Diskgroup|Number' format 999
col diskgroup heading 'Name' format a20
col total_mb heading 'Allocated (MB)' format 999,999,999
col free_mb heading 'Available (MB)' format 999,999,999
col tot_used heading 'Used (MB)' format 999,999,999
col pct_used heading '% Used' format 999
col pct_free heading '% Free' format 999
select group_number,
       name diskgroup,
       total_mb,
       free_mb,
       total_mb-free_mb tot_used,
       pct_used,
       pct_free
  from (select group_number,name,total_mb,free_mb,
             round(((total_mb-nvl(free_mb,0))/decode(total_mb,0,1,total_mb))*100) pct_used,
             round((free_mb/total_mb)*100) pct_free
      from v$asm_diskgroup
      where total_mb >0
      order by pct_free
     )
/

SAMPLE OUTPUT:
Diskgroup
   Number Name            Allocated (MB) Available (MB)   Used (MB) % Used % Free
--------- --------------- -------------- -------------- ----------- ------ ------
        2 DATA2                5,767,234      1,860,008   3,907,226     68     32
        1 DATA1                5,767,234      1,996,305   3,770,929     65     35

Objects in Data Buffers

Objects in Data Buffers:

set pages 50
set linesize 110

spool blocks.lst

ttitle 'Contents of Data Buffers'

drop table t1;

create table t1 as
select
   o.object_name    object_name,
--   o.subobject_name subobject_name,
   o.object_type    object_type,
   count(1)         num_blocks
from
   dba_objects  o,
   v$bh         bh
where
   o.object_id  = bh.objd
and
   o.owner not in ('SYS','SYSTEM')
group by
   o.object_name,
--   o.subobject_name,
   o.object_type
order by
   count(1) desc
/

column c1 heading "Object|Name"                 format a30
--column c1a heading "Partition|Name"             format a15
column c2 heading "Object|Type"                 format a16
column c3 heading "Number of|Blocks"            format 999,999,999,999
column c3a heading "Size (MB)|32k blocks"       format 999,999,999
column c4 heading "Percentage|of object|data blocks|in Buffer" format 999

select
   object_name       c1,
--   subobject_name    c1a,
   object_type       c2,
   num_blocks        c3,
   (num_blocks*32)/1024 c3a,
   (num_blocks/decode(sum(blocks), 0, .001, sum(blocks)))*100 c4
from
   t1,
   dba_segments s
where
   s.segment_name = t1.object_name
and
   num_blocks > 10
group by
   object_name,
--   subobject_name,
   object_type,
   num_blocks
order by
   num_blocks desc
/

exit
/

 
Sample Output:

Mon Aug 06                                                                       page    1
                                 Contents of Data Buffers

                                                                               Percentage
                                                                                of object
Object                         Object                  Number of    Size (MB) data blocks
Name                           Type                       Blocks   32k blocks   in Buffer
------------------------------ ---------------- ---------------- ------------ -----------
...

38 rows selected.

SGA Usage Report

SGA Usage Report:
break on report
compute sum of mb on report
compute sum of inuse on report
set pagesize 50
col mb format 999,999
col inuse format 999,999
select name,
       round(sum(mb),1) mb,
       round(sum(inuse),1) inuse
  from (select case when name = 'buffer_cache'
                    then 'db_cache_size'
                    when name = 'log_buffer'
                    then 'log_buffer'
                    else pool
                end name,
                bytes/1024/1024 mb,
                case when name <> 'free memory'
                     then bytes/1024/1024
                end inuse
           from v$sgastat
       )
 group by name
 order by mb desc
/
exit
/

Sample Output:
NAME                MB    INUSE

------------- -------- --------
db_cache_size   85,504   85,504
shared pool      6,144    3,879
streams pool       512      256
large pool         256        1
java pool          256
log_buffer          98       98
                     2        2
              -------- --------
sum             92,773   89,741

Instance Memory Usage

Instance Memory Usage:
set linesize 100
set pagesize 50
col component format a35
col size_mb format 999,999
select component,
       current_size/1024/1024 size_mb
  from v$memory_dynamic_components
 order by current_size desc
/
!free
exit
/

Sample Output:
COMPONENT                            SIZE_MB

----------------------------------- --------
SGA Target                            92,160
DEFAULT buffer cache                  85,504
PGA Target                            30,720
shared pool                            5,376
streams pool                             256
java pool                                256
large pool                               256
RECYCLE buffer cache                       0
DEFAULT 2K buffer cache                    0
DEFAULT 4K buffer cache                    0
DEFAULT 8K buffer cache                    0
KEEP buffer cache                          0
DEFAULT 32K buffer cache                   0
Shared IO Pool                             0
ASM Buffer Cache                           0
DEFAULT 16K buffer cache                   0

16 rows selected.

SQL Trick - Always Return a Row

This SQL will always return a pre-defined row if none exist.  "no rows selected" was getting really old.

prompt
prompt ###############
prompt INVALID OBJECTS
prompt ###############
col owner       heading 'Owner'  format a15
col object_type heading 'Type'   format a20
col object_name heading 'Object' format A30
with t as (
select owner,
       object_type,
       object_name
from   dba_objects
where  status = 'INVALID'
order by owner,
         object_type,
         object_name)
select owner, object_type, object_name from (
   select rownum rn, owner, object_type, object_name
     from t
   union
   select 0, '~~All VALID~~', '~~All VALID~~', '~~All VALID~~' from dual
   order by 1 desc
   )
  where (rn > 0 or (rownum = 1 and rn = 0))
/

Sample Output when no rows are returned:

Owner           Type                 Object
--------------- -------------------- ------------------------------
~~All VALID~~   ~~All VALID~~        ~~All VALID~~

Redo Generated by Instance / Day

SQL:
prompt
prompt ##############
prompt REDO GENERATED
prompt ##############
col thrd      heading 'Instance'  format '9'
col trans_day heading 'Day'       format a6
col logs_mb   heading 'Redo (MB)' format '9,999,999'
break on thrd skip 1
compute sum label 'Week Total' of logs_mb on thrd
select thrd,
       to_char(transaction_day,'DY-DD') trans_day,
       sum(log_size) logs_mb
  from ( select distinct
                thread# thrd,
                sequence# sequence,
                trunc(first_time) transaction_day,
                round((blocks*block_size)/1048576) log_size
           from v$archived_log
          where first_time > sysdate - 7)
 group by thrd,
          transaction_day
 order by thrd​
/

Sample Output:
##############

REDO GENERATED
##############

Instance Day     Redo (MB)
-------- ------ ----------
       1 WED-08     38,913
         THU-09     50,678
         FRI-10     68,406
         SAT-11     59,472
         SUN-12     73,550
         MON-13     48,961
         TUE-14     81,264
         WED-15     41,908
********        ----------
Week Tot           463,152

       2 WED-08        130
         THU-09      1,206
         FRI-10      1,569
         SAT-11        384
         SUN-12        429
         MON-13        295
         TUE-14        457
         WED-15        264

Undo Status

Undo Status:
prompt
prompt #########
prompt UNDO Size
prompt #########
set head off
select to_char(sum(a.bytes)/1024/1024,'999,999')||' mb' undo_size
  from v$datafile a,
       v$tablespace b,
       dba_tablespaces c
 where c.contents = 'UNDO'
   and c.status = 'ONLINE'
   and b.name = c.tablespace_name
   and a.ts# = b.ts#
/
set head on

column block_size heading 'Block Size' new_value block_size
select to_number(value) block_size
  from v$parameter
 where name = 'db_block_size'
/

set verify off
prompt
prompt ################
prompt UNDO UTILIZATION
prompt ################
col tablespace_name heading 'Undo Tablespace' format a15
col status heading 'Status' format a15
col mb heading 'Size MB' format 999,999
select tablespace_name,
       status,
       round(sum(blocks) * &block_size/1024/1024,2) MB
  from dba_undo_extents
  group by tablespace_name,
           status
  order by tablespace_name,
           status
/

col undo_retention heading 'undo_retention' format a30
select to_char(value,'99,999')||' seconds or '||to_char(value/60,'99')||' minutes' undo_retention from v$parameter where name = 'undo_retention'
/

col undo_tablespace heading 'undo_tablespace' format a30
select value undo_tablespace from v$parameter where name = 'undo_tablespace'
/

col undo_management heading 'undo_management' format a30
select value undo_management from v$parameter where name = 'undo_management'
/

 
Notes:
In Undo Segments there are three types of extents,
Unexpired – Undo data whose age is less than the undo retention time.
Expired – Undo data whose age is greater than the undo retention time.
Active – Undo data that is part of an active transaction.

The sequence for using UNDO extents:

1. A new extent will be allocated from undo when the requirement arises. As undo is written to an undo segment, if the undo reaches the end of the current extent and the next extent contains expired undo then the new undo (generated by the current transaction) wraps into that expired extent, in preference to grabbing a free extent from the undo tablespace free extent pool.

2. If this fails because there are no available free extents and we cannot autoextend the datafile, then Oracle attempts to steal an expired extent from another undo segment.

3. If that fails then it tries to reuse an unexpired extent from the current undo segment.

4. If that fails, then it tries to steal an unexpired extent from another undo segment.

5. If all else fails, an Out-Of-Space error will be reported.

Monitor Long Running Rollback

spool SMON_RollBack_Progress.lst
Prompt
Prompt Script will run for 10 minutes and checks rollback status from x$ktuxe every 2 mins
Prompt -----------------------------------------------------------------------------------
Prompt
set lines 120
col useg format a30
alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';

select sysdate,b.name useg, b.inst# instid, b.status$ status, a.ktuxeusn
xid_usn, a.ktuxeslt xid_slot, a.ktuxesqn xid_seq, a.ktuxesiz undoblocks
from x$ktuxe a, undo$ b
where a.ktuxesta = 'ACTIVE' and a.ktuxecfl like '%DEAD%'
and a.ktuxeusn = b.us#;

Prompt
Prompt ------------------------------------------------------------------------------------

Prompt sleeping for 2 mins ....
exec dbms_lock.sleep(120);

select sysdate,b.name useg, b.inst# instid, b.status$ status, a.ktuxeusn
xid_usn, a.ktuxeslt xid_slot, a.ktuxesqn xid_seq, a.ktuxesiz undoblocks
from x$ktuxe a, undo$ b
where a.ktuxesta = 'ACTIVE' and a.ktuxecfl like '%DEAD%'
and a.ktuxeusn = b.us#;

Prompt
Prompt ------------------------------------------------------------------------------------

Prompt sleeping for 2 mins ....
exec dbms_lock.sleep(120);

select sysdate,b.name useg, b.inst# instid, b.status$ status, a.ktuxeusn
xid_usn, a.ktuxeslt xid_slot, a.ktuxesqn xid_seq, a.ktuxesiz undoblocks
from x$ktuxe a, undo$ b
where a.ktuxesta = 'ACTIVE' and a.ktuxecfl like '%DEAD%'
and a.ktuxeusn = b.us#;

Prompt
Prompt ------------------------------------------------------------------------------------

Prompt sleeping for 2 mins ....
exec dbms_lock.sleep(120);

select sysdate,b.name useg, b.inst# instid, b.status$ status, a.ktuxeusn
xid_usn, a.ktuxeslt xid_slot, a.ktuxesqn xid_seq, a.ktuxesiz undoblocks
from x$ktuxe a, undo$ b
where a.ktuxesta = 'ACTIVE' and a.ktuxecfl like '%DEAD%'
and a.ktuxeusn = b.us#;

Prompt
Prompt ------------------------------------------------------------------------------------

Prompt sleeping for 2 mins ....
exec dbms_lock.sleep(120);

select sysdate,b.name useg, b.inst# instid, b.status$ status, a.ktuxeusn
xid_usn, a.ktuxeslt xid_slot, a.ktuxesqn xid_seq, a.ktuxesiz undoblocks
from x$ktuxe a, undo$ b
where a.ktuxesta = 'ACTIVE' and a.ktuxecfl like '%DEAD%'
and a.ktuxeusn = b.us#;

Prompt ** END OF SCRIPT **
spool off