1 / 27

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

Life Without Tools: Monitoring Database Activity With The Power Of SQL. Malesh Gummula Independent Consultant. Quick Overview of Typical Monitoring Views. Who Is Logged On?. The V$SESSION view contains information on all active sessions:.

jesusedwards
Download Presentation

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

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Life Without Tools: Monitoring Database Activity With The Power Of SQL Malesh Gummula Independent Consultant

  2. Quick Overview of Typical Monitoring Views

  3. 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;

  4. 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

  5. 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);

  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); 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’;

  7. 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

  8. 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

  9. 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.

  10. 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.

  11. 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

  12. 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

  13. 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 ?

  14. 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’;

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

  16. 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

  17. 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

  18. 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.

  19. 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;

  20. 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.

  21. 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

  22. 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

  23. 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

  24. 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

  25. 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

  26. Where to Now? • malu’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 http://geocities.com/malesh_oracle

More Related