오라클 통계생성

1. 해당 스키마에 해당하는 테이블과 테이블 스페이스 조회

SQL> select owner, table_name, tablespace_name from dba_tables where owner = 'SCOTT';

 

OWNER             TABLE_NAME       TABLESPACE_NAME

-------------------- -------------------- --------------------

SCOTT             DEPT          USERS

SCOTT             EMP          USERS

SCOTT             BONUS          USERS

SCOTT             SALGRADE          USERS

 

 

2. 해당 스키마에 해당하는 테이블의 통계정보 조회

SQL> select table_name, num_rows, chain_cnt, blocks, empty_blocks as empty, avg_space,

avg_row_len from dba_tables where owner = 'SCOTT';

 

select table_name, num_rows, chain_cnt, blocks, empty_blocks as empty,

      avg_space,

      avg_row_len,

      to_char(last_analyzed,'YYYY-MM-DD') as lastDate

  from dba_tables a

 where owner = 'CVPL';

 

 

TABLE_NAME           NUM_ROWS  CHAIN_CNT     BLOCKS       EMPTY  AVG_SPACE AVG_ROW_LEN

-------------------- ---------- ---------- ---------- ---------- ---------- -----------

DEPT

EMP

BONUS

SALGRADE

* 통계정보를 갱신하지 않으면 나오지 않음

* num_rows : 행수, chain_cnt : chaining 된 행수, blocks : 사용중인 블록수, empty_blocks : 미사용블록수,  avg_space : 블록당 평균사용수, avg_row_len : 저장된 행의 평균길이

 

 

3. 해당 스키마에 해당하는 인덱스의 통계정보 조회

SQL> select table_name, index_name, status, num_rows, leaf_blocks, blevel

from dba_indexes where owner = 'SCOTT';

 

TABLE_NAME         INDEX_NAME       STATUS             NUM_ROWS LEAF_BLOCKS     BLEVEL

-------------------- -------------------- ------------------------ ---------- ----------- ----------

DEPT             PK_DEPT          VALID

EMP             PK_EMP          VALID

 

 

4. 테이블의 통계정보 갱신

SQL> exec dbms_stats.gather_table_stats('SCOTT','DEPT');

 

PL/SQL procedure successfully completed.

 

SQL> select table_name, num_rows, chain_cnt, blocks, empty_blocks as empty,

avg_space, avg_row_len from dba_tables where owner = 'SCOTT';

 

TABLE_NAME           NUM_ROWS  CHAIN_CNT     BLOCKS       EMPTY  AVG_SPACE AVG_ROW_LEN

-------------------- ---------- ---------- ---------- ---------- ---------- -----------

DEPT                  4      0        5           0      0         20

EMP                 14      0        5           0      0         37

BONUS                  0      0        0           0      0          0

SALGRADE              5      0        5           0      0         10

 

SQL> select table_name, index_name, status, num_rows, leaf_blocks, blevel

from dba_indexes where owner = 'SCOTT';

 

TABLE_NAME         INDEX_NAME       STATUS             NUM_ROWS LEAF_BLOCKS     BLEVEL

-------------------- -------------------- ------------------------ ---------- ----------- ----------

DEPT             PK_DEPT          VALID                 4        1       0

EMP             PK_EMP          VALID                14        1       0

 

5. 스키마안의 모든 세그먼트에 대한 통계정보 갱신

SQL> exec dbms_stats.gather_schema_stats('SCOTT');

 

PL/SQL procedure successfully completed.

 

SQL> select table_name, num_rows, chain_cnt, blocks, empty_blocks as empty,

avg_space, avg_row_len from dba_tables where owner = 'SCOTT';

 

TABLE_NAME           NUM_ROWS  CHAIN_CNT     BLOCKS       EMPTY  AVG_SPACE AVG_ROW_LEN

-------------------- ---------- ---------- ---------- ---------- ---------- -----------

DEPT                  4      0        5           0      0         20

EMP                 14      0        5           0      0         37

BONUS                  0      0        0           0      0          0

SALGRADE              5      0        5           0      0         10

 

6. dbms_stats 패키지로 갱신되지 않는 테이블 통계 정보 갱신

SQL> analyze table scott.dept compute statistics;

 

Table analyzed.

 

SQL> analyze table scott.bonus compute statistics;

 

Table analyzed.

 

SQL> analyze table scott.SALGRADE compute statistics;

 

Table analyzed.

 

SQL> select table_name, num_rows, chain_cnt, blocks, empty_blocks as empty, avg_space, avg_row_len from dba_tables where owner = 'SCOTT';

 

TABLE_NAME           NUM_ROWS  CHAIN_CNT     BLOCKS       EMPTY  AVG_SPACE AVG_ROW_LEN

-------------------- ---------- ---------- ---------- ---------- ---------- -----------

DEPT                  4      0        5           3       8053         23

EMP                 14      0        5           3       7954         40

BONUS                  0      0        0           8      0          0

SALGRADE              5      0        5           3       8058         13

 

SQL> select table_name, index_name, status, num_rows, leaf_blocks, blevel from dba_indexes where owner = 'SCOTT';

 

TABLE_NAME         INDEX_NAME       STATUS             NUM_ROWS LEAF_BLOCKS     BLEVEL

-------------------- -------------------- ------------------------ ---------- ----------- ----------

DEPT             PK_DEPT          VALID                 4        1       0

EMP             PK_EMP          VALID                14        1       0

 

 

7. dbms_stats 패키지로 갱신되지 않는 인덱스 통계정보 갱신

SQL> select name, blocks, lf_rows, del_lf_rows from index_stats;

 

no rows selected

 

 

SQL> analyze index scott.PK_DEPT validate structure;

 

Index analyzed.

 

SQL> select name, blocks, lf_rows, del_lf_rows from index_stats;

 

NAME                                                   BLOCKS     LF_ROWS DEL_LF_ROWS

------------------------------------------------------------------------------------------ ---------- ---------- -----------

PK_DEPT                                                 8           4       0