Understanding Locking in Oracle Utah Oracle Users Group - PowerPoint PPT Presentation

Loading...

PPT – Understanding Locking in Oracle Utah Oracle Users Group PowerPoint presentation | free to download - id: 3adbea-ZGRjM



Loading


The Adobe Flash plugin is needed to view this content

Get the plugin now

View by Category
About This Presentation
Title:

Understanding Locking in Oracle Utah Oracle Users Group

Description:

Understanding Locking in Oracle Utah Oracle Users Group DBA SIG 26 April, 2002 Tim Gorman TruTek Technologies, Inc. Agenda Different types of locking in ... – PowerPoint PPT presentation

Number of Views:323
Avg rating:3.0/5.0
Slides: 65
Provided by: evdbtCom
Learn more at: http://www.evdbt.com
Category:

less

Write a Comment
User Comments (0)
Transcript and Presenter's Notes

Title: Understanding Locking in Oracle Utah Oracle Users Group


1
Understanding Locking in Oracle
Utah Oracle Users Group DBA SIG26 April,
2002Tim Gorman TruTek Technologies, Inc.
2
Agenda
  • Different types of locking in Oracle
  • Latches
  • Enqueues
  • Diagnosing hangs in Oracle
  • Useful utilities in UNIX
  • Dumps, events, traces
  • YAPP report on www.oraperf.com

3
Latches
  • Latches
  • Low-level synchronization mechanism
  • Protects SGA memory structures used by many
    processes
  • Operates at the level of a server process,
    beneath the level of an Oracle session
  • Very lightweight, very fast
  • Allows only exclusive access
  • No queueing latches are obtained by polling

4
Latches
  • A latch is just shared memory containing an
    integer value
  • If already held, contains a process ID
  • If free, contains a NULL representation
  • Accessed using an OS-specific test-and-set
    operation
  • Atomic machine operation to
  • Test for a specific value at a location
  • Reset the location to specified value if test
    succeeds
  • Leave it alone if test fails

5
Latches
  • Latches are sometimes grouped into families
  • For protecting complex related or hierarchical
    data structures
  • Some latches are parents and must be obtained
    before one of the many children latches can be
    obtained
  • Allows concurrency without risk of deadlocking
  • Single individual latches are regarded as parents
    without children
  • Completely hard-coded rules governing which
    latches are parents, which are children, and how
    they are accesssed
  • Views VLATCH_PARENT and VLATCH_CHILDREN

6
Latches
  • Two types of latches
  • Difference between each type is hard-coded into
    the RDBMS
  • Immediate (or no-wait)
  • Failure to obtain the latch returns to the code
    as an error
  • Program logic handles it and does something else
  • Willing-to-wait
  • Failure to obtain the latch results in the
    process waiting
  • Only one immediate get attempt
  • SPIN_COUNT spin get attempts
  • Each attempt spins _LATCH_SPIN_COUNT times
  • Infinite sleep get attempts

7
Latches
  • Tracking latch usage
  • Views VLATCH (cumulative), VLATCH_PARENT, and
    VLATCH_CHILDREN (detailed)
  • Columns IMMEDIATE_GETS and IMMEDIATE_MISSES
  • counts for no-wait (or immediate) latch attempts
  • Columns GETS and MISSES
  • Counts for first immediate stage of
    willing-to-wait latch attempts
  • Column SPIN_GETS
  • Counts for second non-pre-emptive wait of
    willing-to-wait latch attempts
  • Column SLEEPS
  • Counts for third pre-emptive wait stage of
    willing-to-wait latch attempts
  • Columns SLEEP1, SLEEP2, SLEEP3, SLEEP4, ,
    SLEEP11 provide detailed summaries based on
    number of waits
  • Since Oracle8 v8.0, only SLEEP1-3 are used

8
Latches
  • Tracking latch usage (contd)
  • SQLPlus script latch.sql
  • Available at http//www.EvDBT.com/library.htm
  • Sorts display by SLEEPS
  • Depicting the most heavily-contended latches
  • Tracking latch usage is one thing
  • But how do we determine if it is a problem?

9
Latches
  • Wait-event interface for detecting latch
    contention
  • Timing of some operations can be captured
  • For latch operations, timing only captured on
    sleep gets during willing-to-wait latch
    operations
  • Latch waits that last long enough to require
    sleep gets can be disastrous
  • Process context-switching and multi-second sleep
    times dramatically inflate wait time for a latch
  • Each sleep get can be tracked by the Oracle
    wait-event interface
  • Posted as a latch free wait-event

10
Latches
  • Session-wait interface
  • By default, only counts of wait-events are
    captured
  • If parameter TIMED_STATISTICS set to TRUE, then
    RDBMS will also capture times
  • If TIMED_STATISTICS, then get system timestamp
  • Post wake-up signal
  • Go to sleep, relinquish CPU (i.e. pre-emptive
    wait)
  • If TIMED_STATISTICS, then get system timestamp
  • Increment counter of wait-event in wait-event
    views
  • If TIMED_STATISTICS, then post/increment time
    delta to wait-event views

11
Latches
  • Tracking the latch free wait-event
  • In the real-time view VSESSION_WAIT
  • A session whose process is waiting upon a sleep
    get for a latch posts a latch free wait-event
  • P1 raw ADDR value (see VLATCH.ADDR)
  • P2 latch (see VLATCHNAME.LATCH)
  • P3 number of sleep get attempts (a.k.a. tries)

12
Latches
  • Tracking the latch free wait-event (contd)
  • Also, if the SQL Trace event 10046 has been set
    to level 8, same information going to
    VSESSION_WAIT will go to a trace file in the
    USER_DUMP_DEST directory
  • VSESSION_WAIT shows current real-time
    information
  • SQL Trace level 8 shows real-time information
    chronologically over time, logged to a trace file

13
Latches
  • Session-wait views
  • View VSESSION_WAIT
  • Set SECONDS_IN_WAIT during wait, when STATUS
    WAITING
  • Process awakens every 0.5, 1.0, 2,0, 4.0 seconds
  • Max value set with _MAX_SLEEP_HOLDING_LATCH
  • Update WAIT_TIME after wait, when STATUS
    WAITED KNOWN TIME
  • Also update VSESSION_EVENT view column WAIT_TIME
  • Fixed table XKSLES
  • Also update VSYSTEM_EVENT view column WAIT_TIME
  • Fixed table XKSLEI

14
Latches
  • Session-wait views (contd)
  • View VSESSION_EVENT
  • Cumulative since start of session for all active
    sessions
  • Great diagnostic point for finding problems in
    currently active session
  • View VSYSTEM_EVENT
  • Cumulative since instance start for all sessions
  • Great diagnostic point for detecting overall
    trends for an entire instance

15
Latches
  • Determining if latch contention is a problem
  • select event as name, time_waited
  • from vsession_event
  • where sid mysid
  • union
  • select n.name, s.value as time_waited
  • from vsesstat s, vstatname n
  • where s.statistic n.statistic
  • and n.name CPU used by this session
  • where s.sid MySid
  • order by 2 desc

16
Latches
  • SQLPlus script sesstime.sql
  • Available at http//www.EvDBT.com/library.htm
  • For diagnosing relative significance of
    wait-events for active sessions
  • SQLPlus script systime.sql
  • Downloadable from same location
  • For diagnosing relative significance of
    wait-events cumulatively since the instance was
    started

17
Latches
  • SQLgt _at_sesstime
  • Enter value for usr staging
  • Total Non
  • USERNAME SID NAME
    SECS Total Idle
  • --------- ----------- -----------------------
    ------- ------- ---------
  • STAGING 38-TOAD.exe SQLNet message from cl
    41.34 75.014 5.562
  • db file sequential read
    12.00 21.775 21.934
  • CPU used by this sessio
    0.85 1.542 1.554
  • latch free
    0.32 0.581 0.585
  • SQLNet break/reset to
    0.20 0.363 0.000
  • virtual circuit status
    0.20 0.363 0.000
  • enqueue
    0.18 0.327 0.329
  • log file sync
    0.02 0.036 0.037

18
Latches
  • Significant latches
  • cache buffers lru
  • Since Oracle7.3 there are multiple LRU latches
    for the Buffer Cache
  • Controlled by db_block_lru_latches parameter
  • A high of SLEEPS could indicate a number of
    things
  • db_block_lru_latches set too low. Set this
    parameter to a value of 4.
  • Need to scan too many buffers on the LRU to find
    a free buffer The buffers are either dirty or in
    use (pinned)
  • cache buffers chains
  • Normally indicates data contention. Multiple
    sessions are going after a hot block (or a number
    of hot blocks)
  • library cache
  • Could indicate a potential parsing problem. Check
    if application not using bind-variables

19
Latches
  • Significant Latches (contd)
  • shared pool
  • Controls memory allocation and deallocation from
    the Shared Pool. A relatively high number of
    SLEEPS could indicate
  • Shared pool too small
  • Too many hard parses of SQL statements, possibly
    due to unshareable SQL statements resulting from
    not using bind-variables
  • row cache objects
  • Protects access to the data dictionary cache in
    the SGA
  • enqueue
  • Protects the adding and removing of state objects
    from global pool of enqueue resources

20
Latches
  • Latches represent the lowest-level concurrency
    mechanisms in Oracle
  • But before attempting to tune them, be sure that
    there is a problem first!
  • Remember
  • Increasing the number of latches rarely helps
  • Correct the underlying problem

21
Enqueues
  • Latches are low-level mechanisms to protect deep
    internal workings of RDBMS
  • Enqueues are used by sessions (not processes) to
    protect resources for a long time
  • Allows shared non-exclusive access
  • Ensures FIFO access to resources via queuing
  • Waits without spinning for long periods of time
  • Very sophisticated synchronization mechanism
  • Usually dependent on OS concurrency resources
    such as UNIX semaphores

22
Enqueues
  • Lock modes for an enqueue
  • Null (NL) no session holds the resource
  • Shared (S) one or more sessions are
    reading
  • Exclusive (X) one session is writing
  • Sub-shared (SS) read access to a compound
    resource
  • Sub-exclusive (SX) write access to a compound
    resource
  • Shared-sub-exclusive combination read access to
    one part (SSX) and write access to another
    part of a compound resource

23
Enqueues
  • Enqueue locks guard many structures
  • Transaction resources (TX and TM)
  • Buffer cache buffer (BL)
  • Segment and extent allocation (ST)
  • Controlfile access (CF)
  • Direct-path index creation (DL)
  • Distributed transactions (DL)
  • DBMS_JOB job allocation (JQ)
  • User-defined via DBMS_LOCK package (UL)

24
Enqueues
  • Real-time session-level views
  • VLOCK
  • Displays current enqueues held and requested by
    sessions
  • Excellent for seeing all enqueue information for
    a session
  • VLOCKED_OBJECT
  • Useful subset of VLOCK where type TM
  • Ties into VSESSION via XID columns, too

25
Enqueues
  • Excellent fully-decoded scripts for querying
    VLOCK available on MetaLink
  • TFS script tfsclock.sql, doc ID 1020008.6
  • Script lock.sql is very similar to
    tfsclock.sql
  • From http//www.EvDBT.com/library.htm
  • Stored procedure ENQWAIT in enqwait.sql and
    run_enqwait.sql
  • exhaustive analysis of blockers/waiters
  • temp_enqwait.sql uses PL/SQL anonymous block

26
Enqueues
  • Waiter SID281 (ACTIVE), Logged on at 03-MAR
    1045
  • ....... REQUESTED LOCKMODETX (Transaction)
    Exclusive (655425,51351)
  • ....... AppsUserSTILLAU
  • ....... OS PID10870
  • .... TXN ID51.7.50922 (ACTIVE) started03/03/02
    104634 undo1b/1r
  • ....... DML Lock PO.RCV_SHIPMENT_HEADERS (TABLE)
    - LOCK HELDSub-Exclusive
  • ....... DML Lock PO.RCV_TRANSACTIONS_INTERFACE
    (TABLE) - LOCK HELDSub-Exclus
  • .... SQL Statement currently executing
  • ....... DELETE FROM RCV_TRANSACTIONS_INTERFACE
    WHERE TO_NUMBER(b1) GR
  • ....... OUP_ID
  • gtBLOCKER SID151,2 (ACTIVE), Logged on at
    01-MAR 0336
  • ........... HELD LOCKMODETX (Transaction)
    Exclusive
  • ........... AppsUserMIKETAC
  • ........... OS PID12711
  • ....... TXN ID10.65.51351 (ACTIVE)
    started03/01/02 104634 undo2b/30r
  • ........... DML Lock PO.RCV_TRANSACTIONS (TABLE)
    - LOCK HELDSub-Exclusive
  • ........... DML Lock PO.PO_NOTE_REFERENCES
    (TABLE) - LOCK HELDSub-Exclusive
  • ....... SQL currently executing (not necessarily
    the blocking SQL)
  • ........... insert into mtl_material_transactions_
    temp(transaction_temp_id,t

27
UNIX diagnostics
  • If you cannot get into Oracle to do
    diagnostics, some UNIX utilities may be helpful
  • top, ps
  • trace utilities
  • truss (Solaris, AIX, Dynix)
  • tusc (HP-UX)
  • strace (Linux)

28
UNIX diagnostics
  • UNIX utility top
  • Continual reports about state of the system
  • Including list of top CPU-using processes
  • Goals
  • Provide an accurate snapshot of system- and
    process-state
  • Portable
  • Should not be a top CPU-using process itself!

29
UNIX diagnostics
  • System proddw                         Sat
    Mar 23 120324 2002
  • Load averages 2.64, 2.63, 2.47
  • 284 processes 248 sleeping, 36 running
  • CPU   LOAD   USER   NICE    SYS   IDLE  BLOCK 
    SWAIT   INTR   SSYS
  •  0    2.40  84.2   0.0   6.9   8.9   0.0  
    0.0   0.0   0.0
  •  1    2.63  75.2   0.0   6.9  17.8   0.0  
    0.0   0.0   0.0
  •  2    3.03  81.2   0.0   5.0  13.9   0.0  
    0.0   0.0   0.0
  •  3    2.51  78.2   0.0   5.9  15.8   0.0  
    0.0   0.0   0.0
  • ---   ----  -----  -----  -----  -----  ----- 
    -----  -----  -----
  • avg   2.64  80.2   0.0   5.9  13.9   0.0  
    0.0   0.0   0.0
  • Mem 1459956K (1286788K) real, 724328K (175844K)
    virtual, 56900K free
  • CPU TTY    PID USERNAME PRI NI  SIZE   RES STATE
    TIME WCPU  CPU COMMAND
  • 0 pts/tb 22509 tgorman  240 20 1788K  880K run  
    339 16.55 16.52 bcp
  • 2 pts/tb 22515 tgorman  152 20 6788K 5652K run  
    257 14.01 13.99 sqll
  • 2 pts/th 22890 tgorman  236 20  716K  316K run  
    052 11.44 11.42 dump
  • 3   ?    22712 oracle   154 20 9344K 1904K sleep
    138 11.34 11.32 orac

30
UNIX diagnostics
  • UNIX utility ps
  • Standard SysV version
  • ps -eaf
  • XPG3/4 (X/Open Portability Guide v3/4)
  • ps eo opt,opt
  • Provides info about processes
  • Status, PID, user, command text,
  • Cumulative and recent CPU
  • Memory (virtual, resident)

31
UNIX diagnostics
  • An easy home-grown top command
  • ps eaf sort n 3 tail
  • oracle 15848     1 228 095128 ?      1934
    ora_lgwr_dssdwp0
  • tgorman 21167 21164 232 111559 pts/td  259 bcp
    dss.dbo.modi
  • Oracle 20371     1 235 105705 ?       705
    oracledssdwp01 (
  • tgorman 21395 21392 235 112452 pts/tf  019
    /home/tgorman/dd
  • tgorman 20176 20167 239 105152 pts/ta  716
    sqlldr parfilet
  • tgorman 21416 21407 240 112458 pts/tg  024
    sqlldr parfilet
  • tgorman 21471 21468 240 112507 pts/th  018
    /home/tgorman/dd
  • tgorman 21410 21407 252 112458 pts/tg  027
    /home/tgorman/dd

32
UNIX diagnostics
  • Another home-grown top command
  • More accurate than ps eaf
  • Also displays memory consumption in Kbytes
  • ps -eo user,pid,pcpu,vsz,rss,comm sort -n 2
    tail
  • root 28103 0.1 3240 2560
    /usr/local/sbin/sshd
  • oracle 20334 0.1 495056 447056 oracleACTION
  • oracle 20881 0.1 711552 634144 oracleACTION
  • oracle 18626 3.3 463240 428032 ora_lgwr_ACTION
  • oracle 18624 12.2 465112 429480 ora_dbw0_ACTION
  • root 3 14.3 0 0 fsflush
  • oracle 18626 15.3 463240 428032 ora_lgwr_ACTION
  • oracle 28077 30.8 486824 450200 oracleACTION

33
UNIX diagnostics
  • Use top and ps to identify processes in UNIX
  • By current CPU activity
  • By time started
  • By total CPU time consumed
  • By process name
  • By UNIX account

34
UNIX diagnostics
  • Trace utilities
  • truss (Solaris, AIX, Dynix)
  • tusc (HP-UX)
  • strace (Linux)
  • Attach to or run a process and then trace
  • UNIX system calls executed
  • Signals received
  • Machine faults incurred
  • (optional) entry/exit trace of user level
    function calls

35
UNIX diagnostics
  • Output from truss (of PMON process) on Solaris
    8
  • . . .
  • semop(196608, 0xFFBEE7F4, 1)
    (sleeping...)
  • Received signal 14, SIGALRM, in semop()
    caught
  • semop(196608, 0xFFBEE7F4, 1) Err91
    ERESTART
  • sigprocmask(SIG_BLOCK, 0xFFBEE320, 0x00000000)
    0
  • sigprocmask(SIG_UNBLOCK, 0xFFBEE320, 0x00000000)
    0
  • getcontext(0xFFBEE0E0)
  • setcontext(0xFFBEE0E0)
  • sigprocmask(SIG_BLOCK, 0xFFBEE5FC, 0x00000000)
    0
  • setitimer(ITIMER_REAL, 0xFFBEE584, 0x00000000)
    0
  • sigprocmask(SIG_UNBLOCK, 0xFFBEE5FC, 0x00000000)
    0
  • getcontext(0xFFBEE4E8)
  • sigprocmask(SIG_BLOCK, 0xFFBEE5FC, 0x00000000)
    0
  • . . . .

36
UNIX diagnostics
  • Output from strace (of server process using
    SQL Trace) on SuSe Linux 7.2
  • . . .
  • 152602.348564 gettimeofday(1003530362,
    348588, NULL) 0
  • 152602.348659 pread(409,
  • "\6\2\0\0\240\27\200\0\325\227\7\0\0\0\2\0\0\0\0\0
    \1\0\7\0_\f\0\0\210I\7\0\0\0\24P\2\6\3\0\30/\200\0
    \0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0
    \0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\1
    \236\0\0\0N\1\354\3R\6R\6\0\0\236\0\1\0\2\0\3\0\4\
    0\5\0"...,
  • 8192, 12386304) 8192
  • 152602.348995 gettimeofday(1003530362,
    349035, NULL) 0
  • 152602.349174 gettimeofday(1003530362,
    349209, NULL) 0
  • 152602.349260 write(6, "WAIT 1 nam\'db file
    sequential read\' ela 0 p12 p26048 p31", 63)
  • . . .

37
UNIX diagnostics
  • Output from tusc (of SQLPlus process) on HP-UX
    11.11
  • . . .
  • (Attached to process 8825 ("sqlplus scott")
    64-bit )
  • read(0, 0x4005a5f8, 8192).........................
    . sleeping
  • read(0, "\n", 8192)...............................
    ........ 1
  • write(1, "S Q L gt   ", 5).........................
    ........ 5
  • read(0, 0x4005a5f8, 8192).........................
    . sleeping
  • read(0, "s e l e c t   d u m m y   f r o "..,
    8192)...... 24
  • lseek(9, 89600, SEEK_SET).........................
    .... 89600
  • read(9, "\01113 \0\0\0n 13, \0\0\0a21388"..,
    512)...... 512
  • lseek(9, 12800, SEEK_SET).........................
    .... 12800
  • read(9, "\01c0390\0\0\0b00391\0\0\0c90392"..,
    512)...... 512
  • lseek(8, 0, SEEK_CUR).............................
    ... 119218
  • lseek(8, 0, SEEK_CUR).............................
    ... 119218
  • write(8, "n i o q s n    e n t r y \n",
    14)............. 14
  • . . .

38
UNIX diagnostics
  • Less-useful UNIX utilities for diagnosing Oracle
    problems
  • vmstat and sar
  • Displays cumulative VM statistics only
  • Displays redundant CPU statistics (ps and
    top)
  • Displays useless I/O statistics
  • iostat and other standard UNIX-based I/O
    utilities
  • Practically useless now due to widespread use of
    logical volume managers (LVMs)

39
Events, traces, and dumps
  • Events are an undocumented back-door into the
    RDBMS
  • Immediate dumps into trace files
  • Initiated using ALTER SESSION command
  • Tracing traps upon occurrence of specified
    errors
  • Initiated using init.ora parameters only
  • Tracing of certain operations
  • Initiated using ALTER SESSION or init.ora
    parameters
  • Change RDBMS behavior
  • Initiated using init.ora parameters only

40
Events, traces, and dumps
  • File dumps
  • Dump contents of the control file(s)
  • SQLgt ALTER SESSION SET EVENTS 'immediate trace
    name controlf level 10'
  • Dump contents of the datafile headers
  • SQLgt ALTER SESSION SET EVENTS 'immediate trace
    name file_hdrs level 10'
  • Dump contents of the online redo log headers
  • SQLgt ALTER SESSION SET EVENTS 'immediate trace
    name redohdr level 10'

41
Events, traces, and dumps
  • File dumps (contd)
  • ALTER SYSTEM DUMP command
  • DATAFILE TEMPFILE nn filename BLOCK
    xxxxx
  • Dumps a single database block xxxxx
  • DATAFILE TEMPFILE nn filename BLOCK
    MIN yyyyy BLOCK MAX zzzzz
  • Dumps the range of blocks between yyyyy and zzzzz
  • Completely replaces event BLOCKDUMP and the need
    for event SET_TSN_P1 in Oracle8,8i, and 9i

42
Events, traces, and dumps
  • For example
  • SQLgt ALTER SYSTEM DUMP DATAFILE 1 BLOCK 527
  • System altered.
  • SQLgt ALTER SYSTEM DUMP TEMPFILE 1 BLOCK MIN 20
    BLOCK MAX 30
  • System altered.

43
Events, traces, and dumps
  • File dumps (contd)
  • ALTER SYSTEM DUMP command (contd)
  • UNDO HEADER rbs-name
  • Dumps header block of rollback segment specified
  • UNDO BLOCK rbs-name XID xidusn xidslot xidsqn
  • Dumps rollback segment blocks related to the
    specified transaction
  • LOG redo-log-filename
  • Dumps the entire contents of the online or
    archived redo log file

44
Events, traces, and dumps
  • For example
  • SQLgt SELECT XIDUSN, XIDSLOT, XIDSQN FROM
    VTRANSACTION WHERE ADDR IN (SELECT TADDR FROM
    VSESSION WHERE SID nnn)
  • SQLgt SELECT NAME FROM VROLLNAME WHERE USN
    xidusn
  • SQLgt ALTER SYSTEM DUMP UNDO HEADER _SYSSMU01
  • SQLgt ALTER SYSTEM DUMP UNDO BLOCK _SYSSMU01
    XID 1 45 20908

45
Events, traces, and dumps
  • File dumps are useful in situations where some
    form of corruption is suspected
  • Each dump will indicate if corruption is present
  • If it cannot interpret the data
  • Usually used to provide information to Oracle
    Support
  • Extremely cryptic output
  • But educational if you are willing to examine them

46
Events, traces, and dumps
  • Process dumps
  • Dump memory structures for process
  • SQLgt ALTER SESSION SET MAX_DUMP_FILE_SIZE
    UNLIMITED
  • SQLgt ALTER SESSION SET EVENTS 'immediate trace
    name processstate level 10'
  • Or for another process from SQLPlus
  • SQLgt ORADEBUG SETOSPID OS-spid
  • SQLgt ORADEBUG UNLIMIT
  • SQLgt ORADEBUG DUMP PROCESSSTATE 10

47
Events, traces, and dumps
  • Process dumps (contd)
  • Dump program stack trace for process
  • SQLgt ALTER SESSION SET MAX_DUMP_FILE_SIZE
    UNLIMITED
  • SQLgt ALTER SESSION SET EVENTS 'immediate trace
    name errorstack level 3'
  • Or for another process from SQLPlus
  • SQLgt ORADEBUG SETOSPID OS-spid
  • SQLgt ORADEBUG UNLIMIT
  • SQLgt ORADEBUG DUMP ERRORSTACK 3

48
Events, traces, and dumps
  • System SGA dumps
  • Dump memory structures for process
  • SQLgt ALTER SESSION SET MAX_DUMP_FILE_SIZE
    UNLIMITED
  • SQLgt ALTER SESSION SET EVENTS 'immediate trace
    name systemstate level 10'
  • Or for another process from SQLPlus
  • SQLgt ORADEBUG SETOSPID OS-spid
  • SQLgt ORADEBUG UNLIMIT
  • SQLgt ORADEBUG DUMP SYSTEMSTATE 10

49
Events, traces, and dumps
  • Process and system memory dumps are of most use
    to Oracle Support
  • Not supported! Be vewwy, vewwy careful!
  • Extremely cryptic output
  • Can only be interpreted by Oracle Support and
    Development

50
Events, traces, and dumps
  • Besides immediate dumps, traps can be set to
    cause dumps on receipt of a specified error
  • Using init.ora parameters at instance startup
  • Event 904 trace name ERRORSTACK level 3
  • Using ALTER SESSION
  • alter session set events '904 trace name
    ERRORSTACK level 3

51
Events, traces, and dumps
  • SQL Tracing
  • For a session by that session
  • ALTER SESSION SET SQL_TRACE TRUE
  • ALTER SESSION SET EVENTS 10046 trace name
    context forever, level n
  • Level 1 traces SQL operations
  • Level 4 outputs bind variable values
  • Level 8 outputs wait-event info
  • Level 12 all of the above
  • dbms_session.set_sql_trace(TRUE)

52
Events, traces, and dumps
  • SQL tracing (contd)
  • For a session by another session
  • dbms_system.set_sql_trace_in_session(sid,
    serial, TRUE)
  • ORADEBUG SETOSPID OS-spid
  • ORADEBUG UNLIMIT
  • ORADEBUG EVENT 10046 trace name context forever,
    level 12
  • CBO decision-tree tracing
  • ALTER SESSION SET EVENTS 10053 trace name
    context forever, level n

53
Events, traces, and dumps
  • Reading raw SQL trace files
  • PARSING IN CURSOR cc len0 dep0 uid0 oct0
    lid0 tim0 hv0 adx
  • SQL-Statement-text
  • END OF STMT
  • cc cursor number (used to identify all
    operations for a SQL statement)
  • len length of the text of the SQL statement in
    bytes
  • dep recursive execution depth (0 user-level,
    1recursive level 1, etc)
  • uid user parsing statement, from
    DBA_USERS.USERID value
  • oct Oracle command type
  • Lid logical user executing statement, from
    DBA_USERS.USERID
  • Tim timestamp measured in 1/100ths of second
  • Hv decimal hash value, from VSQLAREA.HASH_VALUE
  • Ad hexadecimal address, from VSQLAREA.ADDRESS

54
Events, traces, and dumps
  • Reading raw SQL trace files (contd)
  • PARSE cc c0,e0,p0,cr0,cu0,mis0,r0,dep0,o
    g4,tim0
  • EXEC cc c0,e0,p0,cr0,cu0,mis0,r0,dep0,og
    4,tim0
  • FETCH cc c0,e0,p0,cr0,cu0,mis0,r0,dep0,o
    g4,tim0
  • cc cursor number (same as PARSING IN CURSOR
    statement)
  • c CPU time (measured in 1/100ths of seconds)
  • e elapsed time (measured in 1/100ths of
    seconds)
  • p number of physical reads or disk I/Os
  • cr number of consistent gets on buffer cache
    in the SGA
  • cu number of db block gets (or current
    gets) on buffer cache in the SGA
  • mis number library cache misses
  • r number of rows affected or retrieved
  • dep recursive execution depth (0 user-level,
    1recursive level 1, etc)
  • og optimizer goal 1ALL_ROWS, 2FIRST_ROWS,
    3RULE, 4CHOOSE
  • tim timestamp measured in 1/100ths of seconds

55
Events, traces, and dumps
  • Reading raw SQL trace files (contd)
  • ERROR cc err0 tim0
  • cc cursor number (same as PARSING IN CURSOR
    statement)
  • err Oracle error message number
  • tim timestamp measured in 1/100ths of seconds
  • XCTEND rlbk0 rd_only0
  • Transaction end marker (COMMIT or ROLLBACK)
  • COMMIT is rlbk0, ROLLBACK is rlbk1
  • READ ONLY transaction is rd_only1, otherwise
    rd_only0
  • STAT cc id0 cnt0 pid0 pos0 obj0 optext
    '
  • EXPLAIN PLAN information from cursor cc

56
Events, traces, and dumps
  • Events are documented (very briefly) on UNIX
    platforms
  • File oraus.msg in directory ORACLE_HOME/rdbms/
    mesg
  • Used by oerr utility
  • Events reside in numeric ranges from 10000 until
    10999 only

57
YAPP Reports
  • Yet Another Performance Profiler
  • Available from http//www.oraperf.com
  • Unofficial website run by members of Oracle
    Server Technologies division
  • Upload either BSTAT/ESTAT report.txt file or
    STATSPACK report file
  • Returns an HTML page containing an amazing
    response-time analysis report

58
YAPP Reports
  • Organized from overview to hyperlinked detail
    sections
  • Header (version info, time span of report, etc)
  • Response-time breakout
  • CPU Time or time spent processing SQL
  • Parse, recursive, and other CPU Time breakouts
  • Wait Time or time not spent processing SQL
  • Initialization parameter settings
  • Tuning advise summary

59
YAPP Reports
  • General Information
  • The following comments were generated while
    processing file C\Temp\sp_5349_5350.lst
  • Disclaimer Use information at own risk !
  • All timing information is in 1/100 sec, unless
    stated otherwise.
  • The timing period in this report is too long to
    get any useful tuning advise.
  • End Buffer Gets Threshold 100000
  • Note that resources reported for PL/SQL includes
    the resources used by all SQL statements called
    within the PL/SQL code. As individual SQL
    statements are also reported, it is possible and
    valid for the summed total 10021160010 exceed 100
  • End Executions Threshold 1000
  • only latches with sleeps are shown
  • ordered by name, sleeps desc NoWait Waiter
  • Please be advised that running STATSNAP on
    releases before Oracle8i can give problems.
  • Please be advised that Oracle8 version 8.0.6 is
    the terminal release for Oracle8. You are on an
    older release.
  • Uploaded 167060 bytes in 5.10 seconds

60
YAPP Reports

Response Time
61
YAPP Reports

CPU Time

62
YAPP Reports

Wait Time
63
YAPP Reports
64
QA
About PowerShow.com