Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> 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