DX Unified Infrastructure Management

Hi All,

We are monitoring different databases using UIM DB probes. Our requirement is to query the UIM database to list the database names and the server names where the databases are hosted.

I tried fetching this information from S_QOS_DATA table using the below queries.

To list MS SQL Databases Information:

*******************************************

SELECT REGEXP_SUBSTR(TARGET, '[^\]+', 1, 1) DBSERVER,

                   REGEXP_SUBSTR(TARGET, '[^\]+', 1, 2) DBNAME

FROM S_QOS_DATA Where QOS Like 'QOS_SQLSERVER_DATABASE_STATE%' AND CI_METRIC_ID IS NOT NULL

To List ORACLE Databases Information:

*********************************************

SELECT REGEXP_SUBSTR(TARGET, '[^-]+', 1, 1) DBSERVER,

REGEXP_SUBSTR(TARGET, '[^-]+', 1, 2) DBNAME

FROM S_QOS_DATA Where QOS Like 'QOS_ORACLE_CHECK_DBALIVE%' AND CI_METRIC_ID IS NOT NULL

But these queries are taking long time to get the required information (say above 1 min). This is expected as S_QOS_DATA is a huge table in UIM DB which contains more records for each QOS.

As this query output is fed to other tool where the users select the data from a drop down. Because of this, it should not be recommended to use above query as it is long time to populate the information.

Is there any alternate table that we can query against or suggest an alternate method?

Regards,

Rajashekar

The regex is probably causing the slow down.

May want to try a simple contains or like.

Also might want to create a view first with the where clause and then issue you query against the view.

I would suggest you check with your DBA on how to optimize this.

Hi Gene,

Thank you for the response. I am using regular expressions to separate the host name and database name from TARGET.

For example, In S_QOS_DATA table targets for MS SQL Databases are stored as Host_Name\DB_Name.

Example: NTMSSQL01\msdb.

Is there any alternate table in UIM that I can use to fetch the records? Please let me know.

Regards,

Rajashekar

I am not sure what you are looking for in the result set but I would start with something simple like probe = "sqlserver"

then look at the results set and see if there is another filter say on name that might get you what you want.

If I try with where clause using probe = 'sqlserver', it will give all the QOS list activated on each database. To avoid that, I am taking one QOS which contains all the database instance name.

My final result should list the host name and database name. Other than the mentioned query, I didn't find any query which can fulfill my requirement .

= will be faster than like in almost all cases. So why are you using "like" to test for the QoS name? "Where QOS = 'QOS_SQLSERVER_DATABASE_STATE'" should be much faster. 

Also, my s_qos_data table has slightly more than half a million rows in it - the "like" query takes roughly a second to run. The "=" query takes a flicker of the screen - can't time it really. If yours is taking 60+seconds then maybe you have a resource problem too. It would be worth looking into ow much unnecessary stuff might be in that table from systems that no longer exist for instance.

Also also, you should be able to have your DBA look at the execution plan of the query - often there are things you can do - especially if you know something about the distribution of the data that the database doesn't. Query optimization isn't always straight forwards - regardless, like, or, and calculated fields are all bad from a performance standpoint because they dramatically affect the I/O necessary to execute the query.

-Garin