VMCD.ORG

Focus on database mail:ylouis83#gmail.com

Scripts: Monitor Tablespace space growth

Posted by admin on 10th January 2013

这个脚本可以监控tablespace 空间增长趋势,并且邮件通知到相关domain– 根据TOAD SPACE MANAGEMENT 修改

创建schema,tablespace,table:

create tablespace toad '+ARCH/data/tbs_ts[......]

Read more

Tags:
Posted in Oracle Experience | No Comments »

Scripts: dbms_backup_restore modify dbid and db_name

Posted by admin on 5th December 2012

Scripts:dbms_backup_restore_dbid

eg:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Par[......]

Read more

Tags:
Posted in Oracle Experience | 1 Comment »

Scripts: 查找每年特定星期x的日期

Posted by admin on 10th August 2012

11g之前还不支持分区自动interval day 功能,对于需要指定日期的分区需求例如每周2一个分区,可以采用下面脚本实现

select  'alter table END_USER_LOGIN_LOG_1 add partition P'|| TO_CHAR[......]

Read more

Tags:
Posted in Oracle Experience | No Comments »

Scripts: Monitor smon rollback progress

Posted by admin on 7th July 2012

this scripts is used to monitor transaction rolling back progress

-------------------------------------------------------------------------------
--
-- Script: rolling_back.sql
-- Purpose: to predict when transactions will finish rolling back
-- For: 9.0+
--
-- Copyright: (c) Ixora Pty Ltd
-- Author: Steve Adams
--
-------------------------------------------------------------------------------
spool save_sqlplus_settings

set serveroutput on
set feedback off
prompt
prompt Looking for transactions that are rolling back ...
prompt

declare
  cursor tx is
    select
      s.username,
      t.xidusn,
      t.xidslot,
      t.xidsqn,
      x.ktuxesiz
    from
      sys.x$ktuxe  x,
      sys.v$transaction  t,
      sys.v$session  s
    where
      x.inst_id = userenv('Instance') and
      x.ktuxesta = 'ACTIVE' and
      x.ktuxesiz > 1 and
      t.xidusn = x.ktuxeusn and
      t.xidslot = x.ktuxeslt and
      t.xidsqn = x.ktuxesqn and
      s.saddr = t.ses_addr;
  user_name  varchar2(30);
  xid_usn    number;
  xid_slot   number;
  xid_sqn    number;
  used_ublk1 number;
  used_ublk2 number;
begin
  open tx;
  loop
    fetch tx into user_name, xid_usn, xid_slot, xid_sqn, used_ublk1;
    exit when tx%notfound;
    if tx%rowcount = 1
    then
      sys.dbms_lock.sleep(10);
    end if;
    select
      sum(ktuxesiz)
    into
      used_ublk2
    from
      sys.x$ktuxe
    where
      inst_id = userenv('Instance') and
      ktuxeusn = xid_usn and
      ktuxeslt = xid_slot and
      ktuxesqn = xid_sqn and
      ktuxesta = 'ACTIVE';
    if used_ublk2 < used_ublk1
    then
      sys.dbms_output.put_line(
        user_name ||
        '''s transaction ' ||
        xid_usn  || '.' ||
        xid_slot || '.' ||
        xid_sqn  ||
        ' will finish rolling back at approximately ' ||
        to_char(
          sysdate + used_ublk2 / (used_ublk1 - used_ublk2) / 6 / 60 / 24,
          'HH24:MI:SS DD-MON-YYYY'
        )
      );
    end if;
  end loop;
  if user_name is null
  then
    sys.dbms_output.put_line('No transactions appear to be rolling back.');
  end if;
end;   
/

prompt
exit;

eg:

SQL*Plus: Release 11.2.0.3.0 Production on Sat Jul 7 14:54:02 2012
 
Copyright (c) 1982, 2011, OracleAll rights reserved.
 
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL> @rollback
 
Looking for transactions that are rolling back ...
 
SYS's transaction 10.3.25785 will finish rolling back at approximately 14:55:52
07-JUL-2012
SQL> begin
 
2  for i in 1..1000000 loop
 
3  insert into t1 values (i);
 
4  end loop;
 
5  end;
 
6  /
 
PL/SQL procedure successfully completed.
 
SQL> rollback
 
2  ;
 
Rollback complete.
 
SQL> !date
Sat Jul  7 14:55:59 CST 2012

[......]

Read more

Tags:
Posted in Oracle Experience | No Comments »

Scripts: check user all granted privileges

Posted by admin on 14th June 2012

this scripts is used to check user all privileges

set serveroutput on;
DECLARE
   v_user          VARCHAR2(30)      := 'XXX';
   v_ddl           VARCHAR2(2000);
   v_status        VARCHAR2(32);
BEGIN
   -- Need to add the following to get the lines to end with semi-colons

dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',true);

   select dbms_metadata.get_ddl('USER',v_user) INTO v_ddl from dual;
   dbms_output.put_line(v_ddl);

   -- Get the user's granted quotas
   DECLARE
       v_quota     VARCHAR2(2000);
   BEGIN
       select dbms_metadata.get_granted_ddl('TABLESPACE_QUOTA',v_user)
INTO v_quota from dual;
       dbms_output.put_line(v_quota);
   EXCEPTION
       WHEN OTHERS THEN
           dbms_output.put_line('-- No quotas granted');
   END;

   --Get the user's granted roles
   DECLARE
       v_role      VARCHAR2(2000);
   BEGIN
       select dbms_metadata.get_granted_ddl('ROLE_GRANT',v_user) INTO
v_role from dual;
       dbms_output.put_line(v_role);
   EXCEPTION
       WHEN OTHERS THEN
           dbms_output.put_line('-- No roles granted');
   END;

   -- Get the user's system grants
   DECLARE
       v_system    VARCHAR2(2000);
   BEGIN
       select dbms_metadata.get_granted_ddl('SYSTEM_GRANT',v_user) INTO
v_system from dual;
       dbms_output.put_line(v_system);
   EXCEPTION
       WHEN OTHERS THEN
           dbms_output.put_line('-- NO system grants');
   END;

   -- Get the user's object grants
   DECLARE
       v_object    VARCHAR2(30000);
   BEGIN
       select dbms_metadata.get_granted_ddl('OBJECT_GRANT',v_user) INTO
v_object from dual;
       dbms_output.put_line(v_object);
   EXCEPTION
       WHEN OTHERS THEN
           dbms_output.put_line('-- NO object grants');
   END;

   SELECT account_status INTO v_status FROM dba_users WHERE username =
v_user;

   IF(v_status = 'OPEN') THEN
       dbms_output.put_line('ALTER USER '||v_user||' ACCOUNT UNLOCK');
   END IF;
EXCEPTION
   WHEN OTHERS THEN
       dbms_output.put_line('-- User not found');
END;
/

eg:

SQL> set serveroutput on;
SQL> DECLARE
   v_user          VARCHAR2(30)      := 'LIU';
   v_ddl           VARCHAR2(2000);
   v_status        VARCHAR2(32);
BEGIN
   -- Need to add the following to get the lines to end with semi-colons

dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',true);

   select dbms_metadata.get_ddl('USER',v_user) INTO v_ddl from dual;
   dbms_output.put_line(v_ddl);

   -- Get the user's granted quotas
   DECLARE
       v_quota     VARCHAR2(2000);
   BEGIN
       select dbms_metadata.get_granted_ddl('TABLESPACE_QUOTA',v_user)
  INTO v_quota from dual;
       dbms_output.put_line(v_quota);
   EXCEPTION
       WHEN OTHERS THEN
           dbms_output.put_line('-- No quotas granted');
   END;

   --Get the user's granted roles
   DECLARE
       v_role      VARCHAR2(2000);
   BEGIN
       select dbms_metadata.get_granted_ddl('ROLE_GRANT',v_user) INTO
v_role from dual;
       dbms_output.put_line(v_role);
   EXCEPTION
       WHEN OTHERS THEN
           dbms_output.put_line('-- No roles granted');
   END;

-- Get the user's system grants
   DECLARE
       v_system    VARCHAR2(2000);
   BEGIN
       select dbms_metadata.get_granted_ddl('SYSTEM_GRANT',v_user) INTO
v_system from dual;
       dbms_output.put_line(v_system);
   EXCEPTION
       WHEN OTHERS THEN
           dbms_output.put_line('-- NO system grants');
   END;

   -- Get the user's object grants
   DECLARE
       v_object    VARCHAR2(2000);
   BEGIN
       select dbms_metadata.get_granted_ddl('OBJECT_GRANT',v_user) INTO
v_object from dual;
       dbms_output.put_line(v_object);
 EXCEPTION
       WHEN OTHERS THEN
           dbms_output.put_line('-- NO object grants');
   END;

   SELECT account_status INTO v_status FROM dba_users WHERE username =
v_user;

   IF(v_status = 'OPEN') THEN
       dbms_output.put_line('ALTER USER '||v_user||' ACCOUNT UNLOCK');
   END IF;
EXCEPTION
   WHEN OTHERS THEN
       dbms_output.put_line('-- User not found');
END;
/

   CREATE USER "LIU" IDENTIFIED BY VALUES
'S:416DF25746736369B087A8388B7178AF33DA895C7F7DC8D997455C1BF0E9;9E1A31EC43A0799C
'
      DEFAULT TABLESPACE "UDATA"
      TEMPORARY TABLESPACE "TEMP";
-- No quotas granted

   GRANT "DBA" TO "LIU";

  GRANT UNLIMITED TABLESPACE TO "LIU";

  GRANT EXECUTE ON "SYS"."DBMS_SYSTEM" TO "LIU";
  GRANT DELETE ON
"TEST2"."COUPON" TO "LIU";
  GRANT SELECT ON "TEST2"."COUPON" TO "LIU";
  GRANT
SELECT ON "SYS"."T" TO "LIU";
ALTER USER LIU ACCOUNT UNLOCK

PL/SQL procedure successfully completed.

Tags:
Posted in Oracle Experience | No Comments »

“hcheck.sql” script to check for known problems in Oracle8i, Oracle9i, Oracle10g and Oracle 11g

Posted by admin on 20th April 2012

Execution Environment:
    

Access Privileges:
     Requires to be run connected as SYS schema

Usage:
$ sqlplus
SQL*Plus: Release 9.2.0.2.0 - Production on Mon Nov 11 12:00:06 2002

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Enter user-name: / as sysdba

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.2.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.2.0 - Production 
SQL> set serveroutput on size unlimited
SQL> spool outputfile
SQL> execute hcheck.full
Instructions:

1. Connect as SYS schema in sqlplus
2. Create package hOut as described in Note:101468.1
3. Create package hcheck in SYS schema (Refer the attachments under SCRIPT to Create package hcheck, this is hcheck2.sql and/or hcheck3.sql 
4. spool outputfile
5. execute hcheck.full

6. Output will go to the spool file and the session trace file.
   The script will report various dictionary related issues
   which may or may not be a problem - Any problems reported
   should be reviewed by an experienced support analyst as some
   reported "problems" may be normal and expected.
PROOFREAD THE SCRIPT BEFORE USING IT! Due to differences in the way text
editors, e-mail packages, and operating systems handle text formatting (spaces,
tabs, and carriage returns), this script may not be in an executable state
when you first receive it. Check over the script to ensure that errors of
this type are corrected.The script will produce an output file named [outputfile].
This file can be viewed in a browser or uploaded for support analysis.
Description
To provide a single package which looks for common data dictionary
problems.
The script can be used with Oracle versions 9i onwards (release 2.00 includes 8.1)
It checks consistency of certain dictionary relationships and looks for certain
known issues - certain reported "problems" will be normal and expected.

This script is for use mainly under the guidance of Oracle Support.

eg:

SQL> exec hcheck.full
H.Check Version 9i+/hc3.45
-------------------------------------
--
Catalog Version 11.2.0.3.0 (1102000300)

-------------------------------------
--
 

                  
Catalog     Fixed
Procedure Name               Version    Vs Release      Run
----------------------------
-- ... ---------- -- ----------   ---
.-
SynLastDDLTim           ... 11020003001001000200 : n/a
.-
LobNotInObj               ... 11020003001000000200 : n/a
.-
MissingOIDOnObjCol           ... 1102000300 <=  *All Rel* : Ok
.-
SourceNotInObj           ... 11020003001002000100 : n/a
.-
IndIndparMismatch           ... 11020003001102000100 : n/a
.-
InvCorrAudit            ... 11020003001102000100 : n/a
.-
OversizedFiles           ... 1102000300 <=  *All Rel* : Ok
.-
TinyFiles               ... 1102000300 >   900010000 : n/a
.-
PoorDefaultStorage           ... 1102000300 <=  *All Rel* : Ok
.-
PoorStorage               ... 1102000300 <=  *All Rel* : Ok
.-
MissTabSubPart           ... 1102000300 >   900010000 : n/a
.-
PartSubPartMismatch           ... 11020003001102000100 : n/a
.-
TabPartCountMismatch        ... 1102000300 <=  *All Rel* : Ok
.-
OrphanedTabComPart           ... 1102000300 >   900010000 : n/a
.-
ZeroTabSubPart           ... 1102000300 >   902000100 : n/a
.-
MissingSum$               ... 1102000300 <=  *All Rel* : Ok
.-
MissingDir$               ... 1102000300 <=  *All Rel* : Ok
.-
DuplicateDataobj           ... 1102000300 <=  *All Rel* : Ok
.-
ObjSynMissing           ... 1102000300 <=  *All Rel* : Ok
.-
ObjSeqMissing           ... 1102000300 <=  *All Rel* : Ok
.-
OrphanedUndo            ... 1102000300 <=  *All Rel* : Ok
.-
OrphanedIndex           ... 1102000300 <=  *All Rel* : Ok
.-
OrphanedIndexPartition      ... 1102000300 <=  *All Rel* : Ok
.-
OrphanedIndexSubPartition   ... 1102000300 <=  *All Rel* : Ok
.-
OrphanedTable           ... 1102000300 <=  *All Rel* : Ok
.-
OrphanedTablePartition      ... 1102000300 <=  *All Rel* : Ok
.-
OrphanedTableSubPartition   ... 1102000300 <=  *All Rel* : Ok
.-
MissingPartCol           ... 1102000300 <=  *All Rel* : Ok
.-
OrphanedSeg$            ... 1102000300 <=  *All Rel* : Ok
.-
OrphanedIndPartObj#           ... 1102000300 >  1101000600 : n/a
.-
DuplicateBlockUse           ... 1102000300 <=  *All Rel* : Ok
.-
HighObjectIds           ... 1102000300 >   801060000 : n/a
.-
PQsequence               ... 1102000300 >   800060000 : n/a
.-
TruncatedCluster           ... 1102000300 >   801070000 : n/a
.-
FetUet               ... 1102000300 <=  *All Rel* : Ok
.-
Uet0Check               ... 1102000300 <=  *All Rel* : Ok
.-
ExtentlessSeg           ... 1102000300 <=  *All Rel* : Ok
.-
SeglessUET               ... 1102000300 <=  *All Rel* : Ok
.-
BadInd$               ... 1102000300 <=  *All Rel* : Ok
.-
BadTab$               ... 1102000300 <=  *All Rel* : Ok
.-
BadIcolDepCnt           ... 11020003001101000700 : n/a
.-
WarnIcolDep               ... 11020003001101000700 : n/a
.-
OnlineRebuild$           ... 1102000300 <=  *All Rel* : Ok
.-
DropForceType           ... 11020003001001000200 : n/a
.-
TrgAfterUpgrade           ... 1102000300 <=  *All Rel* : Ok
.-
FailedInitJVMRun           ... 1102000300 <=  *All Rel* : Ok
.-
TypeReusedAfterDrop           ... 1102000300 >   900010000 : n/a
.-
Idgen1$TTS               ... 1102000300 >   900010000 : n/a
.-
DroppedFuncIdx           ... 1102000300 >   902000100 : n/a
.-
BadOwner               ... 1102000300 >   900010000 : n/a
.-
UpgCheckc0801070           ... 1102000300 <=  *All Rel* : Ok
.-
BadPublicObjects           ... 1102000300 <=  *All Rel* : Ok
.-
BadSegFreelist           ... 1102000300 <=  *All Rel* : Ok
.-
BadCol#               ... 1102000300 >  1001000200 : n/a
.-
BadDepends               ... 1102000300 <=  *All Rel* : Ok
.-
CheckDual               ... 1102000300 <=  *All Rel* : Ok
.-
ObjectNames               ... 1102000300 <=  *All Rel* : Ok
.-
BadCboHiLo               ... 1102000300 <=  *All Rel* : Ok
.-
ChkIotTs               ... 1102000300 <=  *All Rel* : Ok
.-
NoSegmentIndex           ... 1102000300 <=  *All Rel* : Ok
.-
BadNextObject           ... 1102000300 <=  *All Rel* : Ok
.-
OrphanIndopt            ... 1102000300 >   902000800 : n/a
.-
UpgFlgBitTmp            ... 11020003001001000100 : n/a
.-
RenCharView               ... 11020003001001000100 : n/a
.-
Upg9iTab$               ... 1102000300 >   902000400 : n/a
.-
Upg9iTsInd               ... 1102000300 >   902000500 : n/a
.-
Upg10gInd$               ... 11020003001002000000 : n/a
.-
DroppedROTS               ... 1102000300 <=  *All Rel* : Ok
.-
ChrLenSmtcs               ... 11020003001101000600 : n/a
.-
FilBlkZero               ... 1102000300 <=  *All Rel* : Ok
.-
DbmsSchemaCopy           ... 1102000300 <=  *All Rel* : Ok
 
Found 0 potential problem(s) and 0 warning(s)
 
PL/SQL procedure successfully completed.

Tags:
Posted in Oracle Experience | No Comments »

Scripts:sess_uncommited_transactions.sql

Posted by admin on 2nd March 2012

This script is monitoring sessions that are not commited :

-- |----------------------------------------------------------------------------|
-- |      Copyright (c) 1998-2012 Jeffrey M. Hunter. All rights reserved.       |
-- |----------------------------------------------------------------------------|
-- | DATABASE : Oracle                                                          |
-- | FILE     : sess_uncommited_transactions.sql                                |
-- | CLASS    : Session Management                                              |
-- | PURPOSE  : Query all users with uncommited transactions.                   |
-- | NOTE     : As with any code, ensure to test this script in a development   |
-- |            environment before attempting to run it in production.          |
-- +----------------------------------------------------------------------------+


SET LINESIZE 145
SET PAGESIZE 9999


COLUMN sid                     FORMAT 99999            HEADING 'SID'
COLUMN serial_id               FORMAT 99999999         HEADING 'Serial ID'
COLUMN session_status          FORMAT a9               HEADING 'Status'          JUSTIFY right
COLUMN oracle_username         FORMAT a14              HEADING 'Oracle User'     JUSTIFY right
COLUMN os_username             FORMAT a12              HEADING 'O/S User'        JUSTIFY right
COLUMN os_pid                  FORMAT 9999999          HEADING 'O/S PID'         JUSTIFY right
COLUMN session_program         FORMAT a18              HEADING 'Session Program' TRUNC
COLUMN session_machine         FORMAT a15              HEADING 'Machine'         JUSTIFY right
COLUMN number_of_undo_records  FORMAT 999,999,999,999  HEADING "# Undo Records"
COLUMN used_undo_size          FORMAT 999,999,999,999  HEADING  "Used Undo Size"


SELECT
    s.sid                  sid
  , lpad(s.status,9)       session_status
  , lpad(s.username,14)    oracle_username
  , lpad(s.osuser,12)      os_username
  , lpad(p.spid,7)         os_pid
  , b.used_urec            number_of_undo_records
  , b.used_ublk * d.value  used_undo_size
  , s.program              session_program
  , lpad(s.machine,15)     session_machine
FROM
    v$process      p
  , v$session      s
  , v$transaction  b
  , v$parameter    d
WHERE
      b.ses_addr =  s.saddr
  AND p.addr (+) =  s.paddr
  AND s.audsid   <> userenv('SESSIONID')
  AND d.name     =  'db_block_size';

Tags:
Posted in Oracle Experience | No Comments »

Scripts:lock_internal

Posted by admin on 10th February 2012

set echo off

--------------------------------------------------
-- @name: lock_internal
-- @author: dion cho
-- @description: my own version of dba_lock_internal
---------------------------------------------------

define __SID = "&1"
define __NAME = "&2"

col handle format a10
col type format a5

select
  *
from (
  -- enqueue
  select
    sid,
    type,
    '('||id1||','||id2||')' as "handle",
    lmode as "mod",
    request as "req",
    (select object_name from dba_objects where object_id = id1 and rownum=1) as name
  from v$lock
  where sid in (&__SID)
  union all
  -- library cache lock
  select
    (select sid from v$session where saddr = kgllkuse) as sid,
    'lock' as "type",
    kgllkhdl||'' as "handle",
    kgllkmod as "mod",
    kgllkreq as "req",
    substr(kglnaobj, 1, 60) as name
  from sys.x$kgllk
  where kgllkuse in (select saddr from v$session where sid in (&__SID))
    and (kgllkmod > 0 or kgllkreq > 0)
  union all
  -- library cache pin
  select
   (select sid from v$session where saddr = kglpnuse) as sid,
    'pin' as "type",
   kglpnhdl||'' as "handle",
    kglpnmod as "mod",
    kglpnreq as "req",
    (select substr(kglnaobj, 1, 60) from sys.x$kglob where kglhdadr = kglpnhdl and rownum = 1) as name
  from sys.x$kglpn
  where kglpnuse in (select saddr from v$session where sid in (&__SID))
    and (kglpnmod > 0 or kglpnreq > 0)
)
where name like '&__NAME'
;


set echo on

example:

SQL> @lock_internal 159 %t2%
SQL> set echo off
old  13:   where sid in (&__SID)
new  13:   where sid in (159)
old  24:   where kgllkuse in (select saddr from v$session where sid in (&__SID))
new  24:   where kgllkuse in (select saddr from v$session where sid in (159))
old  36:   where kglpnuse in (select saddr from v$session where sid in (&__SID))
new  36:   where kglpnuse in (select saddr from v$session where sid in (159))
old  39: where name like '&__NAME'
new  39: where name like '%t2%'
 
      
SID TYPE  handle            mod        req NAME
--------
-- ----- ---------- ---------- ---------- --------------------------------------------------
      
159 lock  3C8C9E1C            1          0 select /*+ session_cache */ * from  t2
      
159 lock  393E4FD0            1          0 select /*+ session_cache */ * from  t2

Tags:
Posted in Oracle Experience | No Comments »

Scripts:show_space

Posted by admin on 16th January 2012

修改过的show_space 支持ASSM

create or replace   
procedure show_space                           
( p_segname_1 in varchar2,
p_space in varchar2 default 'MANUAL',
p_type_1 in varchar2 default 'TABLE' ,
p_freespace in varchar2 default 'N',
p_owner_1 in varchar2 default user)
as
p_segname varchar2(100);
p_type varchar2(10);
p_owner varchar2(30);
l_unformatted_blocks number;
l_unformatted_bytes number;
l_fs1_blocks number;
l_fs1_bytes number;
l_fs2_blocks number;
l_fs2_bytes number;
l_fs3_blocks number;
l_fs3_bytes number;
l_fs4_blocks number;
l_fs4_bytes number;
l_full_blocks number;
l_full_bytes number;
l_free_blks number;
l_total_blocks number;
l_total_bytes number;
l_unused_blocks number;
l_unused_bytes number;
l_LastUsedExtFileId number;
l_LastUsedExtBlockId number;
l_LAST_USED_BLOCK number;
procedure p( p_label in varchar2, p_num in number )
is
begin
dbms_output.put_line( rpad(p_label,40,'.') ||
p_num );
end;
begin
p_segname := upper(p_segname_1); -- rainy changed
p_owner := upper(p_owner_1);
p_type := p_type_1;
 
if (p_type_1 = 'i' or p_type_1 = 'I') then --rainy changed
p_type := 'INDEX';
end if;
 
if (p_type_1 = 't' or p_type_1 = 'T') then --rainy changed
p_type := 'TABLE';
end if;
 
if (p_type_1 = 'c' or p_type_1 = 'C') then --rainy changed
p_type := 'CLUSTER';
end if;
dbms_space.unused_space
( segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
total_blocks => l_total_blocks,
total_bytes => l_total_bytes,
unused_blocks => l_unused_blocks,
unused_bytes => l_unused_bytes,
LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
LAST_USED_BLOCK => l_LAST_USED_BLOCK );
if p_space = 'MANUAL' or (p_space <> 'auto' and p_space <> 'AUTO') then
dbms_space.free_blocks
( segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
freelist_group_id => 0,
free_blks => l_free_blks );
p( 'Free Blocks', l_free_blks );
end if;
p( 'Total Blocks', l_total_blocks );
p( 'Total Bytes', l_total_bytes );
p( 'Unused Blocks', l_unused_blocks );
p( 'Unused Bytes', l_unused_bytes );
p( 'Last Used Ext FileId', l_LastUsedExtFileId );
p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
p( 'Last Used Block', l_LAST_USED_BLOCK );
if p_freespace = 'Y' then
dbms_space.space_usage(segment_owner => p_owner ,
segment_name => p_segname ,
segment_type => p_type ,
unformatted_blocks => l_unformatted_blocks ,
unformatted_bytes => l_unformatted_bytes,
fs1_blocks => l_fs1_blocks,
fs1_bytes => l_fs1_bytes ,
fs2_blocks => l_fs2_blocks,
fs2_bytes => l_fs2_bytes,
fs3_blocks => l_fs3_blocks ,
fs3_bytes => l_fs3_bytes,
fs4_blocks => l_fs4_blocks,
fs4_bytes => l_fs4_bytes,
full_blocks => l_full_blocks,
full_bytes => l_full_bytes);
dbms_output.put_line(rpad(' ',50,'*'));
p( '0% -- 25% free space blocks', l_fs1_blocks);
p( '0% -- 25% free space bytes', l_fs1_bytes);
p( '25% -- 50% free space blocks', l_fs2_blocks);
p( '25% -- 50% free space bytes', l_fs2_bytes);
p( '50% -- 75% free space blocks', l_fs3_blocks);
p( '50% -- 75% free space bytes', l_fs3_bytes);
p( '75% -- 100% free space blocks', l_fs4_blocks);
p( '75% -- 100% free space bytes', l_fs4_bytes);
p( 'Unused Blocks', l_unformatted_blocks );
p( 'Unused Bytes', l_unformatted_bytes );
p( 'Total Blocks', l_full_blocks);
p( 'Total bytes', l_full_bytes);
 
end if;
 
end;

EXAMPLE:

SQL> create table t1 as select * from dba_objects;
 
Table created.
 
SQL> insert into t1 select * from t1;
 
36729 rows created.
 
SQL> commit;
 
Commit complete.
 
SQL> insert into t1 select * from t1;
 
73458 rows created.
 
SQL> commit;
 
Commit complete.
 
 
SQL> insert into t1 select * from t1;
 
146916 rows created.
 
SQL> commit;
 
 
SQL> alter system checkpoint;
 
System altered.
 
SQLexec show_space('T1','AUTO','T','Y');
Total Blocks............................4096
Total Bytes.............................33554432
Unused Blocks...........................0
Unused Bytes............................0
Last Used Ext FileId....................10
Last Used Ext BlockId...................1104905
Last Used Block.........................128
*************************************************
0% -- 25% free space blocks.............2
0% -- 25% free space bytes..............16384
25% -- 50% free space blocks............0
25% -- 50% free space bytes.............0
50% -- 75% free space blocks............0
50% -- 75% free space bytes.............0
75% -- 100% free space blocks...........2
75% -- 100% free space bytes............16384
Unused Blocks...........................0
Unused Bytes............................0
Total Blocks............................4020
Total bytes.............................32931840
 
PL/SQL procedure successfully completed.
 
SQL>

Tags:
Posted in Oracle Experience | No Comments »

Scripts:xplan_extended_display_cursor

Posted by admin on 9th January 2012

Alter session set sql_trace=true;
Alter session set STATISTICS_LEVEL = ALL;


set echo off verify off termout off
set doc off
doc
-- ----------------------------------------------------------------------------------------------
--
-- Script:       xplan_extended_display_cursor.sql
--
-- Version:      0.9
--               December 2011
--
-- Author:       Randolf Geist
--               oracle-randolf.blogspot.com
--
-- Description:  A free-standing SQL wrapper over DBMS_XPLAN. Provides access to the
--               DBMS_XPLAN.DISPLAY_CURSOR pipelined function for a given SQL_ID and CHILD_NUMBER
--
--               This is a prototype for an extended analysis of the data provided by the
--               Runtime Profile (aka. Rowsource Statistics enabled via
--               SQL_TRACE = TRUE, STATISTICS_LEVEL = ALL or GATHER_PLAN_STATISTICS hint)
--               and reported via the ALLSTATS/MEMSTATS/IOSTATS formatting option of
--               DBMS_XPLAN.DISPLAY_CURSOR
--
-- Versions:     This utility will work for all versions of 10g and upwards.
--
-- Required:     The same access as DBMS_XPLAN.DISPLAY_CURSOR requires. See the documentation
--               of DISPLAY_CURSOR for your Oracle version for more information
--
--               The script directly queries
--               1) V$SESSION
--               2) V$SQL_PLAN_STATISTICS_ALL
--
-- Credits:      Based on the original XPLAN implementation by Adrian Billington (http://www.oracle-developer.net/utilities.php
--               resp. http://www.oracle-developer.net/content/utilities/xplan.zip)
--               and inspired by Kyle Hailey's TCF query (http://dboptimizer.com/2011/09/20/display_cursor/)
--
-- Features:     In addition to the PID (The PARENT_ID) and ORD (The order of execution, note that this doesn't account for the special cases so it might be wrong)
--               columns added by Adrian's wrapper the following additional columns over ALLSTATS are provided:
--
--               A_TIME_SELF        : The time taken by the operation itself - this is the operation's cumulative time minus the direct descendants operation's cumulative time
--               LIO_SELF           : The LIOs done by the operation itself - this is the operation's cumulative LIOs minus the direct descendants operation's cumulative LIOs
--               READS_SELF         : The reads performed the operation itself - this is the operation's cumulative reads minus the direct descendants operation's cumulative reads
--               WRITES_SELF        : The writes performed the operation itself - this is the operation's cumulative writes minus the direct descendants operation's cumulative writes
--               A_TIME_SELF_GRAPH  : A graphical representation of A_TIME_SELF relative to the total A_TIME
--               LIO_SELF_GRAPH     : A graphical representation of LIO_SELF relative to the total LIO
--               READS_SELF_GRAPH   : A graphical representation of READS_SELF relative to the total READS
--               WRITES_SELF_GRAPH  : A graphical representation of WRITES_SELF relative to the total WRITES
--               LIO_RATIO          : Ratio of LIOs per row generated by the row source - the higher this ratio the more likely there could be a more efficient way to generate those rows (be aware of aggregation steps though)
--               TCF_GRAPH          : Each "+"/"-" sign represents one order of magnitude based on ratio between E_ROWS_TIMES_START and A-ROWS. Note that this will be misleading with Parallel Execution (see E_ROWS_TIMES_START)
--               E_ROWS_TIMES_START : The E_ROWS multiplied by STARTS - this is useful for understanding the actual cardinality estimate for related combine child operations getting executed multiple times. Note that this will be misleading with Parallel Execution
--
--               More information including demos can be found online at http://oracle-randolf.blogspot.com/2011/12/extended-displaycursor-with-rowsource.html
--
-- Usage:        @xplan_extended_display_cursor.sql [sql_id] [cursor_child_number] [format_option]
--
--               If both the SQL_ID and CHILD_NUMBER are omitted the previously executed SQL_ID and CHILD_NUMBER of the session will be used
--               If the SQL_ID is specified but the CHILD_NUMBER is omitted then CHILD_NUMBER 0 is assumed
--
--               This prototype does not support processing multiple child cursors like DISPLAY_CURSOR is capable of
--               when passing NULL as CHILD_NUMBER to DISPLAY_CURSOR. Hence a CHILD_NUMBER is mandatory, either
--               implicitly generated (see above) or explicitly passed
--
--               The default formatting option for the call to DBMS_XPLAN.DISPLAY_CURSOR is ALLSTATS LAST - extending this output is the primary purpose of this script
--
-- Note:         You need a veeery wide terminal setting for this prototype, something like linesize 400 should suffice
--
--               This tool is free but comes with no warranty at all - use at your own risk
--
#

col plan_table_output format a400
set linesize 400 pagesize 0 tab off

/* ALLSTATS LAST is assumed as the default formatting option for DBMS_XPLAN.DISPLAY_CURSOR */
define default_fo = "ALLSTATS LAST"

column prev_sql_id new_value prev_sql_id
column prev_child_number new_value prev_cn

/* Get the previous command as default
   if no SQL_ID / CHILD_NUMBER is passed */
select
        prev_sql_id
      , prev_child_number
from
        v$session
where
        sid = userenv('sid')
;

-- The following is a hack to use default
-- values for defines
column 1 new_value 1
column 2 new_value 2
column 3 new_value 3

select
        '' as "1"
      , '' as "2"
      , '' as "3"
from
        dual
where
        rownum = 0;

column si new_value si
column cn new_value cn
column fo new_value fo

/* Use passed parameters else refer to previous SQL_ID / CHILD_NUMBER
   ALLSTATS LAST is default formatting option */
select
        nvl('&1', '&prev_sql_id')       as si
      , coalesce('&2', '&prev_cn', '0') as cn
      , nvl('&3', '&default_fo')        as fo
from
        dual
;

column last new_value last

/* Last or all execution */
select
       case
       when instr('&fo', 'LAST') > 0
       then 'last_'
       end  as last
from
       dual
;

set termout on

with
-- The next three queries are based on the original XPLAN wrapper by Adrian Billington
-- to determine the PID and ORD information, only slightly modified to deal with
-- the 10g special case that V$SQL_PLAN_STATISTICS_ALL doesn't include the ID = 0 operation
-- and starts with 1 instead for Rowsource Statistics
sql_plan_data as
(
  select
          id
        , parent_id
  from
          v$sql_plan_statistics_all
  where
          sql_id = '&si'
  and     child_number = &cn
),
hierarchy_data as
(
  select
          id
        , parent_id
  from
          sql_plan_data
  start with
          id in
          (
            select
                    id
            from
                    sql_plan_data p1
            where
                    not exists
                    (
                      select
                              null
                      from
                              sql_plan_data p2
                      where
                              p2.id = p1.parent_id
                    )
          )
  connect by
          prior id = parent_id
  order siblings by
          id desc
),
ordered_hierarchy_data as
(
  select
          id
        , parent_id                                as pid
        , row_number() over (order by rownum desc) as oid
        , max(id) over ()                          as maxid
        , min(id) over ()                          as minid
  from
          hierarchy_data
),
-- The following query uses the MAX values
-- rather than taking the values of PLAN OPERATION_ID = 0 (or 1 for 10g V$SQL_PLAN_STATISTICS_ALL)
-- for determining the grand totals
--
-- This is because queries that get cancelled do not
-- necessarily have yet sensible values in the root plan operation
--
-- Furthermore with Parallel Execution the elapsed time accumulated
-- with the ALLSTATS option for operations performed in parallel
-- will be greater than the wallclock elapsed time shown for the Query Coordinator
--
-- Note that if you use GATHER_PLAN_STATISTICS with the default
-- row sampling frequency the (LAST_)ELAPSED_TIME will be very likely
-- wrong and hence the time-based graphs and self-statistics will be misleading
--
-- Similar things might happen when cancelling queries
--
-- For queries running with STATISTICS_LEVEL = ALL (or sample frequency set to 1)
-- the A-TIME is pretty reliable
totals as
(
  select
          max(&last.cu_buffer_gets + &last.cr_buffer_gets) as total_lio
        , max(&last.elapsed_time)                          as total_elapsed
        , max(&last.disk_reads)                            as total_reads
        , max(&last.disk_writes)                           as total_writes
  from
          v$sql_plan_statistics_all
  where
          sql_id = '&si'
  and     child_number = &cn
),
-- The totals for the direct descendants of an operation
-- These are required for calculating the work performed
-- by a (parent) operation itself
-- Basically this is the SUM grouped by PARENT_ID
direct_desc_totals as
(
  select
          sum(&last.cu_buffer_gets + &last.cr_buffer_gets) as lio
        , sum(&last.elapsed_time)                          as elapsed
        , sum(&last.disk_reads)                            as reads
        , sum(&last.disk_writes)                           as writes
        , parent_id
  from
          v$sql_plan_statistics_all
  where
          sql_id = '&si'
  and     child_number = &cn
  group by
          parent_id
),
-- Putting the three together
-- The statistics, direct descendant totals plus totals
extended_stats as
(
  select
          stats.id
        , stats.parent_id
        , stats.&last.elapsed_time                                  as elapsed
        , (stats.&last.cu_buffer_gets + stats.&last.cr_buffer_gets) as lio
        , stats.&last.starts                                        as starts
        , stats.&last.output_rows                                   as a_rows
        , stats.cardinality                                         as e_rows
        , stats.&last.disk_reads                                    as reads
        , stats.&last.disk_writes                                   as writes
        , ddt.elapsed                                               as ddt_elapsed
        , ddt.lio                                                   as ddt_lio
        , ddt.reads                                                 as ddt_reads
        , ddt.writes                                                as ddt_writes
        , t.total_elapsed
        , t.total_lio
        , t.total_reads
        , t.total_writes
  from
          v$sql_plan_statistics_all stats
        , direct_desc_totals ddt
        , totals t
  where
          stats.sql_id='&si'
  and     stats.child_number = &cn
  and     ddt.parent_id (+) = stats.id
),
-- Further information derived from above
derived_stats as
(
  select
          id
        , greatest(elapsed - nvl(ddt_elapsed , 0), 0)                              as elapsed_self
        , greatest(lio - nvl(ddt_lio, 0), 0)                                       as lio_self
        , trunc((greatest(lio - nvl(ddt_lio, 0), 0)) / nullif(a_rows, 0))          as lio_ratio
        , greatest(reads - nvl(ddt_reads, 0), 0)                                   as reads_self
        , greatest(writes - nvl(ddt_writes,0) ,0)                                  as writes_self
        , total_elapsed
        , total_lio
        , total_reads
        , total_writes
        , trunc(log(10, nullif(starts * e_rows / nullif(a_rows, 0), 0)))           as tcf_ratio
        , starts * e_rows                                                          as e_rows_times_start
  from
          extended_stats
),
/* Format the data as required */
formatted_data1 as
(
  select
          id
        , lio_ratio
        , total_elapsed
        , total_lio
        , total_reads
        , total_writes
        , to_char(numtodsinterval(round(elapsed_self / 10000) * 10000 / 1000000, 'SECOND'))                         as e_time_interval
          /* Imitate the DBMS_XPLAN number formatting */
        , case
          when lio_self >= 18000000000000000000 then to_char(18000000000000000000/1000000000000000000, 'FM99999') || 'E'
          when lio_self >= 10000000000000000000 then to_char(lio_self/1000000000000000000, 'FM99999') || 'E'
          when lio_self >= 10000000000000000 then to_char(lio_self/1000000000000000, 'FM99999') || 'P'
          when lio_self >= 10000000000000 then to_char(lio_self/1000000000000, 'FM99999') || 'T'
          when lio_self >= 10000000000 then to_char(lio_self/1000000000, 'FM99999') || 'G'
          when lio_self >= 10000000 then to_char(lio_self/1000000, 'FM99999') || 'M'
          when lio_self >= 100000 then to_char(lio_self/1000, 'FM99999') || 'K'
          else to_char(lio_self, 'FM99999') || ' '
          end                                                                                                       as lio_self_format
        , case
          when reads_self >= 18000000000000000000 then to_char(18000000000000000000/1000000000000000000, 'FM99999') || 'E'
          when reads_self >= 10000000000000000000 then to_char(reads_self/1000000000000000000, 'FM99999') || 'E'
          when reads_self >= 10000000000000000 then to_char(reads_self/1000000000000000, 'FM99999') || 'P'
          when reads_self >= 10000000000000 then to_char(reads_self/1000000000000, 'FM99999') || 'T'
          when reads_self >= 10000000000 then to_char(reads_self/1000000000, 'FM99999') || 'G'
          when reads_self >= 10000000 then to_char(reads_self/1000000, 'FM99999') || 'M'
          when reads_self >= 100000 then to_char(reads_self/1000, 'FM99999') || 'K'
          else to_char(reads_self, 'FM99999') || ' '
          end                                                                                                       as reads_self_format
        , case
          when writes_self >= 18000000000000000000 then to_char(18000000000000000000/1000000000000000000, 'FM99999') || 'E'
          when writes_self >= 10000000000000000000 then to_char(writes_self/1000000000000000000, 'FM99999') || 'E'
          when writes_self >= 10000000000000000 then to_char(writes_self/1000000000000000, 'FM99999') || 'P'
          when writes_self >= 10000000000000 then to_char(writes_self/1000000000000, 'FM99999') || 'T'
          when writes_self >= 10000000000 then to_char(writes_self/1000000000, 'FM99999') || 'G'
          when writes_self >= 10000000 then to_char(writes_self/1000000, 'FM99999') || 'M'
          when writes_self >= 100000 then to_char(writes_self/1000, 'FM99999') || 'K'
          else to_char(writes_self, 'FM99999') || ' '
          end                                                                                                       as writes_self_format
        , case
          when e_rows_times_start >= 18000000000000000000 then to_char(18000000000000000000/1000000000000000000, 'FM99999') || 'E'
          when e_rows_times_start >= 10000000000000000000 then to_char(e_rows_times_start/1000000000000000000, 'FM99999') || 'E'
          when e_rows_times_start >= 10000000000000000 then to_char(e_rows_times_start/1000000000000000, 'FM99999') || 'P'
          when e_rows_times_start >= 10000000000000 then to_char(e_rows_times_start/1000000000000, 'FM99999') || 'T'
          when e_rows_times_start >= 10000000000 then to_char(e_rows_times_start/1000000000, 'FM99999') || 'G'
          when e_rows_times_start >= 10000000 then to_char(e_rows_times_start/1000000, 'FM99999') || 'M'
          when e_rows_times_start >= 100000 then to_char(e_rows_times_start/1000, 'FM99999') || 'K'
          else to_char(e_rows_times_start, 'FM99999') || ' '
          end                                                                                                       as e_rows_times_start_format
        , rpad(' ', nvl(round(elapsed_self / nullif(total_elapsed, 0) * 12), 0) + 1, '@')                           as elapsed_self_graph
        , rpad(' ', nvl(round(lio_self / nullif(total_lio, 0) * 12), 0) + 1, '@')                                   as lio_self_graph
        , rpad(' ', nvl(round(reads_self / nullif(total_reads, 0) * 12), 0) + 1, '@')                               as reads_self_graph
        , rpad(' ', nvl(round(writes_self / nullif(total_writes, 0) * 12), 0) + 1, '@')                             as writes_self_graph
        , ' ' ||
          case
          when tcf_ratio > 0
          then rpad('-', tcf_ratio, '-')
          else rpad('+', tcf_ratio * -1, '+')
          end                                                                                                       as tcf_graph
  from
          derived_stats
),
/* The final formatted data */
formatted_data as
(
  select
          /*+ Convert the INTERVAL representation to the A-TIME representation used by DBMS_XPLAN
              by turning the days into hours */
          to_char(to_number(substr(e_time_interval, 2, 9)) * 24 + to_number(substr(e_time_interval, 12, 2)), 'FM900') ||
          substr(e_time_interval, 14, 9)
          as a_time_self
        , a.*
  from
          formatted_data1 a
),
/* Combine the information with the original DBMS_XPLAN output */
xplan_data as (
  select
          x.plan_table_output
        , o.id
        , o.pid
        , o.oid
        , o.maxid
        , o.minid
        , a.a_time_self
        , a.lio_self_format
        , a.reads_self_format
        , a.writes_self_format
        , a.elapsed_self_graph
        , a.lio_self_graph
        , a.reads_self_graph
        , a.writes_self_graph
        , a.lio_ratio
        , a.tcf_graph
        , a.total_elapsed
        , a.total_lio
        , a.total_reads
        , a.total_writes
        , a.e_rows_times_start_format
        , x.rn
  from
          (
            select  /* Take advantage of 11g table function dynamic sampling */
                    /*+ dynamic_sampling(dc, 2) */
                    /* This ROWNUM determines the order of the output/processing */
                    rownum as rn
                  , plan_table_output
            from
                    table(dbms_xplan.display_cursor('&si',&cn, '&fo')) dc
          ) x
        , ordered_hierarchy_data o
        , formatted_data a
  where
          o.id (+) = case
                     when regexp_like(x.plan_table_output, '^\|[\* 0-9]+\|')
                     then to_number(regexp_substr(x.plan_table_output, '[0-9]+'))
                     end
  and     a.id (+) = case
                     when regexp_like(x.plan_table_output, '^\|[\* 0-9]+\|')
                     then to_number(regexp_substr(x.plan_table_output, '[0-9]+'))
                     end
)
/* Inject the additional data into the original DBMS_XPLAN output
   by using the MODEL clause */
select
        plan_table_output
from
        xplan_data
model
        dimension by (rn as r)
        measures
        (
          cast(plan_table_output as varchar2(4000)) as plan_table_output
        , id
        , maxid
        , minid
        , pid
        , oid
        , a_time_self
        , lio_self_format
        , reads_self_format
        , writes_self_format
        , e_rows_times_start_format
        , elapsed_self_graph
        , lio_self_graph
        , reads_self_graph
        , writes_self_graph
        , lio_ratio
        , tcf_graph
        , total_elapsed
        , total_lio
        , total_reads
        , total_writes
        , greatest(max(length(maxid)) over () + 3, 6) as csize
        , cast(null as varchar2(128)) as inject
        , cast(null as varchar2(4000)) as inject2
        )
        rules sequential order
        (
          /* Prepare the injection of the OID / PID info */
          inject[r]  = case
                               /* MINID/MAXID are the same for all rows
                                  so it doesn't really matter
                                  which offset we refer to */
                       when    id[cv(r)+1] = minid[cv(r)+1]
                            or id[cv(r)+3] = minid[cv(r)+3]
                            or id[cv(r)-1] = maxid[cv(r)-1]
                       then rpad('-', csize[cv()]*2, '-')
                       when id[cv(r)+2] = minid[cv(r)+2]
                       then '|' || lpad('Pid |', csize[cv()]) || lpad('Ord |', csize[cv()])
                       when id[cv()] is not null
                       then '|' || lpad(pid[cv()] || ' |', csize[cv()]) || lpad(oid[cv()] || ' |', csize[cv()])
                       end
          /* Prepare the injection of the remaining info */
        , inject2[r] = case
                       when    id[cv(r)+1] = minid[cv(r)+1]
                            or id[cv(r)+3] = minid[cv(r)+3]
                            or id[cv(r)-1] = maxid[cv(r)-1]
                       then rpad('-',
                            case when coalesce(total_elapsed[cv(r)+1], total_elapsed[cv(r)+3], total_elapsed[cv(r)-1]) > 0 then
                            14 else 0 end /* A_TIME_SELF */       +
                            case when coalesce(total_lio[cv(r)+1], total_lio[cv(r)+3], total_lio[cv(r)-1]) > 0 then
                            11 else 0 end /* LIO_SELF */          +
                            case when coalesce(total_reads[cv(r)+1], total_reads[cv(r)+3], total_reads[cv(r)-1]) > 0 then
                            11 else 0 end /* READS_SELF */        +
                            case when coalesce(total_writes[cv(r)+1], total_writes[cv(r)+3], total_writes[cv(r)-1]) > 0 then
                            11 else 0 end /* WRITES_SELF */       +
                            case when coalesce(total_elapsed[cv(r)+1], total_elapsed[cv(r)+3], total_elapsed[cv(r)-1]) > 0 then
                            14 else 0 end /* A_TIME_SELF_GRAPH */ +
                            case when coalesce(total_lio[cv(r)+1], total_lio[cv(r)+3], total_lio[cv(r)-1]) > 0 then
                            14 else 0 end /* LIO_SELF_GRAPH */    +
                            case when coalesce(total_reads[cv(r)+1], total_reads[cv(r)+3], total_reads[cv(r)-1]) > 0 then
                            14 else 0 end /* READS_SELF_GRAPH */  +
                            case when coalesce(total_writes[cv(r)+1], total_writes[cv(r)+3], total_writes[cv(r)-1]) > 0 then
                            14 else 0 end /* WRITES_SELF_GRAPH */ +
                            case when coalesce(total_lio[cv(r)+1], total_lio[cv(r)+3], total_lio[cv(r)-1]) > 0 then
                            11 else 0 end /* LIO_RATIO */         +
                            case when coalesce(total_elapsed[cv(r)+1], total_elapsed[cv(r)+3], total_elapsed[cv(r)-1]) > 0 then
                            11 else 0 end /* TCF_GRAPH */         +
                            case when coalesce(total_elapsed[cv(r)+1], total_elapsed[cv(r)+3], total_elapsed[cv(r)-1]) > 0 then
                            11 else 0 end /* E_ROWS_TIMES_START */
                            , '-')
                       when id[cv(r)+2] = minid[cv(r)+2]
                       then case when total_elapsed[cv(r)+2] > 0 then
                            lpad('A-Time Self |' , 14) end ||
                            case when total_lio[cv(r)+2] > 0 then
                            lpad('Bufs Self |'   , 11) end ||
                            case when total_reads[cv(r)+2] > 0 then
                            lpad('Reads Self|'   , 11) end ||
                            case when total_writes[cv(r)+2] > 0 then
                            lpad('Write Self|'   , 11) end ||
                            case when total_elapsed[cv(r)+2] > 0 then
                            lpad('A-Ti S-Graph |', 14) end ||
                            case when total_lio[cv(r)+2] > 0 then
                            lpad('Bufs S-Graph |', 14) end ||
                            case when total_reads[cv(r)+2] > 0 then
                            lpad('Reads S-Graph|', 14) end ||
                            case when total_writes[cv(r)+2] > 0 then
                            lpad('Write S-Graph|', 14) end ||
                            case when total_lio[cv(r)+2] > 0 then
                            lpad('LIO Ratio |'   , 11) end ||
                            case when total_elapsed[cv(r)+2] > 0 then
                            lpad('TCF Graph |'   , 11) end ||
                            case when total_elapsed[cv(r)+2] > 0 then
                            lpad('E-Rows*Sta|'   , 11) end
                       when id[cv()] is not null
                       then case when total_elapsed[cv()] > 0 then
                            lpad(a_time_self[cv()]               || ' |', 14) end ||
                            case when total_lio[cv()] > 0 then
                            lpad(lio_self_format[cv()]           ||  '|', 11) end ||
                            case when total_reads[cv()] > 0 then
                            lpad(reads_self_format[cv()]         ||  '|', 11) end ||
                            case when total_writes[cv()] > 0 then
                            lpad(writes_self_format[cv()]        ||  '|', 11) end ||
                            case when total_elapsed[cv()] > 0 then
                            rpad(elapsed_self_graph[cv()], 13)   ||  '|'      end ||
                            case when total_lio[cv()] > 0 then
                            rpad(lio_self_graph[cv()], 13)       ||  '|'      end ||
                            case when total_reads[cv()] > 0 then
                            rpad(reads_self_graph[cv()], 13)     ||  '|'      end ||
                            case when total_writes[cv()] > 0 then
                            rpad(writes_self_graph[cv()], 13)    ||  '|'      end ||
                            case when total_lio[cv()] > 0 then
                            lpad(lio_ratio[cv()]                 || ' |', 11) end ||
                            case when total_elapsed[cv()] > 0 then
                            rpad(tcf_graph[cv()], 9)             || ' |'      end ||
                            case when total_elapsed[cv()] > 0 then
                            lpad(e_rows_times_start_format[cv()] ||  '|', 11) end
                       end
          /* Putting it all together */
        , plan_table_output[r] = case
                                 when inject[cv()] like '---%'
                                 then inject[cv()] || plan_table_output[cv()] || inject2[cv()]
                                 when inject[cv()] is present
                                 then regexp_replace(plan_table_output[cv()], '\|', inject[cv()], 1, 2) || inject2[cv()]
                                 else plan_table_output[cv()]
                                 end
        )
order by
        r
;

undefine default_fo
undefine prev_sql_id
undefine prev_cn
undefine last
undefine si
undefine cn
undefine fo
undefine 1
undefine 2
undefine 3

col plan_table_output clear
col prev_sql_id clear
col prev_child_number clear
col si clear
col cn clear
col fo clear
col last clear

EXAMPLE:

[oracle@testdb2 ~]$ sqlplus '/as sysdba'
 
SQL*Plus: Release 10.2.0.5.0 - Production on Mon Jan 9 12:58:57 2012
 
Copyright (c) 1982, 2010, OracleAll Rights Reserved.
 
 
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL> Alter session set sql_trace=true;
Alter session set STATISTICS_LEVEL = ALL;
Session altered.
 
SQL>
 
Session altered.
 
SQL>
SQL> Alter session set sql_trace=true;
 
Session altered.
 
SQL> select count (*) from dba_objects;
 
 
COUNT(*)
--------
--
     31094

 
SQL> @ xplan_extended_display_cursor 2jf3kh52jy9fd 0
SQL_ID  2jf3kh52jy9fd, child number 0
-----------------------------------
--
select count (*) from dba_objects

 
Plan hash value: 2476103605
 
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--
| Id  | Pid | Ord | Operation                       | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem | A-Time Self |Bufs Self |A-Ti S-Graph |Bufs S-Graph |LIO Ratio |TCF Graph |E-Rows*Sta|

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--
|   0 |     |  13 | SELECT STATEMENT                |             |      1 |        |      1 |00:00:00.77 |   12166 |       |       |          | 00:00:00.00 |        0 |             |             |        0 |          |          |

|   
1 |   012SORT AGGREGATE                 |             |      1 |      1 |      1 |00:00:00.77 |   12166 |       |       |          | 00:00:00.02 |        0 |             |             |        0 |          |        1 |
|   
2 |   111 |   VIEW                          | DBA_OBJECTS |      12593131094 |00:00:00.75 |   12166 |       |       |          | 00:00:00.09 |        0 | @           |             |        0 |          |    25931 |
|   
3 |   210 |    UNION-ALL                    |             |      1 |        |  31094 |00:00:00.66 |   12166 |       |       |          | 00:00:00.12 |        0 | @@          |             |        0 |          |          |
|* 
4 |   3 |   6 |     FILTER                      |             |      1 |        |  31078 |00:00:00.53 |   12159 |       |       |          | 00:00:00.08 |        0 | @           |             |        0 |          |          |
|* 
5 |   4 |   3 |      HASH JOIN                  |             |      13225532291 |00:00:00.36 |     4431593K1593K| 1477K (0)| 00:00:00.25 |        0 | @@@@        |             |        0 |          |    32255 |
|   
6 |   5 |   1 |       TABLE ACCESS FULL         | USER$       |      1 |     65 |     66 |00:00:00.01 |       6 |       |       |          | 00:00:00.00 |        6 |             |             |        0 |          |       65 |
|* 
7 |   5 |   2 |       TABLE ACCESS FULL         | OBJ$        |      13225532291 |00:00:00.11 |     437 |       |       |          | 00:00:00.11 |      437 | @@          |             |        0 |          |    32255 |
|* 
8 |   4 |   5 |      TABLE ACCESS BY INDEX ROWID| IND$        |   5855 |      1 |   5184 |00:00:00.10 |   11716 |       |       |          | 00:00:00.06 |     5859 | @           | @@@@@@      |        1 |          |     5855 |
|* 
9 |   8 |   4 |       INDEX UNIQUE SCAN         | I_IND1      |   5855 |      1 |   5855 |00:00:00.04 |    5857 |       |       |          | 00:00:00.04 |     5857 | @           | @@@@@@      |        1 |          |     5855 |
|*
10 |   3 |   9 |     HASH JOIN                   |             |      1 |     16 |     16 |00:00:00.01 |       71517K1517K606K (0)| 00:00:00.00 |        0 |             |             |        0 |          |       16 |
1110 |   7 |      INDEX FULL SCAN            | I_LINK1     |      1 |     16 |     16 |00:00:00.01 |       1 |       |       |          | 00:00:00.00 |        1 |             |             |        0 |          |       16 |
1210 |   8 |      TABLE ACCESS FULL          | USER$       |      1 |     65 |     66 |00:00:00.01 |       6 |       |       |          | 00:00:00.00 |        6 |             |             |        0 |          |       65 |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--
 

Predicate Information (identified by operation id):
-------------------------------------------------
--
 

  
4 - filter((("O"."TYPE#"<>1 AND "O"."TYPE#"<>10) OR ("O"."TYPE#"=1 AND =1)))
  
5 - access("O"."OWNER#"="U"."USER#")
  
7 - filter(("O"."LINKNAME" IS NULL AND BITAND("O"."FLAGS",128)=0 AND "O"."NAME"<>'_NEXT_OBJECT' AND
              
"O"."NAME"<>'_default_auditing_options_'))
  
8 - filter(("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR
              
"I"."TYPE#"=9))
  
9 - access("I"."OBJ#"=:B1)
 
10 - access("L"."OWNER#"="U"."USER#")
 
 
36 rows selected.

Tags:
Posted in Oracle Experience | No Comments »