I understand better now. It definitely won't be a problem.
Thank you both!
Original Message:
Sent: 3/31/2023 3:46:00 PM
From: Art Kagel
Subject: RE: A lot of extents in temp tables
Leandro:
I didn't realize that you were only concerned about the temp tables. There isn't much you can do if you do not have access to the application code. However, unless you do not have sufficient cache configured a temp table with many extents should not be a problem. In the current releases of the engine, the limit on the number of extents is over 32000 so with extent size doubling that is virtually unlimited. It should also not be a performance issue because if you have sufficient cache configured, those large temp tables should reside completely in memory and not actually flush to disk.
------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com
------------------------------
Original Message:
Sent: Fri March 31, 2023 03:30 PM
From: Mike Walker
Subject: A lot of extents in temp tables
No, there isn't. Is the large number of extents causing a problem?
------------------------------
Mike Walker
xDB Systems, Inc
www.xdbsystems.com
Original Message:
Sent: Fri March 31, 2023 02:49 PM
From: Leandro Kohler
Subject: A lot of extents in temp tables
Humm ok greatBut, this table is part of a partner's ERP system over which I have no control.
Is it possible to specify this space at the instance level?
Original Message:
Sent: 3/31/2023 2:44:00 PM
From: Mike Walker
Subject: RE: A lot of extents in temp tables
Here's the error message for that error:
-21539 Defragment: You can't run this command on catalog/pseudo/temp tables
ACTION
Run defragment on a regular table/partition
So, did you run this against a true temp table, or a catalog table, e.g. usually a table beginning with "sys" or a table in sysmaster?
If these are truly temporary tables, then are they always around? The next time the temp table is created, consider specifying a first and next extent size so that it preallocates more space when created, e.g.:
create temp table mwtesttmp(a serial, b char(10)) extent size 10000 next size 1000;
This will create the table initially with 10,000 KB of space, and when that fills, then it will grow by 1,000 KB each time.
------------------------------
Mike Walker
xDB Systems, Inc
www.xdbsystems.com
Original Message:
Sent: Fri March 31, 2023 02:22 PM
From: Leandro Kohler
Subject: A lot of extents in temp tables
Hey Art, thanks.
So, look at this error when perform defrag:
Failed to defrag the partition 4194355 - ISAM error 21539
Original Message:
Sent: 3/31/2023 1:53:00 PM
From: Art Kagel
Subject: RE: A lot of extents in temp tables
Leandro:
You can defragment tables and indexes most easily using the "defragment" SQL API function in the sysadmin database.
For tables:
execute function task( 'defragment', 'database:owner.table' );
For indexes (or to defragment a single partition of a partitioned table):
execute function task( 'defragment partition', partnum );
You can find the partnum for table partitions and for indexes (and fragments of partitioned indexes) in the sysmaster:systabnames table.
Art
------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com
Original Message:
Sent: Fri March 31, 2023 12:26 PM
From: Leandro Kohler
Subject: A lot of extents in temp tables
Hi everyone,
I have many of tables with high extents what can I do to fix it?
Follow attached print ox tables and my dbspaces (onstat -d)