[
https://issues.apache.org/jira/browse/TRAFODION-974?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Suresh Subbiah closed TRAFODION-974.
------------------------------------
> LP Bug: 1417337 - Upsert into a table with index hangs and hogs system
> resource
> -------------------------------------------------------------------------------
>
> Key: TRAFODION-974
> URL: https://issues.apache.org/jira/browse/TRAFODION-974
> Project: Apache Trafodion
> Issue Type: Bug
> Components: sql-exe
> Reporter: Weishiun Tsai
> Assignee: Joanie Cooper
> Priority: Blocker
> Fix For: 1.1 (pre-incubation)
>
>
> As shown here, 2 tables: t1 and t2 are identical, except that t2 has an index
> i2 created for it. 2 identical ‘upsert using load’ queries x1 and x2 are
> then prepared for t1 and t2 respectively. The query upserts 1000000 rows.
> Plan x2 includes 2 nested_joins and a trafodion_insert compared to plan x1’s
> trafodion_load. On either a Hortonworks machine or a Cloudera machine, x1
> can generally complete in around 30 seconds on our 4-node systems, but x2
> always ‘hangs’. In the latest try, it hanged for 4 hours without coming
> back. At that point, one of the hbase region servers has used 9.9g virtual
> memory, 6.6g resident memory, and the CUP usage hovered around 600%-700%.
> Somewhere along the way, it also started to have trouble with monitors with
> messages like the followings shown in $SQ_HOME/logs/mon.xxx.log:
> 02/02/2015-22:52:04.796.430: (name=monitor/pid=24127/tid=24131)
> (cmp=1/ev=101130801/fac=320/sev=6): STDERR redirected from
> n001.$Z000HNQ.0.21655: [$Z000HNQ] Exiting
> 02/02/2015-22:52:04.896.247: (name=monitor/pid=24127/tid=24131)
> (cmp=1/ev=101130801/fac=320/sev=6): STDERR redirected from
> n001.$Z000HQM.0.21722: [$Z000HQM] Exiting
> 02/03/2015-00:05:05.891.488: (name=monitor/pid=24127/tid=24131)
> (cmp=1/ev=101130801/fac=320/sev=6): STDERR redirected from
> n001.$Z0005V1.0.7176: [$Z0005V1] Exiting
> 02/03/2015-00:05:05.989.256: (name=monitor/pid=24127/tid=24131)
> (cmp=1/ev=101130801/fac=320/sev=6): STDERR redirected from
> n001.$Z0005XF.0.7260: [$Z0005XF] Exiting
> This is the kind of Trafodion hardening issue regarding overall
> usability/stability for post-1.0 releases, so the bug report is filed as a
> critical one. The problem is fairly reproducible on our systems using the
> v1.0.0 rc3 build. It requires the QA global table g_wisc32.abase, which has
> 32000000 rows.
> ----------------------------------------------
> Here is the entire script to reproduce it (it requires the QA global table
> g_wisc32.abase):
> log mytest.log clear;
> drop schema mytest9 cascade;
> create schema mytest9;
> set schema mytest9;
> showddl trafodion.g_wisc32.abase;
> showstats for table trafodion.g_wisc32.abase on every column;
> create table t1
> (
> UNIQUE1 INT NO DEFAULT NOT NULL NOT DROPPABLE
> , UNIQUE2 INT NO DEFAULT NOT NULL NOT DROPPABLE
> , TWO INT NO DEFAULT NOT NULL NOT DROPPABLE
> , FOUR INT NO DEFAULT NOT NULL NOT DROPPABLE
> , TEN INT NO DEFAULT NOT NULL NOT DROPPABLE
> , TWENTY INT NO DEFAULT NOT NULL NOT DROPPABLE
> , ONEPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
> , TENPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
> , TWENTYPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
> , FIFTYPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
> , UNIQUE3 INT NO DEFAULT NOT NULL NOT DROPPABLE
> , EVENONEPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
> , ODDONEPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
> , STRINGU1 CHAR(52) CHARACTER SET ISO88591 COLLATE
> DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
> , STRINGU2 CHAR(52) CHARACTER SET ISO88591 COLLATE
> DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
> , STRING4 CHAR(52) CHARACTER SET ISO88591 COLLATE
> DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
> , PRIMARY KEY (UNIQUE2 ASC)
> )
> ;
> create table t2
> (
> UNIQUE1 INT NO DEFAULT NOT NULL NOT DROPPABLE
> , UNIQUE2 INT NO DEFAULT NOT NULL NOT DROPPABLE
> , TWO INT NO DEFAULT NOT NULL NOT DROPPABLE
> , FOUR INT NO DEFAULT NOT NULL NOT DROPPABLE
> , TEN INT NO DEFAULT NOT NULL NOT DROPPABLE
> , TWENTY INT NO DEFAULT NOT NULL NOT DROPPABLE
> , ONEPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
> , TENPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
> , TWENTYPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
> , FIFTYPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
> , UNIQUE3 INT NO DEFAULT NOT NULL NOT DROPPABLE
> , EVENONEPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
> , ODDONEPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
> , STRINGU1 CHAR(52) CHARACTER SET ISO88591 COLLATE
> DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
> , STRINGU2 CHAR(52) CHARACTER SET ISO88591 COLLATE
> DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
> , STRING4 CHAR(52) CHARACTER SET ISO88591 COLLATE
> DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
> , PRIMARY KEY (UNIQUE2 ASC)
> )
> ;
> create index i2 on t2 (two, four, unique1, unique3);
> showddl t1;
> showddl t2;
> select count(*) from trafodion.g_wisc32.abase;
> select count(*) from trafodion.g_wisc32.abase where unique2 < 1000000;
> prepare x1 from upsert using load into t1 select * from
> trafodion.g_wisc32.abase where unique2 < 1000000;
> prepare x2 from upsert using load into t2 select * from
> trafodion.g_wisc32.abase where unique2 < 1000000;
> explain options 'f' x1;
> explain options 'f' x2;
> explain x1;
> explain x2;
> sh date;
> execute x1;
> sh date;
> drop table t1 cascade;
> sh date;
> execute x2;
> sh date;
> drop table t2 cascade;
> sh date;
> drop schema mytest9;
> ----------------------------------------------
> Here is the execution output up to the point when ‘execute x2’ hanged for 4
> hours:
> >>drop schema mytest9 cascade;
> *** ERROR[1003] Schema TRAFODION.MYTEST9 does not exist.
> --- SQL operation failed with errors.
> >>create schema mytest9;
> --- SQL operation complete.
> >>set schema mytest9;
> --- SQL operation complete.
> >>showddl trafodion.g_wisc32.abase;
> CREATE TABLE TRAFODION.G_WISC32.ABASE
> (
> UNIQUE1 INT NO DEFAULT NOT NULL NOT DROPPABLE
> , UNIQUE2 INT NO DEFAULT NOT NULL NOT DROPPABLE
> , TWO INT NO DEFAULT NOT NULL NOT DROPPABLE
> , FOUR INT NO DEFAULT NOT NULL NOT DROPPABLE
> , TEN INT NO DEFAULT NOT NULL NOT DROPPABLE
> , TWENTY INT NO DEFAULT NOT NULL NOT DROPPABLE
> , ONEPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
> , TENPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
> , TWENTYPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
> , FIFTYPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
> , UNIQUE3 INT NO DEFAULT NOT NULL NOT DROPPABLE
> , EVENONEPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
> , ODDONEPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
> , STRINGU1 CHAR(52) CHARACTER SET ISO88591 COLLATE
> DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
> , STRINGU2 CHAR(52) CHARACTER SET ISO88591 COLLATE
> DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
> , STRING4 CHAR(52) CHARACTER SET ISO88591 COLLATE
> DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
> , PRIMARY KEY (UNIQUE2 ASC)
> )
> SALT USING 8 PARTITIONS
> ;
> CREATE UNIQUE INDEX IXA4 ON TRAFODION.G_WISC32.ABASE
> (
> UNIQUE3 ASC
> , TWENTYPERCENT ASC
> , FIFTYPERCENT ASC
> )
> ;
> --- SQL operation complete.
> >>showstats for table trafodion.g_wisc32.abase on every column;
> Histogram data for Table TRAFODION.G_WISC32.ABASE
> Table ID: 533733601026556210
> Hist ID # Ints Rowcount UEC Colname(s)
> ========== ====== =========== =========== ===========================
> 2094748496 38 32334424 32334424 UNIQUE1
> 2094748491 31 32334424 32334424 UNIQUE2
> 2094748486 2 32334424 2 TWO
> 2094748481 4 32334424 4 FOUR
> 2094748476 10 32334424 10 TEN
> 2094748471 20 32334424 20 TWENTY
> 2094748466 77 32334424 100 ONEPERCENT
> 2094748461 10 32334424 10 TENPERCENT
> 2094748456 5 32334424 5 TWENTYPERCENT
> 2094748451 2 32334424 2 FIFTYPERCENT
> 2094748446 38 32334424 32334424 UNIQUE3
> 2094748441 77 32334424 100 EVENONEPERCENT
> 2094748436 77 32334424 100 ODDONEPERCENT
> 2094748431 62 32334424 32334424 STRINGU1
> 2094748426 62 32334424 32334424 STRINGU2
> 2094748421 4 32334424 4 STRING4
> 2094748416 8 32334424 8 "_SALT_"
> 2094748501 1 32334424 32334424 "_SALT_", UNIQUE2
> --- SQL operation complete.
> >>
> >>create table t1
> +> (
> +> UNIQUE1 INT NO DEFAULT NOT NULL NOT DROPPABLE
> +> , UNIQUE2 INT NO DEFAULT NOT NULL NOT DROPPABLE
> +> , TWO INT NO DEFAULT NOT NULL NOT DROPPABLE
> +> , FOUR INT NO DEFAULT NOT NULL NOT DROPPABLE
> +> , TEN INT NO DEFAULT NOT NULL NOT DROPPABLE
> +> , TWENTY INT NO DEFAULT NOT NULL NOT DROPPABLE
> +> , ONEPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
> +> , TENPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
> +> , TWENTYPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
> +> , FIFTYPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
> +> , UNIQUE3 INT NO DEFAULT NOT NULL NOT DROPPABLE
> +> , EVENONEPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
> +> , ODDONEPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
> +> , STRINGU1 CHAR(52) CHARACTER SET ISO88591 COLLATE
> +> DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
> +> , STRINGU2 CHAR(52) CHARACTER SET ISO88591 COLLATE
> +> DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
> +> , STRING4 CHAR(52) CHARACTER SET ISO88591 COLLATE
> +> DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
> +> , PRIMARY KEY (UNIQUE2 ASC)
> +> )
> +>;
> --- SQL operation complete.
> >>
> >>create table t2
> +> (
> +> UNIQUE1 INT NO DEFAULT NOT NULL NOT DROPPABLE
> +> , UNIQUE2 INT NO DEFAULT NOT NULL NOT DROPPABLE
> +> , TWO INT NO DEFAULT NOT NULL NOT DROPPABLE
> +> , FOUR INT NO DEFAULT NOT NULL NOT DROPPABLE
> +> , TEN INT NO DEFAULT NOT NULL NOT DROPPABLE
> +> , TWENTY INT NO DEFAULT NOT NULL NOT DROPPABLE
> +> , ONEPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
> +> , TENPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
> +> , TWENTYPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
> +> , FIFTYPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
> +> , UNIQUE3 INT NO DEFAULT NOT NULL NOT DROPPABLE
> +> , EVENONEPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
> +> , ODDONEPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
> +> , STRINGU1 CHAR(52) CHARACTER SET ISO88591 COLLATE
> +> DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
> +> , STRINGU2 CHAR(52) CHARACTER SET ISO88591 COLLATE
> +> DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
> +> , STRING4 CHAR(52) CHARACTER SET ISO88591 COLLATE
> +> DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
> +> , PRIMARY KEY (UNIQUE2 ASC)
> +> )
> +>;
> --- SQL operation complete.
> >>
> >>create index i2 on t2 (two, four, unique1, unique3);
> --- SQL operation complete.
> >>
> >>showddl t1;
> CREATE TABLE TRAFODION.MYTEST9.T1
> (
> UNIQUE1 INT NO DEFAULT NOT NULL NOT DROPPABLE
> , UNIQUE2 INT NO DEFAULT NOT NULL NOT DROPPABLE
> , TWO INT NO DEFAULT NOT NULL NOT DROPPABLE
> , FOUR INT NO DEFAULT NOT NULL NOT DROPPABLE
> , TEN INT NO DEFAULT NOT NULL NOT DROPPABLE
> , TWENTY INT NO DEFAULT NOT NULL NOT DROPPABLE
> , ONEPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
> , TENPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
> , TWENTYPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
> , FIFTYPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
> , UNIQUE3 INT NO DEFAULT NOT NULL NOT DROPPABLE
> , EVENONEPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
> , ODDONEPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
> , STRINGU1 CHAR(52) CHARACTER SET ISO88591 COLLATE
> DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
> , STRINGU2 CHAR(52) CHARACTER SET ISO88591 COLLATE
> DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
> , STRING4 CHAR(52) CHARACTER SET ISO88591 COLLATE
> DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
> , PRIMARY KEY (UNIQUE2 ASC)
> )
> ;
> --- SQL operation complete.
> >>showddl t2;
> CREATE TABLE TRAFODION.MYTEST9.T2
> (
> UNIQUE1 INT NO DEFAULT NOT NULL NOT DROPPABLE
> , UNIQUE2 INT NO DEFAULT NOT NULL NOT DROPPABLE
> , TWO INT NO DEFAULT NOT NULL NOT DROPPABLE
> , FOUR INT NO DEFAULT NOT NULL NOT DROPPABLE
> , TEN INT NO DEFAULT NOT NULL NOT DROPPABLE
> , TWENTY INT NO DEFAULT NOT NULL NOT DROPPABLE
> , ONEPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
> , TENPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
> , TWENTYPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
> , FIFTYPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
> , UNIQUE3 INT NO DEFAULT NOT NULL NOT DROPPABLE
> , EVENONEPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
> , ODDONEPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
> , STRINGU1 CHAR(52) CHARACTER SET ISO88591 COLLATE
> DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
> , STRINGU2 CHAR(52) CHARACTER SET ISO88591 COLLATE
> DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
> , STRING4 CHAR(52) CHARACTER SET ISO88591 COLLATE
> DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
> , PRIMARY KEY (UNIQUE2 ASC)
> )
> ;
> CREATE INDEX I2 ON TRAFODION.MYTEST9.T2
> (
> TWO ASC
> , FOUR ASC
> , UNIQUE1 ASC
> , UNIQUE3 ASC
> )
> ;
> --- SQL operation complete.
> >>
> >>select count(*) from trafodion.g_wisc32.abase;
> (EXPR)
> --------------------
> 32000000
> --- 1 row(s) selected.
> >>select count(*) from trafodion.g_wisc32.abase where unique2 < 1000000;
> (EXPR)
> --------------------
> 1000000
> --- 1 row(s) selected.
> >>
> >>prepare x1 from upsert using load into t1 select * from
> >>trafodion.g_wisc32.aba
> se where unique2 < 1000000;
> --- SQL command prepared.
> >>prepare x2 from upsert using load into t2 select * from
> >>trafodion.g_wisc32.aba
> se where unique2 < 1000000;
> --- SQL command prepared.
> >>explain options 'f' x1;
> LC RC OP OPERATOR OPT DESCRIPTION CARD
> ---- ---- ---- -------------------- -------- -------------------- ---------
> 5 . 6 root 1.00E+006
> 4 . 5 esp_exchange 1:8(hash2) 1.00E+006
> 2 3 4 tuple_flow 1.00E+006
> . . 3 trafodion_load T1 1.00E+000
> 1 . 2 sort 1.00E+006
> . . 1 trafodion_scan ABASE 1.00E+006
> --- SQL operation complete.
> >>explain options 'f' x2;
> LC RC OP OPERATOR OPT DESCRIPTION CARD
> ---- ---- ---- -------------------- -------- -------------------- ---------
> 7 . 8 root x 1.00E+006
> 6 . 7 esp_exchange 1:8(hash2) 1.00E+006
> 4 5 6 nested_join 1.00E+006
> . . 5 trafodion_insert I2 1.00E+000
> 2 3 4 nested_join 1.00E+006
> . . 3 trafodion_ T2 1.00E+000
> 1 . 2 sort 1.00E+006
> . . 1 trafodion_scan ABASE 1.00E+006
> --- SQL operation complete.
> >>explain x1;
> ------------------------------------------------------------------ PLAN
> SUMMARY
> MODULE_NAME .............. DYNAMICALLY COMPILED
> STATEMENT_NAME ........... X1
> PLAN_ID .................. 212289671902829625
> ROWS_OUT ......... 1,007,717
> EST_TOTAL_COST ........... 1.52
> STATEMENT ................ upsert using load into t1 select * from
> trafodion.g_wisc32.abase where unique2 < 1000000;
> ------------------------------------------------------------------ NODE
> LISTING
> ROOT ====================================== SEQ_NO 6 ONLY CHILD 5
> REQUESTS_IN .............. 1
> ROWS_OUT ......... 1,007,717
> EST_OPER_COST ............ 0
> EST_TOTAL_COST ........... 1.52
> DESCRIPTION
> max_card_est ........... 1.00772e+06
> fragment_id ............ 0
> parent_frag ............ (none)
> fragment_type .......... master
> statement_index ........ 0
> affinity_value ......... 0
> est_memory_per_cpu ..... 25586 KB
> max_max_cardinal 1,007,717
> total_overflow_size .... 0.00 KB
> esp_2_node_map ......... (\NSK:-1:-1:-1:-1:-1:-1:-1:-1)
> 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 ................. MYTEST9
> ObjectUIDs ............. 533733601026556210, 28205037036521192
> ESP_EXCHANGE ============================== SEQ_NO 5 ONLY CHILD 4
> REQUESTS_IN .............. 1
> ROWS_OUT ......... 1,007,717
> EST_OPER_COST ............ 0.01
> EST_TOTAL_COST ........... 1.52
> DESCRIPTION
> max_card_est ........... 1.00772e+06
> fragment_id ............ 2
> parent_frag ............ 0
> fragment_type .......... esp
> buffer_size ........ 5,000
> record_length .......... 0
> parent_processes ....... 1
> child_processes ........ 8
> child_partitioning_func hash2 partitioned 8 ways on
> (TRAFODION.G_WISC32.ABASE.UNIQUE2)
> seamonster_query ....... no
> seamonster_exchange .... no
> TUPLE_FLOW ================================ SEQ_NO 4 CHILDREN 2, 3
> REQUESTS_IN .............. 1
> ROWS_OUT ......... 1,007,717
> EST_OPER_COST ............ 0.01
> EST_TOTAL_COST ........... 1.52
> DESCRIPTION
> max_card_est ........... 1.00772e+06
> fragment_id ............ 2
> parent_frag ............ 0
> fragment_type .......... esp
> join_type .............. inner
> join_method ............ in-order nested
> parallel_join_type ..... 1 (OCR Outer Child Repartitioning)
> TRAFODION_LOAD ============================ SEQ_NO 3 NO CHILDREN
> TABLE_NAME ............... TRAFODION.MYTEST9.T1
> REQUESTS_IN .............. 1
> ROWS_OUT ................. 1
> EST_OPER_COST ............ 0
> EST_TOTAL_COST ........... 0
> DESCRIPTION
> max_card_est ........... 1.00772e+06
> fragment_id ............ 2
> parent_frag ............ 0
> fragment_type .......... esp
> iud_type ............... trafodion_load TRAFODION.MYTEST9.T1
> new_rec_expr ........... (UNIQUE1 assign TRAFODION.G_WISC32.ABASE.UNIQUE1),
> (UNIQUE2 assign
> TRAFODION.G_WISC32.ABASE.UNIQUE2),
> (TWO assign TRAFODION.G_WISC32.ABASE.TWO),
> (FOUR assign TRAFODION.G_WISC32.ABASE.FOUR),
> (TEN assign TRAFODION.G_WISC32.ABASE.TEN),
> (TWENTY assign TRAFODION.G_WISC32.ABASE.TWENTY),
> (ONEPERCENT assign
> TRAFODION.G_WISC32.ABASE.ONEPER
> CENT), (TENPERCENT assign
> TRAFODION.G_WISC32.ABASE.TENPERCENT),
> (TWENTYPERCENT assign
> TRAFODION.G_WISC32.ABASE.TWE
> NTYPERCENT), (FIFTYPERCENT assign
> TRAFODION.G_WISC32.ABASE.FIFTYPERCENT),
> (UNIQUE3 assign
> TRAFODION.G_WISC32.ABASE.UNIQUE3),
> (EVENONEPERCENT assign
> TRAFODION.G_WISC32.ABASE.EV
> ENONEPERCENT), (ODDONEPERCENT assign
> TRAFODION.G_WISC32.ABASE.ODDONEPERCENT),
> (STRINGU1 assign
> TRAFODION.G_WISC32.ABASE.STRINGU1
> ), (STRINGU2 assign
> TRAFODION.G_WISC32.ABASE.STRIN
> GU2), (STRING4 assign
> TRAFODION.G_WISC32.ABASE.STR
> ING4)
> SORT ====================================== SEQ_NO 2 ONLY CHILD 1
> REQUESTS_IN .............. 1
> ROWS_OUT ......... 1,007,717
> EST_OPER_COST ............ 0.06
> EST_TOTAL_COST ........... 1.51
> DESCRIPTION
> memory_quota_per_esp ... 0 MB
> max_card_est ........... 1.00772e+06
> fragment_id ............ 2
> parent_frag ............ 0
> fragment_type .......... esp
> est_memory_per_cpu ..... 25586 KB
> sort_type .............. full
> sort_key ............... TRAFODION.G_WISC32.ABASE.UNIQUE2
> TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN
> TABLE_NAME ............... ABASE
> REQUESTS_IN .............. 1
> ROWS_OUT ......... 1,007,717
> EST_OPER_COST ............ 1.45
> EST_TOTAL_COST ........... 1.45
> DESCRIPTION
> max_card_est ........... 1.00772e+06
> fragment_id ............ 2
> parent_frag ............ 0
> fragment_type .......... esp
> scan_type .............. subset scan limited by mdam of table
> TRAFODION.G_WISC32.ABASE
> object_type ............ Trafodion
> cache_size ........ 10,000
> probes ................. 1
> rows_accessed .......... 1.00772e+06
> key_columns ............ _SALT_, UNIQUE2
> mdam_disjunct .......... (UNIQUE2 < 1000000) and (_SALT_ >=
> (\:_sys_HostVarLoHashPart Hash2Distrib 8)) and
> (_SALT_ <= (\:_sys_HostVarHiHashPart Hash2Distrib
> 8))
> part_key_predicates .... (UNIQUE2 < 1000000)
> --- SQL operation complete.
> >>explain x2;
> ------------------------------------------------------------------ PLAN
> SUMMARY
> MODULE_NAME .............. DYNAMICALLY COMPILED
> STATEMENT_NAME ........... X2
> PLAN_ID .................. 212289671903453036
> ROWS_OUT ......... 1,007,717
> EST_TOTAL_COST ........... 1.53
> STATEMENT ................ upsert using load into t2 select * from
> trafodion.g_wisc32.abase where unique2 < 1000000;
> ------------------------------------------------------------------ NODE
> LISTING
> ROOT ====================================== SEQ_NO 8 ONLY CHILD 7
> REQUESTS_IN .............. 1
> ROWS_OUT ......... 1,007,717
> EST_OPER_COST ............ 0
> EST_TOTAL_COST ........... 1.53
> DESCRIPTION
> max_card_est ........... 1.00772e+06
> fragment_id ............ 0
> parent_frag ............ (none)
> fragment_type .......... master
> statement_index ........ 0
> affinity_value ......... 0
> est_memory_per_cpu ..... 25586 KB
> max_max_cardinal 1,007,717
> total_overflow_size .... 0.00 KB
> esp_2_node_map ......... (\NSK:-1:-1:-1:-1:-1:-1:-1:-1)
> upd_action_on_error .... xn_rollback
> 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 ................. MYTEST9
> ObjectUIDs ............. 533733601026556210, 28205037036521336,
> 28205037036521591
> ESP_EXCHANGE ============================== SEQ_NO 7 ONLY CHILD 6
> REQUESTS_IN .............. 1
> ROWS_OUT ......... 1,007,717
> EST_OPER_COST ............ 0.01
> EST_TOTAL_COST ........... 1.53
> DESCRIPTION
> max_card_est ........... 1.00772e+06
> fragment_id ............ 2
> parent_frag ............ 0
> fragment_type .......... esp
> buffer_size ........ 5,000
> record_length .......... 0
> parent_processes ....... 1
> child_processes ........ 8
> child_partitioning_func hash2 partitioned 8 ways on
> (TRAFODION.G_WISC32.ABASE.UNIQUE2)
> seamonster_query ....... no
> seamonster_exchange .... no
> NESTED_JOIN =============================== SEQ_NO 6 CHILDREN 4, 5
> REQUESTS_IN .............. 1
> ROWS_OUT ......... 1,007,717
> EST_OPER_COST ............ 0.01
> EST_TOTAL_COST ........... 1.53
> DESCRIPTION
> max_card_est ........... 1.00772e+06
> fragment_id ............ 2
> parent_frag ............ 0
> fragment_type .......... esp
> join_type .............. inner
> join_method ............ nested
> parallel_join_type ..... 2 (N2J Opens all inner partitions)
> TRAFODION_INSERT ========================== SEQ_NO 5 NO CHILDREN
> TABLE_NAME ............... TRAFODION.MYTEST9.I2
> REQUESTS_IN .............. 1
> ROWS_OUT ................. 1
> EST_OPER_COST ............ 0
> EST_TOTAL_COST ........... 0
> DESCRIPTION
> max_card_est ........... 1.00772e+06
> fragment_id ............ 2
> parent_frag ............ 0
> fragment_type .......... esp
> iud_type ............... index_trafodion_insert TRAFODION.MYTEST9.I2
> new_rec_expr ........... ("TWO@" assign TRAFODION.MYTEST9.T2.TWO),
> ("FOUR@" assign TRAFODION.MYTEST9.T2.FOUR),
> ("UNIQUE1@" assign TRAFODION.MYTEST9.T2.UNIQUE1),
> ("UNIQUE3@" assign TRAFODION.MYTEST9.T2.UNIQUE3),
> (UNIQUE2 assign TRAFODION.MYTEST9.T2.UNIQUE2)
> NESTED_JOIN =============================== SEQ_NO 4 CHILDREN 2, 3
> REQUESTS_IN .............. 1
> ROWS_OUT ......... 1,007,717
> EST_OPER_COST ............ 0.01
> EST_TOTAL_COST ........... 1.52
> DESCRIPTION
> max_card_est ........... 1.00772e+06
> fragment_id ............ 2
> parent_frag ............ 0
> fragment_type .......... esp
> join_type .............. inner
> join_method ............ in-order nested
> parallel_join_type ..... 1 (OCR Outer Child Repartitioning)
> TRAFODION_ ================================ SEQ_NO 3 NO CHILDREN
> TABLE_NAME ............... TRAFODION.MYTEST9.T2
> REQUESTS_IN .............. 1
> ROWS_OUT ................. 1
> EST_OPER_COST ............ 0
> EST_TOTAL_COST ........... 0
> DESCRIPTION
> max_card_est ........... 1.00772e+06
> fragment_id ............ 2
> parent_frag ............ 0
> fragment_type .......... esp
> iud_type ............... trafodion_ TRAFODION.MYTEST9.T2
> new_rec_expr ........... (UNIQUE1 assign TRAFODION.G_WISC32.ABASE.UNIQUE1),
> (UNIQUE2 assign
> TRAFODION.G_WISC32.ABASE.UNIQUE2),
> (TWO assign TRAFODION.G_WISC32.ABASE.TWO),
> (FOUR assign TRAFODION.G_WISC32.ABASE.FOUR),
> (TEN assign TRAFODION.G_WISC32.ABASE.TEN),
> (TWENTY assign TRAFODION.G_WISC32.ABASE.TWENTY),
> (ONEPERCENT assign
> TRAFODION.G_WISC32.ABASE.ONEPER
> CENT), (TENPERCENT assign
> TRAFODION.G_WISC32.ABASE.TENPERCENT),
> (TWENTYPERCENT assign
> TRAFODION.G_WISC32.ABASE.TWE
> NTYPERCENT), (FIFTYPERCENT assign
> TRAFODION.G_WISC32.ABASE.FIFTYPERCENT),
> (UNIQUE3 assign
> TRAFODION.G_WISC32.ABASE.UNIQUE3),
> (EVENONEPERCENT assign
> TRAFODION.G_WISC32.ABASE.EV
> ENONEPERCENT), (ODDONEPERCENT assign
> TRAFODION.G_WISC32.ABASE.ODDONEPERCENT),
> (STRINGU1 assign
> TRAFODION.G_WISC32.ABASE.STRINGU1
> ), (STRINGU2 assign
> TRAFODION.G_WISC32.ABASE.STRIN
> GU2), (STRING4 assign
> TRAFODION.G_WISC32.ABASE.STR
> ING4)
> SORT ====================================== SEQ_NO 2 ONLY CHILD 1
> REQUESTS_IN .............. 1
> ROWS_OUT ......... 1,007,717
> EST_OPER_COST ............ 0.06
> EST_TOTAL_COST ........... 1.51
> DESCRIPTION
> memory_quota_per_esp ... 0 MB
> max_card_est ........... 1.00772e+06
> fragment_id ............ 2
> parent_frag ............ 0
> fragment_type .......... esp
> est_memory_per_cpu ..... 25586 KB
> sort_type .............. full
> sort_key ............... TRAFODION.G_WISC32.ABASE.UNIQUE2
> TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN
> TABLE_NAME ............... ABASE
> REQUESTS_IN .............. 1
> ROWS_OUT ......... 1,007,717
> EST_OPER_COST ............ 1.45
> EST_TOTAL_COST ........... 1.45
> DESCRIPTION
> max_card_est ........... 1.00772e+06
> fragment_id ............ 2
> parent_frag ............ 0
> fragment_type .......... esp
> scan_type .............. subset scan limited by mdam of table
> TRAFODION.G_WISC32.ABASE
> object_type ............ Trafodion
> cache_size ........ 10,000
> probes ................. 1
> rows_accessed .......... 1.00772e+06
> key_columns ............ _SALT_, UNIQUE2
> mdam_disjunct .......... (UNIQUE2 < 1000000) and (_SALT_ >=
> (\:_sys_HostVarLoHashPart Hash2Distrib 8)) and
> (_SALT_ <= (\:_sys_HostVarHiHashPart Hash2Distrib
> 8))
> part_key_predicates .... (UNIQUE2 < 1000000)
> --- SQL operation complete.
> >>
> >>sh date;
> >>execute x1;
> --- 1000000 row(s) inserted.
> >>sh date;
> >>drop table t1 cascade;
> --- SQL operation complete.
> >>sh date;
> >>execute x2;
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
