ORACLE數據庫的統計數據及其生成方式

2024-08-29 13:29:33

字體：大中小

來源：轉載

供稿：網友

oracle數據庫的pl/sql語句執行的優化器，有基于代價的優化器（cbo）和基于規則的優化器（rbo）。
rbo的優化方式，依賴于一套嚴格的語法規則，只要按照規則寫出的語句，不管數據表和索引的內容是否發生變化，不會影響pl/sql語句的"執行計劃"。
cbo自oracle 7版被引入，oracle自7版以來采用的許多新技術都是只基于cbo的，如星型連接排列查詢，哈希連接查詢，反向索引，索引表，分區表和并行查詢等。cbo計算各種可能"執行計劃"的"代價"，即cost，從中選用cost最低的方案，作為實際運行方案。各"執行計劃"的cost的計算根據，依賴于數據表中數據的統計分布，oracle數據庫本身對該統計分布是不清楚的，須要分析表和相關的索引，才能搜集到cbo所需的數據。
cbo是oracle推薦使用的優化方式，要想使用好cbo，使sql語句發揮最大效能，必須保證統計數據的及時性。
統計信息的生成可以有完全計算法和抽樣估算法。sql例句如下：
完全計算法： analyze table abc compute statistics;
抽樣估算法(抽樣20%)： analyze table abc estimate statistics sample 20 percent;
對表作完全計算所花的時間相當于做全表掃描，抽樣估算法由于采用抽樣，比完全計算法的生成統計速度要快，如果不是要求要有精確數據的話，盡量采用抽樣分析法。建議對表分析采用抽樣估算，對索引分析可以采用完全計算。
我們可以采用以下兩種方法，對數據庫的表和索引及簇表定期分析生成統計信息，保證應用的正常性能。
1. 在系統設置定時任務，執行分析腳本。
在數據庫服務器端，我們以unix用戶oracle，運行腳本analyze,在analyze中，我們生成待執行sql腳本，并運行。（假設我們要分析scott用戶下的所有表和索引）
analyze腳本內容如下：
sqlplus scott/tiger << eof
set pagesize 5000
set heading off
spool analytab.sql
select 'analyze table scott.'||table_name||' estimate statistics sample 20 percent ;' from user_tables；
spool off
spool analyind.sql
select 'analyze table scott.'||table_name||' estimate statistics sample 20 percent for all indexes;' from user_tables；
spool off
spool analyze.log
@analytab.sql
@analyind.sql
spool off
exit

在unix平臺上crontab加入，以上文件，設置為每個月或合適的時間段運行。

2. 利用oracle提供的程序包（package）對相關的數據庫對象進行分析。
有以下的程序包可以對表，索引，簇表進行分析。
包中的存儲過程的相關參數解釋如下：
type可以是：table，index，cluster中其一。
schema為：table，index，cluster的所有者，null為當前用戶。
name為：相關對象的名稱。
method是：estimate，compute，delete中其一，當選用estimate，
下面兩項，estimate_rows和estimate_percent不能同
時為空值。
estimate_rows是：估算的抽樣行數。
estimate_percent是：估算的抽樣百分比。
method_opt是：有以下選項，
for table /*只統計表*/
[for all [indexed] columns] [size n] /*只統計有索引的表列*/
for all indexes /*只分析統計相關索引*/

partname是：指定要分析的分區名稱。
1)
dbms_ddl.analyze_object(
type varchar2,
schema varchar2,
name varchar2,
method varchar2,
estimate_rows number default null,
estimate_percent number default null,
method_opt varchar2 default null,
partname varchar2 default null ) ;
該存儲過程可對特定的表，索引和簇表進行分析。
例如，對scott用戶的emp表，進行50%的抽樣分析，參數如下：
dbms_ddl.analyze_object('table', 'scott', 'emp', 'estimate', null,50);

2)
dbms_utility.analyze_schema (
schema varchar2,
method varchar2,
estimate_rows number default null,
estimate_percent number default null,
method_opt varchar2 default null ) ;
dbms_utility.analyze_database (
method varchar2,
estimate_rows number default null,
estimate_percent number default null,
method_opt varchar2 default null ) ;

其中，analyze_schema用于對某個用戶擁有的所有table，index和cluster的分析統計。
analyze_database用于對整個數據庫進行分析統計。
3) dbms_stats是在oracle8i中新增的程序包，它使統計數據的生成和處理更加靈活方便，并且可以并行方式生成統計數據。在程序包中的以下過程分別分析統計table，index，schema，database級別的信息。
dbms_stats.gather_table_stats
dbms_stats.gather_index_stats
dbms_stats.gather_schema_stats
dbms_stats.gather_database_stats

在這里，我們以數據庫job的方式，定時對數據庫中scott模式下所有的表和索引進行分析：
在sql*plus下運行：
variable jobno number;
begin
dbms_jobs.submit ( :jobno ,
' dbms_utility.analyze_schema ( "scott", "estimate", null, 20) ; ',
sysdate, 'sysdate+30');
commit;
end;
/
statement processed.
print jobno
jobno
-------------
16

以上作業，每隔一個月用dbms_utility.analyze_schema對用戶scott的所有表，簇表和索引作統計分析

商業源碼熱門下載www.html.org.cn

上一篇：Oracle 的問題

下一篇：在Oracle9i中如何自行編譯BBED工具