[펌] oralce analyze 관련 구문

Programming 2009/02/25 09:09

출처 : 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;

저작자 표시 비영리 변경 금지

'Programming' 카테고리의 다른 글

통합자막 분리용 스크립트  (1) 2009/07/10
[펌] oralce analyze 관련 구문  (0) 2009/02/25
계산기  (0) 2008/04/21
FCKeditor  (0) 2008/04/03
[펌] oracle dbms utility 관련 구문  (0) 2008/03/13
[펌] oracle procedure 관련  (0) 2008/02/01
Posted by jami
TAG
트랙백 0개, 댓글 0개가 달렸습니다.

트랙백 주소 : http://www.jami.name/trackback/328 관련글 쓰기

댓글을 달아 주세요