Oracle 高水位线查询（show_space)

偏执的太偏执、 2022-06-09 05:42 667阅读 0赞

我们知道这样一种情况，在oracle中，假设A表原先有1000W行数据，后来删除掉了999W行，只剩下1W行数据的时候，全表扫描A表的时间没有什么变化（删除前后）。这就是oracle里面的高水位线引起的。通俗地讲，假设我们要装1000L水，需要1000个桶，后来我倒掉了999个桶里面的水，但是我没把桶回收。这时，我去找水的时候，仍然要一个一个桶地去寻找。下面这个例子就是为了描述这样一种现象，并介绍怎么解决（也就是把桶回收）

1.show\_space 代码

CREATE OR REPLACE PROCEDURE show\_space  
(                                        
 p\_segname\_1 IN VARCHAR2,                                        
 p\_type\_1 IN VARCHAR2 DEFAULT 'TABLE', --t = TABLE; tp=TABLE PARTITION; tsp=TABLE SUBPARTITION                                       
 p\_space IN VARCHAR2 DEFAULT 'AUTO', --manual; auto                                        
 p\_analyzed IN VARCHAR2 DEFAULT 'Y', --  y  n                                        
 p\_partition\_1 IN VARCHAR2 DEFAULT NULL, --partition name                                        
 p\_owner\_1 IN VARCHAR2 DEFAULT USER -- users  
                                       )  
\-- command ;sqlplus    
\-- set serveroutput on  
  -- exec show\_space('SHARK\_TRADERECORD','T','AUTO','Y',NULL);  表  
  -- exec show\_space('SHARK\_TRADERECORD\_HISTORY','TP','AUTO','Y','SYS\_P30','SHARK');     分区表  
  -- exec show\_space('SHARK\_TRADERECORD\_PARTITION','TABLE SUBPARTITION','AUTO','Y','SYS\_SUBP1442');      子分区表  
   
AUTHID CURRENT\_USER  
 AS  
  p\_segname VARCHAR2(100);  
  p\_type VARCHAR2(30);  
  p\_owner VARCHAR2(30);  
  p\_partition VARCHAR2(50);  
  l\_unformatted\_blocks NUMBER;  
  l\_unformatted\_bytes NUMBER;  
  l\_fs1\_blocks NUMBER;  
  l\_fs1\_bytes NUMBER;  
  l\_fs2\_blocks NUMBER;  
  l\_fs2\_bytes NUMBER;  
  l\_fs3\_blocks NUMBER;  
  l\_fs3\_bytes NUMBER;  
  l\_fs4\_blocks NUMBER;  
  l\_fs4\_bytes NUMBER;  
  l\_full\_blocks NUMBER;  
  l\_full\_bytes NUMBER;  
  l\_free\_blks NUMBER;  
  l\_total\_blocks NUMBER;  
  l\_total\_bytes NUMBER;  
  l\_unused\_blocks NUMBER;  
  l\_unused\_bytes NUMBER;  
  l\_LastUsedExtFileId NUMBER;  
  l\_LastUsedExtBlockId NUMBER;  
  l\_LAST\_USED\_BLOCK NUMBER;  
   
  PROCEDURE p(p\_label IN VARCHAR2, p\_num IN NUMBER)   IS  
  BEGIN  
    DBMS\_OUTPUT.put\_line(RPAD(p\_label, 40, '.') || p\_num);  
  END;  
    
   
BEGIN  
   
  p\_segname := UPPER(p\_segname\_1);  
  p\_owner := UPPER(p\_owner\_1);  
  p\_type := p\_type\_1;  
  p\_partition := UPPER(p\_partition\_1);  
   
  IF (p\_type\_1 = 't' OR p\_type\_1 = 'T')   THEN  
    p\_type := 'TABLE';  
    GOTO exec;  
  END IF;  
   
  IF (p\_type\_1 = 'tp' OR p\_type\_1 = 'TP')   THEN  
    p\_type := 'TABLE PARTITION';  
    GOTO exec;  
  END IF;  
   
  IF (p\_type\_1 = 'tsp' or p\_type\_1 = 'TSP') THEN  
    p\_type := 'TABLE SUBPARTITION';  
    GOTO exec;  
  END IF;  
   
  IF (p\_type\_1 = 'i' OR p\_type\_1 = 'I')   THEN  
    p\_type := 'INDEX';  
    GOTO exec;  
  END IF;  
   
  IF (p\_type\_1 = 'ip' OR p\_type\_1 = 'IP')   THEN  
    p\_type := 'INDEX PARTITION';  
    GOTO exec;  
  END IF;  
   
  IF (p\_type\_1 = 'isp' OR p\_type\_1 = 'ISP')   THEN  
    p\_type := 'INDEX SUBPARTITION';  
    GOTO exec;  
  END IF;  
   
  IF (p\_type\_1 = 'c' OR p\_type\_1 = 'C')   THEN  
    p\_type := 'CLUSTER';  
    GOTO exec;  
  END IF;  
   
  <<exec>>  
   
  DBMS\_SPACE.UNUSED\_SPACE(                           
                          segment\_owner => p\_owner,                           
                          segment\_name => p\_segname,                           
                          segment\_type => p\_type,                           
                          partition\_name => p\_partition,                           
                          total\_blocks => l\_total\_blocks,                           
                          total\_bytes => l\_total\_bytes,                           
                          unused\_blocks => l\_unused\_blocks,                           
                          unused\_bytes => l\_unused\_bytes,                           
                          LAST\_USED\_EXTENT\_FILE\_ID => l\_LastUsedExtFileId,                           
                          LAST\_USED\_EXTENT\_BLOCK\_ID => l\_LastUsedExtBlockId,                           
                          LAST\_USED\_BLOCK => l\_LAST\_USED\_BLOCK  
                        );  
   
  IF p\_space = 'MANUAL' OR (p\_space <> 'auto' AND p\_space <> 'AUTO')   THEN  
    DBMS\_SPACE.FREE\_BLOCKS(segment\_owner => p\_owner,                            
                           segment\_name => p\_segname,                            
                           segment\_type => p\_type,                            
                           partition\_name => p\_partition,                            
                           freelist\_group\_id => 0,                            
                           free\_blks => l\_free\_blks  
                           );  
    
    p('Free Blocks', l\_free\_blks);   
  END IF;  
   
  p('Total Blocks', l\_total\_blocks);  
  p('Total Bytes', l\_total\_bytes);  
  p('Unused Blocks', l\_unused\_blocks);  
  p('Unused Bytes', l\_unused\_bytes);  
  p('Last Used Ext FileId', l\_LastUsedExtFileId);  
  p('Last Used Ext BlockId', l\_LastUsedExtBlockId);  
  p('Last Used Block', l\_LAST\_USED\_BLOCK);  
  p('HWM AT BlockID', l\_LastUsedExtFileId + l\_LAST\_USED\_BLOCK - 1);  
  p('HWM AT BlockS', l\_total\_blocks - l\_unused\_blocks);  
   
  /\*IF the segment is analyzed \*/  
   
  IF p\_analyzed = 'Y'   THEN  
    DBMS\_SPACE.SPACE\_USAGE(segment\_owner => p\_owner,                            
                           segment\_name => p\_segname,                            
                           segment\_type => p\_type,                            
                           partition\_name => p\_partition,                            
                           unformatted\_blocks => l\_unformatted\_blocks,                            
                           unformatted\_bytes => l\_unformatted\_bytes,                            
                           fs1\_blocks => l\_fs1\_blocks,                            
                           fs1\_bytes => l\_fs1\_bytes,                            
                           fs2\_blocks => l\_fs2\_blocks,                            
                           fs2\_bytes => l\_fs2\_bytes,                            
                           fs3\_blocks => l\_fs3\_blocks,                            
                           fs3\_bytes => l\_fs3\_bytes,                            
                           fs4\_blocks => l\_fs4\_blocks,                            
                           fs4\_bytes => l\_fs4\_bytes,                            
                           full\_blocks => l\_full\_blocks,                            
                           full\_bytes => l\_full\_bytes);  
    
    DBMS\_OUTPUT.put\_line(RPAD('', 50, '\*'));   
    DBMS\_OUTPUT.put\_line('Thesegment is analyzed');  
    
    p('0% -- 25% free spaceblocks', l\_fs1\_blocks);   
    p('0% -- 25% free spacebytes', l\_fs1\_bytes);   
    p('25% -- 50% free spaceblocks', l\_fs2\_blocks);   
    p('25% -- 50% free spacebytes', l\_fs2\_bytes);   
    p('50% -- 75% free spaceblocks', l\_fs3\_blocks);   
    p('50% -- 75% free spacebytes', l\_fs3\_bytes);   
    p('75% -- 100% free spaceblocks', l\_fs4\_blocks);   
    p('75% -- 100% free spacebytes', l\_fs4\_bytes);   
    p('Unused Blocks', l\_unformatted\_blocks);   
    p('Unused Bytes', l\_unformatted\_bytes);   
    p('Total Blocks', l\_full\_blocks);   
    p('Total bytes', l\_full\_bytes);  
    
  END IF;  
   
END;

2.执行

SQL> set serveroutput on  
SQL> exec show\_space('SYSLOG');  
Total Blocks............................20480  
Total Bytes.............................167772160  
Unused Blocks...........................0  
Unused Bytes............................0  
Last Used Ext FileId....................6  
Last Used Ext BlockId...................116096  
Last Used Block.........................1024  
HWM AT BlockID..........................1029  
HWM AT BlockS...........................20480  
Thesegment is analyzed  
0% -- 25% free spaceblocks..............2  
0% -- 25% free spacebytes...............16384  
25% -- 50% free spaceblocks.............2  
25% -- 50% free spacebytes..............16384  
50% -- 75% free spaceblocks.............3  
50% -- 75% free spacebytes..............24576  
75% -- 100% free spaceblocks............44  
75% -- 100% free spacebytes.............360448  
Unused Blocks...........................83  
Unused Bytes............................679936  
Total Blocks............................20163  
Total bytes.............................165175296

PL/SQL procedure successfully completed.

SQL>