Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

Script to survey unknown database instance

Oracle Database Tips by Burleson Consulting

June 5, 2016

Question: What queries do I run to see a survey of the characteristics of a database?

Answer:  First, see this query to detect all features and options used.  A script to survey a database would include these items:

  • Instance name
  • Release number
  • Uptime
  • CPU cores
  • SGA Size
  • PGA High water mark
  • Space used by segment type
  • Total disk used
  • Object counts by schema
  • Database links
  • Scheduled jobs
  • High Water Marks
  • Optimizer parameters
  • Average disk I/O timings
  • CBO System statistics
  • Physical reads and writes
For a complete survey review to see all of your database characteristics, contact us and we can analyze your existing database and see all of your internal characteristics.
spool s.lst
set pages 999 
prompt *******************************************
prompt Get the instance name
prompt ******************************************* 
select instance_name from v$instance;
clear breaks
set feedback off 
prompt *******************************************
prompt Get the release number
prompt ******************************************* 
select 
   banner 
from 
   v$version
where
   lower(banner) like 'oracle%';
prompt
prompt *******************************************
prompt Display database uptime
prompt ******************************************* 
set heading off
select 
    'Hostname : ' || host_name
    ,'Instance Name : ' || instance_name
    ,'Started At : ' || to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') stime
    ,'Uptime : ' || floor(sysdate - startup_time) || ' days(s) ' ||
    trunc( 24*((sysdate-startup_time) - 
    trunc(sysdate-startup_time))) || ' hour(s) ' ||
    mod(trunc(1440*((sysdate-startup_time) - 
    trunc(sysdate-startup_time))), 60) ||' minute(s) ' ||
    mod(trunc(86400*((sysdate-startup_time) - 
    trunc(sysdate-startup_time))), 60) ||' seconds' uptime
 from 
See code depot for full script
    sys.v_$instance;
set heading on 
prompt
prompt ***********************************************
prompt Get cpu_count
prompt ***********************************************
col c1 heading 'CPU|Cores' format a20
col c2 heading 'Size'      format a20
select
   name  c1,
   value c2
from
   v$parameter
where
   name like '%cpu_count%';
prompt
prompt ***********************************************
prompt Get SGA size from v$sga
prompt ***********************************************
col c1 heading 'Pool|Name' format a20
col c2 heading 'Size'      format 999,999,999
select
   name  c1,
   value c2
from
   v$sga;
select
   sum(value) c2
from
   v$sga;
prompt
prompt ***********************************************
prompt Get RAM size if using manual pools
prompt ***********************************************
col c1 heading 'Pool|Name' format a20
col c2 heading 'Size'      format a20
select
   name  c1,
   value c2
from
See code depot for full script
   v$parameter
where
   (name like '%pool_size%'
or
    name like '%cache_size%')
and
   value is not null
and
   value <> '0';
prompt
prompt ***********************************************
prompt Get RAM size if using AMM
prompt ***********************************************
col c1 heading 'Pool|Name' format a20
col c2 heading 'Size'      format 999,999,999
select
   name  c1,
   to_number(value) c2
from
See code depot for full script
   v$parameter
where
   name like '%target'
and
   (name like 'memory%'
or
   name like 'sga%')
and
   value is not null
and
   value <> '0';
prompt
prompt ***********************************************
prompt Get PGA high water mark
prompt ***********************************************
col c1 heading 'PGA|Stat'  format a40
col c2 heading 'Size'      format 999,999,999
select
   name  c1,
   value c2
from
   v$pgastat
where
   name like ('maximum%');
 
 
prompt ****************************************
prompt Total Disk space used by instance
prompt organized by segment type
prompt **************************************** 
col c1 heading 'Owner'        format a20
col c2 heading 'Segment|Type' format a15
col c3 heading 'MB|Used'      format 999,999,999.999 
break on c1 skip 1 
select 
   owner              c1,
   segment_type       c2,
   sum(bytes)/1048576 c3 
from
See code depot for full script
   dba_segments
where
owner not in (
   'APEX_040200', 
   'MDSYS', 
   'OUTLN', 
   'CTXSYS', 
   'SYSTEM', 
   'DVSYS', 
   'AUDSYS', 
   'DBSNMP', 
   'GSMADMIN_INTERNAL', 
   'OJVMSYS', 
   'ORDSYS', 
   'XDB', 
   'ORDDATA', 
   'SYS', 
   'WMSYS', 
   'LBACSYS') 
group by 
   owner,
   segment_type
order by
   owner,
   segment_type; 
col c3 heading 'Total|Database|Megabytes' format 999,999.999 
select 
   sum(bytes)/1048576 c3 
from
See code depot for full script
   dba_segments
where
owner not in (
   'APEX_040200', 
   'MDSYS', 
   'OUTLN', 
   'CTXSYS', 
   'SYSTEM', 
   'DVSYS', 
   'AUDSYS', 
   'DBSNMP', 
   'GSMADMIN_INTERNAL', 
   'OJVMSYS', 
   'ORDSYS', 
   'XDB', 
   'ORDDATA', 
   'SYS', 
   'WMSYS', 
   'LBACSYS') 
;
prompt *******************************************************
prompt This query counts all objects by schema
prompt *******************************************************
clear breaks
clear computes

col c1 heading 'Schema|Owner' format a20
col c2 heading 'Count'        format 999,999

break on c1 skip 1

compute sum of c2 on c1

select
   owner        c1,
   object_type,
   count(*)     c2
from
See code depot for full script
   dba_objects
where
owner not in (
   'APEX_040200', 
   'MDSYS', 
   'OUTLN', 
   'CTXSYS', 
   'SYSTEM', 
   'PUBLIC',
   'ORDPLUGINS',
   'SI_INFORMTN_SCHEMA',
   'DVF',
   'ORACLE_OCM', 
   'DVSYS', 
   'AUDSYS', 
   'DBSNMP', 
   'FLOWS_FILES',
   'APPQOSSYS', 
   'GSMADMIN_INTERNAL', 
   'OJVMSYS', 
   'ORDSYS', 
   'OLAPSYS', 
   'XDB', 
   'ORDDATA', 
   'SYS', 
   'WMSYS', 
   'LBACSYS') 
group by
   owner,
   object_type
order by
   owner,
   object_type;
prompt *******************************************************
prompt This query displays database links
prompt *******************************************************
clear breaks
clear computes
set lines 80
col c1 heading 'Schema|Owner' format a10
col c2 heading 'Link|Name'    format a20
col c3 heading 'Username'     format a8
col c4 heading 'host'         format a25
col c5 heading 'Created|Date' format a10
break on c1 skip 1
select
   owner    c1,
   db_link  c2,
   username c3,
   host     c4,
   created  c5
from
See code depot for full script
   dba_db_links;
prompt
prompt *******************************************
prompt Display scheduled jobs
prompt ******************************************* 
select 
   job, 
   schema_user, 
   last_date, 
   next_date, 
   interval, 
   what 
from 
   dba_jobs;
prompt
prompt *******************************************
prompt Display login high water mark
prompt ******************************************* 
col c1 heading 'Name'  format a40
col c2 heading 'Value' format 999,999,999
select 
   name  c1, 
   value c2
from 
See code depot for full script
   v$sysstat 
where 
   name like '%logon%';
prompt
prompt *******************************************
prompt Display sessions high water mark
prompt ******************************************* 
select 
   sessions_current, 
   sessions_highwater 
from 
   v$license; 
prompt
prompt *******************************************
prompt Display system high water marks
prompt ******************************************* 
set lines 80
col c1 heading 'Name'            format a58
col c2 heading 'High|Water|Mark' format 999,999,999,999,999
select 
   description c1,
   highwater   c2
from
See code depot for full script
   dba_high_water_mark_statistics
where
   highwater <> 0;
prompt
prompt *******************************************
prompt Display optimizer parameters
prompt ******************************************* 
col c1 heading 'Parameter' format a40
col c2 heading 'Value'     format a15
select 
   name  c1, 
   value c2
from 
See code depot for full script
   v$parameter
where 
   name like 'optim%'
and 
   name not in (
   'optimizer_secure_view_merging',                                
   'optimizer_use_pending_statistics',                               
   'optimizer_capture_sql_plan_baselines',                         
   'optimizer_use_sql_plan_baselines',                       
   'optimizer_use_invisible_indexes',                        
   'optimizer_adaptive_reporting_only',                      
   'optimizer_adaptive_features',                    
   'optimizer_inmemory_aware'
   );
prompt
prompt *******************************************
prompt Display average disk I/O times
prompt ******************************************* 
col c1 heading 'Scattered|Read time (ms)'  format 999
col c2 heading 'Sequential|Read time (ms)' format 999
col c3 heading 'optimizer_index_cost_adj|starting value' format 999
select 
    (
       sum(a.total_waits) / 
       sum(a.total_waits + b.total_waits)
    ) * 100 c1,
    (
       sum(b.total_waits) / 
       sum(a.total_waits + b.total_waits)
    ) * 100 c2,
   (
       sum(b.time_waited_micro) /
       sum(b.total_waits)) / 
       (sum(a.time_waited_micro)/sum(a.total_waits)
    ) * 100 c3 
 from 
See code depot for full script
    dba_hist_system_event a, 
    dba_hist_system_event b
 where 
    a.snap_id = b.snap_id
 and 
    a.event_name = 'db file scattered read'
 and 
    b.event_name = 'db file sequential read';
prompt
prompt *******************************************
prompt Display CBO system statistics
prompt ******************************************* 
col c1 heading 'Read Time' format a20
col c2 heading 'Time (ms)' format 999,999.99
select 
   pname c1,
   pval1 c2
from 
See code depot for full script
   sys.aux_stats$
where
   pname not in(
   'STATUS',
   'DSTART',
   'DSTOP',
   'FLAGS'); 
prompt
prompt *******************************************
prompt Physical reads and writes
prompt ******************************************* 
col c1 heading 'Name'      format a40
col c2 heading 'Megabytes' format 999,999,999,999
select
   name            c1, 
   value/1024/1024 c2 
from 
See code depot for full script
   v$sysstat a 
where
        a.name = 'physical read total bytes' OR
        a.name = 'physical write total bytes' OR
        a.name = 'cell physical IO interconnect bytes';
********************************************
prompt Detect important system stats
prompt ********************************************** 
col c1 heading 'Statistic' format a40
col c2 heading 'Value'     format 999,999,999,999,999
select 
   name  c1,
   value c2
from
See code depot for full script
   v$sysstat
where
   value > 0
and
name in (
   'Forwarded 2PC commands across RAC nodes',
   'RowCR - row contention',
   'SQL*Net roundtrips to/from client', 
   'SQL*Net roundtrips to/from dblink',
   'bytes received via SQL*Net from client',
   'bytes received via SQL*Net from dblink',
   'bytes sent via SQL*Net to client',   
   'bytes sent via SQL*Net to dblink',
   'db corrupt blocks detected',
   'index fast full scans (direct read)',
   'index fast full scans (full)',
   'index fast full scans (rowid ranges)',   
   'index fetch by key',    
   'parse count (hard)',
   'parse count (total)',
   'physical read IO requests',
   'physical reads', 
   'physical writes',
   'session pga memory max',
   'sorts (disk)',
   'table fetch continued row',
   'transaction rollbacks',
   'user logons cumulative',
   'very large tracked transactions',
   'workarea executions - multipass'
);
prompt *******************************************
prompt Get min and max values for metrics
prompt ******************************************* 
col metric_name format a35
col minimum     format 99,999
col maximum     format 999,999,999
col average     format 99,999.999
select  CASE METRIC_NAME
            WHEN 'SQL Service Response Time' then 'SQL Service Response Time (secs)'
            WHEN 'Response Time Per Txn' then 'Response Time Per Txn (secs)'
            ELSE METRIC_NAME
            END METRIC_NAME,
                CASE METRIC_NAME
            WHEN 'SQL Service Response Time' then ROUND((MINVAL / 100),2)
            WHEN 'Response Time Per Txn' then ROUND((MINVAL / 100),2)
            ELSE MINVAL
            END MININUM,
                CASE METRIC_NAME
            WHEN 'SQL Service Response Time' then ROUND((MAXVAL / 100),2)
            WHEN 'Response Time Per Txn' then ROUND((MAXVAL / 100),2)
            ELSE MAXVAL
            END MAXIMUM,
                CASE METRIC_NAME
            WHEN 'SQL Service Response Time' then ROUND((AVERAGE / 100),2)
            WHEN 'Response Time Per Txn' then ROUND((AVERAGE / 100),2)
            ELSE AVERAGE
            END AVERAGE
from    
See code depot for full script
SYS.V_$SYSMETRIC_SUMMARY 
where   METRIC_NAME in ('CPU Usage Per Sec',
                      'CPU Usage Per Txn',
                      'Database CPU Time Ratio',
                      'Database Wait Time Ratio',
                      'Executions Per Sec',
                      'Executions Per Txn',
                      'Response Time Per Txn',
                      'SQL Service Response Time',
                      'User Transaction Per Sec')
ORDER BY 1;
prompt *******************************************
prompt Get min and max values for metrics
prompt ******************************************* 
col c1 heading 'Metric' format a40
col c2 heading 'Value'  format 999,999,999.99
select  
   metric_name c1,
   value       c2
from    
See code depot for full script
   sys.v_$sysmetric
where
   INTSIZE_CSEC = 
   (select max(INTSIZE_CSEC) from SYS.V_$SYSMETRIC)
and
(
   lower(metric_name) like '%cache hit%'
or
   lower(metric_name) like 'i/o%'
); 
prompt *******************************************
prompt Get system waits
prompt ******************************************* 
col c1 heading 'Wait Class'   format a15
col c2 heading 'Total|Waits'  format 999,999,999,999.99
col c3 heading 'Pct|Waits'    format 999
col c4 heading 'Time|Waited|(sec)' format 999,999,999,999.99
col c5 heading 'Pct|Time'    format 999
select  
   wait_class                               c1,
   total_waits                              c2,
   round(100 * (total_waits / sum_waits),2) c3,
   round((time_waited / 100),2)             c4,
   round(100 * (time_waited / sum_time),2)  c5
from
See code depot for full script
(select wait_class,
        total_waits,
        time_waited
from    v$system_wait_class
where   wait_class != 'idle'),
(select  sum(total_waits) sum_waits,
        sum(time_waited) sum_time
from    v$system_wait_class
where   wait_class != 'idle')
order by 5 desc;
prompt *******************************************
prompt Is STATSPACK installed?
prompt ******************************************* 

col c1 heading 'STATSPACK|Installed' format a20 

select 
   decode(count(*), 0, 'No', 'Yes') c1
from
   dba_users
where
   username = 'PERFSTAT';
 

 

 


   
Oracle Training from Don Burleson 

The best on site "Oracle training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!

Oracle training
 
 


  Oracle consulting and training

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2016

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.


 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster