cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
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

1 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

View solution in original post

8 Replies
Level 8

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

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

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

View solution in original post

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​,

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

yep, makes sense

and tytyty

0 Kudos

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

Hi Markus,

Thank you very much for this information.

I hope to get a bit further

Mike

0 Kudos