How to improve activejdbc startup performance?-CodePudding

My org uses legacy components with javalite activejdbc for an ORM in our java web application. I am creating a local docker database (oracle 12c) for development. When I start the local jetty server pointing at my local database the startup takes more than 1 hour. The cause is active jdbc is looking at all the entity classes for all the tables and fetching metadata for each one in a loop. Looking at active JDBC registry class (org.javalite.activejdbc.Registry) its doing this:

Connection c = ConnectionsAccess.getConnection(dbName);
java.sql.DatabaseMetaData databaseMetaData = c.getMetaData();
String[] tables = metaModels.getTableNames(dbName);
for (String table : tables) {
    ResultSet rs = databaseMetaData.getColumns(null, schema, tableName, null);
    ...
}

Each of these calls are taking like 15-30 seconds and there are hundreds of entity classes. When I point my local server at our test database its much faster (but still very slow). Is there anyway I can tune my local docker database so these metadata calls are faster? Or is there any activejdb configuration I can set to make the initialization lazy? There has to be some reason these calls take so much longer on local database vs our test database. I dont think its because our test DB is such a power beast - test DB is really pretty slow and has low resources.

EDITS / Clarifications: This really seems to not be less an active jdbc question and more a question why the metadata queries take so long on my local docker database. The below code takes 16 seconds with local db URL and 356 ms when pointing at test. I can also see the local CPU spikes to 100% in the docker image.

public class DatabaseMetaDataTest {

     public static void main(String args[]) throws SQLException {
          //Registering the Driver
          DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
          //Getting the connection
          String url = "jdbc:oracle:thin:@localhost:1521/ORCLCDB.localdomain";
          //String url = "jdbc:oracle:thin:@test:1532:xe";
          
          Connection con = DriverManager.getConnection(url, "user", "pass");
          System.out.println("Connection established......");
          //Retrieving the meta data object
          DatabaseMetaData metaData = con.getMetaData();
          //Retrieving the columns in the database
          long start = System.currentTimeMillis();
          ResultSet columns = metaData.getColumns(null, "SCHEMA", "TABLE", null);
          long end = System.currentTimeMillis();
          System.out.println("duration:"   (end-start));
          //Printing the column name and size

       }

}

Further updates: I decompiled the oracle driver and found this is the SQL taking forever:

SELECT  NULL AS table_cat,
       t.owner AS table_schem,
       t.table_name AS table_name,
       t.column_name AS column_name,
       DECODE (t.data_type, 'CHAR', 1, 'VARCHAR2', 12, 'NUMBER', 3,
               'LONG', -1, 'DATE', 93, 'RAW', -3, 'LONG RAW', -4,  
               'BLOB', 2004, 'CLOB', 2005, 'BFILE', -13, 'FLOAT', 6, 
               'TIMESTAMP(6)', 93, 'TIMESTAMP(6) WITH TIME ZONE', -101, 
               'TIMESTAMP(6) WITH LOCAL TIME ZONE', -102, 
               'INTERVAL YEAR(2) TO MONTH', -103, 
               'INTERVAL DAY(2) TO SECOND(6)', -104, 
               'BINARY_FLOAT', 100, 'BINARY_DOUBLE', 101, 
               'XMLTYPE', 2009, 
               1111)
              AS data_type,
       t.data_type AS type_name,
       DECODE (t.data_precision,                null, DECODE(t.data_type,                        'NUMBER', DECODE(t.data_scale,                                    null, 0                                   , 38),          DECODE (t.data_type, 'CHAR', t.char_length,                   'VARCHAR', t.char_length,                   'VARCHAR2', t.char_length,                   'NVARCHAR2', t.char_length,                   'NCHAR', t.char_length,                   'NUMBER', 0,           t.data_length)                           ),         t.data_precision)
              AS column_size,
       0 AS buffer_length,
       DECODE (t.data_type,                'NUMBER', DECODE(t.data_precision,                                 null, DECODE(t.data_scale,                                              null, -127                                             , t.data_scale),                                  t.data_scale),                t.data_scale) AS decimal_digits,
       10 AS num_prec_radix,
       DECODE (t.nullable, 'N', 0, 1) AS nullable,
       NULL AS remarks,
       t.data_default AS column_def,
       0 AS sql_data_type,
       0 AS sql_datetime_sub,
       t.data_length AS char_octet_length,
       t.column_id AS ordinal_position,
       DECODE (t.nullable, 'N', 'NO', 'YES') AS is_nullable,
         null as SCOPE_CATALOG,
       null as SCOPE_SCHEMA,
       null as SCOPE_TABLE,
       null as SOURCE_DATA_TYPE,
       'NO' as IS_AUTOINCREMENT
FROM all_tab_columns t
WHERE t.owner LIKE 'SCHEMA' ESCAPE '/'
  AND t.table_name LIKE 'TABLE' ESCAPE '/'
  AND t.column_name LIKE '%' ESCAPE '/'

ORDER BY table_schem, table_name, ordinal_position

I can see when I run this in oracle sql developer it takes like .5 seconds for my sysdba user but the other users its taking 16 seconds. Still investigating what the difference is between these users.

Further updates... This seems to be due to some oracle bug in 12c. select * from all_tab_columns has a bad execution plan when running as regular user. Its complaining about some obscure table "X$KZSRO" doing full table scan and taking forever to sort (the table has 2 rows ffs). When I connect as sysdba it runs faster. Im guessing there is some issue with regular users accessing this table. For now since this is just dev database Im just going to grant sysdba role to my user and figure out some sql profile later. I know its not great solution but it fixes the performance bug in oracle. Startup time went from 1 hour to 1 minute.

CodePudding user response：

First, if getting the metadata from your database takes 15 - 30 seconds per table, there must be something very wrong with that database. ActiveJDBC uses dynamic discovery in order to be in sync with the database on each start. This is the default behavior.

However, if you want, you can use the Static Metadata Generation. Using this method, all database metadata will be collected during a build time and will be packaged into your jar as a file. ActiveJDBC will then start instantly on all other environments because it will read metadata from this file rather than a database.

Obviously, you must ensure that the database at the build time has exactly the same schema as your other databases. If not, you will experience some mapping issues.

While the Static Metadata Generation will solve your startup performance issue, you still have a problem with your database, and I strongly suggest that you investigate that.

Note: the first implementation of ActiveJDBC was in 2009 for Humana and we used an Oracle database as well. Our schema at the time was about 120 tables, and ActiveJDBC always started lightning fast.

CodePudding user response：

this issue is not really an activejdbc issue its an oracle 12c issue. Seems reading from the ALL_TAB_COLUMNS table on 12c has bad performance / bad query plan unless you log in as a sysdba. Its not really a good solution but it works for a local docker dev database so I am just setting my user as sysdba. Will find some sql profile someday as a real solution. sysdba cant be used for any prod environment but its fine with me for localhost dev database.

grant sysdba to my_user;

jetty-env.xml:

<New class="oracle.jdbc.pool.OracleDataSource">
  <Set name="URL">jdbc:oracle:thin:@localhost:1521/ORCLCDB.localdomain</Set>
  <Set name="user">my_user as sysdba</Set>
  <Set name="password">my_password</Set>
</New>