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

From: Chris Taylor <christopherdtaylor1994_at_gmail.com>
Date: Tue, 2 Apr 2019 10:10:01 -0500
Message-ID: <CAP79kiQ+BdUo5fiBj9g9AcyosOZLw7vwr1upOW_xKJja8HF4JA_at_mail.gmail.com>



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

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

Original text of this message