I'm attempting to get the sql wait times for a specific DB User (on the DB User tab) via the DPA DB.
In the UI I can navigate to the DB User in question and drill down to the related SQLHASH and use that to create a query to get the wait times.
What I'm currently unable to find is a means to query the SQLHASH from the DB User name (in my case it's PIMSdbSvcRpt200d).
Thank you for your help ... I'm almost there
Solved! Go to Solution.
I was able to dig a bit more here is what I've got so far (not quite there yet):
select * from CONU_2 where NAME ='PIMSdbSvcRpt200d'
which returns a number which coincides with the url parameter &dId which is used in returning the page to the UI
but I'm not sure what other tables that is used in and as what column name to get me closer to the SQLHASH.
Still need help
Well, I'm able to go from DB User name to DBUSERID and then use that in a table to get the wait times with the following
select * from CONU_2 cu
join CON_DBUSER_SUM_2 c2 on c2.DBUSERID = cu.ID
join CON_DBUSER_TEN_MINUTE_2 c10 on c10.DBUSERID = cu.ID
where NAME ='PIMSdbSvcRpt200d'
however, the DATEHOUR I'm interested in is earlier than is available so, I'm close, but not sure yet how to get historical data.
Still need help lol
have a look at the table CONSW_2. Try to join CONU_2.ID with CONSW_2.XCUW.
I hope this helps you.
Trying to help here some. CONU is database user CONO is O/S user for an Oracle instance and database name in SQL server instances.
This may help you.
-- grab the ID field for this and use as a suffix (_X below) for the tables
SELECT id, name FROM cond;
-- Main wait time table
- XCUW - DB User - joins to CONU_X
- IXOY - O/S User - joins to CONO_X table (Oracle)
- PWMY - Machine - joins to CONM_X table
- IZHO - SQL Hash Value - joins to CONSS_X, CONST_X, CON_SQL_NAME
- KXPI - P1 value from wait interface (Oracle)
- IDSQ - P2 value from wait interface (Oracle)
- KQTZ - P3 value from wait interface (Oracle)
- KEEQ - wait event/wait type - joins to CONEV_X
- UDPW - Program - joins to CONPR_X
- VDSI - SID || '.' || Serial# (Oracle)
- MDCP - unused
- DBML - Module - joins to CONMOD_X (Oracle)
- RMAL - Action - joins to CONACT_X (Oracle)
- BLEE - blockee ID
- BLER - blocker ID
- ORPH - plan hash value - joins to CONPT_X
- HGOB - object id - joins to CONOBJ_X (Oracle)
- CTFL - file id - joins to CONF_X (Oracle)
- QP - frequency of polling in centiseconds - divide by 100 to get seconds
- D - timestamp of the activity
Wow, this is really good information. Thank you for sharing this.
How have you managed to assemble this information?
Thank you very much
SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community. More than 150,000 members are here to solve problems, share technology and best practices, and directly contribute to our product development process. Learn more today by joining now.