[
https://issues.apache.org/jira/browse/TRAFODION-2346?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16051317#comment-16051317
]
ASF GitHub Bot commented on TRAFODION-2346:
-------------------------------------------
GitHub user Weixin-Xu opened a pull request:
https://github.com/apache/incubator-trafodion/pull/1121
[TRAFODION-2346] ODB copy incorrect rows when using rowset and parallel
fix jira TRAFODION-2346 and TRAFODION-2373 bug as follows:
When using parallel, max rows/ parallel will cause rows cutting because of
divisibility.
When using rowset, per thread rows/rowset will cause one more round
contained the same rowset.
For example, rows = 5000 ,max = 500000 ,parallel = 6 :
per thread rows(size_t) = max rows / parallel = 500000 / 6 = 83333
round = per thread rows / rowset = 83333 / 5000 = 16 (actually do one more
round)
Finally , 5000 * 17 * 6 = 510000 rows will be load and copy
You can merge this pull request into a Git repository by running:
$ git pull https://github.com/Weixin-Xu/incubator-trafodion odbtest
Alternatively you can review and apply these changes as the patch at:
https://github.com/apache/incubator-trafodion/pull/1121.patch
To close this pull request, make a commit to your master/trunk branch
with (at least) the following in the commit message:
This closes #1121
----
commit 0abe062c2ce5150ab36344434129d9864c9fea3f
Author: Weixin Xu <[email protected]>
Date: 2017-06-16T02:44:53Z
[TRAFODION-2346] ODB copy incorrect rows when using rowset and parallel
----
> odb copy 510k rows while set parameter max 500k
> ------------------------------------------------
>
> Key: TRAFODION-2346
> URL: https://issues.apache.org/jira/browse/TRAFODION-2346
> Project: Apache Trafodion
> Issue Type: Bug
> Components: db-utility-odb
> Reporter: zhangliang
> Assignee: zhangliang
> Priority: Minor
>
> I use this command to copy data from oracle to trafodion.
> ./odb64luo -u zale:trafodion -p zale:traf123 -d orac:gy_poc -cp
> src=BIG_TABLE:tgt=TRAFODION.ODB_COPY.COPY_p6:rows=5000:max=500000:truncate:parallel=6
> -T 128 -v
> It should be 500k rows copied, but 510k rows were copied, here is the log:
> [root@gy08 odb_test]# ./odb64luo -u zale:trafodion -p zale:traf123 -d
> orac:gy_poc -cp
> src=BIG_TABLE:tgt=TRAFODION.ODB_COPY.COPY_p6:rows=5000:max=500000:truncate:parallel=6
> -T 128 -v
> Connected to Oracle
> odb [main(1457)] - Warning: won't be created more thread (128) then needed
> (18).
> odb [2016-11-09 13:58:47]: starting ODBC connection(s)... 0 >1 >2 3 >4 >5 6
> >7 >8 9 >10 >11 12 >13 >14 15 >16 >17
> Connected to Trafodion
> odb: Now truncating target table (DELETE FROM TRAFODION.ODB_COPY.COPY_p6)
> [1.0.0]--- 0 row(s) deleted in 9.011s (prep 8.959s, exec 0.052s, fetch
> 0.000s/0.000s)
> [0] odb [Ocopy(10210)] - SOURCE statement: SELECT * FROM BIG_TABLE WHERE
> MOD(ORA_HASH(ROWID), 6) = 0
> [6] odb [Ocopy(10210)] - SOURCE statement: SELECT * FROM BIG_TABLE WHERE
> MOD(ORA_HASH(ROWID), 6) = 2
> [3] odb [Ocopy(10210)] - SOURCE statement: SELECT * FROM BIG_TABLE WHERE
> MOD(ORA_HASH(ROWID), 6) = 1
> [9] odb [Ocopy(10210)] - SOURCE statement: SELECT * FROM BIG_TABLE WHERE
> MOD(ORA_HASH(ROWID), 6) = 3
> [15] odb [Ocopy(10210)] - SOURCE statement: SELECT * FROM BIG_TABLE WHERE
> MOD(ORA_HASH(ROWID), 6) = 5
> [12] odb [Ocopy(10210)] - SOURCE statement: SELECT * FROM BIG_TABLE WHERE
> MOD(ORA_HASH(ROWID), 6) = 4
> [3] odb [Ocopy(10552)] - TARGET statement: INSERT INTO
> TRAFODION.ODB_COPY.COPY_p6 VALUES (?,?)
> [15] odb [Ocopy(10552)] - TARGET statement: INSERT INTO
> TRAFODION.ODB_COPY.COPY_p6 VALUES (?,?)
> [16] 5,000 records copied [commit]
> [0] odb [Ocopy(10552)] - TARGET statement: INSERT INTO
> TRAFODION.ODB_COPY.COPY_p6 VALUES (?,?)
> [4] 5,000 records copied [commit]
> [12] odb [Ocopy(10552)] - TARGET statement: INSERT INTO
> TRAFODION.ODB_COPY.COPY_p6 VALUES (?,?)
> [1] 5,000 records copied [commit]
> [5] 5,000 records copied [commit]
> [17] 5,000 records copied [commit]
> [16] 10,000 records copied [commit]
> [2] 5,000 records copied [commit]
> [4] 10,000 records copied [commit]
> [17] 10,000 records copied [commit]
> [13] 5,000 records copied [commit]
> [4] 15,000 records copied [commit]
> [16] 15,000 records copied [commit]
> [14] 5,000 records copied [commit]
> [17] 15,000 records copied [commit]
> [4] 20,000 records copied [commit]
> [1] 10,000 records copied [commit]
> [5] 10,000 records copied [commit]
> [16] 20,000 records copied [commit]
> [4] 25,000 records copied [commit]
> [13] 10,000 records copied [commit]
> [1] 15,000 records copied [commit]
> [16] 25,000 records copied [commit]
> [4] 30,000 records copied [commit]
> [13] 15,000 records copied [commit]
> [1] 20,000 records copied [commit]
> [16] 30,000 records copied [commit]
> [5] 15,000 records copied [commit]
> [14] 10,000 records copied [commit]
> [2] 10,000 records copied [commit]
> [16] 35,000 records copied [commit]
> [4] 35,000 records copied [commit]
> [13] 20,000 records copied [commit]
> [1] 25,000 records copied [commit]
> [5] 20,000 records copied [commit]
> [14] 15,000 records copied [commit]
> [9] odb [Ocopy(10552)] - TARGET statement: INSERT INTO
> TRAFODION.ODB_COPY.COPY_p6 VALUES (?,?)
> [2] 15,000 records copied [commit]
> [17] 20,000 records copied [commit]
> [6] odb [Ocopy(10552)] - TARGET statement: INSERT INTO
> TRAFODION.ODB_COPY.COPY_p6 VALUES (?,?)
> [13] 25,000 records copied [commit]
> [16] 40,000 records copied [commit]
> [4] 40,000 records copied [commit]
> [1] 30,000 records copied [commit]
> [13] 30,000 records copied [commit]
> [5] 25,000 records copied [commit]
> [2] 20,000 records copied [commit]
> [13] 35,000 records copied [commit]
> [16] 45,000 records copied [commit]
> [10] 5,000 records copied [commit]
> [17] 25,000 records copied [commit]
> [4] 45,000 records copied [commit]
> [7] 5,000 records copied [commit]
> [1] 35,000 records copied [commit]
> [13] 40,000 records copied [commit]
> [11] 5,000 records copied [commit]
> [16] 50,000 records copied [commit]
> [4] 50,000 records copied [commit]
> [8] 5,000 records copied [commit]
> [2] 25,000 records copied [commit]
> [13] 45,000 records copied [commit]
> [10] 10,000 records copied [commit]
> [16] 55,000 records copied [commit]
> odb: thread 16 closing connection...
> odb: thread 16 is ending...
> [7] 10,000 records copied [commit]
> [1] 40,000 records copied [commit]
> [13] 50,000 records copied [commit]
> [10] 15,000 records copied [commit]
> [17] 30,000 records copied [commit]
> [4] 55,000 records copied [commit]
> odb: thread 4 closing connection...
> odb: thread 4 is ending...
> [8] 10,000 records copied [commit]
> [2] 30,000 records copied [commit]
> [14] 20,000 records copied [commit]
> odb: thread 17 closing connection...
> [5] 30,000 records copied [commit]
> [7] 15,000 records copied [commit]
> [1] 45,000 records copied [commit]
> [13] 55,000 records copied [commit]
> [11] 10,000 records copied [commit]
> [10] 20,000 records copied [commit]
> odb: thread 17 is ending...
> odb: thread 5 closing connection...
> odb: thread 5 is ending...
> [1] 50,000 records copied [commit]
> [14] 25,000 records copied [commit]
> [11] 15,000 records copied [commit]
> odb: thread 15 closing connection...
> odb: thread 3 closing connection...
> [7] 20,000 records copied [commit]
> [2] 35,000 records copied [commit]
> odb: thread 1 closing connection...
> [13] 60,000 records copied [commit]
> odb: thread 14 closing connection...
> [10] 25,000 records copied [commit]
> [8] 15,000 records copied [commit]
> odb: thread 15 is ending...
> odb: thread 3 is ending...
> [7] 25,000 records copied [commit]
> odb: thread 1 is ending...
> odb: thread 13 closing connection...
> odb: thread 14 is ending...
> [11] 20,000 records copied [commit]
> [7] 30,000 records copied [commit]
> odb: thread 2 closing connection...
> odb: thread 12 closing connection...
> [10] 30,000 records copied [commit]
> odb: thread 13 is ending...
> [7] 35,000 records copied [commit]
> odb: thread 12 is ending...
> [11] 25,000 records copied [commit]
> odb: thread 0 closing connection...
> odb: thread 0 is ending...
> odb: thread 2 is ending...
> [7] 40,000 records copied [commit]
> [10] 35,000 records copied [commit]
> [7] 45,000 records copied [commit]
> [10] 40,000 records copied [commit]
> [8] 20,000 records copied [commit]
> [11] 30,000 records copied [commit]
> [7] 50,000 records copied [commit]
> [10] 45,000 records copied [commit]
> [8] 25,000 records copied [commit]
> [10] 50,000 records copied [commit]
> [7] 55,000 records copied [commit]
> odb: thread 11 closing connection...
> [8] 30,000 records copied [commit]
> odb: thread 7 closing connection...
> odb: thread 11 is ending...
> odb: thread 8 closing connection...
> odb: thread 7 is ending...
> odb: thread 8 is ending...
> odb: thread 6 closing connection...
> [10] 55,000 records copied [commit]
> odb: thread 6 is ending...
> odb: thread 10 closing connection...
> [9] odb version 1.1.0 Copy statistics:
> [0] Source: BIG_TABLE
> [0] Target: TRAFODION.ODB_COPY.COPY_p6
> [0] Total number of columns: 2
> [0] ODBC row size: 45 B (data) + 16 B (len ind)
> [0] Rowset size: 5,000
> [0] Rowset buffer size: 297.85 KiB
> [0] Pre-copy time: 529.145 s (00:08:49.145)
> [0] Copy time: 449.568 s (00:07:29.568)
> [0] Total records copied: 510,000 (1.134 krec/s)
> [0] Copy throughput (ODBC): 0.049 MiB/s (0.171 GiB/h)
> [0] Total/Wait cycles: 17/0
> [0>1] 50,000 records copied in 767.712 (00:12:47.712
> s)
> [0>2] 35,000 records copied in 812.916 (00:13:32.916
> s)
> [3] Total/Wait cycles: 17/0
> [3>4] 55,000 records copied in 685.995 (00:11:25.995
> s)
> [3>5] 30,000 records copied in 756.212 (00:12:36.212
> s)
> [6] Total/Wait cycles: 17/0
> [6>7] 55,000 records copied in 470.136 (00:07:50.136
> s)
> [6>8] 30,000 records copied in 470.142 (00:07:50.142
> s)
> [9] Total/Wait cycles: 17/1
> [9>10] 55,000 records copied in 449.568 (00:07:29.568
> s)
> [9>11] 30,000 records copied in 435.288 (00:07:15.288
> s)
> [12] Total/Wait cycles: 17/0
> [12>13] 60,000 records copied in 745.090
> (00:12:25.090 s)
> [12>14] 25,000 records copied in 720.689
> (00:12:00.689 s)
> [15] Total/Wait cycles: 17/1
> [15>16] 55,000 records copied in 636.156
> (00:10:36.156 s)
> [15>17] 30,000 records copied in 703.201
> (00:11:43.201 s)
> odb: thread 10 is ending...
> odb: thread 9 closing connection...
> odb: thread 9 is ending...
> odb [2016-11-09 14:15:06]: exiting. Session Elapsed time 978.722 seconds
> (00:16:18.722)
> and select from trafodion like this:
> SQL>select count(*) from copy_p6;
> (EXPR)
> --------------------
> 510000
> --- 1 row(s) selected.
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
