HP 3000 Manuals HP 3000 Manuals
Monitoring Tasks
This section summarizes the monitoring tasks you can perform with SQLMON,
giving the appropriate SQLMON screens and fields for each task. The
tasks fall into the following categories:
* disk usage
* memory usage
* tables
* hash structures
* indexes and referential constraints
* transactions
* sessions
* I/O
* logging
* locking
For example, if you are interested in checking the size of a DBEFile,
look at the tasks listed in Table 6-4 . To perform the task, go to
the screen listed in the Screens column and the fields listed in the
Fields column. If you need more information on a screen's fields, see
the corresponding section in the "SQLMON Screen Reference" chapter.
Table 6-4. Monitoring Disk Usage
------------------------------------------------------------------------------------------
| | | |
| Task | Screens | Fields |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Determining log file capacity | Overview | LOG FULL % Used LgPgs Max LgPgs |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Determining DBEFile capacity | Static DBEFile | DBEFILE DBEFILE FULLNESS % USED |
| in a DBEFileSet | | PAGES MAX PAGES |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Identifying DBEFile storage | Static DBEFile | DBEFILE TYP BD |
| restrictions | | |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Determining DBEFileSet | Static DBEFile | DBEFILESET DBEFILESET FULLNESS % |
| capacity | | FSUSED PAGES FSMAX PAGES |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Determining the size of | Static Size | DBEFILESET OWNER.TABLE TABLE |
| database objects in a | | PAGES INDEX PAGES TOTAL PAGES |
| DBEFileSet | | |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Identifying detached DBEFiles | Static DBEFile | DBEFILESET FULLNESS DBEFILE |
| | | FULLNESS |
| | | |
------------------------------------------------------------------------------------------
Table 6-5. Monitoring Memory Usage
------------------------------------------------------------------------------------------
| | | |
| Task | Screens | Fields |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Identifying the size of the | IO | TOTAL DATA BUFFER PAGES |
| data buffer pool | | |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Identifying the size of the | IO | TOTAL LOG BUFFER PAGES |
| log buffer pool | | |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Determining the size of the | Overview | RUNTIME CB % Used Pages Max |
| runtime control block | | Pages |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Resolving memory problems | Lock Memory Lock | all fields (hint: type HELP |
| | TabSummary | LOCK MEMORY TUNE 4 for more |
| | | information) |
| | | |
------------------------------------------------------------------------------------------
Table 6-6. Monitoring Tables
------------------------------------------------------------------------------------------
| | | |
| Task | Screens | Fields |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Identifying tables in a | Static | DBEFILESET OWNER.TABLE |
| DBEFileSet | | |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Identifying tables stored in | Static | OWNER.TABLE IMAGE |
| TurboIMAGE data sets | | |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Determining a table type | Static | OWNER.TABLE TYPE |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Determining the number of | Static | OWNER.TABLE NUM INDEXES |
| indexes and referential | | |
| constraints on a table | | |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Determining the size of a | Static Size Static | OWNER.TABLE TABLE PAGES |
| table | Cluster | |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Determining the number of rows | Static Indirect | OWNER.TABLE TOTAL ROWS |
| in a table | Static Cluster | |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Determining the percentage of | Static Indirect | OWNER.TABLE TABLE INDIRECT ROW % |
| indirect rows in a table | | |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Comparing the number of locks | Lock TabSummary | OWNER.TABLE G TOTAL LOCKS |
| by table | | |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Identifying the locks on a | Lock | OWNER.TABLE G PAGE/ROW ID LOCK |
| table | | QUEUE (hint: use SET |
| | | LOCKTABFILTER) |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Monitoring table I/O | SampleIO Tables | OWNER.TABLE SWAPIN SWAPOUT |
| | SampleIO TabIndex | TOTALIO |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Identifying the tables | SampleIO Objects | OWNER.TABLE CURRENT PGS |
| currently residing in the data | | |
| buffer pool | | |
| | | |
------------------------------------------------------------------------------------------
Table 6-7. Monitoring Hash Structures
------------------------------------------------------------------------------------------
| | | |
| Task | Screens | Fields |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Identifying hashed tables in a | Static | DBEFILESET OWNER.TABLE HASH |
| DBEFileSet | | |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Identifying hashed tables in a | Static Hash | DBEFILESET OWNER.TABLE (hint: |
| DBEFileSet | | only hashed tables are |
| | | displayed) |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Monitoring primary pages | Static Hash | PRIMPAGES PRIMDATA PRIMOVERF |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Monitoring overflow pages | Static Hash | OVERPAGES OVERFLOW CHAIN LNGTH |
| | | MAXOVERFLOW AVGOVERFLOW |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Identifying DBEFiles that are | Static DBEFile | DBEFILE BD |
| bound to hashed tables | | |
| | | |
------------------------------------------------------------------------------------------
Table 6-8. Monitoring Indexes and Referential Constraints
------------------------------------------------------------------------------------------
| | | |
| Task | Screens | Fields |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Identifying the indexes and | Static Size Static | DBEFILESET OWNER.TABLE |
| referential constraints on a | Cluster | |
| table or in a DBEFileSet | | |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Determining the number of | Static | DBEFILESET OWNER.TABLE NUM |
| indexes and referential | | INDEXES |
| constraints on a table or in a | | |
| DBEFileSet | | |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Determining the size of an | Static Size | OWNER.TABLE INDEX PAGES |
| index or a referential | | |
| constraint | | |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Identifying locks on a | Lock | OWNER.TABLE/CONSTRAINT G |
| referential constraint | | PAGE/ROW ID LOCK QUEUE (use SET |
| | | LOCKTABFILTER) |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Monitoring index and | SampleIO Indexes | OWNER.TABLE[/INDEX,CONSTRAINT] |
| referential constraint I/O | SampleIO TabIndex | SWAPIN SWAPOUT TOTALIO |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Identifying the indexes and | SampleIO Objects | OWNER.TABLE[/INDEX,CONSTRAINT] |
| referential constraints | | CURRENT PGS |
| currently residing in the data | | |
| buffer pool | | |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Determining the efficiency of | Static Cluster | CCOUNT UNLOAD/LOAD SUGGESTED % |
| index scan over an index or | | |
| referential constraint | | |
| | | |
------------------------------------------------------------------------------------------
Table 6-9. Monitoring Transactions
------------------------------------------------------------------------------------------
| | | |
| Task | Screens | Fields |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Determining the transaction | Overview Program | XID |
| identifier | Overview Session | |
| | Lock Session Lock | |
| | Object Lock Impede | |
| | Lock TabSummary | |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Determining the isolation | Overview Program | ISO |
| level | Overview Session | |
| | Lock Session Lock | |
| | Object Lock Impede | |
| | Lock TabSummary | |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Determining the transaction | Overview Program | PRI |
| priority | Overview Session | |
| | Lock Session Lock | |
| | Object Lock Impede | |
| | Lock TabSummary | |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Determining the transaction | Overview Program | LABEL |
| label | Overview Session | |
| | Lock Session Lock | |
| | Object Lock Impede | |
| | Lock TabSummary | |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Determining the maximum number | Overview Load | MAX XACT |
| of active transactions | | |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Determining the current number | Overview Load | ACTIVE XACT |
| of active transactions | | |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Determining the number of | Overview Load | IMPEDE XACT |
| active transactions waiting | | |
| for a lock | | |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Determining the number of | Load | THROTTLE WT |
| transactions waiting for a | | |
| transaction slot | | |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Monitoring throughput | Load Load Session | BEGIN WORK COMMIT WORK ROLLBK |
| | Load Program | WORK DEADLOCK |
| | | |
------------------------------------------------------------------------------------------
Table 6-10. Monitoring Sessions
------------------------------------------------------------------------------------------
| | | |
| Task | Screens | Fields |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Determining the number of | Overview Overview | SESSIONS |
| sessions | Session Overview | |
| | Program IO IO Data | |
| | Session IO Data | |
| | Program IO Log | |
| | Session IO Log | |
| | Program Load Load | |
| | Session Load | |
| | Program | |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Determining the process | Overview Session | PIN USER@ACCOUNT PROGRAM NAME |
| identifier of a session | | |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Determining the transaction | Overview Session | XID ISO PRI LABEL |
| information of a session | Lock Session Lock | |
| | Impede Lock | |
| | TabSummary | |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Identifying the program being | Overview Program | PIN USER@ACCOUNT PROGRAM NAME |
| run by a session | Lock Session Lock | |
| | Impede Lock | |
| | TabSummary | |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Identifying the sessions | Overview Program | PIN USER@ACCOUNT PROGRAM NAME |
| running a particular program | | |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Identifying all waiting | Overview Session | PIN USER@ACCOUNT STATUS |
| sessions | Overview Program | |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Monitoring session lock | Lock Session | All fields |
| activity | | |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Monitoring session data buffer | IO Data Session IO | All fields |
| I/O | Data Program | |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Monitoring session log buffer | IO Log Session IO | All fields |
| I/O | Log Program | |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Comparing session throughput | Load Load Session | BEGIN WORK COMMIT WORK ROLLBK |
| | Load Program | WORK DEADLOCKS |
| | | |
------------------------------------------------------------------------------------------
Table 6-11. Monitoring I/O for Data
------------------------------------------------------------------------------------------
| | | |
| Task | Screens | Fields |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Identifying the size of the | IO | TOTAL DATA BUFFER PAGES |
| data buffer pool | | |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Monitoring data buffer I/O | IO IO Data Session | BUFF ACCESS DATA DISK RD DATA |
| | IO Data Program | DISK WR MISS RATE |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Monitoring DBEFile I/O | SampleIO | DBEFILE SWAPIN SWAPOUT TOTALIO |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Monitoring table I/O | SampleIO Tables | OWNER.TABLE SWAPIN SWAPOUT |
| | SampleIO TabIndex | TOTALIO |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Monitoring index and | SampleIO Indexes | INDEX,CONSTRAINT SWAPIN SWAPOUT |
| referential constraint I/O | SampleIO TabIndex | TOTALIO |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Determining the number of data | SampleIO Object | CURRENT PGS |
| buffer pages occupied by an | | |
| object | | |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Determining the percentage of | Static Indirect | OWNER.TABLE TABLE INDIRECT ROW % |
| indirect rows in a table | | |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Determining the efficiency of | Static Cluster | CCOUNT UNLOAD/LOAD SUGGESTED % |
| an index scan over an index or | | |
| referential constraint | | |
| | | |
------------------------------------------------------------------------------------------
Table 6-12. Monitoring I/O for Logging
------------------------------------------------------------------------------------------
| | | |
| Task | Screens | Fields |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Identifying the size of the | IO | TOTAL LOG BUFFER PAGES |
| log buffer pool | | |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Monitoring log buffer I/O | IO IO Log Session | LOG BUFF WR LOG DISK RD LOG DISK |
| | IO Log Program | WR |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Monitoring checkpoints | IO | CHECKPOINTS |
| | | |
------------------------------------------------------------------------------------------
Table 6-13. Additional Monitoring for Logging
------------------------------------------------------------------------------------------
| | | |
| Task | Screens | Fields |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Determining log file capacity | Overview | LOG FULL % Used LgPgs Max LgPgs |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Identifying log mode | Overview IO | Archive Mode |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Detecting logging errors | Overview | LOG ERRORS |
| | | |
------------------------------------------------------------------------------------------
Table 6-14. Monitoring Locking
------------------------------------------------------------------------------------------
| | | |
| Task | Screens | Fields |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Determining the size of a | Overview | RUNTIME CB % Used Pages Max |
| runtime control block | | Pages |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Monitoring DBEnvironment lock | Load | LOCK REQTS LOCK WAITS LOCK WAIT |
| activity | | % |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Comparing the number of locks | Lock TabSummary | OWNER.TABLE G TOTAL LOCKS |
| by table | | |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Comparing the number of locks | Lock Memory | TABLE PAGE ROW TOTAL MAXTOTAL |
| by session | | |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Identifying locks on a table | Lock | OWNER.TABLE[/CONSTRAINT] G |
| or referential constraint | | PAGE/ROW ID LOCK QUEUE (hint: |
| | | use SET LOCKTABFILTER) |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Determining the number of | Lock | LOCK QUEUE |
| sessions that are accessing a | | |
| particular lock | | |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Determining the number of | Overview Load | IMPEDE XACT |
| transactions that are waiting | | |
| for locks | | |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Determining the isolation | Overview Program | XID ISO PRI LABEL |
| level of a transaction | Overview Session | |
| | Lock Impede Lock | |
| | Object Lock | |
| | TabSummary Lock | |
| | Session | |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Identifying locks for which | Lock | all fields (hint: use SET |
| sessions are waiting | | LOCKFILTER) |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Identifying sessions that have | Lock Object | GWC MOD PIN |
| obtained a particular lock | | |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Identifying sessions that are | Lock Object | GWC MOD NEW PIN |
| waiting to obtain (or to | | |
| convert) a particular lock | | |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Identifying lock activity for | Lock Session | all fields |
| a particular session | | |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Identifying locks obtained by | Lock Impede | all fields |
| a particular session that are | | |
| causing other sessions to wait | | |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Detecting deadlocks | Load Load Session | DEADLOCKS |
| | Load Program | |
| | | |
------------------------------------------------------------------------------------------
| | | |
| Resolving deadlocks | Lock Lock Object | all fields (hint: type HELP |
| | Lock Impede | LOCK TUNE 13 for more |
| | | information) |
| | | |
------------------------------------------------------------------------------------------
[REV END]