Performance issue query select obj#, name.. from rmtab$ in 12c
Lot of execution and parsing for the following query caused performance issue in 12c database
select obj#, name, stab#, sobj#, sobjd#, ttab#, tobj#, tobjd#, mflags from rmtab$ where (stab# = :1 or ttab# = :1) order by obj#
In statspack report, we find high hard parsing on checking following query is caused high parsing in top queries. It is related on bug in 12c Bug 20853821. Following query executed with every module its occurred in 12.1.0.2 version of oracle as doc id(20853821.8)
% Total Old Parse Calls Executions Parses Hash Value ------------ ------------ -------- ---------- 111,393 111,408 6.40 1512023764 select obj#, name, stab#, sobj#, sobjd#, ttab#, tobj#, tobjd#, m flags from rmtab$ where (stab# = :1 or ttab# = :1) order by obj#
To overcome the execution problem of the query in 12.1.0.2 version.
We need to apply the latest patches of jan 2017 released. Oracle released bug fixed in Jan Patch 12.1.0.2.170117 (Jan 2017) Bundle Patch.