Mon, 2007-02-26 01:11 — trey
Here are the results of a quick experiment to determine the relative costs in Oracle of:
- Regular vs. Direct-path (/*+append*/) inserts into
- Regular (heap-organized) vs. Temporary table
- In NOARCHIVELOG vs. ARCHIVELOG modes,
- With the regular table marked LOGGING vs. NOLOGGING
I ran this on Oracle 9.2.0.8 with a 8k blocksize on a small Linux server. I ran each test once only.
Overall results of inserting 3917 rows via INSERT INTO ... SELECT * FROM all_objects:
| Log Mode | Table Type | Insert Mode | |||
|---|---|---|---|---|---|
| Regular | Direct-path (/*+ append */) |
||||
| DB Block Gets | Redo | DB Block Gets | Redo | ||
| NOARCHIVELOG | Regular (heap) - LOGGING | 386 | 400,344 | 87 | 11,940 |
| Regular (heap) - NOLOGGING | 382 | 400,228 | 87 | 11,956 | |
| Temporary | 281 | 19,212 | 3 | 224 | |
| ARCHIVELOG | Regular (heap) - LOGGING | 382 | 400,224 | 87 | 408,452 |
| Regular (heap) - NOLOGGING | 382 | 400,244 | 87 | 11,956 | |
| Temporary | 281 | 19,212 | 3 | 224 | |
Note that in ARCHIVELOGMODE, Direct-path insert (/*+ APPEND */) uses basically the same amount of REDO logging as a regular-path insert unless the table is marked NOLOGGING. However, the DB block gets are less since it is making new blocks. And, of course, UNDO will be generated for all – which, in turn, will have REDO.
The attached file test_log.txt contains the output from running the test.
| Attachment | Size |
|---|---|
| test_log.txt | 11.4 KB |
