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.
Download ReportTranscript 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.
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 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)
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
SmartScan-enabled
execution plan:
TABLE ACCESS STORAGE FULL
(AP.EHCC_PARTED)
30M rows
(4 GB)
iDB 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
4
Result set built from
just rows and columns
needed to satisfy
query
Copyright 2013, Zero Defect Computing, Inc.
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-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
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
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 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
C1
C2
Block Header
C2
C3
Block Header
C5
Block Header
C7
C8
C4
C5
C6
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
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
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: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
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 can 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 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
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: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
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 VARCHAR2(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
%:=> comp_rspb
33.89
,row_cmp
,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, '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;
/
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: 3M 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
30M
rows,
sorted
in
KEY_DESC
order,
but nopossible
parallelism
EHCC_PARTED:
Partitioned
on
KEY_DATE,
parallelism
can
effectively
RANDOMIZED_SORTED:
30M
rows,
sorted
inso
KEY_ID
order,
butbe
noused
parallelism
AND TO_DATE('2008-12-31', 'yyyy-mm-dd')
GROUP BY
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| |Id
| |Name
Rows ||
Bytes
|TempSpc|
Cost (%CPU)|
(%CPU)|
Time
Id | |Operation
OperationTO_CHAR(key_date, 'yyyy')
Name
|| Rows
Cost
||
| Name
|Bytes
Rows |TempSpc|
| Bytes |TempSpc|
CostTime
(%CPU)| Time
|
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------,TO_CHAR(key_date, 'ww')
--------------------------------------------------------------------------------------------------------------------| | 0 0| |SELECT
STATEMENT
||
3184K|
60M|
35109 |(2)|
(2)|
00:07:02
|
SELECTUNION
STATEMENT
|| 3184K|
60M|
|| 36716
00:07:21
|
| 3184K|
60M|
3506
(4)| |
00:00:43
|
| | 1 1| | SORT
GROUP
BY
ROLLUP
|
|
3184K|
60M|
|
35109
(2)|
00:07:02
SORT
GROUP
BY
ROLLUP
|
|
3184K|
60M|
|
36716
(2)|
00:07:21
|
PX COORDINATOR
|
|
|
|
|
| |
|
SELECT
| | 2 2| | VIEW
||
3184K|
60M|
34961 |(2)|
(2)|
00:07:00
|
VIEW
|| 3184K|
60M|
|| 36568
00:07:19
PX SEND QC (ORDER)
| :TQ10006
| 3184K|
60M|
3506
(4)| |
00:00:43
|
TO_CHAR(key_date, 'yyyy')
| | 3 3| |
SORT
| | yrnbr
3184K|
24M|
49M|
34961 (56)|
00:07:00
|
SORTUNIQUE
UNIQUE
|| 3184K|
24M|
49M|
00:07:19
ORDER BY
|
| 3184K|
60M|36568
|(56)|
3506
(4)| |
00:00:43
|
| | 4 4| |
UNION-ALL
| | wknbr
|
,TO_CHAR(key_date, 'ww')
UNION-ALL
||
||
||
||
||
PX RECEIVE
|
| 3184K|
60M|
| 3506
(4)| |
00:00:43
|
| | 5 5| |
HASH
GROUP
BY
||
1629K|
12M|
25M|
15808 |(2)|
(2)|
00:03:10
|
HASH
GROUP
BY counted
|| 1629K|
12M|
25M|
00:03:17
PX ,COUNT(*)
SEND
RANGE
| :TQ10005
| 3184K|
60M|16344
3506
(4)| |
00:00:43
|
|*
TABLE
ACCESS
STORAGE
FULL|
RANDOMIZED_UNSORTED
| 1652K|
1652K|
12M|
| 4435
3899 |(1)|
(2)|
00:00:47
|
|*
TABLE
ACCESS
STORAGE
FULL|
RANDOMIZED_SORTED
|
12M|
|
00:00:54
|
| 6 6| |
SORT
GROUP
BY
|
|
3184K|
60M|
3506
(4)|
00:00:43
|
FROM
ap.randomized_sorted
| | 7 7| |
HASH
BY
||
1184K|
9256K|
18M|
12555 |(2)|
(2)|
00:02:31
|
HASH
GROUP
BY
|| 1184K|
18M|
00:02:38
PXGROUP
RECEIVE
|
| 9256K|
3184K|
60M|13091
3506
(4)| |
00:00:43
|
WHERE key_date BETWEEN TO_DATE('2007-06-30', 'yyyy-mm-dd')
.|
.. .. 8
. |
PX SEND HASH
| :TQ10004
| 3184K|
60M|
| 3506
(4)| 00:00:43 |
'yyyy-mm-dd')
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
9 |
SORT GROUP BY ROLLUPAND TO_DATE('2011-06-30',
|
| 3184K|
60M|
| 3506
(4)| 00:00:43 |
Statistic
Name
Bytes
(MB)
GROUP
Statistic
Name
Bytes
(MB) (3)| 00:00:42 |
| 10 |
VIEWBY
|
| 3184K|
60M|
| 3468
----------------------------------------------------------------------------------------| 11 |
SORT UNIQUE
|
| 3184K|
24M|
49M|
3468 (53)| 00: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
physical
IOinterconnect
interconnectbytes
bytes
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
readIO
total
bytes
54.27
cellphysical
physical
IOinterconnect
interconnect
bytesreturned
returnedby
bysmart
smartscan
scan
42.15
TO_CHAR(key_date,
'yyyy') yrnbr
cell
uncompressed
bytes
330.30
cellIO
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
Total
Wait
Avg
cell physical IO
interconnect
18.64
Total
Total Wait AvgWait
Wait
FROM
ap.randomized_sorted
Wait
Event
Waits
Time
(s)
Time
cell
IO
uncompressed
bytes
180.51
Wait Event
Waits
Time (s)
Time(s)
(s)
WHERE key_date >= TO_DATE('2011-10-01', 'yyyy-mm-dd')
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------cell
scan
Total 62
Wait
Wait
GROUP
BY
cellsmart
smarttable
table
scan
73 Total .05
.05 Avg .0008
.0006
cell
single
block
physical
read
6
.01
.001
Wait
Event
Waits
Time
(s)
Time
(s)
cell multiblockTO_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 inKEY_ID
KEY_ID
order when
order,
results
loaded:
in a full table scan!
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
Name
| Rows
| Bytes
| Cost
(%CPU)|
Time | |
| Id
Id |
| Operation
Operation
| |
Name
| Rows
| Bytes
| Cost
(%CPU)|
Time
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
0
| 20002
644K|
(0)|
00:00:03
|
0 |
| SELECT
SELECT STATEMENT
STATEMENT
| |
| 20002
| |644K|
3893186(1)|
00:00:47
| |
|
1
|
TABLE
ACCESS
BY
INDEX
ROWID|
RANDOMIZED_SORTED
|
20002
|
644K|
186
(0)|
00:00:03
|* 1 | TABLE ACCESS STORAGE FULL| RANDOMIZED_UNSORTED | 20002 |
644K| 3893
(1)| 00:00:47 | |
|*
2 |
INDEX RANGE SCAN
| RANDOMIZED_SORTED_PK | 20002 |
|
46
(0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Statistic Name
Bytes (MB)
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
physical IO
IO bytes
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
Wait
Event
----------------------------------------------------------------------------------------------------------------------cell single block physical read
cell
blockscan
physical read
cell single
smart table
Total
Total
Waits
Waits
------------------8
155
45
Copyright 2013, Zero Defect Computing, Inc.
Total Wait
Total(s)
Wait
Time
Time
(s)
------------------.02
.09
.05
Avg Wait
Avg Wait
Time
(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
cell18%
physical
of all
updated
and
the
changes
I/O
committed.
thecontents
row size
rowsinterconnect
basedSince
on bytes!
the
grows,
row migration column.
occurs.
of the KEY_DESC
------------------------------------------------------------------------------------------------| Id | Operation
| Name
| Rows | Bytes | Cost (%CPU)| Time
|
------------------------------------------------------------------------------------------------|
0 | SELECT STATEMENT
|
|
175K| 5657K|
12M| 9814
4514 (15)|
(7)| 00:01:58
00:00:55 |
|* 1 | TABLE ACCESS STORAGE FULL| RANDOMIZED_UNSORTED |
175K| 5657K|
12M| 9814
4514 (15)|
(7)| 00:01:58
00: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
110.12
256.40
Total
Total Wait Avg Wait
Total
Total Wait Avg Wait
Wait Event
Waits
Time (s)
Time (s)
------------------------------------------------------------ ---------- ---------- ---------cell single block physical read
10
7
.04
.01
.0063
.0005
cell smart
multiblock
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
the30%
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
|
| Id | Operation
| Name
| Rows | Bytes | Cost (%CPU)| Time
|
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
0 | SELECT STATEMENT
|
|
175K|
12M| 9814
(7)| 00:01:58 |
|
0 | SELECT
STATEMENT
| RANDOMIZED_UNSORTED |
4514 (15)|
00:00:55 |
|*
1
TABLE ACCESS
STORAGE FULL|
175K| 5657K|
12M| 9814
(7)| 00:01:58
|* 1 | TABLE ACCESS STORAGE FULL| RANDOMIZED_UNSORTED |
175K| 5657K| 4514 (15)| 00:00:55 |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Statistic Name
Bytes (MB)
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
eligible
for predicate
offload
110.02
cell physical IO bytes
interconnect
bytes
returned by
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
Total Wait Avg Wait
Total
Wait
Event Total Wait Avg Wait
Waits
Time (s)
Time (s)
Wait Event
Waits
(s)
Time
(s)
--------------------------------------------------------------------- Time
--------------------------------------------------------------------------------------cell
single block physical read
21153 ---------14.04 ---------.0007
single
physical
read
7
.04
.0063
cell list
ofblock
blocks
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
This query
will retrieve
a arein
Then
all of
the
table’sincrease
rows
physical
reads,
cellof
physical
I/O
significant
rows
(well
updated
bynumber
changing
KEY_STS
interconnect
bytes,
waits!
over
from
the
table.
to
“Z”,99%)
but again,
noand
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
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
Total Wait Avg Wait
Wait Event
Waits
Time (s)
Time (s)
------------------------------------------------------------ ---------- ---------- ---------cell single
smart table
blockscan
physical read
57035
100
34.45
.03
.0003
.0006
cell smart
singletable
blockscan
physical read
98
7
.02
.04
.0035
.0004
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 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
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 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)
Copyright 2013, Zero Defect Computing, Inc.
- 30 -