Re: Performance in 12.2

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 : https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adfns/plscope.html#GUID-10A7702B-41E4-405D-BFAF-622956A40650

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,
Andrew

--
http://www.freelists.org/webpage/oracle-l