OTN Discussion Forums : Worst space allocations ...

user604101

Posts: 388
Registered: 11/06/07

Worst space allocations
Posted: May 12, 2008 5:10 AM

Hi,

I am new to this DBA.

There is a report running daily which says:

10 WORST SPACE ALLOCATIONS - ALLOCATED VS. USED

TABLE_NAME INIT NEXT EXTENTS TOTAL_SIZE ACTUAL_SIZE UNUSED CHAINING

----------

-------

-----------

----------
MARC 314572800 314572800 22 6920601600 291561520 6629040080 0

What do this mean and why is the space allocated is worst?

Thanks

Yas

Posts: 945
Registered: 06/27/00

Re: Worst space allocations
Posted: May 12, 2008 5:22 AM

in response to: user604101

Reply

Oracle has no such report. It looks like a custom report, you should ask your colleagues about it. They can explain its meaning and purpose.

burleson

Posts: 2,343
Registered: 05/06/98

Re: Worst space allocations
Posted: May 12, 2008 5:23 AM

in response to: user604101

Reply

Hi,

I am new to this DBA.

Well, this DBA welcomes you! To get started, Robert Freeman and Steve Karam have some good notes on optimal table space allocation in his "Easy Oracle Jumpstart" book, a great way to undersand the concepts fast:

http://www.rampant-books.com/book_2005_1_easy_oracle_jumpstart.htm

First. let's format it: The

 tag:



TABLE_NAME INIT NEXT EXTENTS TOTAL_SIZE ACTUAL_SIZE UNUSED CHAINING


----------
-------
-----------
----------

MARC 314,572,800 314,572,800 22 6,920,601,600 291,561,520 6,629,040,080 0







Note that:



- Table is 6.9 gig with most of it unused



- 300m is a pretty large NEXT extent size . . . . .



I would reorg this table, if you want toreclaim the unused space:



http://www.dba-oracle.com/t_create_table_select_ctas.htm

user604101

Posts: 388
Registered: 11/06/07

Re: Worst space allocations
Posted: May 12, 2008 6:01 AM

in response to: burleson

Reply

Hi,

How come the Total size of the segment is 6 Gigs and actual size is only 291 Megs.

Do you mean there were lot of deletes in the segment?

If so, why the deletes didn't set the HWM and free the unused space?

Thanks

EdStevens

Posts: 2,851
Registered: 12/13/07

Re: Worst space allocations
Posted: May 12, 2008 6:06 AM

in response to: user604101

Reply

If so, why the deletes didn't set the HWM and free
the unused space?

Thanks

Allocating new extents is a relatively expensive operation. So is deallocating them. If the table grows to a certain size, then a bunch of rows are deleted, it would be less expensive to assume that the formerly used space will be needed again and so leave all of those extents allocated.

ji li

Posts: 820
Registered: 09/24/05

Re: Worst space allocations
Posted: May 12, 2008 6:33 AM

in response to: user604101

Reply

"How come the Total size of the segment is 6 Gigs and actual size is only 291 Megs."

This is because of the way Oracle works.

First, the initial and next extents were somehow set for the table (either by creator of the table, or by default values of the tablespace, or by some other means).

Next, when an Oracle table is populated, (depending on settings of PCTINCREASE), the blocks of an extent are filled, and then Oracle will allocate another extent, using the value of the "NEXT" storage parameter.

For your table to have total size of 6Gb, it must have contained several records at one time, but then most records were deleted.

Oracle still holds the extents for the original segment unless you truncate the table, or deallocate unused blocks.

I would recommend rebuilding the table. This can be done a number of ways.

user604101

Posts: 388
Registered: 11/06/07

Re: Worst space allocations
Posted: May 12, 2008 6:40 AM

in response to: EdStevens

Reply

Hi,

You are right if the table has columns with regular (number/varchar) data types. But what if the segment has LOB (BLOB/CLOB).

Thanks

Jonathan Lewis

Posts: 1,911
Registered: 01/23/07

Re: Worst space allocations
Posted: May 12, 2008 8:01 AM

in response to: user604101

Reply

I think you should follow the advice from Yas before you do anything else.

Find out what the report is supposed to be showing you - it's possible that it was written a long time ago and has some error in it that produces meaningless results in a modern database.

Once you know what it's telling you the you can take appropriate action.

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
http://www.jlcomp.demon.co.uk

Jonathan Lewis

Posts: 1,911
Registered: 01/23/07

Re: Worst space allocations
Posted: May 12, 2008 8:07 AM

in response to: burleson

Reply

Well, this DBA welcomes you! To get started, Robert
Freeman and Steve Karam have some good notes on
optimal table space allocation in his "Easy Oracle
Jumpstart" book, a great way to undersand the
concepts fast:

http://www.rampant-books.com/book_2005_1_easy_oracle_jumpstart.htm

Your resolution in this thread didn't seem to last very long. Remember, you volunteered to disclose the fact when you promoted books that made you money.

Jonathan Lewis
http://jonathanlewis.wordpress.com
http://www.jlcomp.demon.co.uk

user604101

Posts: 388
Registered: 11/06/07

Re: Worst space allocations
Posted: May 12, 2008 8:25 AM

in response to: Jonathan Lewis

Reply

It is run by us, is a daily report that runs on the database to show the activity.

My posting was to find out what it says and how to solve it.

Thanks

Jonathan Lewis

Posts: 1,911
Registered: 01/23/07

Re: Worst space allocations
Posted: May 12, 2008 8:45 AM

in response to: user604101

Reply

It is run by us, is a daily report that runs on the database to show the activity.

My posting was to find out what it says and how to solve it.

It LOOKS as if it might be saying that you've allocated 22 extents of 300Mb each to an object, and only used the first extent and could simply deallocate the other 21 to free up about 6Gb of space.

BUT: you might be running a piece of SQL that was copied from the Internet 15 years ago, and manages to multiply the space allocated in this object by the number of partitions in a table of the same name in a different schema. (Stranger things do happen).

Before you can ask us what the output means, you have to tell us how that output is derived.

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
http://www.jlcomp.demon.co.uk

Ignacio Ruiz

Posts: 517
Registered: 06/27/07

Re: Worst space allocations
Posted: May 12, 2008 1:23 PM

in response to: user604101

Reply

Yes user604101, please... could you post the query that generates that output?

Regards
Ignacio

http://oracledisect.blogspot.com

user604101

Posts: 388
Registered: 11/06/07

Re: Worst space allocations
Posted: May 13, 2008 12:00 PM

in response to: Ignacio Ruiz

Reply

select * from (
select table_name, A.TABLESPACE_NAME, a.initial_extent, a.next_extent, extents,
(a.next_extent * (extents - 1)) + a.initial_extent total_size,
a.num_rows * avg_row_len Actual_size,
((a.next_extent * (extents - 1)) + a.initial_extent) - (a.num_rows * avg_row_len) Unused ,
chain_cnt Chaining
from dba_tables a, dba_segments b where a.owner = 'OWNER' and a.table_name = b.segment_name
and segment_type = 'TABLE' and a.num_rows is not null
order by unused desc)
where rownum < 10;

There is a clob column in the table.

Thanks

Justin Cave

Posts: 24,284
Registered: 10/11/99

Re: Worst space allocations
Posted: May 13, 2008 12:13 PM

in response to: user604101

Reply

This SQL statement is almost certainly not doing what you are expecting it to do...

- Unless all tablespaces are locally managed with uniform extent sizes that have not been migrated from dictionary managed tablespaces, you cannot compute TOTAL_SIZE based on the initial & next extent size and the number of extents. If you do have this (very specific) configuration, TOTAL_SIZE would be initial_extent * extents. On the other hand, there is a BYTES column in DBA_SEGMENTS that could be summed to find the actual size of all the extents.
- DBA_TABLES.NUM_ROWS and AVG_ROW_LENGTH are estimates computed when you gather statistics. So these may be way off if your statistics are old. I believe AVG_ROW_LENGTH is also off if you have out-of-line LOBs. So the ACTUAL_SIZE computation is probably also wrong.
- The UNUSED computation is wrong for the same reasons that TOTAL_SIZE was wrong.

All in all, this SQL statement is almost completely wrong. If you are interested in this sort of information, I'd suggest looking at things like the DBMS_SPACE package.

Justin

Jonathan Lewis

Posts: 1,911
Registered: 01/23/07

Re: Worst space allocations
Posted: May 13, 2008 12:35 PM

in response to: user604101

Reply

Justin has made some good points - but on top of that, there's also a problem with the stats calculation that is version dependent. I don't think you mentioned the version, but here's a simple demo script I ran against 9.2.0.8 then 10.2.0.3

drop table lob_load;
 
create table lob_load (
	id		number(5),
	vc1		varchar2(10),
	vc2		varchar2(10),
	lob_test	clob,
	vc3		varchar2(10)
)
lob (lob_test) store as text_lob(
	enable storage in row 
	nocache
)
;
 
 
insert into lob_load values(
	1, 'abc','def',rpad('x',3000),'ghi'
);
 
commit;
 
begin
	dbms_stats.gather_table_stats(
		ownname		 => user,
		tabname		 =>'LOB_LOAD',
		partname	 => null,
		estimate_percent => 100,
		block_sample 	 => true,
		method_opt 	 => 'for all columns size 1',
		degree		 => null,
		granularity      => 'default',
		cascade		 => true
	);
end;
/ 
 
select avg_row_len from user_tables where table_name = 'LOB_LOAD';

The results:

9.2.0.8: 15
10.2.0.3: 3118

So if you're running 9.2.0.8 (or thereabouts) I'd guess that your comment about LOBs was very important - all the excess space could be "disappearing" because dbms_stats() isn't seeing the length of in-line lobs.

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
http://www.jlcomp.demon.co.uk