View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0002415 | SymmetricDS | Bug | public | 2015-10-19 13:49 | 2021-03-30 19:46 |
| Reporter | andresff | Assigned To | elong | ||
| Priority | high | ||||
| Status | closed | Resolution | fixed | ||
| Product Version | 3.7.10 | ||||
| Target Version | 3.12.8 | Fixed in Version | 3.12.8 | ||
| Summary | 0002415: Reload a table causes ORA-01652: unable to extend temp segment by 128 in tablespace | ||||
| Description | We are trying to reload a Logs table from Corp to Store (unidirectional synchronization from Store to Corp) and a dead trigger for Logs table. The table is a Logs table with around 195000 events. When we add a custom sym_data values, the outgoing batch is created, but it starts to raise the temporary table size, up to it´s max size throwing the error "ORA-01652: unable to extend temp segment by 128 in tablespace TEMP..." It grows to more than 3 Gigabytes (max size)... | ||||
| Steps To Reproduce | Create a Corp and Store scenario with a Log table. Add around 195000 events in Corp Logs table. Start Sym engine at Store and add Run this query to Sym Corp. NSERT INTO sym_data (node_list, table_name, event_type, row_data, trigger_hist_id, channel_id, create_time) SELECT '001', 'logs', 'R', '1=1', (SELECT MAX(h.trigger_hist_id) FROM sym_trigger_hist h where h.trigger_id='tr_logs_dead'), 'reload', CURRENT_TIMESTAMP FROM dual; Run this query to see how temp table starts to increasi it´s size SELECT username, sum(BLOCKS*8192/(1024*1024)) AS used_mb FROM v$tempseg_usage GROUP BY username; | ||||
| Tags | dialect: oracle, performance | ||||
|
|
Yes, I have the same problem during extraction data from Oracle database. The size of table with ≈ 1 000 000 rows is 72MB, the size of batch file is 98MB, but usage of temporarty tablespace is tremendous - 30GB. Have you got any workaround? This table is not the largest table in database, and I have no idea how to load table with more than 10 000 000 rows... SymmetricDS version is 3.12.4. And I suppose it is regression because of I have no problem in old 3.9.2 SymmetricDS. |
|
|
I have made a little investigation and IMHO this bug is related to columns with type DATE. 1. v$tempseg_usage view shows that SymmetricDS uses LOB_DATA temporary segment type. Not temporary tables. 2. I have made four tests. Test1: Table with PK number(10) and three columns with type DATE. 1000000 rows. reload-table from oracle consumes 45,7 GB of temporary tablespace. Test2: Table with PK number(10), one column with type varchar2(1000) and two columns with type DATE. 1000000 rows. reload-table from oracle consumes 30,52 GB of temporary tablespace. Test3: Table with PK number(10), two columns with type varchar2(1000) and one column with type DATE. 1000000 rows. reload-table from oracle consumes 15,26 GB of temporary tablespace. Test4: Table with PK number(10) and three columns with type varchar2(1000). 1000000 rows. reload-table from oracle consumes 0 GB of temporary tablespace. During first test I captured following SQL: select "ID" as x__0,nvl2(t."DATA1", to_clob('')||concat(concat('"',to_char(t."DATA1", 'YYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=''GREGORIAN''')),'"'), '') as x__1,nvl2(t."DATA2", to_clob('')||concat(concat('"',to_char(t."DATA2", 'YYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=''GREGORIAN''')),'"'), '') as x__2,nvl2(t."DATA3", to_clob('')||concat(concat('"',to_char(t."DATA3", 'YYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=''GREGORIAN''')),'"'), '') as x__3 from "DATA"."SYMTEST3" t where 1=1 I suppose the real reason of this bug is complex type conversion from DATE to CLOB. Is this conversion really necessary? Maybe to_char will be enough? |
|
|
And one more thing. The fact is that the oracle RDBMS allocates space for objects (persistent or temporary, nevermind) by blocks, not by bytes. In my database 1 block=16kB. So, even if you create temporary lob and wrie in this lob 1 byte, 16 kB will be allocated. For table with 1000000 rows and 3 field with type DATE 3000000 temporary lobs will be created. 3000000✕16384=49152000000 bytes, or 45.77 GB. So using LOB is ineffective. I do not know if this information will be useful, but I hope it will. And I hope that this bug will be resolved soon. |
|
|
Some work in 0003806 improved the situation with temp space by selecting columns directly without functions around them. It should select DATE columns directly without using formatting functions. |
|
|
The to_clob('') is added when the USE_CAPTURE_LOBS=1 is set on SYM_TRIGGER. Set USE_CAPTURE_LOBS=0 to remove the to_clob(''). We committed a change for 3.12.8 that will select the date and timestamp columns without any functions around the column. |
|
SymmetricDS: 3.12 3ed79d29 2021-03-16 14:47:02 Details Diff |
0002415: select date and timestamp without column template |
Affected Issues 0002415 |
|
| mod - symmetric-client/src/integrationTest/java/org/jumpmind/symmetric/io/data/writer/AbstractBulkDatabaseWriterTest.java | Diff File | ||
| mod - symmetric-core/src/main/java/org/jumpmind/symmetric/db/AbstractTriggerTemplate.java | Diff File | ||
| mod - symmetric-core/src/test/java/org/jumpmind/symmetric/service/impl/AbstractDataLoaderServiceTest.java | Diff File | ||
| mod - symmetric-db/src/main/java/org/jumpmind/db/platform/AbstractDatabasePlatform.java | Diff File | ||
| mod - symmetric-db/src/main/java/org/jumpmind/db/sql/Row.java | Diff File | ||
| mod - symmetric-io/src/integrationTest/java/org/jumpmind/symmetric/io/data/writer/DatabaseWriterTest.java | Diff File | ||
| mod - symmetric-io/src/test/java/org/jumpmind/symmetric/io/AbstractWriterTest.java | Diff File | ||
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2015-10-19 13:49 | andresff | New Issue | |
| 2019-04-23 19:28 | elong | Tag Attached: performance | |
| 2019-04-23 19:28 | elong | Tag Attached: oracle | |
| 2019-04-23 20:50 | admin | Tag Renamed | oracle => dialect: oracle |
| 2020-10-23 07:25 | kraynopp | Note Added: 0001811 | |
| 2020-10-23 13:22 | kraynopp | Note Added: 0001812 | |
| 2020-10-26 07:11 | kraynopp | Note Added: 0001813 | |
| 2020-11-04 18:52 | elong | Relationship added | related to 0003806 |
| 2020-11-04 18:54 | elong | Note Added: 0001820 | |
| 2020-11-04 18:55 | elong | Target Version | => 3.13.0 |
| 2021-03-16 14:50 | elong | Note Added: 0001898 | |
| 2021-03-16 14:51 | elong | Assigned To | => elong |
| 2021-03-16 14:51 | elong | Status | new => resolved |
| 2021-03-16 14:51 | elong | Resolution | open => fixed |
| 2021-03-16 14:51 | elong | Fixed in Version | => 3.12.8 |
| 2021-03-16 14:51 | elong | Target Version | 3.13.0 => 3.12.8 |
| 2021-03-16 15:00 | admin | Changeset attached | => SymmetricDS 3.12 3ed79d29 |
| 2021-03-30 19:46 | admin | Status | resolved => closed |