Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials
Store

DPA table that relates the DBUSER name with the SQLHASH

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

Find more posts tagged with

Accepted answers

jaminsql

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

All comments

mmuller

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

mmuller

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

mrfox

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

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

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

mmuller

Hi Markus,

Thank you very much for this information.

I hope to get a bit further

Mike