Life Without Tools: Monitoring Database Activity With The Power Of SQL

1
Life Without Tools Monitoring Database Activity
With The Power Of SQL

• Ari Kaplan
• Independent Consultant

2
(No Transcript)
3
Quick Overview of Typical Monitoring Views
4
Who Is Logged On?

• The VSESSION view contains information on all
  active sessions

SELECT sid, schemaname, osuser
substr(machine,1,20) MachineFROM vsessionORDER
BY schemaname
5
Who Is Logged On?
SELECT sid, schemaname, osuser,
substr(machine,1,20) MachineFROM vsessionORDER
BY schemaname

• A sample output from the above SQL follows
• SID SCHEMANAME OSUSER MACHINE
• 1 SYS
• 2 SYS
• 3 SYS
• 4 SYS
• 7 SYSTEM plat headq
• 13 SYSTEM oracle headq
• 6 WWW_DBA oracle uk_office
• 14 WWW_DBA oracle uk_office
• 12 PRODMAN mr_boss ny_office

6
What SQL Statement Is A Particular User Account
Executing?
SELECT sql_textFROM vsqlareaWHERE (address,
hash_value) IN (SELECT sql_address,
sql_hash_value FROM vsession WHERE sid
sid_number)
7
What SQL Statement Is A Particular User Account
Executing?
SELECT sql_textFROM vsqlareaWHERE (address,
hash_value) IN (SELECT sql_address,
sql_hash_value FROM vsession WHERE sid
sid_number)

• A sample output from the above SQL follows
• Enter value for sid_number 17
• old 1 WHERE SID sid_number
• new 1 WHERE USERNAME 17
• SQL_TEXT
• Update EMP set first_name Ari where
  first_name Arie

8
Setting Up An Interactive Script For Viewing
Users And SQL
SQL Script SQL1.SQL
SQL Script SQL2.SQL
SELECT sid, schemaname, osuser,
substr(machine,1,20) Machine FROM vsession ORDER
BY schemaname
SELECT sql_textFROM vsqlareaWHERE (address,
hash_value) IN (SELECT sql_address, sql_hash_va
lue FROM vsession WHERE sid sid_number
What SQL is used?

• Who is logged in to the database?

UNIX Script SQL_RUN
echo Enter the database to connect toread
DATABASEecho Enter the password for the SYSTEM
userstty -echoecho Password\cread
PASSWDstty echoecho sqlplus
system/PASSWD\_at_DATABASE ltlt EOF_at_sql1.sqlEOF
echo Enter the SID for which SQL you wish to
seeread SID_NUMsqlplus system/PASSWD_at_DATABASE
ltlt EOFselect sql_text from vsqlareawhere
(address, hash_value) in(select sql_addresss,
sql_hash_valuefrom vsessionWHERE sid
SID_NUM)/EOF
9
What is my datafile activity like?
SELECT file_name, phyrds, phywrts,
decode(phyrds,0,0,phyblkrd/phyrds) Blocks/Read
decode(phywrts,0,0,phyblkwrt/phywrts)
Blocks/WriteFROM dba_data_files,
vfilestatWHERE dba_data_files.file_idvfilesta
t.fileFILE_NAME
PHYRDS PHYWRTS BLOCKS/READ
BLOCKS/WRITE/u01/oradata/PRODDB/system01.dbf
10243 3478
4.928
1/u02/oradata/PRODDB/rbs01.dbf
1759 49649
1
1/u03/oradata/PRODDB/temp01.dbf
0 0
0
0/u04/oradata/PRODDB/tools01.dbf
0 0
0
0/u05/oradata/PRODDB/ctxdata01.dbf 187293
15846 1.959
1/u05/oradata/PRODDB/
ctxdata02.dbf 455377 397 4
14. 258
1/u05/oradata/PRODDB/ctxindex01.dbf
332860 616291
1.248
1/u02/oradata/PRODDB/rbs02.dbf
91528 11593
6.130 1
10
Which Rollback Segments Are Being Used By Which
User Accounts?

• SET TRANSACTION USE ROLLBACK SEGMENT
  rollback_segment_name specifies a rollback
  segment to use. This is issued first, or
  immediately after COMMIT or ROLLBACK.
• Use VROLLSTAT and VROLLNAME to get general
  rollback segment activity
• Link VROLLNAME with VLOCK and VSESSION to
  determine which users are using which rollback
  segments.
• More information on the users can be collected by
  further joining with the VPROCESS view.

11
Which Rollback Segments Are Being Used By Which
User Accounts?

• When a user is doing rollback activity, Oracle
  creates a latch (lock of type TX) on an extent
  of a rollback segment.
• The USN (Unique Serial Number) of VROLLNAME
  links with VLOCKs ID1 column. The ID1 column
  contains the object id of the object (rollback
  segment) being locked.
• The VLOCK view contains a SID column which links
  to the SID column of VSESSION.
• The VSESSION view links to the VPROCESS view
  via the PADDR column.

12
Which Rollback Segments Are Being Used By Which
User Accounts?
NOTE Only relevant columns are listed with tables
VLOCK
VROLLNAME
ID1SIDTYPELMODE
NAMEUSN
VPROCESS
ADDRPIDSPIDUSERNAMETERMINAL
VSESSION
SIDPADDR
13
Which Rollback Segments Are Being Used By Which
User Accounts?
echo Enter the password for the SYSTEM
userread PASSWDsqlplus system/PASSWD ltlt
EOFspool temp.lstset head offset pagesize
0connect system\PASSWD_at_DBNAMESELECT
to_char(rownum3)) rpad(r.name,17)rpad(to_
char(p.pid),11) rpad(s.sid,6)
rpad(p.spid,11) rpad(nvl(p.username,NO
TRANSACTION),17) rpad(p.terminal,8)FROM
v\lock l, v\process p, v\rollname r,
v\session sWHERE l.sid s.sid () AND p.addr
s.paddr AND l.type()TX AND
l.lmode()6 AND trunc(l.idl1()/65536)
r.usnUNIONSELECT 2) ROLLBACK SEGORACLE PID
SYSTEM PID SID TRANSACTION TERMINALFROM
dualUNION SELECT 1) FROM dualUNION SELECT
3) ---------------- ---------- ----------
----------- -------- FROM dualORDER BY 1EOF
14
Which Rollback Segments Are Being Used By Which
User Accounts?

• Below is a sample output from the preceding SQL

1)2) ROLLBACK SEGMENT ORACLE PID SYSTEM PID
SID TRANSACTION TERMINAL3)---------------------
---- --------------- --------------- ----
----------------- ------------4) R01
70 1632 14
oracle ?5) BIG_ROLL
65 1492
21 oracle ?
15
What Has The Largest Number Of Concurrent Users
Been?

• set pagesize 24
• set linesize 100
• SELECT rpad(c.name,11)chr(9)chr(9)
• rpad( current logons(to_number(b.sessions_c
  urrent)-1),20)chr(10)
• cumulative logonsrpad(substr(a.value,1,12),
  12)chr(9)
• highwater markb.sessions_highwaterchr(9)
• FROM vsysstat a, vlicense b, vdatabase c
• WHERE a.name logons cumulative

16
What Has The Largest Number Of Concurrent Users
Been?

• Output from the preceding SQL

PINDB current logons298 cumulative logons7967
highwater mark391
17
What Has The Largest Number Of Concurrent Users
Been?

• export LOGDIRORACLE_HOME/udump
• echo Enter the password for the SYSTEM user
• read PASSWD
• for SID in PRODDB TESTDB TRAINDB DEVDB
• do
• sqlplus system/PASSWD_at_SID ltlt EOF gtgt
  LOGDIR/temp_users.lst
• set linesize 100
• SELECT rpad(c.name,11)chr(9)chr(9)
• rpad( current logons(to_number(b.sessions_c
  urrent)-1),20)chr(10)
• cumulative logonsrpad(substr(a.value,1,12),
  12)chr(9)
• highwater markb.sessions_highwaterchr(9)
• FROM v\sysstat a, v\license b, v\database c
• WHERE a.name logons cumulative
• EOF
• done
• cat LOGDIR/temp_users.lst grep LOG gt
  LOGDIR/archivedate md.log
• rm LOGDIR/temp_users.lst

Loop through four databases
18
What Has The Largest Number Of Concurrent Users
Been?

• Output from the preceding SQL for four databases

PRODDB current logons298 cumulative
logons7967 highwater mark391TESTDB
current logons15 cumulative logons187
highwater mark15TRAINDB current logons8
cumulative logons613 highwater
mark25DEVDB current logons23 cumulative
logons1024 highwater mark30
19
Which Locks Are Being Held?

• -- VLOCK holds information on internal Oracle
  locks.
• -- Six views show information more clearly
• DBA_LOCK locks held and locks requestedis
  blocking?
• DBA_LOCK_INTERNAL locks held and locks
  requested
• DBA_DML_LOCKS DML locks (subset of DBA_LOCK)
• DBA_DDL_LOCKS DDL locks (subset of DBA_LOCK)
• DBA_WAITERS which sessions are holding and
  waiting for locks?
• DBA_BLOCKERS which sessions are holding locks
  but are not waiting on another lock themselves?
• -- These are created by the ORACLE_HOME/rdbms/adm
  in/catblock.sql script
• -- Use ORACLE_HOME/rdbms/admin/utllockt.sql to
  analyze these views.

20
Which Locks Are Being Held?

• SELECT FROM DBA_LOCK

SESSION_ID TYPE MODE_HELD
MODE_REQUESTED---------------
------------------- ---------------
-----------------------LOCK_ID1 LOCK_ID2
LAST_CONVERT BLOCKING_OTHERS----------
-- ------------
------------------ ------------------------2
Media Recovery Share
None212 0
113054 Not Blocking4
Redo Thread Exclusive
None1 0
0 Not
Blocking310 Transaction
Exclusive None196612 399886
123 Not
Blocking310 DML
Row-X (SX) None438580 0
123
Not Blocking
21
Which Locks Are Being Held?

• The previous views can be neatly arranged with
  the ORACLE_HOME/rdbms/admin/utllockt.sql script.
• The utllockt.sql script creates two temporary
  tables LOCK_HOLDERS and DBA_LOCKS_TEMP
• DBA_LOCKS_TEMP is created to improve performance
  over using the slower DBA_LOCKS view.
• Output uses the CONNECT BY and START WITH clauses
  to make a hierarchical tree structure.
• The highest level in the tree is where
  HOLDING_SESSION is null.

22
Which Locks Are Being Held?
SELECT lpad( ,3level-1)) waiting_sessions
WAITING SESSION,
lock_type, mode_requested, mode_held,
lock_id1, lock_id2FROM lock_holdersCONNECT
BY PRIOR waiting_sessionholding_ses
sion START WITH holding_session IS
NULLWAITING_ LOCK_ MODE_
MODE_ LOCK_ LOCK_SESSION TYPE
REQUESTED HELD ID1
ID2---------- ------- -------------
------- -------- ------133
None 319 Transaction Exclusive
Exclusive 196613 406415
23
Which Users Have Hogged Up The Most CPU Usage?
VSESSION
VSESSTAT
PROGRAMTYPESQL_ADDRESSSQL_HASH_VALUEMODULEMOD
ULE_HASHACTIONACTION_HASHCLIENT_INFOFIXED_TABL
E_SEQUENCEROW_WAIT_OBJROW_WAIT_FILEROW_WAIT_B
LOCKROW_WAIT_ROWLOGON_TIMELAST_CALL_ETPDML_E
NABLEDFAILOVER_TYPEFAILOVER_METHODFAILED_OVER
SADDRSIDSERIALAUDSIDPADDRUSERUSERNAMECOMM
ANDOWNERIDTADDRLOCKWAITSTATUSSERVERSCHEMAS
CHEMANAMEOSUSERPROCESSMACHINETERMINAL
SIDSTATISTICVALUE

• like CPU used by this session

VSTATNAME
NAMESTATISTICCLASS
24
Which Users Have Hogged Up The Most CPU Usage?
SELECT substr(sn.name,1,30) parameter,
ss.username ( se_sid )
user_process, se.valueFROM vsession ss,
vsesstat se, vstatname snWHERE se. statistic
sn.statistic AND sn.name LIKE CPU used
by this session AND
se.sidss.sidORDER BY sn.name, se_value
DESCPARAMETER USER_PROCESS
VALUE--------------------------------
------------------------ ---------------CPU
used by this session OLAP_MAN (390)
15230CPU used by this session PIN (35)
4324CPU used by this session
OPERATOR (17) 157CPU used by this
session SYS (11) 0
25
What Is The Archiving Status Of The Database?

• Why is archiving important?

• What happens when the archive volume fills up?

• How do you find the archiving status?

• How do you turn on archiving?

SVRMGRLgt ARCHIVE LOG LISTDatabase log mode No
Archive ModeAutomatic archival DisabledArchive
Destination ?/dbs/archOldest online log
sequence 4525Current log sequence 4528
26
What Is The Archiving Status Of The Database?
SELECT NAME a.name, LOG_MODE
a.log_mode, LOG_ARCHIVE_START
b.value, LOG_ARCHIVE_DEST
c.value, LOG_ARCHIVE_FORMAT
d.valueFROM vdatabase a, vparameter b,
vparameter c, vparameter
dWHERE b.namelog_archive_start AND
c.namelog_archive_dest AND
d.namelog_archive_formatNAMEPINDBLOG_MO
DENOARCHIVELOGLOG_ARCHIVE_STARTTRUELOG_ARCHIVE
_DEST/u01/oracle/product/8.0.3/dbs/archLOG_ARCHI
VE_FOMAT_s_t.log
27
Where to Now?

• There are many discussion Newsgroups on the
  internet for you to give questions and get
  answers
• comp.databases.oracle.server
• comp.databases.oracle.tools
• comp.databases.oracle.misc
• These can be accessed through a newsgroup program
  or www.deja.com
• Aris free Oracle Tips web page at
• There are over 370 tips and answers to questions
  that have been posed to me over the years. This
  paper will be downloadable from the web page as
  well.
• Other good sites with links www.orafaq.org,
  www.orafans.com, www.ioug.org, www.orasearch.com,
  www.revealnet.com

www.arikaplan.com