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

3. Quick Overview of Typical Monitoring Views

4. Who Is Logged On? The V$SESSION view contains information on all active sessions: SELECT sid, schemaname, osuser substr(machine,1,20) MachineFROM v$sessionORDER BY schemaname;

5. Who Is Logged On? SELECT sid, schemaname, osuser, substr(machine,1,20) MachineFROM v$sessionORDER BY schemaname; A sample output from the above SQL follows: SID SCHEMANAME OSUSERMACHINE 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 v$sqlareaWHERE (address, hash_value) IN (SELECT sql_address, sql_hash_valueFROM v$sessionWHERE sid = &sid_number);

7. What SQL Statement Is A Particular User Account Executing? SELECT sql_textFROM v$sqlareaWHERE (address, hash_value) IN (SELECT sql_address, sql_hash_valueFROM v$sessionWHERE 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 “SQL2.SQL” SQL Script “SQL1.SQL” SELECT sid, schemaname, osuser, substr(machine,1,20) Machine FROM v$session ORDER BY schemaname; SELECT sql_textFROM v$sqlareaWHERE (address, hash_value) IN (SELECT sql_address, sql_hash_valueFROM v$sessionWHERE sid= sid_number; Who is logged in to the database? What SQL is used? UNIX Script “SQL_RUN” echo “Enter the database to connect to:”read DATABASEecho “Enter the password for the SYSTEM user:”stty -echoecho “Password:\c”read PASSWDstty echoecho “”sqlplus system/$PASSWD\@DATABASE << EOF@sql1.sqlEOF echo “Enter the SID for which SQL you wish to see:”read SID_NUMsqlplus system/$PASSWD@DATABASE << EOFselect sql_text from v$sqlareawhere (address, hash_value) in(select sql_addresss, sql_hash_valuefrom v$sessionWHERE 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/Write”FROM dba_data_files, v$filestatWHERE dba_data_files.file_id=v$filestat.file#;FILE_NAME PHYRDSPHYWRTSBLOCKS/READBLOCKS/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 V$ROLLSTAT and V$ROLLNAME to get general rollback segment activity • Link V$ROLLNAME with V$LOCK and V$SESSION to determine which users are using which rollback segments. • More information on the users can be collected by further joining with the V$PROCESS 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 V$ROLLNAME links with V$LOCK’s ID1 column. The ID1 column contains the object id of the object (rollback segment) being locked. • The V$LOCK view contains a SID column which links to the SID column of V$SESSION. • The V$SESSION view links to the V$PROCESS view via the PADDR column.

12. Which Rollback Segments Are Being Used By Which User Accounts? NOTE: Only relevant columns are listed with tables V$LOCK V$ROLLNAME ID1SIDTYPELMODE NAMEUSN V$PROCESS ADDRPIDSPIDUSERNAMETERMINAL V$SESSION SIDPADDR

13. Which Rollback Segments Are Being Used By Which User Accounts? echo “Enter the password for the SYSTEM user:”read PASSWDsqlplus system/$PASSWD << EOFspool temp.lstset head offset pagesize 0connect system\$PASSWD@DBNAMESELECT to_char(rownum+3)||’) ‘||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 SEG;ORACLE PID; SYSTEM PID; SID; TRANSACTION; TERMINAL’FROM dualUNIONSELECT ‘1) ‘ FROM dualUNIONSELECT ‘3) ---------------- ---------- ---------- ----------- --------’ FROM dualORDER BY 1;EOF

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 SELECTrpad(c.name||’:’,11)||chr(9)||chr(9)|| rpad(‘ current logons=‘||(to_number(b.sessions_current)-1),20)||chr(10)|| ‘cumulative logons=‘||rpad(substr(a.value,1,12),12)||chr(9)|| ‘highwater mark=‘||b.sessions_highwater||chr(9) FROMv$sysstat a, v$license b, v$database c WHEREa.name = ‘logons cumulative’;

16. What Has The Largest Number Of Concurrent Users Been? Output from the preceding SQL: PINDB: current logons=298 cumulative logons=7967 highwater mark=391

17. What Has The Largest Number Of Concurrent Users Been? Loop through four databases export LOGDIR=$ORACLE_HOME/udump echo “Enter the password for the SYSTEM user:” read PASSWD for SID in PRODDB TESTDB TRAINDB DEVDB do sqlplus system/$PASSWD@$SID << EOF >> $LOGDIR/temp_users.lst set linesize 100 SELECTrpad(c.name||’:’,11)||chr(9)||chr(9)|| rpad(‘ current logons=‘||(to_number(b.sessions_current)-1),20)||chr(10)|| ‘cumulative logons=‘||rpad(substr(a.value,1,12),12)||chr(9)|| ‘highwater mark=‘||b.sessions_highwater||chr(9) FROMv\$sysstat a, v\$license b, v\$database c WHEREa.name = ‘logons cumulative’; EOF done cat $LOGDIR/temp_users.lst |grep “LOG” > $LOGDIR/archive`date “+%m%d”`.log rm $LOGDIR/temp_users.lst

18. What Has The Largest Number Of Concurrent Users Been? Output from the preceding SQL for four databases: PRODDB: current logons=298 cumulative logons=7967 highwater mark=391TESTDB: current logons=15 cumulative logons=187 highwater mark=15TRAINDB: current logons=8 cumulative logons=613 highwater mark=25DEVDB: current logons=23 cumulative logons=1024 highwater mark=30

19. Which Locks Are Being Held? -- V$LOCK holds information on internal Oracle locks. -- Six views show information more clearly: DBA_LOCK: locks held and locks requested;is 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/admin/catblock.sql script -- Use $ORACLE_HOME/rdbms/admin/utllockt.sql to analyze these views.

20. Which Locks Are Being Held? 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 SELECT * FROM DBA_LOCK;

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(‘ ‘,3*level-1)) || waiting_sessions “WAITING SESSION”, lock_type, mode_requested, mode_held, lock_id1, lock_id2FROM lock_holdersCONNECT BY PRIORwaiting_session=holding_session START WITH holding_session IS NULL;WAITING_ 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? V$SESSION V$SESSTAT SADDRSIDSERIAL#AUDSIDPADDRUSER#USERNAMECOMMANDOWNERIDTADDRLOCKWAITSTATUSSERVERSCHEMA#SCHEMANAMEOSUSERPROCESSMACHINETERMINAL PROGRAMTYPESQL_ADDRESSSQL_HASH_VALUEMODULEMODULE_HASHACTIONACTION_HASHCLIENT_INFOFIXED_TABLE_SEQUENCEROW_WAIT_OBJ#ROW_WAIT_FILE#ROW_WAIT_BLOCK#ROW_WAIT_ROW#LOGON_TIMELAST_CALL_ETPDML_ENABLEDFAILOVER_TYPEFAILOVER_METHODFAILED_OVER like ‘%CPU used by this session%’ SIDSTATISTIC#VALUE V$STATNAME NAMESTATISTIC#CLASS

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 v$session ss, v$sesstat se, v$statname snWHERE se. statistic# = sn.statistic# AND sn.name LIKE ‘%CPU used by this session%’ AND se.sid=ss.sidORDER BY sn.name, se_value DESC;PARAMETER 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? SVRMGRL> ARCHIVE LOG LIST;Database 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 v$database a, v$parameter b, v$parameter c, v$parameter dWHERE b.name=‘log_archive_start’ AND c.name=‘log_archive_dest’ AND d.name=‘log_archive_format’;NAME=PINDBLOG_MODE=NOARCHIVELOGLOG_ARCHIVE_START=TRUELOG_ARCHIVE_DEST=/u01/oracle/product/8.0.3/dbs/archLOG_ARCHIVE_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” • Ari’s 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