Wednesday, August 10, 2016

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#
/


Friday, July 15, 2016


MEMORY USED BY SQL STATEMENT

select machine,to_char(SQL_EXEC_START,'DD-MON-YY HH24:MI:SS')            runtime,
       pga_allocated 
  from dba_hist_active_sess_history 
 where sql_id = '&sql_id';


EXAMPLE OUTPUT:

MACHINE                   RUNTIME                 PGA_ALLOCATED

------------------------- -------------------- ----------------
MYPCNAME                  13-JUL-16 16:18:40        807,153,664
MYPCNAME                  13-JUL-16 16:18:40        329,003,008
MYPCNAME                  13-JUL-16 16:18:40        274,280,448
MYPCNAME                  13-JUL-16 16:18:40        215,887,872
MYPCNAME                  13-JUL-16 16:18:40        204,615,680

Tuesday, September 22, 2015

Top 10 - Biggest Tables and Indexes

Top 10 - Biggest Tables and Indexes

set lines 170
set pages 20
col owner      format a20
col object     format a30
col type       format a6
col tablespace format a20
col size_in_gb format 999,999.9
select * 
  from (select owner           owner,
               segment_name    object,
               segment_type    type,
               tablespace_name tablespace,
               round(bytes/1024/1024/1024,1) size_in_gb
          from dba_segments 
         where owner not in ('SYS','SYSTEM')
         order by bytes desc
       ) 
 where rownum < 11
 order by size_in_gb desc;

SQL Outlines or SQL Profiles

SQL OUTLINES or PROFILES

set lines 170
set pages 100
col created      format a14
col type         format a6
col status       format a10
col fm           format a3
col profile_name format a30
col sql_text     format a50
col comp_data    format a50
SELECT created,
       type,
       status,
       force_matching fm,
       profile_name,
       sql_text,
       comp_data
  FROM DBA_SQL_PROFILES PROF,
       DBMSHSXP_SQL_PROFILE_ATTR ATTR
 WHERE prof.name = attr.profile_name
 ORDER BY status, created desc;

Parallel Query Processes - Parent / Child Details

Parallel Process Details including Master/Slave relationships

SQL
set lines 200
set pages 100
col username   format a10
col qcslave    format a10
col slaveset   format a8
col program    format a30
col sid        format a5
col slvinst    format a7
col state      format a8
col waitevent  format a30
col qcsid      format a5
col qcinst     format a6
col reqdop     format 999
col actdop     format 999
col secelapsed format 999,999
SELECT DECODE(px.qcinst_id,NULL,username, ' - '||LOWER(SUBSTR(pp.SERVER_NAME,LENGTH(pp.SERVER_NAME)-4,4) ) ) USERNAME, 
       DECODE(px.qcinst_id,NULL, 'QC', '(Slave)') "QCSLAVE" ,
       TO_CHAR( px.server_set) SLAVESET, 
       s.program PROGRAM, 
       TO_CHAR(s.SID) SID,
       TO_CHAR(px.inst_id) SLVINST, 
       DECODE(sw.state,'WAITING', 'WAIT', 'NOT WAIT' ) STATE,
       CASE  sw.state WHEN 'WAITING' THEN SUBSTR(sw.event,1,30) ELSE NULL END WAITEVENT ,
       DECODE(px.qcinst_id, NULL ,TO_CHAR(s.SID) ,px.qcsid) QCSID,
       TO_CHAR(px.qcinst_id) QCINST, 
       px.req_degree REQDOP, 
       px.DEGREE ACTDOP,
       DECODE(px.server_set,'',s.last_call_et,'') SECELAPSED
  FROM gv$px_session px, 
       gv$session s, 
       gv$px_process pp, 
       gv$session_wait sw
 WHERE px.SID=s.SID (+)
   AND px.serial#=s.serial#(+)
   AND px.inst_id = s.inst_id(+)
   AND px.SID = pp.SID (+)
   AND px.serial#=pp.serial#(+)
   AND sw.SID = s.SID
   AND sw.inst_id = s.inst_id
 ORDER BY DECODE(px.QCINST_ID,  NULL, px.INST_ID,  px.QCINST_ID), 
          px.QCSID,
          DECODE(px.SERVER_GROUP, NULL, 0, px.SERVER_GROUP), 
          px.SERVER_SET, 
          px.INST_ID



Tuesday, October 14, 2014

Script to Find Unopened files in ASM


Find Unopened files in ASM


set pagesize 0
set linesize 200
col full_alias_path format a80

select * from (
select  x.gnum,x.filnum,x.full_alias_path,f.ftype from (
SELECT gnum,filnum,concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path
FROM (SELECT g.name gname, a.parent_index pindex, a.name aname,
            a.reference_index rindex,a.group_number gnum,a.file_number filnum
      FROM v$asm_alias a, v$asm_diskgroup g
      WHERE a.group_number = g.group_number)
START WITH (mod(pindex, power(2, 24))) = 0 CONNECT BY PRIOR rindex = pindex) x,
(select group_number gnum,file_number filnum, type ftype from v$asm_file order by group_number,file_number) f
where x.filnum != 4294967295
and x.gnum=f.gnum and x.filnum=f.filnum
MINUS
select x.gnum,x.filnum,x.full_alias_path,f.ftype
from ( select id1 gnum,id2 filnum from v$lock where type='FA' and (lmode=4 or lmode=2)) l,
(
SELECT gnum,filnum,concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path
FROM (SELECT g.name gname, a.parent_index pindex, a.name aname,
            a.reference_index rindex,a.group_number gnum,a.file_number filnum
      FROM v$asm_alias a, v$asm_diskgroup g
      WHERE a.group_number = g.group_number)
START WITH (mod(pindex, power(2, 24))) = 0 CONNECT BY PRIOR rindex = pindex
) x,
(select group_number gnum,file_number filnum, type ftype from v\$asm_file order by group_number,file_number) f
where x.filnum != 4294967295 and
x.gnum=l.gnum
and x.filnum=l.filnum
and x.gnum=f.gnum and x.filnum=f.filnum) q
order  by q.gnum,q.ftype
/

Sample Output
1      13460 +MYDG1/LEGACYDB1/DATAFILE/indx01.dbf          DATAFILE

1      12440 +MYDG2/LEGACYDB2/DATAFILE/temp01.dbf          TEMPFILE


Tuesday, February 18, 2014

Reading ASM Disk Header

How to Read an ASM Disk Header

As root,
/u01/app/11.2.0/grid/bin/kfed read /dev/mapper/mydisk1|egrep "(dsksize|provstr|dskname|grpname|fgname)"

kfdhdb.driver.provstr:  ORCLDISKMYDISK1 ; 0x000: length=15
kfdhdb.dskname:                 MYDISK1 ; 0x028: length=7
kfdhdb.grpname:               MYDISKGROUP ; 0x048: length=9
kfdhdb.fgname:                  MYDISK1 ; 0x068: length=7
kfdhdb.dsksize:                  524294 ; 0x0c4: 0x00080006



To list all ASM devices using blkid:
blkid|grep sd.*oracleasm|while read a b;do echo -n $a$b" scsi_id=";(echo $a|tr -d [:digit:]|tr -d [:]|cut -d"/" -f3|xargs -i scsi_id -g -s /block/{})done;