Query Optimization: DBMS_METADATA.GET_DDL (Oracle)

Question

I want to get all table definitions for all my tables. And I want to do it fast (this is part of the script that I run a lot)

I am using oracle 11g and I have 700 tables. Simple jdbc code takes 4 minutes and does:

s = con.statement("select DBMS_METADATA.GET_DDL(object_type,object_name) from user_objects where object_type = 'TABLE');
s.execute();
rs = s.getResultSet();
while(rs.next()){
 rs.getString(1);
}

SO I want to optimize this code and reach about 20 seconds.

I have already reached 40-50 seconds by creating 14 threads, each of which opens a connection to the database and reads part of the information using mod on rownum.

But this is not enough.

I think in these areas:

• http://docs.oracle.com/cd/B10501_01/java.920/a96654/connpoca.htm#1063660 - connection caching. can this speed things up by replacing my 14 connections with connectionCaching?

• Is it possible to maintain access to tables with this function in the cache cache area of ​​KEEP?

• , ?

• .

Answer 1

, . GET_DDL Java XSLT .

, . http://metacpan.org/pod/DDL::Oracle

Answer 2

DDL, ? DDL , ALL_OBJECTS.LAST_DDL_TIME .

- GET_DDL , .

Answer 3

First of all, I would like to offer the HAL just for making changes, but I would also look at eliminating any parameters that I don't need - for example, the STORAGE clauses?