Hi,
I tell you how to migrate temp tablespace to flash disk . Because , temp waiting make happen performance problem.
Image version is 18.1.17 . This version is enough for temp using on flash disk . Normally, exadata smart storage itself decides temp tablespace using on flash disk. But if it can not be, we can with following code.
Before Oracle Exadata Storage Server released 12.2.1.1.0, temp writes were not cached in flash cache. Both temp writes and subsequent temp reads were from hard disk only.
Let’s start;
#Create disk group from flash disk . Temp read write events will be decreased.
#X6 cell storage .
#All commands are on compute node1 as root .
#Flash area was dropped.
dcli -g cell_group -l root cellcli -e "alter flashcache all flush" dcli -g cell_group -l root cellcli -e "drop flashcache"
#New flash disk total size for one cell storage, 10t
dcli -g cell_group -l root cellcli -e "create flashcache all size=10240g" dcli -g cell_group -l root cellcli -e "list flashcache detail" dcli -g cell_group -l root cellcli -e "list celldisk attributes name,size,freespace where diskType ='FlashDisk'"
#Griddisk were created from flash disks
dcli -g cell_group -l root cellcli -e "create griddisk all flashdisk prefix=flashdiskx6" dcli -g cell_group -l root cellcli -e "list griddisk attributes diskType, name where diskType=FlashDisk" dcli -g cell_group -l root cellcli -e "list griddisk attributes name,offset,size where diskType=FlashDisk"
#Disk group will be created.
select NAME, GROUP_NUMBER, DISK_NUMBER, STATE,HEADER_STATUS,MOUNT_STATUS, PATH , failgroup, TOTAL_MB ,OS_MB, FREE_MB,VOTING_FILE from v$asm_disk where HEADER_STATUS!='MEMBER' order by GROUP_NUMBER , DISK_NUMBER , NAME; CREATE diskgroup FLASH_ETL normal redundancy disk 'o/*/flashdiskx6*' attribute 'compatible.rdbms'='11.2.0.2.0','compatible.asm'='11.2.0.4.0', 'cell.smart_scan_capable'='TRUE', 'au_size'='4M';
#Disk will be mounted on other instances
alter diskgroup FLASH_ETL mount;
#Tablespace will be created on database
CREATE TEMPORARY TABLESPACE ETL_TEMP_TS TEMPFILE '+FLASH_ETL' SIZE 1G AUTOEXTEND ON NEXT 4G MAXSIZE UNLIMITED, '+FLASH_ETL' SIZE 1G AUTOEXTEND ON NEXT 4G MAXSIZE UNLIMITED, '+FLASH_ETL' SIZE 1G AUTOEXTEND ON NEXT 4G MAXSIZE UNLIMITED, '+FLASH_ETL' SIZE 1G AUTOEXTEND ON NEXT 4G MAXSIZE UNLIMITED, '+FLASH_ETL' SIZE 1G AUTOEXTEND ON NEXT 4G MAXSIZE UNLIMITED TABLESPACE GROUP '' EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
#User default temporary tablespace will be set new tablespace.
ALTER USER etl_user TEMPORARY TABLESPACE ETL_TEMP_TS;
Do you want to learn Exadata detailed, then read the following articles.
Oracle Exadata Tutorials for Beginners – Magic Database Machine