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

Post a Comment

Popular posts from this blog

APEX, SERT, and EPG

APEX, SERT, and EPG Application Express, Security Evaluation and Recommendation Tool, and the Embedded PL/SQL Gateway My organisation has only just started seriously working with APEX and have recently launched our first internal production app (that was a challenge... I was told that this app needed to be in production in 2 days and nobody had bothered to check with me what was needed to make this happen).  I've learned a lot about APEX in the past few weeks and I've got a lot more to go. I recently came across SERT ( https://github.com/OraOpenSource/apex-sert ) and it sounds like a really nifty tool.  Today I found some time to try it out and see if our dev team might find it useful.  I decided to install it in our dev database.  This uses the EPG rather than ORDS. The install didn't go so well.  The first install failed near the end because I think I used a lowercase 'y' for a response somewhere and it got confused, bombing out with an ORA-06502...
Read more

Error upgrading 11.2.0.3 to 11.2.0.4: ORA-00942 & ORA-06512

During an upgrade of an 11.2.0.3 DB to 11.2.0.4 using DBUA, this error popped up: Errors - Oracle Workspace Manager . ORA-00942: table or view does not exist . ORA-06512: at "WMSYS.OWM_MIG_PKG", line 1575 . ORA-06512: at "WMSYS.OWM_MIG_PKG", line 1592 . ORA-06512: at line 1 . VALID 11.2.0.1.0 00:00:39 This error is referenced in MOS note  1051991.1 and can be ignored: If DBA_REGISTRY shows the status of (in this case) Workspace Manager is VALID, then this error is safe to ignore. Click Ignore and the upgrade will continue.
Read more

Data Guard with Transparent Application Failover (TAF)

One of the problems with Data Guard is that even after switching/failing over to the standby database, all clients need to be reconfigured to communicate with the new primary. The simplest way to achieve this (if using a centralised tnsnames.ora ), is to modify the file and  add the line " (FAILOVER=YES) ",  and either multiple ( DESCRIPTION=...) or (ADDRESS=...)  lines, one for each of the standby systems, in the order that the client should attempt connections . This tells the client to try each address in turn until one succeeds. Doing so provides basic failover functionality within Oracle Net, and will probably function correctly most of the time.  Though be aware - because both primary and standby can share the same instance name (e.g. the same SID, therefore the same connection string), there is potential for this simple scheme to go wrong. An example in an active data guard environment (where the standby is open read only): a temporary network issue cau...
Read more