Re: Performance of Views

Joel Garry wrote:
>
> 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

All true, but don't forget to mention that views in general are slow. since every query on them is actually a query on a query. You might not have any performance problems if the view is based on a simple query, but when joining more than a few tables, you might find it takes some time for Oracle to come back with an answer. I tried making two views on a table, and a third joining these two views. After starting this transaction, I broke it of after about ten minutes. Having rewritten the transaction to make use of temp tables instead of views, the transaction took less than a minute to complete.

Roald.

-- 

+-------------------------------------------------------+
| R.A. van Geleuken                     roald_at_xs4all.nl |
+-------------------------------------------------------+
| There's no such thing as impossibility, only          |
| extremely low chance                                  |
+-------------------------------------------------------+