| Previous | Table of Contents | Next |
Listing 5.8 can be used whenever you suspect that locks are impeding performance. This script interrogates all of the views that were created in catblock.sql:
Listing 5.8 alllocks.sql.
REM alllocks.sql - shows all locks in the database. REM written by Don Burleson SET linesize 132 SET pagesize 60 SPOOL /tmp/alllocks COLUMN owner FORMAT a10; COLUMN name FORMAT a15; COLUMN mode_held FORMAT a10; COLUMN mode_requested FORMAT a10; COLUMN type FORMAT a15; COLUMN lock_id1 FORMAT a10; COLUMN lock_id2 FORMAT a10; PROMPT Note that $ORACLE_HOME/rdbma/admin/catblock.sql PROMPT must be run before this script functions . . . PROMPT Querying dba_waiters . . . SELECT waiting_session, holding_session, lock_type, mode_held, mode_requested, lock_id1, lock_id2 FROM sys.dba_waiters; PROMPT Querying dba_blockers . . . SELECT holding_session FROM sys.dba_blockers; PROMPT Querying dba_dml_locks . . . SELECT session_id, owner, name, mode_held, mode_requested FROM sys.dba_dml_locks; PROMPT Querying dba_ddl_locks . . . SELECT session_id, owner, name, type, mode_held, mode_requested FROM sys.dba_ddl_locks; PROMPT Querying dba_locks . . . SELECT session_id, lock_type, mode_held, mode_requested, lock_id1, lock_id2 FROM sys.dba_locks;
Listing 5.9 shows the output.
Listing 5.9 The output of alllocks.sql.
SQL> @alllocks
Note that $ORACLE_HOME/rdbma/admin/catblock.sql
must be run before this script functions . . .
Querying dba_waiters . . .
no rows selected
Querying dba_blockers . . .
no rows selected
Querying dba_dml_locks . . .
SESSION_ID OWNER NAME MODE_HELD MODE_REQUE
---------- ---------- -------------- ---------- ----------
19 RPT RPT_EXCEPTIONS Row-X (SX) None
Querying dba_ddl_locks . . .
SESSION MODE_ MODE_
_ID OWNER NAME TYPE HELD REQUE
------- ----- -------------------- --------------- ------------
13 RPT SHP_PRE_INS_UPD_PROC Table/Procedure Null None
13 SYS STANDARD Body Null None
14 SYS STANDARD Body Null None
13 SYS DBMS_STANDARD Table/Procedure Null None
14 SYS DBMS_STANDARD Table/Procedure Null None
13 SYS DBMS_STANDARD Body Null None
14 SYS DBMS_STANDARD Body Null None
13 SYS STANDARD Table/Procedure Null None
14 SYS STANDARD Table/Procedure Null None
9 rows selected.
Querying dba_locks . . .
SESSION_ID LOCK_TYPE MODE_HELD MODE_REQUE LOCK_ID1 LOCK_ID2
---------- ---------------- --------- ---------- -------- --------
2 Media Recovery Share None 32 0
2 Media Recovery Share None 31 0
2 Media Recovery Share None 30 0
2 Media Recovery Share None 29 0
2 Media Recovery Share None 28 0
2 Media Recovery Share None 27 0
2 Media Recovery Share None 26 0
2 Media Recovery Share None 25 0
2 Media Recovery Share None 24 0
2 Media Recovery Share None 23 0
2 Media Recovery Share None 22 0
2 Media Recovery Share None 21 0
2 Media Recovery Share None 20 0
2 Media Recovery Share None 19 0
2 Media Recovery Share None 18 0
2 Media Recovery Share None 17 0
2 Media Recovery Share None 16 0
2 Media Recovery Share None 15 0
2 Media Recovery Share None 14 0
2 Media Recovery Share None 13 0
2 Media Recovery Share None 12 0
2 Media Recovery Share None 11 0
2 Media Recovery Share None 10 0
2 Media Recovery Share None 9 0
2 Media Recovery Share None 8 0
2 Media Recovery Share None 7 0
2 Media Recovery Share None 6 0
2 Media Recovery Share None 5 0
2 Media Recovery Share None 4 0
2 Media Recovery Share None 3 0
2 Media Recovery Share None 2 0
2 Media Recovery Share None 1 0
3 Redo Thread Exclusive None 1 0
14 PS Null None 0 0
14 PS Null None 0 1
19 DML Row-X (SX) None 1457 0
36 rows selected.
The utilockt.sql script creates a view called lock_holders that can then be queried to see the locked sessions. Beware, however—this view creates a temporary table and can run slowly. Listing 5.10 is an example query.
Listing 5.10 waitsess.sql displays waiting session information.
COLUMN waiting_session FORMAT a8
SELECT lpad(' ',3*(level-1)) || waiting_session waiting_session,
lock_type,
mode_requested,
mode_held,
lock_id1,
lock_id2
FROM lock_holders
CONNECT BY PRIOR waiting_session = holding_session
START WITH holding_session IS NULL;
Listing 5.11 shows the output.
Listing 5.11 Sample output.
WAITING_ LOCK_TYPE MODE_REQUE MODE_HELD LOCK_ID1 LOCK_ID2 -------- -------------- ---------- --------- --------- --------- 34 None 65 Transaction Exclusive Exclusive 662534 11291 44 Transaction Exclusive Exclusive 662534 11291
Here, we can quickly see that sessions 65 and 44 are waiting on session 34 to complete and release its locks.
Concurrency control has become a pressing issue because of the increasing popularity of the client/server system. As client/server matures, we will begin to see concurrency methods that can automatically manage updates to distributed databases. In the meantime, the developer must decide which of the updated control mechanisms is best suited to the operation of their distributed database.
It’s time now to look at tuning the client side of the application and see just how a distributed server environment will impact performance.
| Previous | Table of Contents | Next |