Env:
Exadata Cloud @ 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
