ER Diagram

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!