Author: Yogi

 
SET LINESIZE 230
SET PAGESIZE 2300
COLUMN username FORMAT A25
COLUMN machine FORMAT A25
COLUMN logon_time FORMAT A25
SELECT NVL(s.username, '(oracle)') AS username,
s.inst_id,
s.osuser,
s.sid,
s.serial#,
p.spid,
s.lockwait,
s.status,
s.module,
s.machine,
s.program,
TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM gv$session s,
gv$process p
WHERE s.paddr = p.addr
AND s.inst_id = p.inst_id
ORDER BY s.username, s.osuser ; 

Sample Output:

 
SET LINESIZE 230
COLUMN username FORMAT A30
COLUMN module FORMAT A25
SELECT a.inst_id,
NVL(a.username,'(oracle)') AS username,
a.module,
a.program,
Trunc(b.value/1024) AS memory_kb
FROM gv$session a,
gv$sesstat b,
gv$statname c
WHERE a.sid = b.sid
AND a.inst_id = b.inst_id
AND b.statistic# = c.statistic#
AND b.inst_id = c.inst_id
AND c.name = 'session pga memory'
AND a.program IS NOT NULL
ORDER BY b.value DESC ; 

 
SET LINESIZE 230
SET PAGESIZE 300
COLUMN username FORMAT A30
COLUMN event FORMAT A30
COLUMN wait_class FORMAT A20
SELECT s.inst_id,
NVL(s.username, '(oracle)') AS username,
s.sid,
s.serial#,
sw.event,
sw.wait_class,
sw.wait_time,
sw.seconds_in_wait,
sw.state
FROM gv$session_wait sw, gv$session s
WHERE s.sid = sw.sid
AND s.inst_id = sw.inst_id
ORDER BY sw.seconds_in_wait DESC ; 

 
SET ECHO off
SET FEEDBACK 8
SET HEADING ON
SET LINESIZE 230
SET PAGESIZE 300
SET TERMOUT ON
SET TIMING OFF
SET TRIMOUT ON
SET TRIMSPOOL ON
SET VERIFY OFF
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
COLUMN disk_group_name FORMAT a20 HEAD 'Disk Group Name'
COLUMN disk_path FORMAT a20 HEAD 'Disk Path'
COLUMN reads FORMAT 999,999,999,999 HEAD 'Reads'
COLUMN writes FORMAT 999,999,999,999 HEAD 'Writes'
COLUMN read_errs FORMAT 999,999,999 HEAD 'Read|Errors'
COLUMN write_errs FORMAT 999,999,999 HEAD 'Write|Errors'
COLUMN read_time FORMAT 999,999,999,999 HEAD 'Read|Time'
COLUMN write_time FORMAT 999,999,999,999 HEAD 'Write|Time'
COLUMN bytes_read FORMAT 999,999,999,999,999 HEAD 'Bytes|Read'
COLUMN bytes_written FORMAT 999,999,999,999,999 HEAD 'Bytes|Written'
BREAK ON report ON disk_group_name SKIP 2
COMPUTE sum LABEL "" OF reads writes read_errs write_errs read_time write_time bytes_read bytes_written ON disk_group_name
COMPUTE sum LABEL "Grand Total: " OF reads writes read_errs write_errs read_time write_time bytes_read bytes_written ON report
SELECT
a.name disk_group_name
, b.path disk_path
, b.reads reads
, b.writes writes
, b.read_errs read_errs
, b.write_errs write_errs
, b.read_time read_time
, b.write_time write_time
, b.bytes_read bytes_read
, b.bytes_written bytes_written
FROM v$asm_diskgroup a JOIN v$asm_disk b USING (group_number)
ORDER BY a.name ; 

 
SET ECHO OFF
SET FEEDBACK 6
SET HEADING ON
SET LINESIZE 180
SET PAGESIZE 50000
SET TERMOUT ON
SET TIMING OFF
SET TRIMOUT ON
SET TRIMSPOOL ON
SET VERIFY OFF
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
COLUMN full_path FORMAT a75 HEAD 'ASM File Name / Volume Name / Device Name'
COLUMN system_created FORMAT a8 HEAD 'System|Created?'
COLUMN bytes FORMAT 9,999,999,999,999 HEAD 'Bytes'
COLUMN space FORMAT 9,999,999,999,999 HEAD 'Space'
COLUMN type FORMAT a18 HEAD 'File Type'
COLUMN redundancy FORMAT a12 HEAD 'Redundancy'
COLUMN striped FORMAT a8 HEAD 'Striped'
COLUMN creation_date FORMAT a20 HEAD 'Creation Date'
COLUMN disk_group_name noprint
BREAK ON report ON disk_group_name SKIP 1
COMPUTE sum LABEL "" OF bytes space ON disk_group_name
COMPUTE sum LABEL "Grand Total: " OF bytes space ON report
SELECT
CONCAT('+' || db_files.disk_group_name, SYS_CONNECT_BY_PATH(db_files.alias_name, '/')) full_path
, db_files.bytes
, db_files.space
, NVL(LPAD(db_files.type, 18), '') type
, db_files.creation_date
, db_files.disk_group_name
, LPAD(db_files.system_created, 4) system_created
FROM
( SELECT
g.name disk_group_name
, a.parent_index pindex
, a.name alias_name
, a.reference_index rindex
, a.system_created system_created
, f.bytes bytes
, f.space space
, f.type type
, TO_CHAR(f.creation_date, 'DD-MON-YYYY HH24:MI:SS') creation_date
FROM
v$asm_file f RIGHT OUTER JOIN v$asm_alias a USING (group_number, file_number)
JOIN v$asm_diskgroup g USING (group_number)
) db_files
WHERE db_files.type IS NOT NULL
START WITH (MOD(db_files.pindex, POWER(2, 24))) = 0
CONNECT BY PRIOR db_files.rindex = db_files.pindex
UNION
SELECT
'+' || volume_files.disk_group_name || ' [' || volume_files.volume_name || '] ' || volume_files.volume_device full_path
, volume_files.bytes
, volume_files.space
, NVL(LPAD(volume_files.type, 18), '') type
, volume_files.creation_date
, volume_files.disk_group_name
, null
FROM
( SELECT
g.name disk_group_name
, v.volume_name volume_name
, v.volume_device volume_device
, f.bytes bytes
, f.space space
, f.type type
, TO_CHAR(f.creation_date, 'DD-MON-YYYY HH24:MI:SS') creation_date
FROM v$asm_file f RIGHT OUTER JOIN v$asm_volume v USING (group_number, file_number)
JOIN v$asm_diskgroup g USING (group_number)
) volume_files
WHERE volume_files.type IS NOT NULL ; 

 
SET ECHO off
SET FEEDBACK 8
SET HEADING on
SET LINESIZE 230
SET PAGESIZE 300
SET TERMOUT ON
SET TIMING OFF
SET TRIMOUT ON
SET TRIMSPOOL ON
SET VERIFY OFF
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
COLUMN disk_group_name FORMAT a30 HEAD 'Disk Group Name'
COLUMN disk_file_path FORMAT a20 HEAD 'Path'
COLUMN disk_file_name FORMAT a20 HEAD 'File Name'
COLUMN disk_file_fail_group FORMAT a20 HEAD 'Fail Group'
COLUMN total_mb FORMAT 999,999,999 HEAD 'File Size (MB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
BREAK ON report ON disk_group_name SKIP 1
COMPUTE sum LABEL "" OF total_mb used_mb ON disk_group_name
COMPUTE sum LABEL "Grand Total: " OF total_mb used_mb ON report
SELECT
NVL(a.name, '[CANDIDATE]') disk_group_name
, b.path disk_file_path
, b.name disk_file_name
, b.failgroup disk_file_fail_group
, b.total_mb total_mb
, (b.total_mb - b.free_mb) used_mb
, ROUND((1- (b.free_mb / b.total_mb))*100, 2) pct_used
FROM v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number)
ORDER BY a.name ;

 

 
SET LINESIZE 230
 SET PAGESIZE 500
 SET VERIFY off
 COLUMN group_name FORMAT a20 HEAD 'Disk Group|Name'
 COLUMN sector_size FORMAT 99,999 HEAD 'Sector|Size'
 COLUMN block_size FORMAT 99,999 HEAD 'Block|Size'
 COLUMN allocation_unit_size FORMAT 999,999,999 HEAD 'Allocation|Unit Size'
 COLUMN state FORMAT a11 HEAD 'State'
 COLUMN type FORMAT a6 HEAD 'Type'
 COLUMN total_mb FORMAT 999,999,999 HEAD 'Total Size (MB)'
 COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
 COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
 break on report on disk_group_name skip 1
 compute sum label "Grand Total: " of total_mb used_mb on report

 SELECT
 name group_name
 , sector_size sector_size
 , block_size block_size
 , allocation_unit_size allocation_unit_size
 , state state
 , type type
 , total_mb total_mb
 , (total_mb - free_mb) used_mb
 , ROUND((1- (free_mb / total_mb))*100, 2) pct_used
 FROM v$asm_diskgroup ORDER BY name ; 
 

 
SET LINESIZE 230
SET PAGESIZE 2300
COLUMN username FORMAT A25
COLUMN machine FORMAT A25
COLUMN logon_time FORMAT A25
SELECT NVL(s.username, '(oracle)') AS username,
s.inst_id,
s.osuser,
s.sid,
s.serial#,
p.spid,
s.lockwait,
s.status,
s.module,
s.machine,
s.program,
TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM gv$session s,
gv$process p
WHERE s.paddr = p.addr
AND s.inst_id = p.inst_id
ORDER BY s.username, s.osuser ; 

Sample Output:

Script will list below information with comments

Dictionary Cache Hit Ratio
Library Cache Hit Ratio
DB Block Buffer Cache Hit Ratio
Latch Hit Ratio
Disk Sort Ratio
Rollback Segment Waits
Dispatcher Workload

 
SET SERVEROUTPUT ON
SET LINESIZE 280
SET FEEDBACK OFF
SELECT *
FROM v$database;
PROMPT
DECLARE
v_value NUMBER;
FUNCTION Format(p_value IN NUMBER)
RETURN VARCHAR2 IS
BEGIN
RETURN LPad(To_Char(Round(p_value,2),'990.00') || '%',8,' ') || ' ';
END;
BEGIN
-- --------------------------
-- Dictionary Cache Hit Ratio
-- --------------------------
SELECT (1 - (Sum(getmisses)/(Sum(gets) + Sum(getmisses)))) * 100
INTO v_value
FROM v$rowcache;
DBMS_Output.Put('Dictionary Cache Hit Ratio : ' || Format(v_value));
IF v_value < 90 THEN
DBMS_Output.Put_Line('Increase SHARED_POOL_SIZE parameter to bring value above 90%');
ELSE
DBMS_Output.Put_Line('Value Acceptable.');
END IF;
-- -----------------------
-- Library Cache Hit Ratio
-- -----------------------
SELECT (1 -(Sum(reloads)/(Sum(pins) + Sum(reloads)))) * 100
INTO v_value
FROM v$librarycache;
DBMS_Output.Put('Library Cache Hit Ratio : ' || Format(v_value));
IF v_value < 99 THEN
DBMS_Output.Put_Line('Increase SHARED_POOL_SIZE parameter to bring value above 99%');
ELSE
DBMS_Output.Put_Line('Value Acceptable.');
END IF;
-- -------------------------------
-- DB Block Buffer Cache Hit Ratio
-- -------------------------------
SELECT (1 - (phys.value / (db.value + cons.value))) * 100
INTO v_value
FROM v$sysstat phys,
v$sysstat db,
v$sysstat cons
WHERE phys.name = 'physical reads'
AND db.name = 'db block gets'
AND cons.name = 'consistent gets';
DBMS_Output.Put('DB Block Buffer Cache Hit Ratio : ' || Format(v_value));
IF v_value < 89 THEN
DBMS_Output.Put_Line('Increase DB_BLOCK_BUFFERS parameter to bring value above 89%');
ELSE
DBMS_Output.Put_Line('Value Acceptable.');
END IF;
-- ---------------
-- Latch Hit Ratio
-- ---------------
SELECT (1 - (Sum(misses) / Sum(gets))) * 100
INTO v_value
FROM v$latch;
DBMS_Output.Put('Latch Hit Ratio : ' || Format(v_value));
IF v_value < 98 THEN DBMS_Output.Put_Line('Increase number of latches to bring the value above 98%'); ELSE DBMS_Output.Put_Line('Value Acceptable.'); END IF; -- ----------------------- -- Disk Sort Ratio -- ----------------------- SELECT (disk.value/mem.value) * 100 INTO v_value FROM v$sysstat disk, v$sysstat mem WHERE disk.name = 'sorts (disk)' AND mem.name = 'sorts (memory)'; DBMS_Output.Put('Disk Sort Ratio : ' || Format(v_value)); IF v_value > 5 THEN
DBMS_Output.Put_Line('Increase SORT_AREA_SIZE parameter to bring value below 5%');
ELSE
DBMS_Output.Put_Line('Value Acceptable.');
END IF;
-- ----------------------
-- Rollback Segment Waits
-- ----------------------
SELECT (Sum(waits) / Sum(gets)) * 100
INTO v_value
FROM v$rollstat;
DBMS_Output.Put('Rollback Segment Waits : ' || Format(v_value));
IF v_value > 5 THEN
DBMS_Output.Put_Line('Increase number of Rollback Segments to bring the value below 5%');
ELSE
DBMS_Output.Put_Line('Value Acceptable.');
END IF;
-- -------------------
-- Dispatcher Workload
-- -------------------
SELECT NVL((Sum(busy) / (Sum(busy) + Sum(idle))) * 100,0)
INTO v_value
FROM v$dispatcher;
DBMS_Output.Put('Dispatcher Workload : ' || Format(v_value));
IF v_value > 50 THEN
DBMS_Output.Put_Line('Increase MTS_DISPATCHERS to bring the value below 50%');
ELSE
DBMS_Output.Put_Line('Value Acceptable.');
END IF;
END;
/