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.
SQL Execution Plan has some following limitations.
The following limitations apply:
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.
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
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?
I see the following in the collector logs
+ dynaTrace Collector Copyright (C) 2004-2015 Dynatrace
+ Version 220.127.116.114 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
It looks like the SQL has an error. Does any one else has this problem?
The user that is used to connect to the DB instance needs to be granted the following permissions:
CREATE SESSION, SELECT_CATALOG_ROLE
Check one more time in case if you missed the above permissions.
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.