Mike Ault v$ data dictionary Scripts
Caution - These are extremely powerful Oracle data dictionary scripts. They
should only be used by a certified Oracle DBA, and after a careful review of the
Oracle data dictionary script functionality. These scripts are NOT supported and
knowledge of the data dictionary is required to fully utilize these scripts. USE
AT YOUR OWN RISK.
rem NAME: blockers.sql
rem FUNCTION: Show all processes causing a dead lock
rem HISTORY: MRA 1/15/96 Created
rem
COLUMN username FORMAT a10 HEADING 'Holding|User'
COLUMN session_id HEADING 'SID'
COLUMN mode_held FORMAT a20 HEADING 'Mode|Held'
COLUMN mode_requested FORMAT a20 HEADING 'Mode|Requested'
COLUMN lock_id1 FORMAT a20 HEADING 'Lock|ID1'
COLUMN lock_id2 FORMAT a20 HEADING 'Lock|ID2'
COLUMN type HEADING 'Lock|Type'
COLUMN program FORMAT a15 HEADING 'Executing|Program'
SET LINES 132 PAGES 59 FEEDBACK OFF ECHO OFF
START title132 'Sessions Blocking Other Sessions Report'
SPOOL rep_out/&db/blockers
SELECT
a.session_id,
username,
type,
mode_held,
mode_requested,
lock_id1,
lock_id2
FROM
sys.v_$session b,
sys.dba_blockers c,
sys.dba_lock a
WHERE
c.holding_session=a.session_id and
c.holding_session=b.sid
/
SPOOL OFF
PAUSE press enter/return to continue
CLEAR COLUMNS
SET LINES 80 PAGES 22 FEEDBACK ON
rem NAME: blockers.sql
rem FUNCTION: Show all processes causing a dead lock
rem HISTORY: MRA 1/15/96 Created
rem
COLUMN username FORMAT a10 HEADING 'Holding|User'
COLUMN session_id HEADING 'SID'
COLUMN mode_held FORMAT a20 HEADING 'Mode|Held'
COLUMN mode_requested FORMAT a20 HEADING 'Mode|Requested'
COLUMN lock_id1 FORMAT a20 HEADING 'Lock|ID1'
COLUMN lock_id2 FORMAT a20 HEADING 'Lock|ID2'
COLUMN type HEADING 'Lock|Type'
COLUMN program FORMAT a15 HEADING 'Executing|Program'
SET LINES 132 PAGES 59 FEEDBACK OFF ECHO OFF
ttitle 'Sessions Blocking Other Sessions Report'
SPOOL blockers
SELECT
a.session_id,
username,
type,
mode_held,
mode_requested,
lock_id1,
lock_id2
FROM
sys.v_$session b,
sys.dba_blockers c,
sys.dba_lock a
WHERE
c.holding_session=a.session_id and
c.holding_session=b.sid
/
SPOOL OFF
PAUSE press enter/return to continue
CLEAR COLUMNS
SET LINES 80 PAGES 22 FEEDBACK ON
-- Author: Dick Hubeny
-- Date: July 14, 1999
-- Purpose: To display what is currently in buffer cache
-- Note: Must run catparr.sql script from svrmgrl first
set linesize 100
column owner format a10 heading "Owner"
column segment_name format a30 heading "Object Name"
column segment_type format a8 heading "Type"
column tablespace_name format a15 heading "Tablespace"
column block_count format 99,999 heading "# Blocks" justify right
column bpercent format 99D99 heading "Percent" justify right
set lines 78 pages 47
start title80 'DB Buffer Contents'
spool rep_out/&&db/db_buffer
prompt Buffer Cache Map for Objects Not Owned by SYS or SYSTEM
accept powner char Prompt 'Enter Schema Owner (% for all): '
select owner, segment_name, segment_type,
tablespace_name, count(1) block_count,
round((count(1)*100)/value,2) bpercent
from v$bh, dba_extents, v$parameter
where file# = file_id
and block# between block_id and block_id + blocks - 1
and status <> 'FREE'
and name = 'db_block_buffers'
and owner not in ('SYS','SYSTEM') and owner like UPPER('%&powner%')
group by owner, segment_name, segment_type,
tablespace_name, value
union
select 'None', 'Free Blocks', 'Free', 'None',
count(1) block_count,
round((count(1)*100)/value,2) bpercent
from v$bh, v$parameter
where status = 'FREE'
and name = 'db_block_buffers'
group by value
;
spool off
undef buffer
undef powner
Set echo off
rem File: BUFFER_USAGE.SQL
rem Date: April 17, 2000
rem Created By: Steven P. Karniotis / Compuware Corporation
rem Description:
rem Creates a report showing database block buffer usage by category.
rem
rem
rem
rem Things to review:
rem
rem **************************************************************************
rem Modifications:
rem
rem
rem
rem
rem **************************************************************************
@@title80 'Buffer Usage'
set newpage 1
column buffer_state format a30 heading "State of|Database Buffer" -
justify center
column buffer_count format 999,990 heading "Buffer|Count" justify center
compute sum of buffer_count on report
break on report
ttitle left _date center 'Performance Management Report' skip 1 center -
'for Database ' format a8 &&db right -
'Page: ' format 999 sql.pno skip 1 -
center 'Database Block Buffer Usage' skip 2
select decode(state,0, 'Free', 1, 'Read and Modified',
2,'Read and Not Modified', 3, 'Currently Being Read',
'Other') buffer_state,
count(*) buffer_count
from x$bh
group by decode(state,0, 'Free', 1, 'Read and Modified',
2,'Read and Not Modified', 3, 'Currently Being Read',
'Other');
ttitle off
clear breaks
clear columns
clear computes
@test_pool
@sql_garbage
ttitle off
@reuse_sql2
@pool_rat
create or replace procedure check_corrupt(schema_name in varchar2) is
cursor c is
select OWNER,
object_NAME,
DECODE(object_type,
'TABLE',1,
'INDEX',2,
'CLUSTER',4,object_type) object_type
from dba_objects
where owner = schema_name
and object_type in ('TABLE','CLUSTER','INDEX') ;
v_corrupt_count BINARY_INTEGER;
object_name varchar2(64);
owner_name varchar2(64);
object_type integer;
begin
DBMS_REPAIR.ADMIN_TABLES('REPAIR_TABLE',1,3);
DBMS_REPAIR.ADMIN_TABLES('REPAIR_TABLE',1,1);
DBMS_REPAIR.ADMIN_TABLES('REPAIR_TABLE',1,2);
FOR C_REC IN C LOOP
object_name:=c_rec.object_name;
owner_name:=c_rec.owner;
object_type:=c_rec.object_type;
DBMS_OUTPUT.PUT_LINE(owner_name||'.'||object_name);
DBMS_REPAIR.CHECK_OBJECT(SCHEMA_NAME=>OWNER_NAME,
OBJECT_NAME=>OBJECT_NAME,OBJECT_TYPE=>object_type,CORRUPT_COUNT=>v_corrupt_count);
DBMS_OUTPUT.PUT_LINE(owner_name||'.'||object_name||
' Corrupt Count='||to_char(v_corrupt_count));
END LOOP;
END check_corrupt;
/
connect &1
column xdb_name new_value xdb noprint
select name xdb_name from v$database;
set sqlprompt '&xdb:'
REM FUNCTION: SCRIPT FOR DOCUMENTING DATABASE CONSTRAINTS
REM
REM FUNCTION: This script must be run by the constraint owner.
REM
REM FUNCTION: This script is intended to run with Oracle or Oracle8.
REM
REM FUNCTION: Running this script will document the
REM FUNCTION: primary key - foreign key
REM FUNCTION: constraints in the database
REM
REM
REM Only preliminary testing of this script was performed.
REM Be sure to test
REM it completely before relying on it.
REM
REM MRA 6/14/97 Verified for Oracle8
REM
SET ARRAYSIZE 1 VERIFY OFF FEEDBACK OFF TERMOUT OFF ECHO OFF PAGESIZE 0
SET LONG 4000
SET TERMOUT ON
SELECT 'Creating constraint documentation script...' FROM dual;
SET TERMOUT OFF
CREATE TABLE cons_temp (owner VARCHAR2(30),
constraint_name VARCHAR2(30),
constraint_type VARCHAR2(11),
search_condition VARCHAR2(2000),
table_name VARCHAR2(30),
referenced_owner VARCHAR2(30),
referenced_constraint VARCHAR2(30),
delete_rule VARCHAR2(9),
constraint_columns VARCHAR2(2000),
con_number NUMBER);
TRUNCATE TABLE cons_temp;
DECLARE
CURSOR cons_cursor IS
SELECT
owner,
constraint_name,
DECODE(constraint_type,'P','Primary Key',
'R','Foreign Key',
'U','Unique',
'C','Check',
'D','Default'),
search_condition,
table_name,
r_owner,
r_constraint_name,
delete_rule
FROM
dba_constraints
WHERE
owner NOT IN ('SYS','SYSTEM')
ORDER BY
owner;
CURSOR cons_col (cons_name in VARCHAR2) IS
SELECT
owner,
constraint_name,
column_name
FROM
dba_cons_columns
WHERE
owner NOT IN ('SYS','SYSTEM') AND
constraint_name = UPPER(cons_name)
ORDER BY
owner,
constraint_name,
position;
CURSOR get_cons (tab_nam in VARCHAR2) IS
SELECT DISTINCT
owner,
table_name,
constraint_name,
constraint_type
FROM
cons_temp
WHERE
table_name=tab_nam
AND constraint_type='Foreign Key'
ORDER BY
owner,
table_name,
constraint_name;
CURSOR get_tab_nam is
SELECT
DISTINCT table_name
FROM
cons_temp
WHERE
constraint_type='Foreign Key'
ORDER BY
table_name;
tab_nam user_constraints.table_name%TYPE;
cons_owner user_constraints.owner%TYPE;
cons_name user_constraints.constraint_name%TYPE;
cons_type VARCHAR2(11);
cons_sc user_constraints.search_condition%TYPE;
cons_tname user_constraints.table_name%TYPE;
cons_rowner user_constraints.r_owner%TYPE;
cons_rcons user_constraints.r_constraint_name%TYPE;
cons_dr user_constraints.delete_rule%TYPE;
cons_col_own user_cons_columns.owner%TYPE;
cons_col_nam user_cons_columns.constraint_name%TYPE;
cons_column user_cons_columns.column_name%TYPE;
cons_tcol_name user_cons_columns.table_name%TYPE;
all_columns VARCHAR2(2000);
counter INTEGER:=0;
cons_nbr INTEGER;
BEGIN
OPEN cons_cursor;
LOOP
FETCH cons_cursor INTO cons_owner,
cons_name,
cons_type,
cons_sc,
cons_tname,
cons_rowner,
cons_rcons,
cons_dr;
EXIT WHEN cons_cursor%NOTFOUND;
all_columns :='';
counter := 0;
OPEN cons_col (cons_name);
LOOP
FETCH cons_col INTO
cons_col_own,
cons_col_nam,
cons_column;
EXIT WHEN cons_col%NOTFOUND;
IF cons_owner = cons_col_own AND cons_name=cons_col_nam
THEN
counter := counter+1;
IF counter = 1 THEN
all_columns := all_columns||cons_column;
ELSE
all_columns := all_columns||', '||cons_column;
END IF;
END IF;
END LOOP;
CLOSE cons_col;
INSERT INTO cons_temp VALUES (cons_owner,
cons_name,
cons_type,
cons_sc,
cons_tname,
cons_rowner,
cons_rcons,
cons_dr,
all_columns,
0);
COMMIT;
END LOOP;
CLOSE cons_cursor;
COMMIT;
BEGIN
OPEN get_tab_nam;
LOOP
FETCH get_tab_nam INTO tab_nam;
EXIT WHEN get_tab_nam%NOTFOUND;
/*sys.dbms_output.put_line(tab_nam);*/
OPEN get_cons (tab_nam);
cons_nbr:=0;
LOOP
FETCH get_cons INTO cons_owner,
cons_tname,
cons_name,
cons_type;
EXIT WHEN get_cons%NOTFOUND;
cons_nbr:=cons_nbr+1;
/* sys.dbms_output.put_line('cons_nbr='||cons_nbr);*/
/*sys.dbms_output.put_line(cons_owner||'.'||cons_name||' '||cons_type);*/
UPDATE cons_temp SET con_number=cons_nbr
WHERE
constraint_name=cons_name AND
constraint_type=cons_type AND
owner=cons_owner;
END LOOP;
CLOSE get_cons;
COMMIT;
END LOOP;
CLOSE get_tab_nam;
COMMIT;
END;
END;
/
CREATE INDEX pk_cons_temp ON cons_temp(constraint_name);
CREATE INDEX lk_cons_temp2 ON cons_temp(referenced_constraint);
SET FEEDBACK OFF TERMOUT OFF ECHO OFF
SET VERIFY OFF
SET PAGES 48 LINES 132
COLUMN pri_own FORMAT a10 HEADING 'Pri Table|Owner'
COLUMN for_own FORMAT a10 HEADING 'For Table|Owner'
COLUMN pri_tab FORMAT a25 HEADING 'Pri Table|Name'
COLUMN for_tab FORMAT a25 HEADING 'For Table|Name'
COLUMN pri_col FORMAT a25 HEADING 'Pri Key|COLUMNs' word_wrapped
COLUMN for_col FORMAT a25 HEADING 'For Key|COLUMNs' word_wrapped
START title132 'Primary Key - Foreign Key Report'
SPOOL rep_out/&db/pk_fk
BREAK ON pri_own ON pri_tab ON for_own ON for_tab
SELECT
b.owner pri_own,
b.table_name pri_tab,
RTRIM(b.constraint_columns) pri_col,
a.owner for_own,
a.table_name for_tab,
RTRIM(a.constraint_columns) for_col
FROM
cons_temp a,
cons_temp b
WHERE
a.referenced_constraint=b.constraint_name
ORDER BY
b.owner,b.table_name,a.owner,a.table_name;
SPOOL OFF
DROP TABLE cons_temp;
SET VERIFY ON FEEDBACK ON TERMOUT ON PAGESIZE 22 LINES 80
CLEAR COLUMNS
CLEAR BREAKS
TTITLE OFF
set pages 0 verify off feedback off
column table_name format a20
column constraint_name format a25
column type format a15
column delete_rule format a15
column column_name format a15
start title80 "Constraint Report"
spool rep_out/&&db/cons_rep.lis
select a.table_name,a.constraint_name,decode (constraint_type,'P', 'Primary',
'R', 'Foreign Key') type, delete_rule, column_name from
user_constraints a, user_cons_columns b where
constraint_type in ('P','R') and
a.owner=b.owner and
a.constraint_name=b.constraint_name
order by table_name,constraint_type asc
/
spool off
REM
REM CONTEND.SQL
REM FUNCTION: SHOWS WHERE POSSIBLE CONTENTION FOR RESOURCES
REM IN BUFFER BUSY WAITS USE TO PINPOINT ADDITIONAL
REM TUNING AREAS.
REM
REM CALLED FROM STATUS
REM
SET VERIFY OFF FEEDBACK OFF
SET PAGES 58
SET LINES 79
START title80 "AREA OF CONTENTION REPORT"
DEFINE OUTPUT = 'rep_out/&db/contend'
SPOOL &OUTPUT
SELECT CLASS,SUM(COUNT) TOTAL_WAITS, sum(time) TOTAL_TIME
FROM V$WAITSTAT
group by class;
SPOOL OFF
PAUSE PRESS RETURN TO CONTINUE
set verify on feedback on pages 22 lines 80
ttitle off
break on classselect class, name, value from v$sysstatwhere value <> 0order by
class,value
column value format 999.9999
column name format a25
column meas_date format a16
set pages 0 lines 80
start title80 'Cumulative Hit Ratio'
spool rep_out/&db/cum_hrat
select
name, value, to_char(meas_date,'dd-mon-yy hh24:mi') meas_date
from dba_running_stats where name ='CUMMULATIVE HIT RATIO'
order by meas_date
/
spool off
rem
rem FUNCTION: Create list of all database rollback segments
rem FUNCTION: and their storage defaults
rem
column rollback_seg format a8 heading 'Rollback|Segment'
column tablespace format a10 heading 'Rbks|Tablespace'
column pct format 9990 heading 'Pct|Inc'
column init format 99,999,999 heading 'Init|Ext'
column next format 99,999,999 heading 'Next|Ext'
column min format 999 heading 'Min|Ext'
column max format 9,999 heading 'Max|Ext'
column instance format a3 heading 'Int|Num'
column type heading 'Rbks|Type'
column status heading 'Rbks|Status'
set pages 59 lines 132 feedback off echo off
start title132 'Rollback Storage Parameter Report'
spool rep_out/&db/db_rbks
select segment_name rollback_seg,tablespace_name tablespace,
initial_extent init,
next_extent next, min_extents min, max_extents max,
pct_increase pct, status, instance_num Instance,
decode(owner,'SYS','PRIVATE') type
from dba_rollback_segs;
spool off
pause press enter/return to continue
clear columns
set pages 22 lines 80 feedback on echo on
ttitle off
rem*******************************************************************
rem NAME: db_tgnts.sql
rem
rem FUNCTION: Produce report of table or procedure grants showing
rem GRANTOR, GRANTEE or ROLE and specific GRANTS.
rem
rem INPUTS: Owner name
rem ********************************************************************
rem
COLUMN grantee FORMAT A18 HEADING "Grantee|or Role"
COLUMN owner FORMAT A18 HEADING "Owner"
COLUMN table_name FORMAT A30 HEADING "Table|or Proc"
COLUMN grantor FORMAT A18 HEADING "Grantor"
COLUMN privilege FORMAT A10 HEADING "Privilege"
COLUMN grantable FORMAT A19 HEADING "Grant|Option?"
rem
BREAK ON owner SKIP 4 ON table_name SKIP 1 ON grantee ON grantor ON REPORT
rem
SET LINESIZE 130 PAGES 56 VERIFY OFF FEEDBACK OFF
START title132 "TABLE GRANTS BY OWNER AND TABLE"
DEFINE OUTPUT = rep_out/&&db/db_tgnts
SPOOL &output
REM
SELECT
owner,
table_name,
grantee,
grantor,
privilege,
grantable
FROM
dba_tab_privs
WHERE
owner NOT IN ('SYS','SYSTEM')
ORDER BY
owner,
table_name,
grantor,
grantee;
REM
SPOOL OFF
PAUSE Press enter to continue
rem
rem FUNCTION: Report on database table or procedure grants
rem
column table_name format a16 heading 'Table or|Procedure'
column grantee format a16 heading 'Role or|User'
column privilege format a10 heading 'Granted|Privilege'
column object_type heading 'Type of|Object'
accept user prompt 'Enter user name: '
set lines 80 feedback off verify off
@title80 'Table and Procedure Grants by User/Role'
break on object_type on grantee on table_name
spool rep_out/&db/tgrnt
select grantee,table_name,privilege,object_type
from dba_tab_privs a, dba_objects b
where grantee like upper('%&user%')
and a.owner=b.owner
and a.table_name=b.object_name
order by 4,1,2
/
spool off
set feedback on verify on
clear columns
clear breaks
ttitle off
column value format 999.9999
column name format a30
column meas_date format a16
set pages 0 lines 80
start title80 'Data Dictionary Miss Percent'
spool rep_out/&db/dd_miss
select
name, value, to_char(meas_date,'dd-mon-yy hh24:mi') meas_date
from dba_running_stats where name ='Data Dictionary Miss Percent'
order by meas_date
/
spool off
set echo off
rem
rem title: ddcache.sql
rem FUNCTION: report on the v$rowcache table
rem HISTORY: created sept 1995 MRA
rem
start title80 "DD Cache Hit Ratio"
spool rep_out/&db/ddcache
SELECT (SUM(getmisses)/SUM(gets))*100 Miss_percent
FROM V$ROWCACHE
/
spool off
pause Press enter to continue
ttitle off
rem
rem Function: Document DDL Locks currently in use
rem
column owner format a15 heading 'User'
column session_id heading 'SID'
column mode_held format a20 heading 'Lock Mode|Held'
column mode_requested format a20 heading 'Lock Mode|Requested'
column type heading 'Type|Object'
column name heading 'Object|Name'
set feedback off echo off pages 59 lines 131
start title132 'Report on All DDL Locks Held'
spool rep_out/&db/ddl_lock
select nvl(owner,'SYS') owner,session_id,name,type,
mode_held,mode_requested
from sys.dba_ddl_locks
order by 2
/
spool off
pause press enter/return to continue
clear columns
set feedback on pages 22 lines 80
ttitle off
rem
rem Function: Document DDL Locks currently in use
rem
column owner format a15 heading 'User'
column session_id heading 'SID'
column mode_held format a20 heading 'Lock Mode|Held'
column mode_requested format a20 heading 'Lock Mode|Requested'
column type heading 'Type|Object'
column name heading 'Object|Name'
set feedback off echo off pages 59 lines 131
ttitle 'Report on All DDL Locks Held'
spool rep_out/&db/ddl_lock
select nvl(owner,'SYS') owner,session_id,name,type,
mode_held,mode_requested
from sys.dba_ddl_locks
order by 2
/
spool off
pause press enter/return to continue
clear columns
set feedback on echo on pages 22 lines 80
ttitle off
SET ECHO off
REM NAME: TFSCLOCK.SQL
REM USAGE:"@path/tfsclock"
REM ------------------------------------------------------------------------
REM REQUIREMENTS:
REM SELECT on V$LOCK, V$SESSION, SYS.USER$, SYS.OBJ$
REM ------------------------------------------------------------------------
REM PURPOSE:
REM The following locking information script provides fully DECODED
REM information regarding the locks currently held in the database.
REM The report generated is fairly complex and difficult to read,
REM but has considerable detail.
REM
REM The TFTS series contains scripts to provide (less detailed) lock
REM information in a formats which are somewhat less difficult to read:
REM TFSMLOCK.SQL and TFSLLOCK.SQL.
REM ------------------------------------------------------------------------
REM EXAMPLE:
REM Too complex to show a representative sample here
REM
REM ------------------------------------------------------------------------
REM DISCLAIMER:
REM This script is provided for educational purposes only. It is NOT
REM supported by Oracle World Wide Technical Support.
REM The script has been tested and appears to work as intended.
REM You should always run new scripts on a test instance initially.
REM ------------------------------------------------------------------------
REM Main text of script follows:
set lines 200
set pagesize 66
break on Kill on sid on username on terminal
column Kill heading 'Kill String' format a13
column res heading 'Resource Type' format 999
column id1 format 9999990
column id2 format 9999990
column locking heading 'Lock Held/Lock Requested' format a40
column lmode heading 'Lock Held' format a20
column request heading 'Lock Requested' format a20
column serial# format 99999
column username format a10 heading "Username"
column terminal heading Term format a6
column tab format a30 heading "Table Name"
column owner format a9
column LAddr heading "ID1 - ID2" format a18
column Lockt heading "Lock Type" format a40
column command format a25
column sid format 990
rem L.SID||','||S.SERIAL# Kill,
select
nvl(S.USERNAME,'Internal') username,
L.SID,
nvl(S.TERMINAL,'None') terminal,
decode(command,
0,'None',decode(l.id2,0,U1.NAME||'.'||substr(T1.NAME,1,20),'None')) tab,
decode(command,
0,'BACKGROUND',
1,'Create Table',
2,'INSERT',
3,'SELECT',
4,'CREATE CLUSTER',
5,'ALTER CLUSTER',
6,'UPDATE',
7,'DELETE',
8,'DROP',
9,'CREATE INDEX',
10,'DROP INDEX',
11,'ALTER INDEX',
12,'DROP TABLE',
13,'---',
14,'---',
15,'ALTER TABLE',
16,'---',
17,'GRANT',
18,'REVOKE',
19,'CREATE SYNONYM',
20,'DROP SYNONYM',
21,'CREATE VIEW',
22,'DROP VIEW',
23,'---',
24,'---',
25,'---',
26,'LOCK TABLE',
27,'NO OPERATION',
28,'RENAME',
29,'COMMENT',
30,'AUDIT',
31,'NOAUDIT',
32,'CREATE EXTERNAL DATABASE',
33,'DROP EXTERNAL DATABASE',
34,'CREATE DATABASE',
35,'ALTER DATABASE',
36,'CREATE ROLLBACK SEGMENT',
37,'ALTER ROLLBACK SEGMENT',
38,'DROP ROLLBACK SEGMENT',
39,'CREATE TABLESPACE',
40,'ALTER TABLESPACE',
41,'DROP TABLESPACE',
42,'ALTER SESSION',
43,'ALTER USER',
44,'COMMIT',
45,'ROLLBACK',
46,'SAVEPOINT',
47,'PL/SQL EXECUTE',
48,'SET TRANSACTION',
49,'ALTER SYSTEM SWITCH LOG',
50,'EXPLAIN',
51,'CREATE USER',
52,'CREATE ROLE',
53,'DROP USER',
54,'DROP ROLE',
55,'SET ROLE',
56,'CREATE SCHEMA',
57,'CREATE CONTROL FILE',
58,'ALTER TRACING',
59,'CREATE TRIGGER',
60,'ALTER TRIGGER',
61,'DROP TRIGGER',
62,'ANALYZE TABLE',
63,'ANALYZE INDEX',
64,'ANALYZE CLUSTER',
65,'CREATE PROFILE',
66,'DROP PROFILE',
67,'ALTER PROFILE',
68,'DROP PROCEDURE',
70,'ALTER RESOURCE COST',
71,'CREATE SNAPSHOT LOG',
72,'ALTER SNAPSHOT LOG',
73,'DROP SNAPSHOT LOG',
74,'CREATE SNAPSHOT',
75,'ALTER SNAPSHOT',
76,'DROP SNAPSHOT',
84,'-',
85,'TRUNCATE TABLE',
86,'TRUNCATE CLUSTER',
87,'-',
88,'ALTER VIEW',
89,'-',
90,'-',
91,'CREATE FUNCTION',
92,'ALTER FUNCTION',
93,'DROP FUNCTION',
94,'CREATE PACKAGE',
95,'ALTER PACKAGE',
96,'DROP PACKAGE',
97,'CREATE PACKAGE BODY',
98,'ALTER PACKAGE BODY',
99,'DROP PACKAGE BODY',
command||' - ???') COMMAND,
decode(L.LMODE,1,'No Lock',
2,'Row Share',
3,'Row Exclusive',
4,'Share',
5,'Share Row Exclusive',
6,'Exclusive','NONE') lmode,
decode(L.REQUEST,1,'No Lock',
2,'Row Share',
3,'Row Exclusive',
4,'Share',
5,'Share Row Exclusive',
6,'Exclusive','NONE') request,
l.id1||'-'||l.id2 Laddr,
l.type||' - '||
decode(l.type,
'BL','Buffer hash table instance lock',
'CF','Cross-instance function invocation instance lock',
'CI','Control file schema global enqueue lock',
'CS','Control file schema global enqueue lock',
'DF','Data file instance lock',
'DM','Mount/startup db primary/secondary instance lock',
'DR','Distributed recovery process lock',
'DX','Distributed transaction entry lock',
'FI','SGA open-file information lock',
'FS','File set lock',
'IR','Instance recovery serialization global enqueue lock',
'IV','Library cache invalidation instance lock',
'MB','Master buffer hash table instance lock',
'MM','Mount definition gloabal enqueue lock',
'MR','Media recovery lock',
'RE','USE_ROW_ENQUEUE enforcement lock',
'RT','Redo thread global enqueue lock',
'RW','Row wait enqueue lock',
'SC','System commit number instance lock',
'SH','System commit number high water mark enqueue lock',
'SN','Sequence number instance lock',
'SQ','Sequence number enqueue lock',
'ST','Space transaction enqueue lock',
'SV','Sequence number value lock',
'TA','Generic enqueue lock',
'TD','DDL enqueue lock',
'TE','Extend-segment enqueue lock',
'TM','DML enqueue lock',
'TT','Temporary table enqueue lock',
'TX','Transaction enqueue lock',
'UL','User supplied lock',
'UN','User name lock',
'WL','Being-written redo log instance lock',
'WS','Write-atomic-log-switch global enqueue lock',
'TS',decode(l.id2,0,'Temporary segment enqueue lock (ID2=0)',
'New block allocation enqueue lock (ID2=1)'),
'LA','Library cache lock instance lock (A=namespace)',
'LB','Library cache lock instance lock (B=namespace)',
'LC','Library cache lock instance lock (C=namespace)',
'LD','Library cache lock instance lock (D=namespace)',
'LE','Library cache lock instance lock (E=namespace)',
'LF','Library cache lock instance lock (F=namespace)',
'LG','Library cache lock instance lock (G=namespace)',
'LH','Library cache lock instance lock (H=namespace)',
'LI','Library cache lock instance lock (I=namespace)',
'LJ','Library cache lock instance lock (J=namespace)',
'LK','Library cache lock instance lock (K=namespace)',
'LL','Library cache lock instance lock (L=namespace)',
'LM','Library cache lock instance lock (M=namespace)',
'LN','Library cache lock instance lock (N=namespace)',
'LO','Library cache lock instance lock (O=namespace)',
'LP','Library cache lock instance lock (P=namespace)',
'LS','Log start/log switch enqueue lock',
'PA','Library cache pin instance lock (A=namespace)',
'PB','Library cache pin instance lock (B=namespace)',
'PC','Library cache pin instance lock (C=namespace)',
'PD','Library cache pin instance lock (D=namespace)',
'PE','Library cache pin instance lock (E=namespace)',
'PF','Library cache pin instance lock (F=namespace)',
'PG','Library cache pin instance lock (G=namespace)',
'PH','Library cache pin instance lock (H=namespace)',
'PI','Library cache pin instance lock (I=namespace)',
'PJ','Library cache pin instance lock (J=namespace)',
'PL','Library cache pin instance lock (K=namespace)',
'PK','Library cache pin instance lock (L=namespace)',
'PM','Library cache pin instance lock (M=namespace)',
'PN','Library cache pin instance lock (N=namespace)',
'PO','Library cache pin instance lock (O=namespace)',
'PP','Library cache pin instance lock (P=namespace)',
'PQ','Library cache pin instance lock (Q=namespace)',
'PR','Library cache pin instance lock (R=namespace)',
'PS','Library cache pin instance lock (S=namespace)',
'PT','Library cache pin instance lock (T=namespace)',
'PU','Library cache pin instance lock (U=namespace)',
'PV','Library cache pin instance lock (V=namespace)',
'PW','Library cache pin instance lock (W=namespace)',
'PX','Library cache pin instance lock (X=namespace)',
'PY','Library cache pin instance lock (Y=namespace)',
'PZ','Library cache pin instance lock (Z=namespace)',
'QA','Row cache instance lock (A=cache)',
'QB','Row cache instance lock (B=cache)',
'QC','Row cache instance lock (C=cache)',
'QD','Row cache instance lock (D=cache)',
'QE','Row cache instance lock (E=cache)',
'QF','Row cache instance lock (F=cache)',
'QG','Row cache instance lock (G=cache)',
'QH','Row cache instance lock (H=cache)',
'QI','Row cache instance lock (I=cache)',
'QJ','Row cache instance lock (J=cache)',
'QL','Row cache instance lock (K=cache)',
'QK','Row cache instance lock (L=cache)',
'QM','Row cache instance lock (M=cache)',
'QN','Row cache instance lock (N=cache)',
'QO','Row cache instance lock (O=cache)',
'QP','Row cache instance lock (P=cache)',
'QQ','Row cache instance lock (Q=cache)',
'QR','Row cache instance lock (R=cache)',
'QS','Row cache instance lock (S=cache)',
'QT','Row cache instance lock (T=cache)',
'QU','Row cache instance lock (U=cache)',
'QV','Row cache instance lock (V=cache)',
'QW','Row cache instance lock (W=cache)',
'QX','Row cache instance lock (X=cache)',
'QY','Row cache instance lock (Y=cache)',
'QZ','Row cache instance lock (Z=cache)','????') Lockt
from V$LOCK L,
V$SESSION S,
SYS.USER$ U1,
SYS.OBJ$ T1
where L.SID = S.SID
and T1.OBJ# = decode(L.ID2,0,L.ID1,1)
and U1.USER# = T1.OWNER#
and S.TYPE != 'BACKGROUND'
order by 1,2,5
/
rem DL, Direct Loader Index Creation
rem IN, Instance Number
rem IS, Instance State
rem KK, Redo Log "Kick"
rem PF, Password File
rem PI, Parallel Slaves
rem PR, Process Startup
rem PS, Parallel Slave Synchronization
rem SM, SMON
rem TS, Temporary Segment (also TableSpace)
alter rollback segment r01 offline;
alter rollback segment r02 offline;
alter rollback segment r03 offline;
alter rollback segment r04 offline;
alter rollback segment r05 offline;
alter rollback segment r06 offline;
rem alter rollback segment r07 offline;
rem alter rollback segment r08 offline;
drop rollback segment r01;
drop rollback segment r02;
drop rollback segment r03;
drop rollback segment r04;
drop rollback segment r05;
drop rollback segment r06;
rem drop rollback segment r07;
rem drop rollback segment r08;
@defrag7
create rollback segment r01 tablespace rbs storage(initial 6m next 6m optimal
12m);
create rollback segment r02 tablespace rbs storage(initial 6m next 6m optimal
12m);
create rollback segment r03 tablespace rbs storage(initial 6m next 6m optimal
12m);
create rollback segment r04 tablespace rbs storage(initial 6m next 6m optimal
12m);
create rollback segment r05 tablespace rbs storage(initial 6m next 6m optimal
12m);
create rollback segment r06 tablespace rbs storage(initial 6m next 6m optimal
12m);
rem create rollback segment r07 tablespace rbs storage(initial 2m next 2m
optimal 6m);
rem create rollback segment r08 tablespace rbs storage(initial 2m next 2m
optimal 6m);
alter rollback segment r01 online;
alter rollback segment r02 online;
alter rollback segment r03 online;
alter rollback segment r04 online;
alter rollback segment r05 online;
alter rollback segment r06 online;
rem alter rollback segment r07 online;
rem alter rollback segment r08 online;
column value format 999.9999
column name format a25
column meas_date format a16
set pages 0 lines 80
start title80 'Flush Dates and Times'
spool rep_out/&db/cum_hrat
select
name, value, to_char(meas_date,'dd-mon-yy hh24:mi') meas_date
from dba_running_stats where name ='Flush of Shared Pool'
order by meas_date
/
spool off
CREATE OR REPLACE PROCEDURE flush_it(
p_free IN NUMBER, num_runs IN NUMBER) IS
--
CURSOR get_share IS
SELECT
LEAST(MAX(b.value)/(1024*1024),SUM(a.bytes)/(1024*1024))
FROM v$sgastat a, v$parameter b
WHERE (a.pool='shared pool'
AND a.name <> ('free memory'))
AND b.name = 'shared_pool_size';
--
CURSOR get_var IS
SELECT value/(1024*1024)
FROM v$parameter
WHERE name = 'shared_pool_size';
--
CURSOR get_time IS
SELECT sysdate FROM dual;
--
-- Following cursors from Nice_flush
--
CURSOR reused_cursors IS
SELECT address || ',' || hash_value
FROM sys.v_$sqlarea
WHERE executions > num_runs;
cursor_string varchar2(30);
--
CURSOR cached_sequences IS
SELECT sequence_owner, sequence_name
FROM sys.dba_sequences
WHERE cache_size > 0;
sequence_owner varchar2(30);
sequence_name varchar2(30);
--
CURSOR candidate_objects IS
SELECT kglnaobj, decode(kglobtyp, 6, 'Q', 'P')
FROM sys.x_$kglob
WHERE inst_id = userenv('Instance') AND
kglnaown = 'SYS' AND kglobtyp in (6, 7, 8, 9);
object_name varchar2(128);
object_type char(1);
--
-- end of Cursors
--
todays_date DATE;
mem_ratio NUMBER;
share_mem NUMBER;
variable_mem NUMBER;
cur INTEGER;
sql_com VARCHAR2(60);
row_proc NUMBER;
--
BEGIN
OPEN get_share;
OPEN get_var;
FETCH get_share INTO share_mem;
FETCH get_var INTO variable_mem;
mem_ratio:=share_mem/variable_mem;
IF mem_ratio>p_free/100 THEN
--
-- Following keep sections from nice_flush
--
BEGIN
OPEN reused_cursors;
LOOP
FETCH reused_cursors INTO cursor_string;
EXIT WHEN reused_cursors%notfound;
sys.dbms_shared_pool.keep(cursor_string, 'C');
END LOOP;
END;
BEGIN
OPEN cached_sequences;
LOOP
FETCH cached_sequences INTO sequence_owner, sequence_name;
EXIT WHEN cached_sequences%notfound;
sys.dbms_shared_pool.keep(sequence_owner || '.' || sequence_name, 'Q');
END LOOP;
END;
BEGIN
OPEN candidate_objects;
LOOP
FETCH candidate_objects INTO object_name, object_type;
EXIT WHEN candidate_objects%notfound;
sys.dbms_shared_pool.keep('SYS.' || object_name, object_type);
END LOOP;
END;
--
-- end of section
--
cur:=DBMS_SQL.OPEN_CURSOR;
sql_com:='ALTER SYSTEM FLUSH SHARED_POOL';
DBMS_SQL.PARSE(cur,sql_com,dbms_sql.v7);
row_proc:=DBMS_SQL.EXECUTE(cur);
DBMS_SQL.CLOSE_CURSOR(cur);
OPEN get_time;
FETCH get_time INTO todays_date;
INSERT INTO dba_running_stats VALUES
(
'Flush of Shared Pool',1,35,todays_date,0
);
COMMIT;
END IF;
END flush_it;
rem flush_rep.sql
rem MRA 7/21/98
rem Reports based on count of entries per day in the
rem DBA_RUNNING_STATS table the number of flushes
rem of the shared pool. If average is excessive
rem increase the size of the shared pool or increase the
rem ratio in the flush_it procedure.
rem
column flushes heading 'Flushes|of|Shared Pool'
column meas_date heading 'Date|of|Flushes'
set verify off feedback off
break on report
compute avg of flushes on report
accept min_date prompt 'Enter date (dd-mon-yy) to start from:'
accept no_days prompt 'Enter number of days for report:'
start title80 'Shared Pool Flushes'
spool rep_out/&db/flush.lis
select count(*) flushes, trunc(meas_date) meas_date
from dba_running_stats
where trunc(meas_date)>=to_date('&min_date','dd-mon-yy') and
trunc(meas_date)<=to_date('&min_date','dd-mon-yy')+to_number('&no_days')
and name like 'Flush%'
and upper(to_char(meas_date,'DY')) not in ('SAT','SUN')
group by trunc(meas_date)
/
spool off
clear computes
clear breaks
undef min_date
undef no_days
set verify on feedback on
create or replace PROCEDURE get_count (
tab_name IN VARCHAR2,
rows OUT NUMBER
) AS
cur INTEGER;
ret INTEGER;
com_string VARCHAR2(100);
row_count NUMBER;
BEGIN
com_string :=
'SELECT count(1) row_count FROM '||tab_name;
cur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cur,com_string,dbms_sql.v7);
DBMS_SQL.DEFINE_COLUMN(cur, 1, row_count);
ret := DBMS_SQL.EXECUTE(cur);
ret := DBMS_SQL.FETCH_ROWS(cur);
DBMS_SQL.COLUMN_VALUE(cur, 1, row_count);
DBMS_SQL.CLOSE_CURSOR(cur);
DBMS_OUTPUT.PUT_LINE('Count='||TO_CHAR(row_count));
rows:=row_count;
EXCEPTION
WHEN others THEN
null;
END;
@title80 'Cummulative Hit Ratio'
spool rep_out/&db/hit_ratio
select a.name pool, (1-(a.physical_reads/(a.db_block_gets+a.consistent_gets)))
hit_ratio,
b.free_meg
from v$buffer_pool_statistics a, (select
sum(decode(d.file#,0,1)*c.value)/(1024*1024) free_meg from v$bh d, v$parameter c
where d.file#=0 and c.name='db_block_size') b
/
spool off
ttitle off
COLUMN log_id FORMAT 999999
COLUMN filename FORMAT A45
COLUMN low_scn FORMAT 9999999
COLUMN high_scn FORMAT 9999999
SET LINES 132 PAGES 45
@title132 'Log Miner Log Files'
SPOOL rep_out/&&db/log_miner
select LOG_ID,FILENAME,LOW_TIME,HIGH_TIME,LOW_SCN,NEXT_SCN
from v$logmnr_logs
/
SPOOL OFF
SET LINES 80 PAGES 22
CREATE OR REPLACE PROCEDURE get_logfiles(
logfile_dir in VARCHAR2,
logfile_lis in VARCHAR2)
AS
cur INTEGER;
sql_com VARCHAR2(2000);
file_full_name VARCHAR2(255);
file_proc INTEGER;
file_hand utl_file.file_type;
file_buff VARCHAR2(1022);
file_type VARCHAR2(4);
ctr NUMBER:=0;
BEGIN
file_hand:=utl_file.fopen(logfile_dir,logfile_lis,'R');
LOOP
ctr:=ctr+1;
BEGIN
utl_file.get_line(file_hand,file_buff);
cur:=dbms_sql.open_cursor;
file_full_name:=logfile_dir||'\'||file_buff;
sql_com:= 'begin ';
IF ctr=1 THEN
sql_com:=sql_com||'dbms_logmnr.add_logfile('||chr(39)||file_full_name||chr(39)||','||
dbms_logmnr.new||'); end;';
else
sql_com:=sql_com||'dbms_logmnr.add_logfile('||chr(39)||file_full_name||chr(39)||','||
dbms_logmnr.addfile||'); end;';
end if;
dbms_output.put_line(sql_com);
dbms_sql.parse(cur,sql_com,dbms_sql.v7);
file_proc:=dbms_sql.execute(cur);
dbms_sql.close_cursor(cur);
EXCEPTION
WHEN no_data_found THEN
EXIT;
WHEN others THEN
EXIT;
END;
END LOOP;
utl_file.fclose(file_hand);
END;
/
column meas_date format a25 heading 'Measurement Date'
column name format a25 heading 'Measurement'
column value heading 'Measurement|Value'
column delta heading 'Measurement|Change'
select
name,
value,
delta,
to_char(meas_date,'dd-mon-yy hh24:mi:ss') meas_date
from
dba_running_stats
where
upper(name)=upper('&measurement')
and to_char(meas_date,'dd-mon-yy hh24:mi:ss')>'&measurement_date(dd-mon-yy
hh25:mi:ss)'
order by meas_date
/
rem
rem FUNCTION: Report objects that need to be recompiled
rem FUNCTION: because they may be hanging
rem FUNCTION: run from SYS only
rem
@title80 "Objects that need Recompilation"
column obj# heading Object|Number
column name heading Object|Name
column owner# heading Owner|Number
spool rep_out/&db/hanging
select distinct o2.obj#,o2.name, o2.owner#
from sys.obj$ o,
sys.dependency$ d,
sys.obj$ o2
where o.obj# = d.p_obj#
and o.stime != d.p_timestamp
and d.d_obj# = o2.obj#
and o2.status != 5
order by o2.obj#
/
spool off
clear columns
ttitle off
CREATE OR REPLACE PROCEDURE HITRATIO IS
C_DATE DATE;
C_HOUR NUMBER := 0;
H_RATIO NUMBER(5,2) := 0;
CON_GETS NUMBER := 0;
DB_GETS NUMBER := 0;
P_READS NUMBER := 0;
STAT_NAME VARCHAR(64);
temp_NAME VARCHAR(64);
STAT_VAL NUMBER := 0;
USERS NUMBER := 0;
CURSOR get_stat(p_name varchar2) IS
select
name,
value
from
v$sysstat
where
name = p_name;
BEGIN
select to_char(sysdate,'DD-MON-YY') into c_date from dual;
select to_char(sysdate,'HH24') into c_hour from dual;
STAT_NAME := 'db block gets';
OPEN get_stat(stat_name);
FETCH get_stat INTO temp_name,db_gets;
CLOSE get_stat;
dbms_output.put_line(temp_name||':'||to_char(db_gets));
STAT_NAME := 'consistent gets';
OPEN get_stat(stat_name);
FETCH get_stat INTO temp_name,con_gets;
CLOSE get_stat;
dbms_output.put_line(temp_name||':'||to_char(con_gets));
STAT_NAME := 'physical reads';
OPEN get_stat(stat_name);
FETCH get_stat INTO temp_name,p_reads;
CLOSE get_stat;
dbms_output.put_line(temp_name||':'||to_char(p_reads));
select count(*) into users from v$session where username is not null;
H_RATIO := (((DB_GETS+CON_GETS-p_reads)/(DB_GETS+CON_GETS))*100);
dbms_output.put_line('hit_ratio:'||to_char(h_ratio));
INSERT INTO hit_ratios
VALUES (c_date,c_hour,db_gets,con_gets,p_reads,h_ratio,0,0,users);
commit;
update hit_ratios set period_hit_ratio =
(select
round((((h2.consistent-h1.consistent)+(h2.db_block_gets-h1.db_block_gets)-
(h2.phy_reads-h1.phy_reads))/((h2.consistent-h1.consistent)+
(h2.db_block_gets-h1.db_block_gets)))*100,2) from hit_ratios h1, hit_ratios h2
where h2.check_date = hit_ratios.check_date and h2.check_hour =
hit_ratios.check_hour
and ((h1.check_date = h2.check_date and h1.check_hour+1 = h2.check_hour) or
(h1.check_date+1 = h2.check_date and h1.check_hour = '23' and
h2.check_hour='0')))
where period_hit_ratio = 0;
COMMIT;
update hit_ratios set period_USAGE =
(select ((h2.consistent-h1.consistent)+(h2.db_block_gets-h1.db_block_gets))
from hit_ratios h1, hit_ratios h2 where h2.check_date = hit_ratios.check_date
and h2.check_hour = hit_ratios.check_hour and ((h1.check_date = h2.check_date
and h1.check_hour+1 = h2.check_hour) or (h1.check_date+1 = h2.check_date and
h1.check_hour = '23' and h2.check_hour='0'))) where period_USAGE = 0;
COMMIT;
EXCEPTION
WHEN ZERO_DIVIDE THEN
INSERT INTO hit_ratios VALUES
(c_date,c_hour,db_gets,con_gets,p_reads,0,0,0,users);
COMMIT;
END;
/
rem Name: inv_obj.sql
rem Purpose: Show alll invalid objects in database
rem Mike Ault 7/2/96 TreCom/RevealNet
rem
column object_name format A25 heading 'Object|Name'
column owner format a10 heading 'Object|Owner'
column last_time format a20 heading 'Last Change|Date'
set lines 132 feedback off pages 0 verify off
start title132 'Invalid Database Objects'
spool rep_out/&db/inv_obj
select
owner,
object_name,
object_type,
to_char(last_ddl_time,'DD-MON-YY hh:mi:ss') Last_time
from dba_objects where status='INVALID'
/
pause Press enter to continue
set lines 80 feedback on pages 22 verify on
clear columns
ttitle off
col uga_mem format 999999999
spool jb_mts.lis
SELECT S.type
,S.server
,S.status
,SUM(ST.value) uga_mem
FROM
v$session S
,v$sesstat ST
,v$statname SN
WHERE
S.sid = ST.sid
AND ST.statistic# = SN.statistic#
AND SN.name = 'session uga memory'
GROUP BY type,server,status;
SELECT S.type
,S.server
,S.status
,SUM(ST.value) uga_mem
FROM
v$session S
,v$sesstat ST
,v$statname SN
,v$circuit C
,v$shared_server SS
WHERE
S.sid = ST.sid
AND SS.circuit = C.circuit
AND C.saddr = S.saddr
AND ST.statistic# = SN.statistic#
AND SN.name = 'session uga memory'
GROUP BY S.type,S.server,S.status;
SELECT SUM(bytes) total_other
FROM v$sgastat
WHERE name NOT IN
('free memory','fixed_sga','db_block_buffers','log_buffer'
,'dictionary cache','library cache','sql area');
SELECT name
,bytes
FROM v$sgastat
WHERE name NOT IN
('free memory','fixed_sga','db_block_buffers','log_buffer'
,'dictionary cache','library cache','sql area');
spool off
create or replace procedure kill_session ( session_id in varchar2,
serial_num in varchar2)
as
cur integer;
ret integer;
string varchar2(100);
begin
string :=
'alter system kill session '||''''||session_id||','||serial_num||'''';
cur := dbms_sql.open_cursor;
dbms_sql.parse(cur,string,dbms_sql.v7);
ret := dbms_sql.execute(cur) ;
dbms_sql.close_cursor(cur);
exception
when OTHERS then
raise_application_error(-20001,'Error in execution',TRUE);
if dbms_sql.is_open(cur) then
dbms_sql.close_cursor(cur);
end if;
end;
/
column value format 999.9999
column name format a30
column meas_date format a16
set pages 0 lines 80
start title80 'Latch Miss Percent'
spool rep_out/&db/latch_miss
select
name, value, to_char(meas_date,'dd-mon-yy hh24:mi') meas_date
from dba_running_stats where name like 'Latch Miss%'
order by meas_date
/
spool off
set echo off
column value format 999.9999
column name format a30
column meas_date format a16
set pages 0 lines 80
start title80 'Library reload Percent'
spool rep_out/&db/lib_reload
select
name, value, to_char(meas_date,'dd-mon-yy hh24:mi') meas_date
from dba_running_stats where name like 'Library Reload%'
order by meas_date
/
spool off
set echo off
rem
rem Title: libcache.sql
rem
rem FUNCTION: Generate a library cache report
rem
column namespace heading "Library Object"
column gets format 99,999,999 heading "Gets"
column gethitratio format 999.99 heading "Get Hit%"
column pins format 999,999,999 heading "Pins"
column pinhitratio format 999.99 heading "Pin Hit%"
column reloads format 999,999 heading "Reloads"
column invalidations format 99,999 heading "Invalid"
column db format a10
set pages 58 lines 80
start title80 "Library Caches Report"
define output = rep_out\&db\lib_cache
spool &output
select
namespace,
gets,
gethitratio*100 gethitratio,
pins,
pinhitratio*100 pinhitratio,
RELOADS,
INVALIDATIONS
from
v$librarycache
/
spool off
pause Press enter to continue
set pages 22 lines 80
ttitle off
undef output
CREATE OR REPLACE PROCEDURE load_pool(pct_load IN NUMBER) as
cur_num INTEGER;
row_proc INTEGER;
SQL_TEXT VARCHAR2(2000);
test_val INTEGER;
numobjs INTEGER;
dummy INTEGER;
i INTEGER;
shared_val NUMBER;
pool_size NUMBER;
loop_ctr integer;
cursor get_share is
select sum(bytes) from
v$sgastat where pool='shared pool' and name <>'free memory';
cursor get_var is
select value from v$parameter where name = 'shared_pool_size';
BEGIN
loop_ctr:=0;
SELECT MAX(object_id) INTO numobjs FROM DBA_OBJECTS;
LOOP
BEGIN
FOR i IN 0..numobjs LOOP
SELECT COUNT(*) INTO test_val
FROM dba_objects WHERE object_id=i;
IF test_val=1 THEN
sql_text:='SELECT count(*) FROM dba_objects WHERE object_id='||to_char(i)||
' AND '||to_char(loop_ctr)||'='||to_char(loop_ctr);
cur_num:=dbms_sql.open_cursor;
dbms_sql.parse(cur_num,sql_text,DBMS_SQL.NATIVE);
dbms_sql.define_column(cur_num,1,dummy);
row_proc:=dbms_sql.execute(cur_num);
dbms_sql.close_cursor(cur_num);
open get_share;
open get_var;
fetch get_share into shared_val;
fetch get_var into pool_size;
CLOSE get_share;
CLOSE get_var;
dbms_output.put_line('Shared pool size: '||to_char(shared_val/pool_size*100));
IF (shared_val/pool_size)*100>=pct_load THEN
dbms_output.put_line('Shared pool size: '||to_char(shared_val/pool_size*100));
EXIT;
END IF;
END IF;
END LOOP;
END;
IF (shared_val/pool_size)*100>=pct_load THEN
dbms_output.put_line('Shared pool size: '||to_char(shared_val/pool_size*100));
EXIT;
END IF;
loop_ctr:=loop_ctr+1;
END LOOP;
END;
clear screen
set serveroutput on
BEGIN
dbms_output.enable(1000000);
for do_loop in (select session_id, a.object_id, xidsqn, oracle_username, b.owner
owner,
b.object_name object_name, b.object_type object_type
FROM v$locked_object a, dba_objects b
WHERE xidsqn != 0
and b.object_id = a.object_id)
loop
dbms_output.put_line('.');
dbms_output.put_line('Blocking Session : '||do_loop.session_id);
dbms_output.put_line('Object (Owner/Name):
'||do_loop.owner||'.'||do_loop.object_name);
dbms_output.put_line('Object Type : '||do_loop.object_type);
for next_loop in (select sid from v$lock
where id2 = do_loop.xidsqn
and sid != do_loop.session_id)
LOOP
dbms_output.put_line('Sessions being blocked : '||next_loop.sid);
end loop;
end loop;
END;
/
rem
rem FUNCTION: Report all DB locks
rem
column osuser format a15 heading 'User'
column session_id heading 'SID'
column mode_held format a20 heading 'Mode|Held'
column mode_requested format a20 heading 'Mode|Requested'
column lock_id1 format a10 heading 'Lock|ID1'
column lock_id2 format a10 heading 'Lock|ID2'
column type heading 'Type|Lock'
set feedback off echo off pages 59 lines 131
start title132 'Report on All Locks'
spool rep_out/&db/locks
select nvl(a.osuser,'SYS') osuser,b.session_id,type,
mode_held,mode_requested,
lock_id1,lock_id2
from sys.v_$session a, sys.dba_locks b
where
a.sid=b.session_id
order by 2
/
spool off
pause press enter/return to continue
clear columns
set feedback on echo on pages 22 lines 80
REM ********************************************************************
REM Locks v.1.01 27/05/96
REM This query will list locking conflicts between tables
REM and indicate users who are locking the tables and
REM there waited users. Must be run as SYS
REM Sanjeev Parikh
REM ********************************************************************
set headingsep ='|'
set lines 160
set pagesize 20
ttitle 'Database Locking Conflict Report'
btitle 'Mode Held = indicates the user holding the lock|Mode Request = indicates
the user waiting on the later to finish to establish lock||** End of Locking
Conflict Report **'
column username format a10 heading 'User'
column terminal format a15 heading 'Application|PC'
column object format a15 heading 'Table'
column sql format a15 heading 'SQL'
column sid format 999 heading 'SID'
column lock_type format a15 heading 'Lock|Type'
column mode_held format a11 heading 'Mode|Held'
column mode_requested format a10 heading 'Mode|Request'
column lock_id1 format a8 heading 'Lock ID1'
column lock_id2 format a8 heading 'Lock ID2'
column first_load_time format a19 heading 'Requested'
break on lock_id1
select a.sid,
username,
terminal,
decode(a.type,'MR', 'Media Recovery',
'RT', 'Redo Thread',
'UN', 'User Name',
'TX', 'Transaction',
'TM', 'DML',
'UL', 'PL/SQL User Lock',
'DX', 'Distributed Xaction',
'CF', 'Control File',
'IS', 'Instance State',
'FS', 'File Set',
'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction',
'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction',
'TS', 'Temp Segment',
'IV', 'Library Cache Invalidation',
'LS', 'Log Start or Switch',
'RW', 'Row Wait',
'SQ', 'Sequence Number',
'TE', 'Extend Table',
'TT', 'Temp Table', a.type) lock_type,
decode(a.lmode,0, 'None', /* Mon Lock equivalent */
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive', /* X */
to_char(a.lmode)) mode_held,
decode(a.request,
0, 'None', /* Mon Lock equivalent */
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive', /* X */
to_char(a.request)) mode_requested,
to_char(a.id1) lock_id1, to_char(a.id2) lock_id2,
c.object object,
d.sql_text sql,
e.first_load_time
from v$lock a, v$session, v$access c, v$sqltext d, v$sqlarea e
where (id1,id2) in
(select b.id1, b.id2 from v$lock b where b.id1=a.id1 and
b.id2=a.id2 and b.request>0) and
a.sid = v$session.sid and
a.sid = c.sid and
d.address = v$session.sql_address and
d.hash_value = v$session.sql_hash_value and
d.address = e.address
order by a.id1, a.lmode desc
REM
REM NAME :log_hist.sql
REM PURPOSE:Provide info on logs for last 24 hour since last log switch
REM USE : From SQLPLUS
REM Limitations : None
REM
column thread# format 999 heading 'Thrd#'
column sequence# format 99999 heading 'Seq#'
column low_change# heading 'Low#'
column high_change# heading 'High#'
column first_time heading 'Accessed'
set lines 80
@title80 "Log History Report"
spool rep_out/&db/log_hist
rem
select thread#, sequence#,
first_change#,next_change#,
to_char(a.first_time,'dd-mon-yyyy hh24:mi:ss') time
from
v$log_history a
where
a.first_time >
(select b.first_time-1
from v$log_history b where b.next_change# =
(select max(c.next_change#) from v$log_history c));
spool off
set lines 80
clear columns
ttitle off
pause Press enter to continue
COLUMN log_id FORMAT 999999
COLUMN filename FORMAT A20
COLUMN low_scn FORMAT 9999999
COLUMN high_scn FORMAT 9999999
SET LINES 132 PAGES 45
@title132 'Log Miner Log Files'
SPOOL rep_out/&&db/log_miner
SELECT
db_id,
log_id,filename,
to_char(low_time,'dd-mon-yy hh:mi:ss') low_time,
to_char(high_time,'dd-mon-yy hh:mi:ss') high_time,
low_scn,next_scn
FROM
v$logmnr_logs
ORDER BY
Low_time
/
SPOOL OFF
SET LINES 80 PAGES 22
rem
rem Name: log_stat.sql
rem
rem FUNCTION: Provide a current status for redo logs
rem
rem
column first_change# format 999,999,999 heading Change#
column group# format 9,999 heading Grp#
column thread# format 999 heading Th#
column sequence# format 999,999 heading Seq#
column members format 999 heading Mem
column archived format a4 heading Arc?
COLUMN first_time HEADING First|Time
break on thread#
set pages 60 lines 132 feedback off
start title132 'Current Redo Log Status'
spool rep_out/&db/log_stat
select thread#, group#, sequence#,
bytes, members,archived,status,first_change#,
to_char(first_time,'dd-mon-yyyy hh24:mi') first_time
from sys.v_$log
order by thread#, group#;
spool off
pause Press Enter to continue
set pages 22 lines 80 feedback on
clear breaks
clear columns
ttitle off
select statistic,to_char(value) from sys.v$pq_sysstat
union
select network "Statistic",to_char(sum(busy)/(sum(busy)+sum(idle))) "Value"
from v$dispatcher
union
select network "Statitistic",
decode(sum(totalq), 0, 'No Responses',
sum(wait)/sum(totalq)||' hundreths of seconds')
from v$queue q, v$dispatcher d
where q.type='DISPATCHER'
and q.paddr=d.paddr
group by network;
rem Report on the ratios of unique hash values and
rem unique address values to all values for SQL areas
rem
column u_hash_ratio format 999.999 heading "Unique Hash Ratio"
column u_address_ratio format 999.999 heading "Unique Address Ratio"
start title80 "Unique Hash an Address Ratios"
spool rep_out/&&db/u_hash_addr_rat.lst
select (count(distinct(hash_value))/count(hash_value))*100 u_hash_ratio,
(count(distinct(address))/count(address))*100 u_address_ratio from v$sqlarea
/
spool off
column name format a30 heading 'Pool Measurement'
column meas_date format a15 heading 'Measurement Date'
column value format 9,999.99 heading 'Value'
set pages 47
@title80 'Shared Pool Measurements'
spool rep_out/&db/pool_rep.doc
select name,value,to_char(meas_date,'dd-mon-yy hh24:mi') meas_date
from dba_running_stats
where name in ('Shared Pool Available',
'Shared Pool Used') and
trunc(meas_date) between to_date('&date1') and to_date('&date2')
order by 1,3
/
spool off
REM NAME : RBK1.SQL
REM FUNCTION : REPORT ON ROLLBACK SEGMENT STORAGE
REM FUNCTION : USES THE ROLLBACK1 VIEW
REM USE : FROM SQLPLUS
REM Limitations : None
REM
COLUMN hwmsize FORMAT 9999999999 HEADING 'LARGEST TRANS'
COLUMN tablespace_name FORMAT a10 HEADING 'TABLESPACE'
COLUMN segment_name FORMAT A10 HEADING 'ROLLBACK'
COLUMN optsize FORMAT 9999999999 HEADING 'OPTL|SIZE'
COLUMN shrinks FORMAT 9999 HEADING 'SHRINKS'
COLUMN aveshrink FORMAT 9999999999 HEADING 'AVE|SHRINK'
COLUMN aveactive FORMAT 9999999999 HEADING 'AVE|TRANS'
COLUMN waits FORMAT 99999 HEADING 'WAITS'
COLUMN wraps FORMAT 99999 HEADING 'WRAPS'
COLUMN extends FORMAT 9999 HEADING 'EXTENDS'
rem
BREAK ON REPORT
COMPUTE AVG OF AVESHRINK ON REPORT
COMPUTE AVG OF AVEACTIVE ON REPORT
COMPUTE AVG OF SHRINKS ON REPORT
COMPUTE AVG OF WAITS ON REPORT
COMPUTE AVG OF WRAPS ON REPORT
COMPUTE AVG OF EXTENDS ON REPORT
COMPUTE AVG OF HWMSIZE ON REPORT
SET FEEDBACK OFF VERIFY OFF LINES 132 PAGES 58
@title132 "ROLLBACK SEGMENT STORAGE"
SPOOL rep_out/&db/rbk1
rem
SELECT
a.SEGMENT_NAME,
a.OPTSIZE,
a.SHRINKS,
a.AVESHRINK,
a.AVEACTIVE,
b.HWMSIZE,
b.WAITS,
b.WRAPS,
b.EXTENDS,
A.STATUS
FROM rollback1 a, rollback2 b
WHERE A.SEGMENT_NAME=B.SEGMENT_NAME
ORDER BY segment_name;
SPOOL OFF
CLEAR COLUMNS
TTITLE OFF
SET FEEDBACK ON VERIFY ON LINES 80 PAGES 22
PAUSE Press enter to continue
REM
REM NAME : RBK2.SQL
REM FUNCTION : REPORT ON ROLLBACK SEGMENT STATISTICS
REM FUNCTION : USES THE ROLLBACK2 VIEW
REM USE : FROM SQLPLUS
REM Limitations : None
REM
COLUMN segment_name FORMAT A8 HEADING 'ROLLBACK'
COLUMN extents FORMAT 99999 HEADING 'EXTENTS'
COLUMN xacts FORMAT 9999 HEADING 'TRANS'
COLUMN hwmsize FORMAT 9999999999 HEADING 'LARGEST TRANS'
COLUMN rssize FORMAT 9999999999 HEADING 'CUR SIZE'
COLUMN waits FORMAT 99999 HEADING 'WAITS'
COLUMN wraps FORMAT 99999 HEADING 'WRAPS'
COLUMN extends FORMAT 9999 HEADING 'EXTENDS'
rem
SET FEEDBACK OFF VERIFY OFF lines 132 pages 58
BREAK ON REPORT
COMPUTE AVG OF WAITS ON REPORT
COMPUTE AVG OF WRAPS ON REPORT
COMPUTE AVG OF EXTENDS ON REPORT
COMPUTE AVG OF HWMSIZE ON REPORT
rem
@title132 "ROLLBACK SEGMENT STATISTICS"
SPOOL rep_out/&db/rbk2
rem
SELECT * FROM rollback2 ORDER BY segment_name;
SPOOL OFF
SET LINES 80 PAGES 20 FEEDBACK ON VERIFY ON
TTITLE OFF
CLEAR COLUMNS
PAUSE Press enter to continue
REM
REM NAME : RBK3.SQL
REM FUNCTION : REPORT ON ROLLBACK SEGMENT HEALTH
REM FUNCTION : USES THE ROLLBACK1 and ROLLBACK2 VIEWs
REM USE : FROM SQLPLUS
REM Limitations : None
REM
COLUMN hwmsize FORMAT 9999999999 HEADING 'LARGEST TRANS'
COLUMN tablespace_name FORMAT a10 HEADING 'TABLESPACE'
COLUMN segment_name FORMAT A10 HEADING 'ROLLBACK'
COLUMN optsize FORMAT 9999999999 HEADING 'OPTL|SIZE'
COLUMN shrinks FORMAT 9999 HEADING 'SHRINKS'
COLUMN aveshrink FORMAT 9999999999 HEADING 'AVE|SHRINK'
COLUMN aveactive FORMAT 9999999999 HEADING 'AVE|TRANS'
COLUMN waits FORMAT 99999 HEADING 'WAITS'
COLUMN wraps FORMAT 99999 HEADING 'WRAPS'
COLUMN extends FORMAT 9999 HEADING 'EXTENDS'
rem
BREAK ON REPORT
COMPUTE AVG OF AVESHRINK ON REPORT
COMPUTE AVG OF AVEACTIVE ON REPORT
COMPUTE AVG OF SHRINKS ON REPORT
COMPUTE AVG OF WAITS ON REPORT
COMPUTE AVG OF WRAPS ON REPORT
COMPUTE AVG OF EXTENDS ON REPORT
COMPUTE AVG OF HWMSIZE ON REPORT
SET FEEDBACK OFF VERIFY OFF LINES 132 PAGES 47
@title132 "ROLLBACK SEGMENT HEALTH"
SPOOL rep_out/&db/rbk3
rem
SELECT c.tablespace_name, a.segment_name, a.optsize, a.shrinks, a.aveshrink,
a.aveactive,
b.hwmsize, b.waits, b.wraps, b.extends
FROM rollback1 a, rollback2 b, dba_rollback_segs c
where a.segment_name=b.segment_name
and c.segment_name=a.segment_name
ORDER BY tablespace_name, segment_name;
SPOOL OFF
CLEAR COLUMNS
TTITLE OFF
SET FEEDBACK ON VERIFY ON LINES 80 PAGES 22
PAUSE Press enter to continue
REM
REM rbk_rct.sql
REM
REM FUNCTION: SCRIPT FOR CREATING ROLLBACK SEGMENTS
REM
REM This script must be run by a user with the DBA role.
REM
REM This script is intended to run with Oracle.
REM
REM Running this script will in turn create a script to re-build
REM the database rollback segments. The created script is called
REM create_rollback_segs.sql and can be run by any user with the DBA
REM role or with the 'CREATE ROLLBACK SEGMENT' system privilege.
REM
REM NOTE: This script will NOT capture the optimal storage for
REM a rollback segment that is offline.
REM
REM The rollback segments must be manually brought back online
REM after running the create_rollback_segs.sql script.
REM
REM Only preliminary testing of this script was performed. Be
REM sure to test it completely before relying on it.
REM
set verify off
set feedback off
set termout off
set echo off
set pagesize 0
set termout on
select 'Creating rollback segment build script...' from dual;
rem set termout off
create table rb_temp (lineno NUMBER, rb_name varchar2(30),
text varchar2(800))
/
DECLARE
CURSOR rb_cursor IS select segment_name,
tablespace_name,
decode (owner, 'PUBLIC', 'PUBLIC ', NULL),
segment_id,
initial_extent,
next_extent,
min_extents,
max_extents,
status
from sys.dba_rollback_segs
where segment_name <> 'SYSTEM';
CURSOR rb_optimal (r_no number) IS select usn,
decode(optsize, null, 'NULL', to_char(optsize))
from sys.v_$rollstat
where usn=r_no;
lv_segment_name sys.dba_rollback_segs.segment_name%TYPE;
lv_tablespace_name sys.dba_rollback_segs.tablespace_name%TYPE;
lv_owner VARCHAR2(10);
lv_segment_id sys.dba_rollback_segs.segment_id%TYPE;
lv_initial_extent sys.dba_rollback_segs.initial_extent%TYPE;
lv_next_extent sys.dba_rollback_segs.next_extent%TYPE;
lv_min_extents sys.dba_rollback_segs.min_extents%TYPE;
lv_max_extents sys.dba_rollback_segs.max_extents%TYPE;
lv_status sys.dba_rollback_segs.status%TYPE;
lv_usn sys.v_$rollstat.usn%TYPE;
lv_optsize VARCHAR2(40);
lv_string VARCHAR2(800);
lv_lineno number := 0;
procedure write_out(p_line INTEGER, p_name VARCHAR2, p_string VARCHAR2) is
begin
insert into rb_temp (lineno, rb_name, text) values
(p_line, p_name, p_string);
end;
BEGIN
OPEN rb_cursor;
LOOP
FETCH rb_cursor INTO lv_segment_name,
lv_tablespace_name,
lv_owner,
lv_segment_id,
lv_initial_extent,
lv_next_extent,
lv_min_extents,
lv_max_extents,
lv_status;
EXIT WHEN rb_cursor%NOTFOUND;
lv_lineno := 1;
OPEN rb_optimal(lv_segment_id);
LOOP
FETCH rb_optimal INTO lv_usn,
lv_optsize;
EXIT WHEN rb_optimal%NOTFOUND;
END LOOP;
CLOSE rb_optimal;
if lv_status = 'ONLINE' then
lv_string:='CREATE ' || lv_owner || 'ROLLBACK SEGMENT ' ||
lower(lv_segment_name);
write_out(lv_lineno, lv_segment_name, lv_string);
lv_lineno := lv_lineno + 1;
lv_string:='TABLESPACE ' || lower(lv_tablespace_name);
write_out(lv_lineno, lv_segment_name, lv_string);
lv_lineno := lv_lineno + 1;
lv_string:='STORAGE ' || '(INITIAL ' || lv_initial_extent || ' NEXT ' ||
lv_next_extent || ' MINEXTENTS ' || lv_min_extents ||
' MAXEXTENTS ' || lv_max_extents ||
' OPTIMAL ' || lv_optsize || ')' ;
write_out(lv_lineno, lv_segment_name, lv_string);
lv_lineno := lv_lineno + 1;
lv_string:='/';
write_out(lv_lineno, lv_segment_name, lv_string);
else
lv_string:='CREATE ' || lv_owner || 'ROLLBACK SEGMENT ' ||
lower(lv_segment_name);
write_out(lv_lineno, lv_segment_name, lv_string);
lv_lineno := lv_lineno + 1;
lv_string:='TABLESPACE ' || lower(lv_tablespace_name);
write_out(lv_lineno, lv_segment_name, lv_string);
lv_lineno := lv_lineno + 1;
lv_string:='STORAGE ' || '(INITIAL ' || lv_initial_extent || ' NEXT ' ||
lv_next_extent || ' MINEXTENTS ' || lv_min_extents ||
' MAXEXTENTS ' || lv_max_extents || ')' ;
write_out(lv_lineno, lv_segment_name, lv_string);
lv_lineno := lv_lineno + 1;
lv_string:='/';
write_out(lv_lineno, lv_segment_name, lv_string);
end if;
lv_lineno := lv_lineno + 1;
lv_string:=' ';
write_out(lv_lineno, lv_segment_name, lv_string);
END LOOP;
CLOSE rb_cursor;
END;
/
column dbname new_value db noprint
select value dbname from v$parameter where name='db_name';
spool rep_out/&db/crt_rbks.sql
set heading off
col text format a80 word_wrap
select text
from rb_temp
order by rb_name, lineno;
spool off;
Drop table rb_temp;
set verify on
set feedback on
set termout on
set pagesize 22
set lines 80
clear columns
set heading on
CREATE OR REPLACE VIEW ROLLBACK1 AS
SELECT
d.segment_NAME,EXTENTS,
OPTSIZE,SHRINKS,
AVESHRINK,AVEACTIVE,
d.STATUS
FROM
V$ROLLNAME N,
V$ROLLSTAT S,
dba_rollback_segs d
WHERE
d.segment_id=n.usn(+)
and d.segment_id=S.USN(+)
;
CREATE OR REPLACE VIEW ROLLBACK2 AS SELECT
d.segment_NAME, EXTENTS, XACTS,
HWMSIZE, RSSIZE, WAITS,
WRAPS, EXTENDS
FROM
V$ROLLNAME N,
V$ROLLSTAT S,
dba_rollback_segs d
WHERE
d.segment_id=n.usn(+)
and d.segment_id=S.USN(+);
column value format 999.9999
column name format a30
column meas_date format a16
set pages 0 lines 80
start title80 'Rollback Wait Percent'
spool rep_out/&db/rbk_wait
select
name, value, to_char(meas_date,'dd-mon-yy hh24:mi') meas_date
from dba_running_stats where name like 'Rollback Wait%'
order by meas_date
/
spool off
set echo off
REM
REM FUNCTION: create views required for rbk1 and rbk2 reports.
REM
rem exit
CREATE OR REPLACE VIEW ROLLBACK1 AS
SELECT
d.segment_NAME,EXTENTS,
OPTSIZE,SHRINKS,
AVESHRINK,AVEACTIVE,
d.STATUS
FROM
V$ROLLNAME N,
V$ROLLSTAT S,
dba_rollback_segs d
WHERE
d.segment_id=n.usn(+)
and d.segment_id=S.USN(+)
;
CREATE OR REPLACE VIEW ROLLBACK2 AS SELECT
d.segment_NAME, EXTENTS, XACTS,
HWMSIZE, RSSIZE, WAITS,
WRAPS, EXTENDS
FROM
V$ROLLNAME N,
V$ROLLSTAT S,
dba_rollback_segs d
WHERE
d.segment_id=n.usn(+)
and d.segment_id=S.USN(+);
rem
rem Name: rollback.sql
rem Purpose: Generate soft documentation for rollback segments on database
rem Limitations: User must have access to dba_rollback_segs
rem:
rem History:
rem Who: What: When:
rem M. Ault Initial Creation 4/18/96
rem
column owner format a10 heading 'Owner'
column tablespace_name format a15 heading 'Tablespace|Name'
column segment_name format a30 heading 'Rollback|Segment Name'
column initial_extent heading 'Initial|Extent'
column next_extent heading 'Next|Extent'
column min_extents heading 'Minimum|Extents'
column max_extents heading 'Maximum|Extents'
column extents heading 'Current|Extents'
column optsize heading 'Optimal|Setting'
set pages 48 lines 130
break on owner on tablespace_name
start title132 "Rollback Segment Report"
spool rep_out/&db/rollback
select
owner,
tablespace_name,
segment_name ,
initial_extent,
next_extent,
min_extents,
max_extents,
extents,
optsize
from
dba_rollback_segs a, sys.v_$rollstat b
where
a.segment_id=b.usn
order by
owner,
tablespace_name
/
spool off
set pages 22 lines 80
clear columns
clear breaks
ttitle off
pause Press enter to continue
column what format a35 heading 'Job Content'
column job format 99999 heading 'Job #'
column next_date format a15 heading 'Next Date'
column last_date format a15 heading 'Last Date'
column interval format a20 heading 'Interval'
column broken format a5 heading 'Broke?'
column failures heading 'Failures'
set lines 132
@title132 'Current Job Status'
spool rep_out/&db/see_jobs
select
job,
what,
failures,
to_char(next_date,'dd-mon-yy hh24:mi') next_date,
to_char(last_date,'dd-mon-yy hh24:mi') last_date,
interval,
decode(broken,'N','No','Y','Yes',broken) broken
from
dba_jobs
/
spool off
ttitle off
select
decode(b.name,null,'User',b.name) Process,
decode(s.username,null,'Internal',s.username) username,
dba_utilities.hextointeger(p.spid) Thread,p.spid non_con,p.pid
from
v$bgprocess b,
v$session s,
v$process p
where
p.addr=b.paddr(+)
and p.addr=s.paddr
order by 4
/
select name,value from v$sesstat, v$statname
where v$sesstat.statistic#=v$statname.statistic# and sid=14
/
rem Name: sgastat.sql
rem
rem FUNCTION: Report on the various SGA components
rem and their sizes
rem
rem
column sum_bytes new_value divide_by noprint
column percent format 999.99999
column bytes format 999,999,999,999
col pool header 'Pool'
set pages 60 lines 80 feedback off verify off
break on report
compute sum of bytes on report
compute sum of percent on report
select sum(value) sum_bytes from sys.v_$sga;
start title80 'SGA Component Sizes Report'
spool rep_out/&db/sga_size
select a.name,a.bytes,a.bytes/÷_by*100 Percent,pool
from sys.v_$sgastat a
order by bytes desc
/
spool off
pause Press Enter to continue
clear columns
clear breaks
set pages 22 lines 80 feedback on verify on
ttitle off
/* cal_stat.sql */
/* FUNCTION: PLSQL script cal_stat.sql, used by do_calst.sql*/
create or replace procedure just_statistics as
jdate date;
jsec number;
hr number;
minute number;
sec1 number;
sec2 number;
sec number;
startup_date varchar2(64);
dd_ratio number := 0;
R_CALLS NUMBER := 0;
H_RATIO NUMBER := 0;
SUHW_CONT NUMBER := 0;
SUBW_CONT NUMBER := 0;
UHW_CONT NUMBER := 0;
UBW_CONT NUMBER := 0;
DB_GETS NUMBER := 0;
CON_GETS NUMBER := 0;
P_READS NUMBER := 0;
SUH_WAITS NUMBER := 0;
SUB_WAITS NUMBER := 0;
UH_WAITS NUMBER := 0;
UB_WAITS NUMBER := 0;
U_CALLS NUMBER := 0;
CALLS_U NUMBER := 0;
RLOG_WAIT NUMBER := 0;
STAT_NAME varCHAR2(64);
TEMP_NAME varCHAR2(64);
STAT_VAL NUMBER := 0;
temp_value number := 0;
cursor get_latch is
SELECT
A.NAME,100.*B.SLEEPS/B.GETS
FROM
V$LATCHNAME A, V$LATCH B
WHERE
A.LATCH# = B.LATCH# AND B.SLEEPS > 0;
cursor get_totals is
select
object_type,count(*)
from
dba_objects
where
owner not in ('SYS','SYSTEM')
group by
object_type
order by
object_type;
cursor get_stat(stat in varchar2) is
select
name,value
from
v$sysstat
where
NAME = STAT;
cursor get_count(stat in varchar2) is
select
class,"COUNT"
from
v$waitstat
where
CLASS = STAT_NAME;
BEGIN
DELETE DBA_TEMP;
begin
select to_date(to_number(value,'99999999'),'J') into jdate from
v$instance where key='STARTUP TIME - JULIAN';
select (to_number(value,'9999999')) into jsec from v$instance
where key='STARTUP TIME - SECONDS';
hr:=trunc(jsec/3600,0);
minute:= trunc(((jsec/3600)-hr)*60,0);
sec:= trunc((((((jsec/3600)-hr)*60)-minute)*60),0);
startup_date:=jdate||' '||to_char(hr)||':'||to_char(minute)||':'||
to_char(sec);
insert into dba_temp values ('Startup Date: '||startup_date,0,1);
EXCEPTION
when no_data_found then
insert into dba_temp values ('Startup Date: unknown',0,1);
end;
begin
STAT_NAME := 'recursive calls';
open get_stat(stat_name);
fetch get_stat into TEMP_name, r_calls;
close get_stat;
EXCEPTION
when no_data_found then
close get_stat;
end;
begin
STAT_NAME := 'Data Dictionary Miss Percent';
select stat_name, (sum(getmisses)/sum(gets))*100 into TEMP_name, dd_ratio
from v$rowcache;
INSERT INTO DBA_TEMP VALUES (STAT_NAME, dd_ratio,17);
EXCEPTION
when no_data_found then
INSERT INTO DBA_TEMP VALUES (STAT_NAME,0,17);
commit;
end;
begin
STAT_NAME := 'user calls';
open get_stat(stat_name);
fetch get_stat into TEMP_name, u_calls;
close get_stat;
EXCEPTION
when no_data_found then
close get_stat;
end;
begin
STAT_NAME := 'db block gets';
open get_stat(stat_name);
fetch get_stat into TEMP_name, db_gets;
close get_stat;
EXCEPTION
when no_data_found then
close get_stat;
end;
begin
STAT_NAME := 'consistent gets';
open get_stat(stat_name);
fetch get_stat into TEMP_name, con_gets;
close get_stat;
EXCEPTION
when no_data_found then
close get_stat;
end;
begin
STAT_NAME := 'physical reads';
open get_stat(stat_name);
fetch get_stat into TEMP_name, p_reads;
close get_stat;
EXCEPTION
when no_data_found then
close get_stat;
end;
begin
STAT_NAME := 'system undo header';
open get_count(stat_name);
fetch get_count into TEMP_name, suh_waits;
close get_count;
EXCEPTION
when no_data_found then
close get_count;
end;
begin
STAT_NAME := 'system undo block';
open get_count(stat_name);
fetch get_count into TEMP_name, sub_waits;
close get_count;
EXCEPTION
when no_data_found then
close get_count;
end;
begin
STAT_NAME := 'undo header';
open get_count(stat_name);
fetch get_count into TEMP_name, uh_waits;
close get_count;
EXCEPTION
when no_data_found then
close get_count;
end;
begin
STAT_NAME := 'undo block';
open get_count(stat_name);
fetch get_count into TEMP_name, ub_waits;
close get_count;
EXCEPTION
when no_data_found then
close get_count;
end;
begin
CALLS_U := (R_CALLS/U_CALLS);
H_RATIO := ((DB_GETS+CON_GETS)/(DB_GETS+CON_GETS+P_READS));
SUHW_CONT := (SUH_WAITS/(DB_GETS+CON_GETS)*100);
SUBW_CONT := (SUB_WAITS/(DB_GETS+CON_GETS)*100);
UHW_CONT := (UH_WAITS/(DB_GETS+CON_GETS)*100);
UBW_CONT := (UB_WAITS/(DB_GETS+CON_GETS)*100);
STAT_NAME := 'RECURSIVE CALLS PER USER';
INSERT INTO DBA_TEMP VALUES (STAT_NAME, CALLS_U,18);
STAT_NAME := 'CUMMULATIVE HIT RATIO';
INSERT INTO DBA_TEMP VALUES (STAT_NAME, H_RATIO,2);
STAT_NAME := 'SYS UNDO HDR WAIT CONTENTION';
INSERT INTO DBA_TEMP VALUES (STAT_NAME, SUHW_CONT,3);
STAT_NAME := 'SYS UNDO BLK WAIT CONTENTION';
INSERT INTO DBA_TEMP VALUES (STAT_NAME, SUBW_CONT,3);
STAT_NAME := 'UNDO HDR WAIT CONTENTION';
INSERT INTO DBA_TEMP VALUES (STAT_NAME, UHW_CONT,3);
STAT_NAME := 'UNDO BLK WAIT CONTENTION';
INSERT INTO DBA_TEMP VALUES (STAT_NAME, UBW_CONT,3);
STAT_NAME := 'freelist';
open get_count(stat_name);
fetch get_count into TEMP_name, stat_val;
close get_count;
STAT_NAME := 'Free List Contention Ratio';
INSERT INTO DBA_TEMP VALUES (STAT_NAME, STAT_VAL/(db_gets+con_gets),18);
EXCEPTION
WHEN ZERO_DIVIDE THEN
INSERT INTO DBA_TEMP VALUES (STAT_NAME,0,32);
close get_count;
COMMIT;
when no_data_found then
INSERT INTO DBA_TEMP VALUES (STAT_NAME,0,32);
close get_count;
commit;
end;
/* If post 7.2 uncomment this next block */
/*begin
STAT_NAME := 'Latch Miss%';
select (1-((sum(sleeps)+sum(immediate_misses))/(
sum(gets)+sum(immediate_misses)+sum(immediate_gets)))*100) into stat_val
from v$latch;
INSERT INTO DBA_TEMP VALUES (STAT_NAME, STAT_VAL,4);
EXCEPTION
when no_data_found then
INSERT INTO DBA_TEMP VALUES (STAT_NAME,0,4);
commit;
end; */
begin
STAT_NAME := 'Rollback Wait %';
select (sum(waits)/sum(gets))*100 into stat_val
from v$rollstat;
INSERT INTO DBA_TEMP VALUES (STAT_NAME, STAT_VAL,5);
EXCEPTION
when no_data_found then
INSERT INTO DBA_TEMP VALUES (STAT_NAME,0,5);
commit;
end;
begin
STAT_NAME := 'Library Reload %';
select sum(reloads)/sum(pins)*100 into stat_val
from v$librarycache;
INSERT INTO DBA_TEMP VALUES (STAT_NAME, STAT_VAL,5);
EXCEPTION
when no_data_found then
INSERT INTO DBA_TEMP VALUES (STAT_NAME,0,5);
commit;
end;
begin
STAT_NAME := 'table fetch by rowid';
open get_stat(stat_name);
fetch get_stat into TEMP_name, stat_val;
close get_stat;
INSERT INTO DBA_TEMP VALUES (STAT_NAME, STAT_VAL,9);
EXCEPTION
when no_data_found then
INSERT INTO DBA_TEMP VALUES (STAT_NAME,0,9);
close get_stat;
commit;
end;
begin
STAT_NAME:='Non-Index Lookups Ratio';
select a.value/(a.value+b.value) into STAT_VAL
from v$sysstat a, v$sysstat b
where a.name='table scans (long tables)'
and b.name='table scans (short tables)';
INSERT INTO DBA_TEMP VALUES (STAT_NAME, STAT_VAL,8);
EXCEPTION
when no_data_found then
INSERT INTO DBA_TEMP VALUES (STAT_NAME,0,8);
close get_stat;
commit;
end;
begin
STAT_NAME := 'table fetch continued row';
open get_stat(stat_name);
fetch get_stat into TEMP_name, stat_val;
close get_stat;
INSERT INTO DBA_TEMP VALUES (STAT_NAME, STAT_VAL,14);
EXCEPTION
when no_data_found then
INSERT INTO DBA_TEMP VALUES (STAT_NAME,0,14);
close get_stat;
commit;
end;
begin
STAT_NAME := 'sorts (memory)';
open get_stat(stat_name);
fetch get_stat into TEMP_name, stat_val;
close get_stat;
INSERT INTO DBA_TEMP VALUES (STAT_NAME, STAT_VAL,15);
EXCEPTION
when no_data_found then
INSERT INTO DBA_TEMP VALUES (STAT_NAME,0,15);
close get_stat;
commit;
end;
begin
STAT_NAME := 'sorts (disk)';
open get_stat(stat_name);
fetch get_stat into TEMP_name, stat_val;
close get_stat;
INSERT INTO DBA_TEMP VALUES (STAT_NAME, STAT_VAL,16);
EXCEPTION
when no_data_found then
INSERT INTO DBA_TEMP VALUES (STAT_NAME,0,16);
close get_stat;
commit;
end;
begin
STAT_NAME := 'redo log space requests';
open get_stat(stat_name);
fetch get_stat into TEMP_name, stat_val;
close get_stat;
INSERT INTO DBA_TEMP VALUES (STAT_NAME, STAT_VAL,6);
EXCEPTION
when no_data_found then
INSERT INTO DBA_TEMP VALUES (STAT_NAME,0,6);
close get_stat;
commit;
end;
begin
STAT_NAME := 'redo log space wait time';
open get_stat(stat_name);
fetch get_stat into TEMP_name, stat_val;
close get_stat;
INSERT INTO DBA_TEMP VALUES (STAT_NAME, STAT_VAL, 6);
EXCEPTION
when no_data_found then
INSERT INTO DBA_TEMP VALUES (STAT_NAME,0,6);
close get_stat;
commit;
end;
begin
STAT_NAME := 'TOTAL ALLOCATED MEG';
select SUM(BYTES)/1048576 into stat_val
from dba_DATA_FILES where
STATUS = 'AVAILABLE';
INSERT INTO DBA_TEMP VALUES (STAT_NAME, STAT_VAL,25);
EXCEPTION
when no_data_found then
INSERT INTO DBA_TEMP VALUES (STAT_NAME,0,25);
commit;
end;
begin
STAT_NAME := 'TOTAL USED MEG';
select SUM(BYTES)/1048576 into stat_val
from dba_EXTENTS;
INSERT INTO DBA_TEMP VALUES (STAT_NAME, STAT_VAL,26);
EXCEPTION
when no_data_found then
INSERT INTO DBA_TEMP VALUES (STAT_NAME,0,26);
commit;
end;
begin
STAT_NAME := 'TOTAL SGA SIZE';
select STAT_NAME, SUM(b.value) into TEMP_NAME, stat_val
from v$sGA b;
INSERT INTO DBA_TEMP VALUES (STAT_NAME, STAT_VAL,31);
EXCEPTION
when no_data_found then
INSERT INTO DBA_TEMP VALUES (STAT_NAME,0,31);
commit;
end;
begin
open get_latch;
loop
fetch get_latch into stat_name,stat_val;
exit when get_latch%NOTFOUND;
INSERT INTO DBA_TEMP VALUES (STAT_NAME, STAT_VAL,33);
end loop;
close get_latch;
commit;
end;
begin
open get_totals;
loop
fetch get_totals into stat_name,stat_val;
exit when get_totals%NOTFOUND;
INSERT INTO DBA_TEMP VALUES (STAT_NAME, STAT_VAL,34);
end loop;
close get_totals;
commit;
end;
COMMIT;
END;
/
rem Code for view: SORTERS
CREATE OR REPLACE VIEW sorters as
select SYSDATE
, s.sid
, s.username
, b.extents
, b.blocks
, c.sql_text
from v$session s
, v$sort_usage b
, v$sqlarea c
where s.saddr = b.session_addr
and s.sql_address = c.address
column value format 99,999
column name format a25
column meas_date format a16
set pages 0 lines 80
start title80 'Disk Sort Activity'
spool rep_out/&db/cum_hrat
select
name, value,delta, to_char(meas_date,'dd-mon-yy hh24:mi') meas_date
from dba_running_stats where name ='sorts (disk)'
and trunc(meas_date)>to_date('&dd_mon_yy','dd-mon-yy')
order by meas_date
/
spool off
column meas_date format a17 heading 'Measurement|Date'
column value new_value size_lim noprint
set pages 0 lines 80 feedback off verify off
ttitle off
select a.value*b.value value from v$parameter a, v$parameter b
where a.name = 'cache_size_threshold' and b.name='db_block_size';
column sorts_disk format 999,999 heading 'Disk|Sorts'
column long_scans format 999,999,999 heading 'Long Scans|>&&size_lim Bytes'
column short_scans format 999,999,999 heading 'Short Scans|<&&size_lim bytes'
column total_scans format 999,999,999 heading 'Total Scans'
start title80 'Sorts and Table Scans'
spool rep_out/&db/sorts_table_scans
select to_char(a.meas_date,'dd-mon-yyyy hh24:mi') meas_date, a.value sorts_disk,
b.value long_scans, c.value short_scans, b.value+c.value total_scans
from
dba_running_stats a,
dba_running_stats b,
dba_running_stats c
where
trunc(a.meas_date)>to_date('&dd_mon_yy','dd-mon-yy')
and
a.meas_date=b.meas_date
and
b.meas_date=c.meas_date
and
a.name='sorts (disk)'
and
b.name ='table scans (long tables)'
and
c.name='table scans (short tables)'
order by meas_date
/
spool off
ttitle off
clear columns
set feedback on verify on
create or replace PROCEDURE startup_date (good_date out date)
AS
jdate DATE;
jsec NUMBER;
hr VARCHAR2(2);
sec VARCHAR2(2);
minute VARCHAR2(2);
startup_date VARCHAR2(32);
show_date DATE;
BEGIN
SELECT
TO_DATE(TO_NUMBER(value,'99999999'),'J')
INTO
jdate
FROM
v$instance
WHERE
key='STARTUP TIME - JULIAN';
SELECT
(TO_NUMBER(value,'9999999'))
INTO
jsec
FROM
v$instance
WHERE
key='STARTUP TIME - SECONDS';
hr:=TO_CHAR(TRUNC(jsec/3600,0));
minute:= TO_CHAR(TRUNC(((jsec/3600)-hr)*60,0));
sec:= TO_CHAR(TRUNC((((((jsec/3600)-hr)*60)-minute)*60),0));
IF length(hr)=1 THEN
hr:='0'||hr;
END IF;
IF length(minute)=0 THEN
minute:='0'||minute;
END IF;
IF length(sec)=1 THEN
sec:='0'||sec;
END IF;
startup_date:=jdate||' '||hr||':'||minute||':'||sec;
dbms_output.put_line('date:'||startup_date);
good_date := TO_DATE(startup_date, 'dd-mon-yy hh24:mi:ss');
show_date := TO_DATE(startup_date, 'dd-mon-yy hh24:mi:ss');
dbms_output.put_line('date:'||to_char(show_date, 'dd-mon-yy hh24:mi:ss'));
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('Startup Date: unknown');
END;
--***********************************************************
--
-- STATSPACK alert report for the DBA
--
-- Created 8/4/2000 by Donald K. Burleson
-- www.dba-oracle.com
--
-- This script is provided free-of-charge by Don Burleson
-- and no portion of this script may be sold to anyone for any reason!
--
-- This script accepts the "number of days back" as an imput parameter
--
-- This script can be scheduled to run daily via cron or OEM
-- and e-mail the results to the on-call DBA
--
--***********************************************************
set pages 9999;
set feedback off;
set verify off;
--***********************************************************
-- Alert when data buffer hit ratio is below threshold
--***********************************************************
prompt
prompt
prompt ***********************************************************
prompt When the data buffer hit ratio falls below 90%, you
prompt should consider adding to the db_block_buffer init.ora parameter
prompt
prompt See p. 171 "High Performance Oracle8 Tuning" by Don Burleson
prompt
prompt ***********************************************************
prompt
prompt
column logical_reads format 999,999,999
column phys_reads format 999,999,999
column phys_writes format 999,999,999
column "BUFFER HIT RATIO" format 999
select to_char(snap_time,'yyyy-mm-dd HH24'),
--
a.value + b.value "logical_reads",
--
c.value "phys_reads",
--
d.value "phys_writes",
round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) "BUFFER HIT RATIO"
from perfstat.stats$sysstat a, perfstat.stats$sysstat b,
perfstat.stats$sysstat c, perfstat.stats$sysstat d,
perfstat.stats$snapshot sn
where (round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) ) < 90
and snap_time > sysdate-&1
and a.snap_id = sn.snap_id
and b.snap_id = sn.snap_id
and c.snap_id = sn.snap_id
and d.snap_id = sn.snap_id
and a.statistic# = 39
and b.statistic# = 38
and c.statistic# = 40
and d.statistic# = 41 ;
--***********************************************************
-- Alert when total disk sorts are below threshold
--***********************************************************
prompt
prompt
prompt ***********************************************************
prompt When there are high disk sorts, you should investigate
prompt increasing sort_area_size, or adding indexes to force index_full scans
prompt
prompt See p. 167 "High Performance Oracle8 Tuning" by Don Burleson
prompt
prompt ***********************************************************
prompt
prompt
column sorts_memory format 999,999,999
column sorts_disk format 999,999,999
column ratio format .99999
select to_char(snap_time,'yyyy-mm-dd HH24'), a.value sorts_memory,
b.value sorts_disk, (b.value/a.value) ratio
from perfstat.stats$sysstat a, perfstat.stats$sysstat b,
perfstat.stats$snapshot sn
where
-- Where there are more than 200 disk sorts per hour
b.value > 200 and snap_time > sysdate-&1
and a.snap_id = sn.snap_id
and b.snap_id = sn.snap_id
and a.name = 'sorts (memory)'
and b.name = 'sorts (disk)' ;
--***********************************************************
-- Alert when total I/O wait count is above threshold
--***********************************************************
prompt
prompt
prompt ***********************************************************
prompt When there is high I/O waits, disk bottlenecks may exist
prompt Run iostats to find the hot disk and shuffle files to
prompt remove the contention
prompt
prompt See p. 191 "High Performance Oracle8 Tuning" by Don Burleson
prompt
prompt ***********************************************************
prompt
prompt
break on snapdate skip 2
column snapdate format a16
column filename format a40
select to_char(snap_time,'yyyy-mm-dd HH24') snapdate, filename, wait_count
from perfstat.stats$filestatxs fs, perfstat.stats$snapshot sn
where snap_time > sysdate-&1 and fs.snap_id = sn.snap_id and wait_count > 800 ;
--***********************************************************
-- Alert when average buffer busy waits exceed threshold
--***********************************************************
prompt
prompt
prompt ***********************************************************
prompt Buffer Bury Waits may signal a high update table with too
prompt few freelists. Find the offending table and add more freelists.
prompt
prompt See p. 134 "Oracle SAP Administration" by Don Burleson
prompt
prompt ***********************************************************
prompt
prompt
column buffer_busy_wait format 999,999,999
select to_char(snap_time,'yyyy-mm-dd HH24'), avg(buffer_busy_wait)
buffer_busy_wait
from perfstat.stats$buffer_pool_statistics fs, perfstat.stats$snapshot sn
where snap_time > sysdate-&1 and fs.snap_id = sn.snap_id having
avg(buffer_busy_wait) > 100
group by to_char(snap_time,'yyyy-mm-dd HH24') ;
--***********************************************************
-- Alert when total redo log space requests exceed threshold
--***********************************************************
prompt
prompt
prompt ***********************************************************
prompt High redo log space requests indicate a need to increase
prompt the log_buffer parameter
prompt
prompt
prompt ***********************************************************
prompt
prompt
column redo_log_space_requests format 999,999,999
select to_char(snap_time,'yyyy-mm-dd HH24') snap_date, a.value
redo_log_space_requests
from perfstat.stats$sysstat a, perfstat.stats$snapshot sn
where snap_time > sysdate-&1 and a.value > 300
and a.snap_id = sn.snap_id
and a.name = 'redo log space requests' ;
--***********************************************************
-- Alert when table_fetch_continued_row exceeds threshold
--***********************************************************
prompt
prompt
prompt ***********************************************************
prompt Table fetch continued row indicates chained rows, or fetches of
prompt long datatypes (long raw, blob)
prompt
prompt Investigate increasing db_block_size or reorganizing tables
prompt with chained rows.
prompt
prompt See p. 381 "High Performance Oracle8 Tuning" by Don Burleson
prompt See p. 102 "Oracle SAP Administration" by Don Burleson
prompt
prompt ***********************************************************
prompt
prompt
column table_fetch_continued_row format 999,999,999
select to_char(snap_time,'yyyy-mm-dd HH24'), avg(a.value)
table_fetch_continued_row
from perfstat.stats$sysstat a, perfstat.stats$snapshot sn
where snap_time > sysdate-&1
and a.snap_id = sn.snap_id
and a.name = 'table fetch continued row'
having avg(a.value) > 100000 group by to_char(snap_time,'yyyy-mm-dd HH24') ;
var x number;
begin
dbms_job.submit(:x,'begin dba_utilities.flush_it(85,200);
end;',sysdate,'sysdate+1/24');
commit;
end;
/
column dbname new_value db noprint
set termout off verify off echo off
select value dbname from v$parameter where name='db_name'
/
set termout on pause on
spool rep_out/&db/tune
prompt TUNING REPORT FOR DATABASE &DB
prompt
prompt TUNING MEMORY ALLOCATION
prompt
prompt SQL-Statement #1
prompt
prompt . Percent Missed should be near zero. If it is more than 1% than
prompt . you should do the following...
prompt
prompt . - allocate additional memory for the library cache
prompt . - write identical SQL statements whenever possible
prompt
prompt . Increase the "INIT" parameters "SHARED_POOL_SIZE"
prompt . "OPEN_CURSORS"
prompt
prompt . If you have no library cache misses, you may still be able to speed
prompt . execution calls by setting... "CURSOR_SPACE_FOR_TIME" = TRUE
prompt
prompt . CAUTION: If you set this parameter to TRUE and the shared pool area
prompt . runs out of space, the next user to issue a NEW SQL statement
prompt . will get a "no space in shared pool area" error.
prompt . Stopping their process...
prompt
select sum(pins) "Executions",
sum(reloads) "Cache Misses while Executing",
round(((sum(reloads) / sum(pins)) * 100),3) "Percent Missed"
from V$LIBRARYCACHE;
prompt
prompt SQL-Statement #2
prompt
prompt . Percent Missed should be less than 10%. If it is more than 10% than
prompt . you should do the following...
prompt
prompt . Increase the "INIT" parameters "SHARED_POOL_SIZE"
prompt
select sum(gets) "Data Dictionary Gets",
sum(getmisses) "Data Dictionary Get Misses",
round(((sum(getmisses) / sum(gets)) * 100),3) "Percent Missed"
from V$ROWCACHE;
prompt
prompt SQL-Statement #3
prompt
prompt . The Hit Ratio should be over 90% with the Star_Base Application...
prompt . If it is not than do the following...
prompt
prompt . Increase the "INIT" parameters "DB_BLOCK_BUFFERS"
prompt
select name, value
from V$SYSSTAT
where name in ('db block gets', 'consistent gets', 'physical reads');
prompt . Hit Ratio = 1 - (physical reads / (db block gets + consistent gets))
select round(((1 - (A.value / (B.value + C.value))) * 100),3) "Hit Ratio"
from V$SYSSTAT A, V$SYSSTAT B, V$SYSSTAT C
where A.name = 'physical reads' and
B.name = 'db block gets' and
C.name = 'consistent gets';
prompt
prompt TUNING DISK I/O
prompt
prompt SQL-Statement #1
prompt
prompt . Total the I/O for each drive and make sure it is spread across
prompt . the drives evenly...
prompt
column name format a40
set pagesize 100
select Name "Name", Phyrds "Phyrds", Phywrts "Phywrts", phyrds + phywrts "Sum"
from V$DATAFILE df, V$FILESTAT fs
where df.file# = fs.file#
order by name;
set pagesize 22
prompt SQL-Statement #2
prompt
prompt . Dynamic Extention can cause excessive recursive calls..
prompt . Check for alot of smaller extents and reduce them to one larger extent.
prompt . Other causes are...
prompt . - Misses on the data dictionary cache
prompt . - Firing of database triggers
prompt . - Execution of Data Definition Language statements
prompt . - Execution of SQL statements within stored procedures,
prompt . functions, packages, and anonymous PL/SQL blocks.
prompt . - Enforcement of referential integrity constraints.
select name "Name", value "Value"
from V$SYSSTAT
where name = 'recursive calls';
prompt
prompt TUNING CONTENTION
prompt
prompt SQL-Statement #1
prompt
prompt . Tuning Rollback Segment Contention
prompt
prompt . Percent Waits should be less than 1%. If it is more than 1% for any
class
prompt . than you should do the following...
prompt
prompt . Increase the number of rollback segments...
prompt
prompt . Guide Lines for the Number of rollback segments...
prompt
prompt . # of Concurrent Transactions (n) | Recommended # of Rollback Segments
prompt . ___________________________________|___________________________________
prompt . n < 16 | 4 Rollback Segments
prompt . 16 <= n < 32 | 8 Rollback Segments
prompt . 32 <= n | n / 4, but no more than 50 R/S
select A.class "Class", A.count "Count", sum(B.value) "Total Requests",
round(((A.count / sum(B.value)) * 100),3) "Percent Waits"
from V$WAITSTAT A, V$SYSSTAT B
where A.class in ('system undo header', 'system undo block',
'undo header', 'undo block') and
B.name in ('db block gets', 'consistent gets')
group by A.class, A.count
order by 1;
prompt SQL-Statement #2
prompt
prompt . Tuning Redo Log Buffer Latches
prompt
prompt . Value should be near zero. If it is not then do the following...
prompt
prompt . Increase LOG_BUFFER in increments of 5% until "Value" nears zero.
select name "Name", value "Value"
from V$SYSSTAT
where name = 'redo log space requests';
prompt SQL-Statement #3
prompt
prompt . Tuning Redo Log Activity
prompt
prompt . If ((Misses / Gets) * 100) > 1% or
prompt . ((Immediate Misses / (Immediate Gets + Immediate Misses)) * 100) > 1%
prompt . for either record than...
prompt
prompt . Reducing Contention for "Redo Allocation"...
prompt . - Decrease the "INIT" parameter "LOG_SMALL_ENTRY_MAX_SIZE"...
prompt
prompt . Reducing Contention for "Redo Copy"...
prompt . - On Multi. CPU systems only...
prompt . - Increase the "INIT" parameter "LOG_SIMULTANEOUS_COPIES"...
prompt . This parameter should be no larger than (2 * # of CPU's)...
prompt . - On all systems...
prompt . - Increase "INIT" parameter "LOG_ENTRY_PREBUILD_THRESHOLD"...
prompt . This parameter is expressed in bytes...
column name format a20
select l.name "Name", gets "Gets", misses "Misses",
immediate_gets "Immediate Gets", immediate_misses "Immediate Misses"
from V$LATCH l, V$LATCHNAME ln
where ln.name in ('redo allocation', 'redo copy') and
ln.latch# = l.latch#;
prompt
prompt ADDITIONAL TUNING CONSIDERATIONS
prompt
prompt SQL-Statement #1
prompt
prompt . Tuning Sorts
prompt
prompt . If a significant number of sorts are being done on disk than...
prompt
prompt . Increase the "INIT" parameter "SORT_AREA_SIZE"...
select name "Name", value "Value"
from V$SYSSTAT
where name in ('sorts (memory)', 'sorts (disk)')
order by 1;
prompt
prompt SQL-Statement #2
prompt
prompt . Reducing Free List Contention
prompt
prompt . If Percent Wait is greater than 1% than...
prompt
prompt . Re-create the table and make "FREELISTS" equal to the number of
prompt . Oracle processes that concurrently insert data into the table...
select A.class "Class", A.count "Count",
sum(B.value) "Total Requests",
round(((A.count / sum(B.value)) * 100),3) "Percent Wait"
from V$WAITSTAT A, V$SYSSTAT B
where A.class = 'free list' and
B.name in ('db block gets', 'consistent gets')
group by A.class, A.count;
prompt
prompt ADDITIONAL IMPORTANT NOTES AND INIT PARAMETERS...
prompt
prompt . All Oracle processes should have equal priority...
prompt
prompt . To increase runtime performance and decrease recovery time the
following
prompt . can be done with "Checkpoints"...
prompt
prompt . - Set "INIT" parameter "LOG_CHECKPOINT_INTERVAL" larger than the size
prompt . of your largest redo log file...
prompt . - Set "INIT" parameter "LOG_CHECKPOINT_TIMEOUT" to zero...
prompt . - You may also increase the size of your redo log files so that the
prompt . redo log files do not fill up as quick...
prompt . - You should also set "INIT" parameter "CHECKPOINT_PROCESS" to "TRUE"
prompt . This will free up LGWR when a checkpoint occurs
prompt
prompt . Other "INIT" parameters to look at...
prompt . - "DB_BLOCK_CHECKPOINT_BATCH".
prompt . - "DB_FILE_MULTIBLOCK_READ_COUNT".
prompt . - "DB_FILE_SIMULTANEOUS_WRITES".
prompt . - "DML_LOCKS".
prompt . - "ENQUEUE_RESOURCES".
prompt . - "OPTIMIZER_MODE".
spool off
pause Press enter to continue
rem Name : TX_RBS.SQL
rem Purpose: Generate a report of active rollbacks
rem Use : From SQL*Plus
rem History:
rem Date Who What
rem Sept 91 Lan Nguyen Presented in paper at IOUG
rem Walter Lindsey
rem 5/15/93 Mike Ault Added Title80, sets and output
rem 1/04/97 Mike Ault Verified against 7.3
rem 5/16/99 Mike Ault Verified against Oracle8i
rem reformated added curext, curblk
rem*************************************************************
COLUMN name FORMAT a10 HEADING "Rollback|Segment"
COLUMN pid FORMAT 9999 HEADING "Ora|PID"
COLUMN spid FORMAT 9999 HEADING "Sys|PID"
COLUMN curext FORMAT 99999 HEADING "Curr|Extent"
COLUMN curblk FORMAT 99999 HEADING "Curr|Block"
COLUMN terminal FORMAT a10 HEADING "Terminal"
SET PAGES 56 LINES 80 VERIFY OFF FEEDBACK OFF
START title80 "Rollback Segments in Use"
SPOOL rep_out/&db/tx_rbs
SELECT
r.name, l.Sid, hextointeger(p.spid) spid,
NVL(p.username, 'no transaction') "Transaction",
p.terminal "Terminal",
s.curext,s.curblk
FROM
v$lock l,
v$process p,
v$rollname r,
v$rollstat s
WHERE
l.Sid = p.pid (+)
AND TRUNC(l.id1(+) / 65536) = r.usn
AND l.type(+) = 'TX'
AND l.lmode(+) = 6
AND r.usn=s.usn
ORDER BY r.name;
SPOOL OFF
SET PAGES 22 LINES 80 VERIFY ON FEEDBACK ON
CLEAR COLUMNS
TTITLE OFF
rem
rem FUNCTION: Report on Undo log statistics
rem
start title80 "Undo Log Statistics"
spool rep_out/&db/undo_stat
SELECT class, count
FROM v$waitstat
WHERE class IN ('free list', 'system undo header', 'system undo block',
'undo header', 'undo block')
order by class
/
spool off
pause Press enter to continue
ttitle off
@title80 'Status of DB Block Buffers'
spool rep_out/&db/vbh_status
select status,count(*) number_buffers from v$bh group by status;
spool off
ttitle off
column network format a10
column (sum(busy)/(sum(busy)+sum(idle)))*100 heading 'Busy Rate' format 999.99
column "Average Wait " format 9.99999999
select network, decode (sum(totalq),0,'No responses'),
sum(wait)/sum(totalq) ||' 100ths secs' "Average Wait "
from v$queue q, v$dispatcher d
where q.type='DISPATCHER' and q.paddr=d.paddr
group by network
/
select network, (sum(busy)/(sum(busy)+sum(idle)))*100
from v$dispatcher
group by network
/
rem
rem FUNCTION: Report on sessions waiting for locks
rem
column busername format a10 heading 'Holding|User'
column wusername format a10 Heading 'Waiting|User'
column bsession_id heading 'Holding|SID'
column wsession_id heading 'Waiting|SID'
column mode_held format a20 heading 'Mode|Held'
column mode_requested format a20 heading 'Mode|Requested'
column lock_id1 format a20 heading 'Lock|ID1'
column lock_id2 format a20 heading 'Lock|ID2'
column type heading 'Lock|Type'
set lines 132 pages 59 feedback off echo off
start title132 'Processes Waiting on Locks Report'
spool rep_out/&db/waiters
select
holding_session bsession_id,
waiting_session wsession_id,
b.username busername,
a.username wusername,
c.lock_type type,
mode_held, mode_requested,
lock_id1, lock_id2
from
sys.v_$session b, sys.dba_waiters c, sys.v_$session a
where
c.holding_session=b.sid and
c.waiting_session=a.sid
/
spool off
pause press enter/return to continue
clear columns
set lines 80 pages 22 feedback on
ttitle off
rem
rem FUNCTION: Report on sessions waiting for locks
rem
column busername format a10 heading 'Holding|User'
column wusername format a10 Heading 'Waiting|User'
column bsession_id heading 'Holding|SID'
column wsession_id heading 'Waiting|SID'
column mode_held format a20 heading 'Mode|Held'
column mode_requested format a20 heading 'Mode|Requested'
column lock_id1 format a20 heading 'Lock|ID1'
column lock_id2 format a20 heading 'Lock|ID2'
column type heading 'Lock|Type'
set lines 132 pages 59 feedback off echo off
ttitle 'Processes Waiting on Locks Report'
spool waiters
select
holding_session bsession_id,
waiting_session wsession_id,
b.username busername,
a.username wusername,
c.lock_type type,
mode_held, mode_requested,
lock_id1, lock_id2
from
sys.v_$session b, sys.dba_waiters c, sys.v_$session a
where
c.holding_session=b.sid and
c.waiting_session=a.sid
/
spool off
pause press enter/return to continue
clear columns
set lines 80 pages 22 feedback on echo on
ttitle off