Exadata parameter _SMM_AUTO_MAX_IO_SIZE


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.
Risk:
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.
Solution:
SQL> alter system set "_smm_auto_max_io_size"=1024 sid='*';

6 comments

  1. Karan Kukreja · · Reply

    The section mentioned as “risk” , is it the risk after setting this , or the problem statement for which this was set ?

  2. Hi Karan, yes of course the problem statement for which this was set.

    smm_auto_max_io_size — Maximum IO size (in KB) used by sort/hash-join in auto mode

    More Information: https://sites.google.com/site/embtdbo/wait-event-documentation/oracle-io-waits

    Regards

  3. Karan Kukreja · · Reply

    Hi ,

    Thank you for your reply. We got this value as a recommendation in our exachk report to set to 1024. We are using Oracle EBS r12.1.3.

    Any side effects of this that you have observed ?

    Regards
    Karan

  4. HI Karan,

    This was a problem writing to the disk quickly.

    This 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.

    Regards
    Anar

  5. sriram vrinda · · Reply

    To validate the impact of this parameter, you could use SPA Quick check and see the impact before the change.

  6. Thank you for exercise solution ….

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: