----------------------------------------------------------------------- -- Exadata / ASM 4MB AUs LMT AUTOALLOCATE extent fragmentation issue -- ----------------------------------------------------------------------- -- -- Tablespaces using Locally Managed Extents and the AUTOALLOCATE option -- seem to behave differently on Exadata storage / ASM 4MB AUs than on regular storage / ASM 1MB AUs -- -- On regular storage / ASM 1MB AUs the algorithm has more flexibility when allocating a next extent -- For example, if according to the rules an 8MB extent would be required but only 1MB extents -- are available, it gracefully re-uses the smaller extents, at least to some degree, -- if they are not too small (for example it won't start re-using 64K free extents if an 8MB extent should be allocated) -- -- On Exadata / ASM 4MB AUs the same test case fails with an ORA-01652 since it obviously can't re-use the existing free extents -- Since in the below example there is no suitable extent on AU boundary available the table creation fails -- -- Given a corresponding usage pattern this can lead to huge space wastage or "unable to extend" errors with plenty of (fragmented) free space -- in Exadata / ASM 4MB AUs tablespaces set echo on timing on time on -- Enable this EVENT to disable the extent alignment to AU boundaries -- This will allow below table to get created also in a tablespace using 4MB AUs in ASM -- alter session set events '60060 trace name context forever, level 1'; drop tablespace auto_alloc_test including contents and datafiles; -- Create tablespace either on ASM / Exadata storage or outside on regular file system -- by uncommenting the datafile name clause create tablespace auto_alloc_test datafile --'auto_alloc.dbf' size 400M extent management local autoallocate segment space management auto; -- Create two tables begin for i in 1..2 loop execute immediate 'create table table'||i||'(col1 number,col2 number) /*segment creation immediate*/ tablespace auto_alloc_test'; end loop; end; / -- Interleave the extents until no space left (the ORA-01653 is expected) -- This will generate lots of 1MB extents begin for i in 1..1000 loop execute immediate 'alter table table'||(mod(i,2)+1)||' allocate extent'; end loop; end; / -- Free half of the tablespace, but free extents are fragmented, and max. free contiguous space is 1MB -- If you drop TABLE2, no extents on 4MB AU boundaries will be available drop table table2; -- If you drop TABLE1, extents on 4MB AU boundaries will be available and below CREATE TABLE will be successful --drop table table1; select sum(bytes)/1024/1024 sum_free_mb, max(bytes)/1024/1024 max_free_mb from dba_free_space where tablespace_name='AUTO_ALLOC_TEST'; -- Create a table that fits into the free space (less than 200MB) but usually will request 8MB extents -- -- When you drop TABLE2 above, this fails on Exadata storage/ ASM 4MB AUs because it attempts to find extents on AU boundaries that are not available -- -- When you drop TABLE2 above, this succeeds on regular storage / ASM 1MB AUs because it gracefully re-uses the existing 1MB extents if no 8MB extents can be found among the free extents -- -- This leads to ORA-01652 error messages on Exadata storage / ASM 4MB AUs with a suitable extent usage pattern although there is plenty of (fragmented) free space create table test tablespace auto_alloc_test as select rpad('x', 100) as col1 from (select /*+ cardinality(1e5) */ * from dual connect by level <= 1e5), (select /*+ cardinality(11) */ * from dual connect by level <= 11) ; -- In case of success check segment size and extent layout select bytes/1024/1024 as MB from dba_segments where segment_name = 'TEST' and owner = USER; select extent_id, bytes, blocks from user_extents where segment_name = 'TEST'; -- In case of failure check AU boundaries of free extents with au_size as ( select allocation_unit_size from v$asm_diskgroup where name = (select substr(file_name, 2, instr(file_name, '/') - 2) from dba_data_files where tablespace_name = 'AUTO_ALLOC_TEST') ) select count(*) from ( select block_id * 16384 / allocation_unit_size as AU_info, a.* from dba_free_space a, au_size where tablespace_name = 'AUTO_ALLOC_TEST' ) where au_info = trunc(au_info);If you run this test case on a tablespace using 4MB AU ASM data files, it will fail to create the last table, although it only requires approx. 127MB and there is approx. 200MB of (non-contiguous) free space (200 times 1MB extents), simply because the "desired" extent size is 8MB and no suitable free extents on "AU boundary" can be found. The relevance of the "AU boundary" condition can easily be checked by changing the test case to drop the other table. This table's extents allocate all the "AU boundaries" and hence the table creation will succeed as a sufficient number of (1MB) extents on "AU boundaries" could be found. If you repeat the same test case with the event set or using data files with a 1MB AU (or simply standard file system data files) the table will be created successfully (even with no extents on "AU boundaries" available), re-using the available 1MB extents. Although the "desired" extent size is still 8MB, the "minimum acceptable" extent size of 1MB allows the re-usage since the free extents don't need to be aligned on AU boundaries.
My Favorite Scripts – 2016
14 hours ago