Performance monitoring 2
Performance Monitoring, Part 2
Roger Sanders
Laughing paste
Paradise bird free space original work
Paradise Bird Free Space © 2002-2005 Copyright
Reprint, please keep the integrity of the document
Access more you can view http://hbird.vicp.net/myself.html
Or http://hbird.myrice.com/myself.html
BLOG: http://blog.9cbs.net/mr_bean
BBS discussion: http://hbird.vicp.net
Mail: jackey.wu@163.com
Performance monitoring 2
Roger Sanders
Laughing paste
Original source: "DB2 Magazine" Quarter 3, 2004 Vol. 9, Issue 3
English original (due to unauthorized article translation, please retain the original link when reprint)
Event monitors beyond performance clue snapshots.
In the first one of this series, I pointed out that the performance monitoring tool of DB2 tends to have an organization and has a target-oriented adjustment result. Simply put, they can help you determine the symptoms of performance issues and give you some improved methods.
You may still remember that the database system monitor consists of two different monitoring tools: a snapshot monitor and one or more event monitors. In the previous section, I detail the snapshot monitor and how it is used to capture the current status information on the instance or database in a predetermined point in time. In this chapter, I will tell how event monitors are used to capture monitor data that cannot be grasped by snapshot monitors.
Event monitor
Event monitor collects monitor data such as a specific event or transaction. Therefore, the event monitor provides a method of collecting database system monitor data when the event or activity cannot be used to monitor monitoring.
For example, suppose you want to capture monitor data whenever the deadlock cycle occurs. If you are more familiar with the concept of deadlocks, you should know that a special process called a deadlock listener runs quietly in the background and will "wake up" in a predetermined interval for a deadlock cycle scanning. Locked system. If the deadlock cycle is found, the deadlock listener will randomly select, roll back and terminate any of the transactions in this cycle. As a result, the selected transaction will accept a SQL error code, and all the released transactions that have been released to facilitate the implementation of the remaining transactions. A series of event information cannot be captured by the snapshot monitor, which may be due to the deadlock cycle may have been destroyed before the snapshot is executed for a long time. The event monitor can then capture important information for the event because it can be activated instantly at the deadlock cycle.
Another significant difference between the two monitors is that the snapshot monitor resides in the future process, start capturing monitor data from a database connection; for the purposes, event monitors must be established before they use And activate. Several different event monitors can coexist, and each event monitors are only activated when specific type events or transactions occur. Table 1 shows some of the event types that can cause event monitors, and the types of monitor data collected by each event type.
Table 1 Event type and their corresponding data
Since the event monitor is a special database object, it must be created before use, which can only collected monitor data that occurred in the database or transaction in the database they defined. You cannot use event monitors in instance levels to collect monitor data.
Create event monitor
You can create event monitors directly in the Control Center (select Create Event Monitor from the Event Monitor menu), or create it by performing the SQL statement of Create Event Monitor, its basic syntax is as follows:
CREATE EVENT MONITOR [Name] FOR [DATABASE | BUFFERPOOLS | TABLESPACES | TABLES | DEADLOCKS
Name is a name EVENTCONDITION that is assigned to this event monitor to determine which connection, statement, or transaction-collecting data for the condition GroupName determines the logical data group on the defined target table (using this parameter) Available values can be found in Table 1) Tablename determines that the name of the database table that is assigned to all event monitors is determined to assign the name DirectoryName to all event monitors to write the named pipe. DirectoryName determines one or more files that contain event monitors. Name of the write directory
Note: The parameters that appear in the spare brackets are optional; parameters in square brackets or options are required. Check the full syntax of the Create Event Monitor SQL statement, see IBM DB2 Universal Database, Version 8 SQL Reference Volume 2 documentation.
We assume that if you want to create a value about capturing all the applications and write them in a database table named conn_data when the application aborts the database. You can perform the following Create Event Monitor statement to complete it.
Create Event Monitor Conn_EventsFor ConnectionsWrite To Table
Conn
(Table CONN_DATA)
Now we have to create a monitor data used to capture buffer pools and tablespace events and write all collected data to event monitors called / export / home / bPTS_DATA directory. You can perform the following Create Event Monitor statement to complete it.
Create Event Monitor BPTS_EventsFor BufferPools, TableSpacesWrite To File '/ Export / Home / BPTS_DATA'
As you can see, when you create an event monitor, you have to specify the type of event that activates the monitor and all the collected data writes.
The output of an event monitor can be written to one or more database tables, external files, or a naming pipe. The event monitor flow events of the table and the pipeline type are directly recorded to the specified table or in the named pipe. On the other hand, the file-type event monitor flow event records a series of 8-bit encoded files with a .evt extension (for example, 00000000.evT, 00000001.evt, etc.). Data stored in these files may be processed into a separate file similar to a separate data stream. Although these data are actually split into many small data blocks. (The data stream begins on the first byte of the 00000000.evt file, ending the last byte of the last file created)
If you indicate that the output of the event monitor is stored inside the database table, then all target tables will be automatically created when the CREATE EVENT MONITOR statement is executed. (If you create a table fails because some reasons are created, then an error code is also failed at the CREATE EVENT MONITOR statement, however, if you specify the output of the event monitor, you write one or more external files, or a named pipe At this time, this output directory or named pipe must exist in advance and when the event monitor is activated, the DB2 database manager instance itself must be able to write it. Also, if you are using a named pipe, the named pipe of the application monitors must run while it must also open the pipe to read information before the event monitor is activated. Start and stop event monitors
If you specify an AutoStart option when you create an event monitor, the monitor will start automatically at the beginning of the database that contains its database. (The database begins with the Activate Database command to activate or the first time with the database connection.) If you use ManualStart or unknown options (here, ManualStart is default), its result is the event monitor Monitor data is not collected until it starts to act. The event monitor can begin (stop) by performing the Set Event Monitor SQL statement. Its basic syntax is as follows:
Set Event Monitor [MonitorName] State <=> [MonitorState]
Description:
MonitorName indicates that the name of the event monitor that needs to be changed states monitorState indicates the status of the event monitor that needs to modify the state. If you want to start the event monitor (in other words, it is to activate it), you have to set it to 1. Conversely, it needs to be set to 0.
Now we assume that you want to start a event monitor that is CONN_EVENTS, which uses the ManualStart option when it is created. You can do this:
Set Event Monitor Conn_Events State 1
If you want to get the same result with the above, that is, stop the conn_events event monitor, you can perform this sentence:
Set Event Monitor CONN_EVENTS State 0
At the same time, you can also start and stop the event monitor by selecting and selecting the action in the Control Center's Event Monitor menu.
Once the event monitor starts running, it will wait for the corresponding event or transaction to be monitored for his design in the background. When this happens, the event monitor collects the appropriate monitor data information and writes them into the monitor's output object (table, directory, or named pipe). At this time, these steps will be controlled by events or transaction itself; the database administrator does not need to perform any additional steps to collect event monitor data, which is different from the snapshot monitor when used.
Force output
At some time, low-record execution frequency event monitors (such as monitors designed to monitor database events) store event monitor data in memory without being stored on the target space of the event monitor (because only this is only Is some event record existence). If you want to check the content inside the event monitor's active internal cache, you can execute the Flush Event Monitor SQL statement. The syntax of this statement is Flush Event Monitor [MonitorName]
Force the contents of the CONN_EVENTS event monitor active internal cache to the corresponding space, you can perform the Flush Event Monitor Conn_Events statement. By default, those records that have been written to the event monitor target space are recorded in the event monitor log and have a partial record information. However, if you specify the buffer option in the process of performing the Flush Event Monitor, only the monitor data that appears in the event monitor activity is written to the target space of the event monitor, at the same time in the event monitors There is no part of the log in the log being recorded. When the event monitor is cleared, the counter does not reset. As a result, the event monitor that has not been generated without using the FLUSH EVENT MONITOR statement is still generated when the event monitor is triggered normally.
Event monitor data
Sometimes you want to see the data collected by the event monitor. If these collected data are written to a named pipe, the application that is responsible for the end of the pipeline is usually used as the corresponding form in which the monitor data is displayed when it accepts data. If the event monitor writes the data into a table or a series of files, you can view data by a special tool named Event Analyzer or use the Event Monitor Productivity Tool.
Event Analyzer is a GUI tool that can be activated by selecting the event monitor you want and from the Event Monitor menu in the Control Center or by performing the DB2EVA command. Figure 1 shows a typical example of the event analyzer in its first activation.
Figure 1 Time Analysis Tool
Once it is activated, the event analyzer allows you to dig and browse the information captured by the detailed event monitor.
Event Analyzers can only be used to see those event monitor data that are collected and stored in the database table. If you want to see the monitor data written to a file or named a duct, you will use the text-based Event Monitor Productivity Tool, which can charge data from an event monitor data file or named pipe and process these data into one Formatted report. (Event monitor file and named pipe containing a binary logical data group that must be formatted before display)
An Event Monitor Productivity Tool can be activated through the db2evmon command. The basic syntax of this command is
DB2EVMON -DB [DatabaseAlias] -EVM [MonitorName] or dB2EVMON -PATH [MonitorTarget]
Description:
DatabaseAlias indicates that the alias MonitorName of the database to define the event monitor indicates that the name of the event monitor that needs to display the data is indicated by the Monitortarget that has been specified (directory or named pipe).
As an example, in order to format and output all the collected data defined in the Sample database, you can get the DB2EVMON -DB Sample-Evm Conn_Events command.
For example, suppose you want to create a CONN_EVENTS event monitor using the following statement
Create Event Monitor Conn_EventsFor ConnectionsWrite To File 'C: / Mondata'Autostart
Suppose an application has established a connection with the Sample database (enabling event monitors to collect and record monitoring data). The DB2EVMON -DB SAMPLE-EVM CONN_EVENTS command returns the sample output in the file header table 1 in the output information.
Table 1 Output sample of event monitor conn_events
Reading c: /dbsio/00000000.evt ...-------------------------------------- --------------------------------- Event log header event monitor name: conn_events server product ID: SQL08015 Version of Event Monitor Data: 7 Byte Order: Little Endian Number of Nodes in DB2 Instance: 1 CodePage of Database: 1252 Territory Code of Database: 1 Server Instance Name: DB2 ------------------ -------------------------------------------------- -------------------------------------------------- ------------------------ Database Name: Sample Database Path: C: 03-24-2004 16 : 53: 00.020233 Event Monitor Start Time: 03-24-2004 16: 53: 00.155733 ------------------------------- ---------------------------------------- 3) Connection Header Event ... Appl Handle : 16 Appl ID: * local.db2.0120c
4215303 Appl Seq Number: 0001 DRDA AS CORRELATION TOKEN: * Local.db
2.0120C
4215303 Program Name: db2evmon.exe Authorization Id: RSANDERS Execution Id: RSANDERS Codepage Id: 1252 Territory code: 0 Client Process Id: 1788 Client Database Alias: SAMPLE Client Product Id: SQL08015 Client Platform: Unknown Client Communication Protocol: Local Client Network Name : Connect TimeStamp: 03-24-2004 16: 53: 00.0202334) Connection Event Appl Handle: 16 Appl ID: * Local.db
2.0120C
4215303 Appl Seq number: 0003 Record is the result of a flush: FALSEApplication Status: SQLM_UOWWAIT Lock Statistics: Lock Waits: 0 Total time waited on locks (milliseconds): 0 Deadlocks: 0 Lock escalations: 0 X lock escalations: 0 Lock timeouts: 0 Sort Statistics: Sorts: 0 Total sort time (milliseconds): 0 Sort overflows: 0 Hash Statistics: Hash Joins: 0 Hash Loops: 0 Hash Join Small overflows: 0 Hash Join overflows: 0 Buffer Pool Statistics: Buffer pool logical data page reads: 12 Buffer pool physical data page reads: 4 Buffer pool data page writes: 0 Buffer pool logical index page reads: 30 Buffer pool physical index page reads: 18 Buffer pool index page writes: 0 Buffer pool read time (microseconds): 0 Buffer pool write time (microseconds): 0 Time spent waiting for a prefetch: 0 milliseconds Unread prefetch pages: 0 Workspace Statistics: Shared workspace high water mark: 0 Total shared workspace overflows: 0 Total shared workspace section lookups: 0 Total shared workspace section inserts: 0 Private workspace high water mark: 13746 Total private workspace overflows: 0 Total private workspace section lookups: 2 Total private workspace section inserts: 2 Direct I / O Statistics: Sectors read directly: 14 Sectors written directly: 0 Direct read requests: 5 Direct write requests: 0 Direct read time: 0 Direct write time: 0 SQL Statement counts Commit SQL statements: 1 Rollback SQL statements: 0 Dynamic SQL statements: 1 Static SQL stmts: 3 Failed SQL statements: 0 Select SQL Statements: 2 Data Definition Language SQL Statements: 0 Update / Insert / Delete SQL Statements: 0 Internal Count Rows Deleded: 0 Internal Rows Updated:
0 Internal rows inserted: 0 Internal commits: 0 Internal rollbacks: 0 Internal rollbacks due to deadlock: 0 Row counts Rows deleted: 0 Rows inserted: 0 Rows updated: 0 Rows selected: 2 Rows read: 6 Rows written: 0 Binds / Precompiles : 0 Rejected block cursor requests: 0 Accepted block cursor requests: 0 Package Cache Statistics Package Cache Lookups: 3 Package Cache Inserts: 2 Section Lookups: 3 Section Inserts: 2 Catalog Cache Statistics Catalog Cache Overflows: 0 Catalog Cache High Water Mark: 0 Catalog Cache Lookups: 2 Catalog Cache Inserts: 0 CPU times User CPU time: 0.000000 seconds System CPU time: 0.000000 seconds Memory usage: Memory Pool Type: Other Memory Current size (bytes): 16384 High water mark (bytes): 98304 Maximum size Allowed (bytes): 107164672 Memory pool Type: Application Heap Current Size (bytes): 212992 Maximum Size ALLOWED (BYTES): 1277952 Memory Pool Type: Application Control Heap Current Size (Bytes): 16384 High Water Mark (Bytes): 16384 Maximum Size ALLOWED (BYTES): 704512 Disconnection Time: 03-24-2004 16: 53: 00.191692 Next
Snapshot Monitor and Event Monitor are designed to help you identify and correct performance issues that have affected database systems. DB2 UDB V.8.1 also provides two additional tools to help you find its precursor before they become true problems. This is the subject you want to say in the next chapter.
Full version (PDF file with pictures) please download at http://hbird.vicp.net/viewthread.php?tid=1486
