oracle 9i 性能调优4

十三、Using Oracle Blocks Efficiently           --有效使用oracle块

1、Avoiding Dynamic Allocation

SQL> exec dbms_stats.gather_table_stats('U1','T');

PL/SQL procedure successfully completed.

SQL> select blocks,empty_blocks from user_tables where table_name ='T';

    BLOCKS EMPTY_BLOCKS
---------- ------------
         0            0

SQL> analyze table t compute statistics;

Table analyzed.

SQL> select blocks,empty_blocks from user_tables where table_name ='T';

    BLOCKS EMPTY_BLOCKS
---------- ------------
         0         1280

SQL>select blocks,empty_blocks from dba_tables where owner='U1' and table_name='T';

-- dba_segments

-- dba_extents

select bytes,blocks,extents from dba_segments where owner='U1' AND segment_name='T';

-- dbms_space包

-- dbms_space.unused_space

2、Recovering Space

Below the high-water mark:

* Use the Export and Import utilities 

* Use the alter table employees move command to move the table

Above the high-water mark, use the 

alter table employees deallocate unused;

3、Guidelines

PCTFREE

--Default is 10

-- Zero if no UPDATE activity

-- PCTFREE = 100 * UPD/(Average row length)

PCTUSED

--Default is 40

-- set if rows are deleted

-- PCTUSED = 100 - PCTFREE - 100 * ROWS * (Average row length)/ Block size

4、Use the analyze command to detect migration and chaining

analyze table hodba.items_stat_mon compute statistics;

 select owner, num_rows,chain_cnt from dba_tables
 where table_name='ITEMS_STAT_MON';

5、Selecting Migrated Rows

analyze table hr.t list chained rows;

select owner_name,table_name,head_rowid

from chained_rows

where table_name='T';

6、Monitoring Index Space


execute dbms_stats.gather_index_stats('STDBA','I_GRP_SUB');

select name,(del_lf_rows_len/lf_rows_len) * 100 as wastage from index_stats;

Rebuild indexes with wastage greater than 20%
alter index stdba.i_grp_sub rebuild;

alter index stdba.i_grp_sub coalesce;

7、Cluster Types


1) Index cluster


SQL> create cluster mycluster(deptno number(2)) size 1024;

SQL> create index myc_idx on cluster mycluster;


SQL> create table dept
  2  (deptid number(2) primary key,
  3   dname varchar2(20),
  4   loc   varchar2(30)
  5   )
  6  cluster mycluster(deptid);


Table created.


SQL> create table emp
  2  (empid number primary key,
  3   ename varchar2(20),
  4   sal number,
  5   deptno number(2) references dept(deptid)
  6  )
  7  cluster mycluster(deptno)
  8  /


Table created.


SQL>


2)Hash cluster


SQL> create cluster hc(hk number) hashkeys 1000 size 8192;


Cluster created.




SQL> desc show_space
PROCEDURE show_space
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_SEGNAME                      VARCHAR2                IN
 P_OWNER                        VARCHAR2                IN     DEFAULT
 P_TYPE                         VARCHAR2                IN     DEFAULT
 P_PARTITION                    VARCHAR2                IN     DEFAULT


SQL> set serveroutput on
SQL> exec show_space('HC','STDBA','CLUSTER');
Unformatted Blocks .....................               0
FS1 Blocks (0-25) ......................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0
Full Blocks ............................           1,009
Total Blocks............................           1,152
Total Bytes.............................       9,437,184
Total MBytes............................               9
Unused Blocks...........................             117
Unused Bytes............................         958,464
Last Used Ext FileId....................               5
Last Used Ext BlockId...................         190,225
Last Used Block.........................              11


PL/SQL procedure successfully completed.


SQL>

8、Partitioning Methods
Range
Hash
List
Composite