REM Modified 2/4/02 by Tim Doyle
REM Combined curusers.sql, dbmap.sql, space.sql, oraparams.sql and tuneora7.sql
REM into 1 genereated report spooling dbcheck.lis.  Each report is separated by
REM headers.  
REM also added format to dbmap section for column max_extents as not able to view
REM all numbers
REM added format to column value(used in multiple staements) as all number where 
REM Truncated for the column value.

REM ****Start curusers report****
REM 
REM	file: CURusers.sql 
REM 
REM	This SQL*Plus script helps identify users currently        
REM     connected to the database and the statement they 
REM     are currently running. 
REM 
REM	11-JUL-96	Created.	   	Mike M.	 
 
SET PAGESIZE 999;
SET LINESIZE 132;

spool dbcheck.lis;
set termout off
break on today 
column today new_value _date
select to_char(SYSDATE, 'fmMonth DD, YYYY') today from dual;
clear breaks
set termout on
prompt
prompt ======================================
prompt Start of Database Current User Report
prompt ====================================== 
prompt   
prompt ======================================

column pu format a15 heading 'O/S|Login|ID' justify left 
column su format a15 heading 'Oracle|User ID' justify left 
column stat format a10 heading 'Session|Status' justify left 
column ssid format 999999 heading 'Oracle|Session|ID' justify right 
column sser format 999999 heading 'Oracle|Serial|No' justify right 
column spid format 999999 heading 'O/S|Process|ID' justify right 
column txt format a50 heading 'Current Statment' justify center word 
 
select p.username pu,  
       s.username su,   
       s.status stat, 
       s.sid ssid, 
       s.serial# sser, 
       lpad(p.spid,7) spid, 
       substr(sa.sql_text,1,540) txt 
from v$process p,  
     v$session s, 
     v$sqlarea sa  
where    p.addr=s.paddr  
and      s.username is not null 
and      s.sql_address=sa.address(+) 
and      s.sql_hash_value=sa.hash_value(+) 
order by 1,2,7 
/ 
 
column pu clear  
column su clear  
column stat clear  
column ssid clear  
column sser clear  
column spid clear  


set pagesize 999;
set linesize 132;

REM **** Start DBMAP Report ****
break on today 
column today new_value _date
select to_char(SYSDATE, 'fmMonth DD, YYYY') today from dual;
clear breaks
prompt
prompt ======================================
prompt Start of Database Mapping Report
prompt ====================================== 
prompt   Tablespace/Datafile Listing 
prompt ===================================== 
prompt 
prompt 
column  "Location"              format A60; 
column  "Tablespace Name"       format A15; 
column  "Size(M)"               format 999,990; 
 
break on "Tablespace Name" skip 1 nodup; 
compute sum of "Size(M)" on "Tablespace Name"; 
 
SELECT  tablespace_name "Tablespace Name", 
        file_name "Location", 
        bytes/1048576 "Size(M)"  
FROM sys.dba_data_files 
order by tablespace_name; 
 
prompt 
prompt ====================================== 
prompt   Redo Log Listing 
prompt ===================================== 
prompt 
prompt 
column  "Group"         format 999; 
column  "File Location" format A80; 
column  "Bytes   (K)"     format 999,999,990; 
 
break on "Group" skip 1 nodup; 
 
 
select  a.group# "Group", 
        b.member "File Location", 
        a.bytes/1024 "Bytes (K)" 
from    v$log a, 
        v$logfile b 
where a.group# = b.group# 
order by 1,2; 

 
prompt 
prompt ====================================== 
prompt   Rollback Listing 
prompt ===================================== 
prompt 
prompt 
column  "Segment Name"  format A15; 
column  "Tablespace"    format A15; 
Column  "Initial (K)"   Format 99,990; 
Column  "Next (K)"      Format 99,990; 
column  "Min Ext."      FORMAT 99,990; 
column  "Max Ext."      FORMAT 99,990; 
column  "Status"        Format A7; 
 select  segment_name "Segment Name", 
        tablespace_name "Tablespace", 
        initial_extent/1024 "Initial (K)", 
        next_extent/1024 "Next (K)", 
        min_extents "Min Ext.", 
        max_extents "Max Ext.", 
        status "Status" 
from    sys.dba_rollback_segs 
order by tablespace_name, 
        segment_name;



/*  
   This script displays information about space in  
   tablespaces, fragmentation and potential  
   extent-problems. It is divided in 3: 
	  
   1. Space available per tablespace.  
      Shows total size in bytes, total free space in bytes,  
      percentage free space to total space, 
      the size of the largest contigous free segment and  
      the number of free segments. 
   
   2. Lists The Extents > 50M
			      
   3. Lists tables and indexes with more than 200,to 5 extents. 
					  
   4. Lists tables/indexes whose next extent will not fit
      into their tablespace. 
*/ 
SET PAGESIZE 999; 
SET LINESIZE 132;
						    
column sumb format        999,999,999,999 
column extents format               9,999 
column max_extents format 999,999,999,999 
column next_extent format 999,999,999,999 
column nextext format     999,999,999,999 
column bytes format       999,999,999,999 
column largest format     999,999,999,999 
column Tot_Size format    999,999,999,999 
column Tot_Free format    999,999,999,999 
column Pct_Free format                999 
column Chunks_Free format         999,999  
column Max_Free format    999,999,999,999 
column owner format        a15 
column segment_name format a30 
column segment_type format a8 
set echo off 

REM ****Start Space report****
break on today 
column today new_value _date
select to_char(SYSDATE, 'fmMonth DD, YYYY') today from dual;
clear breaks
prompt
prompt ==========================================================
prompt Start of Database Space Report
prompt ==========================================================
prompt Space available in tablespaces grouped by tablespace name 
prompt ==========================================================

						      
select a.tablespace_name,sum(a.tots) Tot_Size,  
sum(a.sumb) Tot_Free, 
sum(a.sumb)*100/sum(a.tots) Pct_Free,  
sum(a.largest) Max_Free,sum(a.chunks) Chunks_Free  
from  
( 
select tablespace_name,0 tots,sum(bytes) sumb,  
max(bytes) largest,count(*) chunks 
from dba_free_space a
group by tablespace_name 
union 
select tablespace_name,sum(bytes) tots,0,0,0 from 
dba_data_files 
group by tablespace_name
) a 
group by a.tablespace_name;

PROMPT   SEGMENTS WITH MORE THAN 50M NEXT EXTENTS

select owner,segment_name,extents,bytes ,  
max_extents,next_extent,segment_type  
from  dba_segments  
where segment_type in ('TABLE','INDEX') and next_extent>=52428800
order by owner,segment_name;

PROMPT   SEGMENTS WITH MORE THAN 200 EXTENTS ALLOCATED

select owner,segment_name,extents,bytes ,  
max_extents,next_extent,segment_type  
from  dba_segments  
where segment_type in ('TABLE','INDEX') and extents>=200
order by owner,segment_name;

PROMPT   SEGMENTS WITH MORE THAN 175 EXTENTS ALLOCATED

select owner,segment_name,extents,bytes ,  
max_extents,next_extent,segment_type  
from  dba_segments  
where segment_type in ('TABLE','INDEX') and (extents>=175 and extents<200)
order by owner,segment_name; 

PROMPT   SEGMENTS WITH MORE THAN 150 EXTENTS ALLOCATED

select owner,segment_name,extents,bytes ,  
max_extents,next_extent,segment_type  
from  dba_segments  
where segment_type in ('TABLE','INDEX') and (extents>=150 and extents<175)
order by owner,segment_name;

PROMPT   SEGMENTS WITH MORE THAN 125 EXTENTS ALLOCATED

select owner,segment_name,extents,bytes ,  
max_extents,next_extent,segment_type  
from  dba_segments  
where segment_type in ('TABLE','INDEX') and (extents>=125 and extents<150)
order by owner,segment_name; 
					       
PROMPT   SEGMENTS WITH MORE THAN 115 EXTENTS ALLOCATED
PROMPT   Things are really getting critical with the following segments.
PROMPT   The following segments are reaching their final extent allocation.
PROMPT   Remember that pre-version 7.2 databases only have 121 extents possible.

select owner,segment_name,extents,bytes ,  
max_extents,next_extent,segment_type  
from  dba_segments  
where segment_type in ('TABLE','INDEX') and (extents>=115 and extents<125)
order by owner,segment_name; 
									
PROMPT   SEGMENTS WITH MORE THAN 105 EXTENTS ALLOCATED
PROMPT   The following segments are reaching their final extent allocation.
PROMPT   Remember that pre-version 7.2 databases only have 121 extents possible.

select owner,segment_name,extents,bytes ,  
max_extents,next_extent,segment_type
from  dba_segments  
where segment_type in ('TABLE','INDEX') and (extents>=105 and extents<115)
order by owner,segment_name; 
									 
PROMPT   Segments with more than 95 extents allocated
								  
select owner,segment_name,extents,bytes ,  
max_extents,next_extent,segment_type
from  dba_segments  
where segment_type in ('TABLE','INDEX') and (extents>=95 and extents<105) 
order by owner,segment_name; 
									   
PROMPT   Segments with more than 80 extents allocated
									    
select owner,segment_name,extents,bytes ,  
max_extents,next_extent,segment_type
from  dba_segments  
where segment_type in ('TABLE','INDEX') and (extents>=80 and extents<95)
order by owner,segment_name; 
									     
								      
PROMPT   Segments with more than 40 extents allocated
									       
select owner,segment_name,extents,bytes ,  
max_extents,next_extent,segment_type
from  dba_segments  
where segment_type in ('TABLE','INDEX') and (extents>=40 and extents<80)
order by owner,segment_name; 
										
PROMPT   Segments with more than 20 extents allocated
									 
select owner,segment_name,extents,bytes ,  
max_extents,next_extent,segment_type
from  dba_segments  
where segment_type in ('TABLE','INDEX') and (extents>=20 and extents<40)
order by owner,segment_name; 
									  
PROMPT   Segments with more than 10 extents allocated
									   
select owner,segment_name,extents,bytes ,  
max_extents,next_extent,segment_type
from  dba_segments  
where segment_type in ('TABLE','INDEX') and (extents>=10 and extents<20)
order by owner,segment_name; 
									    
PROMPT   Segments with more than 5 extents allocated
						     
select owner,segment_name,extents,bytes ,  
max_extents,next_extent,segment_type
from  dba_segments  
where segment_type in ('TABLE','INDEX') and (extents>=5 and extents<10)
order by owner,segment_name;  
									      
PROMPT Segments where there's not enough room for the next extent  
PROMPT Please add a datafile(s) to the tablespace to add additional room
PROMPT for growth.

select /*+ RULE */ a.owner, a.segment_name, b.tablespace_name,
next_extent,  
freesp.largest, b.segment_type 
from    dba_extents a, 
dba_segments b, 
(select /*+ RULE */ owner, segment_name, max(extent_id) extent_id, 
count(*) extents  
from dba_extents  
group by owner, segment_name 
) ext, 
(select /*+ RULE */ tablespace_name, max(bytes) largest 
from dba_free_space  
group by tablespace_name 
) freesp 
where   a.owner=b.owner and 
a.segment_name=b.segment_name and 
a.owner=ext.owner and  
a.segment_name=ext.segment_name and 
a.extent_id=ext.extent_id and 
b.tablespace_name = freesp.tablespace_name and  
next_extent > freesp.largest
/ 


REM ****Start oraparams report****

set     heading    off
set     pagesize   999
set     verify     off
set     feedback   off
set     maxdata    4000
set     arraysize  1
rem
COLUMN  s_id         NOPRINT new_value s_id;

SELECT value s_id from v$parameter
 where name='db_name';
/

set heading off;
select  'v7dba  Report for SID=&s_id generated on '||
        to_char(sysdate,'DD-MON-YY HH24:MI:SS')||' by '||
        user
from    sys.dual
/
set heading on
prompt
prompt ==========================================================
prompt Start of Database Oracle Parameters Report
prompt ==========================================================
prompt 
prompt ==========================================================
Prompt
prompt *********************************************************************
prompt *                    Database init.ora parameters                   *
prompt *********************************************************************
column  name         format a42
column value format a25
column isdefault format a6
select  name, value, isdefault
from    v$parameter
order by name
/
prompt *********************************************************************
prompt *                   Shows version and startup time                  *
prompt *********************************************************************

set serveroutput on

Declare 
	v_cursor_pos number;
	v_sql_string varchar2(255);
	v_oracle_banner varchar2(255);
	v_oracle_version char;
	v_start_time varchar2(18);
	v_return_val number;
	v_date_format varchar2(18) := 'DD-MON-YY HH:MM:SS';
	v_julian char := 'J';
	v_V7_string varchar2(25) := 'STARTUP TIME - JULIAN'	;
	v_Full_version char(10) ;
Begin

/* Get the Oracle version Banner */
	select banner into v_oracle_banner from v$version
		where banner like 'Oracle%';
/* Isolate the major version number */
	v_oracle_version := substr(v_oracle_banner,instr(v_oracle_banner,'Release')+8,1);
	v_Full_version := substr(v_oracle_banner,instr(v_oracle_banner,'Release')+8,9);

	DBMS_OUTPUT.PUT_LINE('Oracle Version '||v_Full_version);
/* If we are running Oracle 7 */
	if v_oracle_version = '7' then
/* Open the Cursor */
		v_cursor_pos := dbms_sql.open_cursor;
/* Setup the Sql Statement */
		v_sql_string := 'select to_char(to_date(value, :julian), :date_format ) 
						 from v$instance where key = :v7_string' ;
/* Parse it */
		dbms_sql.parse(v_cursor_pos,v_sql_string,dbms_sql.v7);
/* Define the output variable */
		dbms_sql.define_column(v_cursor_pos,1,v_start_time,18);
/* Bind the Variables */
		dbms_sql.bind_variable(v_cursor_pos,':date_format',v_date_format);
		dbms_sql.bind_variable(v_cursor_pos,':v7_string',v_V7_string);
		dbms_sql.bind_variable(v_cursor_pos,':julian',v_julian);
/* Execute it! */
		v_return_val := dbms_sql.execute(v_cursor_pos);
	Loop
		if dbms_sql.fetch_rows(v_cursor_pos) = 0 then
			exit;
		end if;
		dbms_sql.column_value(v_cursor_pos,1,v_start_time);
	End Loop;
/* Close the Cursor */
		dbms_sql.close_cursor(v_cursor_pos);
	else
/* Assume Oracle 8...I know, I know... */
/* Open the Cursor */
		v_cursor_pos := dbms_sql.open_cursor;
/* Setup the Sql Statement */
		v_sql_string := 'select to_char(startup_time,:date_format) start_time from v$instance';
/* Parse it */
		dbms_sql.parse(v_cursor_pos,v_sql_string,dbms_sql.v7);
/* Bind the Variables */
		dbms_sql.bind_variable(v_cursor_pos,':date_format',v_date_format);
/* Define the output variable */
		dbms_sql.define_column(v_cursor_pos,1,v_start_time,18);
/* Execute it! */
		v_return_val := dbms_sql.execute(v_cursor_pos);
	Loop
		if dbms_sql.fetch_rows(v_cursor_pos) = 0 then
			exit;
		end if;
		dbms_sql.column_value(v_cursor_pos,1,v_start_time);
	End Loop;
/* Close the Cursor */
		dbms_sql.close_cursor(v_cursor_pos);
	end if;
/* Output the Result */
	dbms_output.put_line('Instance started at '||v_start_time);

end;
/





REM ****Start tuneora7 report****

break on today 
column today new_value _date
select to_char(SYSDATE, 'fmMonth DD, YYYY') today from dual;
clear breaks
prompt
prompt ==========================================================
prompt Start of Database tune Oracle Report
prompt ==========================================================
prompt 
prompt ==========================================================

prompt
prompt TUNING MEMORY ALLOCATION
prompt
prompt SQL-Statement #1
prompt
prompt .   Percent Missed should be near zero.  If it is more than 1% than
prompt .   you should do the following...
prompt
prompt .       - allocate additional memory for the library cache
prompt .       - write identical SQL statements whenever possible
prompt
prompt .   Increase the "INIT" parameters "SHARED_POOL_SIZE" 
prompt .                                  "OPEN_CURSORS"
prompt
prompt .   If you have no library cache misses, you may still be able to speed
prompt .   execution calls by setting...  "CURSOR_SPACE_FOR_TIME" = TRUE
prompt
prompt .     CAUTION:  If you set this parameter to TRUE and the shared pool area
prompt .               runs out of space, the next user to issue a NEW SQL statement
prompt .               will get a "no space in shared pool area" error.
prompt .               Stopping their process...
prompt
select sum(pins) "Executions",
       sum(reloads) "Cache Misses while Executing",
       round(((sum(reloads) / sum(pins)) * 100),3) "Percent Missed"
from V$LIBRARYCACHE;
prompt
prompt SQL-Statement #2
prompt
prompt .   Percent Missed should be less than 10%.  If it is more than 10% than
prompt .   you should do the following...
prompt
prompt .   Increase the "INIT" parameters "SHARED_POOL_SIZE" 
prompt
select sum(gets) "Data Dictionary Gets",
       sum(getmisses) "Data Dictionary Get Misses",
       round(((sum(getmisses) / sum(gets)) * 100),3) "Percent Missed"
from V$ROWCACHE;
prompt
prompt SQL-Statement #3
prompt
prompt .   The Hit Ratio should be over 90% 
prompt .   If it is not than do the following...
prompt
prompt .   Increase the "INIT" parameters "DB_BLOCK_BUFFERS" 
prompt

column value format 999,999,999,990;
select name, value
from V$SYSSTAT
where name in ('db block gets', 'consistent gets', 'physical reads');
prompt .   Hit Ratio = 1 - (physical reads / (db block gets + consistent gets))
select round(((1 - (A.value / (B.value + C.value))) * 100),3) "Hit Ratio"
from V$SYSSTAT A, V$SYSSTAT B, V$SYSSTAT C
where A.name = 'physical reads' and
      B.name = 'db block gets' and
      C.name = 'consistent gets';
prompt
prompt TUNING DISK I/O
prompt
prompt SQL-Statement #1
prompt
prompt .   Total the I/O for each drive and make sure it is spread across
prompt .   the drives evenly... 
prompt
column name format a40
set pagesize 100
select Name "Name", Phyrds "Phyrds", Phywrts "Phywrts", phyrds + phywrts "Sum"
from V$DATAFILE df, V$FILESTAT fs
where df.file# = fs.file#
order by name;
set pagesize 22
prompt SQL-Statement #2
prompt
prompt .   Dynamic Extention can cause excessive recursive calls..
prompt .   Check for alot of smaller extents and reduce them to one larger extent.
prompt .   Other causes are...
prompt .       - Misses on the data dictionary cache
prompt .       - Firing of database triggers
prompt .       - Execution of Data Definition Language statements
prompt .       - Execution of SQL statements within stored procedures,
prompt .         functions, packages, and anonymous PL/SQL blocks.
prompt .       - Enforcement of referential integrity constraints.

Column value format 999,999,999,990;
select name "Name", value "Value"
from V$SYSSTAT
where name = 'recursive calls';
prompt
prompt TUNING CONTENTION
prompt
prompt SQL-Statement #1
prompt
prompt .   Tuning Rollback Segment Contention
prompt
prompt .   Percent Waits should be less than 1%.  If it is more than 1% for any class
prompt .   than you should do the following...
prompt
prompt .   Increase the number of rollback segments...
prompt
prompt .   Guide Lines for the Number of rollback segments...
prompt
prompt .   # of Concurrent Transactions (n)   | Recommended # of Rollback Segments
prompt .   ___________________________________|___________________________________
prompt .   n < 16                             | 4 Rollback Segments
prompt .   16 <= n < 32                       | 8 Rollback Segments
prompt .   32 <= n                            | n / 4, but no more than 50 R/S
select A.class "Class", A.count "Count", sum(B.value) "Total Requests",
       round(((A.count / sum(B.value)) * 100),3) "Percent Waits"
from V$WAITSTAT A, V$SYSSTAT B
where A.class in ('system undo header', 'system undo block',
                'undo header', 'undo block') and
      B.name in ('db block gets', 'consistent gets')
group by A.class, A.count
order by 1;
prompt SQL-Statement #2
prompt
prompt .   Tuning Redo Log Buffer Latches    
prompt
prompt .   Value should be near zero.  If it is not then do the following...
prompt 
prompt .   Increase LOG_BUFFER in increments of 5% until "Value" nears zero.
select name "Name", value "Value"
from V$SYSSTAT
where name = 'redo log space requests';
prompt SQL-Statement #3
prompt
prompt .   Tuning Redo Log Activity    
prompt
prompt .   If ((Misses / Gets) * 100) > 1%   or
prompt .      ((Immediate Misses / (Immediate Gets + Immediate Misses)) * 100) > 1% 
prompt .   for either record than...
prompt
prompt .   Reducing Contention for "Redo Allocation"...
prompt .        -  Decrease the "INIT" parameter "LOG_SMALL_ENTRY_MAX_SIZE"...
prompt
prompt .   Reducing Contention for "Redo Copy"...
prompt .        -  On Multi. CPU systems only...
prompt .           -  Increase the "INIT" parameter "LOG_SIMULTANEOUS_COPIES"...
prompt .              This parameter should be no larger than (2 * # of CPU's)...
prompt .        -  On all systems...
prompt .           -  Increase "INIT" parameter "LOG_ENTRY_PREBUILD_THRESHOLD"...
prompt .              This parameter is expressed in bytes...
column name format a20
select l.name "Name", gets "Gets", misses "Misses",
       immediate_gets "Immediate Gets", immediate_misses "Immediate Misses"
from V$LATCH l, V$LATCHNAME ln
where ln.name in ('redo allocation', 'redo copy') and
      ln.latch# = l.latch#;
prompt
prompt ADDITIONAL TUNING CONSIDERATIONS
prompt
prompt SQL-Statement #1
prompt
prompt .   Tuning Sorts
prompt
prompt .   If a significant number of sorts are being done on disk than...
prompt
prompt .   Increase the "INIT" parameter "SORT_AREA_SIZE"...
select name "Name", value "Value"
from V$SYSSTAT
where name in ('sorts (memory)', 'sorts (disk)')
order by 1;
prompt
prompt SQL-Statement #2
prompt
prompt .   Reducing Free List Contention
prompt
prompt .   If Percent Wait is greater than 1% than...
prompt
prompt .   Re-create the table and make "FREELISTS" equal to the number of
prompt .   Oracle processes that concurrently insert data into the table...
select A.class "Class", A.count "Count",
       sum(B.value) "Total Requests", 
       round(((A.count / sum(B.value)) * 100),3) "Percent Wait"
from V$WAITSTAT A, V$SYSSTAT B
where A.class = 'free list' and
      B.name in ('db block gets', 'consistent gets')
group by A.class, A.count;
prompt
prompt SQL-Statement #1
prompt
prompt .   This tells ammount of Sessions allowed.
prompt .   This Value can be raised in the init.ora parameters and 
prompt .   will take effect the next time the database is Mounted.
prompt
select value "Maximum Attaches" from v$parameter where name = 'sessions';
prompt
prompt
prompt
prompt SQL-Statement #2
prompt
prompt .   This is the amount Being used at the present time.
prompt
select count(*) "Currently Attached" from v$session ;
prompt
prompt
prompt
prompt
prompt ADDITIONAL IMPORTANT NOTES AND INIT PARAMETERS...
prompt 
prompt .   All Oracle processes should have equal priority...
prompt 
prompt .   To increase runtime performance and decrease recovery time the following
prompt .   can be done with "Checkpoints"...
prompt
prompt .        -  Set "INIT" parameter "LOG_CHECKPOINT_INTERVAL" larger than the size
prompt .           of your largest redo log file...
prompt .        -  Set "INIT" parameter "LOG_CHECKPOINT_TIMEOUT" to zero...
prompt .        -  You may also increase the size of your redo log files so that the
prompt .           redo log files do not fill up as quick...
prompt .        -  You should also set "INIT" parameter "CHECKPOINT_PROCESS" to "TRUE"
prompt .           This will free up LGWR when a checkpoint occurs
prompt
prompt .   Other "INIT" parameters to look at...
prompt .        - "DB_BLOCK_CHECKPOINT_BATCH".
prompt .        - "DB_FILE_MULTIBLOCK_READ_COUNT".
prompt .        - "DB_FILE_SIMULTANEOUS_WRITES".
prompt .        - "DML_LOCKS".
prompt .        - "ENQUEUE_RESOURCES".
prompt .        - "OPTIMIZER_MODE".
prompt
prompt
prompt
prompt
spool off;

exit