opt_param to the rescue

It's been a few weeks since my last blog entry - work has been crazy with all things cloud and I've not had the time (or the energy) to write anything up.

Just a short post today on something that has been probably mentioned by a few people over the last few years but i think it's worth covering again as it helped us today.

We've just upgraded a 11.2 system to 12.1 and we have a few dictionary queries that are performing badly - as an example we have something like this

SELECT '', c.owner, c.synonym_name, decode(a.uniqueness, 'UNIQUE', 0, 1), a.owner, a.index_name, 3, b.column_position, b.column_name, 'A', a.distinct_keys, a.leaf_blocks, NULL
FROM ALL_INDEXES a, ALL_IND_COLUMNS b, ALL_SYNONYMS c
WHERE c.synonym_name='OBJECT_NAME_HERE' AND c.owner='SCHEMA_NAME_HERE' AND c.table_name = a.table_name AND c.table_name = b.table_name AND c.table_owner= a.table_owner AND c.table_owner= b.table_owner AND a.owner = b.index_owner AND a.index_name = b.index_name

This is due to the changes in a lot of the dictionary views that came in at 11g, there are specific notes related to this in MOS

At 11g we'd bodged the fix by globally setting optimizer_features_enabled to 10.2.0.5 as in 11g other SQL was affected and this was the simplest fix.

At 12c this query seems to be the only one that is slow with optimizer_features_enabled set to 12c. We'd prefer of course to have the value set to 12c to take advantage of the cleverer optimizations Oracle have added since 10g. So to just 'fix' this one query we can make use of the opt_param hint as follows

SELECT /*+ opt_param('optimizer_features_enable','10.2.0.5')*/ '', c.owner, c.synonym_name, decode(a.uniqueness, 'UNIQUE', 0, 1), a.owner, a.index_name, 3, b.column_position, b.column_name, 'A', a.distinct_keys, a.leaf_blocks, NULL

FROM ALL_INDEXES a, ALL_IND_COLUMNS b, ALL_SYNONYMS c

WHERE c.synonym_name='OBJECT_NAME_HERE' AND c.owner='SCHEMA_OWNER_HERE' AND c.table_name = a.table_name AND c.table_name = b.table_name AND c.table_owner= a.table_owner AND c.table_owner= b.table_owner AND a.owner = b.index_owner AND a.index_name = b.index_name

There is more than one way to do this kind of thing of course but this works for us in this situation and is a very 'visible' modification.

This kind of query is very common in odbc drivers where they are building an object definition to be used in the driver - in that case you don;t really have control of the code and other solutions would need to be looked into.