Re: long to clob conversion redo size and new table size
Date: Tue, 28 Jul 2009 04:04:53 -0700 (PDT)
Message-ID: <25bac77c-21f7-4297-8a72-57aae5f137a4_at_d32g2000yqh.googlegroups.com>
On Jul 27, 6:19 pm, Grzegorz <grzegor..._at_interia.pl> wrote:
> I've got 300GB table t1 with 2 columns:
>
> id number primary key,
> some_data long
>
> currently 70 mln rows.
> When I'm doing to_lob conversion via:
>
> insert /*+ APPEND NOLOGGING */ into t2 select id, to_lob(some_data)
> some_data from t1 where id between 1 and 2222222;
>
> table t2 clob is defined as 8kb chunk in MSSM tablespace (uniform size 1M)
>
> a 63MB redolog is switched every 18 seconds.
> My question is what is in redo ?
> The only think which comes to my mind is lob index maintenance but
> is that true ? Database is 9.2.0.8 and NOARCHOVELPG mode.
> Second question :
> How to minimize ne wtable (t2) size (table is insert/select only).
>
> Regards.
> Grzegorz
There are multiple things to consider here:
- As others have already pointed out, NOLOGGING is not a valid hint, but an attribute of a segment (e.g. ALTER TABLE / INDEX ... LOGGGING) that can be overridden by the FORCE LOGGING setting at tablespace or database level.
Therefore your direct-path insert into the table still generates redo, but less undo (except for potential index maintenance).
Have you verified that you're actually performing a direct-path insert? Can you access the table after the insert without committing? If yes, no direct-path insert took place. There are certain restrictions to direct-path operations, e.g. enabled triggers, foreign keys, deferrable constraints etc. all prevent a direct-path operation
2. LOBs are a different matter though:
You haven't said how you've created the LOB:
- ENABLE/DISABLE STORAGE IN ROW: Affects significantly how the LOB
will generate UNDO/REDO
- (NO)CACHE (NO)LOGGING: Affects significantly how the LOB will
generate REDO (and affects caching & I/O as well)
If you enabled storage in row, then all LOBs that are less than 3964 bytes will be stored in-row and the table's attributes regarding UNDO/ REDO generation apply (the other LOB-specific attributes don't apply in this case)
If you disable storage in row, then the LOBINDEX will be used more (since the first 12 chunk addresses are stored in row even when lob is stored out of row with storage in row enabled), and the LOB specific attributes apply, in particular the caching and logging defined for the LOB along with the PCTVERSION / RETENTION setting for read consistency control.
The default is NOCACHE LOGGING, which I think generates REDO for the whole LOB chunk size written, since it is done in direct path mode (no buffer cache / DBWR involved). This is in particular a nuisance if you modify the LOB. In this case the whole CHUNK size will always be written to REDO, even if you manipulate only 1 byte of the CHUNK.
So my guess is that most of the REDO is generated by the LOB operation.
If you want to minimize the REDO generation during LOAD, use NOCACHE NOLOGGING. If you enable storage in row you need to set the table segment itself to NOLOGGING as well, to prevent REDO generation for the LOB data stored in-line.
Further references on Metalink:
LOBS - Storage, Redo and Performance Issues / Doc ID: 66431.1 LOBS - Storage, Read-consistency and Rollback / Doc ID: 162345.1 LOB Performance Guideline / Doc ID: 268476.1
Regarding minimize the size: For the table segment, you could consider enabling compression and enable storage in row if your LOBs are mostly smaller than 4k. This way you might be able to benefit from the compression of the table segment. 11g introduces compression for LOBs / Securefiles, but that's not available in 9i.
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 Tue Jul 28 2009 - 06:04:53 CDT