Stored Procedures ID in MSSQL

[Swati_Bhat]

Hi all,

can someone suggest how to capture and display storedprocedure ID?

Thanks!

[MaciejNeumann]

Hello @Swati_Bhat ,

As we're waiting for help from the rest of the Community, you can check out these documentation articles, which explains how to manage your MSSQL extensions and how to get even more information by creating your own MSSQL extension.

[Mike_L]

As this is in the extensions forum: Do you mean with the MSSQL extension (which currently doesn't capture stored procedures), or do you mean through the OneAgent deep monitoring? I'd expect the latter, but just want to make sure before moving the thread to the correct forum.

[Babar_Qayyum]

Hello @Mike_L 

We are using the Oracle Database Extension 1.1.3, but do not see the most time consuming Oracle statements. What could be the reason?

Regards,

Babar

[Mike_L]

There is a new feature set added called TopN, you need to enable that to get the top queries. 

[Babar_Qayyum]

Hello @Mike_L 

The feature is already enabled.

Regards,

Babar

[Mike_L]

And you are on logs v2 or grail? If so I’d have a look at the AG logs to look for a permission error or so.

[Babar_Qayyum]

Hello @Mike_L 

We are on Log v2. Normally, we are providing the following permissions:

• CREATE_SESSION
• SELECT_CATALOG_ROLE

Sure, I can check the AG logs as well.

Regards,

Babar

[Babar_Qayyum]

Hello @Mike_L 

Is there any perticular error should I find in the AG logs?

Regards,

Babar

[Mike_L]

Permission errors in the Oracle datasource extension logs.

[Babar_Qayyum]

Hello @Mike_L 

I did not find anything related to the permissions except the following WARN is appearing in the logs.

[c0ceabd8-545e-3465-a186-f0e9fae683bd][-8419178555432512324][198073][out][c0ceabd8-545e-3465-a186-f0e9fae683bd]2023-06-08 | 13:20:08.413 | pool-2-thread-2      |  WARN | c.d.s.p.p.GroupPoller     | While polling from endpoint XXXXX-scan:0000:ABCD, querying group 'Redo', some metrics were ignored because Database returned null value: [com.dynatrace.extension.sql-oracle.memory.sga.redoBuffer.redoLogSpaceWaitTime, com.dynatrace.extension.sql-oracle.memory.sga.redoBuffer.redoSizeIncrease, com.dynatrace.extension.sql-oracle.memory.sga.redoBuffer.redoWriteTime, com.dynatrace.extension.sql-oracle.memory.physicalReads, com.dynatrace.extension.sql-oracle.memory.physicalReadsDirect, com.dynatrace.extension.sql-oracle.memory.memorySorts, com.dynatrace.extension.sql-oracle.memory.diskSorts]

Regards,

Babar

[Mike_L]

Download the latest version of the extension from the public hub, open the yaml and grab the query related to TopN. If you execute that manually as the configured user, do you get data?

[Babar_Qayyum]

Hello @Mike_L 

The query is providing SQL_ID and SQL_FULL TEXT but in the Dynatrace we do not see any Oracle statements. The following screenshots are for your reference.

Regards,

Babar

[Mike_L]

Please create a support ticket.

[Babar_Qayyum]

Hello @Mike_L 

A quick update on the issue discussed with you. 

There was a rule applied to drop the info log, so after modifying the rule, TopN statements start capturing.

Regards,

Babar