I have a strange (to me) performance issue. Based on the schema that runs the query I received very different performance. Here's my setup.
Oracle 12c DB SAMPLE schema/user WIDGET table (300,000 rows/10 columns of varchars, nvarchars, dates, and numbers) USER1 schema/user USER2 schema/user DBA1 schema/user --SELECT granted on WIDGET table to USER1 and USER2. --DBA1 already has access to all table accross all schemas and has many more privs.
When I execute either of the following:
select * from SAMPLE.WIDGET; select count(1) from SAMPLE.WIDGET;
It takes about 0.5 seconds in SAMPLE schema or DBA1 schema. It takes about 10 seconds in USER1 and USER2 schema.
So I'm looking for the following:
Under what condition(s) can query performance in Oracle differ based on the user you are logged in to when you execute a query?