When Smart Scan Plays Dumb Teach Exadata Some

When Smart Scan Plays Dumb, Teach Exadata Some New Tricks Jim Czuprynski Zero Defect Computing, Inc. April 17, 2013 Copyright 2013, Zero Defect Computing, Inc.

My Credentials 30+ years of database-centric IT experience Oracle DBA since 2001 Oracle 9 i, 10 g, 11 g OCP 95+ articles on databasejournal. com and ioug. org Teach core Oracle DBA courses (G/I+RAC, Exadata, Performance Tuning, Data Guard) 2009: Oracle Education Partner Trainer of the Year Speaker at Oracle Open. World, IOUG COLLABORATE 11, and IOUG COLLABORATE 13 Oracle-centric blog (Generally, It Depends) Copyright 2013, Zero Defect Computing, Inc. -2 -

Our Agenda Smart Scan is part of the Exadata Database Machine’s “secret sauce”. . . but it’s not perfect. This presentation therefore aims to: Demonstrate how Smart Scan typically reduces query execution time Identify why Smart Scan sometimes “plays dumb” and chooses sub-optimal retrieval methods Reveal when Smart Scan isn’t the cheapest method to answer a query Explore how to resolve these issues by helping Smart Scan to raise its “query IQ” whenever possible Copyright 2013, Zero Defect Computing, Inc. -3 -

Exadata Database I/O User issues query: SELECT key_id, key_desc FROM ap. ehcc_parted WHERE key_sts <> ‘Y’; 2 Smart. Scan-enabled execution plan: TABLE ACCESS STORAGE FULL (AP. EHCC_PARTED) 30 M rows (4 GB) i. DB command generated and issued to Exadata storage cells 3 1 3 AUs 6 80 bytes Result set containing just data elements needed is FETCHed 5 4 rows X 2 cols Result set built from just rows and columns needed to satisfy query Copyright 2013, Zero Defect Computing, Inc. 4 Storage cells determine which ASM AUs answer the query -4 -

Smart Scan and Smart Storage Complex DSS and OLAP queries may need huge numbers of database blocks to be retrieved, usually via full scans of extremely large row sources (tables as well as indexes) Smart Scan retrieves only the necessary data as efficiently as possible Smart Storage determines if corresponding blocks are already in Exadata servers’ buffer caches Exadata knows not only which database blocks are already available in memory, but how applications are using each block Copyright 2013, Zero Defect Computing, Inc. -5 -

Smart Flash Features Leverages Exadata storage cells’ write-throughcache flash memory Overcomes intrinsic limitations of HDDs Smart Flash Cache extends database’s buffer cache size dramatically Smart Flash Logging can accelerate online redo log writes (and thus speed COMMITs) Flash-based Grid Disks can be deployed for intense random access requirements Top rated speed per storage cell: 10 K IOPS Total I/O response time per X 2 -2 full rack: 1. 5 M IOPS Copyright 2013, Zero Defect Computing, Inc. -6 -

Column and Predicate Filtering Exadata also dramatically limits the amount of data retrieved when selection criteria and specific columns are mentioned in a SQL statement: Predicate filtering captures only those rows necessary to answer query Column filtering (aka column projection) captures only those columns necessary to answer query Only the data elements (columns X rows) needed for the statement are returned Think: SQL Results Query Cache Copyright 2013, Zero Defect Computing, Inc. -7 -

Storage Indexes Automatically built in Exadata storage cells’ memory Region indexes map which AUs contain ranges of column data values Storage indexes are built upon region indexes Storage cells thus know exactly which AUs can be used to answer a predicate Only way to “see” them: Positive impact on query performance Cell-specific instance statistics Sensitive to sort order during initial data load Copyright 2013, Zero Defect Computing, Inc. -8 -

Hybrid Columnar Compression (HCC) provides two extra data compression methods: Warehouse Compression (10 X – 15 X): Data that’s accessed relatively infrequently Archival Compression (15 X – 50 X): Data that’s only retained for long-term periods or just for regulatory requirements Compute nodes perform compression Storage cells perform decompression Compression units store compressed data Copyright 2013, Zero Defect Computing, Inc. -9 -

HCC: Compression Units Compression Unit Block Header CU Header C 1 C 2 Block Header C 2 C 3 Block Header C 5 Block Header C 7 C 8 C 4 C 5 C 6 A compression unit (CU) is a new logical structure that spans multiple database blocks Row and column data is completely reorganized based on individual column values Every column is separately compressed Rows do not span multiple CUs Smart Scan benefits dramatically from CUs Copyright 2013, Zero Defect Computing, Inc. - 10 -

Why Didn’t Smart Scan Happen?

Did Smart Scan Really Happen? As Reagan once said to Gorbachev: (Doveryai, Trust, но but no проверяй proveryai) verify Доверяй, So trust the expected EXPLAIN PLAN … … but then verify: The actual post-execution plan Instance statistics specific to Smart Scan Wait events related to Smart Scan Copyright 2013, Zero Defect Computing, Inc. - 12 -

Conditions Preventing Smart Scan For DSS or Data Warehousing application workloads, Smart Scan may not occur because: Dynamic Sampling is invoked A Flashback Versions Query is requested Predicates specified for a virtual column Query against a LONG or LOB datatype Row-level dependency is enabled Over 255 columns in a query* * Unless HCC is used on all tables in query! Copyright 2013, Zero Defect Computing, Inc. - 13 -

Conditions Preventing Smart Scan For OLTP or hybrid OLTP / DSS application workloads, other factors may come into play: Fast Full Index Scans are prohibited for: Reverse key indexes (common for RAC) Compressed indexes (common for parent-child tables) Full scans on index-organized tables (IOTs) Full scans on clustered tables ORA_ROWSCN virtual column requested Optimizer decides to return rows in ROWID order Tables are just too small! Copyright 2013, Zero Defect Computing, Inc. - 14 -

A Successful Smart Scan can use the primary key index for table AP. RANDOMIZED_SORTED: SELECT MIN(key_id), AVG(key_id), MAX(key_id) FROM ap. randomized_sorted WHERE key_id BETWEEN 1000000 AND 2499999; A successful Smart Scan of the PK index is shown in both the EXPLAIN PLAN and statistics. -----------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -----------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 6 | 1804 (2)| 00: 22 | | 1 | SORT AGGREGATE | | 1 | 6 | | 2 | PX COORDINATOR | | | | 3 | PX SEND QC (RANDOM) | : TQ 10000 | 1 | 6 | | 4 | SORT AGGREGATE | | 1 | 6 | | 5 | PX BLOCK ITERATOR | | 1500 K| 8789 K| 1804 (2)| 00: 22 | |* 6 | INDEX STORAGE FAST FULL SCAN| RANDOMIZED_SORTED_PK | 1500 K| 8789 K| 1804 (2)| 00: 22 | ----------------------------------------------------- Statistic Name Bytes (MB) ----------------------------------------physical read total bytes 51. 26 cell physical IO interconnect bytes 23. 33 cell physical IO bytes eligible for predicate offload 51. 20 cell physical IO bytes saved by storage index. 00 cell physical IO interconnect bytes returned by smart scan 23. 27 cell IO uncompressed bytes. 00 Copyright 2013, Zero Defect Computing, Inc. - 15 -

Impact: REVERSE KEY Index In this example, the primary key index for table AP. INVOICES cannot be used for Smart Scan: SELECT MIN(invoice_id), MAX(invoice_id) FROM ap. invoices; Even though Fast Full Scan of PK index is indicated, statistics and waits show it didn’t happen at all! ------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 4 | 5 (0)| 00: 01 | | 1 | SORT AGGREGATE | | 1 | 4 | | 2 | INDEX STORAGE FAST FULL SCAN| INVOICES_PK_IDX | 4500 | 18000 | 5 (0)| 00: 01 | ------------------------------------------------Statistic Name Bytes (MB) ----------------------------------------physical read total bytes. 22 physical write total bytes. 00 cell physical IO interconnect bytes. 22 cell physical IO bytes eligible for predicate offload. 00 cell physical IO bytes saved by storage index. 00 cell physical IO interconnect bytes returned by smart scan. 00 cell IO uncompressed bytes. 00 Total Wait Avg Wait Event Waits Time (s) ------------------------------ -----cell single block physical read 8. 02. 0031 cell multiblock physical read 3 0. 0011 Copyright 2013, Zero Defect Computing, Inc. - 16 -

Conditions Preventing Smart Scan Some factors may be incidental, difficult to isolate, and perhaps non-negotiable: Indexed retrieval may still be faster than table scans Initialization parameters may affect optimizer decisions (e. g. OPTIMIZER_INDEX_COST_ADJUST) Parallel processing may not have been chosen “Hidden” initialization parameters actually govern whether Smart Scan be utilized Copyright 2013, Zero Defect Computing, Inc. - 17 -

Secret Initialization Parameters Three hidden parameters impact Smart Scan: _small_table_threshold Determines if a table is “small” Specified in number of blocks (e. g. 512 x 8 KB = 4 MB) _serial_direct_read Will force Smart Scan when there’s no parallelism Default: FALSE _direct_read_decision_statistics_driven New in Oracle Database 11. 2. 0. 3. 1 Set to TRUE by default Copyright 2013, Zero Defect Computing, Inc. - 18 -

Impact: _small_table_threshold If a table or index is < 2% of current buffer cache size, then it’s excluded from Smart Scan: SELECT MIN(key_id), AVG(key_id), MAX(key_id) FROM ap. randomized_sorted WHERE key_id BETWEEN 1000000 AND 1199999; Fast Full Scan of PK index fails because the total number of blocks scanned is well below the size of _smart_table_threshold ---------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------| 0 | SELECT STATEMENT | | 1 | 6 | 441 (1)| 00: 06 | | 1 | SORT AGGREGATE | | 1 | 6 | |* 2 | INDEX RANGE SCAN| RANDOMIZED_SORTED_PK | 200 K| 1171 K| 441 (1)| 00: 06 | ---------------------------------------------Statistic Name Bytes (MB) ----------------------------------------physical read total bytes 3. 55 cell physical IO interconnect bytes 3. 55 cell physical IO bytes eligible for predicate offload. 00 cell physical IO bytes saved by storage index. 00 cell physical IO interconnect bytes returned by smart scan. 00 cell IO uncompressed bytes. 00 Total Wait Avg Wait Event Waits Time (s) ------------------------------ -----cell single block physical read 454. 22. 0005 Copyright 2013, Zero Defect Computing, Inc. - 19 -

Smart Scan: The Good, The Bad, and The Ugly (With apologies to Sergio Leone)

Using DBMS_COMPRESSION SET SERVEROUTPUT ON DECLARE comp_blks NUMBER; unco_blks NUMBER; pcti_blks NUMBER; comp_rspb NUMBER; unco_rspb NUMBER; comp_ratio NUMBER; Compression Advisor self-check validation successful. comp_type VARCHAR 2(100); pcti_rspb NUMBER; select count(*) on both Uncompressed and EHCC Compressed BEGIN format = 3000000 rows DBMS_COMPRESSION. GET_COMPRESSION_RATIO ( scratchtbsname => 'USERS' Compression Type: "Compress For Query Low" , ownname => 'AP' , tabname => 'RANDOMIZED_SORTED' Compression Ratio: 2. 90 , partname => NULL Uncompressed Blocks: 15654 , comptype => DBMS_COMPRESSION. COMP_FOR_QUERY_HIGH , blkcnt_cmp => comp_blks Compressed Blocks: 5305 , blkcnt_uncmp => unco_blks Block Compression %: 33. 89 , row_cmp => comp_rspb , row_uncmp Uncompressed Rows Per=> unco_rspb Block: 191 , cmp_ratio => comp_ratio Compressed Rows, comptype_str Per Block: 566 => comp_type ); Rows Per Block Compression %: 296. 34 pcti_blks : = ((comp_blks / unco_blks) * 100); pcti_rspb : = ((comp_rspb / unco_rspb) * 100); DBMS_OUTPUT. PUT_LINE('Compression Type: ' || comp_type); DBMS_OUTPUT. PUT_LINE('Compression Ratio: ' || TO_CHAR(comp_ratio, '999999. 99')); DBMS_OUTPUT. PUT_LINE('Uncompressed Blocks: ' || TO_CHAR(unco_blks, '99999')); DBMS_OUTPUT. PUT_LINE('Compressed Blocks: ' || TO_CHAR(comp_blks, '99999')); DBMS_OUTPUT. PUT_LINE('Block Compression %: ' || TO_CHAR(pcti_blks, '999999. 99')); DBMS_OUTPUT. PUT_LINE('Uncompressed Rows Per Block: ' || TO_CHAR(unco_rspb, '99999')); DBMS_OUTPUT. PUT_LINE('Compressed Rows Per Block: ' || TO_CHAR(comp_rspb, '99999')); DBMS_OUTPUT. PUT_LINE('Rows Per Block Compression %: ' || TO_CHAR(pcti_rspb, '999999. 99')); END; / DBMS_COMPRESSION Results for AP. RANDOMIZED_SORTED Compression Level: WAREHOUSE LOW WAREHOUSE HIGH ARCHIVE LOW ARCHIVE HIGH Compression Ratio 2. 9% 5. 4% 5. 5% 7. 4% Compressed Blocks 5305 2885 2833 2090 Block Compression Ratio 33. 9% 18. 4% 18. 1% 13. 5% Compressed Rows Per Block 566 1040 1059 1435 Rows Per Block Compression Ratio 296. 3% 544. 5% 554. 5% 751. 3% Original table statistics: 3 M rows, 15654 blocks Copyright 2013, Zero Defect Computing, Inc. - 21 -

SELECT yrnbr, wknbr, SUM(counted) FROM ( SELECT TO_CHAR(key_date, 'yyyy') yrnbr , TO_CHAR(key_date, 'ww') wknbr , COUNT(*) counted FROM ap. randomized_sorted WHERE key_date BETWEEN TO_DATE('2003 -07 -01', 'yyyy-mm-dd') RANDOMIZED_UNSORTED: Same 30 M rows, inso sorted in KEY_DESC order, but nopossible parallelism EHCC_PARTED: Partitioned on KEY_DATE, parallelism can effectively RANDOMIZED_SORTED: 30 M rows, sorted KEY_ID order, butbe noused parallelism AND TO_DATE('2008 -12 -31', 'yyyy-mm-dd') GROUP BY ----------------------------------------------------------------------------------------------------------------------------------------------------------------------TO_CHAR(key_date, 'yyyy') | |Id | |Name Rows || Bytes |Temp. Spc| Cost (%CPU)| Time | Id | |Operation Name || Rows Cost | Name |Bytes Rows |Temp. Spc| | Bytes |Temp. Spc| Cost. Time (%CPU)| | Time | ------------------------------------------------------------------------------------------------------------, TO_CHAR(key_date, 'ww') ----------------------------------------------------------| | 0 0| |SELECT STATEMENT || 3184 K| 60 M| 35109 |(2)| 00: 07: 02 | SELECTUNION STATEMENT || 3184 K| 60 M| || 36716 00: 07: 21 | | 3184 K| 60 M| 3506 (4)| | 00: 43 | | | 1 1| | SORT GROUP BY ROLLUP | | 3184 K| 60 M| | 35109 (2)| 00: 07: 02 SORT GROUP BY ROLLUP | | 3184 K| 60 M| | 36716 (2)| 00: 07: 21 | PX COORDINATOR | | | | SELECT | | 2 2| | VIEW || 3184 K| 60 M| 34961 |(2)| 00: 07: 00 | VIEW || 3184 K| 60 M| || 36568 00: 07: 19 PX SEND QC (ORDER) | : TQ 10006 | 3184 K| 60 M| 3506 (4)| | 00: 43 | TO_CHAR(key_date, 'yyyy') | | 3 3| | SORT | | yrnbr 3184 K| 24 M| 49 M| 34961 (56)| 00: 07: 00 | SORTUNIQUE || 3184 K| 24 M| 49 M| 00: 07: 19 ORDER BY | | 3184 K| 60 M|36568 |(56)| 3506 (4)| | 00: 43 | | | 4 4| | UNION-ALL | | wknbr | , TO_CHAR(key_date, 'ww') UNION-ALL || || || PX RECEIVE | | 3184 K| 60 M| | 3506 (4)| | 00: 43 | | | 5 5| | HASH GROUP BY || 1629 K| 12 M| 25 M| 15808 |(2)| 00: 03: 10 | HASH GROUP BY counted || 1629 K| 12 M| 25 M| 00: 03: 17 PX , COUNT(*) SEND RANGE | : TQ 10005 | 3184 K| 60 M|16344 3506 (4)| | 00: 43 | |* TABLE ACCESS STORAGE FULL| RANDOMIZED_UNSORTED | 1652 K| 12 M| | 4435 3899 |(1)| (2)| 00: 47 | |* TABLE ACCESS STORAGE FULL| RANDOMIZED_SORTED | 12 M| | 00: 54 | | 6 6| | SORT GROUP BY | | 3184 K| 60 M| 3506 (4)| 00: 43 | FROM ap. randomized_sorted | | 7 7| | HASH BY || 1184 K| 9256 K| 18 M| 12555 |(2)| 00: 02: 31 | HASH GROUP BY || 1184 K| 18 M| 00: 02: 38 PXGROUP RECEIVE | | 9256 K| 3184 K| 60 M|13091 3506 (4)| | 00: 43 | WHERE key_date BETWEEN TO_DATE('2007 -06 -30', 'yyyy-mm-dd'). |. . 8. | PX SEND HASH | : TQ 10004 | 3184 K| 60 M| | 3506 (4)| 00: 43 | 'yyyy-mm-dd') -----------------------------------------------------------------------------------------------------------| 9 | SORT GROUP BY ROLLUPAND TO_DATE('2011 -06 -30', | | 3184 K| 60 M| | 3506 (4)| 00: 43 | Statistic Name Bytes (MB) GROUP Statistic Name Bytes (MB) (3)| 00: 42 | | 10 | VIEWBY | | 3184 K| 60 M| | 3468 --------------------------------------------| 11 | SORT UNIQUE | | 3184 K| 24 M| 49 M| 3468 (53)| 00: 42 | --------------------------------------------TO_CHAR(key_date, 'yyyy') physical read total bytes 330. 12 … physical read total bytes 368. 49 , TO_CHAR(key_date, 'ww') cell IO 42. 33 Statistic Name Bytes (MB) cellphysical IOinterconnectbytes 42. 56 UNION cell physical IO bytes eligible for predicate offload 330. 07 --------------------------------------------cell physical IO bytes eligible for predicate offload 368. 09 SELECT cell bytes 42. 29 physical read. IO total bytes 54. 27 cellphysical IOinterconnect bytesreturnedby bysmartscan 42. 15 TO_CHAR(key_date, 'yyyy') yrnbr cell uncompressed bytes 330. 30 cell. IO physical IO interconnect bytes 25. 25 IO uncompressed bytes 368. 33 , TO_CHAR(key_date, 'ww')predicate wknbr cell physical IO bytes eligible for offload 47. 66 , COUNT(*) countedbytes returned by smart scan Total Wait Avg cell physical IO interconnect 18. 64 Total Wait Avg. Wait FROM ap. randomized_sorted Wait Event Waits Time (s) Time cell IO uncompressed bytes 180. 51 Wait Event Waits Time (s) Time(s) WHERE key_date >= TO_DATE('2011 -10 -01', 'yyyy-mm-dd') ---------------------------------------------------------------------------------------cell scan Total 62 Wait GROUP BY cellsmarttable scan 73 Total. 05 Avg. 0008. 0006 cell single block physical read 6. 01. 001 Wait Event Waits Time (s) cell multiblock. TO_CHAR(key_date, physical read 1 0. 0006 'yyyy') -------------------------------------------cell single block physical read 'ww') 49. 03. 0005 , TO_CHAR(key_date, cell list of) blocks physical read 1 0. 0005 cell single block physical read 8. 05. 0056 GROUP BY ROLLUP (yrnbr, wknbr) ORDER BY yrnbr, wknbr; ; Hybrid Columnar Compression Impact HCC can make incredible differences in performance! Copyright 2013, Zero Defect Computing, Inc. - 22 -

Impact of Sorted Row Sources Columns typically used as predicate targets can benefit dramatically from sorting during initial load ap. randomized_unsorted SELECT * FROM ap. randomized_sorted WHERE key_id BETWEEN 110000 AND 130000; And A simple now query the same against query, data but sorted onnot columns ascending sorted in. KEY_ID order when order, results loaded: in a full table scan! --------------------------------------------------------------------------------------------------| Name | Rows | Bytes | Cost (%CPU)| Time | | | Id Id | | Operation | | Name | Rows | Bytes | Cost (%CPU)| Time --------------------------------------------------------------------------------------------------| 0 | 20002 644 K| (0)| 00: 03 | 0 | | SELECT STATEMENT | | | 20002 | |644 K| 3893186(1)| 00: 47 | | | 1 | TABLE ACCESS BY INDEX ROWID| RANDOMIZED_SORTED | 20002 | 644 K| 186 (0)| 00: 03 |* 1 | TABLE ACCESS STORAGE FULL| RANDOMIZED_UNSORTED | 20002 | 644 K| 3893 (1)| 00: 47 | | |* 2 | INDEX RANGE SCAN | RANDOMIZED_SORTED_PK | 20002 | | 46 (0)| 00: 01 | --------------------------------------------------------------------------------------------------Statistic Name Bytes (MB) ----------------------------------------------------------------------------------------physical read total bytes 110. 09 physical read total bytes 1. 21 cell physical IO interconnect bytes. 83 cell physical IO interconnect bytes 1. 21 cell physical IO bytes eligible for predicate offload 110. 02 cell predicate. 00 cell physical IO IO bytes eligible saved by for storage index offload. 27 cell physical IO bytes saved by storage index. 00 cell physical IO interconnect bytes returned by smart scan. 76 cell physical IO interconnect bytes returned by smart scan. 00 cell IO uncompressed bytes 109. 76 cell IO uncompressed bytes. 00 Wait Event -----------------------------------------------------------cell single block physical read cell blockscan physical read cell single smart table Total Waits ---------8 155 45 Copyright 2013, Zero Defect Computing, Inc. Total Wait Time (s) ---------. 02. 09. 05 Avg Wait Avg Time. Wait (s) Time (s) ---------. 0028. 0006. 0012 - 23 -

Is Smart Scan Always Optimal? Smart Scan may actually be more expensive than expected when: Row source row migration is excessive Row sources are partially or completely modified and these changes haven’t been committed yet Storage cell CPUs are overloaded These cases may be unavoidable without changing application logic or execution patterns! Copyright 2013, Zero Defect Computing, Inc. - 24 -

Excessive Row Migration Row migration requires additional mandatory single-block reads to retrieve the migrated rows: SELECT * FROM ap. randomized_unsorted WHERE UPPER(SUBSTR(key_desc, 1, 3)) IN ('AAA', 'BBB', 'CCC', 'XXX', 'YYY', 'ZZZ'); Note This the resulting uses a increase scalar Then, query those same rows arein physical function to reads filter and about cell 18% physical of all updated and the changes I/O interconnect committed. thecontents row size rows based. Since on bytes! the grows, row migration column. occurs. of the KEY_DESC ------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------| 0 | SELECT STATEMENT | | 175 K| 5657 K| 12 M| 9814 4514 (15)| (7)| 00: 01: 58 00: 55 | |* 1 | TABLE ACCESS STORAGE FULL| RANDOMIZED_UNSORTED | 175 K| 5657 K| 12 M| 9814 4514 (15)| (7)| 00: 01: 58 00: 55 | ------------------------------------------------Statistic Name Bytes (MB) ----------------------------------------physical read total bytes 110. 08 256. 30 cell physical IO interconnect bytes 20. 72 30. 33 cell physical IO bytes eligible for predicate offload 110. 02 256. 20 cell physical IO interconnect bytes returned by smart scan 20. 67 30. 22 cell IO uncompressed bytes 256. 40 110. 12 Total Wait Avg Wait Event Waits Time (s) ------------------------------ -----cell single block physical read 10 7. 04. 01. 0063. 0005 cell multiblock smart tablephysical scan read 33 1. 04 0. 0011. 0006 cell smart table scan 41. 02. 0005 Copyright 2013, Zero Defect Computing, Inc. - 25 -

Partially Updated Row Sources If a query’s row sources have been partially modified, but those changes are as yet uncommitted, it will cost significantly more to retrieve the unmodified data: SELECT * FROM ap. randomized_unsorted WHERE UPPER(SUBSTR(key_desc, 1, 3)) IN ('AAA', 'BBB', 'CCC', 'XXX', 'YYY', 'ZZZ'); Note significant We’ll the use the 30% identical Then, about ofincreases thequery, table’sin physical reads, I/O whichare filters an cell 18% sample of rows updated …physical but since interconnect bytes, and waits. entire table based on therow the row size grows, it forces KEY_DESC column’s contents. migration: ------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 175 K| 12 M| 9814 (7)| 00: 01: 58 | | 0 | SELECT STATEMENT | RANDOMIZED_UNSORTED | 4514 (15)| 00: 55 | |* 1 TABLE ACCESS STORAGE FULL| 175 K| 5657 K| 12 M| 9814 (7)| 00: 01: 58 |* 1 | TABLE ACCESS STORAGE FULL| RANDOMIZED_UNSORTED | 175 K| 5657 K| 4514 (15)| 00: 55 | ------------------------------------------------------------------------------------------------Statistic Name Bytes (MB) ----------------------------------------------------------------------------------------physical read total bytes 422. 17 physical read IO total bytes 110. 08 cell physical interconnect bytes 332. 32 interconnect bytes 20. 72 cell physical IO bytes eligible for predicate offload 256. 32 bytes eligible for returned predicateby offload 110. 02 cell physical IO interconnect bytes smart scan 166. 47 cell physical IO interconnect bytes returned by smart scan 20. 67 IO uncompressed bytes 256. 32 cell IO uncompressed bytes 110. 12 Total Wait Avg Wait Total Wait Event Total Wait Avg Waits Time (s) Wait Event Waits Time (s) -----------------------------------------------------------------------------cell single block physical read 21153 -----14. 04 -----. 0007 single physical read 7. 04. 0063 cell list ofblocks physical read 1 0. 0009 cell smart table scan 33. 04. 0011 140. 05. 0003 Copyright 2013, Zero Defect Computing, Inc. - 26 -

Fully Updated Row Sources If a query’s row sources have been completely changed but those changes are as yet committed, the additional retrieval costs could be enormous! SELECT * FROM ap. randomized_unsorted WHERE key_sts = 'Y' Note the horrendous increase This query will table’s retrieve a arein Then all of the rows physical reads, cellof physical I/O significant rows (well updated bynumber changing KEY_STS interconnect bytes, waits!are over from the table. to “Z”, 99%) but again, noand changes committed. ------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------| 0 | SELECT STATEMENT | | 2999 K| 94 M| 3929 (2)| 00: 48 | |* 1 | TABLE ACCESS STORAGE FULL| RANDOMIZED_UNSORTED | 2999 K| 94 M| 3929 (2)| 00: 48 | ------------------------------------------------Statistic Name Bytes (MB) ----------------------------------------physical read total bytes 110. 08 555. 61 cell physical IO interconnect bytes 110. 10 555. 64 cell physical IO bytes eligible for predicate offload 110. 02 cell physical IO interconnect bytes returned by smart scan 110. 04 110. 05 cell IO uncompressed bytes 110. 48 110. 02 Total Wait Avg Wait Event Waits Time (s) (s) ------------------------------ -----cell single smart table blockscan physical read 57035 100 34. 45. 03. 0006 cell smart singletable blockscan physical read 98 7. 02. 04. 0004 0035 Copyright 2013, Zero Defect Computing, Inc. - 27 -

Overloaded CPUs on Storage Cells Exadata may simply relocate query operations back to the buffer cache of one or more compute nodes when it detects a storage cell’s CPUs are overloaded: Statistic Name Bytes (MB) ----------------------------------------physical read total bytes 175. 80 physical write total bytes. 00 cell physical IO interconnect bytes 28. 33 cell physical IO bytes sent directly to DB node to balance. CPU u 24. 16 cell physical IO bytes saved during optimized file creation. 00 cell physical IO bytes saved during optimized RMAN file restore. 00 cell physical IO bytes eligible for predicate offload 175. 80 cell physical IO bytes saved by storage index. 00 cell physical IO interconnect bytes returned by smart scan 28. 23 table fetch continued row. 00 cell IO uncompressed bytes 175. 80 Total Wait Avg Event Waits Time (s) ------------------------------ -----cell smart table scan 3178 18. 36 0. 17 Copyright 2013, Zero Defect Computing, Inc. - 28 -

Other Resources If I have seen farther than others, it is because I was standing on the shoulders of giants. - Sir Isaac Newton Tanel Poder’s excellent recent blogs on Smart Scan Fritz Hoogland’s recent blogging on Oracle Exadata Smart Scan internals IOUG 2012 Exadata SIG presentation on Smart Scan from UBS Bank Copyright 2013, Zero Defect Computing, Inc. - 29 -

Thank You For Your Kind Attention Please fill out a session evaluation form! Session P 65 When Smart Scan Plays Dumb, Teach Exadata Some New Tricks If you have any questions or comments, feel free to: ØE-mail me at jczuprynski@zerodefectcomputingcom ØFollow my blog (Generally, It Depends): http: //jimczuprynski. wordpress. com ØFollow me on Twitter (@jczuprynski) ØConnect with me on Linked. In (Jim Czuprynski) Copyright 2013, Zero Defect Computing, Inc. - 30 -