|
Lock Down
|
In this issue, I will analyze the statistics of lockdown in Oracle 10g.
x_to_null column in X$BH is obsolete in Oracle 10g.
If I run the following SQL statements, an error will result.
SQL> |
1 select count(*) cnt,lru_flag,sum(x_to_null)
2 from x$bh
3* group by lru_flag
SQL> /
select count(*) cnt,lru_flag,sum(x_to_null)
*
Error on line 1:
ORA-00904: X_TO_NULL: Invalid identifier
|
Lockdown in 10g
I will explain where to get the statistics of the lockdown in Oracle 10g.
The following performance views are added in Oracle 9i Release 2.
v$segstat
v$segment_statistics
v$segment_statistics has OWNER, OBJECT_NAME, and TABLESPACE_NAME columns
in addition to the columns in v$segstat.
Further Information of Lockdown
You can get the following statistics of the lockdown by viewing v$sysstat.
But, it is much more useful for us to view them in v$segstat because the statistics
can be viewed per object.
SQL> |
1 select statistic_name , sum(value)
2 from v$segment_statistics
3* group by statistic_name
SQL> /
STATISTIC_NAME SUM(VALUE)
--------------------------- ----------
ITL waits 0
buffer busy waits 3900
db block changes 1668608
gc buffer busy 180
gc cr blocks received 8711
gc current blocks received 12012
logical reads 7632208
physical reads 44455
physical reads direct 1
physical writes 42898
physical writes direct 108
row lock waits 310
segment scans 81
space allocated 7864320
space used 5272976
|
The table above contains the accumulation of the statistics of all objects
after an instance is started. X_TO_NULL in X$BH, on the other hand,
contains the information on the database buffer only.
gc current blocks received indicates the statistics of Write/Write contention.
The value is counted up when a block is obtained from other instances in current mode.
I get back to the basics for a while. How does the lockdown occur?
1. Write/Write contention occurs.
2. Write lock is released in a node holding a write lock.
3. Block is sent to a node querying the block
4. gc current blocks received is counted up in a node querying the block.
gc current blocks received is actually not the statistics of the occurrence of
the lockdown but the number of times which causes the lockdown.
The number of times which caused lockdowns per object
SQL> |
1 select object_name, statistic_name , value
2 from v$segment_statistics
3 where statistic_name like 'gc %'
SQL> /
OBJECT_NAME STATISTIC_NAME SUM(VALUE)
-------------- ------------------------------ ----------
ITEM gc buffer busy 0
ITEM gc cr blocks received 0
ITEM gc current blocks received 81
TEST gc buffer busy 0
TEST gc cr blocks received 0
TEST gc current blocks received 0
STOCK gc buffer busy 1
STOCK gc cr blocks received 602
STOCK gc current blocks received 603
ORDERS gc buffer busy 8
ORDERS gc cr blocks received 1
ORDERS gc current blocks received 146
.
.
.
.
|
Statistics of all objects are accumulated since an instance is started.
But, you have to be careful accessing this view because a huge amount of
records may be scanned in some environment. How do we check this view smoothly?
I will talk about that next week.
That's it for today.
Yoshihiro Uratsuji
|
|
