oracleperformancejvx

Slow meta data with oracle database

We have an oracle database with several schemes with a lot of tables and grants between the schemes. The initial metadata select of jvx OracleDBAccess, takes about 1 minute. Is there a possibility, to improve the speed of gathering the meta data?

I have a DBStorage, using the OracleDBAccess.

  DBStorage dbsContacts = new DBStorage();
  dbsContacts.setDBAccess(getDBAccess());
  dbsContacts.setWritebackTable("CONTACTS");
  dbsContacts.open();

First usage of the storage takes about 1 minute.

Solution

  • First, be sure that your statement/query is not the problem. It's possible that the query is slow and you think that metadata queries are the problem.

    Simply enable logging for: com.sibvisions.rad.persist.jdbc package

    In your example, if this is the real use-case, the query shouldn't be a problem because it's a select * from CONTACTS (and some joins if your table has foreign keys).

    Next, try to optimize metadata directly in your database:

    purge dba_recyclebin;
DBMS_STATS.gather_dictionary_stats
DBMS_STATS.gather_fixed_objects_stats;
DBMS_STATS.GATHER_SYSTEM_STATS;
DBMS_STATS.GATHER_PROCESSING_RATE;

    Above statemens will cleanup the metadata cache and recalculates statistics.

    Additional information about gather_dictionary_stats, gather_fixed_objects_stats, gather_system_stats

    Also a similar question for Oracle 12c.