Databases Reference
In-Depth Information
Table
16-2
points out some interesting performance aspects of our queries against HCC tables and
uncompressed tables:
•
The amount of physical I/O bytes required for the compressed tables was less than the
uncompressed table, which is to be expected since the compressed tables are smaller in size.
•
Without Smart Scan, the number of bytes transmitted over the storage interconnect both
the
MYOBJ_COMPQH
and
MYOBJ_COMPAH
queries was equal to the number of bytes physical read
from disks.
•
The
cell IO uncompressed bytes
statistic is a measure of how many uncompressed bytes
the storage grid processes after decompression; in other words, it reflects how much data was
decompressed on the storage cells prior to sending in uncompressed format to the compute
nodes. Note that without Smart Scan enabled in Columns 3 and 4,
no
data was uncompressed
on the storage cells. This means that all of the decompression occurred on the compute node.
•
When forcing Smart Scan, which we accomplished by setting
_serial_direct_read
=
always
at the session level, our query on MYOBJ_COMPQH in the rightmost column performed
roughly the same amount of physical I/O as the traditional scan but transmitted almost four
times more data over the interconnect. This is an indication that the storage servers performed
at least some of the required decompression before sending data to the compute nodes.
•
The query execution times were longer when accessing HCC data without smart scans as
compared to querying on the uncompressed table; this was the case because the compute
nodes were forced to process uncompressed data from the storage cells and decompress on
the compute node.
•
CPU usage was higher when accessing data from HCC tables; in these tests, this was due to
where the decompression occurred.
The same type of analysis can also be achieved by querying
V$SQL
for our SQL statements. Listing 16-5 provides
an example showing the same behavior for the previous test cases.
Listing 16-5.
lst16-05-exahcc-sqls.sql
SQL> select sql_id ,avgelapsed,phybytes/1024/1024 phymb,
io_cell_uncompressed_bytes/1024/1024 celluncompmb,
io_interconnect_bytes/1024/1024 icmb,
sql_text
from (
select sql_id,
physical_read_bytes+physical_write_bytes phybytes,io_interconnect_bytes,
(elapsed_time/1000000)/
executions/
decode(px_servers_executions,0,1,px_servers_executions) avgelapsed,
substr(sql_text,1,30) sql_text,
io_cell_uncompressed_bytes
from v$sql
where executions > 0
and sql_text like '%'||'&&sql_text'||'%'
order by avgelapsed desc);
Enter value for sql_text: hcc_myobj
Search WWH ::
Custom Search