Re: Temporary table use and the redo log
Date: Wed, 9 Sep 2009 13:20:18 -0700 (PDT)
Message-ID: <9c643aad-1a75-48ed-bc52-07ea406977ee_at_g23g2000yqh.googlegroups.com>
On Sep 9, 7:44 pm, "codefragm..._at_googlemail.com" <codefragm..._at_googlemail.com> wrote:
> On 9 Sep, 17:34, joel garry <joel-ga..._at_home.com> wrote:
> Turns out it didn't work :-(
> I tried it using a noddy example, adding 1 million rows into a
> temporary table. Great, no redo log activity. Tried it in the actual
> problem stored procedure
> and still got a lot of redo logs. No idea why. In the end every insert
> statement had the /* +append hint around it, all in autonomous
> transactions so they could be queries from later, commit at the end,
> redo use growing and growing...
>
> My guess is that its a hint, not a promise
Please check above mentioned point: You need to verify that the inserts are actually direct-path inserts in your "actual problem stored procedure". You can do so by using above technique of querying/ accessing the table after inserting before committing. If this *doesn't* fail with an ORA-12838, then you've performed a conventional insert. The "APPEND" hint will be ignored if any of the restrictions apply for direct-path inserts.
Here are two links that describe the most common restrictions for direct-path inserts:
http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_9014.htm#i2163698
http://download.oracle.com/docs/cd/B28359_01/server.111/b28313/usingpe.htm#CACEJACE
By the way, who tells you that the inserts are causing the redo generation? There might other DML/DDL going on that generates redo. You could query V$SESSTAT for the "redo size" statistic after each step of your procedure, something like the following possibly wrapped into a procedure, note that you need select privileges directly granted to the code owner on V$SESSTAT and V$MYSTAT to do that from a definer's rights stored procedure:
select
value
from
v$sesstat
where
statistic# = 134
and sid = (
select
sid
from
v$mystat
where
rownum = 1
)
You obviously need to calculate the delta to the previously obtained value to determine the redo generated by the particular step. This would tell you which step of your procedure generated what amount of redo.
To be honest, I don't get your approach of using an autonomous transaction... The description you've made doesn't fit with directpath inserts I think:
- start a (autonomous) transaction,
- load the temporary table up,
- do my selects -> at this point the select should fail with an ORA-12838 if a directpath insert was used to "load up" the temporary table
- commit the transaction
I second the point that it seems to be odd that you're so keen on avoiding redo generation. What is exactly your objective, what are you trying to achieve? Is this a performance issue, a space constraint with archive logs etc. etc.?
Regards,
Randolf
Oracle related stuff blog:
http://oracle-randolf.blogspot.com/
SQLTools++ for Oracle (Open source Oracle GUI for Windows):
http://www.sqltools-plusplus.org:7676/
http://sourceforge.net/projects/sqlt-pp/
Received on Wed Sep 09 2009 - 15:20:18 CDT