Difference in performance using ALL_* vs. USER_* tables

An interesting discussion on the Oracle-L mailing list recently highlighted the differences between using a query such as this;

SELECT COUNT(1) FROM DUAL WHERE EXISTS (SELECT 1 FROM ALL_SYNONYMS WHERE SYNONYM_NAME = :B1 AND OWNER = 'USER');

And this:

SELECT COUNT(1) FROM DUAL WHERE EXISTS (SELECT 1 FROM USER_SYNONYMS WHERE SYNONYM_NAME = :B1);
 I assumed, naively perhaps, that there wouldn't be that much difference, but how wrong could I be...?

Turns out, very much so!  This is the output of autotracing those two queries with a buffer cache flush in between just to be sure there wasn't any untoward advantage in running one first.  Left column is ALL_ and the right column is USER_.  I've highlighted what appear (to me!) to be the biggest differences.

buffer is pinned count 1 3
buffer is not pinned count 250 66
bytes received via SQL*Net from client 464 441
bytes sent via SQL*Net to client 24957 24960
calls to kcmgcs 29 42
calls to get snapshot scn: kcmgss 19 23
cell physical IO interconnect bytes 0 311296
cluster key scans 0 8
cluster key scan block gets 0 8
cleanout - number of ktugct calls 11 3
cleanouts only - consistent read gets 11 3
commit txn count during cleanout 12 3
CPU used by this session 41 1
consistent gets from cache 2322 84
consistent gets - examination 114 45
consistent gets 2322 84
consistent changes 11 3
DB time 56 32
CR blocks created 11 1
CPU used when call started 42 1
cursor authentications 2 0
execute count 19 23
file io wait time 0 301153
enqueue requests 1 1
enqueue releases 1 1
free buffer requested 11 39
immediate (CR) block cleanout applications 11 3
index scans kdiixs1 75 14
index fast full scans (full) 2 0
index fetch by key 60 11
logical read bytes from cache 19021824 688128
no work - consistent read gets 2164 35
min active SCN optimization applied on CR 33 6
non-idle wait count 5 43
opened cursors cumulative 19 21
non-idle wait time 0 30
physical read total bytes 0 311296
physical read IO requests 0 38
physical reads 0 38
physical reads cache 0 38
physical read total IO requests 0 38
parse count (total) 4 1
parse count (hard) 1 1
physical read bytes 0 311296
recursive cpu usage 15 1
recursive calls 110 145
rows fetched via callback 60 2
Requests to/from client 3 3
session cursor cache hits 15 22
session logical reads 2322 84
session cursor cache count 1 -1
shared hash latch upgrades - no wait 9 22
session pga memory -262144 131072
sorts (memory) 5 14
sorts (rows) 1344 1288
user calls 5 5
user I/O wait time 0 30
workarea executions - optimal 23 10
SQL*Net roundtrips to/from client 3 3
table fetch by rowid 90 17
table scan blocks gotten 359 0
table scan rows gotten 60987 0
table scans (short tables) 5 0

Any comments?  Is this blindingly obvious and I've just not had enough coffee yet to realise?

Comments

Popular posts from this blog

Data Guard with Transparent Application Failover (TAF)

RMAN-05531 During RMAN Duplicate from Active Data Guard Standby

Data pump - "ORA-39786: Number of columns does not match between export and import databases"