Re: Performance in 12.2

From: Andy Sayer <>
Date: Sun, 20 May 2018 23:48:29 +0100
Message-ID: <>

Hi Pete,

Obviously doing DDL including creating triggers frequently is not a recipe for performance, but it seems you were getting by before so let's leave it at that.

Plscope_action$ is part of PL/Scope. There are some new features to this in 12.2 which essentially allows you to search for certain types of SQL statements in your PL/SQL , have a read here :

In my sandbox instance, this table is empty so the impact is minimal. However, looking through my own SQL trace for creating a before update trigger, I can see a few statements being called. Checking the indexes on the table (just one on obj#, signature, action) it looks like this SQL probably would cause problems:

select pi.obj# from plscope_identifier$ pi where pi.type# in (20, 39, 55) minus select pi.obj# from plscope_identifier$ pi, plscope_action$ pa

 where pa.signature = pi.signature and         pi.type# in (20, 39, 40, 55)
and         pa.action = 4 and pa.obj# != :1

Side note: I get an index skip scan because my stats know the table is empty. If you have many objects that have been compiled with plscope enabled, it's easy to see why a full table scan would be preferred.

I have absolutely no idea why this particular SQL would be called from a create trigger, it doesn't have any real driving filters which is red flag - you would think that it should be directly related to the object you are creating. Even a well thought out index is going to have problems here. Have you had a search in MOS for this? I would probably raise an SR if it's causing problems, it doesn't seem right.

Hope that helps,

Received on Mon May 21 2018 - 00:48:29 CEST

Original text of this message