Re: Non-caching of SQL queries

On Fri, 30 Nov 2007 17:23:33 -0800 (PST), Peter Teoh <htmldeveloper_at_gmail.com> wrote:

>On Dec 1, 1:42 am, DA Morgan <damor..._at_psoug.org> wrote:
>> And how would caching them do anything other than waste memory?
>
>Thanks for the reply.
>
>Erh....not sure why u ask that question. If my batch script is
>running into millions of SQL, wouldn't it be faster if more cache is
>available for transaction, as the caching of this SQL is competing for
>the actual processing of the SQL. As far as I am aware of, the
>SQLAREA's cache is to facilitate minimize parsing of the SQL. But if
>the SQL is executed only once, and almost every subsequent SQL need to
>be reparsed again, then I supposed it is not necessary to hog the
>memory with these SQL. The only way I can flush the SQL from SQLAREA
>currently, is to issue DDL statements against the underlying
>structures (be it a view, or table etc) and immediately all the
>previous SQL in SQLAREA is intelligently removed. This is because
>the next SQL will need to be parsed again anyway.
>
>Thank you.

You are confusing the buffer cache and the shared pool. Caching sql statements will not impact 'the cache available for transaction in anwyay'.
Tom Kyte has written much about the advisibility to cache SQL, if you wrote your SQL in such a fashion you use hardcoded literals all over the place, you did something fundamentally wrong.

As far as not caching SQL: just flush the shared pool after every statement and notice the performance hog.

-- 
Sybrand Bakker
Senior Oracle DBA