-- Reportingquicktune.sql
-------------------------------------------------------------------------------------------------------
---******************************************************
--- Change location of spool file as per directory
--- Execute Reportingquicktune.sql file on SQL prompt
---SQL> @
---******************************************************
SET FEEDBACK OFF;
SET HEADING ON;
SET ECHO OFF;
SET PAGESIZE 50000;
SET TRIMSPOOL ON
SET LINESIZE 122;
CLEAR SCREEN;
spool
Prompt ============== Start Time ==============
prompt
SELECT TO_CHAR(SYSDATE,'DD-MM-YYYY HH24:MI') StartTime
FROM DUAL ;
prompt
prompt
Prompt ============== Instance Information ==============
prompt
prompt
SET LINESIZE 300;
SELECT * FROM v$instance ;
prompt
prompt
SET LINESIZE 122;
prompt =======================================================================================================
prompt QUICK TUNE PARAMETERS
prompt =======================================================================================================
prompt
prompt DATABASE INFO
column date_of_run format a30;
column open_time format a30;
column block_size_bytes format a20;
select d.name DATABASE,
to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') DATE_OF_RUN,
to_char(open_time, 'DD-MON-YYYY HH24:MI:SS') OPEN_TIME,
value BLOCK_SIZE_BYTES
from v$database d,
v$parameter p,
v$thread t
where p.name = 'db_block_size';
prompt
prompt
prompt =======================================================================================================
prompt
prompt DB BUFFER CACHE HIT RATIO
column "DB BUFFER CACHE HIT RATIO" format 999,999,999,999,999,999.9999
select (1-c.value/(a.value+b.value+0.000001))*100 "DB BUFFER CACHE HIT RATIO"
from v$sysstat a, v$sysstat b, v$sysstat c, v$sysstat d
where a.name = 'db block gets' -- 'logical_reads' = 'db block gets' + 'consistent gets'
and b.name = 'consistent gets'
and c.name = 'physical reads'
and d.name = 'physical writes';
prompt
prompt
prompt =======================================================================================================
prompt
prompt DICTIONARY CACHE HIT RATIO
column "DICTIONARY CACHE HIT RATIO" format 999.9999
select (1-sum(getmisses)/sum(gets))*100 "DICTIONARY CACHE HIT RATIO"
from v$rowcache;
prompt
prompt
prompt =======================================================================================================
prompt
prompt LIBRARY CACHE HIT RATIO
column "LIBRARY CACHE HIT RATIO" format 999.9999
select (1-sum(reloads)/sum(pins))*100 "LIBRARY CACHE HIT RATIO"
from v$librarycache;
prompt
prompt
prompt =======================================================================================================
prompt
prompt LIBRARY CACHE - GET and PIN HIT RATIO
column "LIBRARY CACHE GET HIT RATIO" format 999.9999
column "LIBRARY CACHE PIN HIT RATIO" format 999.9999
select min(gethitratio)*100 "LIBRARY CACHE GET HIT RATIO",
min(pinhitratio)*100 "LIBRARY CACHE PIN HIT RATIO"
from v$librarycache
where namespace IN ('SQL AREA', 'TABLE/PROCEDURE', 'BODY', 'TRIGGER');
prompt
prompt
prompt =======================================================================================================
prompt
prompt REDO BUFFER ALLOCATION RETRIES RATIO
column "REDO ALLOCATION RETRIES RATIO" format 999.9999
select a.value/(b.value+0.0001)*100 "REDO ALLOCATION RETRIES RATIO"
from v$sysstat a, v$sysstat b
where a.name = 'redo buffer allocation retries'
and b.name = 'redo writes';
prompt
prompt
prompt =======================================================================================================
prompt
prompt REDO BUFFER SPACE REQUEST RATIO
column "REDO SPACE REQUEST RATIO" format 999.9999
select a.value/(b.value+0.0001)*100 "REDO SPACE REQUEST RATIO"
from v$sysstat a, v$sysstat b
where a.name = 'redo log space requests'
and b.name = 'redo entries';
prompt
prompt
prompt =======================================================================================================
prompt
prompt FREE_MEMORY/SHARED_POOL RATIO
column "FREE_MEMORY/SHARED_POOL RATIO" format 999.9999
select min(least((b.bytes/(c.value+0.0001)),1)*100) "FREE_MEMORY/SHARED_POOL RATIO"
from v$sgastat b, v$parameter c
where b.name = 'free memory' -- sometimes 'free memory' shows all RAM
and c.name = 'shared_pool_size';
prompt
prompt
prompt =======================================================================================================
prompt
prompt FREE_MEMORY/SHARED_POOL_RESERVED RATIO
column "FREE_MEMORY/SP_RES RATIO" format 999.9999
select min(least((b.bytes/(c.value+0.0001)),1)*100) "FREE_MEMORY/SP_RES RATIO"
from v$sgastat b, v$parameter c
where b.name = 'free memory' -- sometimes 'free memory' shows all RAM
and c.name = 'shared_pool_reserved_size';
prompt
prompt
prompt =======================================================================================================
prompt
prompt SHARED POOL SIZES RATIOS
column "SP_RES/SP RATIO" format 999.9999
column "SP_RES_MIN_ALLOC/SP_RES RATIO" format 999.9999
select (b.value/(a.value+0.0001))*100 "SP_RES/SP RATIO",
(to_number(replace(c.value,'K','000'))/(b.value+1000))*100 "SP_RES_MIN_ALLOC/SP_RES RATIO"
from v$parameter a, v$parameter b, v$parameter c
where a.name = 'shared_pool_size'
and b.name = 'shared_pool_reserved_size'
and c.name = 'shared_pool_reserved_min_alloc';
prompt
prompt
prompt =======================================================================================================
prompt
prompt SHARED POOL REQUESTS RATIOS
column "REQUEST MISSES RATIO" format 999.9999
column "REQUEST FAILURES RATIO" format 999.9999
select (request_misses/(requests+0.0001))*100 "REQUEST MISSES RATIO",
(request_failures/(requests+0.0001))*100 "REQUEST FAILURES RATIO"
from v$shared_pool_reserved;
prompt
prompt
prompt =======================================================================================================
prompt
prompt SHARED POOL RESERVED MINALLOC SIZE RATIOS
column "SP_RES_MIN_ALLOC/LAST_FAIL" format 999.9999
column "SP_RES_MIN_ALLOC/AVG_FREE" format 999.9999
select (to_number(replace(a.value,'K','000'))
/greatest(b.max_used_size,b.last_failure_size,(to_number(replace(a.value,'K','000')))))*100
"SP_RES_MIN_ALLOC/LAST_FAIL",
(to_number(replace(a.value,'K','000'))
/greatest(b.avg_free_size,(to_number(replace(a.value,'K','000')))))*100
"SP_RES_MIN_ALLOC/AVG_FREE"
from v$parameter a, v$shared_pool_reserved b
where a.name = 'shared_pool_reserved_min_alloc';
prompt
prompt
prompt =======================================================================================================
prompt
prompt SORT AREA SIZE RATIOS
column "SORT_AREA/SHARED_POOL" format 999.9999
column "SA_RETAINED/SORT_AREA" format 999.9999
select (b.value/(a.value+0.0001))*100 "SORT_AREA/SHARED_POOL",
(c.value/(b.value+0.0001))*100 "SA_RETAINED/SORT_AREA"
from v$parameter a, v$parameter b, v$parameter c
where a.name = 'shared_pool_size'
and b.name = 'sort_area_size'
and c.name = 'sort_area_retained_size';
prompt
prompt
prompt =======================================================================================================
prompt
prompt SORT IN MEMORY/TOTAL RATIO
column "SORT IN MEMORY/TOTAL RATIO" format 999.9999
select (a.value/(a.value+b.value+0.0001))*100 "SORT IN MEMORY/TOTAL RATIO"
from v$sysstat a, v$sysstat b
where a.name = 'sorts (memory)'
and b.name = 'sorts (disk)';
prompt
prompt
prompt =======================================================================================================
prompt
prompt CPU TIME WAIT RATIO
column "CPU TIME WAIT RATIO" format 999.9999
select (a.value-b.value)/(c.value+0.0001)*100 "CPU TIME WAIT RATIO"
from v$sysstat a, v$sysstat b, v$sysstat c
where a.name = 'parse time elapsed'
and b.name = 'parse time cpu'
and c.name IN ('parse count', 'parse count (total)');
prompt
prompt
prompt =======================================================================================================
prompt
prompt RBS PERFORMANCE HIT RATIO
column "RBS PERFORMANCE HIT RATIO" format 999.9999;
select (1-max(w.count/sum(s.value)))*100 "RBS PERFORMANCE HIT RATIO"
from v$waitstat w, v$sysstat s
where w.class IN ('system undo header',
'system undo block',
'undo header',
'undo block')
and s.name IN ('db block gets', 'consistent gets') -- "Total Number of Requests for Data"
group by w.count;
prompt
prompt
prompt =======================================================================================================
prompt
prompt RBS SEGMENT CONTENTION RATIO
column "RBS SEGMENT CONTENTION RATIO" format 999.9999
select max(waits/gets)*100 "RBS SEGMENT CONTENTION RATIO"
from v$rollstat;
prompt
prompt
prompt =======================================================================================================
prompt
prompt FREELIST CONTENTION RATIO
column "FREELIST CONTENTION RATIO" format 999.9999;
select w.count/sum(s.value)*100 "FREELIST CONTENTION RATIO"
from v$waitstat w, v$sysstat s
where w.class IN ('free list')
and s.name IN ('db block gets', 'consistent gets') -- "Total Number of Requests for Data"
group by w.count;
prompt
prompt
prompt =======================================================================================================
prompt
prompt LATCH W2W_MISS/IMMED_MISS RATIO
column "LATCH W2W MISS RATIO" format 999.9999
column "LATCH IMMED MISS RATIO" format 999.9999
select max((l.misses/(l.gets+l.misses+0.0001))*100) "LATCH W2W MISS RATIO",
max((l.immediate_misses/(l.immediate_gets+l.immediate_misses+0.0001))*100) "LATCH IMMED MISS RATIO"
from v$latch l;
prompt
prompt
prompt =======================================================================================================
prompt
prompt MAX SESSION EVENT AVERAGE WAIT
column "MAX SESSION EVENT AVERAGE WAIT" format 999,999,999.9999;
select max(average_wait) "MAX SESSION EVENT AVERAGE WAIT"
from v$session_event;
prompt
prompt
prompt =======================================================================================================
prompt
prompt MTS DISPATCHER BUSY RATE RATIO
column "MTS DISPATCHER BUSY RATE RATIO" format 999.9999
select NVL(sum(busy)/sum(busy+idle),0)*100 "MTS DISPATCHER BUSY RATE RATIO"
from v$dispatcher;
prompt
prompt
prompt =======================================================================================================
prompt
prompt MTS DISPATCHER TIME WAIT RATIO
column "MTS DISPATCHER TIME WAIT RATIO" format 999.9999
select NVL(sum(q.wait)/(sum(q.totalq)+0.0001),0)*100 "MTS DISPATCHER TIME WAIT RATIO"
from v$queue q, v$dispatcher d
where q.type IN ('DISPATCHER', 'COMMON')
and q.paddr = d.paddr;
prompt
prompt
prompt
prompt =======================================================================================================
set linesize 132
column "%FREE" format 99.99
prompt
prompt Find Free Space for tablespace
prompt
Select A.TableSpace_Name, B.Bytes/1024/1024 bytes,
(B.Bytes - A.Bytes)/1024/1024 used,
A.Bytes/1024/1024 free,
(A.Bytes / B.Bytes) * 100 "%FREE"
From (select tablespace_name, sum(nvl(bytes,0)) bytes
from Sys.DBA_Free_Space group by tablespace_name) A,
(select tablespace_name, sum(nvl(bytes,0)) bytes
from Sys.DBA_Data_Files group by tablespace_name) B
Where A.tablespace_name = B.tablespace_name order by 5;
prompt
prompt =======================================================================================================
prompt
prompt Find tablespace status, stores data, extent management and allocation type
prompt
select TABLESPACE_NAME,STATUS,CONTENTS,EXTENT_MANAGEMENT,ALLOCATION_TYPE
from dba_tablespaces ;
prompt
prompt =======================================================================================================
prompt
prompt
prompt Find autoextended datafiles and maxbytes
prompt
select TABLESPACE_NAME,STATUS,CONTENTS,EXTENT_MANAGEMENT,ALLOCATION_TYPE
from dba_tablespaces ;
prompt
prompt =======================================================================================================
prompt
set linesize 132
prompt I/O BALANCE RATIOS
column FS_SIZE_STDDEV_RATIO format 999.999 heading "FILE|SYSTEM|SIZES|STANDARD|DEVIATION|RATIO"
column PHYS_READS_STDDEV_RATIO format 999.999 heading "PHYSICAL|READS|STANDARD|DEVIATION|RATIO"
column PHYS_WRITES_STDDEV_RATIO format 999.999 heading "PHYSICAL|WRITES|STANDARD|DEVIATION|RATIO"
select stddev(sum(a.bytes))/avg(sum(a.bytes))*100 FS_SIZE_STDDEV_RATIO,
stddev(sum(b.phyrds))/avg(sum(b.phyrds))*100 PHYS_READS_STDDEV_RATIO,
stddev(sum(b.phywrts))/avg(sum(b.phywrts))*100 PHYS_WRITES_STDDEV_RATIO
from v$datafile a,
v$filestat b
where a.file# = b.file#
group by substr(name,1,instr(name,'/',-1)), substr(name,1,instr(name,'\',-1));
prompt
prompt
prompt =======================================================================================================
prompt
prompt I/O CONTENTION
prompt
prompt =======================================================================================================
prompt
column TOTALS format a44 heading "DATABASE";
column file_system_name format a44 heading "File System|Name";
column tablespace_name format a44 heading "TableSpace|Name";
column file_name format a44 heading "File|Name";
column MBytes format 9,999,999 heading "Size|(MBytes)";
column phyblkrd format 9,999,999 heading "Number|Blocks|Read|(000's)";
column phyblkwrt format 9,999,999 heading "Number|Blocks|Written|(000's)";
column read_time_per_block_msec format 9,999,999 heading "Time|to|Read|1 Block|(msecs)";
column write_time_per_block_msec format 9,999,999 heading "Time|to|Write|1 Block|(msecs)";
column avg_access_time_tot format 9,999,999 heading "Average|Time|to|Read/Write|1 Block|(msecs/blk)";
column avg_access_time format 9,999,999 heading "SORTED|ON THIS|COLUMN|:|Average|Time|to|Read/Write|1 Block|(msecs/blk)";
column avg_access_speed format 999,999,999 heading "Average|Speed|to|Read/Write|1 Second|(blks/sec)";
column avg_estimated_srcer_speed format 9,999.999 heading "Estimated|Average|SRCER|Perform.|(recs/sec)";
column avg_estimated_rlser_speed format 9,999.999 heading "Estimated|Average|RLSER|Perform.|(recs/sec)";
column tps format 9,999.999 heading "ORACLE TRANSACTIONS PER SECOND";
column spt format 9,999.999 heading " SECONDS PER ORACLE TRANSACTION";
select d.name DATABASE,
to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') DATE_OF_RUN,
to_char(open_time, 'DD-MON-YYYY HH24:MI:SS') OPEN_TIME,
value BLOCK_SIZE_BYTES
from v$database d,
v$parameter p,
v$thread t
where p.name = 'db_block_size';
select 'TOTALS' TOTALS ,
sum(bytes)/(1024*1024) MBytes,
sum(phyblkrd)/1000 phyblkrd,
sum(phyblkwrt)/1000 phyblkwrt,
(sum(readtim)*10)/(sum(phyblkrd)+1) read_time_per_block_msec,
(sum(writetim)*10)/(sum(phyblkwrt)+1) write_time_per_block_msec,
(((sum(readtim)*10)/(sum(phyblkrd)+1))*(sum(phyblkrd)) + ((sum(writetim)*10)/(sum(phyblkwrt)+1))*(sum(phyblkwrt))) /
(sum(phyblkrd) + sum(phyblkwrt) + 1)
avg_access_time_tot,
(sum(phyblkrd) + sum(phyblkwrt)) /
(((((sum(readtim)*10)/(sum(phyblkrd)+1))*(sum(phyblkrd)) + ((sum(writetim)*10)/(sum(phyblkwrt)+1))*(sum(phyblkwrt))) + 1)) * 1000
avg_access_speed
from v$filestat,
dba_data_files
where file_id = file#;
select substr(name,1,instr(name,'/',-1))||substr(name,1,instr(name,'\',-1)) file_system_name,
sum(bytes)/(1024*1024) MBytes,
sum(phyblkrd)/1000 phyblkrd,
sum(phyblkwrt)/1000 phyblkwrt,
avg((readtim*10)/(phyblkrd+1)) read_time_per_block_msec,
avg((writetim*10)/(phyblkwrt+1)) write_time_per_block_msec,
(avg((readtim*10)/(phyblkrd+1))*sum(phyblkrd)/1000 + avg((writetim*10)/(phyblkwrt+1))*sum(phyblkwrt)/1000) /
(sum(phyblkrd)/1000 + sum(phyblkwrt)/1000 + 1)
avg_access_time,
((sum(phyblkrd) + sum(phyblkwrt)) /
(avg((readtim*10)/(phyblkrd+1))*sum(phyblkrd) + avg((writetim*10)/(phyblkwrt+1))*sum(phyblkwrt) + 1)) * 1000
avg_access_speed
from v$datafile a,
v$filestat b
where a.file# = b.file#
group by substr(name,1,instr(name,'/',-1)), substr(name,1,instr(name,'\',-1))
order by 7 desc, 6 desc;
select t.tablespace_name,
sum(bytes)/(1024*1024) MBytes,
sum(phyblkrd)/1000 phyblkrd,
sum(phyblkwrt)/1000 phyblkwrt,
avg((readtim*10)/(phyblkrd+1)) read_time_per_block_msec,
avg((writetim*10)/(phyblkwrt+1)) write_time_per_block_msec,
(avg((readtim*10)/(phyblkrd+1))*sum(phyblkrd)/1000 + avg((writetim*10)/(phyblkwrt+1))*sum(phyblkwrt)/1000) /
(sum(phyblkrd)/1000 + sum(phyblkwrt)/1000 + 1)
avg_access_time,
((sum(phyblkrd) + sum(phyblkwrt)) /
(avg((readtim*10)/(phyblkrd+1))*sum(phyblkrd) + avg((writetim*10)/(phyblkwrt+1))*sum(phyblkwrt) + 1)) * 1000
avg_access_speed
from dba_tablespaces t,
v$filestat,
dba_data_files d
where t.tablespace_name = d.tablespace_name
and d.file_id = file#
group by t.tablespace_name,
t.status
order by 7 desc, 6 desc;
select file_name,
bytes/(1024*1024) MBytes,
phyblkrd/1000 phyblkrd,
phyblkwrt/1000 phyblkwrt,
(readtim*10)/(phyblkrd+1) read_time_per_block_msec,
(writetim*10)/(phyblkwrt+1) write_time_per_block_msec,
(((readtim*10)/(phyblkrd+1))*(phyblkrd) + ((writetim*10)/(phyblkwrt+1))*(phyblkwrt)) /
(phyblkrd + phyblkwrt + 1)
avg_access_time,
(phyblkrd + phyblkwrt) /
(((((readtim*10)/(phyblkrd+1))*(phyblkrd) + ((writetim*10)/(phyblkwrt+1))*(phyblkwrt)) + 1)) * 1000
avg_access_speed
from v$filestat,
dba_data_files
where file_id = file#
order by 7 desc, 6 desc;
prompt
prompt
prompt =======================================================================================================
prompt
prompt ORACLE TRANSACTIONS (USER COMMITS + TRANSACTION ROLLBACKS) = TOTAL AVERAGES SINCE DATABASE STARTUP
select max(d.name) DATABASE,
to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') DATE_OF_RUN,
sum(s.value/(24*60*60*(sysdate-t.open_time))+0.001) tps,
1/sum(s.value/(24*60*60*(sysdate-t.open_time))+0.001) spt
from v$database d, v$sysstat s, v$thread t
where s.name in ('user commits', 'transaction rollbacks');
prompt
prompt
prompt
prompt
prompt =======================================================================================================
prompt
prompt SYSTEM EVENTS
col event format a37 heading 'Event'
col total_waits format 99999999 heading 'Total|Waits'
col time_waited format 9999999999 heading 'Time Wait|In Hndrds'
col total_timeouts format 999999 heading 'Timeout'
col average_wait heading 'Average|Time' format 999999.999
set pages 999
select *
from v$system_event ;
prompt
prompt
prompt =======================================================================================================
prompt
prompt SYSTEM STATISTICS
column value format 9999999999
select statistic#, name, value
from v$sysstat
where value > 0 ;
prompt
prompt
prompt =======================================================================================================
prompt
prompt ROLLBACK STATISTICS:
prompt
column extents format 999 heading 'Extents'
column rssize format 999,999,999 heading 'Size in|Bytes'
column optsize format 999,999,999 heading 'Optimal|Size'
column hwmsize format 999,999,999 heading 'High Water|Mark'
column shrinks format 9,999 heading 'Num of|Shrinks'
column wraps format 9,999 heading 'Num of|Wraps'
column extends format 999,999 heading 'Num of|Extends'
column aveactive format 999,999,999 heading 'Average size|Active Extents'
column rownum noprint
select rssize, optsize, hwmsize,
shrinks, wraps, extends, aveactive
from v$rollstat
order by rownum ;
prompt
prompt
prompt =======================================================================================================
prompt
prompt ROLLBACK STATISTICS - gets waits writes
select rownum, extents, rssize,
xacts, gets, waits, writes
from v$rollstat
order by rownum ;
prompt
prompt
prompt =======================================================================================================
prompt
prompt FREE SPACE
select
a.tablespace_name , tbsize , tbfree ,
round( b.tbfree/a.tbsize*100,2) "RATIO" , b.Largest "LARGEST SPACE"
from ( select tablespace_name,round(sum(bytes)/1024/1024,2) tbsize
from dba_data_files group by tablespace_name) a,
( select tablespace_name,round(sum(bytes)/1024/1024 ,2)tbfree,
round(max(bytes)/1024/1024,2) Largest
from dba_free_space group by tablespace_name) b
where a.tablespace_name=b.tablespace_name order by 4 ;
prompt
prompt
prompt =======================================================================================================
prompt LOG FILE SIZE
SELECT * FROM
V$LOG;
prompt =======================================================================================================
prompt
prompt
prompt LOG FILES
column MEMBER format a60
SELECT * FROM
V$LOGFILE;
prompt
prompt =======================================================================================================
prompt
prompt
prompt
prompt Rollback setment information
select SEGMENT_NAME,TABLESPACE_NAME ,INITIAL_EXTENT,NEXT_EXTENT,STATUS
from dba_ROLLBACK_SEGS;
prompt
prompt =======================================================================================================
prompt
prompt User Information
select USERNAME ,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE
from dba_users ;
prompt
prompt =======================================================================================================
prompt
prompt
prompt Table Fragmentation
prompt Wait..... Script is running....
column owner noprint new_value owner_var
column segment_name format a30 heading 'Object Name'
column segment_type format a9 heading 'Table/Indx'
column sum(bytes) format 999,999,999 heading 'Bytes Used'
column count(*) format 999,999 heading 'No.'
select a.owner, segment_name, segment_type,
sum(bytes), max_extents, count(*)
from dba_extents a, dba_tables b
where segment_name = b.table_name
and a.owner='EDA_USER'
having count(*) > 3
group by a.owner, segment_name, segment_type, max_extents
order by a.owner, segment_name, segment_type, max_extents ;
prompt
prompt
prompt =======================================================================================================
prompt
prompt
prompt Index Fragmentation
prompt Wait..... Script is running....
select a.owner, segment_name, segment_type,
sum(bytes), max_extents, count(*)
from dba_extents a, dba_indexes b
where segment_name = index_name
and a.owner='EDA_USER'
having count(*) > 3
group by a.owner, segment_name, segment_type, max_extents
order by a.owner, segment_name, segment_type, max_extents ;
prompt
prompt
prompt =======================================================================================================
prompt
prompt
prompt Initialize Parameter files
column name format a60
column value format a60
SELECT name, value
FROM v$parameter;
prompt
prompt
prompt =======================================================================================================
prompt
prompt - BLOCK LEVEL FRANGMENTATION FOR INDEX
set serveroutput on size 100000
set lines 132
declare
sowner varchar2(30):='EDA_USER'; ---- Segment Owner
segtype varchar2(30):='INDEX'; ---- Segment Type
segname varchar2(81); ---- Segment Name
v_total_blocks number; ---- Total No. of Blocks in a Segment
v_total_bytes number; ---- Total No. of Bytes in a Segment
v_unused_blocks number; ---- Total No. of Unused blocks
v_unused_bytes number; ---- Total No. of Unused Bytes
v_last_used_extent_file_id number; ---- Last used Datafile No.
v_last_used_extent_block_id number; ---- Last used Extent No.
v_last_used_block number; ---- Last used block
freeblocks number; ---- Blocks Below High water Mark
cursor selsegment(ssowner varchar2,segtype varchar2)
is
select segment_name from dba_segments
where owner=upper(sowner) and segment_type=upper(segtype);
begin
dbms_output.put_line(' Owner => '||sowner);
dbms_output.put_line(' Segment_type => '||segtype);
open selsegment(sowner,segtype); ------ select the segment_name
dbms_output.put_line('Segment Name |'||'Total Blocks|'||'Total Bytes|'||'Unused Blocks|'||'Unused Bytes|'||'Lusedfile|'||'Lusedextent|'||'Last used block|'||'Free Blocks');
loop
fetch selsegment into segname;
exit when selsegment%notfound;
dbms_space.unused_space (sowner,segname,segtype,v_total_blocks, v_total_bytes, v_unused_blocks,v_unused_bytes,v_last_used_extent_file_id, v_last_used_extent_block_id, v_last_used_block);
DBMS_SPACE.FREE_BLOCKS (sowner,segname,segtype,0,freeblocks);
dbms_output.put_line(segname||'|'||v_total_blocks||'|'||v_total_bytes||'|'||v_unused_blocks||'|'||v_unused_bytes||'|'||v_last_used_extent_file_id||'|'||v_last_used_extent_block_id||'|'||v_last_used_block||'|'||freeblocks);
end loop;
close selsegment;
end;
/
prompt
prompt
prompt =======================================================================================================
prompt
prompt
prompt - BLOCK LEVEL FRANGMENTATION FOR TABLES
set serveroutput on size 100000
set lines 132
declare
sowner varchar2(30):='EDA_USER'; ---- Segment Owner
segtype varchar2(30):='TABLE'; ---- Segment Type
segname varchar2(81); ---- Segment Name
v_total_blocks number; ---- Total No. of Blocks in a Segment
v_total_bytes number; ---- Total No. of Bytes in a Segment
v_unused_blocks number; ---- Total No. of Unused blocks
v_unused_bytes number; ---- Total No. of Unused Bytes
v_last_used_extent_file_id number; ---- Last used Datafile No.
v_last_used_extent_block_id number; ---- Last used Extent No.
v_last_used_block number; ---- Last used block
freeblocks number; ---- Blocks Below High water Mark
cursor selsegment(ssowner varchar2,segtype varchar2)
is
select segment_name from dba_segments
where owner=upper(sowner) and segment_type=upper(segtype);
begin
dbms_output.put_line(' Owner => '||sowner);
dbms_output.put_line(' Segment_type => '||segtype);
open selsegment(sowner,segtype); ------ select the segment_name
dbms_output.put_line('Segment Name |'||'Total Blocks|'||'Total Bytes|'||'Unused Blocks|'||'Unused Bytes|'||'Lusedfile|'||'Lusedextent|'||'Last used block|'||'Free Blocks');
loop
fetch selsegment into segname;
exit when selsegment%notfound;
dbms_space.unused_space (sowner,segname,segtype,v_total_blocks, v_total_bytes, v_unused_blocks,v_unused_bytes,v_last_used_extent_file_id, v_last_used_extent_block_id, v_last_used_block);
DBMS_SPACE.FREE_BLOCKS (sowner,segname,segtype,0,freeblocks);
dbms_output.put_line(segname||'|'||v_total_blocks||'|'||v_total_bytes||'|'||v_unused_blocks||'|'||v_unused_bytes||'|'||v_last_used_extent_file_id||'|'||v_last_used_extent_block_id||'|'||v_last_used_block||'|'||freeblocks);
end loop;
close selsegment;
end;
/
prompt
prompt
prompt =======================================================================================================
prompt
prompt
prompt Buffer GetS - for > 10 execution
set linesize 400
column SQL_TEXT format a80
SELECT DISK_READS, BUFFER_gETS, EXECUTIONS,ROWS_PROCESSED,
DISK_READS/DECODE(EXECUTIONS,0,1,EXECUTIONS) PEREXECDISK,
BUFFER_gETS/DECODE(EXECUTIONS,0,1,EXECUTIONS) PEREXECBUFF,SQL_TEXT
FROM V$SQLAREA
WHERE BUFFER_gETS/DECODE(EXECUTIONS,0,1,EXECUTIONS) > 1000
and executions > 10
ORDER BY BUFFER_gETS/DECODE(EXECUTIONS,0,1,EXECUTIONS) DESC ;
prompt
prompt
prompt =======================================================================================================
prompt
prompt Disk Reads - for > 10 executions
SELECT DISK_READS, BUFFER_gETS, EXECUTIONS,ROWS_PROCESSED,
DISK_READS/DECODE(EXECUTIONS,0,1,EXECUTIONS) PEREXECDISK,
BUFFER_gETS/DECODE(EXECUTIONS,0,1,EXECUTIONS) PEREXECBUFF,SQL_TEXT
FROM V$SQLAREA
WHERE DISK_READS/DECODE(EXECUTIONS,0,1,EXECUTIONS) >= 500
and executions >=10
ORDER BY DISK_READS/DECODE(EXECUTIONS,0,1,EXECUTIONS) DESC ;
prompt
prompt
prompt =======================================================================================================
prompt
prompt
prompt
prompt Buffer GetS - for top 100 queries
SELECT * FROM (
SELECT DISK_READS, BUFFER_gETS, EXECUTIONS,ROWS_PROCESSED,
DISK_READS/DECODE(EXECUTIONS,0,1,EXECUTIONS) PEREXECDISK,
BUFFER_gETS/DECODE(EXECUTIONS,0,1,EXECUTIONS) PEREXECBUFF,SQL_TEXT
FROM V$SQLAREA
WHERE executions >= 1
ORDER BY BUFFER_gETS/DECODE(EXECUTIONS,0,1,EXECUTIONS) DESC )
WHERE ROWNUM <= 100 ;
prompt
prompt
prompt
prompt =======================================================================================================
prompt
prompt Disk Reads - for top 100 queries
SELECT * FROM (
SELECT DISK_READS, BUFFER_gETS, EXECUTIONS,ROWS_PROCESSED,
DISK_READS/DECODE(EXECUTIONS,0,1,EXECUTIONS) PEREXECDISK,
BUFFER_gETS/DECODE(EXECUTIONS,0,1,EXECUTIONS) PEREXECBUFF,SQL_TEXT
FROM V$SQLAREA
WHERE executions >=1
ORDER BY DISK_READS/DECODE(EXECUTIONS,0,1,EXECUTIONS) DESC )
WHERE ROWNUM <= 100;
prompt
prompt
prompt =======================================================================================================
prompt
prompt
prompt
prompt
prompt Parse to execution
column SQL_TEXT format a100
SELECT PARSE_CALLS,EXECUTIONS,SQL_TEXT
FROM ( select PARSE_CALLS,EXECUTIONS,SQL_TEXT from v$sql
where executions < parse_calls
and parse_calls > 0
order by parse_calls desc )
WHERE rownum <= 20;
prompt
prompt
prompt =======================================================================================================
prompt
prompt
prompt
prompt Parse to execution ratio %
prompt
--- low number may indicate a literal SQL or other sharing problem
--- OLTP should be 90 percent
select 100 * (1-a.hard_parses/b.executions) noparse_hitratio
from (select value hard_parses
from v$sysstat
where name = 'parse count (hard)' ) a
,(select value executions
from v$sysstat
where name = 'execute count') b;
prompt
prompt
prompt =======================================================================================================
prompt
prompt TABLE STATISTICS
prompt
select table_name,num_rows,blocks,avg_row_len,user_stats,
IOT_NAME,SAMPLE_SIZE,LAST_ANALYZED
FROM dba_tables
where OWNER='EDA_USER';
prompt
prompt
prompt =======================================================================================================
prompt
prompt INDEDX STATISTICS
prompt
select OWNER,INDEX_NAME, INDEX_TYPE,
num_rows,user_stats , UNIQUENESS,
SAMPLE_SIZE,LAST_ANALYZED,FUNCIDX_STATUS
FROM dba_indexes
where table_owner='EDA_USER' ;
prompt
prompt
prompt =======================================================================================================
prompt
prompt LOG SWITCH INFORMATION
prompt
SELECT thread#, sequence#, first_time
FROM ( select thread#, sequence#,
to_char(first_time,'mm/dd/yyyy hh24:mi:ss') first_time
from v$loghist
order by sequence# desc )
WHERE rownum <=100;
prompt
prompt
prompt =======================================================================================================
prompt
prompt
Prompt ============== End Time ==============
prompt
SELECT TO_CHAR(SYSDATE,'DD-MM-YYYY HH24:MI') EndTime
FROM DUAL ;
Prompt =======================================
prompt
prompt
prompt End Report
prompt
spool off;
