11/1/2018 3:38 PM

12/2/2024 9:11 PM

Database Performance Analyzer (DPA) collects SQL statistics every 10 minutes for each monitored database instance to provide data such as executions and reads for each SQL statement. The "stats poll" is a multi-step process that involves querying the monitored database instance for raw statistics data and post-processing the data in the DPA Repository. Ideally, the entire process takes less than a couple of minutes, but for various reasons it can take longer than expected.

Database Performance Analyzer

To diagnose issues with the stats polling. Sign into DPA as an administrator and click options -> DPA Diagnostics  from the "support utilities" section of the options menu. Click on stats tab. Select the instance that has missing stats or is suspected of problems. 

Timings of each step of the stats poll and it's insert are listed on this page. The longest timing value may tell you what item is at issue. 

Refer to the following sections for background information and potential solutions for each issue:

Query issue 

The query to retrieve SQL statistics from the monitored instance is taking a long time. This is typically due to either:

• A large number of statistics records retrieved from the monitored instance and sent across the network.
• High network latency between DPA and the monitored instance. This is often the case when you are monitoring a cloud database instance from a DPA server running on premises (or vice versa).

Solution to consider 

Enable Limited Stats in DPA for this monitored instance 

Enabling Limited Stats will lower the number of queried records. This can help on slow networks by bringing fewer rows across the network. However, it can result in slower query time on underpowered monitored instances because the limited stats query makes the monitored instance determine the top N queries.

1. Go to DPA's Advanced Options and click on the DB Instance Options tab.
2. Select this monitored instance.
3. Change the value of SQLSTATS_LIMITED to true.
4. (Optional) Change the value of SQLSTATS_LIMITED_ROWS to alter the number of statistics rows to retrieve from the monitored instance.

Truncate issue 

The data kept in this table is temporary. It is used for intermediate calculations, and therefore does not need to be logged or backed up. Each time the stats poll runs, a table in the repository is emptied to prepare for a new set of statistics records. By default, DPA truncates the table to empty it, and this operation is taking a long time.

Solutions to consider 

Lower the number of DPA permits 

On underpowered repositories, too many permits can essentially bottleneck the repository instance because DPA is asking it to do too much at one time.

1. On the DPA server, open the system.properties file.
2. Uncomment or add a line like this:
   com.confio.idc.repositoryDataSourceSemaphore.permits=15
3. Restart DPA.

Enable Limited Stats in DPA for this monitored instance 

Enabling Limited Stats will lower the number of queried records. This can help on slow networks by bringing fewer rows across the network. However, it can result in slower query time on underpowered monitored instances because the limited stats query makes the monitored instance determine the top N queries.

1. Go to DPA's Advanced Options and click on the DB Instance Options tab.
2. Select this monitored instance.
3. Change the value of SQLSTATS_LIMITED to true.
4. (Optional) Change the value of SQLSTATS_LIMITED_ROWS to alter the number of statistics rows to retrieve from the monitored instance.

Force a Delete of the temp table 

Truncate statements are typically very efficient, so it should not be common that a Delete runs faster. However, if the previous solutions do not resolve the issue, try deleting the temp table.

1. Go to DPA's Advanced Options and click the System Options tab.
2. Change the value of SQLSTATS_DELETE_FROM_TEMP_TABLES to true.
3. Restart DPA.

Configure the repository 

See other possible solutions in the Underpowered repository section.

Permit wait issue 

Permits are a mechanism that DPA uses to limit simultaneous stats poll activity against the repository. The more permits you have, the more simultaneous activity can happen. The default permit count is 20.

Typically, each monitored instance's stats poll is either using the repository (in which case it has a permit), or is waiting to acquire a permit to use the repository. Significant time spent waiting for permits is a sign that the repository database cannot handle the amount of simultaneous activity that the stats polls are demanding.

Solutions to consider 

Lower the number of DPA permits 

On underpowered repositories, too many permits can essentially bottleneck the repository instance because DPA is asking it to do too much at one time.

1. On the DPA server, open the system.properties file.
2. Uncomment or add a line like this:
   com.confio.idc.repositoryDataSourceSemaphore.permits=15
3. Restart DPA.

Enable Limited Stats on the monitored instances with the most stats rows 

The goal here is to decrease the overall stats poll load against the repository. Enabling Limited Stats for a monitored instance will lower the number of queried records, which will in turn limit the processing that the repository needs to do. Note, however, that this can result in slower query time on underpowered monitored instances because the limited stats query makes the monitored instance determine the top N queries. If this happens for a given monitored instance, then you might want to consider disabling Limited Stats for that monitored instance.

For each monitored instance that is processing a lot of stats rows:

1. Go to DPA's Advanced Options and click the DB Instance Options tab.
2. Select the monitored instance.
3. Change the value of SQLSTATS_LIMITED to true.
4. (Optional) Change the value of SQLSTATS_LIMITED_ROWS to alter the number of statistics rows to retrieve from the monitored instance.

Configure the repository 

See other possible solutions in the Underpowered repository section.

Insert issue 

DPA is spending a significant amount of time inserting statistics into the DPA repository. Monitors take turns (based on permits) inserting batches of raw statistics records. Each monitor's raw statistics data is held in its own pair of tables (CONTSS1_<dbid> and CONTSS2_<dbid>).

Solutions to consider 

Lower the number of DPA permits 

On underpowered repositories, too many permits can essentially bottleneck the repository instance because DPA is asking it to do too much at one time.

1. On the DPA server, open the system.properties file.
2. Uncomment or add a line like this:
   com.confio.idc.repositoryDataSourceSemaphore.permits=15
3. Restart DPA.

Enable Limited Stats on the monitored instances with the most stats rows 

The goal here is to decrease the overall stats poll load against the repository. Enabling Limited Stats for a monitored instance will lower the number of queried records, which will in turn limit the processing that the repository needs to do. Note, however, that this can result in slower query time on underpowered monitored instances because the limited stats query makes the monitored instance determine the top N queries. If this happens for a given monitored instance, then you might want to consider disabling Limited Stats for that monitored instance.

For each monitored instance that is processing a lot of stats rows:

1. Go to DPA's Advanced Options and click the DB Instance Options tab.
2. Select the monitored instance.
3. Change the value of SQLSTATS_LIMITED to true.
4. (Optional) Change the value of SQLSTATS_LIMITED_ROWS to alter the number of statistics rows to retrieve from the monitored instance.

Configure the repository 

See other possible solutions in the Underpowered repository section.

Diff issue 

The statistics values kept by the monitored instance are counters that increase from the time the query first runs or when it re-enters the monitored instance's cache. To calculate that statistics during a period of time, DPA takes a delta of statistics samples taken at the beginning and end of that time period. In this case, the delta is taking a long time to run.

Solutions to consider 

Lower the number of DPA permits 

On underpowered repositories, too many permits can essentially bottleneck the repository instance because DPA is asking it to do too much at one time.

1. On the DPA server, open the system.properties file.
2. Uncomment or add a line like this:
   com.confio.idc.repositoryDataSourceSemaphore.permits=15
3. Restart DPA.

Enable Limited Stats on the monitored instances with the most stats rows 

The goal here is to decrease the overall stats poll load against the repository. Enabling Limited Stats for a monitored instance will lower the number of queried records, which will in turn limit the processing that the repository needs to do. Note, however, that this can result in slower query time on underpowered monitored instances because the limited stats query makes the monitored instance determine the top N queries. If this happens for a given monitored instance, then you might want to consider disabling Limited Stats for that monitored instance.

For each monitored instance that is processing a lot of stats rows:

1. Go to DPA's Advanced Options and click the DB Instance Options tab.
2. Select the monitored instance.
3. Change the value of SQLSTATS_LIMITED to true.
4. (Optional) Change the value of SQLSTATS_LIMITED_ROWS to alter the number of statistics rows to retrieve from the monitored instance.

Configure the repository 

See other possible solutions in the Underpowered repository section.

Underpowered repository 

Stats polls can take a long time simply because the repository is underpowered. The solutions below offer a variety of ways to decrease stats poll load and to increase the power/efficiency of the repository configuration. Some overlap with solutions from other, more specific, issues.

Solutions to consider 

Ensure that the repository has sufficient resources 

• Increase memory and CPU allocation to the repository.

• Use faster drives for the repository.

• Do not co-locate DPA and the repository. If the DPA application and the repository are running on the same server, put them on separate servers so they are not competing for the same computing resources.

• If DPA and the repository are not co-located, make sure there is a fast network between them.

• Limit or eliminate other activity on the repository instance. If other applications are using the repository's database instance, they may be competing with DPA for resources. If possible, separate out the repository into an instance on its own server.

• For Oracle repositories, configure redo logs files. Make sure the redo log files are large enough, that there are enough of them, and they are not on same drives as data files. Look at disk device latencies.

• Register the repository to see why it is slow. Make sure you set MONITOR_IGNITE_MONITORING so you can see DPA query activity.

Lower the number of DPA permits 

On underpowered repositories, too many permits can essentially bottleneck the repository instance because DPA is asking it to do too much at one time.

1. On the DPA server, open the system.properties file.
2. Uncomment or add a line like this:
   com.confio.idc.repositoryDataSourceSemaphore.permits=15
3. Restart  DPA.

Enable Limited Stats on the monitored instances with the most stats rows 

The goal here is to decrease the overall stats poll load against the repository. Enabling Limited Stats for a monitored instance will lower the number of queried records, which will in turn limit the processing that the repository needs to do. Note, however, that this can result in slower query time on underpowered monitored instances because the limited stats query makes the monitored instance determine the top N queries. If this happens for a given monitored instance, then you might want to consider disabling Limited Stats for that monitored instance.

For each monitored instance that is processing a lot of stats rows:

1. Go to DPA's Advanced Options and click the DB Instance Options tab.
2. Select the monitored instance.
3. Change the value of SQLSTATS_LIMITED to true.
4. (Optional) Change the value of SQLSTATS_LIMITED_ROWS to alter the number of statistics rows to retrieve from the monitored instance.

Missing statistics notification

You will see this notification if, due to the issue(s) that caused the Stats Poll to take a long time, there was not enough time to insert and process all statistics records in the repository.

• This results in some queries with no statistics for some 10 minute intervals.
• You can also see some queries with inaccurate statistics values, sometimes lower than expected and sometimes higher than expected.

It is important to act on some of the solutions suggested to resolve the underlying issues so that the stats polls complete quickly, enabling them to collect all statistics information.

 

Further troubleshooting step.

It can be helpful to know how many rows would have been inserted of gathered had DPA kept going after the SQLSTATS_FETCH_TIME_LIMIT_SECS value has been exceeded. This helps at times to know if tuning of the above settings helped. 

You can set an advanced Support options called SQLSTATS_FETCH_TIMEOUT_COUNT_ROWS this flag will to continue counting SQL Stats rows after timeout. Used to get an accurate count of how many SQL Stats Rows were not processed after a timeout. 
Note: Can be set globally using System option as well. 

This is set in Options -> Advanced options -> Support options check box selected. It causes the INCOMPLETE_INSTERT to populate a row count of rows that would have been inserted had the time out not been exceeded.