Database Reference
In-Depth Information
INT SQL_ID AWT CONWT CLWT IOICBYTES
---- ---------------- -------- ------------- ------------- -------------
1 9q7k9nbpvk8pv 1303 8681215 3833115628 12166062080
7p9s66ud42nmw 1607 14135556 1966278932 5832507392
dhp8kqqkag9tq 2401 213355467 54421595 0
7x5s1cjq1dd8z 1882 7169 35940718 1099939840
bq829z449nhfu 3130756 1651536 32660485 23085375488
6h85ar3uzms52 926053 7850711 29252830 28858040320
2 7p9s66ud42nmw 87597709 11132035 1985073170 6146048000
dhp8kqqkag9tq 1.11E+08 214990704 376465304 8192
7x5s1cjq1dd8z 785755 12371906 84915341 52792958976
bq829z449nhfu 629 19779 14628380 1275248640
3 3v6dt7mn41xb3 1878 1403328 11742867534 6319423488
8104kxuj6f3hp 1559 9791612 748775808 1299734528
106crpma4qfu9 574 22560 20996170 1303863296
4 3v6dt7mn41xb3 4043 964909 10298084731 9079422976
8104kxuj6f3hp 2126 5922086 104678129 12071821312
1q931v7v7skp7 1727773 10161297 53504995 28051447808
cfzuc2bt9kk4s 605709 10428350 45948917 33957453824
Querying
GV$SQLSTATS
indicated the application used database sequence numbers to populate the primary key
column during
INSERT
operations:
SELECT inst_id INT,
sql_id,
sql_text
FROM gv$sqlstats
WHERE sql_id = 'dhp8kqqkag9tq';
INT SQL_ID SQL_TEXT
--- ---------------- ---------------------------------------------
1 dhp8kqqkag9tq SELECT INST_FILE_UPLOAD_SEQ.NEXTVAL FROM DUAL
2 dhp8kqqkag9tq SELECT INST_FILE_UPLOAD_SEQ.NEXTVAL FROM DUAL
3 dhp8kqqkag9tq SELECT INST_FILE_UPLOAD_SEQ.NEXTVAL FROM DUAL
4 dhp8kqqkag9tq SELECT INST_FILE_UPLOAD_SEQ.NEXTVAL FROM DUAL
Step 8
Almost always, due to movement of blocks between the holding instance and the requesting instance (reasons
discussed in Chapter 2), highly insert-intensive applications that generate surrogate keys using database sequence
numbers are creating significant contention issues at the index leaf block level. The contention can be at the leaf block
level when inserting rows into the table and can also be delayed if there are concurrent requests for sequence due to
depilation of the sequence cache (default 20). Typically, instance-level contention issues for sequences measured in
frequent gets (
GETS
), misses (
GETMISSES
), and updates (
MODIFICATIONS
) can be obtained by checking the
dc_sequences
parameter from
GV$ROWCAHCE
view; similarly, cluster-level contentions can also be obtained from the same view by
querying the
DLM_REQUESTS
,
DLM_CONFLICTS
, and
DLM_RELEASES
columns.
Search WWH ::
Custom Search