Re: Preventing delayed block cleanout due to loading a datawarehouse
Date: Thu, 14 Apr 2011 07:58:19 -0700 (PDT)
Message-ID: <6cdd8235-b880-4a41-b9d5-5821e77c031e_at_n2g2000prj.googlegroups.com>
On Apr 13, 1:41 pm, Randolf Geist <mah..._at_web.de> wrote:
> On Apr 12, 11:05 am, Donatello Settembrino
>
> <donatello.settembr..._at_gmail.com> wrote:
> 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/1430226684http://www.amazon.com/Expert-Oracle-Practices-Database-Administration...
Hi Randolf,
I do not think that this happens
settembrino_at_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_at_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_at_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_at_ORA11>SELECT to_char(CURRENT_SCN) current_scn FROM V $DATABASE; CURRENT_SCN
39099538717
settembrino_at_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_at_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_at_ORA11>SELECT to_char(CURRENT_SCN) FROM V$DATABASE;
CURRENT_SCN
39099538765
settembrino_at_ORA11>commit;
Commit complete.
displays the SCN, after the commit
settembrino_at_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_at_ORA11>set autotrace on;
settembrino_at_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_at_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_at_ORA11>drop table t purge;
Table dropped.
settembrino_at_ORA11>create table t (x varchar2(4000), y varchar2(4000), z varchar2(4000), w varchar2(4000));
Table created.
settembrino_at_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_at_ORA11>alter system flush buffer_cache;
System altered.
settembrino_at_ORA11>commit;
Commit complete.
The following select will have to delayed block cleanout, because now
the blocks
are on disk
settembrino_at_ORA11>set autotrace on;
settembrino_at_ORA11>select count(*) from t ;
COUNT(*)
100
Execution Plan
| Id | Operation | Name | Rows | Cost (%CPU)|
| 0 | SELECT STATEMENT | | 1 | 42 (0)| | 1 | SORT AGGREGATE | | 1 | | | 2 | TABLE ACCESS FULL| T | 113 | 42 (0)| --------------------------------------------------------
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 Received on Thu Apr 14 2011 - 09:58:19 CDT