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