HP Neoview Repository User Guide HP Part Number: 544811-001 Published: April 2008 Edition: N02.
© Copyright 2008 Hewlett-Packard Development Company, L.P. Legal Notice Confidential computer software. Valid license from HP required for possession, use or copying. Consistent with FAR 12.211 and 12.212, Commercial Computer Software, Computer Software Documentation, and Technical Data for Commercial Items are licensed to the U.S. Government under vendor’s standard commercial license. The information contained herein is subject to change without notice.
Table of Contents About This Document.........................................................................................................9 Supported Release Version Updates (RVUs)..........................................................................................9 Intended Audience.................................................................................................................................9 New and Changed Information in This Edition.............................................
Get the Number of Rows in a View......................................................................................................61 Retrieve a Specified Number of Rows from a View.............................................................................61 Retrieve Statistics for Completed Queries............................................................................................61 Retrieve Statistics for Incomplete Queries......................................................................
List of Figures 1-1 Neoview Repository and Related Products..................................................................................
List of Tables 3-1 3-2 3-3 3-4 3-5 3-6 3-7 3-8 3-9 3-10 3-11 3-12 3-13 3-14 3-15 3-16 3-17 Comparison of ODBC Query Statistics and Query Runtime Statistics.........................................21 ODBC_QUERY_STATS_V1 Field Definitions...............................................................................22 ODBC_QUERY_STATS_V2 Fields.................................................................................................25 QUERY_RUNTIME_STATS_V1 Field Definitions...........................
About This Document This document describes the views supported by the Neoview Manageability Repository, a Neoview SQL database and set of programs that collect and store statistics and other information about queries, query sessions, tables, disks, processing nodes, and (optionally) selected processes involved in query execution on a Neoview platform. Supported Release Version Updates (RVUs) This manual supports N02.03 and subsequent N-series RVUs until otherwise indicated in a replacement publication.
Document Organization This document is organized as follows: Chapter 1: Introduction Use this chapter to learn about the Repository and related Neoview software products. Chapter 2: Getting Started with Repository 2.3 Use this chapter to find how to obtain and install the Repository software, enable capture of session data, and configure retention times for data.
DROP SCHEMA schema [CASCADE] [RESTRICT] DROP SCHEMA schema [ CASCADE | RESTRICT ] { } Braces Braces enclose required syntax items. For example: FROM { grantee[, grantee]...} A group of items enclosed in braces is a list from which you are required to choose one item. The items in the list can be arranged either vertically, with aligned braces on each side of the list, or horizontally, enclosed in a pair of braces and separated by vertical lines.
If there is no space between two items, spaces are not permitted. In this example, no spaces are permitted between the period and any other items: myfile.sh Line Spacing If the syntax of a command is too long to fit on a single line, each continuation line is indented three spaces and is separated from the preceding line by a blank line. This spacing distinguishes items in a continuation line from items in a vertical list of selections.
Neoview Workload Management Information about using Neoview Workload Management Services (WMS) to Services Guide manage workload and resources on a Neoview data warehousing platform.
1 Introduction Repository Features and Interfaces The Neoview Manageability Repository is a Neoview SQL database and set of programs that automatically collect and store statistics and other information about the following entities on a Neoview platform: • • • • • • Queries initiated through ODBC and JDBC. Certain data about queries is available from the time the query starts; other data becomes available only after the query is complete. Queries managed by Workload Management Services (WMS).
NOTE: Because the Query Runtime Statistics feature updates the Repository as new query statistics information is received from the Dashboard, you can now use a standard SQL interface (such as the Neoview Command Interface) to monitor running queries on a Neoview platform. The figure labeled Figure 1-1 illustrates, at a very high level, the architectural relationship and interfaces of the Dashboard and Repository.
2 Getting Started with Repository 2.3 Installation The Repository is initially installed on the Neoview platform by HP Manufacturing. Repository upgrades are installed by field personnel in the context of Neoview platform upgrades. If no Repository program files or tables have ever existed on the platform, the installation script creates Repository tables and views, and installs and starts Repository 2.3 processes.
If you’ve written queries using views defined in Release 2.1, modify them to use the corresponding views described in this document. The Release 2.1 (deprecated) views are no longer available in Release 2.3. Differences between Release 2.1 and more recently defined views include: • • • Naming consistent with the terminology used in Neoview user documentation. ANSI SQL Date and Time columns, which allow ANSI SQL standard date and time computations.
Starting the Repository During installation, the Repository is configured to come up automatically when the Neoview platform starts. By default, statistics are collected for queries, sessions, processing nodes, and tables but not processes. The same Repository configuration can support ODBC query statistics, query runtime statistics, or both. By default, both ODBC query statistics and query runtime statistics are collected.
Repository Metric Retention Time Table statistics 30 days Disk statistics 30 days To modify these retention times, contact your account representative. 20 Getting Started with Repository 2.
3 Repository Views Overview You can use the following views for access to the Repository: • • • • • • • • • • “VIEW NEO.HP_METRICS.ODBC_QUERY_STATS_V1” (page 22) “VIEW NEO.HP_METRICS.ODBC_QUERY_STATS_V2” (page 25) “VIEW NEO.HP_METRICS.QUERY_RUNTIME_STATS_V1” (page 27) “VIEW NEO.HP_METRICS.ODBC_SESSION_STATS_V1” (page 35) “VIEW NEO.HP_METRICS.ODBC_SESSION_STATS_V2” (page 38) “VIEW NEO.HP_METRICS.NODE_STATS_V1” (page 41) “VIEW NEO.HP_METRICS.PROCESS_STATS_V1” (page 44) “VIEW NEO.HP_METRICS.
Table 3-1 Comparison of ODBC Query Statistics and Query Runtime Statistics (continued) ODBC Query Statistics Query Runtime Statistics Data is available for all queries, whether unique or non-unique. Data is available only for queries monitored by WMS. (WMS monitors only NON_UNIQUE queries.) Not synchronized with query data in Neoview Management Dashboard. Synchronized with QueryRTS entity in Neoview Management Dashboard. VIEW NEO.HP_METRICS.
Table 3-2 ODBC_QUERY_STATS_V1 Field Definitions (continued) Field Name Data Type Description METRIC_CLASS_ID INTEGER UNSIGNED Repository assigned value. Used internally. METRIC_CLASS_VERSION INTEGER UNSIGNED Repository assigned value. Used internally. SEQUENCE_SIZE SMALLINT UNSIGNED Number of rows required to accommodate the query text, which is provided in 3200–byte chunks. EST_COST DOUBLE PRECISION Estimated query cost. DISK_READS LARGEINT Cumulative number of disk reads.
Table 3-2 ODBC_QUERY_STATS_V1 Field Definitions (continued) 24 Field Name Data Type Description DATASOURCE CHARACTER(128) or NCHAR(128) Client data source name used at the time of the connection. APPLICATION_ID CHARACTER(130) or NCHAR(128) Client application name or main window caption. CLIENT_ID CHARACTER(15) or NCHAR(15) Client workstation TCP/IP or NetBios/network name. SEGMENT_NAME CHARACTER(10) or NCHAR(10) Name of the Neoview segment used by the data source.
Table 3-2 ODBC_QUERY_STATS_V1 Field Definitions (continued) Field Name Data Type Description QUERY_ID VARCHAR(160) or NCHAR VARYING(160) A unique ID for the query, beginning with the string “MXID” and including, among other elements a unique query number, delimited by underscores, and an ODBC-generated statement ID. If an error occurred before the query launched, the value in this field is the text . SQL_TEXT VARCHAR(3200) or NCHAR VARYING(3200) Text of the query.
Table 3-3 ODBC_QUERY_STATS_V2 Fields (continued) 26 Complete Queries Incomplete Queries Data Type Source and Notes SESSION_ID SESSION_ID CHAR(108) or NCHAR(108) START USER_ID USER_ID CHAR(8) or NCHAR(8) START USER_NAME USER_NAME CHAR(20) or NCHAR(20) START CLIENT_ID CLIENT_ID CHAR(15) or NCHAR(15) START APPLICATION_ID APPLICATION_ID CHAR(130) or NCHAR(130) START DATASOURCE DATASOURCE CHAR(128) or NCHAR(128) START QUERY_ID QUERY_ID when available (see Note following table) VAR
Table 3-3 ODBC_QUERY_STATS_V2 Fields (continued) Complete Queries Incomplete Queries Data Type Source and Notes QUERY_END_TIME QUERY_START_TIME TIME END, or NULL for an incomplete query QUERY_END_DATETIME_UTC QUERY_START_DATETIME_UTC TIMESTAMP END, or NULL for an incomplete query QUERY_END_DATE_UTC QUERY_START_DATE_UTC DATE END, or NULL for an incomplete query QUERY_END_TIME_UTC QUERY_START_TIME_UTC TIME END, or NULL for an incomplete query EST_COST DOUBLE PRECISION START, else END, els
When compared with the other available views that provide information for queries, this one contains many additional fields. Fields available only in this view are marked with asterisks (*) in the list below. Note that only queries managed by Neoview Workload Management Services are reflected in this view. For information about the features and limitations of WMS, see the Neoview Workload Management Services Guide. Also note that query completion statistics are not guaranteed to be present in this view.
Table 3-4 QUERY_RUNTIME_STATS_V1 Field Definitions (continued) Field Name (* means available only in this view) Data Type PCT_NODE_BUSY_ALL_ESPS* SMALLINT UNSIGNED NO DEFAULT Description Cumulative node utilization percentage for all the Executor Server Processes. PCT_TOT_BUSY_FOR_INTERVAL* SMALLINT UNSIGNED NO DEFAULT Node utilization percentage for all query processes for the last statistics collection interval.
Table 3-4 QUERY_RUNTIME_STATS_V1 Field Definitions (continued) Field Name (* means available only in this view) Data Type 30 Description USER_ID INTEGER SIGNED NO DEFAULT Numeric form of the user ID, an integer identifying the user. NUM_ESPS_GT_THRESHOLD* INTEGER SIGNED NO DEFAULT Total number of child Executor Server Processes busy (as busy as or busier than BUSY_THRESHOLD) during the last interval.
Table 3-4 QUERY_RUNTIME_STATS_V1 Field Definitions (continued) Field Name (* means available only in this view) Data Type Description MESSAGE_BYTES_TO_DISK LARGEINT SIGNED NO DEFAULT Number of bytes written to disk. MESSAGES_TO_DISK LARGEINT SIGNED NO DEFAULT Number of messages sent to disk. ROWS_ACCESSED LARGEINT SIGNED NO DEFAULT Number of records accessed by the disk process or the file system to evaluate the statement.
Table 3-4 QUERY_RUNTIME_STATS_V1 Field Definitions (continued) Field Name (* means available only in this view) Data Type 32 Description EST_IDLE_TIME* DOUBLE PRECISION NO DEFAULT Estimate of the number of seconds to wait for an operation to complete, (e.g., opening a table or starting a process). The estimate occurs at the time the query is prepared. EST_NODE_TIME* DOUBLE PRECISION NO DEFAULT Estimate of the number of seconds of processor time it might take to execute the query.
Table 3-4 QUERY_RUNTIME_STATS_V1 Field Definitions (continued) Field Name (* means available only in this view) Data Type Description QUERY_ELAPSED_TIME INTERVAL MINUTE(18) NO DEFAULT Elapsed clock time, in minutes, since the query started executing, including compile time and time spent in the WMS queue. Sum of MASTER_EXECUTOR_BUSY_TIME, COMPILE_ELAPSED_TIME, and QUE_TIME.
Table 3-4 QUERY_RUNTIME_STATS_V1 Field Definitions (continued) Field Name (* means available only in this view) Data Type 34 Description QUERY_STATUS* CHAR(14) or NCHAR(14) NO DEFAULT What the executor is doing (INITIAL, OPEN, EOF, CLOSE, DEALLOCATED, FETCH, CLOSE_TABLES, PREPARE, PROCESS_ENDED, UNKNOWN). For queries with actual end statistics, this value will be CLOSE or DEALLOCATED.
Table 3-4 QUERY_RUNTIME_STATS_V1 Field Definitions (continued) Field Name (* means available only in this view) Data Type Description EXEC_START_TS TIMESTAMP(6) NO DEFAULT ANSI Timestamp, in Local Civil Time, indicating when query execution began. If the query has not yet begun execution, the value is NULL. EXEC_END_TS TIMESTAMP(6) NO DEFAULT ANSI Timestamp indicating when query execution ended, converted to LCT from UTC. If the query has not yet begun execution, the value is NULL.
Table 3-5 ODBC_SESSION_STATS_V1 Field Definitions (continued) 36 Field Name Data Type Description SESSION_EVENT_DATETIME_UTC TIMESTAMP(6) ANSI SQL Timestamp, in Coordinated Universal Time, indicating when the statistics were recorded in the Repository. SESSION_EVENT_DATE_UTC DATE ANSI SQL date, in Coordinated Universal Time, when the statistics were recorded in the Repository.
Table 3-5 ODBC_SESSION_STATS_V1 Field Definitions (continued) Field Name Data Type Description TOTAL_INSERT_STMTS_EXECUTED LARGEINT Total number of SQL INSERT statements sent by the HP ODBC driver to the NDCS SQL server. Does not include internally generated SQL statements. TOTAL_ODBC_ELAPSED_TIME LARGEINT Total NDCS server real time, in microseconds, from the time the connection started to the time it was closed.
Table 3-5 ODBC_SESSION_STATS_V1 Field Definitions (continued) Field Name Data Type Description SEGMENT_NAME CHAR(10) or NCHAR(10) Name of the Neoview segment used by the data source. PROCESS_NAME CHAR(18) or NCHAR(18) Process name of the NDCS server. USER_ID CHAR(8) or or NCHAR(8) A deprecated internal representation of a Neoview role. USER_NAME CHAR(20) or or NCHAR(20) The name, used at time of connection, to identify a Neoview user.
Character columns use ISO88591 or UCS2 encoding, as appropriate to the Neoview platform configuration. In the following table: • If a data type is given as CHAR or NCHAR, then CHAR applies if the character set is ISO88591, and NCHAR applies if the character set is UCS2. • If a data type is given as VARCHAR or NCHAR VARYING, the VARCHAR applies if the character set is ISO88591, and NCHAR VARYING applies if the character set is UCS2.
Table 3-6 ODBC_SESSION_STATS_V2 Fields (continued) 40 Source and Notes Complete Sessions Incomplete Sessions Data Type SESSION_END_DATE SESSION_END_DATE DATE END, or NULL for an incomplete session SESSION_ END _TIME SESSION_ END _TIME TIME END, or NULL for an incomplete session SESSION_END_DATETIME_UTC SESSION_START_DATETIME_UTC TIMESTAMP END, or NULL for an incomplete session SESSION_END_DATE_UTC SESSION_END_DATE_UTC DATE END, or NULL for an incomplete session SESSION_ END _TIME_UTC SE
Table 3-6 ODBC_SESSION_STATS_V2 Fields (continued) Complete Sessions Incomplete Sessions Source and Notes Data Type TOTAL_INSERT_STMTS_EXECUTED LARGEINT END, or NULL for an incomplete session TOTAL_ODBC_ELAPSED_TIME LARGEINT END, or NULL for an incomplete session TOTAL_MASTER_EXEC_EXECUTION_TIME LARGEINT END, or NULL for an incomplete session TOTAL_PREPARES LARGEINT END, or NULL for an incomplete session TOTAL_SELECT_STMTS_EXECUTED LARGEINT END, or NULL for an incomplete session TOTAL_UPD
Table 3-7 NODE_STATS_V1 Field Definitions (continued) 42 Field Name Data Type Description MEASURE_END_DATETIME TIMESTAMP(6) ANSI SQL timestamp, in Local Civil Time, indicating when the measurement interval ended. MEASURE_END_DATE DATE ANSI SQL date, in Local Civil Time, indicating when the measurement interval ended. MEASURE_END _TIME TIME(6) ANSI SQL time, in Local Civil Time, indicating when the measurement interval ended.
Table 3-7 NODE_STATS_V1 Field Definitions (continued) Field Name Data Type Description DISPATCHES LARGEINT Number of times a process was selected from the ready list and executed by the processing node. SWAPS LARGEINT Number of swap operations, both into and out of memory, performed by the memory manager. INTR_BUSY_TIME LARGEINT Time, in microseconds, that the processing node spent executing interrupt handlers.
• • The Neoview NDCS server (MXOSRVR) The Repository collector processes (MMCOLLEC) By default, a measurement of each of these processes occurs every five minutes. Aggregates are computed every five minutes with a 15–minute delay: that is, aggregate data becomes available from the Repository 15 minutes after the end of the sampling interval. VIEW NEO.HP_METRICS.PROCESS_STATS_V1 This view is populated only if process data collection is enabled.
Table 3-8 PROCESS_STATS_V1 Field Definitions (continued) Field Name Data Type Description MEASURE_START_DATE_UTC DATE ANSI SQL date, in Coordinated Universal Time, indicating when the measurement interval began MEASURE_START_TIME_UTC TIME(6) ANSI SQL time, in Coordinated Universal Time, indicating when the measurement interval began DELTA_TIME LARGEINT Duration of the measurement in microseconds SEGMENT_NAME CHARACTER(8) Neoview segment name PROCESS_NAME CHARACTER(8) Name of the process bei
Table 3-8 PROCESS_STATS_V1 Field Definitions (continued) Field Name Data Type Description EXT_SEGS_QUE_TIME LARGEINT Time, in microseconds, that extended segments were allocated to the process RECV_QUE_TIME LARGEINT Time, in microseconds, that messages spent waiting on a process message input queue MESSAGES_SENT LARGEINT Number of messages sent by the process (associated with I/O operations and some system procedure calls) SENT_BYTES LARGEINT Number of bytes sent by the process RETURNED_BYTES
In the case of an NDCS server process or a Neoview SQL compiler process, statistics are not really aggregates because each record represents only one process. In this view, all CHARACTER columns use ISO88591 encoding, regardless of the underlying Neoview platform configuration.
Table 3-9 PROCESS_AGGR_LEVEL1_STATS_V1 Field Definitions (continued) 48 Field Name Data Type Description AVG_PRIORITY INTEGER UNSIGNED Average creation priority all processes described by this record MAX_PRIORITY INTEGER UNSIGNED Maximum creation priority for all processes described by this record CHILD_COUNT INTEGER UNSIGNED Number of processes included in this aggregation TOTAL_CHILD_ELAPSED_TIME LARGEINT Sum of elapsed times, in microseconds, between the end of the last aggregation period
Table 3-9 PROCESS_AGGR_LEVEL1_STATS_V1 Field Definitions (continued) Field Name Data Type Description BUSY_HISTOGRAM_75_95 LARGEINT Number of processes with a CPU busy percentage between 75 and 95 BUSY_HISTOGRAM_95 LARGEINT Number of processes with a CPU busy percentage greater than 95 AVG_HISTOGRAM_1 LARGEINT Average percent busy of all processes with a CPU busy percentage less than 1 AVG_HISTOGRAM_1_25 LARGEINT Average percent busy of all processes with a CPU busy percentage between 1 and 25
Table 3-9 PROCESS_AGGR_LEVEL1_STATS_V1 Field Definitions (continued) 50 Field Name Data Type Description STDDEV _HISTOGRAM_75_95 LARGEINT Standard deviation of percent busy for processes with a CPU busy percentage between 75 and 95 STDDEV _HISTOGRAM_95 LARGEINT Standard deviation of percent busy for processes with a CPU busy percentage greater than 95 TOTAL_CHILD_READY_TIME LARGEINT Sum of ready time, in microseconds, for all aggregated processes CHILD_SYSTEM_ READY _PCT DECIMAL(6,2) Ready t
Table 3-9 PROCESS_AGGR_LEVEL1_STATS_V1 Field Definitions (continued) Field Name Data Type Description MIN_NUMBER_OF_PAGE_FAULTS INTEGER UNSIGNED Among processes that had page faults, the smallest number of page faults AVG_NUMBER_OF_ PAGE_FAULTS INTEGER UNSIGNED Among processes that had page faults, the average number of page faults MAX_NUMBER_OF_ PAGE_FAULTS INTEGER UNSIGNED Among processes that had page faults, the maximum number of page faults NUMBER_OF_CHILDREN_W_FILE_OPEN_CALLS INTEGER UNSI
Table Statistics The Repository includes two views that provide access to data about tables or materialized views whose histogram statistics are missing or out of date. The views have identical column definitions but satisfy different purposes: • TABLE_STATS_V2 provides the most recently collected data for each monitored object. Thus, the view includes only one row per monitored table or materialized view.
Table 3-10 TABLE_STATS_V2 and TABLE_STATS_DETAIL_V2 Field Definitions (continued) Field Name Data Type Description CATALOG_NAME CHAR(128) or NCHAR(128) Name of the catalog in which the object (e.g., the table) is stored SCHEMA_NAME CHAR(128) or NCHAR(128) Name of applicable schema for the object (e.g., the table). OBJECT_NAME CHAR(128) or NCHAR(128) Name of the table, index, or materialized view.
Table 3-10 TABLE_STATS_V2 and TABLE_STATS_DETAIL_V2 Field Definitions (continued) Field Name Data Type Description UPDATE_STATS_RUNNING_TS TIMESTAMP(6) If update statistics is currently running, start time for that operation, in Local Civil Time. LAST_REORG_TS TIMESTAMP(6) Time at which the last reorganization of the table occurred. REORG_STATUS CHAR(11) or NCHAR(11) COMPLETED, IN_PROGRESS, or NONE MISSING_STATS_COUNT INTEGER Total count of missing statistics events for this object.
Table 3-11 DISK_STATS_V1 Field Definitions (continued) Field Name Data Type Description PIN SMALLINT UNSIGNED Process identification number of the disk process for this volume SEGMENT_NAME CHAR(8) Neoview platform segment name SAMPLE_ELAPSED_TIME INTERVAL SECOND(12,6) Duration of the sample interval FULL_PCT DECIMAL(6,1) Percentage of disk space currently in use AVAILABLE_SPACE DECIMAL(12,1) Available disk space, in megabytes CAPACITY INTEGER Total capacity of this volume, in megabytes
Table 3-11 DISK_STATS_V1 Field Definitions (continued) Field Name Data Type Description BLOCKED_REQS_PER_SEC SMALLINT UNSIGNED Number of disk requests blocked per second for this interval PAGE_SWAPS_PER_SEC DECIMAL(9,1) Number of disk page swaps per second for this interval STATUS_TEXT CHAR(12) Availability or performance status of the disk, reflecting objectives defined in the Neoview Management Dashboard configuration:: 'Exists' 'Up' 'Low' 'Medium' 'High' 'Warning' 'Critical' 'Down' 'Questionab
Table 3-11 DISK_STATS_V1 Field Definitions (continued) Field Name Data Type Description READS_PER_SEC_MIR DECIMAL(9,1) Number of disk process read operations per second for this interval, for the mirror disk WRITES_PER_SEC_MIR DECIMAL(9,1) Number of disk process write operations per second for this interval, for the mirror disk OPS_PER_SEC_MIR DECIMAL(9,1) Total number of disk process reads, writes, and seeks per second for this interval, for the mirror disk BUSY_PCT_MIR DECIMAL(6,1) Percentag
Table 3-12 Field Name Mapping from ACCESS_ODBCMX_QUERY_STATS_V1 to ODBC_QUERY_STATS_V1 (continued) Field Name in ACCESS_ODBCMX_QUERY_STATS_V1 Field Name in ODBC_QUERY_STATS_V1 LOCKWAITS LOCK_WAITS MSGSBYTESTODISC MESSAGE_BYTES_TO_DISK MSGSTODISC MESSAGES_TO_DISK ODBC_ELAPSED_TIME QUERY_ELAPSED_TIME ODBC_EXECUTION_TIME MASTER_EXEC_EXECUTION_TIME TOTAL_ODBC_ELAPSED_TIME Column Removed - Duplicate of QUERY_ELAPSED_TIME TOTAL_ODBC_EXECUTION_TIME Column Removed - Duplicate of MASTER_EXEC_EXECUTION
Table 3-14 Field Name Mapping from ACCESS_ODBCMX_SESSION_DATA_V1 to ODBC_SESSION_STATS_V1.
Table 3-16 Changes in Field Definitions for QUERY_RUNTIME_STATS_V1 from Repository 2.2 to Repository 2.3 (continued) Field Name in Repository 2.2 Change in Repository 2.3 MASTER_EXECUTOR_BUSY_TIME Value is now precise to the microsecond, or NULL if data is unavailable. COMPILE_ELAPSED_TIME Value is now precise to the microsecond, or NULL if data is unavailable. Value is updated when the entry is refreshed. NUM_ROWS_UID Now NULL for queries in which rows cannot be updated, inserted, or deleted, e.g.
4 Examples and Guidelines for Creating Repository Queries Overview This section lists some simple queries against the available Repository views and provides a few suggestions for writing Repository queries. NOTE: More realistic examples of Repository queries are included in the Neoview Reports product. In general, each row in a Repository view includes two kinds of data: • • Observations or statistics pertaining to a query, session, or other object.
The list following the query shows values that might be returned in a single record. select [first 10] * from NEO.HP_METRICS.ODBC_QUERY_STATS_V2 where STATEMENT_STATE = ’COMPLETE’ ORDER BY QUERY_START_DATETIME DESC FOR READ UNCOMMITTED ACCESS; Field Value “SEGMENT_NAME” \NEO0102 "SEGMENT_ID" 2 "NODE_ID" 1 "PIN" 653 "START_PRIORITY" 148 “PROCESS_NAME” \NEO0102.$Z2YK "SESSION_ID" "MXID01001000882212041585284557791000000000509SUPER.SERVICES00" "USER_ID" "511 " "USER_NAME" "ROLE.
Field Value "QUERY_ELAPSED_TIME" 2325 "MASTER_EXEC_EXECUTION_TIME" 16-7 "DISK_READS" 0 "LOCK_ESCALATIONS" 0 "LOCK_WAITS" 0 "MESSAGE_BYTES_TO_DISK" 177744 "MESSAGES_TO_DISK" 18 "ROWS_ACCESSED" 20 "ROWS_RETRIEVED" 20 "NUM_ROWS_IUD" NULL "TOTAL_EXECUTES" 1 "SQL_TEXT" "select AVG(CPU) CPU, AVG(MEMORY) MEMORY,…" Retrieve Statistics for Incomplete Queries This query lists information about queries that have started but not ended.
Field Value "STATEMENT_ID" "SQL_CUR_26680060 " "STATEMENT_TYPE" "SQL_SELECT-NON_UNIQUE" "STATEMENT_STATE" "INCOMPLETE" "QUERY_TEXT_IS_MULTIROW" "N" "ERROR_CODE" “” "SEQUENCE_NUM" 0 “SEQUENCE_SIZE” 0 "QUERY_START_DATETIME" 2007-05-23 22:30:05.867082 "QUERY_START_DATE" 2007-05-23 "QUERY_START_TIME" 22:30:05 "QUERY_START_DATETIME_UTC" 2007-05-24 05:30:05.
this example, an ODBC session that executed a single query saturated the system from approximately 19:25:00 to 19:35:00 and then was inactive. reset param set param ?segment_id 1; set param ?node_id 0; set param ?pin 863; show param; select segment_id, node_id, pin, sample_date, sample_time metric_type, child_count, child_system_busy_pct as sys_busy from NEO.HP_METRICS.
AND AND GROUP FOR QUERY_START_DATETIME >= DATE_TRUNC('DAY', CURRENT - INTERVAL '1' DAY) STATEMENT_STATE = 'COMPLETE' BY 1 READ UNCOMMITTED ACCESS; START_DATE NUM_OF_QUERIES MAX_START_TIME MIN_START_TIME 2007–05–23 479 12:48:46.879774 00:23:59 2007–05–22 49 18:44:05.390777 13:15:14 Queries within the Past 24 Hours, Grouped by Client ID This example indicates how many queries different clients ran within a 24-hour period. SELECT client_id, COUNT(*) AS NUM_OF_QUERIES FROM NEO.HP_METRICS.
AND QUERY_START_DATETIME >= CURRENT - INTERVAL '1' DAY STATEMENT_STATE = 'COMPLETE' BY client_id BY client_id READ UNCOMMITTED ACCESS; AND GROUP ORDER FOR CLIENT_ID NUM_OF_QUERIES AVG_QRY_ELAPSED_MSEC MAX_QRY_ELAPSED_TIME EXPTC082205 16 17330 32750 ORDROBERTSC-P2 51 20340 728870 ORDHROTHGAR-P2 162 260 2070 ordbantam-p2 28 316730 5646470 Formatting Techniques to Enhance Readability of Query Output Often the typical data in a column is much narrower than the maximum defined column size.
rstein-xp TDM_Default_DataSource saxonyw-dc76c TDM_Default_DataSource tanakas.fargo.c TDM_Default_DataSource darius-d530 smart_msaccess REVSTADTBR 31 102 825 22 98 Minor formatting of the columns can yield a more readable result: select [first 10] left (datasource, 25) as datasource1, client_id, count(*) as query_count from NEO.HP_METRICS.
This next query assumes that you know the specific QUERY_ID of interest: SELECT [first 10] left(query_id,95) as query_id1, query_start_date, query_start_time, sequence_num as seq_num, sequence_size as seq_size, statement_state, query_text_is_multirow as multi_row, left(sql_text,50) as sql_text1 FROM NEO.HP_METRICS.ODBC_QUERY_STATS_V2 WHERE QUERY_ID = 'MXID01001000969212041869954468496000000000214SUPER.
Glossary Collector process A Repository process that collects metrics for a monitored entity. Execdirect A SQL statement or call that executes a query directly, as opposed to using a previously prepared statement. Manageability Repository A database and related components for collecting metric data about resources on a Neoview platform and making that data available to consumers, by means of APIs.
Index separate rows for start and end of query, 22 single row per query, 25 views that expose, 21 A Aggregate statistics for processes, 46 C R CPU statistics (see Processing node statistics) Repository features and interfaces, 15 Retention times for Repository data, 19 RTS defined, 71 D Default configuration, 19 Disk statistics, 54 Documents, related information, 12 E Example query count completed queries, 65 number of rows in a view, 61 retrieve a specified number of rows, 61 statistics for incomple
