While analyzing CA Release Automation 6.x DB performance you may come across the following SQL statements and exceptions.
- Data Management Server (aka NAC) creates SQL statement with prefix "CREATE GLOBAL"
- SQL error recorded during deployment (with ORACLE DB it will look like below)
Error: java.sql.SQLSyntaxErrorException: ORA-00955: name already used by an existing object
The above observation rose the following questions
- Why Data Management Server (aka NAC) creates SQL statement prefix with "CREATE GLOBAL"?
- Why these SQL exceptions are getting recorded?
- Do these SQL exception degrades Application performance?
Release Automation: 6.1, 6.2 and 6.3
To address those questions, please find the answers below:
- Q1 Answer: Release Automation uses, Hibernate framework to map application’s java objects to the database tables, which include generation of SQL queries for select, update, and delete operations. The underlying implementation by hibernate to implement certain delete and update scenarios is via temporary table to hold the ID values participating in delete or update operation. This is the reason for the “CREATE GLOBAL TEMPORARY TABLE” statements
- Q2 Answer: The reason for ORA-00955 error is a known issue with Hibernate. As per current implementation it doesn't bother to check first if the table already exist, rather prefer to rely on database "let it know" which result in ORA-00955 errors. This behavior has been corrected in later version of hibernate and once RA upgrade to newer version it will be corrected.
- Q3 Answer: Creation of temporary tables as well as failing with error ORA-00955 is not something to be concerned about, until they are resulting in some specific observable performance implications.