Re: Performance of Views

In article <4jgmvi$ajp_at_lal.interserv.net> Paul Power <paul.power_at_rssi.com> writes:
>We are using several views in or database. They include joining 15 tables, 40 columns, and contain
>around 1000 rows. There is a debate going on as to how these views are handled by the database. We
>are accessing these view from reports and our GUI. Do the views get destroyed after use - or do they stay
>in memory until they are pushed out. Are they a performance hit on the database server if several users are
>accesssing them at the same time ?
>Any help is appreciated
>Paul

They stay in memory. If the users are using the exact same SQL, it stays in the shared SQL area, and helps performance. See your server administrator manual about V$LIBCACHE, V$SYSTAT, V$ROWCACHE, and SHARED_POOL_SIZE. You may want to explicitly pin these views in memory (in the library cache).

select sum(pins) "Executions", sum(reloads) "Cache Misses While Executing" from v$librarycache;

If you can run the library cache monitor under the server manager this will all be much clearer.

jg

-- 
Joel Garry               joelga_at_rossinc.com               Compuserve 70661,1534
These are my opinions, not necessarily those of Ross Systems, Inc.   <> <>
%DCL-W-SOFTONEDGEDONTPUSH, Software On Edge - Don't Push.            \ V /
panic: ifree: freeing free inodes...                                   O