Re: Blocks in DBA_TABLES and DBA_SEGMENTS (2)

On 3 May 2002 14:01:18 -0700, Jeff Y. Y. wrote:

>Thank you for your help. I wrote below script to get the Blocks in
>DBA_TABLES and DBA_SEGMENTS, and to see free blocks in each segment.
>Surprisingly, the difference is big and a lot of segments are full.

First, have you analyze your table recently ? The empty_blocks and blocks fields of dba_tables are updated only when you analyze your table.
The blocks field of dba_segments is automatically updated.

It seems that your statistics are not up to date.

>1. How can I know if extent will be allocated to those segments
>automatically?

A new extent is allocated when there is a need for space. If the field empty_blocks (dba_tables) is near 0, the probability of a imminent allocation of a new extent is high.

>2. Should I manually allocate extents to the "full" segments to
>improve the performance (using ALTER TABLE ...)?

No. The allocation of a new extent should not be frequent. It should not be noticeable.

>3. For the segment with big number of extents (such as 6186), is there
>a way to reduce the number of extents without deleting the data?
>(because Oracle doesn't recommend large number of extents for a
>segment)

alter table move ... (rebuild the indexes after this operation) or
export/import

Personally, I'm trying to keep the number of extents below 500 (I often read 1000). The extent size should always be a multiple of the parameter db_file_multiblock_read_count.

>4. When will the database stop to allocate extent to a segment (until
>reaching MAXEXTENTS?)?

When it will reach maxextents or when it will not find enough space for a new extent.

>5. Where is the 251 blocks (dba_segments.blocks - dba_tables.blocks =
>260 - 9) in the last segment?

You should reanalyze your schema and rerun your query.

>I am using 8.1.7.0.0 on NT 2000. I like to know if anything wrong in
>my database.
>
>Thank you very much again.
>
>Jeff
>_____________________________________________________________-
> > select table_name, a.empty_blocks "empty",
> 2 1-(empty_blocks/(empty_blocks+a.blocks)) "% Block Used",
> 3 a.blocks "t_block", b.blocks "s_block", b.extents,
> 4 b.bytes/1024 "KBytes"
> 5 from dba_tables a, dba_segments b
> 6 where a.owner = upper('&owner')
> 7 and 1-(empty_blocks/(empty_blocks+a.blocks)) > .95
> 8 and a.owner = b.owner
> 9 and a.table_name = b.segment_name
> 10 order by table_name;
>Enter value for owner: prod
>
>TABLE_NAME empty % Block Used t_block s_block EXTENTS KBytes
>---------- ----- ------------ -------- -------- ------- --------
>T42UI800 0 1 19 30 3 240
>T4301 5 .996744792 1531 2087 211 16,696
>T4311 0 1 8811 11703 1183 93,624
>T43121 5 .999603363 12601 16866 1704 134,928
>T43199 5 .999891658 46145 61200 6186 489,600
>T4801 0 1 9 260 26 2,080
Received on Fri May 03 2002 - 16:56:44 CDT