| DATABASE ADMINISTRATION |
REMOTE DBA
HOT-STANDBY
DISASTER RECOVERY
DBO/ANALYZE/ONLINE (DBMS only)
TECHNICAL
ARTICLES -> MAINTENANCE
ARTICLES
Q: |
It appears that DBO/ANALYZE/ONLINE
does not run as a “snapshot” (BATCH RETRIEVAL)
transaction when used with the /NOSET qualifier. This appears
inconsistent with other use of the “/ONLINE” qualifier, and in
our environment this causes excessive stalls waiting for “snapshot
cursor.” Is this a bug or “feature?”
|
A: |
Yes, this is confusing, as is the
documentation on this topic. The DBA Reference Manual (page 9-13)
states “When you specify the /ONLINE qualifier, users can continue
to update the database during the procedure. The online analysis
procedure uses the snapshot file to retrieve the previous version of
any record that has been modified since the analysis began.” This
seems to clearly state that the “/ONLINE” qualifier means it
runs as a snapshot transaction (BATCH RETRIEVAL). However, the
Maintenance and Performance Guide (page 14-3) states, “ANALYZE
runs in PROTECTED RETRIEVAL mode while doing set analysis. (If you are not doing set analysis [/NOSET], DBO/ANALYZE runs in CONCURRENT RETRIEVAL mode.)” The short answer is that DBO always uses “CONCURRENT RETRIEVAL” (not BATCH RETRIEVAL) when performing a /NOSET analysis. In large databases, this command may run for a long time (and thus cause long stalls waiting for the “snapshot cursor” lock). As a work-around, try disabling deferred snapshots ($DBO/MODIFY/SNAP=NODEFER<root>) or modifying the programs that are stalling so that they are also ready for CONCURRENT RETRIEVAL. |