Prompted by an email from Yves Colin I decided to dig out a little script I wrote some years ago and re-run an old test before asking this simple question: What’s the largest size array insert that Oracle will handle ?
If you’re tempted to answer, watch out – it’s not exactly a trick question but there is a bit of a catch.
Answer:
There is an internal limit of 255 on the size of array that Oracle can insert as a unit. I leave it as an exercise to the reader to decide whether or not this makes any significant difference to performance since the effects of row size, number of indexes maintained, and disk I/O requirements may make the effects of the limit virtually undetectable. To demonstrate the limit all we need do is insert a few hundred rows into a table and do a block dump, optionally followed by a dump of the redo log.
rem rem Script: internal_array_size.sql rem Dated: Nov 2014 rem Author: J P Lewis rem create table t1 (n1 number not null) segment creation immediate; alter system switch logfile; insert into t1 select rownum from all_objects where rownum <= 1000; commit; execute dump_log start rowid_count t1 /* FILE_NO BLOCK_NO ROWS_IN_BLOCK ---------- ---------- ------------- 5 180 660 5 184 340 2 rows selected. ROWS_IN_BLOCK BLOCKS ------------- ---------- 340 1 660 1 2 rows selected. */ alter system flush buffer_cache; alter system dump datafile 5 block 180;
The dump_log procedure is simply a pl/sql wrapper for a call to ‘alter system dump logfile {current log}’; the script rowid_count.sql extracts the file and block numbers from rowids in the given table and aggregates them in different ways. The reason for running the script is to find a table block with a lot of rows in it; the block I dumped actually held the first 660 rows of the insert. Here’s a tiny extract from the block dump (with one little comment added):
tab 0, row 0, @0x1904 -- 6,400 dec tl: 6 fb: --H-FL-- lb: 0x1 cc: 1 col 0: [ 2] c1 02 tab 0, row 1, @0x190a tl: 6 fb: --H-FL-- lb: 0x1 cc: 1 col 0: [ 2] c1 03 tab 0, row 2, @0x1910 tl: 6 fb: --H-FL-- lb: 0x1 cc: 1 col 0: [ 2] c1 04 tab 0, row 3, @0x1916 tl: 6 fb: --H-FL-- lb: 0x1 cc: 1
This shows the first four rows inserted, and you can see that the offset from the row directory entry to the actual location of the first row is 6,400 bytes, and then each subsequent row is 6 bytes further down the block (when the values in the n1 column get larger the row lengths will increase to 7 bytes). The positioning of these rows is, at first sight, a little odd – you might have guessed that they would either start at offset “zero” and work down the block to offset (approximately) “8K”, or start at “8K” and work back up the block to zero – why is the first row roughly 3/4 of the way down the block?
Rather than show you more row dumps, I’ll give you a carefully selected extract from the row directory:
0x12:pri[0] offs=0x1904 0x14:pri[1] offs=0x190a ... 0x20c:pri[253] offs=0x1f8a 0x20e:pri[254] offs=0x1f91 End of first 255 rows 0x210:pri[255] offs=0x120e Start of second 255 rows 0x212:pri[256] offs=0x1215 ... 0x40a:pri[508] offs=0x18f6 0x40c:pri[509] offs=0x18fd End of second 255 rows 0x40e:pri[510] offs=0xdf5 Start of last 150 rows 0x410:pri[511] offs=0xdfc ... 0x536:pri[658] offs=0x1200 0x538:pri[659] offs=0x1207 End of last 150 rows
- The first 255 rows inserted are stacked at the bottom of the block at offsets 0x1904 to 0x1f91.
- The second 255 rows inserted are stacked above them at offsets 0x120e to 0x18fd (note 0x18fd + 6 = 0x1903)
- The last 150 rows inserted are stack above them at offsets 0xdf5 to 0x1207 (note 0x1207 + 6 = 0x120d)
No matter how large your attempted array insert, the maximum number of rows (or index entries) Oracle can insert into a block in a single internal array operation is 255.
Further corroboration comes from the redo log dump – here’s a tiny bit of a single change vector (i.e. a single atomic change to a single Oracle block) from the redo generated while this insert was going on:
CHANGE #18 CON_ID:0 TYP:0 CLS:1 AFN:5 DBA:0x014000c7 OBJ:95876 SCN:0x0000.007528dd SEQ:1 OP:11.11 ENC:0 RBL:0 FLG:0x0000 KTB Redo op: 0x01 ver: 0x01 compat bit: 4 (post-11) padding: 1 op: F xid: 0x0007.007.000009f2 uba: 0x01000bda.02a9.14 KDO Op code: QMI row dependencies Disabled xtype: XA flags: 0x00000000 bdba: 0x014000c7 hdba: 0x01400083 itli: 1 ispac: 0 maxfr: 4858 tabn: 0 lock: 1 nrow: 255 slot[0]: 0 tl: 6 fb: --H-FL-- lb: 0x0 cc: 1 col 0: [ 2] c1 02 slot[1]: 1 tl: 6 fb: --H-FL-- lb: 0x0 cc: 1 col 0: [ 2] c1 03 slot[2]: 2 tl: 6 fb: --H-FL-- lb: 0x0 cc: 1 col 0: [ 2] c1 04
Although the CON_ID in line 1 tells you this dump is from 12c (or later) the same limit holds across all (modern) versions of Oracle. The redo Op Code (OP 11.11 – in line 1) is “Insert Multiple Rows”, and at line 9 you can see: “nrow: 255”. I haven’t included the rest of the change vector, but all it does is show the remaining 252 rows.
I suppose that size of array insert is limited by size of collection used by FORALL statement. Maximum collection size I believe is platform and version specific. At least I remember 16GB limit on Linux x86 64 and HP-UX on versions < 11.2.0.4 in my tests before. I made quick test on 11.2.0.4, Linux x86 64, with /proc/sys/vm/max_map_count adjustment (_realfree_heap_pagesize_hint can be used too) and was able to allocate up to 32GB in PGA for PL/SQL collection. So I guess that largest size array insert that Oracle will handle in 11.2.0.4, Linux is 32 GB.
Comment by Vyacheslav Rasskazov — November 8, 2014 @ 4:35 am GMT Nov 8,2014 |
Vyacheslav,
Thanks for the response – and looking at the question from a PL/SQL perspective does uncover a couple of interesting details.
There was a bit of a catch to the question though, so I’ll point out that “insert / select …. ” also uses Oracle array insert mechanism, and you can select from any size table you want to. So there is at least one other way to consider the question. (Answer some time late this evening, probably)
Comment by Jonathan Lewis — November 10, 2014 @ 12:17 pm GMT Nov 10,2014 |
[…] case, the “glitch” partway through the data block due to the initial insert using an internal array size of 255 rows is […]
Pingback by Quiz Night | Oracle Scratchpad — June 18, 2021 @ 9:55 am BST Jun 18,2021 |
[…] Quiz Night 27 (Nov 2014): What’s the maximum (internal) arraysize used by Oracle for “insert as select”. […]
Pingback by Quiz Catalogue | Oracle Scratchpad — September 14, 2022 @ 10:54 am BST Sep 14,2022 |