Performance monitoring 1 - snapshot
Performance Monitoring, Part 1: It's A SNAP (Shot)
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.myrice.com/myself.html
Mailto: jackey.wu@163.com
Performance monitoring 1 - snapshot
Roger Sanders
Laughing paste
Original source: "DB2 Magazine" Quarter 2, 2004 Vol. 9, Issue 2
English original (due to unauthorized article translation, please retain the original link when reprint)
How do you know where is your focus of your adjustment? Still do a snapshot.
When you review my recent column, I also recommend that DBA must make a clear and detailed plan in the performance adjustment of performance adjustments; there must be a realistic measurable goal in mind. Otherwise, it will become a meaningless practice. When improving database performance, you must first clear where to be a bottleneck and have corresponding countermeasures. This is the land of the DB2 UDB series of performance monitoring tools.
From this column, I will tell you how to use these tools to determine where you should pay attention to the adjustment effect, and find whether to adjust the effective solution. In this part, I will introduce the tools that can be used, and one of them will be described in detail, and the snapshot monitors and switch parameters associated with it. I will introduce event monitors in the next column.
DB2 Database System Monitor
Although its name sounds like a single tool, in fact, the database system monitor is actually composed of two different types of monitoring tools, they are responsible for capturing and returning system information: Snapshot Monitor and one or more events Monitor. Snapshot Monitor will allow you to get a status image on a specified point in time. The event display acquires the monitor data when the specified time occurs and records them into the log file. The information collected by these two types is stored in the monitor element (sometimes considered data elements); each element is designed to store the information of the specified type. List of monitored monitor elements listed below:
The counter is used to retain a total value of the number of activities or events (for example, the total number of SQL statements that have executed in a database). The counter number is worth the growth of the life cycle throughout the monitor; and in many cases it may reset the counter monitor feature.
Gaug indicates the current value of a project (for example, the number of applications currently connected to the database.) Unlike the counter value, the value of Gauges can be high or low; they are in any of the measured points in real time Depending on the level of the database activity.
Watermarks indicates that an indicator can achieve the maximum or minimum value that can be achieved after the start of the start of the surveillance (for example, the maximum number of lines affected by the update operation).
Information elements provide details of all monitoring activities (for example, buffer pool name, database name, alias, detailed path, etc.).
Timestamp indicates a date and time that occurs or events (for example, the date and time of the first connection database established). The timestamp is seen as the second and subtle quantity of the second and subtle quantity starting from January 1, 1970.
The time element recording time is expected to perform costs of an activity or event (for example: the ordering operation time is expected). The value of the time element is manifested in the form of seconds and microseconds from the start of the activity or event. Some time elements can be reset.
The database system monitor uses some combinations of these elements to obtain monitoring data and provide several options for each of the data stores of each element. Both snapshots and event monitors can give you all the collected data in the file or database table, check it or use a custom program to process it.
The database system monitor returns the monitoring data to the client application by some of the described data flows. Using a snapshot monitoring application, you can call the appropriate API to get the snapshot information and process the returned data stream. Using event monitoring applications, you are preparing to receive data from file or named pipe in advance, activating the corresponding event monitor, and then processes the data stream as just received. Snapshot monitor
As mentioned, the snapshot display is designed to collect information of the DB2 UDB instances and some database status of a particular point in it controls. Snapshots are very useful for determining a status of a database system. With a fixed time interval, they can provide information about the scope of orientation and identify potential issues. Snapshot Monitoring By performing a get snapshot command in the command line processor (CLP) or call db2getsnapshot () and db2ggetsnapshot () (if using advanced programming languages other than C or C ) API by calling db2getsnapshot () and db2ggetsnapshot () from the application.
switch
Although snapshot monitor information helps identify the scope of the problem, collecting data often causes additional processing burden. For example, in order to calculate the time spent in which the SQL statement is performed, the DB2 database manager must perform the timestamp information before and after execution of the SQL statement. The cost of these system-level calls may be expensive. Other side effects are used in increasing memory: Snapshot monitor data is collected and stored in memory rather than in some specific tables or external files.
In order to help reduce the number of overloaded demand for collecting snapshot monitor data, DB2 recommends using controls that are collected new quantities and types of snapshot monitor switches.
Like other basic switches, each snapshot monitor has two states of opening and related states. When the snapshot monitor is turned on, some of the information of some of the monitor elements under this switch control is collected. In contrast. (Note: A certain number of monitoring information is not controlled by these switches, so this information will always be collected regardless of what the switch is set.) Table 1 lists the available snapshot monitor switch parameters and describes This switch can be collected by the type of information after the switch is started.
In addition to the timestamp default is open, all available snapshot displays are default.
Table 1 . Snapshot monitor switch parameters
View switch settings
To some extent, since the snapshot monitor switch controls the type and quantity of the information that is safer when a snapshot is opened, you should figure out which switches are open before starting your monitoring process and those that are turned off. of. The easiest way to get this information is to execute the Get Monitor Switches command in the CLP. In multi-partition database environments, its basic syntax is: GET MONITOR SWITCHES
Note: The parameters displayed by angle brackets (<>) are optional parameters, and parameters inside square brackets ([]) are must be.
Get and display the status of a snapshot monitor switch for a separate partition database, you can execute the Get Monitor Switches command.
It is assumed that the default settings are used, as shown in Listing 1.
Listing 1. Run the result of the Get Monitor Switches command.
As can be seen from the above, the status of this snapshot monitor switch is ON and other OFF. The exact date and time of this switch open is displayed later in this switch state.
Change switch parameters
After you know the current state of each available snapshot monitor switch, you want to change one or more of the switches in you before you start monitoring. You can change the settings of the snapshot monitor switch by changing the corresponding database manager configuration parameters (see Table 1) or calls the application-level DB2Monitorswitches () API function or executes the Update Monitor Switches command (which is restarted after the instance is restarted) The basic syntax of this command is still effective: update monitor switches using [[switchid] on | Off, ...]
SwitchID indicates a snapshot monitor switch that needs to be changed. (This parameter can be part of the following or all: BufferPool, Lock, Sort, Statement, Table, TimeStamp, UOW.)
To set the Lock and Sort Snapshot Monitor switch parameters to ON (instance level), you can execute the Update Monitor Switch Using Lock on Sort ON command.
retrieve data
When the database is activated or established with the database, the snapshot display automatically begins collecting monitor data. However, you must choose a snapshot before you can watch the collected data. (Snapshots look like the image of monitoring elements at that time at the time.) You can get snapshots by calling db2getsnapshot () API or execute the Get Snapshot command. Listing 2 indicates the basic syntax of this command, DatabaseAlias is used to explain the database alias that requires mobile phone snapshot monitor information.
Listing 2. The syntax of the get snapshot command.
Just want to get the locked snapshot information held by the application in the salary database, you can execute the Get Snapshot for Locks on Payroll command.
The working product output from this command is similar to the results in Listing 3 (it is important to note that this is just a very simple example. The monitoring data returned by the real monitor is usually much larger than this)
Listing 3. Use the sample of the snapshot output obtained by using the Get Snapshot for Locks command.
As you can see, using the get snapshot command (or DB2GetSnapshot () API) can be used to get several different types of monitoring data, including:
DB2 Database Manager instance data
The same instance controls database data for all active databases
Application data
Buffer pool activity data
Table space data
Table data
Lock data (information about all lock locks)
Dynamic SQL data (information about the SQL statement in the SQL statement cache).
It should be noted that there is a direct interaction between the snapshot monitor switch and the different types of monitoring data is collected when a snapshot is opened. If the snapshot of the monitoring element associated with it is selected if the snapshot display switch is turned off, the monitoring data does not return any information. (In the earlier example, the reason why is listed as "uncharged" information is that the Lock's snapshot monitor switch is set to turn off. If you do not get the lock information in a snapshot opening, then "keep the lock" The value will be 0 and the list of locked information will not be supplied.)
Use SQL to capture data
In the earlier versions of DB2 UDB, get the unique way to get the snapshot monitor is to perform the Get snapshot command or call it from the application to the API function. In DB2 UDB 8.1, you can also construct a query to collect snapshot data by reference to one of 20 available snapshot monitor table functions. Table 2 lists these functions and indicating the specific snapshot information they can acquire.
Table 2. Snapshot monitor's table functions
Use the following syntax will create a query that references non-digital library manager grade table functions:
Select * from table ([DBNAME], [Partitionname]) as [correlationname] here functionName is used to illustrate the table function of the snapshot monitor used; DBNAME indicates that it is necessary to need from the snapshot monitor from that database. Collect data; PartitionNum Description Requires data from the snapshot monitor from that database partition; CorrelationName is the name of the result data set generated by the query.
The syntax of the snapshot monitor table function query is constructed to construct a reference database manager level. Different: DBNAME parameters are not used.
If you want to get the snapshot monitor data of a current partition in a partition database environment, you can set the value of the PartitionNum parameter to -1; if you want to get the snapshot monitor data of all partitions, you can set it to -2. Similarly, if you want to get the snapshot information of the current connection database, you can set the DBNAME parameter into an null value, or use a pair of single quotes or use a CAST - for example: Cast (NULL AS CHAR)
If you want to use the table function snapshot_lock using the snapshot monitor to capture the snapshot information of the lock data of the currently connected database associated with the application, you can perform the following statement:
SELECT * from Table (Snapshot_lock (Cast (Null As Char), -1) AS LOCK_INFO
If we use the Payroll database (previous example) as the currently connected database, the information returned by the executive Get Snapshot for locks on payroll command will be similar to the previous very similar (Table 3).
Reset counter
Another monitor element is referred to as a counter that saves the accumulation of the number of specific activities or events occurring during a run. A typical count begins to open or connect to the database of the snapshot monitor (if the instance level monitor is used, the count begins to connect the application for the application to connect to the database connection under this instance). Once the count starts, he will continue to the appropriate snapshot display switch is turned off or until all database connections are terminated. However, but sometimes you can reset all the counters to zero if you don't change one or more snapshot monitor switching status and without terminating and rebuilding all current active databases. In this case, all the monitors of the monitor can return them by executing the reset monitor command. The basic syntax of this command is: RESET MONITOR ALL or RESET MONITOR for [Database | DB] [DatabaseAlias] [DatabaseAlias] refers to the name of the database that you want to return the snapshot monitor to zero.
If you want to reset all database snapshot monitor's counter under an instance, you can switch to this instance to execute the RESET MONITOR ALL command.
On the other hand, if you just want to reset the counter of the snapshot monitor associated with the Payroll database to 0, then you can do this - Execute the RESET MONITOR for Database PayRoll command.
Remember, you cannot use the Reset Monitor command to reset their counters to the special monitor group controlled by the snapshot monitor switch. Instead, you must turn off the appropriate snapshot monitor switch and then open or terminate and rebuild the database connection.
Next
The snapshot monitor is only one of the monitoring tools available for DB2 UDB, and at some time, the snapshot is not a good choice. In the next section, I will introduce when using event monitors to collect events or events that have no way to handle the snapshot monitors.
