Home » RDBMS Server » Performance Tuning » Finding HWM (oracle9i)
Finding HWM [message #321589] Tue, 20 May 2008 15:24 Go to next message
shrinika
Messages: 266
Registered: April 2008
Senior Member
Hello,

I have couple of question.

1) Can any one please ensure that the below query will tell us the HWM?

select sum(blocks) - sum(empty_blocks) from user_tables
WHERE TABLE_NAME='EMP1';


2) Can any one please tell me why the below two query gives differnet figure?? My understanding here is, sum(blocks) should be same for user_segments & user_tables..

SQL> select sum(blocks) from dba_tables where table_name='EMP1';

SUM(BLOCKS)
-----------
5380

SQL> select sum(blocks) from dba_segments where segment_name='EMP1';

SUM(BLOCKS)
-----------
5504

SQL>
Re: Finding HWM [message #321590 is a reply to message #321589] Tue, 20 May 2008 15:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1) No it gives you information based on statistics
2) dba_tables contains statistics information, dba_segments actual information

Regards
Michel

[Updated on: Tue, 20 May 2008 15:27]

Report message to a moderator

Re: Finding HWM [message #321592 is a reply to message #321590] Tue, 20 May 2008 15:49 Go to previous messageGo to next message
shrinika
Messages: 266
Registered: April 2008
Senior Member
Michel, I would like to thank for your immediate reply. I am good with second question answer. But regarding first question, i analyzed the table before i ran the query.

In the below scenario, Can i say that, this is the right query to find the HWM? Please clarify this... Thanks again

SQL> EXECUTE DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'SCOTT',TABNAME => 'EMP1',ESTIMATE_PERCENT =>
10,METHOD_OPT => 'FOR ALL COLUMNS SIZE 1',CASCADE => TRUE)

PL/SQL procedure successfully completed.

SQL> select sum(blocks) - sum(empty_blocks) from user_tables
2 WHERE TABLE_NAME='EMP1';

SUM(BLOCKS)-SUM(EMPTY_BLOCKS)
-----------------------------
21523

SQL>

Re: Finding HWM [message #321704 is a reply to message #321592] Wed, 21 May 2008 01:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, I think so but it is better to use dbms_space.unused_space procedure, it is made for this.
Try it with your table and compare the results.

Regards
Michel
Re: Finding HWM [message #321818 is a reply to message #321704] Wed, 21 May 2008 07:06 Go to previous message
shrinika
Messages: 266
Registered: April 2008
Senior Member
Thank you Michel
Previous Topic: Query is taking exceptionally large amount of time
Next Topic: Oracle partition doesn't work for select which scans entire table
Goto Forum:
  


Current Time: Wed Dec 07 04:43:25 CST 2016

Total time taken to generate the page: 0.10682 seconds