Home » RDBMS Server » Performance Tuning » HWM in table
HWM in table [message #234373] Tue, 01 May 2007 04:51 Go to next message
shahnazurs
Messages: 240
Registered: June 2005
Location: India
Senior Member
hi,
I had a table which holds 1 million records, recently i deleted most of the records. Now it is havibng only 10k records.
But still when i query this table i am getting same delay

Thanks.
Re: HWM in table [message #234375 is a reply to message #234373] Tue, 01 May 2007 05:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Because you're doing full table scan that is scan all blocks till HWM and delete does not change HWM and so the number of blocks you scan.

Regards
Michel
Re: HWM in table [message #234379 is a reply to message #234375] Tue, 01 May 2007 06:07 Go to previous messageGo to next message
shahnazurs
Messages: 240
Registered: June 2005
Location: India
Senior Member
Michel,
Then what is the work around for this?

Thanks.
Re: HWM in table [message #234380 is a reply to message #234379] Tue, 01 May 2007 06:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There are workarounds.
They depend on your Oracle version and if use ASSM or not but in short it is rebuilding the table/moving the rows.

Regards
Michel
Re: HWM in table [message #234388 is a reply to message #234380] Tue, 01 May 2007 06:37 Go to previous messageGo to next message
shahnazurs
Messages: 240
Registered: June 2005
Location: India
Senior Member
Hi Michel,

This tablespace is in automatic segment space management only,but still the same time.
My oracle vesion is 9.2.0.6.0

Thanks.
Re: HWM in table [message #234389 is a reply to message #234388] Tue, 01 May 2007 06:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
- alter table move
- create table as select / truncate / insert back
- export / truncate / import

These are the options you have. Only the first can be done online.

Regards
Michel
Re: HWM in table [message #234394 is a reply to message #234389] Tue, 01 May 2007 07:11 Go to previous messageGo to next message
shahnazurs
Messages: 240
Registered: June 2005
Location: India
Senior Member
Thanks Michel for your information.

alter table move??

Do we need to move this table to another tablespace?

Thanks.
Re: HWM in table [message #234397 is a reply to message #234394] Tue, 01 May 2007 07:41 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Move to the same tablespae as_it_was_before and collect stats again.
And, SEARCH before posting.
THis topic is handled numerous times ( An i am getting tired of parroting this in recent times Smile
Re: HWM in table [message #234844 is a reply to message #234397] Thu, 03 May 2007 05:28 Go to previous messageGo to next message
sriram717
Messages: 48
Registered: February 2007
Location: UNITED KINGDOM
Member
Even Better in 10g with Online shrink.
Re: HWM in table [message #234857 is a reply to message #234844] Thu, 03 May 2007 06:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But I asked OP version precisely to prevent from posting useless and irrelevant "solution".

Regards
Michel
Re: HWM in table [message #234860 is a reply to message #234857] Thu, 03 May 2007 06:05 Go to previous messageGo to next message
sriram717
Messages: 48
Registered: February 2007
Location: UNITED KINGDOM
Member
This was not a solution , this was just an input on 10g feature.

I said 10g even better
Re: HWM in table [message #235065 is a reply to message #234860] Thu, 03 May 2007 23:46 Go to previous messageGo to next message
shahnazurs
Messages: 240
Registered: June 2005
Location: India
Senior Member
Thanks all for the suggestion.

Hi Mahesh,
Thanks for your advice. We could able to search the forum but the exact answer we are not getting that is why we are posting.

Thanks.
Re: HWM in table [message #241389 is a reply to message #235065] Tue, 29 May 2007 07:42 Go to previous messageGo to next message
shahnazurs
Messages: 240
Registered: June 2005
Location: India
Senior Member
Hi,
Do we any stratergy to find out the table which has unused block till HWM?

Thanks.
Re: HWM in table [message #241432 is a reply to message #241389] Tue, 29 May 2007 09:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at dbms_space package.

Regards
Michel
Re: HWM in table [message #241502 is a reply to message #234373] Tue, 29 May 2007 16:09 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Check your application - it may keep rowids of the table as data in some other table(s) ( ERP does it a lot, but it's not a "usual" application behavior).
In that case - you can NOT reorg it, because rowids will change.

HTH.
Michael
Re: HWM in table [message #241662 is a reply to message #234373] Wed, 30 May 2007 06:12 Go to previous messageGo to next message
shahnazurs
Messages: 240
Registered: June 2005
Location: India
Senior Member
Thanks Michel,

I just ran the following script

DECLARE
total_blocks number;
total_bytes number;
unused_blocks number;
unused_bytes number;
last_used_extent_file_id number;
last_used_extent_block_id number;
last_used_block number;
begin
DBMS_SPACE.UNUSED_SPACE('SHAHNAZ', 'XYZ', 'TABLE', total_blocks => total_blocks ,
total_bytes => total_bytes,
unused_blocks =>unused_blocks,
unused_bytes => unused_bytes,
last_used_extent_file_id => last_used_extent_file_id,
last_used_extent_block_id =>last_used_extent_block_id ,
last_used_block => last_used_block );
DBMS_OUTPUT.PUT_LINE ( 'total_blocks '|| total_blocks);
DBMS_OUTPUT.PUT_LINE ( 'total_bytes '|| total_bytes);
DBMS_OUTPUT.PUT_LINE ( 'unused_block '|| unused_blocks);
DBMS_OUTPUT.PUT_LINE ( 'unused_bytes '|| unused_bytes);
end;



BUT it is giving like

total_blocks 2578
total_bytes 45298483
unused_block 0
unused_bytes 0

I know this table is taking too much time to query eventhough it is having minimum records!!!


Thanks.
Re: HWM in table [message #241685 is a reply to message #241662] Wed, 30 May 2007 07:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This shows you the blocks that never been used (none here).
You may have blocks that have been used but are empty now.

Use dbms_space.space_usage.

Regards
Michel

[Updated on: Wed, 30 May 2007 07:11]

Report message to a moderator

Re: HWM in table [message #241918 is a reply to message #241685] Thu, 31 May 2007 01:00 Go to previous messageGo to next message
shahnazurs
Messages: 240
Registered: June 2005
Location: India
Senior Member
Thanks

when i see the value of full_blocks it shows less blocks compared to dba_segments block column. Is the procedure dbms_space.space_usage is checking till last analyzed?

Thanks.
Re: HWM in table [message #241921 is a reply to message #241918] Thu, 31 May 2007 01:06 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You also have partially used blocks and empty blocks.
dbms_space.space_usage checks what is current.

Regards
Michel
Previous Topic: Need help
Next Topic: automatic statspack
Goto Forum:
  


Current Time: Mon Dec 05 23:56:27 CST 2016

Total time taken to generate the page: 0.07318 seconds