You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@trafodion.apache.org by "Suresh Subbiah (JIRA)" <ji...@apache.org> on 2015/10/06 22:44:27 UTC
[jira] [Commented] (TRAFODION-1482) disabling BlockCache for all
unbounded scan is not correct for dictionary tables
[ https://issues.apache.org/jira/browse/TRAFODION-1482?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14945751#comment-14945751 ]
Suresh Subbiah commented on TRAFODION-1482:
-------------------------------------------
The 2 problem lines shown above have been removed.
Verified using cardinality hint that for a table with no stats, use of block cache is disabled by the the compiler using cardinality estimates. Note that cache_blocks is OFF below.
>>explain select * from ttf <<+cardinality 10e8>> ;
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... NOT NAMED
PLAN_ID .................. 212310923609732808
ROWS_OUT ..... 1,000,000,000
EST_TOTAL_COST ....... 2,015.21
STATEMENT ................ select * from ttf <<+cardinality 10e8>>;
------------------------------------------------------------------ NODE LISTING
ROOT ====================================== SEQ_NO 2 ONLY CHILD 1
REQUESTS_IN .............. 1
ROWS_OUT ..... 1,000,000,000
EST_OPER_COST ............ 0
EST_TOTAL_COST ....... 2,015.21
DESCRIPTION
max_card_est ........... 1e+09
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
statement_index ........ 0
affinity_value ......... 0
max_max_cardinal 1,000,000,000
total_overflow_size .... 0.00 KB
xn_access_mode ......... read_only
xn_autoabort_interval 0
auto_query_retry ....... enabled
plan_version ....... 2,600
embedded_arkcmp ........ used
IS_SQLCI ............... ON
LDAP_USERNAME
SCHEMA ................. SCH
SHOWCONTROL_SHOW_ALL ... OFF
GENERATE_EXPLAIN ....... ON
ObjectUIDs ............. 2930231598034905188
select_list ............ TRAFODION.SCH.TTF.VCH7, TRAFODION.SCH.TTF.NINT,
TRAFODION.SCH.TTF.CH3, TRAFODION.SCH.TTF.NNUM9,
TRAFODION.SCH.TTF.CH4, TRAFODION.SCH.TTF.NNUM5,
TRAFODION.SCH.TTF.VCH5, TRAFODION.SCH.TTF.NSINT
TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN
TABLE_NAME ............... TTF
REQUESTS_IN .............. 1
ROWS_OUT ..... 1,000,000,000
EST_OPER_COST ........ 2,015.21
EST_TOTAL_COST ....... 2,015.21
DESCRIPTION
max_card_est ........... 1e+09
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
scan_type .............. subset scan of table TRAFODION.SCH.TTF
object_type ............ Trafodion
columns ................ all
begin_keys(incl)
end_keys(incl)
cache_size ........ 10,000
cache_blocks ........... OFF
probes ................. 1
rows_accessed .......... 1e+09
key_columns ............ SYSKEY
> disabling BlockCache for all unbounded scan is not correct for dictionary tables
> --------------------------------------------------------------------------------
>
> Key: TRAFODION-1482
> URL: https://issues.apache.org/jira/browse/TRAFODION-1482
> Project: Apache Trafodion
> Issue Type: Bug
> Components: sql-cmp, sql-exe
> Affects Versions: 1.1 (pre-incubation)
> Reporter: Eric Owhadi
> Assignee: Suresh Subbiah
> Labels: performance
>
> There is a workaround that was implemented to avoid cacheBlock trashing triggered by full table scan.
> It is in HTableClient.java, line looking like:
> //Disable block cache for full table scan
> If (startRow == null && stopRow == null)
> Scan.setCacheBlocks(false);
>
> This line bypass the cacheBlocks parameter passed to the startScan, hence is a workaround.
>
> However, this is a potentially negative workaround from some other performance angle on situations like “dictionary tables” on normalized schema.
> For example, if you have a table storing status code, error code, country etc , and linked to with foreign key, these tables are small and I would imagine they will most likely be fetched and spread on esps for hash joins with startRow and stopRow null. They won’t be cached with the workaround, but should be. Cache trashing is a problem only when scanning large tables.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)