0

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)

EM Cloud Control Performance Home

EM Cloud Control SQL Details

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.

  • Well the first step should be to find out which user is running that statement and from where the connection comes. – a_horse_with_no_name Oct 11 '18 at 20:10
  • @a_horse_with_no_name It is SYS user running the statemtent and the service is SYS$BACKGROUND. – saygley Oct 12 '18 at 13:10
  • That is obviously not application code, it is recursive dicitonary SQL of the database. First I would check the execution plan and statistics of that statement, because there is an index on the short column, but it may not be utilized. If everything looks good, then the number of entries in the javasnm$ table, then compare it with the name and number of actual Java objects in the database to make sure there are no orphan entries in it. – Balazs Papp Oct 18 '18 at 20:50
1

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:

select longname from javasnm$ where short = :1

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.

  • The information you give about javasnm$ is very beneficial, and it does not exist in official Oracle documentation. Thanks for that! I will definitely check with the application team and ask them if they intentionally use JVM component of Oracle itself. The application is a java app and it runs on top of Tomcat. And I know that it does not implement connection pooling. Your comment also brought curiosity on my mind how lack of connection pooling can relate to enormous number of executions for this SQL. Any comment or point regarding this would be highly appreciated. – saygley Oct 22 '18 at 14:48
  • Number of executions for this SQL is because it is running on Oracle's JVM. I am saying it should not run there. Whether you use connection pooling or not will not affect that SQL, but since you're using OEM I assume you are monitoring large databases. If that is the case, then you should be using connection pooling via clusters. This is necessary for oracle session management, as repetitive connection/disconnection is not scaleable for performance. – sandman Oct 23 '18 at 6:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.