For the Oracle 12c database I've been administrating, I usually carry out the monitoring using Oracle Enterprise Manager Cloud Control 13c. From time to time, the database suffers from performance issues that are felt by end users.
When I check the performance details of the database in EMCC following the menu in Database Home page Performance -> Top Activity, I see some application specific SQLs are problematic. In addition to problematic SQLs, I also usually see the following SQL listed as the top consumer:
select longdbcs from javasnm$ where short = :1
I know that the 3rd party application running on top of this Oracle database is Java based, and it might not have the most optimized code in terms of Oracle calls.
Would anyone have any idea what this SQL would be about and how to figure out if it is really needed or something that could have been disabled? (at Oracle level or with a slight change in application level)
I searched the internet for relevant information but was not able to find anything clear / comprehensible enough for me. For anyone who would have any points, suggestions and answers are welcome. Thanks in advance.