Key Capabilities of Exadata Database Machine

 Key Capabilities of Exadata Database Machine

Exadata storage server software provides below key capabilities

1. Smart Scan and query offloading
2. Smart Flash Cache
3. Smart Flash log
4. Storage index
5. I/O Resource Management
6. Hybrid Columnar Compression

Exadata Smart Scan and offloading

• Offloading mean some portion of sql processing is done by storage server.
• It offloads I/O operations to storage servers .It filters rows and columns at storage server and return relevant rows to user (It retrieve specific rows rather than complete blocks)and It reduces storage interconnect traffic by eliminating unneeded rows and columns, improving query response times while reducing total CPU and memory requirements on the database server.Smart Scan optimization is a runtime decision. it is not integrated with oracle optimizer. however it is influenced by the results of query optimization.

Smart Scan includes:

Full Table and 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, rather than all the rows in a table.

Column filtering: Only the requested columns are returned to the database server, rather than all the table columns.

Join filtering: Join processing using Bloom filters are offloaded to Exadata Storage Server.

In addition to the query-specific requirements, the following general requirements must also be met to enable Smart Scan:

• Smart Scan must be enabled within the database. The CELL_OFFLOAD_PROCESSING initialization parameter controls Smart Scan. The default value of the parameter is TRUE, meaning that Smart Scan is enabled by default.Each segment being scanned must be on a disk group that is completely stored on Exadata cells. The disk group must also have the following disk group attribute settings:

      'compatible.rdbms' = '11.2.0.0.0' (or later)

      'compatible.asm' = '11.2.0.0.0' (or later)

       'cell.smart_scan_capable' = 'TRUE‘

The following are common wait event

    SELECT s.name, m.value/1024/1024 MB FROM V$SYSSTAT s, V$MYSTAT m WHERE s.statistic# = m.statistic# AND(s.name LIKE 'physical%total bytes' OR s.name LIKE 'cell phys%‘ OR s.name LIKE 'cell IO%');

• cell interconnect retransmit during physical read-  Database wait during retransmission for an I/O of a single-block or multiblock read
• cell list of blocks physical read -  Cell equivalent of db file parallel read
• cell single block physical read - Cell equivalent of db file sequential read
• cell multiblock physical read  -  Cell equivalent of db file scattered read
• cell smart table scan               -  Database wait for table scan to complete
• cell smart index scan               - Database wait for file creation operation
• cell smart  incremental backup  - Database wait for incremental backup operation
• cell smart restore from backup  - Database wait during file initialization for restore

Hybrid Columnar Compression

Hybrid Columnar Compression  uses modified form of  columnar storage. Instead of row-major storage. Data is organized into sets of rows called compression unit. within compression unit, Data is organized by column and then compressed .  Compression is also achieved by replacing repeating values with smaller symbolic references  because compression unit is much larger than an oracle block. And because  column organization of data brings similar value close together. Each row is self –contained within a compress unit

• Hybrid Columnar Compression reduces disk space usage and typically yields faster query times as fewer blocks are required to satisfy the same operation.
• Hybrid Columnar Compression is possible at table,partition and tablespace level.
• Candidate big Table with Hybrid Columnar Compression:
• Data must be loaded via direct path read
• Compressed tables should expect very little DML

Types of EHCC

1. Query Low

2. Query High

3. Archive High

4. Archive Low

Smart Flash Cache

The Flash cache is a hardware component configured in the exadata storage cell server which deliver high performance in read and write operations. Four 96G PCIe flash memory cards (384 per cell) are present on each exadata storage server cell.

• Exadata Smart Flash Cache provides a caching mechanism for frequently accessed data(data and index block) on each Exadata cell.
• Smart Flash Cache reduces physical disk I/O by caching data in PCI flash, which offers lower I/O latency than physical disks.
• Exadata Storage server provides Exadata Smart Flash 
• By default, Exadata Smart Flash Cache operates in writethrough mode,however, Exadata Smart Flash Cache can also be configured to operate in write-back mode.
• writethrough mode is best suited to random repeated reads commonly found in OLTP application. 
• write-back mode is best suited to write intensive applications.
• It uses internal statistics and other measures to determine whether or not an object (table or index) should be cached .It takes into account the size of the object and the frequency of access to the object. 

Exadata Smart Flash Log

Exadata Smart Flash Log provides a mechanism for improving the latency of database redo log write operations. Exadata Smart Flash Log uses a small portion of the high-performance flash memory on Exadata Storage Server as temporary storage to provide low latency redo log writes. By default, Exadata Smart Flash Log uses a total of 512 MB on each Exadata Storage Server. The default Exadata Smart Flash Log can be dropped and recreated using a different size. However, the size of the default Exadata Smart Flash Log is sufficient for most uses. Exadata Smart Flash Log is managed automatically by Exadata Storage Server.

Use the LIST FLASHLOG DETAIL command to examine the Exadata Smart Flash Log area on this cell.

    CellCLI> list flashlog detail

•  In addition to Exadata Smart Flash Cache, Exadata Smart Flash Log provides a mechanism for improving the latency of database redo log write operations. 
• Exadata Smart Flash Log uses a small portion of high-performance flash memory as temporary storage to facilitate low latency redo log writes. 
• By default, Exadata Smart Flash Log uses a total of 512 MB on each Exadata Storage Server;

Use the LIST FLASHLOG DETAIL command to examine the Exadata Smart Flash Log area on this cell.

CellCLI> list flashlog detail

Storage Indexes

A storage index is a memory-based structure that reduces the amount of physical I/O in a  the exadata cell when accessing data via Exadata Smart Scan. A storage index keeps track the minimum and maximum column values and information is used to avoid useless I/Os.

• By default, storage indexes are created and maintained automatically by Exadata's cell services software
• Storage indexes is a unique  whose primary goal is to reduce the amount of I/O required to service I/O requests for Exadata Smart Scan. 
• Storage Indexes consist  of  a minimum and a maximum column value. This structure disk is logically divided  inside each cell into 1MB chunks called storage regions

I/O Resource Management

I/O Resource Management prioritizes I/O requests to more important resources higher than less important resources, enabling the critical databases to operate within the parameters of performance SLAs.

• By default , The Goal of IORM is to fully use the available disk resources . Any allocation that is not fully utilized is made available to other workloads.  
• IORM can be enabled on single database (intradatabase) and multiple database(interdatabase)
• The intradatabase I/O resource plan specifies how I/O resource are allocated among  consumer groups in a specific database. An intradatabase I/O resource plan is created with procedure in the DBMS_RESOURCE_MANAGER PL/SQL package
• IORMPLAN is used to configure IORM on single and multiple databases . 
• IORMPLAN object is automatically created when cell is configured .It cannot be recreated or dropped
• IORMPLAN specifies how I/O resources are allocated for each cell.
• The category plan assigns I/O resources using categories.
• The interdatabase specifies how resources are divided  among multiple data bases. The interdatabase plan is configured and activated  with CELLCLI on each cell.
• The interdatabase plan assigns I/O resources using database names.
• All combinations are possible. Use CellCLI to define and activate the IORMPLAN on each cell.
• IORM manages exadata I/O resources on a per-cell basis, scheduling incoming I/O request  according to the configured resource plans. IORM schedules I/O by selecting requests from different CELLSRV queues. The resource plans are used to determine the order in which the queued I/O requests are issued to disk. By default  , The goal of IORM is to fully use the available disk resources. Any allocation that is not fully utilized is made available to other  workloads in proportion to the configured resources plans.

        Cellcli> list iormplan details

Exadata I/O Resource Management (IORM) is a tool for managing how multiple workloads and databases share the I/O resources of Oracle Exadata Database Machine.I/O Resource Manager (IORM) is the extension of DBRM, DBRM deals with the resource management on DB server and IORM deals on storage sever. Database Resource Manager (DBRM) is basically geared toward managing CPU resources at the database tier.Oracle Database Resource Manager enables you to manage workloads within a database.Consumer group represent collections fo users within a database.DBRM without any plan/plan directive, which means we are not capping any CPU resources at DB level

DBRM consist of: - Plan --> Directive -->Consumer group.

IORM manages the storage server I/O resources on a per-cell basis. Whenever the I/O requests start to saturate a cell's capacity, IORM schedules incoming I/O requests according to the configured resource plans.

IORM allows workloads and databases to share Oracle Exadata Storage Servers according to user-defined policies

Categories represent collections of consumer groups across all database

IORM schedules I/O  by selecting requests from the different CELLSRV queues. The resource plans are used to determine the orderin which the queues I/O requests  are issued to disk

The valid objective values are:

auto - Causes IORM to determine the best mode based on active workloads and resource plans. IORM continuously and dynamically determines the optimization objective based on the observed workloads and enabled resource plans. For most use cases, auto is the recommended value.

Starting with Oracle Exadata System Software release 21.2.0, auto is the default setting for new deployments.

high_throughput - Optimizes critical DSS workloads that require high throughput. This setting improves disk throughput at the cost of I/O latency.

low_latency - Optimizes critical OLTP workloads that require extremely good disk latency. This setting provides the lowest possible latency at the cost of throughput by limiting disk utilization.

balanced - Balances low disk latency and high throughput, which is useful for a mixture of critical OLTP and DSS workloads. This setting limits disk utilization of large I/Os to a lesser extent than low_latency to achieve a balance between latency and throughput.

basic - Use this setting to limit the maximum small I/O latency and otherwise disable I/O prioritization. This is the default setting for new deployments in Oracle Exadata System Software release 20.1.0 and earlier. Specifically, using this setting:

IORM guards against extremely high latencies for log writes, buffer cache reads, and other critical I/Os by prioritizing them ahead of smart scans and other disk intensive I/O operations.

IORM manages access to flash cache and flash log.

IORM manages scans to ensure that resources are shared between different workloads.

IORM does not enforce the maximum utilization limits in the IORM plan. Plan allocations are only used to guard against extremely high latencies, and there is no consideration for plan conformance. For stricter plan conformance, and enforcement of maximum utilization limits, the objective option must be set to something other than basic.

Note :  We  can  evaluate  after we initially change the IORM plan to auto.  If within AUTO, you need to set limits or tweak based on cell metrics, you would do individual “dbplans”. 

For the Auto objective ,IORM continually analyzes the I/O workload and active IORM plans to select the appropriate objective setting , for example if a particular database is allocated the majority of I/O resources and if is running an OLTP workload , then the low_letency setting will be automatically engaged. 

dcli -g ~/cell_grp -l root cellcli -e "list iormplan detail”

dcli -g ~/cell_grp -l root cellcli -e "ALTER IORMPLAN OBJECTIVE = AUTO”

dcli -g ~/cell_grp -l root cellcli -e "list iormplan detail”

A)-- How to configure IORM on cell nodes.

ODS and EDW- Level 1 (40%+40%=80%)

All other (EDWSTG and any other) - Level 2 (100% - that's nothing but overall 20%)

Note: IORM have 8 levels like DBRM.

-- To create IORM, need to create the IORMPLAN on each cell node

CellCLI> alter iormplan dbplan=((name='ODS', level=1, allocation=40),(name='EDW', level=1, allocation=40),(name=other,level=2,allocation=100))

IORMPLAN successfully altered

-- How to Activate

CellCLI> alter iormplan active

-- How to Inactivate

CellCLI> alter iormplan inactive

-- Check if the IORM is Active?

CellCLI> LIST IORMPLAN

atl02cel02_IORMPLAN active

-- Check if current IORM plan in place?

CellCLI> LIST IORMPLAN detail

name: atl02cel01_IORMPLAN

catPlan:

dbPlan: name=ODS,level=1,allocation=40

name=EDW,level=1,allocation=40

name=other,level=2,allocation=100

status: active

B)-- Monitoring IORM with cellcli command.

-- How to check Small IO load per database

CellCLI> LIST METRICCURRENT where name=DB_IO_RQ_SM

DB_IO_RQ_SM EDW 7,387,543 IO requests

DB_IO_RQ_SM ODS 5,262,853 IO requests

DB_IO_RQ_SM EDWSTG 11,526,325 IO requests

-- How to check Large IO load per database

CellCLI> LIST METRICCURRENT where name=DB_IO_RQ_LG

DB_IO_RQ_LG EDW 13,210,476 IO requests

DB_IO_RQ_LG ODS 5,865,946 IO requests

DB_IO_RQ_LG EDWSTG 4,644,494 IO requests

-- How to check Small IO load/sec. per database, last minute

CellCLI> LIST METRICCURRENT where name=DB_IO_RQ_SM_SEC

DB_IO_RQ_SM_SEC EDW 14.5 IO/sec

DB_IO_RQ_SM_SEC ODS 86.5 IO/sec

DB_IO_RQ_SM_SEC EDWSTG 135 IO/sec

-- How to check Large IO load/sec. per database, last minute

CellCLI> LIST METRICCURRENT where name=DB_IO_RQ_LG_SEC

DB_IO_RQ_LG_SEC EDW 54.4 IO/sec

DB_IO_RQ_LG_SEC ODS 47.0 IO/sec

DB_IO_RQ_LG_SEC EDWSTG 73.5 IO/sec

-- How to check Small IO Waits per database. per min.

CellCLI> LIST METRICCURRENT where name=DB_IO_WT_SM

DB_IO_WT_SM EDW 35,576,746 ms

DB_IO_WT_SM ODS 5,686,954 ms

DB_IO_WT_SM EDWSTG 7,236,957 ms

-- How to check Large IO Waits per database. per min.

CellCLI> LIST METRICCURRENT where name=DB_IO_WT_LG

DB_IO_WT_LG EDW 2,284,982,657 ms

DB_IO_WT_LG ODS 384,891,454 ms

DB_IO_WT_LG EDWSTG 394,093,606 ms