HWM question after delete
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: HWM question after delete

  1. #1
    Join Date
    Oct 2003
    Posts
    312

    HWM question after delete

    Hi all,

    Please see the folllowing test:

    Code:
    SQL> set linesize 10000
    SQL> spool c:\temp\hwm.txt
    SQL> set timing on
    SQL> select count(*) from TEST;
    
      COUNT(*)
    ----------
       2798098
    
    Elapsed: 00:08:24.01
    
    SQL> SELECT tablespace_name "Tablespace Name",  
      2  bytes/1048576 "Size(M)" 
      3  FROM dba_segments
      4  where owner='TEST';
    
    Tablespace Name                                                                               Size(M
    ------------------------------------------------------------------------------------------ ---------
    TEST_DATA                                                                                       145.5
    TEST_DATA                                                                                        48.5
    TEST_DATA                                                                                        56.5
    
    Elapsed: 00:00:01.04
    
    SQL> delete from TEST;
    
    2798098 rows deleted.
    
    SQL> ALTER TABLE TEST.TEST MOVE 
      2  /
    
    Table altered.
    
    Elapsed: 00:00:13.03
    SQL> ALTER INDEX TEST.TEST REBUILD LOGGING NOREVERSE TABLESPACE TEST_DATA
      2  /
    
    Index altered.
    
    
    Elapsed: 00:00:00.02
    SQL> ALTER INDEX TEST.TEST_PK REBUILD LOGGING NOREVERSE TABLESPACE TEST_DATA
      2  /
    
    Index altered.
    
    Elapsed: 00:00:00.04
    SQL> SELECT tablespace_name "Tablespace Name",  
      2  bytes/1048576 "Size(M)" 
      3  FROM dba_segments
      4  where owner='TEST';
    
    Tablespace Name                                                                               Size(M
    ------------------------------------------------------------------------------------------ ---------
    TEST_DATA                                                                                       100.5
    TEST_DATA                                                                                        48.5
    TEST_DATA                                                                                        56.5
    
    Elapsed: 00:00:00.06
    SQL> SPOOL OFF
    SQL>
    Why HWM is not shrink close to zero after I move tables and rebuild index???? I intentionally move it on the same tablespace, I also tried to move to different tablespace but it doens't help.

    could someone please tell me why???

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Since we're taking about storage, you moved your table without changing any storage parameters.

    ALTER TABLE xxx MOVE STORAGE(minextents 1);
    Last edited by marist89; 01-10-2005 at 11:23 AM.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Oct 2003
    Posts
    312
    Thanks Jeff,

    So if I don't have (minextents 1), will HWM shrink??? All I care about to shrink HWM to improve performance.


    also, please see the below test:


    Code:
    SQL> SPOOL OFF
    SQL> ALTER TABLE TEST.test MOVE (minextents 1);
    ALTER TABLE TEST.test MOVE (minextents 1)
                                                    *
    ERROR at line 1:
    ORA-14133: ALTER TABLE MOVE cannot be combined with other operations
    
    
    Elapsed: 00:00:00.01
    SQL>
    thanks again
    Last edited by learning_bee; 01-10-2005 at 11:12 AM.

  4. #4
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    ?
    ALTER TABLE xxx MOVE STORAGE(minextents 1);

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by learning_bee
    So if I don't have (minextents 1), will HWM shrink??? All I care about to shrink HWM to improve performance.
    Does HWM have anything to do with the storage allocated to that segment?
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  6. #6
    Join Date
    Oct 2003
    Posts
    312
    Code:
    SQL> ALTER TABLE TEST.TEST move storage (minextents 1);
    
    Table altered.
    
    Elapsed: 00:00:00.04
    SQL> SELECT tablespace_name "Tablespace Name",  
      2  bytes/1048576 "Size(M)" 
      3  FROM dba_segments
      4  where owner='TEST';
    
    Tablespace Name                                                                               Size(M
    ------------------------------------------------------------------------------------------ ---------
    TEST_DATA                                                                                         100.5
    TEST_DATA                                                                                        48.5
    TEST_DATA                                                                                        56.5
    
    Elapsed: 00:00:00.01
    SQL> analyze table TEST compute statistics;
    
    Table analyzed.
    
    Elapsed: 00:00:00.00
    SQL> SELECT tablespace_name "Tablespace Name",  
      2  bytes/1048576 "Size(M)" 
      3  FROM dba_segments
      4  where owner='TEST';
    
    Tablespace Name                                                                               Size(M
    ------------------------------------------------------------------------------------------ ---------
    TEST_DATA                                                                                         100.5
    TEST_DATA                                                                                        48.5
    TEST_DATA                                                                                        56.5
    
    Elapsed: 00:00:00.01
    SQL>
    nothing is changing.

  7. #7
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    See below:

    PHP Code:
        +1  09:54:27 SQLdrop table t1;
        +
    2
        
    +3  Table dropped.
        +
    4
        
    +5  Elapsed00:00:01.92
        
    +6  09:54:33 SQLcreate table t1 as select from dba_objects ;
        +
    7
        
    +8  Table created.
        +
    9
       
    +10  Elapsed00:00:01.39
       
    +11  09:54:49 SQLinsert into t1 select from t1 ;
       +
    12
       
    +13  23360 rows created.
       +
    14
       
    +15  Elapsed00:00:00.90
       
    +16  09:55:08 SQL> /
       +
    17
       
    +18  46720 rows created.
       +
    19
       
    +20  Elapsed00:00:00.99
       
    +21  09:55:10 SQL> /
       +
    22
       
    +23  93440 rows created.
       +
    24
       
    +25  Elapsed00:00:00.97
       
    +26  09:55:11 SQL> /
       +
    27
       
    +28  186880 rows created.
       +
    29
       
    +30  Elapsed00:00:05.14
       
    +31  09:55:17 SQLcommit;
       +
    32
       
    +33  Commit complete.
       +
    34
       
    +35  Elapsed00:00:00.13
       
    +36  09:55:20 SQLcol segment_name format a5
       
    +37  09:55:37 SQLselect segment_nameblocksbytes
       
    +38                 from user_segments where segment_name='T1' ;
       +
    39
       
    +40  SEGME     BLOCKS      BYTES
       
    +41  ----- ---------- ----------
       +
    42  T1          7205   59023360
       
    +43
       
    +44  Elapsed00:00:00.48
       
    +45
       
    +46  09:56:38 SQLselect tablespace_name ,
       +
    47  09:56:47   2  extentssegment_nameblocksbytes
       
    +48                from user_segments where segment_name='T1' ;
       +
    49
       
    +50  TABLESPACE_NAME                   EXTENTS SEGME     BLOCKS      BYTES
       
    +51  ------------------------------ ---------- ----- ---------- ----------
       +
    52  USERS                                  17 T1          7205   59023360
       
    +53
       
    +54  Elapsed00:00:00.09
       
    +55  09:56:54 SQLcommit;
       +
    56
       
    +57  Commit complete.
       +
    58
       
    +59  Elapsed00:00:00.00
       
    +60  09:57:01 SQLdelete from t1 ;
       +
    61
       
    +62  373760 rows deleted.
       +
    63
       
    +64  Elapsed00:00:20.10
       
    +65  09:57:29 SQLcommit;
       +
    66
       
    +67  Commit complete.
       +
    68
       
    +69  Elapsed00:00:00.01
       
    +70  09:57:32 SQL>  select tablespace_name ,
       +
    71  09:57:38   2  extentssegment_nameblocksbytes
       
    +72                 from user_segments where segment_name='T1' ;
       +
    73
       
    +74  TABLESPACE_NAME                   EXTENTS SEGME     BLOCKS      BYTES
       
    +75  ------------------------------ ---------- ----- ---------- ----------
       +
    76  USERS                                  17 T1          7205   59023360
       
    +77
       
    +78  Elapsed00:00:00.10
       
    +79  09:57:40 SQLalter table t1 move ;
       +
    80
       
    +81  Table altered.
       +
    82
       
    +83  Elapsed00:00:01.53
       
    +84  09:57:49 SQL>  select tablespace_name ,
       +
    85  09:57:51   2   extentssegment_nameblocksbytes
       
    +86                  from user_segments where segment_name='T1' ;
       +
    87
       
    +88  TABLESPACE_NAME                   EXTENTS SEGME     BLOCKS      BYTES
       
    +89  ------------------------------ ---------- ----- ---------- ----------
       +
    90  USERS                                   1 T1             5      40960
       
    +91
       
    +92  Elapsed00:00:00.44
       
    +93  09:57:54 SQLspool off 
    Tamil

  8. #8
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    How many extents?
    DMT or LMT?
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  9. #9
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by learning_bee
    Why HWM is not shrink close to zero after I move tables and rebuild index???? I intentionally move it on the same tablespace, I also tried to move to different tablespace but it doens't help.
    You are observing the space alocated by the table segment, your query does not tell you anything about HWM. HWM and space alocated to the table are two totaly different things.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width