Recommendation Benefit / Impact:
Setting _SMM_AUTO_MAX_IO_SIZE to the recommended value permits 1MB IOs for hash joins that spill to disk, which can increase performance up to 40% due to increased throughput. These performance increases can prevent the need to move TEMP to flash.
Slower performance for hash joins that utilize the temp tablespace on disk.
Action / Repair:
If _SMM_AUTO_MAX_IO_SIZE is not set to 1024, correct the parameter setting.
SQL> alter system set "_smm_auto_max_io_size"=1024 sid='*';