Unix Addiction

SET PAGESIZE 1000 SELECT SUBSTR(t.name,1,50) AS file_name,        f.phyblkrd AS blocks_read,        f.phyblkwrt AS blocks_written,        f.phyblkrd + f.phyblkwrt AS total_io FROM   v$tempstat f,        v$tempfile t WHERE  t.file# = f.file# ORDER BY f.phyblkrd + f.phyblkwrt DESC;

SET LINESIZE 300 COLUMN name  FORMAT A30 COLUMN value FORMAT A60 COLUMN displayvalue FORMAT A60 SELECT sp.sid,        sp.name,        sp.value,        sp.display_value FROM   v$spparameter sp ORDER BY sp.name, sp.sid;

SELECT property_name,        property_value FROM   database_properties ORDER BY property_name;

COLUMN size_for_estimate          FORMAT 999,999,999,999 heading 'Cache Size (MB)' COLUMN buffers_for_estimate       FORMAT 999,999,999 heading 'Buffers' COLUMN estd_physical_read_factor  FORMAT 999.90 heading 'Estd Phys|Read Factor' COLUMN estd_physical_reads        FORMAT 999,999,999,999 heading 'Estd Phys| Reads' SELECT size_for_estimate,         buffers_for_estimate,        estd_physical_read_factor,        estd_physical_reads FROM   v$db_cache_advice WHERE  name          = 'DEFAULT' AND    block_size    = (SELECT value                         FROM   v$parameter                         WHERE  name = 'db_block_size') AND    advice_status = 'ON';                                 Estd Phys        Estd Phys  Cache Size (MB)      Buffers Read Factor            Reads ---------------- ------------ ----------- ----------------               48        5,913        2.23    2,321,820,977               96       11,

SET LINESIZE 200 COLUMN file_name FORMAT A70 SELECT file_id,        file_name,        ROUND(bytes/1024/1024/1024) AS size_gb,        ROUND(maxbytes/1024/1024/1024) AS max_size_gb,        autoextensible,        increment_by,        status FROM   dba_data_files ORDER BY file_name;

The hit ratio should be above 92% SELECT Sum(Decode(a.name, 'consistent gets', a.value, 0)) "Consistent Gets",        Sum(Decode(a.name, 'db block gets', a.value, 0)) "DB Block Gets",        Sum(Decode(a.name, 'physical reads', a.value, 0)) "Physical Reads",        Round(((Sum(Decode(a.name, 'consistent gets', a.value, 0)) +          Sum(Decode(a.name, 'db block gets', a.value, 0)) -          Sum(Decode(a.name, 'physical reads', a.value, 0))  )/            (Sum(Decode(a.name, 'consistent gets', a.value, 0)) +              Sum(Decode(a.name, 'db block gets', a.value, 0))))              *100,2) "Hit Ratio %" FROM   v$sysstat a; Consistent Gets DB Block Gets Physical Reads Hit Ratio % --------------- ------------- -------------- ----------- 219290056242 935535251 84827353092 61,48

SET LINESIZE 800 SET PAGESIZE 100 COLUMN username FORMAT A15 COLUMN machine FORMAT A25 COLUMN logon_time FORMAT A20 SELECT NVL(s.username, '(oracle)') AS username,        s.osuser,        s.sid,        s.serial#,        p.spid,        s.lockwait,        s.status,        s.module,        s.machine,        s.program,        TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time,        s.last_call_et AS last_call_et_secs FROM   v$session s,        v$process p WHERE  s.paddr  = p.addr AND    s.status = 'ACTIVE' ORDER BY s.username, s.osuser;

set linesize 300 set pagesize 80 select event, total_waits,time_waited  from V$system_event where event NOT IN ('pmon timer', 'smon timer', 'rdbms ipc reply', 'parallel deque wait', 'virtual circuit', '%SQL*Net%', 'client message', 'NULL event') order by time_waited desc; EVENT                                                            TOTAL_WAITS TIME_WAITED ---------------------------------------------------------------- ----------- ----------- SQL*Net message from client                                       2544593029  9.5738E+10 rdbms ipc message                                                   59890948  9356298126 EMON slave idle wait                                                 4176203  2088107168 Space Manager: slave idle wait                                       5305448  1054797785 DIAG idle wait                                                       8318686   831907680 Streams AQ: emn c

ORACLE DATABASE 10 or greater ORACLE SOLARIS SPARC 1.  As "root" use create  OSWatcher startup/stop script (startOSWbb.sh) # uname -a SunOS solaristest  5.10 Generic_150-400 # pwd /etc/init.d # cat OSW_init.sh  OSW_SRC_DIR= <<<<----- Modify this to reflect your OS Watcher source directory echo $OSW_SRC_DIR echo "******************************************************" >> $OSW_SRC_DIR/init_osw.log case $1 in 'start') echo "...Starting OSWBB from init at `date` " >> $OSWBB_SRC_DIR/init_osw.log cd $OSWBB_SRC_DIR; ./startOSWbb.sh ;; 'stop') echo "...Stopping OSWBB from init at `date` " >> $OSWBB_SRC_DIR/init_osw.log cd $OSW_SRC_DIR; ./stopOSWbb.sh ;; *) echo "Usage: $0 start|stop" >&2 exit 1 ;; esac exit 0   2.  Add "execute" permissions on this script: #chmod +x OSW_init.sh 3.  Create a soft link to this script fr

col read_time format a9 heading "Read Time|(ms)" col reads format 999999,99999999,9999999 heading "Reads" col histogram format a51 heading "" set pagesize 10000 set lines 100  set echo on  SELECT LAG(singleblkrdtim_milli, 1)           OVER (ORDER BY singleblkrdtim_milli)            || '<' || singleblkrdtim_milli read_time,         SUM(singleblkrds) reads,        RPAD(' ', ROUND(SUM(singleblkrds) * 50 /           MAX(SUM(singleblkrds)) OVER ()), '*')  histogram FROM v$file_histogram GROUP BY singleblkrdtim_milli ORDER BY singleblkrdtim_milli;  Read Time (ms)                         Reads --------- ------------------------ --------------------------------------------------- <1                     660,1811398  ************************************************* 1<2                     25,2251982  * 2<4                      4,5348096 4<8                        5699221 8<16                    

set pagesize 1000 set lines 1000 col sid format 9999 col username format a12 col module format a30 column pga_memory_mb format 9,99999.99 heading "PGA MB" column max_pga_memory_mb format 9,999999.99 heading "PGA MAX|MB" col service name format a20  col sql_text format a70 heading "Currently executing SQL" set echo on  WITH pga AS      (SELECT sid,             ROUND(SUM(CASE name WHEN 'session pga memory'                         THEN VALUE / 1048576 END),2) pga_memory_mb,             ROUND(SUM(CASE name WHEN 'session pga memory max'                        THEN VALUE / 1048576  END),2) max_pga_memory_mb       FROM v$sesstat         JOIN v$statname  USING (statistic#)      WHERE name IN ('session pga memory','session pga memory max' )      GROUP BY sid) SELECT sid, username,s.module,         pga_memory_mb,         max_pga_memory_mb, substr(sql_text,1,70) sql_text   FROM v$session s   JOIN (SELECT sid