SQL Store Procedure Execute Time incorrect
Hello,
In the screenshot below, we see a sp called and the execution time is good (ms). However, when looking on the database server itself, we can see the sp taking much longer(10 sec). The transaction flow displays the oracle time as minimal. Is this normal behavior, or should DT record the actual db execution time?
i talked to R&D and it's pretty obvious that there's a bug in the dynaTrace Collector regarding the Auto-Sensor matching. unfortunately we did not see this one before and so we don't have any fix for this issue yet. i'd suggest to open up a support ticket with this session and the information about the non-matching auto-sensors. you can also refer to me and I'll tell support the details then. this should be the best way to handle this issue.
in the meantime you could do some CPU samplings on the affected agents (Beacon_UserMGMT_Service_MS*) and look for methods which consume a lot of time below those doService() calls. you will not have transaction, web service or SQL context in these samplings, but if you need this, you can place custom sensors on those time-consuming methods and then you'll find them on your PurePaths hopefully giving you a better idea of what consumes all the time in your searchPatients() calls.
hope this helps,
Christian
Thank you so much for you time and efforts. I will open the ticket as suggested. We will also use CPU Sampling to see if the slow method is revealed.
Thank you Christian for the help. I really look forward to R&D's response. As you can see the execution times for the purepaths are extremely bad for a search function and severely impact our customer's experience. Again, the dev team here believes that this is a database issue due to many factors such as large tables and indexing constraints. It is just hard to provide metrics to support the theory when analyzed with Dynatrace.
Is there anything I can do to provide more information? Any configuration changes to sensors that may provide more insight?
i had a short look at your session.
unfortunately the auto sensor data in your PurePaths is not really helpful here, because the auto sensor information could not be matched at all in the PurePaths of interest.
there have been some auto sensor matching bug fixes since dynaTrace 5.5, i guess one of them affects those PurePaths. i'll talk to R&D if we maybe can get some more information out of your session.
Christian
Gents, thank you for your responses.
Andreas, I understand the situation you are describing and the time is not in JDBC at all. It is messagedispatchServlet. I should mention that this is spring framework.
Christian, I will follow up with the database team to see exactly what consumes the time.
All, I have attached pp for your review. Thanks
hi,
for reviewing Andi's theories, it would be really helpful, if you could attach some PurePaths here.
what I'm wondering: what is it, that you see about this stored procedure on your database server? the time for one execution or for all executions in a specific timeframe?
best, Christian
Hi
When you drill from the Database Dashlet to the PurePath - can you see time spent in database related methods such as FETCH? Our Auto Sensors should pick up execution times of these methods as well that are iterating through the result set.
The time that we show in the database dashlet is the same time we show in the PurePath in the executeStatement or executeQuery methods. So - it could be that these executions return rather fast - while the "majority" of the time is later spent in iterating through the result set. This time is currently not attributed to the executeQuery/executeStatement methods but can only be seen through the Auto Sensor nodes
Let me know if this makes sense
