Re: APEX_PUBLIC_USER + Library Cache Pin/Library Cache Lock Problem (Grants Mystery too!)

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Tue, 2 Apr 2019 11:35:01 -0400
Message-ID: <f5c0215a-c6c8-af09-395c-02b52c79d563_at_gmail.com>



Have you tried locking the APEX procedures in memory using DBMS_SHARED_POOL.KEEP? That is the usual way of dealing with library latches.

Regards

On 4/2/19 11:10 AM, Chris Taylor wrote:
> Env:
> Exadata Cloud _at_ Customer
> RAC DB: 12.1.0.2  (Multi-tenant)
>
> Ok team, I'll be the first to admit I don't know a lot about APEX and
> how it plays with the database.
>
> We have a query that runs from a middleware server where the page is
> built in APEX and connects to the DB as APEX_PUBLIC_USER to execute
> queries.
>
> This one query (which is really ugly and annoying)  involves SEVERAL
> views from another schema like so:
>
> (Owners & Names changed to protect the innocent)
>
> USERNAME.VIEW_1
> USERNAME.VIEW_2
> USERNAME.VIEW_3
>
> The APEX_PUBLIC_USER session goes into a spin on library cache
> lock/library cache pin waits when trying to run this particular query.
>
> I spent all day yesterday trying to tune the query from my DBA account
> and couldn't figure it out.
>
> Today I logged in as the APEX_PUBLIC_USER account and I figure out
> that there are NO grants on these views to ANYONE.  Yet, the query
> will eventually return when executed from the middleware server.
>
> The query will error if I login as APEX_PUBLIC_USER directly with
> "Object does not exist" (because no grants).
>
> Gathering FIXED_OBJECT_STATISTICS & GATHER_DICTIONARY_STATS seems to
> fix the problem but I'm super confused about how this query can even
> run when its executing as APEX_PUBLIC_USER when it doesn't have
> privileges on the VIEWs used by the query.
>
> TL;DR:
> 1. Gathering FIXED_OBJECT_STATISTICS & GATHER_DICTIONARY_STATS helps
> resolve the library cache lock/library cache pin spins and query returns
> 2.  How does APEX_PUBLIC_USER get the necessary rights to the Views if
> there are no grants on the views in the database?
>
> Chris Taylor
>

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217


--
http://www.freelists.org/webpage/oracle-l
Received on Tue Apr 02 2019 - 17:35:01 CEST

Original text of this message