Viewing resource consumption by database
The Throughput_all view provides a global view of resource consumption by a database.
Common uses of this data include:
- Detecting performance problems by watching for excess consumption of specific resources
- Tracking how much work is being accomplished by a database
Source
The data found in the throughput_all view comes from the table function mon_get_service_subclass. Data is aggregated across all service classes, including the system service class.
Columns
As with all repository views, the columns marked Y in the Delta column are actually
two columns. For example, the metric described here as rows_read is actually a pair of columns:
- rows_read: a counter of how many rows have been read by activities since the data server was activated
- rows_read_delta: identifies the number of rows read by activities within the collection interval. For most purposes, the _delta version of the metrics is the most useful. The raw counter version is provided for completeness.
| Column Name | Delta | Type | Description |
|---|---|---|---|
| dbconn_int | n | integer | |
| dbconn_id | n | varchar(255) | |
| prev_collected | n | timestamp | |
| collected | n | timestamp | |
| delta_msec | n | bigint | |
| row_id | n | char(5) | This column always contains the string (all) |
| total_cpu_usec | Y | bigint |
Microseconds of CPU time consumed by the data server. Divide by 1,000,000 to scale this as seconds. CPU consumption is a good indication of how much work is being done. All activities, including overhead (such as compiling queries and utility work), are reflected in CPU consumption. Inefficiencies that increase the cost of doing business, such as excessive recompilation or lock contention, can sometimes be seen by observing that CPU consumption has risen disproportionately to the amount of useful work that is being accomplished. |
| act_completed_total | Y | bigint |
Activities completed by the data server. Activities typically correspond to a query. The number of activities completed is a measure of work accomplished. |
| rows_read | Y | bigint | Rows read is a good indication of how much work is being done by queries. Non-query work, such as utilities and overhead for internal activities of the data server, is largely excluded from rows read. As a result, it is a better measure than CPU time for evaluating how much useful work is being accomplished. |
| rows_modified | Y | bigint | Rows modified includes updates and deletions of table data. Modifying data is much more expensive than reading it. |
| rows_returned | Y | bigint | This is the number of rows returned to the client by queries. Excessive amounts of data returned to the client can indicate a run-away query (cross product, missing filters), abuse (people in the process of making unauthorized copies of the data), or even bad practices, such as misguided attempts to pull large portions of the data into a spreadsheet or an application for post processing that could be better done inside the server. |
| logical_reads | Y | bigint | |
| direct_reads | Y | bigint | |
| direct_writes | Y | bigint | |
| total_sorts | Y | bigint | |
| total_app_commits | Y | bigint | |
| total_commit_time | Y | bigint | |
| total_app_rollbacks | Y | bigint | |
| total_section_time | Y | bigint | |
| total_routine_time | Y | bigint | |
| pool_read_time | Y | bigint | |
| pool_write_time | Y | bigint | |
| direct_read_time | Y | bigint | |
| direct_write_time | Y | bigint | |
| otal_section_sort_time | Y | bigint | |
| wlm_queue_time_total | Y | bigint | |
| lock_wait_time | Y | bigint | |
| log_buffer_wait_time | Y | bigint | |
| log_disk_wait_time | Y | bigint | |
| total_compile_proc_time | Y | bigint | |
| total_implicit_compile_proc_time | Y | bigint | |
| lock_waits | Y | bigint | |
| lock_timeouts | Y | bigint | |
| lock_escals | Y | bigint | |
| deadlocks | Y | bigint | |
| agent_wait_time | Y | bigint | |
| lock_wait_time_global | Y | bigint | |
| tcpip_wait_time | Y | bigint | |
| ipc_wait_time | Y | bigint | |
| audit_subsystem_wait_time | Y | bigint | |
| audit_file_wrt_wait_time | Y | bigint | |
| diaglog_write_wait_time | Y | bigint | |
| evmon_wait_time | Y | bigint | |
| total_extended_latch_wait_time | Y | bigint | total extended latch wait time |
| prefetch_wait_time | Y | bigint | |
| comm_exit_wait_time | Y | bigint | |
| ida_wait_time | Y | bigint | |
| cf_wait_time | Y | bigint | |
| reclaim_wait_time | Y | bigint | |
| spcmapg_reclaim_wait_time | Y | bigint | spacemappage_reclaim_wait_time |
| total_rtn_u_code_proc_time | Y | bigint | total_rountine_user_code_proc_time |
| total_section_proc_time | Y | bigint | |
| total_sect_sort_proc_time | Y | bigint | total_section_sort_proc_time |
| total_col_proc_time | Y | bigint | |
| total_commit_proc_time | Y | bigint | |
| total_rollback_proc_time | Y | bigint | |
| total_runstats_proc_time | Y | bigint | |
| total_reorg_proc_time | Y | bigint | |
| total_load_proc_time | Y | bigint | |
| total_backup_proc_time | Y | bigint | |
| total_idx_build_proc_time | Y | bigint | total_index_build_proc_time |
| total_con_req_prc_time | Y | bigint | total_connect_request_proc_time |
| total_con_auth_prc_time | Y | bigint | total_connect_authentication_proc_time |
| client_idle_wait_time | Y | bigint | |
| total_rqst_time | Y | bigint | |
| total_wait_time | Y | bigint | |
| sum_sql_execution_time | Y | bigint | |
| sum_nonsql_proc_time | Y | bigint | |
| sum_io_time | Y | bigint | |
| local_lock_wait_time | Y | bigint | |
| other_wait_time | Y | bigint | |
| total_act_time | Y | bigint | |
| act_aborted_total | Y | bigint | |
| total_compilations | Y | bigint | |
| sort_overflows | Y | bigint | |
| physical_reads | Y | bigint | |
| fed_wait_time | Y | bigint | |
| fed_rows_deleted | Y | bigint | |
| fed_rows_inserted | Y | bigint | |
| fed_rows_updated | Y | bigint | |
| fed_rows_read | Y | bigint | |
| fed_waits_total | Y | bigint |