terça-feira, 28 de setembro de 2010

Objetos por Tablespace - Oracle

select OWNER, SEGMENT_NAME, SEGMENT_TYPE
from dba_segments
where TABLESPACE_NAME='XXX';

domingo, 5 de setembro de 2010

Monitorar Processos - RMAN - Oracle

select sid,
serial#,
context,
sofar,
totalwork,
round(sofar/totalwork*100,2) porcentagem
from
v$session_longops
where
opname like 'RMAN%'
and opname not like '%aggregate%'
and totalwork != 0
and sofar <> totalwork

Analise de Backups - RMAN - Oracle

select operation,object_type,status,output_device_type,
to_char(end_time,'DD-MM-RRRR HH24:MI:SS'),
round(MBYTES_PROCESSED/1024,2)
from
v$rman_status
where
operation <> 'CATALOG'
and trunc(end_time)>=trunc(sysdate-1)
order by
end_time;

domingo, 29 de agosto de 2010

Execução de JOBS - Oracle

To show details on job run:
select log_date
,      job_name
,      status
,      req_start_date
,      actual_start_date
,      run_duration
from   dba_scheduler_job_run_details

To show running jobs:
select job_name
,      session_id
,      running_instance
,      elapsed_time
,      cpu_used
from dba_scheduler_running_jobs;

To show job history:
 select log_date
 ,      job_name
 ,      status
 from dba_scheduler_job_log;


show all schedules:
select schedule_name, schedule_type, start_date, repeat_interval 
from dba_scheduler_schedules;

show all jobs and their attributes:
select *
from dba_scheduler_jobs


show all program-objects and their attributes
select *
from dba_scheduler_programs;

show all program-arguments:
select *
from   dba_scheduler_program_args;

Matar sessão do usuário - Oracle

alter system disconnect session '288,1635' immediate


1 parametro : SID
2 parametro : SERIAL#


informações na view v$session

sábado, 28 de agosto de 2010

Função para verificação de senha - Oracle

CREATE OR REPLACE  FUNCTION "SYS"."SYS_PASSWD_VALIDATION" 
    (username varchar2,
  password varchar2,
  old_password varchar2)
  RETURN boolean IS
   n boolean;
   m integer;
   differ integer;
   isdigit boolean;
   ischar  boolean;
   digitarray varchar2(32);
   chararray varchar2(52);
   digi integer;


BEGIN
   digitarray:= '0123456789!"#$%&()``*+,-/:;<=>?_';
   chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';


   -- Check if the password is same as the username
   IF password = username THEN
     raise_application_error(-20001, 'Password must differ from userid');
   END IF;


   -- Check for the minimum length of the password
   IF length(password) < 8 THEN
      raise_application_error(-20002, 'Password length less than 8');
   END IF;


   -- Check if the password is too simple. A dictionary of words may be
   -- maintained and a check may be made so as not to allow the words
   -- that are too simple for the password.
   IF NLS_LOWER(password) IN ('welcome', 'database', 'account', 'user', 'password', 'oracle', 'computer', 'abcdefgh') THEN
      raise_application_error(-20002, 'Password too simple');
   END IF;


   -- Check if the password contains at least one letter, one digit and one
   -- punctuation mark.
   -- 1. Check for the digit


   digi:=0;
   m := length(password);
   FOR i IN 1..length(digitarray) LOOP
      FOR j IN 1..m LOOP
         IF substr(password,j,1) = substr(digitarray,i,1) THEN
            digi:=digi+1;
            IF digi >1 THEN
               GOTO findchar;
            END IF;
         END IF;
      END LOOP;
   END LOOP;
   IF digi < 2 THEN
      raise_application_error(-20003, 'Password should contain at least two alphanumeric characters');
   END IF;


   -- 2. Check for the character
   <<findchar>>
   ischar:=FALSE;
   FOR i IN 1..length(chararray) LOOP
      FOR j IN 1..m LOOP
         IF substr(password,j,1) = substr(chararray,i,1) THEN
            ischar:=TRUE;
             GOTO endsearch;
         END IF;
      END LOOP;
   END LOOP;
   IF ischar = FALSE THEN
      raise_application_error(-20003, 'Password should contain at least one \
              alphabetic character');
   END IF;


   <<endsearch>>
   -- Check if the password differs from the previous password by at least
   -- 3 letters
   IF old_password = '' THEN
      raise_application_error(-20004, 'Old password is null');
   END IF;
   -- Everything is fine; return TRUE ;
   differ := length(old_password) - length(password);


   IF abs(differ) < 3 THEN
      IF length(password) < length(old_password) THEN
         m := length(password);
      ELSE
         m := length(old_password);
      END IF;
      differ := abs(differ);
      FOR i IN 1..m LOOP
          IF substr(password,i,1) != substr(old_password,i,1) THEN
             differ := differ + 1;
          else
dbms_output.put_line('OLD:'||substr(password,i,1));
dbms_output.put_line('NEW:'||substr(old_password,i,1));
          END IF;
      END LOOP;
      IF differ < 2 THEN
          raise_application_error(-20004, 'Password should differ by at \
            least 2 characters');
      END IF;
   END IF;
   -- Everything is fine; return TRUE ;
   RETURN(TRUE);
END;

Pacote tunning - Oracle

spool /tmp/tuning_stats.txt  
set head on
prompt _________________________________________________________________________
column name heading 'DATABASE NAME' format a30
column dbid heading 'DATEBASE ID#' format 9999999999999
column sysdate heading 'REPORT DATED' format a20
select dbid, name, created,sysdate from v$database, dual;
prompt +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
prompt
prompt
set head off
prompt LIBRARY CACHE STATISTICS:  
prompt +++++++++++++++++++++++++
set feedback off
set pages 0
select 'PINS    - # of times an item in the library cache was executed - '||sum(pins),'RELOADS - # of library cache misses on execution steps         - '|| sum (reloads), '____________________________                                                    RELOADS / PINS * 100 = '||round((sum(reloads) / sum(pins) *  
100),2)||'%' from v$librarycache; 
prompt
prompt Increase memory until RELOADS is near 0 but watch out for Paging/swapping
prompt To increase library cache, increase SHARED_POOL_SIZE  
prompt  
prompt ** NOTE: Increasing SHARED_POOL_SIZE will increase the SGA size.  
prompt  
prompt Library Cache Misses indicate that the Shared Pool is not big  
prompt enough to hold the shared SQL area for all concurrently open cursors.  
prompt If you have no Library Cache misses (PINS = 0), you may get a small  
prompt increase in performance by setting CURSOR_SPACE_FOR_TIME = TRUE which  
prompt prevents ORACLE from deallocating a shared SQL area while an application 
prompt cursor associated with it is open.  
prompt  
prompt For Multi-threaded server, add 1K to SHARED_POOL_SIZE per user.  
prompt  
prompt ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 
column xn1 format a50  
column xn2 format a50  
column xn3 format a50  
column xv1 new_value xxv1 noprint  
column xv2 new_value xxv2 noprint  
column xv3 new_value xxv3 noprint  
column d1  format a50  
column d2  format a50  
prompt HIT RATIO:  
prompt ++++++++++
prompt  
prompt Values Hit Ratio is calculated against:  
set feedback off
select lpad(name,20,' ')||'  =  '||value xn1, value xv1  
from   v$sysstat  
where  name = 'db block gets'  
/  
select lpad(name,20,' ')||'  =  '||value xn2, value xv2   
from   v$sysstat  
where  name = 'consistent gets'  
/  
select lpad(name,20,' ')||'  =  '||value xn3, value xv3   
from   v$sysstat b  
where  name = 'physical reads'  
/  
set pages 60  
select 'Logical reads = db block gets + consistent gets ',  
        lpad ('Logical Reads = ',24,' ')||to_char(&xxv1+&xxv2) d1  
from    dual  
/  
select 'Hit Ratio = (logical reads - physical reads) / logical reads',  
        lpad('Hit Ratio = ',24,' ')||  
        round( (((&xxv2+&xxv1) - &xxv3) / (&xxv2+&xxv1))*100,2 )||'%' d2  
from    dual  
/  
prompt +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

prompt If the hit ratio is less than 60%-70%, increase the initialization  
prompt parameter DB_BLOCK_BUFFERS.  ** NOTE:  Increasing this parameter will  
prompt increase the SGA size.  
prompt  
prompt------------------------------------------------------------------------  
  
col name format a30  
col gets format 9,999,999  
col waits format 9,999,999  
prompt ROLLBACK CONTENTION STATISTICS:  
prompt +++++++++++++++++++++++++++++++ 
prompt  
  
prompt GETS - # of gets on the rollback segment header 
prompt WAITS - # of waits for the rollback segment header  
  
set head on;  

select name, waits, gets  
from   v$rollstat, v$rollname  
where  v$rollstat.usn = v$rollname.usn  
/  

set head off  

select 'The average of waits/gets is '||  
   round((sum(waits) / sum(gets)) * 100,2)||'%'  
From    v$rollstat  
/  
prompt ##########################
  
prompt  
prompt If the ratio of waits to gets is more than 1% or 2%, consider  
prompt creating more rollback segments  
prompt  
prompt Another way to gauge rollback contention is:  
prompt  
  
column xn1 format 9999999  
column xv1 new_value xxv1 noprint  

set head on  

select class, count  
from   v$waitstat  
where  class in ('system undo header', 'system undo block', 
                 'undo header',        'undo block'          )  
/  

set head off  

select 'Total requests = '||sum(count) xn1, sum(count) xv1  
from    v$waitstat  
/  

select 'Contention for system undo header = '||  
       (round(count/(&xxv1+0.00000000001),4)) * 100||'%'  
from  v$waitstat  
where   class = 'system undo header'  
/  

select 'Contention for system undo block = '||  
       (round(count/(&xxv1+0.00000000001),4)) * 100||'%'  
from    v$waitstat  
where   class = 'system undo block'  
/  

select 'Contention for undo header = '||  
       (round(count/(&xxv1+0.00000000001),4)) * 100||'%'  
from    v$waitstat  
where   class = 'undo header'  
/  

select 'Contention for undo block = '||  
       (round(count/(&xxv1+0.00000000001),4)) * 100||'%'  
from    v$waitstat  
where   class = 'undo block'  
/  

prompt  
prompt If the percentage for an area is more than 1% or 2%, consider  
prompt creating more rollback segments.  Note:  This value is usually very  
prompt small 
prompt and has been rounded to 4 places.  
prompt  
prompt------------------------------------------------------------------------  
  
prompt %%%%%%%%%%%%%%%%%%%%%%%%%%%%
prompt REDO CONTENTION STATISTICS:  
prompt %%%%%%%%%%%%%%%%%%%%%%%%%%%%
prompt  
prompt The following shows how often user processes had to wait for space in  
prompt the redo log buffer:  
  
select name||' = '||value  
from   v$sysstat  
where  name = 'redo log space requests'  
/  

prompt  
prompt This value should be near 0.  If this value increments consistently,  
prompt processes have had to wait for space in the redo buffer.  If this  
prompt condition exists over time, increase the size of LOG_BUFFER in the  
prompt init.ora file in increments of 5% until the value nears 0.  
prompt ** NOTE: increasing the LOG_BUFFER value will increase total SGA size.  
prompt  
prompt -----------------------------------------------------------------------  
  
  
col name format a15  
col gets format 9999999  
col misses format 9999999  
col immediate_gets heading 'IMMED GETS' format 9999999  
col immediate_misses heading 'IMMED MISS' format 9999999  
col sleeps format 999999  

prompt %%%%%%%%%%%%%%%%%%%%%%%%%%%%
prompt LATCH CONTENTION:  
prompt %%%%%%%%%%%%%%%%%%%%%%%%%%%%
prompt  
prompt GETS - # of successful willing-to-wait requests for a latch  
prompt MISSES - # of times an initial willing-to-wait request was unsuccessful  
prompt IMMEDIATE_GETS - # of successful immediate requests for each latch  
prompt IMMEDIATE_MISSES = # of unsuccessful immediate requests for each latch  
prompt SLEEPS - # of times a process waited and requests a latch after an  
prompt          initial willing-to-wait request  
prompt  
prompt If the latch requested with a willing-to-wait request is not  
prompt available, the requesting process waits a short time and requests  
prompt again.  
prompt If the latch requested with an immediate request is not available,  
prompt the requesting process does not wait, but continues processing  
prompt  
  
set head on  

select name,          gets,              misses,  
       immediate_gets,  immediate_misses,  sleeps  
from   v$latch  
where  name in ('redo allocation',  'redo copy')  
/  

set head off  

select 'Ratio of MISSES to GETS: '||  
        round((sum(misses)/(sum(gets)+0.00000000001) * 100),2)||'%'  
from    v$latch  
where   name in ('redo allocation',  'redo copy')  
/  

select 'Ratio of IMMEDIATE_MISSES to IMMEDIATE_GETS: '||  
        round((sum(immediate_misses)/  
       (sum(immediate_misses+immediate_gets)+0.00000000001) * 100),2)||'%'  
from    v$latch  
where   name in ('redo allocation',  'redo copy')  
/  

prompt  
prompt If either ratio exceeds 1%, performance will be affected.  
prompt  
prompt Decreasing the size of LOG_SMALL_ENTRY_MAX_SIZE reduces the number of  
prompt processes copying information on the redo allocation latch.  
prompt  
prompt Increasing the size of LOG_SIMULTANEOUS_COPIES will reduce contention  
prompt for redo copy latches.  
  
rem  
rem This shows the library cache reloads  
rem  

set head on  

prompt  
prompt------------------------------------------------------------------------  
  
prompt  
prompt Look at gethitratio and pinhit ratio  
prompt  
prompt GETHITRATIO is number of GETHTS/GETS  
prompt PINHIT RATIO is number of PINHITS/PINS - number close to 1 indicates  
prompt that most objects requested for pinning have been cached.  Pay close  
prompt attention to PINHIT RATIO.  
prompt  
  
column namespace    format a20   heading 'NAME'  
column gets         format 99999999 heading 'GETS'  
column gethits      format 99999999 heading 'GETHITS'  
column gethitratio  format 999.99   heading 'GET HIT|RATIO'  
column pins         format 9999999  heading 'PINHITS'  
column pinhitratio  format 999.99   heading 'PIN HIT|RATIO'  

select namespace,    gets,  gethits,  
       gethitratio,  pins,  pinhitratio  
from   v$librarycache  
/  

rem  
rem  
rem This looks at the dictionary cache miss rate  
rem  

prompt  
prompt------------------------------------------------------------------------  
  
prompt %%%%%%%%%%%%%%%%%%%%%%%%%%%%
prompt THE DATA DICTIONARY CACHE:  
prompt %%%%%%%%%%%%%%%%%%%%%%%%%%%%
prompt  
prompt  
prompt Consider keeping this below 5% to keep the data dictionary cache in  
prompt the SGA.  Up the SHARED_POOL_SIZE to improve this statistic. **NOTE:  
prompt increasing the SHARED_POOL_SIZE will increase the SGA.  
prompt  

column dictcache format 999.99 heading 'Dictionary Cache | Ratio %'  

select sum(getmisses) / (sum(gets)+0.00000000001) * 100 dictcache  
from   v$rowcache  
/  

prompt  
prompt------------------------------------------------------------------------  
  
prompt  
prompt %%%%%%%%%%%%%%%%%%%%%%%%%%%%
prompt SYSTEM EVENTS:  
prompt %%%%%%%%%%%%%%%%%%%%%%%%%%%%
prompt  
prompt Not sure of the value of this section yet but it looks interesting.  
prompt  

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------------------------------------------------------------------------  
  
rem  
rem  
rem This looks at the sga area breakdown  
rem  

prompt %%%%%%%%%%%%%%%%%%%%%%%%%%%%
prompt THE SGA AREA ALLOCATION:  
prompt %%%%%%%%%%%%%%%%%%%%%%%%%%%%
prompt  
prompt  
prompt This shows the allocation of SGA storage.  Examine this before and  
prompt after making changes in the INIT.ORA file which will impact the SGA.  
prompt  

col name format a40  

select name, bytes  
from   v$sgastat  
/  

set head off  

select 'total of SGA                            '||sum(bytes)  
from    v$sgastat  


prompt  
prompt------------------------------------------------------------------------  
  
rem  
rem Displays all the base session statistics  
rem  

set head on  
set pagesize 110  

column name        format a55            heading 'Statistic Name'  
column value       format 9,999,999,999  heading 'Result'  
column statistic#  format 9999           heading 'Stat#' 

ttitle center 'Instance Statistics' skip 2  

prompt  
prompt Below is a dump of the core Instance Statistics that are greater than0.  
prompt Although there are a great many statistics listed, the ones of greatest  
prompt value are displayed in other formats throughout this report.  Of   
prompt interest here are the values for:  
prompt  
prompt  cumulative logons  
prompt(# of actual connections to the DB since last startup - good  
prompt  volume-of-use statistic)  
prompt  
prompt  #93  table fetch continued row  
prompt  (# of chained rows - will be higher if there are a lot of long fields   
prompt  if the value goes up over time, it is a good signaller of general   
prompt  database fragmentation)  
prompt  
  
select statistic#,  name,  value  
from   v$sysstat  
where  value > 0  
/  

prompt  
prompt -----------------------------------------------------------------------  
  
set pages 66;  
set space 3;  
set heading on;  

prompt  
prompt Parse Ratio usually falls between 1.15 and 1.45.  If it is higher, then  
prompt it is usually a sign of poorly written Pro* programs or unoptimized  
prompt SQL*Forms applications.  
prompt  
prompt Recursive Call Ratio will usually be between  
prompt  
prompt   7.0 - 10.0 for tuned production systems  
prompt  10.0 - 14.5 for tuned development systems  
prompt  
prompt Buffer Hit Ratio is dependent upon RDBMS size, SGA size and  
prompt the types of applications being processed.  This shows the %-age  
prompt of logical reads from the SGA as opposed to total reads - the  
prompt figure should be as high as possible.  The hit ratio can be raised  
prompt by increasing DB_BUFFERS, which increases SGA size.  By turning on  
prompt the "Virtual Buffer Manager" (db_block_lru_statistics = TRUE and  
prompt db_block_lru_extended_statistics = TRUE in the init.ora parameters),  
prompt you can determine how many extra hits you would get from memory as  
prompt opposed to physical I/O from disk.  **NOTE:  Turning these on will  
prompt impact performance.  One shift of statistics gathering should be enough  
prompt to get the required information.  
prompt  
  
ttitle left 'Ratios for this Instance' skip 2  

column pcc   heading 'Parse|Ratio'       format 99.99  
column rcc   heading 'Recsv|Cursr'       format 99.99  
column hr    heading 'Buffer|Ratio'      format 999,999,999.999  
column rwr   heading 'Rd/Wr|Ratio'       format 999,999.9  
column bpfts heading 'Blks per|Full TS'  format 999,999 

REM Modified for O7.1 to reverse 'cumulative opened cursors' to  
REM 'opened cursors cumulative'  
REM was:sum(decode(a.name,'cumulative opened cursors',value, .00000000001))  
REM pcc,  
REM and:sum(decode(a.name,'cumulative opened cursors',value,.00000000001))  
REM rcc,  

select sum(decode(a.name,'parse count',value,0)) /  
       sum(decode(a.name,'opened cursors cumulative',value,.00000000001)) pcc,  
       sum(decode(a.name,'recursive calls',value,0)) /  
       sum(decode(a.name,'opened cursors cumulative',value,.00000000001)) rcc,  
       (1-(sum(decode(a.name,'physical reads',value,0)) /  
       sum(decode(a.name,'db block gets',value,.00000000001)) +  
  sum(decode(a.name,'consistent gets',value,0))) * (-1)) hr,  
       sum(decode(a.name,'physical reads',value,0)) /  
       sum(decode(a.name,'physical writes',value,.00000000001)) rwr,  
       (sum(decode(a.name,'table scan blocks gotten',value,0)) -  
       sum(decode(a.name,'table scans (short tables)',value,0)) * 4) /  
       sum(decode(a.name,'table scans (long tables)',value,.00000000001))  
bpfts  
from   v$sysstat a  
/  

prompt  
prompt -----------------------------------------------------------------  
prompt
prompt This looks at overall i/o activity against individual  
prompt files within a tablespace  
prompt  
prompt Look for a mismatch across disk drives in terms of I/O  
prompt  
prompt Also, examine the Blocks per Read Ratio for heavily accessed  
prompt TSs - if this value is significantly above 1 then you may have  
prompt full tablescans occurring (with multi-block I/O)  
prompt  
prompt If activity on the files is unbalanced, move files around to balance  
prompt the load.  Should see an approximately even set of numbers across files  
prompt  
  
set pagesize 100;  
set space 1  

column pbr       format 99999999  heading 'Physical|Blk Read'  
column pbw       format 999999    heading 'Physical|Blks Wrtn'  
column pyr       format 999999    heading 'Physical|Reads'  
column readtim   format 99999999  heading 'Read|Time'  
column name      format a40       heading 'DataFile Name'  
column writetim  format 99999999  heading 'Write|Time'  

prompt %%%%%%%%%%%%%%%%%%%%%%%%%%%%
ttitle center 'Tablespace Report' skip 2  
prompt %%%%%%%%%%%%%%%%%%%%%%%%%%%%

compute sum of f.phyblkrd, f.phyblkwrt on report  

select fs.name name,  f.phyblkrd pbr,  f.phyblkwrt pbw, 
       f.readtim,     f.writetim  
from   v$filestat f, v$datafile fs  
where  f.file#  =  fs.file#  
order  by fs.name  
/  

prompt  
prompt -----------------------------------------------------------------  
  
prompt %%%%%%%%%%%%%%%%%%%%%%%%%%%%
prompt GENERATING WAIT STATISTICS:  
prompt %%%%%%%%%%%%%%%%%%%%%%%%%%%%
prompt  
prompt This will show wait stats for certain kernel instances.  This  
prompt may show the need for additional rbs, wait lists, db_buffers  
prompt  
 ttitle center 'Wait Statistics for the Instance' skip 2  

column class  heading 'Class Type'  
column count  heading 'Times Waited'  format 99,999,999 
column time   heading 'Total Times'   format 99,999,999  

select class,  count,  time  
from   v$waitstat  
where  count > 0  
order  by class  
/  

prompt  
prompt Look at the wait statistics generated above (if any). They will  
prompt tell you where there is contention in the system.  There will  
prompt usually be some contention in any system - but if the ratio of  
prompt waits for a particular operation starts to rise, you may need to  
prompt add additional resource, such as more database buffers, log buffers,  
prompt or rollback segments  
prompt  
prompt -----------------------------------------------------------------  
  
prompt %%%%%%%%%%%%%%%%%%%%%%%%%%%%
prompt ROLLBACK STATISTICS:  
prompt %%%%%%%%%%%%%%%%%%%%%%%%%%%%
prompt  
  
ttitle off;  

set linesize 80  

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 99,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 -----------------------------------------------------------------  
  
set linesize 80  

break on report  

compute sum of gets waits writes on report  

ttitle center 'Rollback Statistics' skip 2  

select rownum,  extents,  rssize,  
       xacts,   gets,     waits,   writes  
from   v$rollstat  
order  by rownum  
/  

ttitle off  

set heading off  

prompt  
prompt -----------------------------------------------------------------  
  
prompt  
prompt %%%%%%%%%%%%%%%%%%%%%%%%%%%%
prompt SORT AREA SIZE VALUES:  
prompt %%%%%%%%%%%%%%%%%%%%%%%%%%%%
prompt  
prompt To make best use of sort memory, the initial extent of your Users  
prompt sort-work Tablespace should be sufficient to hold at least one sort  
prompt run from memory to reduce dynamic space allocation.  If you are getting  
prompt a high ratio of disk sorts as opposed to memory sorts, setting  
prompt sort_area_retained_size = 0 in init.ora will force the sort area to be  
prompt released immediately after a sort finishes.  
prompt  

column value format 999,999,999  

select 'INIT.ORA sort_area_size: '||value  
from    v$parameter  
where   name like 'sort_area_size' 

  
select a.name,  value  
from   v$statname a,  v$sysstat  
where  a.statistic#  =   v$sysstat.statistic#  
and    a.name        in ('sorts (disk)', 'sorts (memory)', 'sorts (rows)')  
/  

prompt  
prompt -----------------------------------------------------------------  
  
set heading on  
set space 2  

prompt  
prompt This looks at Tablespace Sizing - Total bytes and free bytes  
prompt  

ttitle center 'Tablespace Sizing Information' Skip 2  

column tablespace_name  format a30            heading 'TS Name'  
column sbytes           format 9,999,999,999  heading 'Total Bytes'  
column fbytes           format 9,999,999,999  heading 'Free Bytes'  
column kount            format 999            heading 'Ext'  

compute sum of fbytes on tablespace_name  
compute sum of sbytes on tablespace_name  
compute sum of sbytes on report  
compute sum of fbytes on report  

break on report  

select a.tablespace_name,    a.bytes sbytes,  
       sum(b.bytes) fbytes,  count(*) kount  
from   dba_data_files a,  dba_free_space b  
where  a.file_id  =  b.file_id  
group  by a.tablespace_name, a.bytes  
order  by a.tablespace_name  
/  

set linesize 80  

prompt  
prompt A large number of Free Chunks indicates that the tablespace may need  
prompt to be defragmented and compressed.  
prompt  
prompt -----------------------------------------------------------------  
  
set heading off  

ttitle off  

column value format 99,999,999,999  

select 'Total Physical Reads', value  
from    v$sysstat  
where   statistic# = 39  
/  

prompt  
prompt If you can significantly reduce physical reads by adding incremental  
prompt data buffers...do it.  To determine whether adding data buffers will  
prompt help, set db_block_lru_statistics = TRUE and  
prompt db_block_lru_extended_statistics = TRUE in the init.ora parameters.  
prompt You can determine how many extra hits you would get from memory as  
prompt opposed to physical I/O from disk.  **NOTE:  Turning these on will  
prompt impact performance.  One shift of statistics gathering should be enough  
prompt to get the required information.  
prompt  
  
set heading on  

clear computes  

ttitle off  
spool off