RE: Performance in 12.2

Do my eyes deceive me or is there no filter on user in that select? So a different user (including sys and system) could affect your user?  

That does not seem right, but my coffee is still brewing. Perhaps listing owner, object type and object name for the obj#(s) found in these two tables would be useful as a diagnostic.  

mwf  

PS: I still think source$ should be partitioned by owner and users should be able to supply tablespace name by owner and edition for source other than sys and system, but that is completely based on paranoia regarding dictionary size of some COTS applications and the notion you should be able to de-pollute source$ if you drop a nasty user. Perhaps that applies to these tables as well.  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Pete Sharman Sent: Sunday, May 20, 2018 7:58 PM
To: andysayer_at_gmail.com; '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.  

Pete  

From: oracle-l-bounce_at_freelists.org <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> 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