RE: Performance in 12.2

From: Pete Sharman <>
Date: Mon, 21 May 2018 09:58:01 +1000
Message-ID: <035b01d3f096$63911710$2ab34530$>

Thanks Andy. Yeah, not much I can do about the application code unfortunately.  

I did look into the PL/Scope side of things, but all the PL/SQL in this application has PLSCOPE_SETTINGS='IDENTIFIERS:NONE', so I’m not sure why there is information there anyway.  

I also checked in with Steve and Bryn, and Bryn wanted a test case with a virgin database. From my understanding, a virgin database would have zero rows there (like yours) so I pushed back on that request as there would be no performance issue then!  

I’ll double check the index entries when I can get back onto the environment.  

Does anyone know WHY that data needs to be there and if there is a supported way (i.e. not TRUNCATE TABLE plscope_action$!) of purging that table? Seems the data there is of little to no use to the customer right now.  


From: <> On Behalf Of Andy Sayer Sent: Monday, May 21, 2018 08:48 AM
To: ORACLE-L <> Subject: 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 :  

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 - 01:58:01 CEST

Original text of this message