Oracle SQL
  • LICENSE

Why are HCC stats being bumped when Smart Scanning row major data in 12.2

Posted on May 4, 2017 by Roger MacNicol Posted in oracle, SmartScan

In 12.2, there is a stat “cell blocks pivoted” that points to a new optimization. When Smart Scan processes data, it has to create a new synthetic block that only contains the columns that are needed and the rows that pass the offloaded predicates.

If Smart Scan has to create a new block, why would you create a new row-major block when we can just as easily create uncompressed HCC block? It is roughly the same amount of work but once the synthetic block is returned to the RDBMS for processing, columnar data is 10X cheaper to process when the query uses rowsets.  SO where does the name of this stat come from? Simple, it works just like a spreadsheet PIVOT operation, swapping rows for columns.

So what is a “rowset”? It simply means that a step in a query plan will take in a set of rows before processing and then process that entire batch of rows in one go before fetching more rows; this is significantly more efficient than processing one row at a time. You can see when a query uses rowsets via:

select * from table(dbms_xplan.display_cursor('<sql_id>',1,'-note +projection'));

in which case you will see projection metadata with details such as “(rowsets=200)”.

When Smart Scan sees that the query on the RDBMS is using rowsets, it tries to create a columnar output block instead of a row-major output block and when this arrives on the RDBMS, the table scan will see an HCC block and bump the HCC stats even though the table being scanned doesn’t use HCC. Let’s see how this works in practice:

I created a TPC-H Scale 1 database in NOCOMPRESS format:

SQL> select COMPRESSION,COMPRESS_FOR from user_tables where table_name='SUPPLIER';

COMPRESS COMPRESS_FOR
-------- ------------------------------
DISABLED

and run Query 6:

SQL> select sum(l_extendedprice * l_discount) as revenue
     from   lineitem
     where  l_shipdate >= to_date( '1994-01-01',  'YYYY-MM-DD')
        and l_shipdate < add_months(to_date( '1994-01-01',  'YYYY-MM-DD'),  12) 
        and l_discount between .06 - 0.01 and .06 + 0.01
        and l_quantity < 24;
SQL> select name,value from v$sysstat where name = 'cell blocks pivoted'; 

NAME                          VALUE
------------------------------ ----------
cell blocks pivoted                  8261

Just to show how this is tied to rowsets, let’s disable this optimization and recheck the stat:

SQL> alter session set "_rowsets_enabled"=false;
SQL> select sum(l_extendedprice * l_discount) as revenue
     from   lineitem
     where  l_shipdate >= to_date( '1994-01-01',  'YYYY-MM-DD')
        and l_shipdate < add_months(to_date( '1994-01-01',  'YYYY-MM-DD'),  12) 
        and l_discount between .06 - 0.01 and .06 + 0.01
        and l_quantity < 24; 

SQL> select name,value from v$sysstat where name = 'cell blocks pivoted';

NAME                           VALUE
------------------------------ ----------
cell blocks pivoted                  8261

I hope this helps. 

—
Roger

execution plan HCC oracle query optimization Roger MacNicol rowsets SmartScan
« Using INMEMORY with External Tables
Why you dont want to set _partition_large_extents FALSE »
photo Sayan Malakshinov

Oracle ACE Pro Oracle ACE Pro

DEVVYOracle Database Developer Choice Award winner

Oracle performance tuning expert

UK / Cambridge

LinkedIn   Twitter
sayan@orasql.org

Recent Posts

  • CBO and Partial indexing
  • Slow index access “COL=:N” where :N is NULL
  • Where does the commit or rollback happen in PL/SQL code?
  • :1 and SP2-0553: Illegal variable name “1”.
  • ORA exceptions that can’t be caught by exception handler

Recent Comments

  • Oracle SGA 값을 증가 시킬 때 발생 장애 원인 – DBA의 정석 on Example of controlling “direct path reads” decision through SQL profile hints (index_stats/table_stats)
  • Oracle SQL | Oracle diagnostic events — Cheat sheet on Where does the commit or rollback happen in PL/SQL code?
  • Functions & Subqueries | Oracle Scratchpad on Deterministic function vs scalar subquery caching. Part 3
  • Materialized views state turns into compilation_error after refresh - kranar.top - Answering users questions... on Friday prank: select from join join join
  • Exadata Catalogue | Oracle Scratchpad on When bloggers get it wrong – part 1
  • Exadata Catalogue | Oracle Scratchpad on Serial Scans failing to offload
  • lateral join – decorrelation gone wrong – svenweller on Lateral view decorrelation(VW_DCL) causes wrong results with rownum
  • 255 column catalogue | Oracle Scratchpad on Intra-block row chaining optimization in 12.2
  • 255 column catalogue | Oracle Scratchpad on row pieces, 255 columns, intra-block row chaining in details
  • opt_estimate catalogue | Oracle Scratchpad on Correct syntax for the table_stats hint

Blogroll

  • Alex Fatkulin
  • Alexander Anokhin
  • Andrey Nikolaev
  • Charles Hooper
  • Christian Antognini
  • Coskan Gundogar
  • David Fitzjarrell
  • Igor Usoltsev
  • Jonathan Lewis
  • Karl Arao
  • Mark Bobak
  • Martin Bach
  • Martin Berger
  • Neil Chandler
  • Randolf Geist
  • Richard Foote
  • Riyaj Shamsudeen
  • Tanel Poder
  • Timur Akhmadeev
  • Valentin Nikotin

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org
©Sayan Malakshinov. Oracle SQL