Growing table
- From: "Terry Sutton" <terrysutton@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 12 Nov 2004 08:11:09 -0800
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
