GeoMedia Q&A

GeoMedia connection performance to Oracle 12C seems to be significantly degraded

on (1,447 Views)

Symptoms

After upgrading to Oracle 12C, GeoMedia connections are much slower.

Diagnosis

Versions of GeoMedia prior to version 2016, build 10267, may exhibit a connection performance problem for Oracle 12C warehouse connections. GeoMedia had to modify the way SQL statements were submitted to take advantage of an Oracle 12C enhancement that changed the dictionary view for ALL_OBJECTS.

Changes in GeoMedia were implemented that should improve connection performance issue through the use of bind variables for certain SQL select statements. The changes were made in the GeoMedia versions listed below:

GeoMedia Connection Type	GeoMedia Version Implemented
Oracle Object Model Read-Only	16.00.0000.10267
Oracle Object Model Read-Write	16.00.0000.10267
Oracle Object LTT Read-Write	16.00.0000.10271

GeoMedia also delivers an optional .sql script that may help improve GDOSYS metadata interactions with the GFEATURES view. The script is recommended only for Oracle 12C databases. Look for NewGFeaturesView.sql located in the C:\Program Files (x86)\Common Files\Intergraph\GeoMedia\Scripts folder.

Solution

Ensure that you are using GeoMedia 2016 build 10267 or higher. Consider running the NewGFeaturesView.sql provided with GeoMedia.

Other Considerations:

Ensure Oracle 12C installation is on the latest patches. If upgrading to Oracle 12C, install version 12.1.0.2.

Oracle 12.1.0.1

At a minimum, you need to install p6880880 for Opatch
To fix problems with spatial filtering, install p18069284_1210104. Later patches are OK as well.

Oracle 12.1.0.2:

Issues noted for 12.1.0.1 have been corrected.

Statistics:

For both 11g and 12c, database statistics are very important and must be up to date. Set up Oracle jobs for the following:

Run Weekly during non-peak hours (run nightly if data changes frequently)

DBMS_STATS.gather_database_stats; -- Gathers complete database statistics
DBMS_STATS.gather_dictionary_stats; -- Gathers statistics on SYS, SYSTEM, and other dictionary schemas

Run Occasionally

DBMS_STATS.gather_system_stats; -- Gathers host system OS statistics.

Run Rarely (after patches or other updates to the database instance)

DBMS_STATS.gather_fixed_objects_stats;
Local Statistic Gathering - Run as needed for high use schemas, particularly those undergoing lots of changes.
DBMS_STATS.gather_schema_stats (USER, CASCADE=>TRUE);
DBMS_STATS.gather_table_stats(USER, 'TABLE', CASCADE=>TRUE);
DBMS_STATS.gather_index_stats(USER, 'INDEXNAME');