Re: Avoiding generating redo logs

From: Galen Boyer <galen_boyer_at_yahoo.com>
Date: 19 Sep 2006 16:49:02 -0500
Message-ID: <uac4v4aks.fsf@rcn.com>

On Tue, 19 Sep 2006, jeremy0505_at_gmail.com wrote:

> In article <ulkog4a9w.fsf_at_rcn.com>, Galen Boyer says...

>> On Mon, 18 Sep 2006, jeremy0505_at_gmail.com wrote:
>> > In article <uejua5rr0.fsf_at_rcn.com>, Galen Boyer says...
>> >
>> >> Why do you need to worry about redo?
>> >>
>> >>
>> > Perhaps I don't - see other responses.
>>
>> I would look at materialized views myself, because, that is really
>> what you are describing (ie, I want to have a query not have to
>> execute everytime, but instead, have it stored so I can get the
>> results quicker) An MV helps solve that in a most impressive fashion.
>>

> This may help actually, yes - however when the user requests the data
> to be refreshed, it needs to be up-to-date rather than based on the
> latest snapshot (am I right that MVs are periodically refreshed rather
> than instantaneously with each transaction that may affect the
> content?).

Well, if you are worried about that, you certainly shouldn't be trying to save your own manufactured snapshot. You will never get that correct while Oracle will always get that correct.

>> But, If you really needed to use a global temp to get away from the
>> redo, then you could do something like the following pseudo-code:
>>
>> IF (p_clientpackage.count_global(client_id) < 1)
>> BEGIN
>> p_clientpackage.insert_global;
>> END
>>
>> p_clientpackage.get_from_global;
>>
>>
>> This would mean you execute the operation for each client, at most,
>> the number of connections you have in the pool. It would also need
>> some boundary questions asked, like, what if the query could return 0
>> rows...

> 
> No problem, thanks for the pointer.
> 

>>
>> Of course, your global temp would need to be "ON COMMIT PRESERVE
>> ROWS".
>>
>>

> As I understand it, this allows the rows to be preserved across 
> transactions NOT across sessions - and as mentioned, a new session is 
> necessarily created for each user-interaction (apache/mod_plsql).

The comments were assuming that the apache/mod_plsql has a connection pool. Then, a new session wouldn't mean a _new_ connection. Just one retrieved from the pool.

-- 
Galen Boyer

Received on Tue Sep 19 2006 - 16:49:02 CDT