health check

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