1 / 30

When Smart Scan Plays Dumb, Teach Exadata Some New Tricks

When Smart Scan Plays Dumb, Teach Exadata Some New Tricks. Jim Czuprynski Zero Defect Computing, Inc. April 17, 2013. 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

simeon
Download Presentation

When Smart Scan Plays Dumb, Teach Exadata Some New Tricks

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

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

  2. My Credentials • 30+ years of database-centric IT experience • Oracle DBA since 2001 • Oracle 9i, 10g, 11g 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 OpenWorld, IOUG COLLABORATE11, and IOUG COLLABORATE13 • Oracle-centric blog (Generally, It Depends)

  3. 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

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

  5. 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

  6. Smart Flash Features • Leverages Exadata storage cells’ write-through-cache 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: 10K IOPS • Total I/O response time per X2-2 full rack: 1.5M IOPS

  7. Column and Predicate Filtering Exadata also dramatically limits the amount of data retrieved when selection criteria and specificcolumns 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

  8. 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

  9. Hybrid Columnar Compression • Hybrid Columnar Compression (HCC) provides two extra data compression methods: • Warehouse Compression (10X – 15X): Data that’s accessed relatively infrequently • Archival Compression (15X – 50X): Data that’s only retained for long-term periodsor just for regulatoryrequirements • Compute nodes perform compression • Storage cells perform decompression • Compression units store compressed data

  10. HCC: Compression Units Compression Unit Block Header Block Header Block Header Block Header CU Header C2 C5 C7 C8 C1 C4 C3 C6 C5 C2 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

  11. Why Didn’t Smart Scan Happen?

  12. Did Smart Scan Really Happen? 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 As Reagan once said to Gorbachev: (Doveryai, no proveryai) Trust,but verify Доверяй, нопроверяй

  13. 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 LOBdatatype • Row-level dependency is enabled • Over 255 columns in a query* * Unless HCC is used on all tables in query!

  14. 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!

  15. A Successful Smart Scan 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:00:22 | | 1 | SORT AGGREGATE | | 1 | 6 | | | | 2 | PX COORDINATOR | | | | | | | 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 6 | | | | 4 | SORT AGGREGATE | | 1 | 6 | | | | 5 | PX BLOCK ITERATOR | | 1500K| 8789K| 1804 (2)| 00:00:22 | |* 6 | INDEX STORAGE FAST FULL SCAN| RANDOMIZED_SORTED_PK | 1500K| 8789K| 1804 (2)| 00: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

  16. Impact: REVERSE KEY Index In this example, the primary key index for table AP.INVOICES cannot be used for Smart Scan: Even though Fast Full Scan of PK index is indicated, statistics and waits show it didn’t happen at all! SELECT MIN(invoice_id), MAX(invoice_id) FROM ap.invoices; ------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 4 | 5 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 4 | | | | 2 | INDEX STORAGE FAST FULL SCAN| INVOICES_PK_IDX | 4500 | 18000 | 5 (0)| 00: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 Total Wait Avg Wait Wait Event Waits Time (s) Time (s) ------------------------------------------------------------ ---------- ---------- ---------- cell single block physical read 8 .02 .0031 cell multiblock physical read 3 0 .0011

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

  18. 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 8KB = 4MB) • _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

  19. Impact: _small_table_threshold If a table or index is < 2% of current buffer cache size, then it’s excluded from Smart Scan: Fast Full Scan of PK index fails because the total number of blocks scanned is well below the size of _smart_table_threshold SELECT MIN(key_id), AVG(key_id), MAX(key_id) FROM ap.randomized_sorted WHERE key_id BETWEEN 1000000 AND 1199999; ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 6 | 441 (1)| 00:00:06 | | 1 | SORT AGGREGATE | | 1 | 6 | | | |* 2 | INDEX RANGE SCAN| RANDOMIZED_SORTED_PK | 200K| 1171K| 441 (1)| 00: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 Total Wait Avg Wait Wait Event Waits Time (s) Time (s) ------------------------------------------------------------ ---------- ---------- ---------- cell single block physical read 454 .22 .0005

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

  21. 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; comp_type VARCHAR2(100); pcti_rspb NUMBER; BEGIN DBMS_COMPRESSION.GET_COMPRESSION_RATIO ( scratchtbsname => 'USERS' ,ownname => 'AP' ,tabname => 'RANDOMIZED_SORTED' ,partname => NULL ,comptype => DBMS_COMPRESSION.COMP_FOR_QUERY_HIGH ,blkcnt_cmp => comp_blks ,blkcnt_uncmp => unco_blks ,row_cmp => comp_rspb ,row_uncmp => unco_rspb ,cmp_ratio => comp_ratio ,comptype_str => comp_type ); 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, '999999999')); DBMS_OUTPUT.PUT_LINE('Compressed Blocks: ' || TO_CHAR(comp_blks, '999999999')); DBMS_OUTPUT.PUT_LINE('Block Compression %: ' || TO_CHAR(pcti_blks, '999999.99')); DBMS_OUTPUT.PUT_LINE('Uncompressed Rows Per Block: ' || TO_CHAR(unco_rspb, '999999999')); DBMS_OUTPUT.PUT_LINE('Compressed Rows Per Block: ' || TO_CHAR(comp_rspb, '999999999')); DBMS_OUTPUT.PUT_LINE('Rows Per Block Compression %: ' || TO_CHAR(pcti_rspb, '999999.99')); END; / Compression Advisor self-check validation successful. select count(*) on both Uncompressed and EHCC Compressed format = 3000000 rows Compression Type: "Compress For Query Low" Compression Ratio: 2.90 Uncompressed Blocks: 15654 Compressed Blocks: 5305 Block Compression %: 33.89 Uncompressed Rows Per Block: 191 Compressed Rows Per Block: 566 Rows Per Block Compression %: 296.34

  22. Hybrid Columnar Compression Impact 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') AND TO_DATE('2008-12-31', 'yyyy-mm-dd') GROUP BY TO_CHAR(key_date, 'yyyy') ,TO_CHAR(key_date, 'ww') UNION 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('2007-06-30', 'yyyy-mm-dd') AND TO_DATE('2011-06-30', 'yyyy-mm-dd') GROUP BY TO_CHAR(key_date, 'yyyy') ,TO_CHAR(key_date, 'ww') UNION SELECT TO_CHAR(key_date, 'yyyy') yrnbr ,TO_CHAR(key_date, 'ww') wknbr ,COUNT(*) counted FROM ap.randomized_sorted WHERE key_date >= TO_DATE('2011-10-01', 'yyyy-mm-dd') GROUP BY TO_CHAR(key_date, 'yyyy') ,TO_CHAR(key_date, 'ww') ) GROUP BY ROLLUP (yrnbr, wknbr) ORDER BY yrnbr, wknbr;; HCC can make incredible differences in performance! EHCC_PARTED: Partitioned on KEY_DATE, so parallelism can be used effectively RANDOMIZED_SORTED: 30M rows, sorted in KEY_ID order, but no parallelism possible RANDOMIZED_UNSORTED: Same 30M rows, sorted in KEY_DESC order, but no parallelism ------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 3184K| 60M| | 36716 (2)| 00:07:21 | | 1 | SORT GROUP BY ROLLUP | | 3184K| 60M| | 36716 (2)| 00:07:21 | | 2 | VIEW | | 3184K| 60M| | 36568 (2)| 00:07:19 | | 3 | SORT UNIQUE | | 3184K| 24M| 49M| 36568 (56)| 00:07:19 | | 4 | UNION-ALL | | | | | | | | 5 | HASH GROUP BY | | 1629K| 12M| 25M| 16344 (2)| 00:03:17 | |* 6 | TABLE ACCESS STORAGE FULL| RANDOMIZED_SORTED | 1652K| 12M| | 4435 (1)| 00:00:54 | | 7 | HASH GROUP BY | | 1184K| 9256K| 18M| 13091 (2)| 00:02:38 | . . . ------------------------------------------------------------------------------------------------------------ Statistic Name Bytes (MB) -------------------------------------------------------------------------------- ---------- physical read total bytes 368.49 cell physical IO interconnect bytes 42.56 cell physical IO bytes eligible for predicate offload 368.09 cell physical IO interconnect bytes returned by smart scan 42.15 cell IO uncompressed bytes 368.33 Total Total Wait Avg Wait Wait Event Waits Time (s) Time (s) ------------------------------------------------------------ ---------- ---------- ---------- cell smart table scan 73 .05 .0006 cell multiblock physical read 1 0 .0006 cell single block physical read 49 .03 .0005 --------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3184K| 60M| | 3506 (4)| 00:00:43 | | 1 | PX COORDINATOR | | | | | | | | 2 | PX SEND QC (ORDER) | :TQ10006 | 3184K| 60M| | 3506 (4)| 00:00:43 | | 3 | SORT ORDER BY | | 3184K| 60M| | 3506 (4)| 00:00:43 | | 4 | PX RECEIVE | | 3184K| 60M| | 3506 (4)| 00:00:43 | | 5 | PX SEND RANGE | :TQ10005 | 3184K| 60M| | 3506 (4)| 00:00:43 | | 6 | SORT GROUP BY | | 3184K| 60M| | 3506 (4)| 00:00:43 | | 7 | PX RECEIVE | | 3184K| 60M| | 3506 (4)| 00:00:43 | | 8 | PX SEND HASH | :TQ10004 | 3184K| 60M| | 3506 (4)| 00:00:43 | | 9 | SORT GROUP BY ROLLUP | | 3184K| 60M| | 3506 (4)| 00:00:43 | | 10 | VIEW | | 3184K| 60M| | 3468 (3)| 00:00:42 | | 11 | SORT UNIQUE | | 3184K| 24M| 49M| 3468 (53)| 00:00:42 | … Statistic Name Bytes (MB) -------------------------------------------------------------------------------- ---------- physical read total bytes 54.27 cell physical IO interconnect bytes 25.25 cell physical IO bytes eligible for predicate offload 47.66 cell physical IO interconnect bytes returned by smart scan 18.64 cell IO uncompressed bytes 180.51 Total Total Wait Avg Wait Wait Event Waits Time (s) Time (s) ------------------------------------------------------------ ---------- ---------- ---------- cell list of blocks physical read 1 0 .0005 cell single block physical read 8 .05 .0056 -------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3184K| 60M| | 35109 (2)| 00:07:02 | | 1 | SORT GROUP BY ROLLUP | | 3184K| 60M| | 35109 (2)| 00:07:02 | | 2 | VIEW | | 3184K| 60M| | 34961 (2)| 00:07:00 | | 3 | SORT UNIQUE | | 3184K| 24M| 49M| 34961 (56)| 00:07:00 | | 4 | UNION-ALL | | | | | | | | 5 | HASH GROUP BY | | 1629K| 12M| 25M| 15808 (2)| 00:03:10 | |* 6 | TABLE ACCESS STORAGE FULL| RANDOMIZED_UNSORTED | 1652K| 12M| | 3899 (2)| 00:00:47 | | 7 | HASH GROUP BY | | 1184K| 9256K| 18M| 12555 (2)| 00:02:31 | . . . ------------------------------------------------------------------------------------------------------------ Statistic Name Bytes (MB) -------------------------------------------------------------------------------- ---------- physical read total bytes 330.12 cell physical IO interconnect bytes 42.33 cell physical IO bytes eligible for predicate offload 330.07 cell physical IO interconnect bytes returned by smart scan 42.29 cell IO uncompressed bytes 330.30 Total Total Wait Avg Wait Wait Event Waits Time (s) Time (s) ------------------------------------------------------------ ---------- ---------- ---------- cell smart table scan 62 .05 .0008 cell single block physical read 6 .01 .001

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

  24. 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 completelymodified 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!

  25. 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 the resulting increase in physical reads and cell physical I/O interconnect bytes! This query uses a scalar function to filter about 18% of all rows based on the contents of the KEY_DESC column. Then, those same rows are updated and the changes committed. Since the row size grows, row migration occurs. ------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 175K| 5657K| 4514 (15)| 00:00:55 | |* 1 | TABLE ACCESS STORAGE FULL| RANDOMIZED_UNSORTED | 175K| 5657K| 4514 (15)| 00:00:55 | ------------------------------------------------------------------------------------------------- Statistic Name Bytes (MB) -------------------------------------------------------------------------------- ---------- physical read total bytes 110.08 cell physical IO interconnect bytes 20.72 cell physical IO bytes eligible for predicate offload 110.02 cell physical IO interconnect bytes returned by smart scan 20.67 cell IO uncompressed bytes 110.12 Total Total Wait Avg Wait Wait Event Waits Time (s) Time (s) ------------------------------------------------------------ ---------- ---------- ---------- cell single block physical read 7 .04 .0063 cell smart table scan 33 .04 .0011 ------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 175K| 12M| 9814 (7)| 00:01:58 | |* 1 | TABLE ACCESS STORAGE FULL| RANDOMIZED_UNSORTED | 175K| 12M| 9814 (7)| 00:01:58 | ------------------------------------------------------------------------------------------------- Statistic Name Bytes (MB) -------------------------------------------------------------------------------- ---------- physical read total bytes 256.30 cell physical IO interconnect bytes 30.33 cell physical IO bytes eligible for predicate offload 256.20 cell physical IO interconnect bytes returned by smart scan 30.22 cell IO uncompressed bytes 256.40 Total Total Wait Avg Wait Wait Event Waits Time (s) Time (s) ------------------------------------------------------------ ---------- ---------- ---------- cell single block physical read 10 .01 .0005 cell multiblock physical read 1 0 .0006 cell smart table scan 41 .02 .0005

  26. 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 the significant increases in physical reads, cell physical I/O interconnect bytes, and waits. We’ll use the identical query, which filters an 18% sample of the entire table based on the KEY_DESC column’s contents. Then, about 30% of the table’s rows are updated … but since the row size grows, it forces row migration: ------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 175K| 12M| 9814 (7)| 00:01:58 | |* 1 | TABLE ACCESS STORAGE FULL| RANDOMIZED_UNSORTED | 175K| 12M| 9814 (7)| 00:01:58 | ------------------------------------------------------------------------------------------------- Statistic Name Bytes (MB) -------------------------------------------------------------------------------- ---------- physical read total bytes 422.17 cell physical IO interconnect bytes 332.32 cell physical IO bytes eligible for predicate offload 256.32 cell physical IO interconnect bytes returned by smart scan 166.47 cell IO uncompressed bytes 256.32 Total Total Wait Avg Wait Wait Event Waits Time (s) Time (s) ------------------------------------------------------------ ---------- ---------- ---------- cell single block physical read 21153 14.04 .0007 cell list of blocks physical read 1 0 .0009 cell smart table scan 140 .05 .0003 ------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 175K| 5657K| 4514 (15)| 00:00:55 | |* 1 | TABLE ACCESS STORAGE FULL| RANDOMIZED_UNSORTED | 175K| 5657K| 4514 (15)| 00:00:55 | ------------------------------------------------------------------------------------------------- Statistic Name Bytes (MB) -------------------------------------------------------------------------------- ---------- physical read total bytes 110.08 cell physical IO interconnect bytes 20.72 cell physical IO bytes eligible for predicate offload 110.02 cell physical IO interconnect bytes returned by smart scan 20.67 cell IO uncompressed bytes 110.12 Total Total Wait Avg Wait Wait Event Waits Time (s) Time (s) ------------------------------------------------------------ ---------- ---------- ---------- cell single block physical read 7 .04 .0063 cell smart table scan 33 .04 .0011

  27. 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! Note the horrendous increase in physical reads, cell physical I/O interconnect bytes, and waits! This query will retrieve a significant number of rows (well over 99%) from the table. SELECT * FROM ap.randomized_unsorted WHERE key_sts = 'Y' Then all of the table’s rows are updated by changing KEY_STS to “Z”, but again, no changes are committed. ------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2999K| 94M| 3929 (2)| 00:00:48 | |* 1 | TABLE ACCESS STORAGE FULL| RANDOMIZED_UNSORTED | 2999K| 94M| 3929 (2)| 00:00:48 | ------------------------------------------------------------------------------------------------- Statistic Name Bytes (MB) -------------------------------------------------------------------------------- ---------- physical read total bytes 110.08 cell physical IO interconnect bytes 110.10 cell physical IO bytes eligible for predicate offload 110.02 cell physical IO interconnect bytes returned by smart scan 110.04 cell IO uncompressed bytes 110.48 Total Total Wait Avg Wait Wait Event Waits Time (s) Time (s) ------------------------------------------------------------ ---------- ---------- ---------- cell smart table scan 100 .03 .0003 cell single block physical read 7 .02 .0035 ------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2999K| 94M| 3929 (2)| 00:00:48 | |* 1 | TABLE ACCESS STORAGE FULL| RANDOMIZED_UNSORTED | 2999K| 94M| 3929 (2)| 00:00:48 | ------------------------------------------------------------------------------------------------- Statistic Name Bytes (MB) -------------------------------------------------------------------------------- ---------- physical read total bytes 555.61 cell physical IO interconnect bytes 555.64 cell physical IO bytes eligible for predicate offload 110.02 cell physical IO interconnect bytes returned by smart scan 110.05 cell IO uncompressed bytes 110.02 Total Total Wait Avg Wait Wait Event Waits Time (s) Time (s) ------------------------------------------------------------ ---------- ---------- ---------- cell single block physical read 57035 34.45 .0006 cell smart table scan 98 .04 .0004

  28. 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 balanceCPU 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 Total Wait Avg Event Waits Time (s) Time (s) ------------------------------------------------------------ ---------- ---------- ---------- cell smart table scan 3178 18.36 0.17

  29. Other Resources • Tanel Poder’sexcellent 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 If I have seen farther than others, it is because I was standing on the shoulders of giants. - Sir Isaac Newton

  30. Thank You For Your Kind Attention Please fill out a session evaluation form! • Session P65 • 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 LinkedIn (Jim Czuprynski)

More Related