life without tools monitoring database activity with the power of sql n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Life Without Tools: Monitoring Database Activity With The Power Of SQL PowerPoint Presentation
Download Presentation
Life Without Tools: Monitoring Database Activity With The Power Of SQL

Loading in 2 Seconds...

play fullscreen
1 / 27

Life Without Tools: Monitoring Database Activity With The Power Of SQL - PowerPoint PPT Presentation


  • 194 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Life Without Tools: Monitoring Database Activity With The Power Of SQL' - francis


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
life without tools monitoring database activity with the power of sql
Life Without Tools: Monitoring Database Activity With The Power Of SQL

Ari Kaplan

Independent Consultant

who is logged on
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;

who is logged on1
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

what sql statement is a particular user account executing
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);

what sql statement is a particular user account executing1
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’;

setting up an interactive script for viewing users and sql
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

what is my datafile activity like
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

which rollback segments are being used by which user accounts
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.
which rollback segments are being used by which user accounts1
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.
which rollback segments are being used by which user accounts2
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

slide13

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

slide14

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 ?

what has the largest number of concurrent users been
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’;

what has the largest number of concurrent users been1
What Has The Largest Number Of Concurrent Users Been?

Output from the preceding SQL:

PINDB: current logons=298 cumulative logons=7967 highwater mark=391

what has the largest number of concurrent users been2
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

what has the largest number of concurrent users been3
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

which locks are being held
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.

which locks are being held1
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;

which locks are being held2
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.
which locks are being held3
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

which users have hogged up the most cpu usage
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

which users have hogged up the most cpu usage1
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

what is the archiving status of the database
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

what is the archiving status of the database1
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

where to now
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