| Oracle7 Parallel Server Concepts and Administrator's Guide |  Library |
 Product |
 Contents |
 Index |
| Oracle7 Parallel Server Concepts and Administrator's Guide | Library |
Product |
Contents |
Index |
Run the CATPARR.SQL script after creating or modifying database objects and allocating storage for them, not when you create the database. Do not include CATPARR.SQL in the value of the initialization parameter INIT_SQL_FILES, which specifies SQL scripts such as CATALOG.SQL to run at database creation.
CATALOG.SQL creates the standard V$ dynamic views, as described in the "Data Dictionary Reference" chapter of Oracle7 Server Reference.
You should rerun CATPARR.SQL to update the information about database objects in the views after making any structural changes to the database, or after the Oracle Server allocates new extents to database objects.
Note: If you drop objects without rerunning CATPARR.SQL, the views may display misleading information about those database objects.
The following data dictionary views are available to monitor a parallel server:
See Also: "Dynamic Performance & Monitoring Views"
![[*]](jump.gif)
for descriptions of the data dictionary views.Oracle7 Server Reference for more information on dynamic views and monitoring your database.
| V$BH |
| V$CACHE |
| V$CACHE_LOCK |
| V$FALSE_PING |
| V$LOCK_ACTIVITY |
| V$LOCK_ELEMENT |
| V$PING |
The V$BH, V$CACHE, and V$PING views contain statistics about the frequency of PCM lock conversion due to contention between instances. Each row in these views represents one block in the buffer cache of the current instance.
Each block starts with an XNC value of zero when it first enters the buffer cache. This value is incremented whenever the instance releases the PCM lock covering that block. If a PCM lock covers multiple blocks, they can have different values of XNC because they may enter the buffer cache at different times.
Note: A single block can appear in multiple rows of the V$BH, V$CACHE, and V$PING views. Each row represents a different copy (version) of the block. Multiple versions created for read-consistent queries appear with the status CR. For tuning purposes, you only need consider the current copy (status XCUR or SCUR) that contains the greatest value of XNC.
When an instance writes a block to disk and reuses that buffer for other data, XNC is reset to zero. If the block returns to the buffer cache while other versions of that block are still in the cache, it starts with the greatest value of XNC for any version of the same block, rather than starting with zero.
Use the following procedure to monitor and tune the distributed lock activity in a parallel server.
This section covers the following topics:
SELECT * FROM V$LOCK_ACTIVITY;
could display these rows:
FROM TO ACTION COUNTER ---- ---- -------------------------------------------------- ------- NULL S Lock buffers for read 5953 NULL X Lock buffers for write 1118 S NULL Make buffers CR (no write) 6373 S X Upgrade read lock to write 2077 X NULL Make buffers CR (write dirty buffers) 1 X S Downgrade write lock to read (write dirty buffers) 3164 X SSX Write transaction table/undo blocks 1007 SSX NULL Transaction table/undo blocks (write dirty buffers) 2 SSX S Make transaction table/undo block available share 1 SSX X Rearm transaction table write mechanism 1007
See Also: Your platform-specific Oracle documentation for information about connecting with SQL*Net.
To Monitor and Tune Instance Lock Activity
SELECT * FROM V$LOCK_ACTIVITY;
SELECT * FROM V$LOCK_ACTIVITY;
SELECT * FROM V$PING;
SELECT * FROM V$PING WHERE XNC > 100;
SELECT FILE#, BLOCK#, MAX(XNC) FROM V$PING GROUP BY FILE#, BLOCK#;
Note: Querying V$BH is faster than querying V$PING or V$CACHE. You can query V$BH to find the block numbers and file numbers of interest, then query V$CACHE for the particular blocks to find out what database objects contain them.
to look up its class. If a pinged block is class 1, for example, you should change the GC_FILES_TO_LOCKS and GC_DB_LOCKS parameters. If a block is class 4, change GC_SEGMENTS.
See Also: "Dynamic Performance Views" for a description of each view.
To Detect Pinging
SQL> SELECT NAME, FILE#, CLASS#, MAX(XNC) FROM V$PING
2 GROUP BY NAME, FILE#, CLASS#
3 ORDER BY NAME, FILE#, CLASS#;
NAME FILE# CLASS# MAX(XNC)
------------ -------- ------ ---------
...
DEPT 8 1 492
DEPT 8 4 10
EMP 8 1 3197
EMP 8 4 29
...
SQL> SELECT * FROM V$PING WHERE FILE# = 8;
FILE# BLOCK# STAT XNC CLASS# NAME KIND
------ ------ ---- ----- ------ -------------- -------
8 98 XCUR 450 1 EMP TABLE
8 764 SCUR 59 1 DEPT TABLE
SQL> SELECT ROWID, EMPNO, ENAME FROM EMP
2 WHERE chartorowid(rowid) like '00000062%';
ROWID EMPNO ENAME
------------------ ------ ----------
00000062.0000.0008 12340 JONES
00000062.0000.0008 6491 CLARK
....;
This section covers the following topics:
To Monitor Contention for Blocks in Free Lists
SQL> SELECT CLASS, COUNT FROM V$WAITSTAT
2 WHERE CLASS = 'free list';
CLASS COUNT
------------------ -------
free list 12
SQL> SELECT SUM(VALUE) FROM V$SYSSTAT
2 WHERE name IN
3 ('db block gets', 'consistent gets');
SUM (VALUE)
------------
12050211
SQL> CREATE TABLE new_emp
2 STORAGE (FREELISTS 5)
3 AS SELECT * FROM emp;
Table created.
SQL> DROP TABLE emp;
Table dropped.
SQL> RENAME new_emp TO emp;
Table renamed.
To Monitor Contention for Rollback Segments
SQL> SELECT CLASS, COUNT
2 FROM V$WAITSTAT
3 WHERE CLASS IN ('system undo header',
4 'system undo block','undo header','undo block');
CLASS COUNT
------------------ -------
system undo header 12
system undo block 11
undo header 28
undo block 6
SQL> SELECT SUM(VALUE) FROM V$SYSSTAT
2 WHERE name IN
3 ('db block gets', 'consistent gets');
SUM (VALUE)
------------
12050211
See Also: "Data Dictionary Reference" chapter in Oracle7 Server Reference.
To Query the V$FILESTAT View
SQL> SELECT NAME, PHYRDS, PHYWRTS
2 FROM V$DATAFILE df, V$FILESTAT fs
3 WHERE df.file# = fs.file#;
NAME PHYRDS PHYWRTS
------------------------- ----------- ----------
/test71/ora_system.dbs 7679 2735
/test71/ora_system1.dbs 32 546
To Display System Statistics
SQL> SELECT * FROM V$SYSSTAT WHERE CLASS = 32 OR CLASS = 40;
STATISTIC# NAME CLASS VALUE
---------- -------------------------------------- ----- --------
28 global lock gets (non async) 32 225663
29 global lock gets (async) 32 169023
30 global lock get time 32 23199
31 global lock converts (non async) 32 773052
32 global lock converts (async) 32 93488
33 global lock convert time 32 65636
34 global lock releases (non async) 32 381994
35 global lock releases (async) 32 0
36 global lock release time 32 13637
59 DBWR cross instance writes 40 230
60 remote instance undo writes 40 0
61 remote instance undo requests 40 255
62 cross instance CR read 40 24
69 next scns gotten without going to DLM 32 0
73 calls to get snapshot scn kcmgss 32 349
74 kcmsss waited for batching 32 0
75 kcmgss reads scn without going to DLM 32 0
84 hash latch wait gets 40 1
18 rows selected.
The following tips will help you interpret statistics obtained from these views.
global lock converts (async)
Divide this number by the V$SYSSTAT statistic "user commits" to calculate the percentage of cache hits.
This value equates to the number of blocks pinged. For large values, reallocate locks based on V$PING statistics.
A large value may signify pinging activity.
A large value indicates that data modified by this instance if often read by another instance; locate applications (and thus transactions) contending for the same data on the same instance.
This is a slow read because every instance has to write out the block; a large value indicates that the instance is spending too much time waiting on blocks modified by other instances. Evaluate the distribution of locks in the GC_FILES_TO_LOCKS parameter and reallocate to keep the value of this statistic small.
next scns gotten without going to DLM
Divide this value by the total number of SCN gets given by the "user commits" statistic to calculate the percentage of SCN gets satisfied from the cache and thus measure the effectiveness of a parallel server's SCN cache.
If this value is large or rapidly increasing, increase the number of hash latches.
An internal call to get a snapshot might have to wait (for an on-going fetch of a SCN to complete) before contacting the distributed lock manager. This statistic value indicates system load and the number of opportunities that Oracle has to batch a single get-snapshot-SCN with other SCN fetches.
kcmgss reads scn without going to DLM
If an internal call (to get a snapshot SCN) waits for an on-going SCN fetch, it may use the SCN acquired by the SCN fetch, thus avoiding overhead in using the distributed lock manager.
The ratio of "kcmgss reads scn without going to DLM" and "kcmgss waited for batching" indicates the effectiveness of the parallel server's SCN batch algorithm.
See Also: Oracle7 Server Reference for definitions of these statistics.
Oracle Server Manager User's Guide descriptions of the MONITOR STATISTICS CACHE display for information about monitoring contention for various kinds of blocks.
|
Prev Next |
 Copyright © 1996 Oracle Corporation. All Rights Reserved. |
Library |
Product |
Contents |
Index |