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.
Best Answer
From what I remember about javasnm$, it is a table that stores long names of java objects, as oracle objects can only be up to 30 characters (stored in field SHORT). Most java objects are over 30 characters so it constantly has to do these look-ups.
The SQL we have is:
We had similar performance concerns about the high execution counts of similar SQL when apps made use of the JVM built in to oracle, but nowadays most of our java apps don't use the built-in JVM and that issue isn't there anymore.
Check with your third-party vendor if they can move it off the database and use something like connection pooling e.g. Weblogic to interact with oracle.