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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Pete Sharman
Sent: Sunday, May 20, 2018 7:58 PM
To: andysayer@xxxxxxxxx; '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@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> On Behalf
Of Andy Sayer
Sent: Monday, May 21, 2018 08:48 AM
To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
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
