常用Oracle資料庫調優工具介紹
1. 級調優
1.1.修改資料庫引數
以sys使用者登入,執行如下的命令:
alter system set optimizer_index_cost_adj=10 scope=spfile
alter system set optimizer_dynamic_sampling=5 scope=spfile
optimizer_index_cost_adj取值0-1000,預設100 ;值越小就越趨向使用索引
optimizer_dynamic_sampling 配置oracle對沒有統計資訊的表會動態取樣,會得到更準確的統計資訊,同時讓最佳化器得到更準確的執行計劃。這個引數對臨時表尤其有用。
1.2.分析表
應該把所有沒有做過分析的表都做一下分析:
執行如下語句的返回結果。
select
'analyze table '||owner||'.'||table_name||' compute statistics; '
from dba_tables
where num_rows is not null
and owner not in ('SYS','SYSTEM')
and table_name not like '%$%'
and temporary = 'N'
如果針對SCHEMA裡的所有表做分析,以symbols為例
begin
dbms_stats.gather_schema_stats ('SYMBOLS');
end;
/
1.3.增加資料維護的job
1.3.1. 將表的監視開啟
select ‘alter table ‘||owner||’.’||table_name||’ monitoring; ’
from dba_tables
where owner not in (‘SYS’,’SYSTEM’,’OUTLN’,’WMSYS’)
and table_name not like '%$%'
and TEMPORARY=’N’;
這個監視是在記憶體中做的,一般情況下對效能不會有什麼影響,如果發現實在有影響的時候,
下面語句關閉監視,
select ‘alter table ‘||owner||’.’||table_name||’ nomonitoring; ’
from dba_tables
where owner not in (‘SYS’,’SYSTEM’,’OUTLN’,’WMSYS’)
and table_name not like '%$%'
and TEMPORARY=’N’;
1.3.2. 新增JOB
用sys使用者登入
執行如下語句,mynextdate,mynextSatdate需要手工更改為下一日和下一個週六。
define mynextdate='2004-03-03'; --to_char(sysdate + 1,'yyyy-mm-dd');
define mynextSatdate='2004-03-06'; --to_char(NEXT_DAY(sysdate,7),'yyyy-mm-dd');
--每天早上3點左右執行flush monitor info
call sys.dbms_job.remove(101);
call sys.dbms_job.isubmit( 101,
'sys.DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO();',
to_date('&mynextdate03:00:00','YYYY-MM-DD HH24:MI:SS'),
'to_date(''&mynextdate03:00:00'',''YYYY-MM-DD HH24:MI:SS'') + 1');
commit;
--統計資料庫資訊
call sys.dbms_job.remove(102);
call sys.dbms_job.isubmit( 102,
'sys.dbms_stats.gather_database_stats (options => ''GATHER STALE'');',
to_date('&mynextdate03:10:00','YYYY-MM-DD HH24:MI:SS'),
'to_date(''&mynextdate03:10:00'',''YYYY-MM-DD HH24:MI:SS'') + 1');
commit;
--每週六下午6:30把執行對那些被更新特別頻繁的表的索引做rebuild
call sys.dbms_job.remove(201);
call sys.dbms_job.isubmit( 201,
'execute immediate ''alter INDEX history.PK_INCREMENT_STOCK_BALANCE_HIS rebuild nologging'';'||
'execute immediate ''alter INDEX history.ind_inc_stock_balance_1 rebuild nologging'';'||
'execute immediate ''alter INDEX history.ind_inc_stock_balance_2 rebuild nologging'';'||
'execute immediate ''alter INDEX history.PK_TB_INCREMENT_BALANCE_HIS rebuild nologging'';'||
'execute immediate ''alter INDEX history.Ind_inc_balance rebuild nologging'';',
to_date('&mynextdate18:30:00','YYYY-MM-DD HH24:MI:SS'),
'to_date(''&mynextdate18:30:00'',''YYYY-MM-DD HH24:MI:SS'') + 7');
commit;
1.4.刪除重複的索引
查詢重複的索引
select *
from
( select index_owner,
index_name,
table_owner,
table_name,
max(decode(p, 1, c,NULL)) ||
max(decode(p, 2,', '||c,NULL)) ||
max(decode(p, 3,', '||c,NULL)) ||
max(decode(p, 4,', '||c,NULL)) ||
max(decode(p, 5,', '||c,NULL)) ||
max(decode(p, 6,', '||c,NULL)) ||
max(decode(p, 7,', '||c,NULL)) ||
max(decode(p, 8,', '||c,NULL)) ||
max(decode(p, 9,', '||c,NULL)) ||
max(decode(p,10,', '||c,NULL)) ||
max(decode(p,11,', '||c,NULL)) ||
max(decode(p,12,', '||c,NULL)) ||
max(decode(p,13,', '||c,NULL)) ||
max(decode(p,14,', '||c,NULL)) ||
max(decode(p,15,', '||c,NULL)) ||
max(decode(p,16,', '||c,NULL)) index_cols
from (select index_owner,index_name,table_owner,table_name, substr(column_name,1,30) c, column_position p
from dba_ind_columns )
group by index_owner,index_name,table_owner,table_name ) A,
( select index_owner,
index_name,
table_owner,
table_name,
max(decode(p, 1, c,NULL)) ||
max(decode(p, 2,', '||c,NULL)) ||
max(decode(p, 3,', '||c,NULL)) ||
max(decode(p, 4,', '||c,NULL)) ||
max(decode(p, 5,', '||c,NULL)) ||
max(decode(p, 6,', '||c,NULL)) ||
max(decode(p, 7,', '||c,NULL)) ||
max(decode(p, 8,', '||c,NULL)) ||
max(decode(p, 9,', '||c,NULL)) ||
max(decode(p,10,', '||c,NULL)) ||
max(decode(p,11,', '||c,NULL)) ||
max(decode(p,12,', '||c,NULL)) ||
max(decode(p,13,', '||c,NULL)) ||
max(decode(p,14,', '||c,NULL)) ||
max(decode(p,15,', '||c,NULL)) ||
max(decode(p,16,', '||c,NULL)) index_cols
from (select index_owner,index_name,table_owner,table_name, substr(column_name,1,30) c, column_position p
from dba_ind_columns )
group by index_owner,index_name,table_owner,table_name ) B
where a.table_owner=b.table_owner
and a.table_name=b.table_name
and (a.index_owner<>b.index_owner
or a.index_name <> b.index_name )
and a.index_cols like b.index_cols || '%'
and a.owner not in (‘SYS’,’SYSTEM’,’OUTLN’,’WMSYS’)
and a.table_name not like '%$%'
出來的結果中,排在後面的索引是可以刪除的。
1.5.做為一項常規任務,每個月做一次停下應用的維護
檢查哪些表碎片比較多,對這些碎片很多的表做move同時將失效的索引全部重建。
這一項任務是在已經對錶都做了分析的情況下來做。
做scrīpt0,對錶全部做分析
begin
dbms_stats.gather_schema_stats (‘JZ21’);
end;
/
針對所有的schema都做一次。
--檢視哪些表的空間碎片比較大,目前是認為大於30%就做整理。
select * from (
Select owner,table_name,(num_rows*avg_row_len/8000),blocks,
(num_rows*avg_row_len/8000)/( decode(blocks,0,1,null,1,blocks)) bb
from dba_tables where owner in (‘JZ21’)
and TEMPORARY=’N’
) tt
where tt.bb<0.7 and blocks>10;
--生成整理指令碼scrīpt1
select ‘alter table ‘||owner||’.’||table_name||’ move; ’ from (
Select owner,table_name,(num_rows*avg_row_len/8000),blocks,
(num_rows*avg_row_len/8000)/( decode(blocks,0,1,null,1,blocks)) bb
from dba_tables where owner in (‘JZ21’)
and TEMPORARY=’N’
) tt
where tt.bb<0.7 and blocks>10;
--預備好分析指令碼,不過這個指令碼應該最後做scrīpt3
select 'exec dbms_stats.gather_table_stats( '''||owner||''','''||table_name||''' ,method_opt => ''for all columns'', cascade => true ); ' from (
Select owner,table_name,(num_rows*avg_row_len/8000),blocks,
(num_rows*avg_row_len/8000)/( decode(blocks,0,1,null,1,blocks)) bb
from dba_tables where owner in (‘JZ21’)
and TEMPORARY='N'
) tt
where tt.bb<0.7 and blocks>10;
--生成重建索引指令碼scrīpt2
select ‘alter index ‘||owner||’.’||index_name||’ rebuild nologging;’ from dba_indexes where owner in (‘JZ21’) and status='UNUSABLE' ;
步驟:先執行scrīpt0
結束後,生成scrīpt1,scrīpt2,scrīpt3
按順序執行,scritp1,scrīpt2,scrīpt3。
1.6.特殊情況下對資料庫重新整理
1.6.1. 對錶做move
alter table test_move move ;
select ‘alter table ‘||owner||’.’||table_name||’ move; ’ from dba_tables where owner in (‘JZ21’) and TEMPORARY=’N’;
這裡用需要做move的schema替換。
1.6.2. 對index做rebuild
alter INDEX idx_test_move rebuild nologging;
select ‘alter index ‘||owner||’.’||index_name||’ rebuild nologging;’ from dba_indexes where owner in (‘JZ21’);
1.6.3. 對所有的表做分析
begin
dbms_stats.gather_schema_stats (‘JZ21’);
end;
/
針對所有的schema都做一次。
1.7.對索引開啟監視開關,看哪些索引沒有被使用(慎用)
select ‘alter index ‘||owner||’.’||index_name||’ monitoring usage;’ from dba_indexes
where owner not in (‘SYS’,’SYSTEM’);
在執行了一個很完整的週期以後(對我們來說,例如2個完整的交易日),檢查有哪些索引沒有被使用
select index_name,monitoring,used,start_monitoring,end_monitoring from v$object_usage where used='NO';
如果這些索引不是主鍵或唯一索引,一般情況下這些索引可以去掉。
檢查完一次以後,將索引監視關閉,不需要老是監視索引的使用:
select ‘alter index ‘||owner||’.’||index_name||’ nomonitoring usage;’ from dba_indexes where owner in (‘WHT’);
2. 語句調優
2.1.PL/SQL中的語法解釋工具
語句的執行效率是否符合需要,一個是簡單的把語句提取出來直接執行一下,看效果如何,但很多情況下,語句的執行是根據實際的條件執行的,所以有時候我們寫完語句有要先分析一下語句是否是最優的,這時候我們就可以用PL/SQL的explain視窗(或直接選種語句後,按F5),有時侯,我們已知語句比較慢,要尋找為什麼會慢,也可以如此做。
在EXPLAIN視窗,我們需要關注幾個重要的東西,COST(成本),如果這個COST數字很大,說明語句效率不高,需要想辦法;有無TABLE ACCESS FULL,如果有,只要資料量大一些,效率就不會高。我們要儘量避免產生table access full,方法之一就是建索引,另一個方法是改寫連線方式,使能夠用到索引。另一個需要關注的是join方式,nest loops outer和hash join outer,這兩種方式的效果是不一樣的,有時有天壤之別,目前我沒有類似的經驗可以寫,以後碰到後補充。
2.2.ORACLE提供的調優工具
ORACLE本身提供了很好的客戶端的效能除錯和跟蹤工具。ORACLE的工具非常好,可以幫助我們很容易的找到系統效能的瓶頸語句在哪裡。ORACLE效能調整工具的位置在:
開始—程式—Oracle – Orahome92 – Enterprise Management Packs –Diagnostics –Performance Manager,登陸後找到資料庫欄,然後連線就可以看到。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/22578826/viewspace-663109/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- JVM常用調優工具介紹2020-11-11JVM
- oracle資料庫調優描述2022-03-23Oracle資料庫
- Oracle資料庫審計功能介紹2023-02-25Oracle資料庫
- 掌握Oracle資料庫效能調優方法2022-05-01Oracle資料庫
- oracle常用函式介紹2018-10-21Oracle函式
- 如何調優 Oracle SQL系列文章:查詢優化器介紹2019-08-21OracleSQL優化
- 資料庫介紹2022-01-24資料庫
- BBED修復資料庫常用命令介紹2021-05-06資料庫
- 資料庫調優2018-03-12資料庫
- 資料庫介紹--初識資料庫2018-07-15資料庫
- [轉]Oracle資料庫ASH和AWR的簡單介紹2020-04-07Oracle資料庫
- Oracle資料庫使用者安全策略功能介紹2023-03-04Oracle資料庫
- oracle資料庫常用語句2021-09-02Oracle資料庫
- 35個國內外社會調查資料工具介紹2019-11-28
- L10資料庫——資料庫介紹2018-11-22資料庫
- Linux系統調優介紹2023-03-08Linux
- 資料庫連線池基本介紹及常用jar包<一>2020-12-22資料庫JAR
- Java常用工具介紹2021-04-17Java
- Oracle:容器資料庫簡介2021-07-12Oracle資料庫
- 11_Oracle bbed工具介紹2020-03-10Oracle
- HSQL 資料庫介紹(1)--簡介2024-07-07SQL資料庫
- MySQL資料庫鎖介紹2019-02-18MySql資料庫
- hhdb資料庫介紹(4)2024-11-22資料庫
- Oracle資料庫常用命令2024-05-07Oracle資料庫
- 介紹些Golang常用工具庫(偏後臺方向)2018-11-05Golang
- Oracle 效能調優工具:SQL Monitor2024-03-11OracleSQL
- oracle資料庫透過sqlplus連線的幾種方式介紹2019-01-27Oracle資料庫SQL
- Sqlserver_Oracle_Mysql_Postgresql不同資料庫之隔離級別介紹2023-02-13ServerOracleMySql資料庫
- 達夢資料庫dexp邏輯匯出工具使用介紹2020-12-22資料庫
- Mysql資料庫的join演算法介紹,優美的執行優化2019-03-25MySql資料庫演算法優化
- 資料庫安全知識介紹2019-04-01資料庫
- HSQL 資料庫介紹(2)--使用2024-07-14SQL資料庫
- QuestDB時序資料庫介紹2023-04-17資料庫
- 10個常用流行 Kotlin 庫介紹2024-03-20Kotlin
- HBase資料庫效能調優OW2022-03-21資料庫
- Oracle 20C 多租戶_1.2 資料庫與例項介紹2020-04-21Oracle資料庫
- 資料字典生成工具及文件工具作用介紹2021-09-09
- 資料介面測試工具 Postman 介紹2021-09-09Postman
- H2 資料庫介紹(1)--簡介2024-05-12資料庫