Oracle 10g - slow metadata queries

Question

We recently upgraded to 10g and I've noticed that metadata queries seem to take an extraordinarily long time (at a guess 2-5 times slower than on 9i). Has anyone else noticed this? Any suggestions on ways to rectify this problem (settings/patches/incantations)?

Thanks.

Answer 1

If you have access to Oracle Metalink (MyOracleSupport, or whatever they're calling it today), look up bug #2475998, DRAMATIC DROP IN PERFORMANCE USING SYNONYMS VIA SETINCLUDESYNONYMS METHOD. The submitter reported a 60x degradation of performance of DatabaseMetadata.getColumns() when synonyms exist. Oracle recommends getting metadata in some other way e.g. execute a dummy query and get ResultSetMetaData.

Also see Jack Shirazi's Java Performance Tuning, which has a section on performance of obtaining database metadata. (link to Google Books excerpt)

Bottom line is, don't use DatabaseMetadata.getColumns().