cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
mmuller
Level 8

DPA table that relates the DBUSER name with the SQLHASH

Jump to solution

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

Mike

8 Replies
mmuller
Level 8

Re: DPA table that relates the DBUSER name with the SQLHASH

Jump 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

http://server.as.local:8123/iwc/database.iwc?repo_id=1

&db_id=2

&day=Apr+16!991555390800000

&time=1PM-2PM!991555437600000

&dType=6

&dId=314

&dName=PIMSdbSvcRpt200d

&bc=

&action=0

&ts=3600

&tab=31

&iorw=r

&pm=P

&sdTab=

&fullSQLText=

&chartType=

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

Thank you.

Mike

0 Kudos
mmuller
Level 8

Re: DPA table that relates the DBUSER name with the SQLHASH

Jump to solution

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

Mike

0 Kudos
mrfox
Level 7

Re: DPA table that relates the DBUSER name with the SQLHASH

Jump to solution

Hi Mike,

have a look at the table CONSW_2. Try to join CONU_2.ID with CONSW_2.XCUW.

I hope this helps you.

Kind regards

Markus

jaminsql
Level 12

Re: DPA table that relates the DBUSER name with the SQLHASH

Jump to solution

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

CONSW_X

----------

- 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

mmuller
Level 8

Re: DPA table that relates the DBUSER name with the SQLHASH

Jump to solution

Hi Jaminsql,

Wow, this is really good information. Thank you for sharing this.

How have you managed to assemble this information?

Thank you very much

Mike

0 Kudos
mmuller
Level 8

Re: DPA table that relates the DBUSER name with the SQLHASH

Jump to solution

Hi Markus,

Thank you very much for this information.

I hope to get a bit further

Mike

0 Kudos
jaminsql
Level 12

Re: DPA table that relates the DBUSER name with the SQLHASH

Jump to solution

mmuller​,

I am Sr. Support for DPA I work for Solarwinds and DPA has been my primary product for almost 5 years.

mmuller
Level 8

Re: DPA table that relates the DBUSER name with the SQLHASH

Jump to solution

yep, makes sense

and tytyty

0 Kudos