티스토리 뷰
출처 : http://www.psoug.org/reference/analyze.html
다음 내용은 최신 버전이 아닐 수 있습니다. 최신 버전은 위 사이트를 참조하세요.
다음 내용은 최신 버전이 아닐 수 있습니다. 최신 버전은 위 사이트를 참조하세요.
Note: Do not use the COMPUTE and ESTIMATE clauses of
ANALYZE to collect optimizer statistics. These clauses are supported for backward compatibility. Instead, use the
DBMS_STATS package, which lets you collect statistics in parallel, collect global statistics for partitioned objects,
and fine tune your statistics collection in other ways. The cost-based optimizer, which depends upon statistics, will
eventually use only statistics that have been collected by DBMS_STATS. Those ANALYZE capabilities not recommended by Oracle are not documented here. |
|
Create Table To Hold Validation Output | utlvalid.sql |
SQL> @?/rdbms/admin/utlvalid.sql desc invalid_rows |
|
Create Table To Hold Chained Row Output | utlvalid.sql |
SQL>
@?/rdbms/admin/utlchn1.sql desc chained_rows |
|
CLUSTER | |
Create Demo Cluster |
CREATE CLUSTER uw_cluster ( table_name VARCHAR2(30)) SIZE 512; CREATE INDEX ix_tabnames ON CLUSTER uw_cluster; CREATE TABLE uwtables CLUSTER uw_cluster (table_name) AS SELECT table_name, tablespace_name FROM all_tables; CREATE TABLE uwindexes CLUSTER uw_cluster (table_name) AS SELECT table_name, index_name FROM all_indexes; SELECT COUNT(*) FROM uwtables; SELECT COUNT(*) FROM uwindexes; |
List Chained Rows | ANALYZE CLUSTER <cluster_name> LIST CHAINED ROWS INTO <table_name>; |
ANALYZE CLUSTER uw_cluster LIST CHAINED ROWS INTO chained_rows; SELECT * FROM chained_rows; |
|
Validate Structure | ANALYZE CLUSTER <cluster_name> VALIDATE STRUCTURE CASCADE INTO <table_name> <OFFLINE | ONLINE> |
ANALYZE CLUSTER uw_cluster VALIDATE STRUCTURE CASCADE; | |
INDEX | |
Create Demo Table & Index |
CREATE TABLE test PCTFREE 0 AS SELECT object_name, object_type FROM all_objects WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'W'; CREATE INDEX ix_test ON test (object_name, object_type) PCTFREE 0; |
Validate Structure |
ANALYZE INDEX <index_name> [PARTITION <partition_name>] [SUBPARTITION <subpartition_name>] VALIDATE STRUCTURE CASCADE INTO <table_name> <OFFLINE | ONLINE> |
desc index_stats set linesize 121 SELECT height, blocks, lf_rows, lf_blks, br_rows, br_blks, btree_space, used_space FROM index_stats WHERE name = 'IX_TEST'; ANALYZE INDEX ix_test VALIDATE STRUCTURE; SELECT height, blocks, lf_rows, lf_blks, br_rows, br_blks, btree_space, used_space FROM index_stats WHERE name = 'IX_TEST'; SELECT opt_cmpr_count, opt_cmpr_pctsave FROM index_stats; DROP INDEX ix_test; CREATE INDEX ix_test ON test (object_name, object_type) PCTFREE 0 COMPRESS 1; ANALYZE INDEX ix_test VALIDATE STRUCTURE; SELECT height, blocks, lf_rows, lf_blks, br_rows, br_blks, btree_space, used_space FROM index_stats WHERE name = 'IX_TEST'; SELECT opt_cmpr_count, opt_cmpr_pctsave FROM index_stats; |
|
TABLE | |
List Chained Rows Note: While pm.online_media has chained rows in previous versions of Oracle it does not in the 11gR2 beta |
ANALYZE TABLE <table_name> LIST CHAINED ROWS INTO <table_name>; |
conn / as sysdba SELECT owner, table_name FROM dba_tables WHERE chain_cnt > 0; conn pm/pm SQL> @?/rdbms/admin/utlchn1.sql desc chained_rows; ANALYZE TABLE online_media LIST CHAINED ROWS INTO chained_rows; set linesize 121 col owner_name format a10 col table_name format a15 col cluster_name format a7 col partition_name format a9 col subpartition_name format a12 col head_rowid format a20 SELECT * FROM chained_rows; |
|
Compute Statistics Deprecated: Use DBMS_STATS |
ANALYZE TABLE <table_name> <COMPUTE | DELETE | ESTIMATE> STATISTICS |
conn
uwclass/uwclass SELECT num_rows, blocks, empty_blocks, avg_space, avg_row_len FROM user_tables WHERE table_name ='TEST'; ANALYZE TABLE test COMPUTE STATISTICS; SELECT num_rows, blocks, empty_blocks, avg_space, avg_row_len FROM user_tables WHERE table_name ='TEST'; ANALYZE TABLE test DELETE STATISTICS; SELECT num_rows, blocks, empty_blocks, avg_space, avg_row_len FROM user_tables WHERE table_name ='TEST'; ANALYZE TABLE test ESTIMATE STATISTICS; SELECT num_rows, blocks, empty_blocks, avg_space, avg_row_len FROM user_tables WHERE table_name ='TEST'; |
|
Validate Structure | ANALYZE TABLE <table_name> [PARTITION <partition_name>] [SUBPARTITION <subpartition_name>] VALIDATE STRUCTURE CASCADE [INTO <table_name>] <OFFLINE | ONLINE> |
conn
uwclass/uwclass ANALYZE TABLE test VALIDATE STRUCTURE CASCADE ONLINE; SELECT * FROM invalid_rows; |
'Dev > Oracle' 카테고리의 다른 글
[펌] oracle procedure 관련 (0) | 2008.02.01 |
---|---|
[펌] oracle import 관련 구문 (0) | 2007.11.22 |
[펌] oracle table 관련 구문 (0) | 2007.10.26 |
[펌] oracle tablespace 관련 구문 (0) | 2007.10.11 |
[펌] oracle update 구문 (1) | 2007.10.01 |
댓글
최근에 올라온 글
최근에 달린 댓글
- Total
- Today
- Yesterday
TAG
- 정부과천청사
- iBATIS
- 김용
- Git
- 프로젝트
- 최대 메모리
- 파워콤해지
- 헌혈
- Eclipse
- 충동구매
- 바이크
- 금연
- 짜증
- EditPlus
- oracle
- 출근
- 출근길
- ubuntu
- connect by
- GB-P100
- 무료 프로그램
- 법무부
- 안드로이드 앱
- 낭패
- Java
- VMware
- 오라클
- 윈도우
- 액땜
- 일하는 곳
일 | 월 | 화 | 수 | 목 | 금 | 토 |
---|---|---|---|---|---|---|
1 | 2 | 3 | 4 | |||
5 | 6 | 7 | 8 | 9 | 10 | 11 |
12 | 13 | 14 | 15 | 16 | 17 | 18 |
19 | 20 | 21 | 22 | 23 | 24 | 25 |
26 | 27 | 28 | 29 | 30 | 31 |
글 보관함