cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

This product reached the end of support date on March 31, 2021.

Error getting Execution Plan with Oracle Database agent

quazi_ahmed
Participant

We are testing the database agent in AppMon 6.5 and we are getting database performance metrics similar to what we get out of Oracle AWR report which is very good. However, we are running into an error when trying to get a SQL Execution Plan displayed. We checked the DB access and we can generate Execution Plan in Oracle using the same id, but cannot do so from dynatrace. Any thoughts on what might be going on? Thanks for your help.

9 REPLIES 9

Babar_Qayyum
DynaMight Leader
DynaMight Leader

Hello Quazi,

SQL Execution Plan has some following limitations.

The following limitations apply:


  • Not supported for stored procedures. Statements that start with call, exec, {call or {exec should have the Show SQL Execution Plan option disabled.
  • If the DB cache has been cleared, prior execution plans are not available.
  • Not supported for the Oracle Database setting cursor_sharing set to force
  • In order to request an execution plan the user must have the DB permission to view SQL statements and the AppMon one (Settings menu > Dynatrace Server > Settings item > Confidential Strings horizontal tab) to view confidential strings.

Regards,

Babar

quazi_ahmed
Participant

Hi Babar, I have seen the list of limitations. However, I can't seem to find anything that is relevant in my case. I can see the SQLs from within the agent and they are simple SELECT statements as opposed to call or exec stored procedures. I am also running workload as I am trying to capture the execution plan. The cursor sharing is set to EXACT in the database and we are using admin account in dynatrace to try this function. So, it is not clear to me if anyone of these limitations apply.

adam_gardner
Dynatrace Champion
Dynatrace Champion

Can you get any execution plans for any SQL statements? Are you missing only some but not others? I have seen this scenario and the DBA I was working with believed it was because they were dropping out of the cache. In that scenario we were able to see explain plans for the most frequently run statements, but not the "rarer" ones

quazi_ahmed
Participant

Hi Adam, We are not seeing any execution plan at all in dynatrace. Using the same database ID as the one used in dynatrace, I can get the execution plan from the database without any issues. Anything else I can look?

alex_proano
Participant

I see the following in the collector logs

+-----------------------------------------------------------------------------
+ dynaTrace Collector Copyright (C) 2004-2015 Dynatrace
+-----------------------------------------------------------------------------
+ Version 6.3.4.1034 built Thu May 19 08:14:30 EDT 2016
+ Platform: Windows 7 6.1, amd64 64bit
+ vm: Java HotSpot(TM) 64-Bit Server VM 1.8.0_66, Oracle Corporation, mixed mode

2017-03-01 10:28:40 SEVERE [DbAgentPeerWrapper] Db agent Db-Agent1failed to generate explainPlan for query: select decode(failover_method, NULL, 0 , 'BASIC', 1, 'PRECONNECT', 2 , 'PREPARSE', 4 , 0), decode(failover_type, NULL, 1 , 'NONE', 1 , 'SESSION', 2, 'SELECT', 4, 1), failover_retries, failover_delay, flags from service$ where name = :1: com.dynatrace.diagnostics.collector.agentcenter.db.DbAgentPeerWrapper getExplainPlanForQuery:371
java.sql.SQLSyntaxErrorException: ORA-00904: : invalid identifier

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1017)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:655)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:249)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:566)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:215)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:58)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:776)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:897)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1034)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3820)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3867)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1502)
at com.dynatrace.diagnostics.collector.agentcenter.db.explainplan.OracleExplainPlanRetriever.a(SourceFile:66)
at com.dynatrace.diagnostics.collector.agentcenter.db.explainplan.OracleExplainPlanRetriever.getExplainPlan(SourceFile:51)
at com.dynatrace.diagnostics.collector.agentcenter.db.DbAgentPeerWrapper.getExplainPlanForQuery(SourceFile:369)
at com.dynatrace.diagnostics.collector.agentcenter.db.OracleAgentPeerWrapper.getExplainPlanForQuery(SourceFile:34)
at com.dynatrace.diagnostics.collector.agentcenter.db.DbAgentCenter$7.call(SourceFile:578)
at com.dynatrace.diagnostics.collector.agentcenter.db.DbAgentCenter$7.call(SourceFile:573)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)

It looks like the SQL has an error. Does any one else has this problem?

That might be a bug on the DB Agent side. Please open a support case as Babar suggests.

Wiktor

Babar_Qayyum
DynaMight Leader
DynaMight Leader

Hello Alex,

The user that is used to connect to the DB instance needs to be granted the following permissions:

Oracle Database 11g/12c

CREATE SESSION, SELECT_CATALOG_ROLE

Check one more time in case if you missed the above permissions.

Regards,

Babar

quazi_ahmed
Participant

Hi Babar, We have the roles setup from the database side and we know that SELECT CATALOG ROLE is working since we are able to get to the database metrics such as Wait Times and others that you usually get from Oracle AWR reports that require that role if you are not a DBA. The issue or the error that we see is only with SQL Execution Plan and as Alex has posted dynatrace Collector is getting a java.sql.SQLSyntaxErrorException: ORA-00904 when this issue occurs.

Hello Quazi,

I would suggest you to open a support case for this specific issue.

Regards,

Babar