RE: Performance in 12.2

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. ☹  

Pete  

From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> On Behalf Of Pete Sharman Sent: Monday, May 21, 2018 09:58 AM
To: andysayer_at_gmail.com; 'ORACLE-L' <oracle-l_at_freelists.org> 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.  

Pete  

From: oracle-l-bounce_at_freelists.org <mailto:oracle-l-bounce_at_freelists.org> <oracle-l-bounce_at_freelists.org <mailto:oracle-l-bounce_at_freelists.org> > On Behalf Of Andy Sayer Sent: Monday, May 21, 2018 08:48 AM
To: ORACLE-L <oracle-l_at_freelists.org <mailto:oracle-l_at_freelists.org> > 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 : 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