RE: Growing table

Hi Terry

Bug aside... I see only two situations that lead to unused free space:

• the inserts are performed with direct load (e.g. hint "append")
• minimize record per block is enabled and a very small value is used

The former is more probably since in the second situation you should = have at least some rows in each block... and according to your = description it is not the case.

HTH
Chris

>-----Original Message-----
>From: oracle-l-bounce_at_freelists.org =

[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Terry
>Sutton
>Sent: 12 November 2004 17:11
>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