Using ASM Scalability and Performance Enhancements in Oracle 11g:
This feature increases the maximum data file size that Oracle can support to 128 TB. ASM supports file sizes greater than 128 TB in any redundancy mode. This provides near unlimited capacity for future growth. The ASM file size limits are:
- External redundancy – 140 PB
- Normal redundancy – 42 PB
- High redundancy – 15 PB
Customers can also increase the allocation unit size for a disk group in powers of 2 up to 64 MB.
These improvements reduce database startup time and memory requirements, and allow support for larger ASM files, making it feasible to implement several hundred TB or even PB Oracle databases on ASM. Larger allocation units provide better sequential read performance.
Use the following criteria to determine the number of disk groups to create:
- Disks in a given disk group should have similar size and performance characteristics. If you have several different types of disks in terms of size and performance, then create several disk groups that contain similar characteristics.
- Create separate disk groups for your database files and fast recovery area for backup files. This configuration allows fast recovery should a disk group failure occur.
ASM files are stored in a disk group as a collection of extents. In Oracle 10g each extent mapped directly to one allocation unit (AU), but in Oracle 11g an extent can be made up of one or more allocation units. As files get larger, the extent size can grow to reduce the size of the extent map describing the file, thus saving memory.
When the disk group compatibility attributes are set to 11.1 or higher, the extent size will automatically grow as the file grows. In 11.1, the first 20,000 extents match the allocation unit size (1*AU). The next 20,000 extents are made up of 8 allocation units (8*AU). Beyond that point, the extent size becomes 64 allocation units (64*AU). In 11.2 this behavior has changed from 1, 8, 64 to 1, 4, 16.
In addition to the automatic expansion of the extent sizes, Oracle 11g also allows control over the allocation unit size using the ATTRIBUTE clause in the CREATE DISKGROUP statement, with values ranging from 1M to 64M.
SQL> CREATE DISKGROUP disk_group_2 EXTERNAL REDUNDANCY
DISK '/dev/sde1'
ATRRIBUTE 'au_size' = '32M';The combination of expanding extent sizes and larger allocation units should result in increased I/O performance for very large databases.
Specifying the Allocation Unit Size
An AU is the basic unit of allocation within an ASM disk group. In Oracle Database 10g, each AU had a single extent, which created problems with memory usage. If you have a large database with numerous default-sized allocation units, the database would need a very large amount of memory in the shared pool. The default AU size is only 1MB. File extents contain a minimum of one AU and an ASM file consists of at least one extent. You can set variable size extents with extents of size 1, 4, 16, 32,and 64 megabytes. The ability to set variable-size ASM extents means that ASM can now support larger file size extents while using less memory. ASM sets thresholds for each file and, as a file grows, ASM will increase the extent size based on the file size thresholds. Thus, a file can start with 1MB extents, with ASM increasing the extent size to 4, 16, 32, or 64 megabytes as the file size grows. Note that the size of an extent can vary among files as well as within a file. As a result of the variable extents feature, the database needs fewer extent pointers to describe an ASM file and less memory to manage the extent maps in the shared pool, thus making it easier to implement large ASM configurations.
Variable size extents raise the maximum possible ASM file size and also reduce shared pool memory requirements. You can periodically rebalance a disk group to avoid external fragmentation, which may occur because of allocating and freeing up of small data extents. However, the database will also automatically perform defragmentation when it has a problem finding the right size extent during extent allocation. ASM is also more scalable in Oracle Database 11g as compared to Oracle -Database 10g. The maximum ASM file size for external redundancy is now 140 petabytes, instead of 35 terabytes in Oracle Database 11g. Variable extent sizes enable you to configure ASM installations that are several hundred terabytes or even several petabytes in size. You can set the au_size attribute easily in Oracle Database 11g, by using the new attribute clause in the create diskgroup command, as shown here:
SQL> create diskgroup PAWDG6
external redundancy
disk
'/dev/raw/raw1'
attribute 'au_size' = '8M';You can also specify the au_size in bytes instead of megabytes. You can set an au_size of 1, 2, 4, 8, 16, 32, or 64 megabytes.
You can find out the allocation unit sizes for all disk groups by executing the following query on the V$ASM_DISKGROUP view:
SQL> select name, allocation_unit_size from v$asm_diskgroup;
NAME ALLOCATION_UNIT_SIZE
------------------ ---------------------------------------------------
PAWDGROUP1 1048576
PAWDGROUP3 1048576
PAWDGGROUP4 1048576
PAWDGGROUP2 1048576The variable extent sizes feature means that you can tailor the allocation unit sizes to meet the needs of your system
The allocation unit size is specified with the disk group attribute AU_SIZE. The AU size cannot be altered after a disk group is created. Below given example shows how the AU_SIZE is specified with the CREATE DISKGROUP SQL statement.
SQL> CREATE DISKGROUP data NORMAL REDUNDANCY
FAILGROUP controller1 DISK
'/devices/diska1' NAME diska1,
'/devices/diska2' NAME diska2,
'/devices/diska3' NAME diska3,
'/devices/diska4' NAME diska4
FAILGROUP controller2 DISK
'/devices/diskb1' NAME diskb1,
'/devices/diskb2' NAME diskb2,
'/devices/diskb3' NAME diskb3,
'/devices/diskb4' NAME diskb4
ATTRIBUTE 'au_size'='4M',
'compatible.asm' = '11.2',
'compatible.rdbms' = '11.2',
'compatible.advm' = '11.2';DB_FILE_MULTIBLOCK_READ_COUNT is one of the parameters you can use to minimize I/O during table scans. It specifies the maximum number of blocks read in one I/O operation during a sequential scan. The total number of I/Os needed to perform a full table scan depends on such factors as the size of the table, the multiblock read count, and whether parallel execution is being utilized for the operation.
As of Oracle Database 10g release 2, the default value of this parameter is a value that corresponds to the maximum I/O size that can be performed efficiently. This value is platform-dependent and is 1MB for most platforms. Because the parameter is expressed in blocks, it will be set to a value that is equal to the maximum I/O size that can be performed efficiently divided by the standard block size. Note that if the number of sessions is extremely large the multiblock read count value is decreased to avoid the buffer cache getting flooded with too many table scan buffers.
Even though the default value may be a large value, the optimizer will not favor large plans if you do not set this parameter. It would do so only if you explicitly set this parameter to a large value.
Online transaction processing (OLTP) and batch environments typically have values in the range of 4 to 16 for this parameter. DSS and data warehouse environments tend to benefit most from maximizing the value of this parameter. The optimizer is more likely to choose a full table scan over an index if the value of this parameter is high.
The maximum value is the operating system’s maximum I/O size expressed as Oracle blocks ((max I/O size)/DB_BLOCK_SIZE). If you set this parameter to a value greater than the maximum, Oracle uses the maximum.
To change the DB_FILE_MULTIBLOCK_READ_COUNT setting, use the ALTER SYSTEM SQL statement, Below given example assumes an 8 K block size.
Altering the DB_FILE_MULTIBLOCK_READ_COUNT Setting
SQL> ALTER SYSTEM SET db_file_multiblock_read_count=512 scope=both sid=*Thank you for reading… This is Airy…Enjoy Learning 🙂