Database Performnace monitoring

set pagesize 1000
SET LINESIZE 110
col COMP_ID format A9
col COMP_NAME format A40
col STATUS format A15
col VERSION format A12
col owner format a30
col object_name format a30
col object_type format a30
SPOOL DB_DD_CHECK.TXT
/* Database identification */
select NAME, PLATFORM_ID, DATABASE_ROLE from v$database;
select * from V$version where banner like 'Oracle Database%';
select INSTANCE_NAME, to_char(STARTUP_TIME,'DD/MM/YYYY HH24:MI:SS') "STARTUP_TIME" from v$instance;
/* Database Component Registry status */
select comp_id, comp_name, status, version from dba_registry;
/* Level of patch in the database */
select * from dba_registry_history;
/* INVALID objects count, by type & in detail */
select count(*) "INVALID Objects Count" from dba_objects where status !='VALID';
select owner, object_type, count(*) from dba_objects where status !='VALID' group by owner, object_type order by owner, object_type;
select owner, object_type, object_name, status from dba_objects where status !='VALID' order by owner, object_type, object_name;
SPOOL OFF

 High CPU USAGE
--------------
select
   ss.username,
   se.SID,
   VALUE/100 cpu_usage_seconds
from
   v$session ss,
   v$sesstat se,
   v$statname sn
where
   se.STATISTIC# = sn.STATISTIC#
and
   NAME like '%CPU used by this session%'
and
   se.SID = ss.SID
and
   ss.status='ACTIVE'
and
   ss.username is not null
order by VALUE desc;

 Log waiters
----------------
SET LINESIZE 180
COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A30
SELECT s.inst_id,
s.sid,
s.serial#,
p.spid,
s.username,
s.program,
s.seconds_in_wait
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != 'BACKGROUND'
AND sid='&a';

  SQL text Based on the SID
select sid, sql_text from
v$sqltext t,
v$session s
where
s.sql_address = t.address
and s.sid = '&a'
order by s.sid, piece;

 Waiter more than  3600 seconds

 set lines 180
column sid format 999999999
column username format a12
column status format a8
column osuser format a10
column process format a10
column wait_class format a20
column wait_time format 999999999
column seconds_in_wait format 999999999
column spid format a10
column pname format a10
column username format a20
column serial# format 999999999
select s.sid, s.serial#, s.username,
       s.status, s.osuser,
       s.process, s.wait_class,
       s.wait_time, s.seconds_in_wait,
       p.spid, p.pname, p.username
from v$session s, v$process p
where      s.paddr = p.addr
       and s.seconds_in_wait > 3600
and (p.pname is null or (p.pname != 'MMNL' and p.pname != 'E000'))
       and s.wait_class != 'Idle'; 
 
Blockers on the database
----------------------------
column sess format A20
SELECT substr(DECODE(request,0,'Holder: ','Waiter: ')||sid,1,12) sess,
       id1, id2, lmode, request, type, inst_id
 FROM GV$LOCK
WHERE (id1, id2, type) IN
   (SELECT id1, id2, type FROM GV$LOCK WHERE request>0)
     ORDER BY id1, request;
select s1.username || '@' || s1.machine  || ' ( SID=' || s1.sid || ' ) is blocking '  || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;
---------------------------------------------
select b.session_id ,a.SERIAL#, a.username "Blocker Details"
from v$session a,dba_lock b
where b.session_id = a.sid
and b.blocking_others = 'Blocking';
--------------------------------

 Based on the  OS SPID  find the SID,OS user information

 col sid format 999999
col username format a20
col osuser format a15
select b.spid,a.sid, a.serial#,a.username, a.osuser
from v$session a, v$process b
where a.paddr= b.addr
and b.spid='&spid'
order by b.spid;

 uncommited Transactions

 set lines 250
column start_time format a20
column sid format 999
column serial# format 999999
column username format a10
column status format a10
column schemaname format a10
column osuser format a10
column process format a10
column machine format a15
column terminal format a10
column program format a25
column module format a10
column logon format a20
prompt ####################################################
prompt # current transactions:
prompt ####################################################
select t.start_time,s.sid,s.serial#,s.username,s.status,s.schemaname,
s.osuser,s.process,s.machine,s.terminal,s.program,s.module,to_char(s.logon_time,'DD/MON/YY HH24:MI:SS') logon_time
from v$transaction t, v$session s
where s.saddr = t.ses_addr
order by start_time;
If you got the sid it should be easy to find SQL by using the following sql :

 select s.sid, q.sql_text from v$sqltext q, v$session s
 where q.address = s.sql_address
 and s.sid = &sid
 order by piece;
----------------------------------------

 col sid format 999999
col username format a20
col osuser format a15
select b.spid,a.sid, a.serial#,a.username, a.osuser
from v$session a, v$process b
where a.paddr= b.addr
and b.spid='&spid'
order by b.spid;
------------------------------------
find the user sessions

 select substr(s.username,1,20) "User Name",
s.osuser "OS User",
s.status "Status",
lockwait "Lock Wait",
substr(s.program,1,30) "Program",
substr(s.machine,1,15) "Machine",
p.program "Process Program",
si.consistent_gets "Consistent Gets",
s.process "Process PID",
p.spid, p.pid, s.serial#, si.sid
from sys.v_$sess_io si, sys.v_$session s, sys.v_$process p
where s.username is not null and
si.sid(+)=s.sid
and p.addr(+)=s.paddr
order by si.consistent_gets desc

DB link information
--------------------------

 COLUMN host             FORMAT a18      HEADING "Connect|String"
COLUMN owner            FORMAT a8      HEADING "Creator"
COLUMN db_link          FORMAT a19      HEADING "DB Link|Name"
COLUMN username         FORMAT a8      HEADING "Connect|User"
COLUMN created          FORMAT a15      HEADING "Date|Created"
SELECT
     host,owner,db_link,username,
     to_char(created,'dd-mon-yy hh24:mi') created
FROM
     DBA_db_links ORDER BY owner,host;
CLEAR BUFFER
------------
alter system flush buffer_cache;
alter system flush shared_pool;

TABLESPACE LOCATION

set lines 150
column file_name format a80
column MB format 999,999,999
column MAXMB format 999,999,999
select file_name, bytes/1024/1024 MB, autoextensible, maxbytes/1024/1024 MAXMB
from dba_data_files
where tablespace_name = upper('&ts_name');

Tablespace usage

select  df.tablespace_name,
        df.MB TOTAL,
        decode(fs.MB,null,0,fs.MB) FREE,
        to_number(to_char((decode(fs.MB,null,0,fs.MB)
                             *100)/df.MB,'999.99')) PCT_FREE
from
(select tablespace_name, sum(bytes)/1024/1024 MB
from dba_data_files
group by tablespace_name) df
left join
(select tablespace_name, sum(bytes)/1024/1024 MB
from dba_free_space
group by tablespace_name) fs
on df.tablespace_name = fs.tablespace_name
order by 4 asc;
undo usage

set lines 150
select to_char(begin_time,'hh24:mi dd-mon-yyyy') "START",
       to_char(end_time,'hh24:mi dd-mon-yyyy') "END",
       unxpstealcnt,
       expstealcnt,
       activeblks,
       unexpiredblks,
       expiredblks,
       tuned_undoretention
from v$undostat
order by end_time;

Total DB size in GB

COLUMN "Total Gb" FORMAT 999,999,999.0
COLUMN "Redo Gb" FORMAT 999,999,999.0
COLUMN "Temp Gb" FORMAT 999,999,999.0
COLUMN "Data Gb" FORMAT 999,999,999.0
Prompt
Prompt "Database Size"
select (select sum(bytes/1073741824) from dba_data_files) "Data Gb",
(select NVL(sum(bytes/1073741824),0) from dba_temp_files) "Temp Gb",
(select sum(bytes/1073741824)*max(members) from v$log) "Redo Gb",
(select sum(bytes/1073741824) from dba_data_files) +
(select NVL(sum(bytes/1073741824),0) from dba_temp_files) +
(select sum(bytes/1073741824)*max(members) from v$log) "Total Gb"
from dual;

ASM usage informaion

SET LINESIZE  145
SET PAGESIZE  9999
SET VERIFY    off
COLUMN disk_group_name        FORMAT a20           HEAD 'Disk Group Name'
COLUMN disk_file_path         FORMAT a17           HEAD 'Path'
COLUMN disk_file_name         FORMAT a20           HEAD 'File Name'
COLUMN disk_file_fail_group   FORMAT a20           HEAD 'Fail Group'
COLUMN total_mb               FORMAT 999,999,999   HEAD 'File Size (MB)'
COLUMN used_mb                FORMAT 999,999,999   HEAD 'Used Size (MB)'
COLUMN pct_used               FORMAT 999.99        HEAD 'Pct. Used'
break on report on disk_group_name skip 1
compute sum label ""              of total_mb used_mb on disk_group_name
compute sum label "Grand Total: " of total_mb used_mb on report
SELECT
    NVL(a.name, '[CANDIDATE]')                       disk_group_name
  , b.path                                           disk_file_path
  , b.name                                           disk_file_name
  , b.failgroup                                      disk_file_fail_group
  , b.total_mb                                       total_mb
  , (b.total_mb - b.free_mb)                         used_mb
  , ROUND((1- (b.free_mb / b.total_mb))*100, 2)      pct_used
FROM
    v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number)
ORDER BY
    a.name
/

asm_disksreport.sql

SET LINESIZE  145
SET PAGESIZE  9999
SET VERIFY    off
COLUMN group_name             FORMAT a20           HEAD 'Disk Group|Name'
COLUMN sector_size            FORMAT 99,999        HEAD 'Sector|Size'
COLUMN block_size             FORMAT 99,999        HEAD 'Block|Size'
COLUMN allocation_unit_size   FORMAT 999,999,999   HEAD 'Allocation|Unit Size'
COLUMN state                  FORMAT a11           HEAD 'State'
COLUMN type                   FORMAT a6            HEAD 'Type'
COLUMN total_mb               FORMAT 999,999,999   HEAD 'Total Size (MB)'
COLUMN used_mb                FORMAT 999,999,999   HEAD 'Used Size (MB)'
COLUMN pct_used               FORMAT 999.99        HEAD 'Pct. Used'
break on report on disk_group_name skip 1
compute sum label "Grand Total: " of total_mb used_mb on report
SELECT
    name                                     group_name
  , sector_size                              sector_size
  , block_size                               block_size
  , allocation_unit_size                     allocation_unit_size
  , state                                    state
  , type                                     type
  , total_mb                                 total_mb
  , (total_mb - free_mb)                     used_mb
  , ROUND((1- (free_mb / total_mb))*100, 2)  pct_used
FROM
    v$asm_diskgroup
ORDER BY
    name
/

job_scheduler_report.sql

set feedback off
set echo off
set lines 205
column owner format a15
column job_name format a22
column program_name format a24
column status format a10
column state format a10
column last_start format a25
column last_start_norm format a18
column "DURATION (d:hh:mm:ss)" format a21
column next_run format a25
column next_run_norm format a18
var local_offset number
begin
   select extract(timezone_hour from systimestamp) into :local_offset from dual;
end;
/
select dsj.owner,
       dsj.job_name,
       dsj.program_name,
       dsjlmax.status,
       dsj.state,
--     to_char(dsj.last_start_date,'dd-mon-yyyy hh24:mi TZH:TZM') last_start,
       to_char(dsj.last_start_date + (:local_offset-extract(timezone_hour from dsj.last_start_date))/24,'dd-mon-yyyy hh24:mi') last_start_norm,
       extract(day from dsj.last_run_duration) ||':'||
          lpad(extract(hour from dsj.last_run_duration),2,'0')||':'||
          lpad(extract(minute from dsj.last_run_duration),2,'0')||':'||
          lpad(round(extract(second from dsj.last_run_duration)),2,'0') "DURATION (d:hh:mm:ss)",
--     to_char(dsj.next_run_date,'dd-mon-yyyy hh24:mi TZH:TZM') next_run,
       to_char(dsj.next_run_date + (:local_offset-extract(timezone_hour from dsj.next_run_date))/24,'dd-mon-yyyy hh24:mi') next_run_norm
from dba_scheduler_jobs dsj,
(select job_name, status
from dba_scheduler_job_log dsjl
where log_date =
   (select max(log_date)
    from dba_scheduler_job_log dsjl2
    where dsjl2.job_name = dsjl.job_name)
) dsjlmax
where dsj.owner not in ('SYS','SYSTEM','EXFSYS','ORACLE_OCM')
and dsj.job_name = dsjlmax.job_name;

Comments

Post a Comment

Popular posts from this blog

QUERY DATAGUARD AND STANDBY STATUS

-- Find which logs were applied in the last day  SELECT SEQUENCE#, to_char(FIRST_TIME,'hh24:mi:ss dd/mm/yyyy'), to_char(NEXT_TIME,'hh24:mi:ss dd/mm/yyyy'),APPLIED FROM V$ARCHIVED_LOG where next_time>sysdate-1 ORDER BY SEQUENCE# ; -- Find last applied log   select to_char(max(FIRST_TIME),'hh24:mi:ss dd/mm/yyyy') FROM V$ARCHIVED_LOG where applied='YES';   -- What are the managed standby processes doing?   SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;   -- Are we on production or standby?  SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM V$DATABASE;   -- Check for errors  SELECT MESSAGE FROM V$DATAGUARD_STATUS;   -- Check that the DB was openned correctly   SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS; -- important lag statistics  select * from v$dataguard_stats;  -- co...
Read more

Oracle Standby database

Shutting Down a Physical Standby Database Issue the following query to find out if the standby database is performing Redo Apply or real-time apply.  If the MRP0 or MRP process exists, then the standby database is applying redo. SQL> SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY; If Redo Apply is running, cancel it as shown in the following example: SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; Shut down the standby database. SQL> SHUTDOWN IMMEDIATE; Start and mount the physical standby database: SQL> STARTUP MOUNT; Start Redo Apply or real-time apply: To start Redo Apply, issue the following statement: SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  DISCONNECT FROM SESSION; To start real-time apply, issue the following statement: SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  USING CURRENT LOGFILE; ALTER SESSION SET nls_date_format=’DD-MON-YYYY HH24:MI:SS’; select ...
Read more

DBA_LOCKS

select l.session_id, l.oracle_username, l.os_user_name, o.object_name , o.owner from gv$locked_object l, dba_objects o where l.object_id = o.object_id; select l.session_id, l.oracle_username, l.os_user_name, o.object_name , o.owner, 'alter system kill session ''' || s.sid || ',' || s.serial# ||''';' kill_session from v$locked_object l, dba_objects o, v$session s where l.object_id = o.object_id and l.session_id = s.sid; select sid, serial# from gv$session where sid = 0000; alter sysetm kill session ''; select s.sid, s.serial#, p.spid unix_process, s.osuser, s.username, s.machine, s.program, s.sql_hash_value current_hash_value, q1.sql_text current_sql_text, s.prev_hash_value prev_hash_value, q2.sql_text prev_sql_text, to_char(s.logon_time,'DD/MM/YYYY HH24:MI') session_logon_time, o.owner ||'.'|| o.object_name locked_object, l.type lock_type, l.lmode lock_mode, l.ctime lock_time_in_sec from v$lock l, v$session s, v$pro...
Read more