Hi community,

We are still testing for Migration from 14.FC4W1 and 14.FC11 to 15.0.0.2.

During our tests we recognized, that only after activating Infrastructure Level (IL) 1, a specific script which usually takes about 240 seconds suddenly took over 2 hours.

When setting

TABLE_SIZE SMALL

in onconfig, the script now runs with acceptable 247 seconds.

During my investigation, the query plan (SET EXPLAIN ON), was exactly the same in 14, 15 IL0 and IL1. That's why i finally thought it might have to do something with SMALL/LARGE tables in IL1.

The second statement in the script - which is the sql which really took long with IL1 - is a

insert into t select ... from a, b ... where ... <join condition>

while the first statement is a

create temp table t ...

So i suppose when TABLE_SIZE LARGE (default) was set in onconfig, the temporary table was created as LARGE table. And that the time was spent on converting the SMALL table data from the select into the temp LARGE table.

However, can it be that this has such a huge impact? And further more: What type of temp table (SMALL/LARGE) to use if we finally have a "real" LARGE table? Or if we join a "real" LARGE table to a "real" SMALL table?

Or are there any best practices (in the interwebs) how to deal with this situation?

Hmm - interesting.

Inserting into a large temp. table does not convert anything. Is simply creating a new temp. table in the LARGE page format. (in my understanding so far)
This should not make such a difference. 
Can we please get a case?
And can you please share the "repro"? I think we need some amount of data to see a difference.

What also would be interesting is a SQLTRACE. This should tell in more detail where the time is spend. 

Hi community,

We are still testing for Migration from 14.FC4W1 and 14.FC11 to 15.0.0.2.

During our tests we recognized, that only after activating Infrastructure Level (IL) 1, a specific script which usually takes about 240 seconds suddenly took over 2 hours.

When setting

TABLE_SIZE SMALL

in onconfig, the script now runs with acceptable 247 seconds.

During my investigation, the query plan (SET EXPLAIN ON), was exactly the same in 14, 15 IL0 and IL1. That's why i finally thought it might have to do something with SMALL/LARGE tables in IL1.

The second statement in the script - which is the sql which really took long with IL1 - is a

insert into t select ... from a, b ... where ... <join condition>

while the first statement is a

create temp table t ...

So i suppose when TABLE_SIZE LARGE (default) was set in onconfig, the temporary table was created as LARGE table. And that the time was spent on converting the SMALL table data from the select into the temp LARGE table.

However, can it be that this has such a huge impact? And further more: What type of temp table (SMALL/LARGE) to use if we finally have a "real" LARGE table? Or if we join a "real" LARGE table to a "real" SMALL table?

Or are there any best practices (in the interwebs) how to deal with this situation?

Case is already initiated from my colleague (TS021230355), as you already have seen.

Hmm - interesting.

Inserting into a large temp. table does not convert anything. Is simply creating a new temp. table in the LARGE page format. (in my understanding so far)
This should not make such a difference. 
Can we please get a case?
And can you please share the "repro"? I think we need some amount of data to see a difference.

What also would be interesting is a SQLTRACE. This should tell in more detail where the time is spend. 

Hi community,

We are still testing for Migration from 14.FC4W1 and 14.FC11 to 15.0.0.2.

During our tests we recognized, that only after activating Infrastructure Level (IL) 1, a specific script which usually takes about 240 seconds suddenly took over 2 hours.

When setting

TABLE_SIZE SMALL

in onconfig, the script now runs with acceptable 247 seconds.

During my investigation, the query plan (SET EXPLAIN ON), was exactly the same in 14, 15 IL0 and IL1. That's why i finally thought it might have to do something with SMALL/LARGE tables in IL1.

The second statement in the script - which is the sql which really took long with IL1 - is a

insert into t select ... from a, b ... where ... <join condition>

while the first statement is a

create temp table t ...

So i suppose when TABLE_SIZE LARGE (default) was set in onconfig, the temporary table was created as LARGE table. And that the time was spent on converting the SMALL table data from the select into the temp LARGE table.

However, can it be that this has such a huge impact? And further more: What type of temp table (SMALL/LARGE) to use if we finally have a "real" LARGE table? Or if we join a "real" LARGE table to a "real" SMALL table?

Or are there any best practices (in the interwebs) how to deal with this situation?

Hi community,

We are still testing for Migration from 14.FC4W1 and 14.FC11 to 15.0.0.2.

During our tests we recognized, that only after activating Infrastructure Level (IL) 1, a specific script which usually takes about 240 seconds suddenly took over 2 hours.

When setting

TABLE_SIZE SMALL

in onconfig, the script now runs with acceptable 247 seconds.

During my investigation, the query plan (SET EXPLAIN ON), was exactly the same in 14, 15 IL0 and IL1. That's why i finally thought it might have to do something with SMALL/LARGE tables in IL1.

The second statement in the script - which is the sql which really took long with IL1 - is a

insert into t select ... from a, b ... where ... <join condition>

while the first statement is a

create temp table t ...

So i suppose when TABLE_SIZE LARGE (default) was set in onconfig, the temporary table was created as LARGE table. And that the time was spent on converting the SMALL table data from the select into the temp LARGE table.

However, can it be that this has such a huge impact? And further more: What type of temp table (SMALL/LARGE) to use if we finally have a "real" LARGE table? Or if we join a "real" LARGE table to a "real" SMALL table?

Or are there any best practices (in the interwebs) how to deal with this situation?

Hi community,

We are still testing for Migration from 14.FC4W1 and 14.FC11 to 15.0.0.2.

During our tests we recognized, that only after activating Infrastructure Level (IL) 1, a specific script which usually takes about 240 seconds suddenly took over 2 hours.

When setting

TABLE_SIZE SMALL

in onconfig, the script now runs with acceptable 247 seconds.

During my investigation, the query plan (SET EXPLAIN ON), was exactly the same in 14, 15 IL0 and IL1. That's why i finally thought it might have to do something with SMALL/LARGE tables in IL1.

The second statement in the script - which is the sql which really took long with IL1 - is a

insert into t select ... from a, b ... where ... <join condition>

while the first statement is a

create temp table t ...

So i suppose when TABLE_SIZE LARGE (default) was set in onconfig, the temporary table was created as LARGE table. And that the time was spent on converting the SMALL table data from the select into the temp LARGE table.

However, can it be that this has such a huge impact? And further more: What type of temp table (SMALL/LARGE) to use if we finally have a "real" LARGE table? Or if we join a "real" LARGE table to a "real" SMALL table?

Or are there any best practices (in the interwebs) how to deal with this situation?

I, frankly, would be curious to learn about any such overhead - off the top of my head, I couldn't think of much.

The only substantial difference I'd see are wider index entries, but not why this would be making such a performance difference.

So yes, we should get to the details of this actual case.
For instance

• how wide are the rows?
• are legacy blob types (BYTE/TEXT) involved?
• do we even have indices?
• is this a logged temp table?

Andreas

Hi community,

We are still testing for Migration from 14.FC4W1 and 14.FC11 to 15.0.0.2.

During our tests we recognized, that only after activating Infrastructure Level (IL) 1, a specific script which usually takes about 240 seconds suddenly took over 2 hours.

When setting

TABLE_SIZE SMALL

in onconfig, the script now runs with acceptable 247 seconds.

During my investigation, the query plan (SET EXPLAIN ON), was exactly the same in 14, 15 IL0 and IL1. That's why i finally thought it might have to do something with SMALL/LARGE tables in IL1.

The second statement in the script - which is the sql which really took long with IL1 - is a

insert into t select ... from a, b ... where ... <join condition>

while the first statement is a

create temp table t ...

So i suppose when TABLE_SIZE LARGE (default) was set in onconfig, the temporary table was created as LARGE table. And that the time was spent on converting the SMALL table data from the select into the temp LARGE table.

However, can it be that this has such a huge impact? And further more: What type of temp table (SMALL/LARGE) to use if we finally have a "real" LARGE table? Or if we join a "real" LARGE table to a "real" SMALL table?

Or are there any best practices (in the interwebs) how to deal with this situation?

Hi community,

We are still testing for Migration from 14.FC4W1 and 14.FC11 to 15.0.0.2.

During our tests we recognized, that only after activating Infrastructure Level (IL) 1, a specific script which usually takes about 240 seconds suddenly took over 2 hours.

When setting

TABLE_SIZE SMALL

in onconfig, the script now runs with acceptable 247 seconds.

During my investigation, the query plan (SET EXPLAIN ON), was exactly the same in 14, 15 IL0 and IL1. That's why i finally thought it might have to do something with SMALL/LARGE tables in IL1.

The second statement in the script - which is the sql which really took long with IL1 - is a

insert into t select ... from a, b ... where ... <join condition>

while the first statement is a

create temp table t ...

So i suppose when TABLE_SIZE LARGE (default) was set in onconfig, the temporary table was created as LARGE table. And that the time was spent on converting the SMALL table data from the select into the temp LARGE table.

However, can it be that this has such a huge impact? And further more: What type of temp table (SMALL/LARGE) to use if we finally have a "real" LARGE table? Or if we join a "real" LARGE table to a "real" SMALL table?

Or are there any best practices (in the interwebs) how to deal with this situation?

Hi Leo, 

About your advice:

It would be nice to avoid application code changes because of this Informix behavior, and force programmers to use 

CREATE SMALL TEMP TABLE ...

All over the place.

Would it not be possible to add an ONCONFIG parameter to defined the default size mode for temp tables?

TEMP_TABLE_SIZE = { SMALL | LARGE }

That would overwrite the definition of TABLE_SIZE.

Side note: in the ONCONFIG file created after installation, I can read following comment for the TABLE_SIZE parameter:

# TABLE_SIZE     - The default size of newly created permanent tables.

It should read:

# TABLE_SIZE     - The default size of newly created permanent and temporary tables.

Seb

Hi community,

We are still testing for Migration from 14.FC4W1 and 14.FC11 to 15.0.0.2.

During our tests we recognized, that only after activating Infrastructure Level (IL) 1, a specific script which usually takes about 240 seconds suddenly took over 2 hours.

When setting

TABLE_SIZE SMALL

in onconfig, the script now runs with acceptable 247 seconds.

During my investigation, the query plan (SET EXPLAIN ON), was exactly the same in 14, 15 IL0 and IL1. That's why i finally thought it might have to do something with SMALL/LARGE tables in IL1.

The second statement in the script - which is the sql which really took long with IL1 - is a

insert into t select ... from a, b ... where ... <join condition>

while the first statement is a

create temp table t ...

So i suppose when TABLE_SIZE LARGE (default) was set in onconfig, the temporary table was created as LARGE table. And that the time was spent on converting the SMALL table data from the select into the temp LARGE table.

However, can it be that this has such a huge impact? And further more: What type of temp table (SMALL/LARGE) to use if we finally have a "real" LARGE table? Or if we join a "real" LARGE table to a "real" SMALL table?

Or are there any best practices (in the interwebs) how to deal with this situation?

Sebastien:

Good suggestion. Make it an RFE and publish the link here so we can vote on it.

Art

Hi Leo, 

About your advice:

It would be nice to avoid application code changes because of this Informix behavior, and force programmers to use 

CREATE SMALL TEMP TABLE ...

All over the place.

Would it not be possible to add an ONCONFIG parameter to defined the default size mode for temp tables?

TEMP_TABLE_SIZE = { SMALL | LARGE }

That would overwrite the definition of TABLE_SIZE.

Side note: in the ONCONFIG file created after installation, I can read following comment for the TABLE_SIZE parameter:

# TABLE_SIZE     - The default size of newly created permanent tables.

It should read:

# TABLE_SIZE     - The default size of newly created permanent and temporary tables.

Seb

Hi community,

We are still testing for Migration from 14.FC4W1 and 14.FC11 to 15.0.0.2.

During our tests we recognized, that only after activating Infrastructure Level (IL) 1, a specific script which usually takes about 240 seconds suddenly took over 2 hours.

When setting

TABLE_SIZE SMALL

in onconfig, the script now runs with acceptable 247 seconds.

During my investigation, the query plan (SET EXPLAIN ON), was exactly the same in 14, 15 IL0 and IL1. That's why i finally thought it might have to do something with SMALL/LARGE tables in IL1.

The second statement in the script - which is the sql which really took long with IL1 - is a

insert into t select ... from a, b ... where ... <join condition>

while the first statement is a

create temp table t ...

So i suppose when TABLE_SIZE LARGE (default) was set in onconfig, the temporary table was created as LARGE table. And that the time was spent on converting the SMALL table data from the select into the temp LARGE table.

However, can it be that this has such a huge impact? And further more: What type of temp table (SMALL/LARGE) to use if we finally have a "real" LARGE table? Or if we join a "real" LARGE table to a "real" SMALL table?

Or are there any best practices (in the interwebs) how to deal with this situation?

Hello Art!

https://ideas.ibm.com/ideas/INFX-I-697

Seb

Sebastien:

Good suggestion. Make it an RFE and publish the link here so we can vote on it.

Art

Hi Leo, 

About your advice:

It would be nice to avoid application code changes because of this Informix behavior, and force programmers to use 

CREATE SMALL TEMP TABLE ...

All over the place.

Would it not be possible to add an ONCONFIG parameter to defined the default size mode for temp tables?

TEMP_TABLE_SIZE = { SMALL | LARGE }

That would overwrite the definition of TABLE_SIZE.

Side note: in the ONCONFIG file created after installation, I can read following comment for the TABLE_SIZE parameter:

# TABLE_SIZE     - The default size of newly created permanent tables.

It should read:

# TABLE_SIZE     - The default size of newly created permanent and temporary tables.

Seb

Hi community,

We are still testing for Migration from 14.FC4W1 and 14.FC11 to 15.0.0.2.

During our tests we recognized, that only after activating Infrastructure Level (IL) 1, a specific script which usually takes about 240 seconds suddenly took over 2 hours.

When setting

TABLE_SIZE SMALL

in onconfig, the script now runs with acceptable 247 seconds.

During my investigation, the query plan (SET EXPLAIN ON), was exactly the same in 14, 15 IL0 and IL1. That's why i finally thought it might have to do something with SMALL/LARGE tables in IL1.

The second statement in the script - which is the sql which really took long with IL1 - is a

insert into t select ... from a, b ... where ... <join condition>

while the first statement is a

create temp table t ...

So i suppose when TABLE_SIZE LARGE (default) was set in onconfig, the temporary table was created as LARGE table. And that the time was spent on converting the SMALL table data from the select into the temp LARGE table.

However, can it be that this has such a huge impact? And further more: What type of temp table (SMALL/LARGE) to use if we finally have a "real" LARGE table? Or if we join a "real" LARGE table to a "real" SMALL table?

Or are there any best practices (in the interwebs) how to deal with this situation?

Hi Leo, 

About your advice:

It would be nice to avoid application code changes because of this Informix behavior, and force programmers to use 

CREATE SMALL TEMP TABLE ...

All over the place.

Would it not be possible to add an ONCONFIG parameter to defined the default size mode for temp tables?

TEMP_TABLE_SIZE = { SMALL | LARGE }

That would overwrite the definition of TABLE_SIZE.

Side note: in the ONCONFIG file created after installation, I can read following comment for the TABLE_SIZE parameter:

# TABLE_SIZE     - The default size of newly created permanent tables.

It should read:

# TABLE_SIZE     - The default size of newly created permanent and temporary tables.

Seb

Hi community,

We are still testing for Migration from 14.FC4W1 and 14.FC11 to 15.0.0.2.

During our tests we recognized, that only after activating Infrastructure Level (IL) 1, a specific script which usually takes about 240 seconds suddenly took over 2 hours.

When setting

TABLE_SIZE SMALL

in onconfig, the script now runs with acceptable 247 seconds.

During my investigation, the query plan (SET EXPLAIN ON), was exactly the same in 14, 15 IL0 and IL1. That's why i finally thought it might have to do something with SMALL/LARGE tables in IL1.

The second statement in the script - which is the sql which really took long with IL1 - is a

insert into t select ... from a, b ... where ... <join condition>

while the first statement is a

create temp table t ...

So i suppose when TABLE_SIZE LARGE (default) was set in onconfig, the temporary table was created as LARGE table. And that the time was spent on converting the SMALL table data from the select into the temp LARGE table.

However, can it be that this has such a huge impact? And further more: What type of temp table (SMALL/LARGE) to use if we finally have a "real" LARGE table? Or if we join a "real" LARGE table to a "real" SMALL table?

Or are there any best practices (in the interwebs) how to deal with this situation?

Hi,

As i wrote yesterday i'm still checking with the experts what is actually going on. And i have to be more precise. With $ONCONFIG:TABLE_SIZE LARGE the temp DBSpaces (not temp tables as i wrote) get heavily utilized in our specific query. Allthough we changed the query to "unload to '/dev/null' select ...".

It was just my first wild guess as switching between SMALL and LARGE in $ONCONFIG had such a huge impact. And in the first version the rows were actually inserted into a user created temp table. But as no user created temp tables are involved in the current test setup and temp DBSpaces are still heavilly utilized my first assumption seems quite wrong.

So user created temp tables might still be SMALL regardless of $ONCONFIG:TABLE_SIZE.

BR

Wilhelm

Hi Leo, 

About your advice:

It would be nice to avoid application code changes because of this Informix behavior, and force programmers to use 

CREATE SMALL TEMP TABLE ...

All over the place.

Would it not be possible to add an ONCONFIG parameter to defined the default size mode for temp tables?

TEMP_TABLE_SIZE = { SMALL | LARGE }

That would overwrite the definition of TABLE_SIZE.

Side note: in the ONCONFIG file created after installation, I can read following comment for the TABLE_SIZE parameter:

# TABLE_SIZE     - The default size of newly created permanent tables.

It should read:

# TABLE_SIZE     - The default size of newly created permanent and temporary tables.

Seb

Hi community,

We are still testing for Migration from 14.FC4W1 and 14.FC11 to 15.0.0.2.

During our tests we recognized, that only after activating Infrastructure Level (IL) 1, a specific script which usually takes about 240 seconds suddenly took over 2 hours.

When setting

TABLE_SIZE SMALL

in onconfig, the script now runs with acceptable 247 seconds.

During my investigation, the query plan (SET EXPLAIN ON), was exactly the same in 14, 15 IL0 and IL1. That's why i finally thought it might have to do something with SMALL/LARGE tables in IL1.

The second statement in the script - which is the sql which really took long with IL1 - is a

insert into t select ... from a, b ... where ... <join condition>

while the first statement is a

create temp table t ...

So i suppose when TABLE_SIZE LARGE (default) was set in onconfig, the temporary table was created as LARGE table. And that the time was spent on converting the SMALL table data from the select into the temp LARGE table.

However, can it be that this has such a huge impact? And further more: What type of temp table (SMALL/LARGE) to use if we finally have a "real" LARGE table? Or if we join a "real" LARGE table to a "real" SMALL table?

Or are there any best practices (in the interwebs) how to deal with this situation?

Just chiming in here to echo Andreas' point that we believe something else is going on here (aside from large vs. small), and are working to get to the bottom of it asap. As far as I know we're still unable to reproduce the problem internally but we're anxious to do so.

As far as we're concerned large tables, whether permanent or temporary, should perform as well as small tables. Indexes on large tables will have a slightly larger footprint, so you can expect some small difference in I/O performance since more I/O will be necessary to store the same key information, but caching should negate most of this. If large rowids are having a real performance impact we'll want to hear about it. Any significant difference between the performance of large and small tables, all else being equal, would be unexpected and considered a defect. Our internal TPC benchmarks (admittedly, fully cached) do not show a noticeable difference.

Note that user-created temp tables (create temp table..., select into temp) already default to small. You have to explicitly use the 'large' keyword when creating them in order to override the default. This is also true of all tables in system databases (sysadmin, sysha, sysuser, sysmaster, sysutils).

Sorry for the problem Emanuel. Once we figure out what's behind your performance problem we'll make sure this thread is updated.

Thanks.

-jc

Hi,

As i wrote yesterday i'm still checking with the experts what is actually going on. And i have to be more precise. With $ONCONFIG:TABLE_SIZE LARGE the temp DBSpaces (not temp tables as i wrote) get heavily utilized in our specific query. Allthough we changed the query to "unload to '/dev/null' select ...".

It was just my first wild guess as switching between SMALL and LARGE in $ONCONFIG had such a huge impact. And in the first version the rows were actually inserted into a user created temp table. But as no user created temp tables are involved in the current test setup and temp DBSpaces are still heavilly utilized my first assumption seems quite wrong.

So user created temp tables might still be SMALL regardless of $ONCONFIG:TABLE_SIZE.

BR

Wilhelm

Hi Leo, 

About your advice:

It would be nice to avoid application code changes because of this Informix behavior, and force programmers to use 

CREATE SMALL TEMP TABLE ...

All over the place.

Would it not be possible to add an ONCONFIG parameter to defined the default size mode for temp tables?

TEMP_TABLE_SIZE = { SMALL | LARGE }

That would overwrite the definition of TABLE_SIZE.

Side note: in the ONCONFIG file created after installation, I can read following comment for the TABLE_SIZE parameter:

# TABLE_SIZE     - The default size of newly created permanent tables.

It should read:

# TABLE_SIZE     - The default size of newly created permanent and temporary tables.

Seb

Hi community,

We are still testing for Migration from 14.FC4W1 and 14.FC11 to 15.0.0.2.

During our tests we recognized, that only after activating Infrastructure Level (IL) 1, a specific script which usually takes about 240 seconds suddenly took over 2 hours.

When setting

TABLE_SIZE SMALL

in onconfig, the script now runs with acceptable 247 seconds.

During my investigation, the query plan (SET EXPLAIN ON), was exactly the same in 14, 15 IL0 and IL1. That's why i finally thought it might have to do something with SMALL/LARGE tables in IL1.

The second statement in the script - which is the sql which really took long with IL1 - is a

insert into t select ... from a, b ... where ... <join condition>

while the first statement is a

create temp table t ...

So i suppose when TABLE_SIZE LARGE (default) was set in onconfig, the temporary table was created as LARGE table. And that the time was spent on converting the SMALL table data from the select into the temp LARGE table.

However, can it be that this has such a huge impact? And further more: What type of temp table (SMALL/LARGE) to use if we finally have a "real" LARGE table? Or if we join a "real" LARGE table to a "real" SMALL table?

Or are there any best practices (in the interwebs) how to deal with this situation?

Hi jc,

I would like to mention that I've started this post in behalf of my colleague Wilhelm Seyerl because he had troubles to get the initial post online. He also opened the case and works on this issue from our side. So I'm informed all the time.

But of course all other readers will be pleased to be informed of the outcome.

Thank you!

Just chiming in here to echo Andreas' point that we believe something else is going on here (aside from large vs. small), and are working to get to the bottom of it asap. As far as I know we're still unable to reproduce the problem internally but we're anxious to do so.

As far as we're concerned large tables, whether permanent or temporary, should perform as well as small tables. Indexes on large tables will have a slightly larger footprint, so you can expect some small difference in I/O performance since more I/O will be necessary to store the same key information, but caching should negate most of this. If large rowids are having a real performance impact we'll want to hear about it. Any significant difference between the performance of large and small tables, all else being equal, would be unexpected and considered a defect. Our internal TPC benchmarks (admittedly, fully cached) do not show a noticeable difference.

Note that user-created temp tables (create temp table..., select into temp) already default to small. You have to explicitly use the 'large' keyword when creating them in order to override the default. This is also true of all tables in system databases (sysadmin, sysha, sysuser, sysmaster, sysutils).

Sorry for the problem Emanuel. Once we figure out what's behind your performance problem we'll make sure this thread is updated.

Thanks.

-jc

Hi,

As i wrote yesterday i'm still checking with the experts what is actually going on. And i have to be more precise. With $ONCONFIG:TABLE_SIZE LARGE the temp DBSpaces (not temp tables as i wrote) get heavily utilized in our specific query. Allthough we changed the query to "unload to '/dev/null' select ...".

It was just my first wild guess as switching between SMALL and LARGE in $ONCONFIG had such a huge impact. And in the first version the rows were actually inserted into a user created temp table. But as no user created temp tables are involved in the current test setup and temp DBSpaces are still heavilly utilized my first assumption seems quite wrong.

So user created temp tables might still be SMALL regardless of $ONCONFIG:TABLE_SIZE.

BR

Wilhelm

Hi Leo, 

About your advice:

It would be nice to avoid application code changes because of this Informix behavior, and force programmers to use 

CREATE SMALL TEMP TABLE ...

All over the place.

Would it not be possible to add an ONCONFIG parameter to defined the default size mode for temp tables?

TEMP_TABLE_SIZE = { SMALL | LARGE }

That would overwrite the definition of TABLE_SIZE.

Side note: in the ONCONFIG file created after installation, I can read following comment for the TABLE_SIZE parameter:

# TABLE_SIZE     - The default size of newly created permanent tables.

It should read:

# TABLE_SIZE     - The default size of newly created permanent and temporary tables.

Seb

Hi community,

We are still testing for Migration from 14.FC4W1 and 14.FC11 to 15.0.0.2.

During our tests we recognized, that only after activating Infrastructure Level (IL) 1, a specific script which usually takes about 240 seconds suddenly took over 2 hours.

When setting

TABLE_SIZE SMALL

in onconfig, the script now runs with acceptable 247 seconds.

During my investigation, the query plan (SET EXPLAIN ON), was exactly the same in 14, 15 IL0 and IL1. That's why i finally thought it might have to do something with SMALL/LARGE tables in IL1.

The second statement in the script - which is the sql which really took long with IL1 - is a

insert into t select ... from a, b ... where ... <join condition>

while the first statement is a

create temp table t ...

So i suppose when TABLE_SIZE LARGE (default) was set in onconfig, the temporary table was created as LARGE table. And that the time was spent on converting the SMALL table data from the select into the temp LARGE table.

However, can it be that this has such a huge impact? And further more: What type of temp table (SMALL/LARGE) to use if we finally have a "real" LARGE table? Or if we join a "real" LARGE table to a "real" SMALL table?

Or are there any best practices (in the interwebs) how to deal with this situation?

Hi community,

We are still testing for Migration from 14.FC4W1 and 14.FC11 to 15.0.0.2.

During our tests we recognized, that only after activating Infrastructure Level (IL) 1, a specific script which usually takes about 240 seconds suddenly took over 2 hours.

When setting

TABLE_SIZE SMALL

in onconfig, the script now runs with acceptable 247 seconds.

During my investigation, the query plan (SET EXPLAIN ON), was exactly the same in 14, 15 IL0 and IL1. That's why i finally thought it might have to do something with SMALL/LARGE tables in IL1.

The second statement in the script - which is the sql which really took long with IL1 - is a

insert into t select ... from a, b ... where ... <join condition>

while the first statement is a

create temp table t ...

So i suppose when TABLE_SIZE LARGE (default) was set in onconfig, the temporary table was created as LARGE table. And that the time was spent on converting the SMALL table data from the select into the temp LARGE table.

However, can it be that this has such a huge impact? And further more: What type of temp table (SMALL/LARGE) to use if we finally have a "real" LARGE table? Or if we join a "real" LARGE table to a "real" SMALL table?

Or are there any best practices (in the interwebs) how to deal with this situation?

Update: Fix found and should be available in the next V15.0.1.x CSB.

Wilhelm, Hedi and Holger worked together to come up with a repro for development, which was key. The problem was a V15-specific glitch in the kernel that prevented us from releasing a lock during a table drop. Ordinarily this problem isn't noticeable, because the lock is eventually released during commit or when the session exits. But the repro here is a single query that is processed under the covers by creating and dropping 2.7 million temp tables. The unreleased locks prevent us from re-using pages in the temp dbspace's partition partition, so that partition grows and grows. Meanwhile the huge and constantly growing lock table slows the processing of this query to a crawl.

So why the TABLE_SIZE connection? The failure to release the lock during the drop only happens with large tables, and although it's true that user-created tables are always small unless explicitly created with the "LARGE" keyword, these 2.7 million temp tables are internal objects created implicitly during the query, and whether they are large or small will depend on the TABLE_SIZE setting.

Why would we want internal temp tables to be large? After all, aren't they short-lived and usually quite small, as in this case? Well, given that permanent tables can now contain gigantic amounts of data it's possible a query or upsert will need an enormous interim result set. Since users can't control the creation of internal temp tables we take the conservative approach of following TABLE_SIZE.

-jc

Hi community,

We are still testing for Migration from 14.FC4W1 and 14.FC11 to 15.0.0.2.

During our tests we recognized, that only after activating Infrastructure Level (IL) 1, a specific script which usually takes about 240 seconds suddenly took over 2 hours.

When setting

TABLE_SIZE SMALL

in onconfig, the script now runs with acceptable 247 seconds.

During my investigation, the query plan (SET EXPLAIN ON), was exactly the same in 14, 15 IL0 and IL1. That's why i finally thought it might have to do something with SMALL/LARGE tables in IL1.

The second statement in the script - which is the sql which really took long with IL1 - is a

insert into t select ... from a, b ... where ... <join condition>

while the first statement is a

create temp table t ...

So i suppose when TABLE_SIZE LARGE (default) was set in onconfig, the temporary table was created as LARGE table. And that the time was spent on converting the SMALL table data from the select into the temp LARGE table.

However, can it be that this has such a huge impact? And further more: What type of temp table (SMALL/LARGE) to use if we finally have a "real" LARGE table? Or if we join a "real" LARGE table to a "real" SMALL table?

Or are there any best practices (in the interwebs) how to deal with this situation?

Hi community,

We are still testing for Migration from 14.FC4W1 and 14.FC11 to 15.0.0.2.

During our tests we recognized, that only after activating Infrastructure Level (IL) 1, a specific script which usually takes about 240 seconds suddenly took over 2 hours.

When setting

TABLE_SIZE SMALL

in onconfig, the script now runs with acceptable 247 seconds.

During my investigation, the query plan (SET EXPLAIN ON), was exactly the same in 14, 15 IL0 and IL1. That's why i finally thought it might have to do something with SMALL/LARGE tables in IL1.

The second statement in the script - which is the sql which really took long with IL1 - is a

insert into t select ... from a, b ... where ... <join condition>

while the first statement is a

create temp table t ...

So i suppose when TABLE_SIZE LARGE (default) was set in onconfig, the temporary table was created as LARGE table. And that the time was spent on converting the SMALL table data from the select into the temp LARGE table.

However, can it be that this has such a huge impact? And further more: What type of temp table (SMALL/LARGE) to use if we finally have a "real" LARGE table? Or if we join a "real" LARGE table to a "real" SMALL table?

Or are there any best practices (in the interwebs) how to deal with this situation?