RE: Performance in 12.2

From: Pete Sharman <>
Date: Mon, 4 Jun 2018 10:40:51 +1000
Message-ID: <05f301d3fb9c$b28ab0e0$17a012a0$>

So for those that wondered where this might have ended, the client in question had a replica database that was about to be rebuilt. I truncated all four PLSCOPE tables and the performance issue went away. I still have no understanding of why those tables were even populated since PLSCOPE_SETTINGS was set to 'IDENTIFIERS:NONE'. We’ll monitor the tables and set up a job to do the truncation regularly if they start being populated again.  

For this particular client I do not have access to their CSI, so I am unable to log it as a bug. ☹  


From: <> On Behalf Of Pete Sharman Sent: Monday, May 21, 2018 09:58 AM
To:; 'ORACLE-L' <> Subject: RE: Performance in 12.2  

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 Jun 04 2018 - 02:40:51 CEST

Original text of this message