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?
Parents
-
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
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.
-
-
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
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.
-
