ER Diagram

Question

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?

often_confused_dba · Accepted Answer

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.

ER Diagram

Top Replies