Performance issue query select obj#, name.. from rmtab$ in 12c

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s