如何在ORACLE系統中對表進行統計分析

begin

創新互聯建站是一家專注于做網站、成都網站設計與策劃設計,湖州網站建設哪家好?創新互聯建站做網站,專注于網站建設十多年,網設計領域的專業建站公司;建站業務涵蓋:湖州等地區。湖州做網站價格咨詢:18980820575

dbms_stats.gather_table_stats( ownname='XXXX',tabname = 'XXXX' ,ESTIMATE_PERCENT=100,degree=8, CASCADE=true);

end;

如何分析oracle數據庫中的表結構？

去這里看看??

數據結構被稱為物理（存儲）的數據結構在計算機中的表示（圖片）。它包括一個數據元素表示的關系的表示。

物理結構，也就是由Oracle數據庫所使用的操作系統的文件結構。對于數據庫的物理結構文件，不同版本的Oracle，不同的操作系統平臺上的數據庫文件存放的目錄結構不同的物理結構

其作用可分為三類：

數據日志文件的文件

控制文件

數據文件，數據文件，用于存儲數據庫中的數據，如表，索引等。當讀取數據時，系統首先讀取的數據從數據庫文件，并存儲到數據緩沖器，SGA。

重做日志文件，重做日志文件

所有的記錄在數據庫中的信息。這是三種類型的文件，文件中最復雜的，而且要保證數據庫的安全性和數據庫的備份和恢復文件直接。

控制文件

控制文件是一個二進制文件，用來描述數據庫的物理結構，數據庫只需要一個控制文件，控制文件的內容，包括：

同步需要恢復的數據文件和日志文件的信息標識數據庫和數據庫的名稱，唯一標識

數據庫，檢查點數量

/

a

如果有，請記得采納為滿意的答復，謝謝你！我祝你幸福的生活！

vaela

Oracle數據庫表做表分析、索引分析的命令是什么？

analyze table 表名 compute statistics

analyze index 索引ID compute statistics

如果想分析所有的表名和index名可以從視圖user_tables，user_indexes取得相關的信息，自動生成SQL命令

如何分析Oracle

以oracle表分析為例：

drop table test;

select count(*) from test;

--創建測試表

create table test

(

id number(9),

nick varchar2(30)

);

--插入測試數據

begin

for i in 1..100000 loop

insert into test(id) values(i);

end loop;

commit;

end;

select * from test;

--更新nick字段，使數據發生嚴重傾斜

update test set nick='abc' where rownum99999;

--創建索引

create index idx_test_nick on test(nick);

update test set nick='def' where nick is null;

--只對索引進行分析

analyze index idx_test_nick compute statistics;

select * from user_indexes;

--查看索引名,對應存儲的數據塊,不同的key數量,記錄數（行數）的分析信息

select index_name, LEAF_BLOCKS, DISTINCT_KEYS, NUM_ROWS

from user_indexes

where index_name = 'IDX_TEST_NICK';

--dba_tab_col_statistics

--查看表的統計信息

select COLUMN_NAME, NUM_BUCKETS, num_distinct

from USER_tab_columns

where table_name = 'TEST';

select * from test where nick ='abc';

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST'

2 1 INDEX (RANGE SCAN) OF 'IDX_TEST_NICK' (NON-UNIQUE)

select * from test where nick ='def';

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST'

2 1 INDEX (RANGE SCAN) OF 'IDX_TEST_NICK' (NON-UNIQUE)

--由上可以看到,對索引分析之后,sql的執行路徑都是基于規則的,索引的字段的偏移

--先根據索引找到rowid,然后再根據rowid讀取記錄,這個過程肯定比全表掃描讀取記錄要慢

--user_part_col_statistics 分區分析信息

--分析表的第二列nick

analyze table test compute statistics for columns size 2 nick;

select * from test where nick ='abc';

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST'

2 1 INDEX (RANGE SCAN) OF 'IDX_TEST_NICK' (NON-UNIQUE)

--根據上面的執行計劃，還是按照規則來執行的

--分析表

analyze table test compute statistics for table;

select * from test where nick ='abc';

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=49 Card=99998 Bytes=

1499970)

1 0 TABLE ACCESS (FULL) OF 'TEST' (Cost=49 Card=99998 Bytes=14

99970)

--分析表之后，完全按照成本來執行

--刪除所有的統計數據，并只對表與列進行分析，不分析索引，

--ORACLE使用CBO的優化器，并產生了正確的執行計劃

analyze table test delete statistics;

--分析列nick

analyze table test compute statistics for table for columns size 2 nick;

select * from test where nick ='abc';

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=49 Card=99998 Bytes=

1499970)

1 0 TABLE ACCESS (FULL) OF 'TEST' (Cost=49 Card=99998 Bytes=14

99970)

--

select * from test where nick ='def';

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=2 Bytes=30)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=2 Byt

es=30)

2 1 INDEX (RANGE SCAN) OF 'IDX_TEST_NICK' (NON-UNIQUE) (Cost

=1 Card=2)

--創建TEST表ID列上的索引，但不對索引進行分析

create index idx_test_id on test(id);

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1000 Bytes=15

000)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=1000

Bytes=15000)

2 1 INDEX (RANGE SCAN) OF 'IDX_TEST_ID' (NON-UNIQUE) (Cost=1

Card=400)

--當條件中即有id，又有nick時，因為nick上有直方圖,ORACLE知道nick='abc'的值特別的多，所以不走IDX_TEST_NICK索引,走IDX_TEST_ID上的索引

select * from test where id=5 and nick='abc';

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1000 Bytes=15

000)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=1000

Bytes=15000)

2 1 INDEX (RANGE SCAN) OF 'IDX_TEST_ID' (NON-UNIQUE) (Cost=1

Card=400)

--當條件中即有id，又有nick時，因為nick上有直方圖,ORACLE知道nick='def'的值特別的少，所以走IDX_TEST_NICK上的索引,不走IDX_TEST_ID索引

select * from test where id=5 and nick='def';

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=15)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=1 Byt

es=15)

2 1 INDEX (RANGE SCAN) OF 'IDX_TEST_NICK' (NON-UNIQUE) (Cost

=1 Card=2)

select * from test where nick='def' and id=5;

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=15)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=1 Byt

es=15)

2 1 INDEX (RANGE SCAN) OF 'IDX_TEST_NICK' (NON-UNIQUE) (Cost

=1 Card=2)

--在分析ID列后，ORACLE發現ID列的選擇度更高，所以不再選擇IDX_TEST_NICK索引，而是選擇IDX_TEST_ID

analyze table test compute statistics for columns size 1 id;

select * from test where id=5 and nick='def';

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=7)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=1 Byt

es=7)

2 1 INDEX (RANGE SCAN) OF 'IDX_TEST_ID' (NON-UNIQUE) (Cost=1

Card=1)

/*

下面來看另外一種情況，我們刪除所有的統計數據，然后在ID列上創建唯一索引,在此條件下，

只分析表與分析列nick,我們看到ORACLE走了正確的執行計劃，

走了UK_TEST_ID,其實從這里也給我們帶來很多的啟示：

在主鍵與唯一鍵約束的列上是否需要直方圖的問題?

如果在這些列上有像這樣的查詢where id 100 and id 1000,

我們還是需要有直方圖的，但除此之外，好像真的沒有直方圖的必要了!

*/

analyze table test delete statistics;

drop index idx_test_id;

create unique index uk_test_id on test(id);

--分析表的第二列nick

analyze table test compute statistics for table for columns size 2 nick;

select * from test where id=5 and nick='def';

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=15)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=1 Byt

es=15)

2 1 INDEX (UNIQUE SCAN) OF 'UK_TEST_ID' (UNIQUE) (Cost=1 Car

d=100000)

從以上一系列的實驗可以看出，對ORACLE的優化器CBO來說，表的分析與列的分析才是最重要的，索引的分析次之。還有我們可以考慮我們的哪些列上需要直方圖，對于bucket的個數問題,oracle的默認值是75個，所以根據你的應用規則，選擇合適的桶數對性能也是有幫助的。因為不必要的桶的個數的大量增加，必然會帶來SQL語句硬解析時產生執行計劃的復雜度問題。

如何正確地分析Oracle數據庫模式，表及索引

在Oracle 數據庫中，我們經常需要去手工分析表和索引，還有某個模式下所有的表和索引等等。oracle 提供了兩種分析方法，分別是dbms_stats 包和analyze 命令。這些是我們在日常項目管理中的真實使用的方法，供參考。

在10g 中分析某一個用戶下所有的對象，如user_miki 用戶

Sql代碼 收藏代碼

sys.dbms_stats.gather_schema_stats(ownname = 'user_miki',estimate_percent = 30,method_opt = 'FOR ALL INDEXED COLUMNS SIZE AUTO',cascade = true,options = 'GATHER');

這是我在項目中分析一個用戶所有對象的分析方法，取樣比例為30% ，分析所有索引字段，生成柱形圖，并且也分析索引。

(miki西游 @mikixiyou 文檔，原文鏈接: )

在10g 中分析某一個用戶下某一個表及其索引，如user_miki 用戶下info 表和它的索引

Sql代碼 收藏代碼

execute dbms_stats.gather_table_stats(ownname = 'user_miki',tabname = 'info',estimate_percent = 30,method_opt = 'for all indexed columns size auto');

這是我在項目中單獨分析一個用戶中一張表的分析方法，取樣比例為30% ，分析所有索引字段，生成柱形圖，并且也分析索引。

我們在google 時，還能看到使用analzye 命令分析表的方法。

Sql代碼 收藏代碼

analyze table table_name compute statistics;

這個會將索引和表一起分析；

Sql代碼 收藏代碼

analyze table table_name compute statistics for table;

這個僅分析表；

analyze table table_name compute statistics for all columns;

這個僅分析表，和上面所有的字段，生成柱形圖

analyze table table_name compute statistics for all indexed columns;

這個僅分析表，和上面所有建立索引的字段，生成柱形圖

在9i 中，我們常常使用analyze 方法，在10g 以后，就改為dbms_stats.gather_schema_stats 類似的包來分析數據庫對象了。

10g 的分析函數包比9i 的analyze 方法更靈活一些。

在10g 中，會有個系統的計劃任務，每天10 點和周末兩天都自動分析新的數據庫對象和它認為統計信息已經過期的數據庫對象。這個分析結果有時候不靠譜。

因此，我都采用10g 中提供的dbms_stats.gather_schema_stats 方法作為補充，設置一個計劃任務定期去分析業務用戶下所有數據庫對象。

Sql代碼 收藏代碼

variable job number;

begin

sys.dbms_job.submit(job = :job,

what = 'sys.dbms_stats.gather_schema_stats(ownname = ''USR_MIKI'',estimate_percent = 30,method_opt = ''FOR ALL INDEXED COLUMNS SIZE AUTO'',cascade = true,options = ''GATHER'');',

next_date = to_date('16-12-2012 00:56:24', 'dd-mm-yyyy hh24:mi:ss'),

interval = 'sysdate+7');

commit;

end;

/

例如，設置為每周六運行一次。

本文名稱：oracle中如何分析表,oracle對表進行統計分析
轉載來源：http://vcdvsql.cn/article14/heoege.html

成都網站建設公司_創新互聯，為您提供企業建站、、網站設計公司、網站制作、手機網站建設、網站排名

廣告

聲明：本網站發布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網站立場，如需處理請聯系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經允許不得轉載，或轉載時需注明來源： 創新互聯