This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

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?

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

    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!

Reply Children
No Data