Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials
Store

DPA - Top SQL Statements chart not showing historical data.

For one of our databases (Oracle) the historical data is not displaying in the TOP SQL Statements chart (first diagram below). The second screen shot shows the Storage I/O chart. We have done some licence management with this database - the licence was removed on Dec 15 and reinstated on Jan 11 after realizing this db required monitoring. How can I get the historical TOP SQL Statements chart to show what historical data is there?

Thanks,

Marg.

Find more posts tagged with

Accepted answers

margbrown

Support Case put in with SolarWinds.

First Reply...

Engineering Reply:

This appears to be a bug...

Because their data has been so sporadic, the charting engine has switched is using weekly data to generate those charts instead of detail data. This happens when the data in CONSW contains 30 distinct days of data that span more than 95 days.

When DPA switches to the weekly data, it asks Java for the first day of the week to start looking for data, which for "en_AU", is "Sunday".

Unfortunately, when those records were inserted into the weekly tables, DPA used Oracle's "trunc" function to truncate dates to the beginning of the week. Oracle considers "Monday" the first day of the week.

Therefore, DPA says to look for weekly data using a Sunday date but all the data is actually stored in the database as a Monday date and the charting engine never finds any data.

Once there is enough consistent detail data (30 distinct days of data that does not span more than 95 days), DPA will switch back to using detail data and the charts will work.

Other than waiting for the data to be collected, the only workaround I can think of is to possibly create a database login trigger that changes the DPA monitoring user's first day of week to Sunday.

First Reply....

Our Engineer recommends the following for you: They have CLEAN_DAYS_OF_DETAIL set to 90 days...which means 90 days of distinct data. Their dates are showing as November 15, 2015 - March 12, 2016. This means that in that span of 119 days, DPA has collected data for 90 of those days (29 days have been skipped). The easiest thing for them to do would be to change the CLEAN_DAYS_OF_DETAIL value for GENP_CODBSVR3 to 60 days...that should solve the problem. If they want to figure out how many distinct days will push them past 95 days, they can run this query and calculate it. select trunc(IEDX) D, sum(CNT) from CONTT_2 group by trunc(IEDX) order by D desc; I would recommend that they set CLEAN_DAYS_OF_DETAIL to 60 days for that database. They will then have to wait for the cleaners to run (either over night or using jConsole) and the charts should start displaying correctly. After 30 more days, they can set the CLEAN_DAYS_OF_DETAIL back to 90 days. Thanks, Jane | Solarwinds

All comments

mandevil

Can you tell us what version of DPA you are on?

margbrown

It is version 10.0.352.

Thanks.

margbrown

Have also noticed this in the db log file. Is that suggesting a process on the db is missing.stopped?

margbrown

Support Case put in with SolarWinds.

First Reply...

Engineering Reply:

This appears to be a bug...

When DPA switches to the weekly data, it asks Java for the first day of the week to start looking for data, which for "en_AU", is "Sunday".

Therefore, DPA says to look for weekly data using a Sunday date but all the data is actually stored in the database as a Monday date and the charting engine never finds any data.

Once there is enough consistent detail data (30 distinct days of data that does not span more than 95 days), DPA will switch back to using detail data and the charts will work.

Other than waiting for the data to be collected, the only workaround I can think of is to possibly create a database login trigger that changes the DPA monitoring user's first day of week to Sunday.

First Reply....