Working on a POC that would create a custom report that would query DPA data.
is there an ER diagram, or other info on how DPA stores data available to customers?
I made some notes in Confluence, let me see if I can cut and paste them here. This is not complete, but it was the tables I needed, so I am going to mark it as an answer.
In the event you need to query the DPA Repository, here are some notes that might be helpful.
Location
Database: dpa_repository
Schema: ignite (all tables are in this schema)
Table COND
This contains a list of monitored instances
The ID column is the instance ID.
Tables ending in _#
Each monitored instance has its own set of tables to store data.
All tables ending in _# (number) are instance data storage
In these notes, database instance storage will be referred to as _x
Table
Notes
CON_STATS_TEN_MINUTE_x
4 days of history with data broken down into 10 minute increments. Data includes SQLHASH, Physical Reads, Logical Reads, Row Count, Total Time in Seconds
CON_STATS_SUM_x
Long term history summarized by hour. Data includes SQLHASH, Physical Reads, Logical Reads, Row Count, Total Time in Seconds
CON_STATS_DAY_SUM_x
Query data summarized by day. SQLHASH, DATE, TIMESECS, EXECS
CON_SQL_TEN_MINUTE_x
4 days history of query wait time data broken down in 10 minute increments. Data includes SQLHASH, EVENTID, Time in Seconds.
CON_SQL_SUM_x
Long term history summarized by hour. Data includes SQLHASH, EVENTID, Time in Seconds.
CONST_x
Query text. Queries are broken up into 4000 character chunks and stored in this table. SQLHASH identifies the query, P is the sequence column for ordering the chunks. ST is the SQL Text.
CONPR_x
Client program names
CONM_x
Client host/machine names
CONO_x
Database Names
CONU_x
User names
CONSPT_x
XML plan data for each plan hash
CONSPH_x
Plan handle to Plan hash mapping
CON_PLAN_SUM_x
Time for each query plan broken down by hour, sqlhash.
CON_PLAN_TEN_MINUTE_x
Time for each query plan broken down by 10 minutes, limited to ~4 days
CON_SQL_NAME
Custom names given to SQL Queries.
Not sure how much can be posted on our community site here, but if you reach out to support, this is something that we have shared previously - meaning the full ERD... However, this looks like a good start!
