View Issue Details

IDProjectCategoryView StatusLast Update
0002415SymmetricDSBugpublic2021-03-30 19:46
Reporterandresff Assigned Toelong  
Status closedResolutionfixed 
Product Version3.7.10 
Target Version3.12.8Fixed in Version3.12.8 
Summary0002415: Reload a table causes ORA-01652: unable to extend temp segment by 128 in tablespace
DescriptionWe 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 ReproduceCreate 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,
trigger_hist_id, channel_id, create_time)

    SELECT '001', 'logs', 'R',
    (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;
Tagsdialect: oracle, performance


related to 0003806 closedelong Initial load large character (varchar 4000) columns and stay under SQL limit 



2020-10-23 07:25

reporter   ~0001811

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.


2020-10-23 13:22

reporter   ~0001812

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.

Table with PK number(10) and three columns with type DATE. 1000000 rows. reload-table from oracle consumes 45,7 GB of temporary tablespace.

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.

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.

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?


2020-10-26 07:11

reporter   ~0001813

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.


2020-11-04 18:54

developer   ~0001820

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.


2021-03-16 14:50

developer   ~0001898

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.

Related Changesets

SymmetricDS: 3.12 3ed79d29

2021-03-16 14:47:02


Details Diff
0002415: select date and timestamp without column template Affected Issues
mod - symmetric-client/src/integrationTest/java/org/jumpmind/symmetric/io/data/writer/ Diff File
mod - symmetric-core/src/main/java/org/jumpmind/symmetric/db/ Diff File
mod - symmetric-core/src/test/java/org/jumpmind/symmetric/service/impl/ Diff File
mod - symmetric-db/src/main/java/org/jumpmind/db/platform/ Diff File
mod - symmetric-db/src/main/java/org/jumpmind/db/sql/ Diff File
mod - symmetric-io/src/integrationTest/java/org/jumpmind/symmetric/io/data/writer/ Diff File
mod - symmetric-io/src/test/java/org/jumpmind/symmetric/io/ Diff File

Issue History

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