Re: Growing table
- From: "Terry Sutton" <terrysutton@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 12 Nov 2004 10:37:50 -0800
Deletes are done by a process which runs a few times per hour. The delete
process is completing successfully. The number of rows is appropriate.
They just aren't filling the blocks.
1 freelist group. We're not using RAC.
If by verify, you mean "select pct_free, pct_used from dba_tables where
table_name = 'SESSION_DATA';", yes. These are the settings used when the
table was moved to a new tablespace a while ago.
I don't think increasing the PCTUSED is the solution. If the table were 2-4
times its expected size I could see that. But we're way beyond that.
Thanks,
Terry
----- Original Message -----
From: "Powell, Mark D" <mark.powell@xxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Friday, November 12, 2004 9:03 AM
Subject: 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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Terry Sutton
Sent: Friday, November 12, 2004 11:11 AM
To: oracle-l@xxxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
