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