Database Management-Issue 116 Oracle Exadata 06-ESS-Part 2 (202301114)
It has been a full week since the last serious technology sharing, and 11 days have passed since the last Exadata special article. Today I finished writing ESS in one go. After all, I have to fly to Beijing again tomorrow.
1 Smart Scan
In fact, smart scan was briefly introduced in Database Management – Issue 61: Does Exadata Need Indexes (20230314). Data query and retrieval processing can be offloaded to Exadata storage nodes:
- The load is dropped to the storage server, giving full play to the computing power of the storage
- Transform storage into smart storage, returning only the data you need – row filtering, column filtering
- Reduce the amount of data returned and reduce IO channel requirements
- Reduce database server CPU, memory requirements
Smart Scan automatically optimizes full table scans, fast full index scans and fast full bitmap index scans using direct path reads, that is, parallel operations and large-scale sequential scans. In fact, the simplest way to trigger offload is to use parallelism in SQL.
In the SQL Monitor of the database on Exadata, you can see the storage offload efficiency of the statement, and you can also see the operation content of TABLE ACCESS STORAGE FULL in the specific execution plan.
2 Storage Index
The storage index is transparent and automatically maintained, eliminating IO-reducing features without any additional overhead:
- Storage indexes are not stored in the database like Oracle’s traditional B-tree or bitmap indexes. They cannot identify a set of records with a specific value in a given column. It is a function of storage server software.
- Storage indexes work by storing minimum and maximum column values for a disk storage unit, which is 1 MB by default, and are called region indexes.
- SQL predicates are passed to the storage node when a Smart Scan is performed, so the storage software can check the predicate against the storage index metadata (maximum and minimum values) before performing the requested I/O. Any storage area that is unlikely to have a matching row will be skipped.
3 EHCC
EHCC, hybrid columnar compression takes into account performance and compression ratio
- Takes up less disk space
- Get better performance with compression
– Scan tables based on compressed data
– Can retain more data in memory and Flash
– Use less IO when transferring data from disk to memory and Flash - Save an average of 10 times the space while querying faster
– QUERY: 5-10X, ARCHIVE MODE: 10-15X
EHCC syntax:
Warehouse Compression Syntax: CREATE TABLE emp (…) COMPRESS FOR QUERY [LOW | HIGH]; Online Archival Compression Syntax: CREATE TABLE emp (…) COMPRESS FOR ARCHIVE [LOW | HIGH];
Different compression methods use different algorithms: query low uses the LZO algorithm, query high and archive low use the ZLIB algorithm, and archive high uses the BZIP2 algorithm.
Exadata-specific compression: compress for query|archive low|high –Only data inserted by direct paths will be compressed, and ordinary DML operations will reduce the compression level.
In hybrid column compression, multiple data blocks form a compression unit (CU). The data in the compression unit is stored in the form of columns, rather than the data of the entire table. /strong>It is stored in the form of columns, so it is hybrid column compression, and the number of data blocks contained in the compression unit is not fixed, and is determined by factors such as the compression mode and the compression rate of the loaded data; hybrid column compression is used In a column-compressed table, ordinary insert data will be inserted, and the newly added row records will be downgraded to OLTP compression mode (at this time, a move operation is performed on the table to reorganize the table, and the table will become hybrid column compression. If a DML operation is performed again, it may be changed again. will be degraded to OLTP compression, so HCC tables should perform as few DML operations as possible). Hybrid column compression will only be triggered when data is loaded in the following ways:
- Direct path insert statement (/* + append */);
- Parallel DML statements;
- Direct path sqlloader;
- CTAS (Create Table As Select) method.
HCC can be specified at the table level, partition level and table space level.
You can specify HCC when creating the table, or you can modify the existing table to an HCC table. At this time, the original data in the table is not compressed, and you need to perform a move reorganization on the table to make it Some data undergoes hybrid column compression.
4 Intelligent storage processing process
Combining the previously mentioned Smart Scan, Storage Index and EHCC, after making general storage intelligent in Exadata, the data processing flow becomes as shown in the figure below:
Taking full advantage of hardware features and resources, and based on Oracle’s full understanding of its database storage, using ESS greatly speeds up the data query efficiency on Exadata.
5 Smart Flash Cache
Smart flash caching and write-back:
Caching hot data in PMEM/XRMEM or Flash Card can speed up data query efficiency and database writing efficiency.
Monitor whether the data is cached:
SQL> SELECT data_object_id FROM DBA_OBJECTS object_name='EMP'; DATA_OBJECT_ID ---------- 57435 CellCLI> LIST FLASHCACHECONTENT WHERE objectNumber=57435 DETAIL cachedKeepSize: 0 cachedSize: 495438874 dbID: 70052 hitCount: 415483 missCount: 2059 objectNumber: 57435 tableSpaceNumber:
Ensure hot data is cached:
SQL> ALTER TABLE xxx STORAGE (CELL_FLASH_CACHE KEEP);
Note: cell_flash_cache has three values: none (never placed in flash memory), default (placed according to policy), keep (put in flash memory as much as possible); In order to prevent the object of the keep attribute from occupying the entire Flashcache, the system limits the total space occupied by objects with the keep attribute to no more than 80% of the flashcache. In order to prevent some rarely accessed objects with the keep attribute from occupying the flashcache without limit, the system will regularly use elimination strategies.
Let’s cull these objects briefly. Generally no human intervention is required, the default is sufficient.
Starting with Oracle Exadata System Software version 23.1.0, the XRMEM (PMEM) cache can only operate in write-through mode.
6 Smart Flash Logging
Smart flash log:
That is, whichever log is written first will return a successful transaction without human configuration and intervention, thus accelerating write operations.
7 IORM
IO resource management:
Storage resource pool: Data is dynamically redistributed to eliminate I/O distribution hot spots. The database platform can continuously expand linearly and data is automatically distributed to provide the highest performance and high availability.
- Balance is maintained when new hardware is added
- Balance is maintained when old hardware is removed
- Equilibrium is maintained when hardware fails
- Can tolerate damage to a single disk/damage to a single storage cell
IORM can control the storage IO performance of each database in a fine-grained manner based on the importance and IO requirements of the business database. Of course, Exadata’s powerful IO performance does not require configuring IORM in most environments. IORM can be configured through CELLCLI or EMCC:
8 In_memory extension
A table running an Oracle database other than Exadata can only choose one mode between row format and column format:
Exadata can break through format limitations and achieve dual-format storage:
Exadata also implements storage layer in-memory columnar analysis:
- Exadata automatically and intelligently converts and loads column format data into Flash cache
– Storage server implements fast vector query processing - Unique dual format data flash cache
– Mixed load support, supporting both row-based OLTP databases and column-based analytical databases - Fully automated and transparent
9 High-performance protocol
Exadata uses the following high-performance transmission protocols to improve data transmission efficiency within the rack:
Summary
ESS is basically completed, follow the old rules, and know what to write.