Re: R: Public variables

<davide_at_yahoo.com> wrote in message
news:9sbe1b$12ker4$1_at_ID-18487.news.dfncis.de...
> Fabio GRANDE <fabiog_at_fileita.it> wrote:
> > I think that putting those values in variables I could improve the DB
> > performances... Is it right ?
>
> Not completely. If you access some data that resides into a table,
> those data will be copied in the server's memory (in the Shared Pool),
> so the second time you are (effectively) reading from memory.

No. This just wrong. Data blocks get read into the buffer cache. sql pl/sql etc gets read as you say into the shared pool. The one does not affect the other (except in so far as they are allocated from server RAM). I think this misuderstanding leads you to say
>
> But, if you clutter the server's memory with variables, you will force
> the server to clean the buffers used to store data to keep your
> variables, so you will slow down query performances (all queries).
> Worse, if the shared pool become too full of variables, the system will
> start swapping to disk, slowing down way more.

No. If the shared pool gets full it will age out query plans etc perhaps forcing reparses. Itr will not affect the rate of aging out of data blocks. In addition since the shared pool is a fixed size - filling it up wiill not induce swapping - though sizing it wrongly will.

> If you want to improve your performance, start tuning your system
> (aka: give enough buffer and enough memory to the server), then check
> if your database structure can be improved (extents, tablespaces...),
> then move to your queries (creating/using indexes, kind of queries...).

I also think that this is in the wrong order. You should definitely be tuning sql before tuning storage. Arguably you should be tuning sql before tuning memory allocation , though my personal view on this is that the both should be done together.

So to reply further to the original poster I agree that you should look at the query plans for the queries that access these lookup values. they maybe horribly inefficient. You could also look (8i & up) at allocating the lookup tables to the keep buffer pool. If and only if you cannot successfully tune the actual sql then you could consider the use of global variables that Jonathan describes. global; variables do have a use - but it isn't the same as looking up values from a lookup table.

just my opinion

--
Niall Litchfield
Oracle DBA
Audit Commission Uk