RE: Performance in 12.2

Sorry guys, got sucked off into something else. :(  

Well, with some application logging we’ve been able to find the cause if not the resolution. The application does a bunch of DDL, which includes creating triggers about 10 times. The create trigger used to complete in 12.1.0.2 in about 1/100th of a second. Immediately on switching to 12.2, the create trigger statement takes about 4 to 6 seconds, which is a hell of a blowout. I traced the operation and found the problem was it is selecting from PLSCOPE_ACTION$ via a full table scan. That table has around 15 million rows in it, and that’s where all the time is spent (relatively speaking, that is - all the other recursive SQL flies through). Why that should be a problem as of 12.2, I really don’t know at this stage. Anyone understand what that table is actually used for?  

Thanks!

Pete

From: Seshi Rao <seshi.bby_at_gmail.com> Sent: Saturday, May 12, 2018 06:34 PM
To: peter.sharman_at_westnet.com.au
Cc: ORACLE-L <oracle-l_at_freelists.org> Subject: Re: Performance in 12.2  

Hi.  

What is the IS?... did you get a chance to check with network team ?  

On Mon, May 7, 2018, 4:38 AM Pete Sharman <peter.sharman_at_westnet.com.au <mailto:peter.sharman_at_westnet.com.au> > wrote:

Is anyone aware of performance issues in 12.2 as opposed to 12.1, in particular related to the speed of creating tablespaces, schemas, tables and PL/SQL objects? Trying to see why those operations might be running slower in 12.2. Looking at AWR/ASH reports, the system is running flat strap on CPU when recompiling large numbers of PL/SQL objects (as I would expect), but no waits etc. that I can determine to be problematic. Just wondering if there is an issue around that which I have not been aware of yet.  

Not a lot of detail there I know, but this particular site is rather hard to get information out of – just a perceived slow down in the same operations after upgrade to 12.2. ☹  

Pete

--
http://www.freelists.org/webpage/oracle-l