SET SESSION RATE

Teradata® Database Application Programming Reference

brand
Software
prodname
Teradata Database
vrm_release
16.20
category
Programming Reference
featnum
B035-1090-162K

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)

For more information on roles and privileges, see:

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.

To avoid confusion, Teradata recommends that applications not query data more frequently than the faster of the two rates. If MONITOR SESSION requests are executed too frequently, one of the following may occur:
  • 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.

A change to the global rate may cause a block because:
  • 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.

The local rate reported is your own local rate. If the local rate is not set, the local rate is reported as zero.

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.