OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » HWM in table

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

HWM in table [message #234373]

Tue, 01 May 2007 04:51

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.

Report message to a moderator

Re: HWM in table [message #234375 is a reply to message #234373]

Tue, 01 May 2007 05:05

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: HWM in table [message #234379 is a reply to message #234375]

Tue, 01 May 2007 06:07

shahnazurs
Messages: 240
Registered: June 2005
Location: India

Senior Member

Michel,
Then what is the work around for this?

Thanks.

Report message to a moderator

Re: HWM in table [message #234380 is a reply to message #234379]

Tue, 01 May 2007 06:14

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: HWM in table [message #234388 is a reply to message #234380]

Tue, 01 May 2007 06:37

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.

Report message to a moderator

Re: HWM in table [message #234389 is a reply to message #234388]

Tue, 01 May 2007 06:48

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: HWM in table [message #234394 is a reply to message #234389]

Tue, 01 May 2007 07:11

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.

Report message to a moderator

Re: HWM in table [message #234397 is a reply to message #234394]

Tue, 01 May 2007 07:41

Mahesh Rajendran
Messages: 10708
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

Report message to a moderator

Re: HWM in table [message #234844 is a reply to message #234397]

Thu, 03 May 2007 05:28

sriram717
Messages: 48
Registered: February 2007
Location: UNITED KINGDOM

Member

Even Better in 10g with Online shrink.

Report message to a moderator

Re: HWM in table [message #234857 is a reply to message #234844]

Thu, 03 May 2007 06:03

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

But I asked OP version precisely to prevent from posting useless and irrelevant "solution".

Regards
Michel

Report message to a moderator

Re: HWM in table [message #234860 is a reply to message #234857]

Thu, 03 May 2007 06:05

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

Report message to a moderator

Re: HWM in table [message #235065 is a reply to message #234860]

Thu, 03 May 2007 23:46

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.

Report message to a moderator

Re: HWM in table [message #241389 is a reply to message #235065]

Tue, 29 May 2007 07:42

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.

Report message to a moderator

Re: HWM in table [message #241432 is a reply to message #241389]

Tue, 29 May 2007 09:42

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Have a look at dbms_space package.

Regards
Michel

Report message to a moderator

Re: HWM in table [message #241502 is a reply to message #234373]

Tue, 29 May 2007 16:09

michael_bialik
Messages: 621
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

Report message to a moderator

Re: HWM in table [message #241662 is a reply to message #234373]

Wed, 30 May 2007 06:12

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.

Report message to a moderator

Re: HWM in table [message #241685 is a reply to message #241662]

Wed, 30 May 2007 07:11

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

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

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.

Report message to a moderator

Re: HWM in table [message #241921 is a reply to message #241918]

Thu, 31 May 2007 01:06

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You also have partially used blocks and empty blocks.
dbms_space.space_usage checks what is current.

Regards
Michel

Report message to a moderator

Previous Topic:	Need help
Next Topic:	automatic statspack

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 23 08:16:40 CST 2024