Slow after script execution

[former_SQLA_member1694872]

I get the impression that every time I make a change in the structure of a table must run the command line "reoarganize table ..." Just added new fields, it may be that some field is coming from foreign key, and some uncommon fields. After the execution of my scripts clients then immediately complain of "slowness" in the system (this slowness, usually technical support here the company ends up being resolved with the option to UNLOAD / RELOAD), but it's a very time consuming job. I am using ASA 9.0.2.3951

In what situation is it really necessary to run the command line "reorganize table ..."?

If anyone can help me, thank you!

[former_SQLA_member1694878 · Accepted solution]

When creating the table for the first time, try using the PCTFREE clause of CREATE TABLE to avoid this issue.

If you create a table like this:

CREATE TABLE example(pk bigint, col1 int, col2 char(4));

the row width is 16 bytes. There is a small amount of overhead for each row, as well as a small amount of overhead for each page.

We'll fill the page with rows:

INSERT INTO example
SELECT row_num, 1, 'abcd' FROM sa_rowgenerator(1,168);
CHECKPOINT;
SELECT table_page_count,ext_page_count FROM SYSTABLE WHERE table_name = 'example';

Result:1,0

Notice exactly one page is used. Now add a column:

ALTER TABLE example ADD newcol int;
UPDATE example SET newcol = 2;
CHECKPOINT;
SELECT table_page_count,ext_page_count FROM SYSTABLE WHERE table_name = 'example';

Result:1,1

Because the page holding the row was already full, the new column had to go in a separate extended page. Now a SELECT pk, newcol FROM example has to read from two pages for the rows in question. This is slower than having the whole row on the same page.

If we add PCTFREE 15 to reserve space in each page, columns can be added with more flexibility. Of course this also mean that we'll have less rows per page, and inefficient page usage if the columns are never added.

-- Create table with 15% free reserved space
DROP TABLE example;
CREATE TABLE example  (pk bigint, col1 int, col2 char(4), PCTFREE 15) ;

-- Fill page (the page holds less rows now, because we are reserving 15%)
INSERT INTO example
SELECT row_num, 1, 'abcd' FROM sa_rowgenerator(1,150);
CHECKPOINT;
SELECT table_page_count,ext_page_count FROM SYSTABLE WHERE table_name = 'example';

-- Add new column
ALTER TABLE example ADD newcol int;
UPDATE example SET newcol = 2;

-- Observe that the new column fits on the same page
CHECKPOINT;
SELECT table_page_count,ext_page_count FROM SYSTABLE WHERE table_name = 'example';

Result:1,0

[former_SQLA_member1694872 · Accepted solution]

How do I pctfree the alter an existing table?

[former_SQLA_member1694878 · Accepted solution]

ALTER TABLE example ADD (PCTFREE NN); REORGANIZE TABLE example; will reorganize the table and leave extra space on each page for the row to grow.

Maybe I need to be more clear about the caveats though :). Leaving free space on each page, especially large amounts, will actually decrease performance vs. just reorganizing the table. Also the database file could grow significantly to accommodate this extra space. Leaving free space on each page will mean less rows in cache, more pages hit per query, etc.

The trade-off is that with free space, altering the row length (i.e. adding a column) a small amount shouldn't have as big of a performance impact. Of course, a significant addition will exhaust the free space and you'll be back where you started.

This is really application specific though. It's probably best to try reproducing the issue in a development environment, and trying various adjustments to see what works best.

As Nick mentioned, there are a variety of other potential causes that could be investigated too. Reproducing the issue and grabbing a detailed execution plan would be a good first step before trying any changes.

[former_SQLA_member1694872 · Accepted solution]

@Mikel Rychlisk

Ok. I'll do the tests in the development environment. Thank you.

[former_SQLA_member1694872 · Accepted solution]

Hi Mikel Rychlisk,

Perform various tests and changed the size of the database page to 8k and still and assigns pctfree 20, the result was surprising (much improved performance :)), the system is running smooth smooth smooth. thanks for the tip. Success for you!

[Former Member]

If unload/reload improves the performance it sounds like you are experiencing some degree of table fragmentation due to page splits and probably a higher page count. That could indicate you may have a marginally optimizable query and investigating that could be worthwhile exercise. So too would be a check for weak statistics (and a subsequent investigation of how much of a benefit a create statistics operation alone provides).

Adding columns to rows that have filled table pages has a significant potential of filling up the pages and require many (or most) to be split into more than one page. This would also impact the content of index leaf pages and could easily reduce the degree of clustering there. All of which could benefit from a reorganization. UNLOAD+TRUNCATE+RELOAD would accomplish that. So too would a REORGANIZE TABLE which can happen during production ours and live on an active database.

There could easily be more complexity to the issues surround the performance of multiple queries and those would need to be identified and analyzed individually but at the level of information provided the above should answer your main question.

HTH

[former_SQLA_member1694872]

Hi guys,

Run multiple tests, I onload / reload of the and changed the size of the database page to 8k and still and assigns pctfree 20 (listed tables with higher record - about 35 tables), the result was surprising (much improved the performance :)), the system is running smooth smooth smooth. thanks for the tip. Success for everyone!