Purpose
Sets the global and local rates for updating session-level statistics in memory.
Input Data
Element | Data Type | Description |
---|---|---|
IndByte | BYTE | Indicator bits that
specify which fields to treat as NULL if you are using indicator
mode. Each bit in the byte corresponds to one field in the input data. If data is supplied for that field, set the bit to zero. If the data for that field is NULL (that is, there is no data supplied for that field), set the bit to 1. The IndByte field is only required if the CLIv2 request is submitted in indicator mode.
|
mon_ver_id | SMALLINT NOT NULL |
MONITOR software version
ID. This can be version 2 or later. For a general explanation of monitor version choices, see MONITOR VERSION. |
sample_rate | SMALLINT NOT NULL range 1-3600 seconds |
Value of the collection
interval. If the global sample rate is set to
zero an error is returned.
|
local_change | VARCHAR (1) | Type of session to which
this rate change applies. Specify Y or y if the rate is set within a local session or N, n, NULL, or blank if the rate applies to global queries. |
Monitor Privileges
To use this request, you must have the SETSESSRATE privilege as part of your default role or this privilege must be granted directly to you.
If you make changes to either the system or local rate, this can reset the starting point at which data is collected. Therefore, Teradata recommends that the SETSESSRATE privilege be granted to a restricted number of users (such as DBAs or certain application programmers)
- Teradata Vantage™ - Database Administration, B035-1093
- Teradata Vantage™ NewSQL Engine Security Administration, B035-1100
- Teradata JDBC Driver Reference, available at https://developer.teradata.com/connectivity/reference/jdbc-driver
Usage Notes - SET SESSION RATE
This request is recorded in the DBC.SW_Event_Log table (accessible from the DBC.Software_Event_LogV view) and also written to the DBW. Global rate updates are recorded only in the GDO control record.
Session usage data is placed in a separate, independent memory repository from the processor resource data. As a result, any changes in the session monitoring rate have no impact on the resource data. However, a user who sets a local collection rate could affect the session usage data that other users see because all session usage data is stored in the same memory repository.
Session-level usage data is collected differently from the way processor resource usage data is collected. Processor resource usage data is collected for a particular collection period, while session usage data is collected cumulatively. The report can include cumulative data over many collection periods.
Monitoring a system may cause some form of global performance degradation. To limit the overhead cost, Teradata recommends that you set the global rate at 3600 seconds (or 1 hour) for general system monitoring. To perform problem analysis more quickly, you can set a more frequent local rate. After the analysis is done, terminate the session or set the local rate to zero. The lower global rate takes effect again in that session.
- The user application program sees duplicated data and CPU resources are wasted.
- The user application program sees data showing the result of an alteration caused by another user.
It is also recommended that you restrict the SET SESSION RATE privilege to the database administrator or system administrator.
- Someone else is changing the global rate at the same time.
Rate change requests are queued and processed in the order received because the system does not allow different rates to be used on different processors. The system ensures that all processors use the same rate.
- The GDO control record is being updated.
Rate Scenario 1: Effects on Other Users When Increasing the Global Rate
User A has the SET SESSION RATE privilege. User B does not.
Step | Action |
---|---|
1 | User A changes the global rate from 10 minutes to 5 minutes at 8:55. User A now requests data every 5 minutes instead of 10 minutes. |
2 | User A makes a request at 8:57. Data returned shows data from the interval of 8:50 to 8:57, plus the cumulative data from 8:00 to 8:50. By forcing an update of the data at 8:57, User A has reset the collection start time (or time-stamp) to 8:57 because the current time (8:57) minus the time-stamp of the data (8:50) is greater than the session collection rate of 5 minutes. Resetting the collection start time means resetting the time from which the interval is calculated to 8:57. |
3 | User A makes a next request at 9:00. Data returned is the same data that was returned at 8:57. The reason is that the current session-level data that was available was considered current, that is, the age of the data (9:00 - 8:57 = 3) is less than the session collection rate of 5. |
If User B is not aware that the global rate changed to 5 minutes, the application for User B continues to request data every 10 minutes. User B is requesting data at a less frequent rate than the data collection rate. User B may not notice a difference in data, since the data is cumulative. However, since all session resource data is deposited in the same memory pool, a change in collection rate by another user could affect the data User B sees.
Although it is transparent to the user, the user data impacted depends on which user request is received first. This usually happens when the user is not requesting data at the same rate as data is collected.
For example, User A makes a request at 9:00 and the request made by User A is received before the request (also at 9:00) made by User B. As described previously, User A sees data returned that is cumulative from 8:00 to 8:57.
Step | Action |
---|---|
1 | The request made by User B at 9:00 is queued. User B gets the cumulative data from 8:00 to 8:57 because User A made a request at 8:57. The request made by User B does not trigger an update because the request made by User A resets the time-stamp (or start time) to 8:57, and the elapsed time between 8:57 to 9:00 is less than 5 minutes (the data collection rate of the session). Thus, User B gets data from 8:50 to 8:57, plus the cumulative data from 8:00 to 8:50. In this case, there is no difference in the data User A or User B sees as a result of the request order. |
2 | User B gets a warning message that the rate changed to 5 minutes. User B can check to see what the new global rate is if MONITOR SESSION is being run. The data returned in the first Record parcel is the SampleSec, which indicates the duration of the collection period in seconds. User B can observe that SampleSec reports 300 seconds (or 5 minutes) instead of 600 seconds (or 10 minutes). |
Rate Scenario 2: Effects On Other Users When Increasing the Local Rate
Users A and B are requesting session-level data at the same rate as data is collected.
Step | Action |
---|---|
1 | User A wants to troubleshoot a new application program and at 9:00 sets a local rate of 1 minute within his session. The local rate now overrides the global rate of 10 minutes within the session for User A. User A now requests data every 1 minute instead of 10 minutes. |
2 | User A makes a request at 9:01. Data returned shows data from the interval of 9:00 to 9:01 (because User A has reset the session beginning collection time to 9:00), plus the cumulative data from 8:00 to 9:00. |
3 | User A makes the next request at 9:02. Data returned shows data from the interval of 9:01 to 9:02, plus the cumulative data from 8:00 to 9:01. From now on, responses to queries for User A returns data updated every 1 minute. |
The actions made by User A in setting a local rate does not change the current global collection rate of 10 minutes.
The way the session for User B is calculating its collection interval does not change. Therefore, the collection rate and request rate for User B continues at 10-minute intervals. However, since all session usage data is stored in the same memory repository, the more frequent collection for User A may affect the cumulative data that User B might see.
User B may see more dynamic data than previously and receive more data than expected. Each of the requests made by User B could potentially include data from one collection period mixed in with data from a subsequent collection period that was initiated by a different use. In this case, the 1-minute interval collection for User A.
While the local rate for User A is in effect, User B makes a request at 9:00. Data returned shows data from the interval of 8:50 to 9:00, plus the cumulative data from 8:00 to 8:50.
If User B were to make a request at 9:04, User B would see data returned from 9:00 to 9:04, plus the cumulative data from 8:00 to 9:00. Because of the local data collecting for User A, User B can take advantage of the faster collection rate and thus receive more data than expected.
On the other hand, if the local rate for User A were not in effect and User B makes a request at 9:04, the data returned would be from the interval of 8:00 to 9:00 only.
Rate Scenario 3: How Request Order Affects Data Reported
The DBA sets the global rate at 8:00 a.m. to collect every 10 minutes. User A decides to reset the global or global rate from 10 minutes to 5 minutes. In addition, User B decides to request session-level data at a different rate than the data collection rate.
Step | Action |
---|---|
1 | User A resets the global session collection rate to 300 seconds (or 5 minutes) at 9:00, thus setting the timestamp for any current data to 9:00. |
2 | At 9:00, User A also sets a local collection rate of 1 minute for his session. |
3 | User B makes a request for data at 9:05:25. |
4 | User B receives the cumulative data from 8:00 to 9:05:25 and, in the process, resets the data time-stamp to 9:05:25. |
User A does not know that the data timestamp is altered by the request made by User B because User A had sent in a request immediately after the request made by User B.
The request made by User A for data at 9:06:00 is queued behind the request made by User B of 9:05:25. Because the current data is considered current (9:06:00 - 9:05:25 = 0:00:35, which is less than the local collection rate of 1 minute), User A receives the same data (from 8:00 to 9:05:25) that User B received.
In this case, the request order affects the data User A receives. If not the earlier request made by User B, User A could have received data from the 8:00 to 9:06 interval, which User A probably expected.
Rate Scenario 4: Different Collection and Request Rates
Users A and B are running different application programs and are monitoring the applications with MONITOR SESSION and/or MONITOR VIRTUAL SUMMARY. User A has the SET SESSION RATE privilege. User A is requesting session-level data at a different rate from the rate at which data is collected.
User A is using the global collection rate of 10 minutes set by the DBA. However, User A wants to troubleshoot a new program and decides to set the application program to request data every 6 seconds.
Clearly, User A is requesting data more frequently than the data collection rate. Every 6 seconds, User A sees a data display that will show the same data until the 10-minute interval has elapsed.
In this case, data returned from monitoring the session for User A would probably be more meaningful if User A had set the global collection rate to the same rate as the request rate.
CLIv2 Response Parcels
The response returned from the Teradata Database contains the following sequence of parcel type:
Parcel Sequence | Parcel Flavor | Length (Bytes) |
Comments/Key Parcel Body Fields |
---|---|---|---|
Success | 8 | 18 to 273 | Activity Count = Previous rate Activity Type = 83 (PCLSETSESSR) |
DataInfo | 71 | 6 to 64100 | This parcel is present if request was IndicReq parcel; depends on the data type. |
EndStatement | 11 | 6 | StatementNo: 2-byte integer |
EndRequest | 12 | 4 | None |
Sample Input - CLIv2 Request
This example shows how the parcels for a SET SESSION RATE request, built by CLIv2, look when sent to the Teradata Database server with a sample_rate of 6 and a local_change of y. In the example, the size of the response buffer is set at the maximum (64,000 bytes), although you can set it to any size. However, a minimum response size is 32,000 bytes.
Flavor | Length | Body | |||
---|---|---|---|---|---|
Num | Name | Bytes | Field | Value | |
0001 | Req | 20 | Request | SET SESSION RATE | |
0003 | Data | 9 | MonVerID SampleRate LocalChg |
2 6 Y |
|
0004 | Resp | 6 | BufferSize | 64000 |
Sample Input - Teradata JDBC Driver Request
For an example of how the PM/API request, built in Java, appears when sent to the Teradata Database server, see Teradata JDBC Driver Reference, available at https://developer.teradata.com/connectivity/reference/jdbc-driver .
Sample Output
With a sample_rate of 6 and a local_change of y, this example shows the values returned in character text format for the SET SESSION RATE request. Your application program may display returned values in a different format.
Success parcel: StatementNo: 1 ActivityCount: 0 ActivityType: 83 FieldCount: 0 DataInfo parcel: FieldCount: 0 EndStatement. EndRequest.
Relationship Between SET SESSION RATE and MONITOR SESSION
You must execute the SET SESSION RATE request to activate session-level data collection before you execute a MONITOR SESSION request. This means that either the global rate or local rate must be set to nonzero. If both rates are set to zero, an error message is returned.
For information on this PMPC PM/API request relationship, see Relationship Between MONITOR SESSION and SET SESSION RATE.
Relationship Between SET SESSION RATE and MONITOR PHYSICAL SUMMARY or MONITOR VIRTUAL SUMMARY
Changes to the session-level rates (global and local) specified by SET SESSION RATE are reported in the data returned by MONITOR PHYSICAL SUMMARY or MONITOR VIRTUAL SUMMARY.
As more session-level monitoring is done (by setting a faster SET SESSION RATE), the resulting overhead may increase the level of CPU usage (reported in MONITOR PHYSICAL SUMMARY or MONITOR VIRTUAL SUMMARY data) by your system. However, this may depend on the size of the rate change and the type of work done by other sessions.