Oracle 10g - slow metadata queries
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.
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().
精彩评论