Hi,

I'm in charge of a datawarehouse which we update every weekend. That
means loading lots of tables with

TRUNCATE
INSERT /*+ APPEND PARALLEL */ ... SELECT ... FROM ...
COMMIT

for every table. The volume of the total load is about half to one TB.

Afterwards, when the tables are queried, every now and again an
ORA-01555 happens, sometimes even after days. In my opinion this can
only be because of delayed block cleanout, because loading and then
querying a table is *always* done serially.

If I would load the tables with

DROP TABLE
CREATE TABLE ... AS SELECT ... FROM ...

would the blocks be written pre-cleaned out, thus preventing the
ORA-01555 from happening?

Regards,
Jaap.

On Fri, 08 Apr 2011 21:02:28 GMT, j.w.vandijk.removethis (AT) hetnet (DOT) nl
(Jaap W. van Dijk) wrote:

Oops, forgot the version: 9.2.0.8 on VMS.

On Apr 8, 5:02*pm, j.w.vandijk.removet... (AT) hetnet (DOT) nl (Jaap W. van Dijk)
wrote:

how long does the truncate process take?
How long does the "insert into.. " processing take?
if it takes more than the weekend, then you need to do this
differently.
Have you tried to increase your undo_retention?
Have you tried to add more rollback segments?

ORA-01555 can only be caused by insufficient rollback segments. Make
your transactions MUCH smaller.
A good explanation can be found at: <http://asktom.oracle.com/pls/
asktom/f?p=100:11:0:::11_question_id:1441804355350>

I am not sure I would agree with truncating a"warehouse" on a weekly
basis.?.? From your description, it sounds like the base tables are
also in the same database??? Why? And how does making a copy in these
"warehouse" tables help your overall system performance?

You might consider breaking it into smaller pieces.
insert into ... select * from oldtab where somedatefield between date0
and date1;
commit;
insert into ... select * from oldtab where somedatefield between date1
and date2;
commit;
insert into ... select * from oldtab where somedatefield between date2
and date3;
commit;
insert into ... select * from oldtab where somedatefield between date3
and date4;
commit;
etc...
and start enough of them at a time in parallel where you get the
throughput but don't saturate your I/O or CPU bandwidth - and make
sure your indexing satisfies the "where" clause.


Or, if you are really good at DCL try using exp/imp

NOT TESTED:
exp test1/test1 file = SYS$OUTPUT log = exp_XXX.log tables = XXX
feedback = 1000000 buffer = 40960000 grants = n constraints = n
indexes = n
compress = n direct = y | imp test2/test2 file=SYS$PIPE tables= XXX
rows=y ignore=y

With 10g and above, using impdp you can do this with NETWORK_LINK
without doing the expdp first.

On Apr 8, 5:02*pm, j.w.vandijk.removet... (AT) hetnet (DOT) nl (Jaap W. van Dijk)
wrote:
Hi Jaap,

The next time you see this, check for "transaction table consistent
reads - undo records applied" and see if that is increasing during the
failed query.

On Apr 8, 11:02*pm, j.w.vandijk.removet... (AT) hetnet (DOT) nl (Jaap W. van
Dijk) wrote:
Yes, I think CTAS is the only way of creating "clean" blocks since the
COMMIT SCN is already known as part of the DDL operation, all other
DML operations will generate blocks that need to be cleaned out
afterwards.

If you want to stick to the INSERT approach - what do you do with the
tables after loading - is there any gather statistics job running (I
hope so...).

If yes, and you can afford it, try to gather the statistics using
DBMS_STATS serially (I don't know if ANALYZE does a block cleanout, I
guess it does/has to but I haven't check that recently) which might
pose a problem in terms of runtime with that data volume. If you
gather them in parallel, the direct path read performed by the
parallel slaves can *not* do the block clean out persistently,
although it happens during the processing of the data in the PGA of
the parallel slave, the modified block won't be written back to disk.
If you run the DBMS_STATS serially the SELECT performed will do the
delayed block cleanout.

If gathering statistics serially is too expensive then executing a
serial dummy "SELECT ... FROM TAB" after the load is another idea to
force the delayed block cleanout.

Note all this also applies to the new adaptive serial direct path
reads introduced in 11.1 - since you're still on 9.2 it doesn't matter
but is interesting to know that in case the serial direct read is used
a delayed block cleanout is not written back to disk and has to be
repeated by subsequent queries.

See e.g. http://antognini.ch/2009/07/impact-o...ock-cleanouts/

Regards,
Randolf

Oracle related stuff blog:
http://oracle-randolf.blogspot.com/

Co-author of the "OakTable Expert Oracle Practices" book:
http://www.apress.com/book/view/1430226684
http://www.amazon.com/Expert-Oracle-.../dp/1430226684

As Randolf says,
create table as select ...
clean up the blocks.

The dbms_stats will clean the block after an insert (conventional).

I would add two things to what has been said by Randolf

A table created with insert / * + append * /, has the blocks of the
table
with the "ITL clean" but if there are indexes, the indexes will have
the "dirty ITL, will be
therefore necessary (delayed) block cleanout
select / * + index (.....) * / from table;

because the queries that follow,
if they have a where, probably, will access to the indexes.

Instead, for "Direct Reads"mentioned Randolf,
that happen (probably when it exceeds the threshold
of _small_table_threshold), it is worth adding that,
when Oracle decides to use them, can not do cleanout, the blocks
will be in the PGA, and not SGA. It will be necessary in fact, that
DBWR writes those blocks
on disk, so that the following reading may make the cleanout.

Regards

Donatello Settembrino

On Apr 12, 11:05*am, Donatello Settembrino
<donatello.settembr... (AT) gmail (DOT) com> wrote:
That's a good point about the indexes that I forgot to mention,
although I assumed in this case due to the data volume mentioned the
load is actually done without indexes enabled / created.

Note however that there is a subtle point about the blocks generated
by the direct-path insert: Although the lock bytes on the rows are not
set and do not need to be cleaned out, the commit SCN of the ITL entry
is not known at insert time therefore the database has to look up the
commit SCN (obviously by cleaning out a single block) when
encountering such blocks and therefore I think you can still get a
ORA-01555 error even with table blocks loaded via direct-path insert
if the transaction table slot in the undo segment header has been
overridden in the meantime and Oracle can not determine the commit SCN
otherwise.

Regards,
Randolf

Oracle related stuff blog:
http://oracle-randolf.blogspot.com/

Co-author of the "OakTable Expert Oracle Practices" book:
http://www.apress.com/book/view/1430226684
http://www.amazon.com/Expert-Oracle-.../dp/1430226684

On Apr 13, 1:41*pm, Randolf Geist <mah... (AT) web (DOT) de> wrote:

Hi Randolf,
I do not think that this happens

settembrino@ORA11>select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit
Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

settembrino@ORA11>select name , value from v$parameter where name =
'db_block_size';

NAME VALUE
---------------------------- -----------
db_block_size 16384



I create a table, just a row for block

settembrino@ORA11>create table t (x varchar2(4000), y varchar2(4000),
z varchar2(4000), w varchar2(4000));

Table created.

I display the current SCN of the moment

settembrino@ORA11>SELECT to_char(CURRENT_SCN) current_scn FROM V
$DATABASE;

CURRENT_SCN
----------------------------------------
39099538717

settembrino@ORA11>insert /*+ append */ into t
2 select lpad('x', 4000, 'x'), lpad('y', 4000, 'y'), lpad('z',
4000, 'z'), lpad('w', 4000, 'w')
3 from dual
4 connect by level <= 100;

100 rows created.

The following select, confirm the use of direct-path insert

settembrino@ORA11>select * from t;
select * from t
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

I view the scn, before committing

settembrino@ORA11>SELECT to_char(CURRENT_SCN) FROM V$DATABASE;

CURRENT_SCN
----------------------------------------
39099538765

settembrino@ORA11>commit;

Commit complete.

displays the SCN, after the commit

settembrino@ORA11>SELECT to_char(CURRENT_SCN) FROM V$DATABASE;

CURRENT_SCN
----------------------------------------
39099538768


if you look at the SCN of rows in the table (note that this is the
first query on the table)

settembrino@ORA11>set autotrace on;
settembrino@ORA11>select to_char(min(ora_rowscn)),
to_char(max(ora_rowscn)) from t ;

TO_CHAR(MIN(ORA_ROWSCN)) TO_CHAR(MAX(ORA_ROWSCN))
----------------------------------------
----------------------------------------
39099538766 39099538766


...
...
...


Statistics
----------------------------------------------------------
9 recursive calls
1 db block gets
171 consistent gets
100 physical reads
168 redo size
295 bytes sent via SQL*Net to client
251 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed


SCN in the table is a number > of that before committing (39099538765)
and <= of that immediately after the commit.
Which means that Oracle is successful attributing the SCN blocks of
the table, and without running (delayed)
block cleanout (only 168 bytes of redo.)

If I create a table as the previous test but with 1000 rows instead of
100 rows, I get the same number of bytes of redo

settembrino@ORA11>insert /*+ append */ into t
2 select lpad('x', 4000, 'x'), lpad('y', 4000, 'y'), lpad('z',
4000, 'z'), lpad('w', 4000, 'w')
3 from dual
4 connect by level <= 1000;

.. .
.. .
.. .

Statistics
----------------------------------------------------------
9 recursive calls
1 db block gets
1073 consistent gets
1000 physical reads
168 redo size
295 bytes sent via SQL*Net to client
250 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

as I said, always 168 bytes of redo but this time I have 1000 physical
reads
The insert in direct-path bypass the buffer cache and writes above HWM

It is different for an insert in conventional mode

settembrino@ORA11>drop table t purge;

Table dropped.

settembrino@ORA11>create table t (x varchar2(4000), y varchar2(4000),
z varchar2(4000), w varchar2(4000));

Table created.

settembrino@ORA11>insert into t
2 select lpad('x', 4000, 'x'), lpad('y', 4000, 'y'), lpad('z',
4000, 'z'), lpad('w', 4000, 'w')
3 from dual
4 connect by level <= 100;

100 rows created.

before reading, flush the buffer cache
settembrino@ORA11>alter system flush buffer_cache;

System altered.

settembrino@ORA11>commit;

Commit complete.

The following select will have to delayed block cleanout, because now
the blocks
are on disk


settembrino@ORA11>set autotrace on;
settembrino@ORA11>select count(*) from t ;

COUNT(*)
----------
100


Execution Plan
----------------------------------------------------------

--------------------------------------------------------



Statistics
----------------------------------------------------------
10 recursive calls
1 db block gets
304 consistent gets
127 physical reads
7376 redo size
224 bytes sent via SQL*Net to client
250 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

the amount of redo generated, should confirm that delayed block
cleanout occurs

Regards

Donatello Settembrino

On Apr 14, 4:58*pm, Donatello Settembrino
<donatello.settembr... (AT) gmail (DOT) com> wrote:
I depends on what you exactly mean by "this" means. Note I didn't say
that delayed block cleanout will happen in the same fashion as for
conventional DML, but you simply can take some block dumps after the
direct-path insert and you'll see that there is no commit SCN
available from the ITL slots, so the block is not really "clean".

If you check the session statistics of your session running the query
after committing the direct-path insert you'll notice that the first
time the session accesses a block from the table it will perform a
block cleanout and generate redo for that single block, from then on
it will re-use the "cached" commit SCN for accessing / processing the
remaining blocks.

The corresponding statistics are:
cleanouts only - consistent read gets (increases by one after first
access in the session)
Commit SCN cached (increases depending on the number of blocks
accessed)

That is also the explanation why you don't see a difference in the
amount of redo generated if you use a larger table - it will still do
a cleanout of a single block to determine the commit SCN.

But you're right that my explanation above was not really sound
regarding direct-path inserts and cleanout since running a query
afterwards does not really clean out every block.

Regards,
Randolf

On Fri, 08 Apr 2011 21:02:28 GMT, j.w.vandijk.removethis (AT) hetnet (DOT) nl
(Jaap W. van Dijk) wrote:

Hi guys,

What an in-depth discussion! Thank you all for increasing my
knowledge. As far as I know this stuff is not easily found, not even
at the Oracle support site. I make good use of it.

On thing is still puzzling me: to prevent an ORA-01555 due to delayed
block cleanout, in case of the rollback transaction slot being
overwritten, in Note 40689.1 on the Oracle Support site the following
is recommended:

alter session set optimizer_goal = rule;
select count(*) from table_name;

for the same reason for which Randolf Geist recommends gathering
statistics I guess, forcing a visit and cleaning out all the blocks.
But why won't this run into the same ORA-01555? Or is discovering that
the rollback transaction slot isn't there anymore somehow enough when
doing a count(*) or when gathering statistics?

Regards,
Jaap