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;
And this:
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.
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