prompt **********************************************************
prompt * Health Checker *
prompt **********************************************************
set feedback off
clear columns;
column timecol new_value today
column spool_extension new_value suffix
select to_char(sysdate,'YYYYMMDD') timecol,
'.txt' spool_extension from sys.dual;
column output new_value inst_name
select value || '_' output
from v$parameter where name = 'instance_name';
set linesize 160
set pages 150
spool health_&&inst_name&&today&&suffix
prompt
prompt *******************************************************
prompt * host info *
prompt *******************************************************
prompt
col Host format a15
col version for a10
col InstID for 99
select name "DB Name", instance_number "InstID", instance_name "Inst Name",
host_name "Host", version "Version", TO_CHAR(startup_time, 'YYYY-MM-DD HH24:MI:SS') "Startup Time",
parallel "Parallel", log_mode "Log Mode", archiver "Archiver"
from v$instance, v$database
/
prompt
prompt *******************************************************
prompt * parameter info *
prompt *******************************************************
prompt
col name for a35
col value for a50
select name, value from v$parameter
where name IN ('cpu_count', 'db_name', 'db_block_size','db_cache_size', 'shared_pool_size','large_pool_size', 'java_pool_size', 'log_buffer');
select name, value from v$parameter order by 1;
clear columns;
prompt
prompt *******************************************************
prompt * Memory Size *
prompt *******************************************************
select decode(pool,null,name,pool) as POOL ,trunc(sum(bytes)/1024/1024,1) as MB
from v$sgastat group by decode(pool,null,name,pool) order by 1
/
prompt
prompt *******************************************************
prompt * version info *
prompt *******************************************************
prompt
col comp_name for a40;
col version for a30;
select * from v$version;
select comp_name, version, status from dba_registry;
clear columns;
prompt
prompt
prompt
clear columns;
prompt
prompt *******************************************************
prompt * archived log mode *
prompt *******************************************************
prompt
select name, log_mode from v$database;
archive log list;
prompt
prompt *******************************************************
prompt * HOT BACKUP INFO *
prompt *******************************************************
prompt
col tablespace_name for a25
col file_name for a40
col BACKUP_TIME for a30
select /*+ use_nl (a, b) */ file#, tablespace_name, a.status, change#, to_char(TIME, 'YYYY-MM-DD HH24:MI') as BACKUP_TIME
from v$backup a, dba_data_files b
where a.file#=b.file_id
order by 1, 2, 3;
clear columns;
prompt
prompt *******************************************************
prompt * Resource Info *
prompt *******************************************************
prompt
select RESOURCE_NAME "Resource Name",
CURRENT_UTILIZATION "Current Util",
MAX_UTILIZATION "Max Util",
INITIAL_ALLOCATION "Initial Alloc",
LIMIT_VALUE "Limit"
from v$resource_limit
/
clear columns;
prompt *******************************************************
prompt * Library Cache Hit Ratio (Namespace) *
prompt *******************************************************
prompt
col "Gets" for 999,999,999,999
col "Pins" for 999,999,999,999
select namespace "Namespace",
gets "Gets", round(gethitratio*100,3) "GetHit Ratio",
pins "Pins", round(pinhitratio*100,3) "PinHit Ratio",
invalidations "Invalidations",
decode(pins,0,0,round((1-reloads/pins)*100, 3)) "Hit Ratio(Pins-Reloads)"
from v$librarycache
/
clear columns;
prompt
prompt *******************************************************
prompt * DATA DICTIONARY CACHE TUNING *
prompt *******************************************************
prompt
select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') "Time",
trunc(sum(getmisses)/sum(gets)*100, 5)||'% (less than 9.8%)'
"Data dictionary miss ratio "
from v$rowcache
/
prompt
prompt *******************************************************
prompt * Dictionary Cache Hit Ratio (Parameter) *
prompt *******************************************************
prompt
SELECT parameter "Parameter",
sum(gets) "Gets",
sum(getmisses) "GetMisses" ,
decode(sum(gets), 0, 0, round((1-sum(getmisses)/sum(gets))*100, 3)) "Hit Ratio"
FROM v$rowcache
GROUP BY parameter
/
prompt
prompt *******************************************************
prompt * Shared Pool Free Space *
prompt *******************************************************
prompt
col "Shared Pool Reserved(MB)" format a40
select s.total "Shared Pool Total(MB)",
s.free - r.r_free "Shared Pool Free(MB)",
'Free : ' || r.r_free || ', Used : ' ||r.r_used "Shared Pool Reserved(MB)",
s.other - r.r_used "Shared Pool Other(MB)"
from (select round(sum(bytes/1024/1024),2) total,
round(sum(decode(name, 'free memory', bytes))/1024/1024,2) free,
round(sum(decode(name, 'free memory',null, bytes))/1024/1024,2) other
from v$sgastat
where pool = 'shared pool' ) s,
(select round(nvl(sum(free_space),0)/1024/1024,2) r_free,
round(nvl(sum(used_space),0)/1024/1024,2) r_used
from v$shared_pool_reserved ) r
/
prompt
prompt *******************************************************
prompt * Shared Pool Advice *
prompt *******************************************************
prompt
select SHARED_POOL_SIZE_FOR_ESTIMATE "Shared Pool Size(MB)" ,SHARED_POOL_SIZE_FACTOR "Size Factor",
ESTD_LC_SIZE "Estd Lib Cache Size", ESTD_LC_MEMORY_OBJECTS "Cached Objects",
ESTD_LC_TIME_SAVED "Saved Time", ESTD_LC_TIME_SAVED_FACTOR "Saved Time Factor", ESTD_LC_MEMORY_OBJECT_HITS "Object Hits"
from v$shared_pool_advice
/
prompt
prompt *******************************************************
prompt * BUFFER CACHE TUNING *
prompt *******************************************************
prompt
col "Logical Reads" for 999,999,999,999
col "Physical Reads" for 999,999,999,999
select to_char(sysdate,'YYYY/MM/DD HH24:MI:SS') "Time",
sum(decode(name,'session logical reads', value)) "Logical Reads",
sum(decode(name,'physical reads', value)) "Physical Reads",
sum(decode(name,'physical reads direct', value)) "Phy-Reads Direct",
sum(decode(name,'physical reads direct (lob)', value)) "Phy-Reads Direct(LOB)",
round((1 - (sum(decode(name,'physical reads', value))
- sum(decode(name,'physical reads direct', value))
- sum(decode(name,'physical reads direct (lob)', value)))
/ sum(decode(name,'session logical reads', value))) * 100, 3) "Hit Ratio"
from v$sysstat
/
clear columns;
prompt
prompt *******************************************************
prompt * LRU Queue Average Reusable Buffer *
prompt *******************************************************
prompt
select a.value "free buffer found", b.value "free buffer requests", round(decode(b.value, 0, 0, (a.value / b.value)),1) "Average Reusable Buffer"
from v$sysstat a, v$sysstat b
where a.name = 'DBWR free buffers found'
and b.name = 'DBWR make free requests'
/
prompt
prompt *******************************************************
prompt * Average LRU Buffer Scan *
prompt *******************************************************
prompt
select round(decode(b.value, 0, 0, (a.value / b.value)),1) "Average LRU Buffer Scan"
from v$sysstat a, v$sysstat b
where a.name = 'DBWR buffers scanned'
and b.name = 'DBWR lru scans'
/
prompt
prompt *******************************************************
prompt * Free Buffer Scan Ratio *
prompt *******************************************************
prompt
select round(decode(b.value, 0, -1, (a.value / b.value)*100),2) "Free Buffer Scan Ratio"
from v$sysstat a, v$sysstat b
where a.name = 'free buffer inspected'
and b.name = 'free buffer requested'
/
prompt
prompt *******************************************************
prompt * Buffer Cache Advice *
prompt *******************************************************
prompt
select size_for_estimate "Cache Size (MB)", size_factor "Size Factor", buffers_for_estimate "Buffers",
estd_physical_read_factor "Estd Phys Read Factor", estd_physical_reads "Estd Phys Reads"
from v$db_cache_advice where advice_status = 'ON' and block_size = (select value from v$parameter where name = 'db_block_size') and name = 'DEFAULT'
order by 1,2
/
prompt
prompt *******************************************************
prompt * INTERNAL SORT AND EXTERNAL SORT *
prompt *******************************************************
prompt
select a.value "Sort(memory)",
b.value "Sort(disk)",
round(a.value/(a.value+b.value) * 100 ,2) "Memory Sort Hit Ratio"
from v$sysstat a, v$sysstat b
where a.name = 'sorts (memory)'
and b.name = 'sorts (disk)'
/
prompt
prompt *******************************************************
prompt * Rows per Sort *
prompt *******************************************************
prompt
select round(a.value / (b.value + c.value),2) "Rows per Sort"
from v$sysstat a, v$sysstat b, v$sysstat c
where a.name = 'sorts (rows)'
and b.name = 'sorts (disk)'
and c.name = 'sorts (memory)'
/
prompt
prompt *******************************************************
prompt * Sort Segment Usage *
prompt *******************************************************
prompt
SELECT tablespace_name "Tablespace Name",
extent_size "Extent Size",
current_users "Current Users",
total_extents "Total Exts",
used_extents "Used Exts",
free_extents "Free Exts",
max_used_size "Max Used Exts"
FROM v$sort_segment
/
prompt
prompt *******************************************************
prompt * PGA Statistics *
prompt *******************************************************
prompt
col name for a40
col value for 999,999,999,999,999
select name, value, 'bytes' unit from v$sysstat
where name like 'session pga memory%'
union all
select name, value, unit from v$pgastat
/
prompt
prompt *******************************************************
prompt * SQL Workarea Statistics *
prompt *******************************************************
prompt
select name "Profile", cnt "Count",
decode(total, 0, 0, round(cnt*100/total)) "Percentage"
from (select name, value cnt,
(sum(value) over ()) total
from v$sysstat
where name like 'workarea executions%')
/
prompt
prompt *******************************************************
prompt * PGA Target Advice *
prompt *******************************************************
prompt
select round(pga_target_for_estimate/1024/1024) "Target Size(MB)",
pga_target_factor "Target Factor", estd_pga_cache_hit_percentage "PGA Cache Hit(%%)",
estd_overalloc_count "Over-allocation Count"
from v$pga_target_advice where advice_status = 'ON'
/
prompt
prompt *******************************************************
prompt * ROLLBACK SEGMENT`S WAIT RATIO *
prompt *******************************************************
prompt
col "Rollback Segment" for a20
col "Miss Ratio" for a15
col "Rsize" for 999,999,999,999
col "Writes" for 999,999,999,999
col "Gets" for 999,999,999,999
select name "Rollback Segment",
rssize "Rsize", writes "Writes", xacts "Xacts", status "Status",
waits "Waits", gets "Gets",
trunc(waits/gets*100, 5)||'%' "Miss Ratio"
from v$rollstat a, v$rollname b
where a.usn = b.usn
order by waits/gets desc
/
clear columns;
prompt
prompt *******************************************************
prompt * REDO LOG FILE`S WAIT RATIO *
prompt *******************************************************
prompt
select round(sum(decode(name, 'redo buffer allocation retries', value))/sum(decode(name, 'redo entries', value)),5)
"Redo Buffer Space Wait(%%)",
sum(decode(name, 'redo log space requests', value)) "Redo Space Reqeusts"
from v$sysstat
/
col name for a30
col value for a40
select name, value from v$parameter
where name IN('log_buffer', 'log_checkpoint_interval', 'log_checkpoint_timeout','fast_start_mttr_target');
clear columns;
prompt
prompt *******************************************************
prompt * Redo Entry Average Size *
prompt *******************************************************
prompt
select round(decode(b.value, 0, -1, a.value / b.value),1) "Redo Entry Average Size(bytes)"
from v$sysstat a, v$sysstat b
where a.name = 'redo size'
and b.name = 'redo entries'
/
prompt
prompt *******************************************************
prompt * Log Buffer Retry Ratio *
prompt *******************************************************
prompt
select round(decode(b.value, 0, -1, a.value / b.value * 100),6) "Log Buffer Retry Ratio(%%)"
from v$sysstat a, v$sysstat b
where a.name = 'redo buffer allocation retries'
and b.name = 'redo entries'
/
prompt
prompt *******************************************************
prompt * Redo Log Switch Count(Daily) 1 week *
prompt *******************************************************
prompt
select thread#, to_char(first_time, 'yyyy/mm/dd') "Time", count(*) "Count"
from v$loghist
where first_time > sysdate -7
group by thread#, to_char(first_time, 'yyyy/mm/dd')
order by 1, 2
/
select thread#, count(*) "1 Week Count"
from v$loghist
where first_time > sysdate -7
group by thread#
order by 1
/
prompt
prompt *******************************************************
prompt * Latch Hit Ratio *
prompt *******************************************************
select * from (select name "Name",
decode(sign(gets-immediate_gets), 1, gets, immediate_gets) "(I)Gets",
decode(sign(gets-immediate_gets), 1, misses, immediate_misses) "(I)Misses",
sleeps "Sleeps",
decode(sign(gets-immediate_gets), 1, round(decode(gets,0,-1,(1-misses/gets) * 100),3),
round(decode(immediate_gets+immediate_misses,0,-1,
(1- immediate_misses/ (immediate_gets+immediate_misses)) * 100), 3 )) "Hit Ratio"
from v$latch
where decode(sign(gets-immediate_gets), 1, gets, immediate_gets) > 0
order by 5, 2 desc) where rownum <= 10
/
clear columns;
prompt
prompt *******************************************************
prompt * Session Count *
prompt *******************************************************
col username for a15
col machine for a25
select username, program, machine, count(*) as session_count
from v$session
where username is not null
group by username, program, machine
/
select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') "Time",
count(*) "Total Sessions",
count(decode(status, 'ACTIVE',1) ) "Active Sessions"
from v$session
where username is not null
/
clear columns;
rem prompt
rem prompt *******************************************************
rem prompt * Session Cursor Cache Hit Ratio *
rem prompt *******************************************************
rem col "Cursor Cache Hits(%%)" for a21
rem select
rem round(100 * (calls - sess - hard) / calls, 3) "Soft Parses(%%)",
rem round(100 * hard / calls, 3) "Hard Parses(%%)",
rem decode(round(100 * sess / calls, 3),0,decode(parm,0,to_char('N/A'),0),round(100 * sess / calls, 3))
rem "Cursor Cache Hits(%%)"
rem from
rem ( select value calls from v$sysstat where name = 'parse count (total)' ),
rem ( select value hard from v$sysstat where name = 'parse count (hard)' ),
rem ( select value sess from v$sysstat where name = 'session cursor cache hits' ),
rem ( select value parm from v$parameter where name = 'session_cached_cursors')
rem /
rem clear columns;
prompt
prompt *******************************************************
prompt * Cursors opened per transaction *
prompt *******************************************************
select decode(b.value, 0, -1, round(a.value/b.value)) "Cursors opened per transaction"
from v$sysstat a, v$sysstat b
where a.name = 'opened cursors cumulative'
and b.name = 'user commits'
/
prompt
prompt *******************************************************
prompt * Recursive to User Call Ratio *
prompt *******************************************************
select decode(b.value, 0, -1, round(a.value/b.value*100)) "Recur to User Call Ratio(%%)"
from v$sysstat a, v$sysstat b
where a.name = 'recursive calls'
and b.name = 'user calls'
/
prompt
prompt *******************************************************
prompt * Transaction Rate (TPS) *
prompt *******************************************************
select round((a.value+b.value) / ((sysdate - (select startup_time from v$instance)) * 24 * 3600),2) "Transaction Rate"
from v$sysstat a, v$sysstat b
where a.name = 'user commits'
and b.name = 'user rollbacks'
/
prompt
prompt *******************************************************
prompt * Parse Count per User Calls *
prompt *******************************************************
select decode(b.value, 0, -1, round(a.value/b.value)) "Parse Count per User Calls"
from v$sysstat a, v$sysstat b
where a.name = 'parse count (total)'
and b.name = 'user calls'
/
prompt
prompt *******************************************************
prompt * Active Transaction *
prompt *******************************************************
col username for a10
col "Machine" for a15
col "Pgm" for a15
col "RBS" for a10
col status for a10
col sid for 9999
col "Pgm" for a15
select /*+ ordered */ s.username,
s.sid sid ,
substr(s.machine,1,15) "Machine" ,
substr(s.program,1,15) "Pgm",
t.status "Status",
RECURSIVE "Recur",
to_char(to_date(t.start_time, 'mm/dd/yy HH24:MI:SS'), 'yyyy/mm/dd hh24:mi:ss') "StartTime",
r.name "RBS",
t.used_ublk "UBLKS",
t.used_urec "URECS",
t.log_io "L_IO",
t.PHY_IO "P_IO"
from v$transaction t, v$session s, v$rollname r, v$process p
where s.saddr = t.ses_addr and t.xidusn = r.usn
and s.paddr = p.addr
order by used_ublk
/
clear columns;
prompt
prompt *******************************************************
prompt * Pending Distributed Transaction *
prompt *******************************************************
col host for a10
col global_tran_id for a30
select local_tran_id, global_tran_id, state, mixed, host, commit#
from dba_2pc_pending;
col database for a30
select local_tran_id, in_out, database, dbuser_owner, interface
from dba_2pc_neighbors;
prompt
prompt *******************************************************
prompt * System Wait Event *
prompt *******************************************************
col event format a35
select *
from (select event,
total_waits "Total Waits",
time_waited "Time Waited(cs)",
round(average_wait,5) "Avg Wait Time(cs)",
total_timeouts "Total Timeouts"
from v$system_event
where event not in ('SQL*Net message to client','SQL*Net more data to client','SQL*Net message from client',
'SQL*Net more data from client','SQL*Net break/reset to client','rdbms ipc message','rdbms ipc reply',
'SQL*Net message from dblink','SQL*Net more data from dblink','SQL*Net message to dblink',
'pipe get','pipe put','smon timer','pmon timer','dispatcher timer','PL/SQL lock timer','wakeup time manager',
'ges remote message','gcs remote message','i/o slave wait','jobq slave wait','slave wait','class slave wait',
'PX Idle Wait','PX Deque wait','PX Deq: Execution Msg','PX Deq: Table Q Normal','PX Deq Credit: need buffer','PX Deq Credit: send blkd',
'PX Deq: Par Recov Reply','PX Deq: Par Recov Execute','PX Deq: Par Recov Change Vector','PX Deq: Join ACK','PX Deq: Execute Reply','PX Deq: Parse Reply',
'PX Deq: reap credit','PX Deq: Signal ACK','PX Deq: Msg Fragment','PX Deq: Table Q Get Keys',
'Queue Monitor Slave Wait','Queue Monitor Wait','Queue Monitor Shutdown Wait','Queue Monitor IPC wait','single-task message',
'Streams AQ: waiting for messages in the queue','Streams AQ: waiting for time management or cleanup tasks',
'Streams AQ: qmn coordinator idle wait','Streams AQ: qmn slave idle wait','virtual circuit status','Null event')
and time_waited > 0
order by time_waited desc)
where rownum < 21
/
prompt
prompt *******************************************************
prompt * Invalid Obecjt *
prompt *******************************************************
prompt
SELECT count(*) as "ALL Count"
FROM DBA_OBJECTS
WHERE STATUS = 'INVALID'
/
select
owner, object_type,
count(*) "Invalid Count"
from dba_objects
where status = 'INVALID'
group by owner, object_type
order by 1, 2
/
prompt
prompt *******************************************************
prompt * User Objects in SYSTEM Tablespace *
prompt *******************************************************
prompt
select owner "Owner", segment_name "Segment Name", segment_type "Segment Type"
from dba_segments
where tablespace_name = 'SYSTEM'
and owner not in ('SYSTEM', 'SYS', 'OUTLN', 'DBSNMP', 'MDSYS', 'ORDSYS','OLAPDBA', 'ORDPLUGINS',
'OLAPSVR', 'OSE$HTTP$ADMIN', 'AURORA$ORB$UNAUTHENTICATED', 'AURORA$JIS$UTILITY$','WMSYS')
/
prompt *******************************************************
prompt * Index Scan Ratio *
prompt *******************************************************
prompt
select
round(sum(decode(name, 'table fetch by rowid', value)) /
(sum(decode(name, 'table scan rows gotten', value)) + sum(decode(name, 'table fetch by rowid', value)))*100,2) "Index Scan Ratio"
from v$sysstat
/
prompt *******************************************************
prompt * Session Index Scan Ratio *
prompt *******************************************************
prompt
select /*+ ordered use_hash(c) swap_join_inputs(c) */ s.sid, s.program "Program",
sum(decode(c.name, 'table fetch by rowid', a.value)) "Index Scan",
sum(decode(c.name, 'table scan rows gotten',a.value)) "Full Scan",
decode(sum(decode(c.name, 'table fetch by rowid', a.value))+sum(decode(c.name, 'table scan rows gotten',a.value)),0,
-1, round(sum(decode(c.name, 'table fetch by rowid', a.value))/
(sum(decode(c.name, 'table fetch by rowid', a.value))+sum(decode(c.name, 'table scan rows gotten',a.value)))*100,2)) "Index Scan Ratio"
from v$session s, v$sesstat a, v$statname c
where c.name in ( 'table scan rows gotten', 'table fetch by rowid' )
and a.statistic# = c.statistic#
and s.sid = a.sid
and s.type != 'BACKGROUND'
and s.module <> 'Orange for ORACLE'
group by s.sid, s.program
order by 5
/
prompt *******************************************************
prompt * Chained Row Ratio *
prompt *******************************************************
prompt
SELECT sum(decode(name,'table fetch continued row',value,0)) "Chained row Access",
sum(decode(name,'table fetch by rowid',value,0)) "Index Access",
sum(decode(name,'table scan rows gotten',value,0)) "Table Scan Access",
round(sum(decode(name,'table fetch continued row',value,0)) /
(sum(decode(name,'table fetch by rowid',value,0)) + sum(decode(name,'table scan rows gotten',value,0))) * 100,5) "Ratio"
FROM v$sysstat
/
prompt *******************************************************
prompt * TOP-20 SQL (by Buffer Gets) *
prompt *******************************************************
prompt
col username for a12
col "HASH_VALUE" for a12
col executions for 99,999,999
col buffer_gets for 99,999,999,999
col disk_reads for 999,999,999
col "RowsPerExec" for 999,999,999
col parse_calls for 99,999,999
col "SQL_TEXT" for a120
select * from (
select u.username, sharable_mem, executions, parse_calls, disk_reads, buffer_gets, round(decode(executions,0,-1,rows_processed/executions)) "RowsPerExec",
round(elapsed_time/(decode(executions,0,-1000000,executions*1000000)),2) "Elap-Time(Sec)", optimizer_mode, to_char(hash_value) "HASH_VALUE", --address, child_number,
substr(sql_text,1,120) "SQL_TEXT"
from v$sql s, all_users u
where s.parsing_user_id = u.user_id
and u.username <> 'SYS'
and u.username <> 'SYSTEM'
and command_type <> 47
order by buffer_gets desc)
where rownum <= 20
/
prompt *******************************************************
prompt * TOP-20 SQL (by Buffer Gets per Execution)*
prompt *******************************************************
prompt
select * from (
select u.username, sharable_mem, executions, parse_calls, disk_reads,
trunc(decode(executions, 0, -1, (buffer_gets/executions))) "BufferGets/Exec", round(decode(executions,0,-1,rows_processed/executions)) "RowsPerExec",
round(elapsed_time/(decode(executions,0,-1000000,executions*1000000)),2) "Ela-Time(Sec)",
optimizer_mode, to_char(hash_value) "HASH_VALUE", --address, child_number,
substr(sql_text,1,120) "SQL_TEXT"
from v$sql s, all_users u
where s.parsing_user_id = u.user_id
and u.username <> 'SYS'
and u.username <> 'SYSTEM'
and command_type <> 47
order by decode(executions, 0, -1, (buffer_gets/executions)) desc)
where rownum <= 20
/
prompt *******************************************************
prompt * TOP-20 SQL (by Disk Reads) *
prompt *******************************************************
prompt
select * from (
select u.username, sharable_mem, executions, parse_calls, disk_reads, buffer_gets, round(decode(executions,0,-1,rows_processed/executions)) "RowsPerExec",
round(elapsed_time/(decode(executions,0,-1000000,executions*1000000)),2) "Elap-Time(Sec)", optimizer_mode, to_char(hash_value) "HASH_VALUE", --address, child_number,
substr(sql_text,1,120) "SQL_TEXT"
from v$sql s, all_users u
where s.parsing_user_id = u.user_id
and u.username <> 'SYS'
and u.username <> 'SYSTEM'
and command_type <> 47
order by disk_reads desc)
where rownum <= 20
/
prompt *******************************************************
prompt * TOP-20 SQL (by Elapsed Time) *
prompt *******************************************************
prompt
select * from (
select u.username, sharable_mem, executions, parse_calls, disk_reads, buffer_gets, round(decode(executions,0,-1,rows_processed/executions)) "RowsPerExec",
round(elapsed_time/(decode(executions,0,-1000000,executions*1000000)),2) "Elap-Time(Sec)", optimizer_mode, to_char(hash_value) "HASH_VALUE", --address, child_number
substr(sql_text,1,120) "SQL_TEXT"
from v$sql s, all_users u
where s.parsing_user_id = u.user_id
and u.username <> 'SYS'
and u.username <> 'SYSTEM'
and command_type <> 47
order by round(elapsed_time/(decode(executions,0,-1000000,executions*1000000)),2) desc)
where rownum <= 20
/
clear columns;
prompt *******************************************************
prompt * Literal SQL *
prompt *******************************************************
prompt
col "SQL_TEXT" for a70
col "HASH_VALUE" for a12
select substr(sql_text, 1, 70) "SQL_TEXT",
count(*) "Count",
sum(executions) "TotExecs",
sum(sharable_mem) "Mem",
--to_char(to_date(min(first_load_time), 'yyyy-mm-dd/hh24:mi:ss'), 'YYYY/MM/DD hh24:mi:ss') "Start Time",
--to_char(to_date(max(first_load_time), 'yyyy-mm-dd/hh24:mi:ss'), 'YYYY/MM/DD hh24:mi:ss') "End TIme",
to_char(max(hash_value)) "HASH_VALUE"
from v$sql
where executions < 5
group by substr(sql_text, 1, 70)
having count(*) > 30
order by 2 desc
/
clear columns;
prompt
prompt *******************************************************
prompt * Tablespace Fragments *
prompt *******************************************************
prompt
select tablespace_name
,total_extents
,percent_extents_coalesced || '% Coalesced'
from dba_free_space_coalesced
where percent_extents_coalesced <> 100
/
prompt
prompt *******************************************************
prompt * Many Extent Allocation segment(100 > ) *
prompt * Max_Extents < Extents + 10 *
prompt *******************************************************
prompt
set feedback on
set pagesize 150
set linesize 150
col owner for a10;
col segment for a25;
col type for a15;
col tablespace for a18;
select owner
,segment_name "Segment"
,segment_type "Type"
,tablespace_name "Tablespace"
,round(bytes/1024/1024,2) "Size(MB)"
,extents "Extents"
,max_extents "MaxExtents"
,round(initial_extent/1024/1024,2) "Initial(MB)"
,round(next_extent/1024/1024,2) "Next(MB)"
from dba_segments
where
(extents > 100
or max_extents < extents + 10)
and owner NOT IN ('SYS','SYSTEM')
/
prompt
prompt *******************************************************
prompt * Unable to allocate next extent *
prompt * Not Enough Free space in Tablespace *
prompt *******************************************************
prompt
select s.owner
,s.segment_name "Segment"
,s.segment_type "Type"
,s.tablespace_name "Tablespace"
,round(s.next_extent/1024/1024,2) "Next(MB)"
from dba_segments s
where s.next_extent > (select MAX(f.bytes)
from dba_free_space f
where f.tablespace_name = s.tablespace_name)
/
clear columns;
prompt
prompt *******************************************************
prompt * TABLESPACE Usage *
prompt *******************************************************
prompt
set feedback off
SET PAGESIZE 100
SET LINESIZE 100
COLUMN pct_free FORMAT 999.99 HEADING "% Free"
COLUMN pct_used FORMAT 999.99 HEADING "% Used"
COLUMN name FORMAT A25 HEADING "Tablespace Name"
COLUMN mbytes FORMAT 99,999,999 HEADING "Total MBytes"
COLUMN used FORMAT 99,999,999 HEADING "Used Mbytes"
COLUMN free FORMAT 99,999,999 HEADING "Free Mbytes"
BREAK ON REPORT
COMPUTE SUM LABEL TOTAL OF mbytes free used ON REPORT
SELECT df.tablespace_name name, ts.extent_management ext_mgt, df.totalspace mbytes,
(df.totalspace - NVL(fs.freespace,0)) used, NVL(fs.freespace,0) free,
100 * (NVL(fs.freespace,0) / df.totalspace) pct_free,
100 * ((df.totalspace - NVL(fs.freespace,0)) / df.totalspace) pct_used
FROM (SELECT tablespace_name, ROUND(SUM(bytes) / 1048576) TotalSpace
FROM dba_data_files
GROUP BY tablespace_name) df, (SELECT tablespace_name,
ROUND(SUM(bytes) / 1048576) FreeSpace
FROM dba_free_space
GROUP BY tablespace_name ) fs, dba_tablespaces ts
WHERE df.tablespace_name = fs.tablespace_name(+)
AND df.tablespace_name = ts.tablespace_name
order by 1
/
rem select df.tablespace_name "Tablespace",
rem round(df.TBS_byte /1048576,2) "Total(MB)",
rem round((df.TBS_byte - fs.Free_byte)/1048576,2) "Used(MB)",
rem round(fs.Free_byte /1048576,2) "Free(MB)",
rem round((fs.Free_byte/df.TBS_byte) *100,0) "Free(%%)",
rem fs.pieces "Pieces",
rem round(fs.Max_free /1048576,2) "MaxFree(MB)"
rem from ( select tablespace_name, sum(bytes) TBS_byte
rem from dba_data_files group by tablespace_name ) df,
rem ( select tablespace_name, max(bytes) Max_free, sum(bytes) Free_byte, count(*) pieces
rem from dba_free_space group by tablespace_name ) fs,
rem ( select tablespace_name, initial_extent, next_extent
rem from dba_tablespaces ) db
rem where df.tablespace_name = db.tablespace_name
rem and df.tablespace_name = fs.tablespace_name(+)
rem order by 5
rem /
set feedback on
prompt
prompt *******************************************************
prompt * datafile autoextensible *
prompt *******************************************************
prompt
set linesize 150
set pagesize 150
col file_name for a50;
col tablespace_name for a20;
select tablespace_name, file_name,bytes/1024/1024 as MB, maxbytes/1024/1024 as maxsize, autoextensible
from dba_data_files
order by 1
/
prompt
prompt *******************************************************
prompt * File I/O INFO *
prompt *******************************************************
prompt
col name for a40;
select name "Name"
,phyrds "Phy Rds"
,phywrts "Phy Wrts"
,phyrds+phywrts "Total IO"
,trunc(phyrds/read_sum*100,2) "Read(%%)"
,trunc(phywrts/write_sum*100,2) "Write(%%)"
,trunc( (phyrds+phywrts) / (read_sum+write_sum) * 100 , 2) "Total IO(%%)"
,avgiotim "Avg IO Time"
from v$filestat a, v$datafile b,
(select sum(phyrds) read_sum, sum(phywrts) write_sum
from v$filestat ) c
where a.file# = b.file#
order by name desc
/
clear columns;
prompt
prompt *******************************************************
prompt * Tablespace I/O INFO *
prompt *******************************************************
prompt
SELECT max(c.name) "Tablespace Name",
sum(phyrds) "Phy Rds",
sum(phywrts) "Phy Wrts",
sum(phyblkrd) "Phy Blk Rd",
sum(phyblkwrt) "Phy Blk Wrt",
sum(phyrds+phywrts) "Phy Rds+Wrts",
sum(phyblkrd+phyblkwrt) "Phy Blk Rd+Wrt",
trunc(decode(sum(phyrds),0,-1, sum(phyblkrd)/sum(phyrds)),1) "Blocks/Read"
FROM v$filestat a,
v$datafile b,
v$tablespace c
WHERE a.file# = b.file#
and b.ts# = c.ts#
group by b.ts#
order by "Blocks/Read" desc, "Phy Blk Rd" desc
/
prompt
prompt *******************************************************
prompt * Table and Index Size Calculation *
prompt *******************************************************
prompt
select ty as seg_type, sum(sz) as "Size(MB)"
from (select decode(substr(segment_type,1,5),'TABLE','Table Segments','INDEX','Index Segments','LOBSE','Table Segments','LOBIN','Index Segments','Etc Segments') as ty,
trunc(sum(bytes/1024/1024),0) as sz
from dba_segments
where owner not in ('SYSTEM', 'SYS', 'SYSMAN', 'OUTLN', 'DBSNMP', 'MDSYS', 'ORDSYS','OLAPDBA', 'ORDPLUGINS',
'OLAPSVR', 'OSE$HTTP$ADMIN', 'AURORA$ORB$UNAUTHENTICATED', 'AURORA$JIS$UTILITY$','WMSYS',
'WMSYS', 'CTXSYS', 'XDB', 'ANONYMOUS', 'TRACESVR', 'REPADMIN', 'SCOTT', 'HR', 'SH')
group by segment_type)
group by ty order by 1 desc;
spool off;
exit
