Oracle資料庫Table,Index,Database分析統計資料方式總結及注意點(zt)
刪除後再分析就更新了,或者直接用dbms_stats分析
SQL> exec dbms_stats.delete_table_stats(ownname=>’FAN’,tabname=>’TEST’);
PL/SQL procedure successfully completed.
SQL> analyze table test compute statistics; 或者用dbms_stats也可以。
Table analyzed.
http://www.dbafan.com/blog/?p=90
-------------------------------------------------------------------------------------------------------------
ORACLE資料庫的SQL語句執行的最佳化器,有基於代價的最佳化器(CBO)和基於規則的最佳化器(RBO)。RBO的最佳化方式,依賴於一套嚴格的語法規則,只要按照規則寫出的語句,不管資料表和索引的內容是否發生變化,不會影響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提供的
包中的儲存過程的相關引數解釋如下:
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中新增的
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的所有表,簇表和索引作統計分析。 包(PACKAGE)對相關的資料庫物件進行分析。
有以下的包可以對錶,索引,簇表進行分析。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/35489/viewspace-85123/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- ORACLE資料庫Table (index) 分析統計及其生成方式Oracle資料庫Index
- 資料庫表--index organized table資料庫IndexZed
- 資料庫表--index clustered table資料庫Index
- database資料庫的資料結構Database資料庫資料結構
- oracle資料遷移注意點(結合lzgaj)Oracle
- oracle10g 常用資料字典總結 (zt)Oracle
- 資料庫設計總結資料庫
- Oracle資料庫索引使用及索引失效總結 轉Oracle資料庫索引
- Oracle資料庫資料遷移或匯出匯入(exp/imp,dblink)應該注意的點(總結)Oracle資料庫
- Oracle 資料庫碎片整理(zt)Oracle資料庫
- Oracle資料庫資料鎖機制解析(zt)Oracle資料庫
- 資料庫效能分析及調整一例(zt)資料庫
- Oracle資料庫的資料統計(Analyze)Oracle資料庫
- 深度分析資料庫的熱點塊問題 (zt)資料庫
- 【Web總結】資料庫系統Web資料庫
- oracle資料字典的一點總結!Oracle
- [zt] 收縮使用者資料庫 (shrink database)資料庫Database
- MySQL 資料庫設計總結MySql資料庫
- oracle資料庫hang住分析工具Hanganalyze使用總結Oracle資料庫
- Oracle10g 資料匯入及index建立 - 統計資訊收集OracleIndex
- oracle之 oracle database vault(資料庫保險庫)OracleDatabase資料庫
- zt_Oracle 資料庫體系結構圖_eygleOracle資料庫
- h2database資料庫分析Database資料庫
- 資料庫總結資料庫
- Oracle資料庫效能優化總結Oracle資料庫優化
- oracle 資料庫安裝思路總結Oracle資料庫
- 【資料庫資料恢復】Oracle資料庫誤truncate table的資料恢復案例資料庫資料恢復Oracle
- 【資料庫資料恢復】oracle資料庫誤truncate table怎麼恢復資料?資料庫資料恢復Oracle
- 【資料庫資料恢復】誤truncate table的Oracle資料庫資料恢復方案資料庫資料恢復Oracle
- 【資料庫】資料查詢和管理知識點總結資料庫
- Oracle資料庫資料物件分析(上)Oracle資料庫物件
- Oracle資料庫資料物件分析(轉)Oracle資料庫物件
- Oracle Database 12c 資料庫32個新特性與案例總結(轉)OracleDatabase資料庫
- 資料庫程式設計方法總結資料庫程式設計
- Oracle中的資料字典技術及常用資料字典總結Oracle
- 電商系統商品資料表設計分析與總結
- 修改Oracle資料庫字符集(zt)Oracle資料庫
- Oracle資料庫連線方式Oracle資料庫