View Issue Details

IDProjectCategoryView StatusLast Update
0002415SymmetricDSBugpublic2021-03-30 19:46
Reporterandresff Assigned Toelong  
Priorityhigh 
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,
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;
Tagsdialect: oracle, performance

Relationships

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

Activities

kraynopp

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.

kraynopp

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.

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?

kraynopp

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.

elong

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.

elong

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

admin

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

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