Exadata
Oracle Exadata Smart Flash Cache
* Exadata cell -> Flash LUN -> Cell Disk -> Flash Cache/Flash Log
* CELL_FLASH_CACHE - – DEFAULT (LRU), KEEP, NONE
* ALTER TABLE <TABLE_NAME> STORAGE(FLASH_CACHE [NONE|DEFAULT|KEEP])
* ALTER INDEX <INDEX NAME> STORAGE(FLASH_CACHE [NONE|DEFAULT|KEEP])
Smart Flash Cache Mode:
1. Write Through Flash Cache Mode (Default):
Write on Disk -> Copy to Flash Cache
* excellent for absorbing repeated random reads
* commonly found in OLTP applications.
* dcli -g cell_servers -l celladmin cellcli -e "list cell attributes flashcachemode"
* Steps to enable Write-Through Mode
CellCLI> ALTER FLASHCACHE ALL FLUSH
CellCLI> DROP FLASHCACHE
CellCLI> ALTER CELL SHUTDOWN SERVICES CELLSRV
CellCLI> ALTER CELL flashCacheMode=WriteThrough
CellCLI> ALTER CELL STARTUP SERVICES CELLSRV
CellCLI> CREATE FLASHCACHE ALL
2. Write Back Flash Cache Mode:
Write on Flash -> Copy to Disk
* best for write-intensive applications
* commonly found in OLAP applications.
* If any application is write intensive and if one finds significant waits for “free buffer waits” or high IO times noted in AWR reports, then write back flash cache is the best suitable option.
* If problem is detected, I/O operations transparently fail over to mirrored copies of data also on flash.
* Steps to enable Write-Back Mode
CellCLI> DROP FLASHCACHE
CellCLI> ALTER CELL SHUTDOWN SERVICES CELLSRV
CellCLI> ALTER CELL FhashCacehMode = WriteBack
CellCLI> ALTER CELL STARTUP SERVICES CELLSRV
CellCLI> CREATE FLASHCACHE ALL
Hybrid Columnar Compression
* can be specified at Table or Table Partition and Tablespace Level.
* Not Suitable for data which is frequently changing.
* Locking at CU level instead of row level so potentially many rows could be locked instead of single row
* Compression Unit (CU): set of rows. Data is Organized by column in CU and then compressed.
* Data needs to be loaded using direct path loads like Parallel DML, INSERT /*+ APPEND*/, direct path SQL Loader
Two types of compression:
1. Warehouse Compression
* optimized for query performance as its reduced the I/O and mostly for data warehouse type of applications.
* compression can be up to 10x.
* COMPRESS FOR QUERY {LOW | HIGH}
2. Archive Compression
* optimized for maximum compression ration for space
* recommended to use if data changes rarely.
* compressed can be 10x to 50x.
* COMPRESS FOR ARCHIVE {LOW | HIGH}
For e.g. create table sales_compression_high compress for archive high nologging parallel 4 as select * from sales;
Distributed Command Line Interface (DCLI)
CELLCLI
* Each Exadata Storage Cell is administered individually by using cellcli
* connect a local cell server
* works with MS process to perform the administration task like creation of cell or grid disk.
DCLI
* enables execution of commands on multiple database and storage servers
* Following types of command supported by DCLI.
OS command
CELLCLI command
Operating System scripts
CELLCLI scripts
Steps to Configure dcli:
1. Add all cell server’s host name to a file.
cat cell_servers
exacell01
exacell02
2. Generate RSA public and private key
ssh-keygen -t rsa
3. Add the key to dcli for all listed cell servers.
dcli -g cell_servers -l celladmin -k -s '-o StrictHostKeyChecking=no'
4. DCLI command:
dcli -g cell_servers cellcli -e list cell
dcli -g cell_servers cellcli -e list celldisk
Storage Indexes in Exadata System
* keeps track of minimum and maximum column values of a chunk in GRID Disk
* used to avoid useless I/Os.
* Storage indexes are not stored on disk; resident in the memory of the storage cell servers
* created automatically after the storage cells receive repeated queries—with predicates—for columns.
* mostly used for full table scan
* not useful for encrypted table.
* cell_offload_processing=true
select name,value/1024/1024 as mb from v$statname natural join v$mystat where name in ('cell physical IO interconnect bytes returned by smart scan', 'cell physical IO bytes saved by storage index');
Exadata Smart Scan or Cell Offloading:
* storage send only required rows to database node from storage instead of entire Oracle Block.
* iDB - Intelligent Database Protocol
* Full Table/Fast Full Index Scans: Scans are performed inside Exadata Storage Server, rather than transporting all the data to the database server.
* Predicate filtering: Only the requested rows are returned to the database server
* Column filtering: Only the requested columns are returned to the database server
* Join filtering: Join processing using Bloom filters are offloaded to Exadata Storage Server.
Smart Scan statistics:
SQL> SELECT a.NAME, b.VALUE/1048576 MB FROM v$sysstat a, v$mystat b WHERE a.statistic# = b.statistic# AND ( a.NAME IN ('physical read total bytes', 'physical write total bytes', 'cell IO uncompressed bytes') OR a.NAME LIKE 'cell physical%');
When Smart Scan happens:
Full Table Scans
Full Index Scans
Direct-path reads
Direct-path reads are automatically used for parallel queries
Not used by default for serial scans of small tables Can be forced via _serial_direct_read=TRUE at either session or system level
To enable smart scan:
CELL_OFFLOAD_PROCESSING = true #In DB
‘cell.smart_scan_capable’=’TRUE’ #In ASM
SQL> ALTER DISKGROUP SET ATTRIBUTE.
Comments
Post a Comment