RE: Growing table

How about some more information:

How are the deletes done? Have you verified that the delete process is in fact completing successfully?

How many freelist groups?

Did you verify the pctfree and pctused settings? And that the current dictionary settings are what was used when the table was created?

If the deletes are done in batch and rows inserted together are likely deleted together then I would up the pctused to 80%.

HTH -- Mark D Powell --

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Terry Sutton Sent: Friday, November 12, 2004 11:11 AM To: oracle-l_at_freelists.org
Subject: Growing table

We have some odd behavior here, and I'm trying to figure out the cause. I know I'm probably missing something semi-obvious, so I'm looking for further ideas.
In a web application, data representing the states of client sessions is held in 2 tables, SESSION_INFO and SESSION_DATA. SESSION_INFO has 1 row for each client connected to the application, and SESSION_DATA is a child table to SESSION_INFO, averaging 5 rows per SESSION_INFO row. During the day it is typical to have ~10,000 rows in SESSION_INFO and 50,000 rows in SESSION_DATA. Average row length for SESSION_INFO is 46, and for SESSION_DATA is 493. So the tables should be around 500KB for SESSION_INFO and 25MB for SESSION_DATA. PCTFREE is 20 and PCTUSED is 60 on each table.

The problem is that the tables keep growing. SESSION_INFO is now 2MB and SESSION_DATA is now >2GB. I could see the size of SESSION_INFO, as that is 4x expected, so it makes sense considering that blocks don't get back on the freelist quickly enough and maybe the number of sessions got to 15-20,000, blah blah blah. But the size of SESSION_DATA is absurd. This is 80 times the expected size. Any ideas what could be causing this? I'm guessing it's a freelist issue, but I don't see the specifics.

The tables are analyzed frequently (gather stale), and average space per block is now >7000 (8K block size). ASSM is not being used. Freelists is 1.

BTW, the concern over size is because we want to have these tables in the KEEP pool, as they're getting accessed millions of times/hour.

--Terry

--

http://www.freelists.org/webpage/oracle-l
--

http://www.freelists.org/webpage/oracle-l Received on Fri Nov 12 2004 - 11:00:34 CST