大 db_block_size
大 db_cache_size
使用多個塊大小
多個數據庫寫入(DBWR)進程
大sort_area_size
大的在線重作日志
Oracle 對象在數據庫內部(表和索引的設置可以減少物理磁盤IO)低pctused pctuseed的值越小,隨后的sql插入中出現的io就越少
低pctfree 假如設置了pctfree,以答應在沒有分割的情況下擴展所有行,那么在隨后的sql選擇中就會產生更少的磁盤io
使用索引將表重新組織成簇行 假如以最常使用索引的相同物理次序放置表3 oracle sql
在sql語句內,有許多技術可以減少物理磁盤io使用索引或提示(hint)防止不必要的全表搜索使用位映射(bitmapped)索引應用sql提示oracle 內部結構和磁盤io --查找稀疏表(自由表失去平衡!)select
substr(dt.table_name,1,10) c3,
ds.extents c5,
ds.bytes/1048576 c4,
dt.next_extent/1048576 c8,
(dt.empty_blocks*4096)/1048576 c7,
(ds.bytes*4096)/1048576 c6,
(avg_row_len*num_rows)/(db.blocks*4096) c10from sys.dba_segments ds ,
sys.dba_tables dt
where
--調整oracle數據庫實例接下來我們要調整oracle數據庫實例,以及查看所有影響性能的 參數,配置和設定用STATAPACK檢測實例潛在的性能問題1.oracle實例概述2.調整oracle 數據緩沖區3.調整共享池概述4.調整庫高速緩存5.調整oracle排序6.調整回滾段7.oracle 9i RAM 內存調整通常的角度看 oracle實例包括了兩個組件: 系統全局區(SGA) 以及 oracle后臺進程我們通常通過調整oracle參數來控制SGA和后臺進程當oracle啟動時 oracle就會使用malloc()命令去建立一個RAM內存區域,這個SGA通常也稱為oracle區域oracle DBA 可以控制SGA的規模 正確的SGA治理可以極大的影響性能
盡管初始化參數成百上千
但是只有很少的oracle9i參數對調整非常重要:
buffer_pool_keep 這個數據緩沖池用于存儲執行全表掃描的小表
buffer_pool_recycle 這個池用來保存進行全表掃描的非常大的表的表塊
db_cache_size 這個參數會決定ORACLE SGA 中數據庫塊緩沖區的數量,它是oracle內存的最重要的參數
db_block_size 數據庫塊大小能夠對性能產生(作為一個一般的規則,塊尺寸越大,物理IO就越少,整體性能就越快)
db_file_multiblock_read_count 這個參數用于全表搜索或者大表范圍掃描的時候,進行多塊讀入
large_pool_szie 這是一個使用多線程服務器的時候,保留用于SGA使用的共享池中的非凡區域.最大池也用于并行查詢RAM進程
log_buffer 這個參數會決定為oracle重作日志緩沖區分配的內存數量.假如具有大量的更新活動，就應該給log_buffer分配更多的空間shared_pool_size 這個參數會定義系統中所有用戶的共享池,包括SQL區域和數據字典高速緩存.
--有三個oracle參數可以影響數據緩沖區的大小
db_cache_size
buffer_pool_keep
buffer_pool_recycleoracle建議緩沖區的命中率要超過90％ DBA可以通過給初始化參數增加數據塊數量來控制數據緩沖區命中率數據庫緩沖池的內部結構--使用statspack監視緩沖池的使用
--緩沖池命中率和statpackselect * from stats$buffer_pool_statisticsSGA_MAX_SIZE=6000M
DB_BLOCK_SIZE=16384
DB_CACHE_SIZE=5000M
BUFFER_POOL_KEEP=(1400,3)
BUFFER_POOL_RECYCLE=(900,3)
--在oracle8 可以使用ALTER TABLE CUSTOMER STORAGE(buffer_pool KEEP);
ALTER TABLE USER.TABLE_NAME STORAGE(buffer_pool keep);--高級KEEP池候選識別
除了進行全表掃描的小表之外，keep緩沖池還非常適合放置頻繁使用的數據段的數據塊--使用x$bh視圖來識別平均塊接觸次數超過5次，并且在緩存中占用超過20個數據塊的對象
--
hot_buffer.sql
--識別熱點對象 select object_type mytype,
object_name myname ,
blocks,
count(1) buffers,
avg(tch) avg_toUChes
from
sys.x$bh a,
dba_objects b,
dba_segments s
where
a.obj=b.object_id
and
b.object_name=s.segment_name
and
b.owner not in('SYS','SYSTEM')
GROUP BY object_name,object_type,
blocks,obj
having avg(tch)>5
and count(1)>20;
識別出熱點對象后，可以決定將對象隔離放入keep池中
作為一般的規則，應該有足夠的RAM存儲可以用于整個表或者索引
列如，假如希望為keep池增加頁表，就需要給init.ora的buffer_pool_keep 參數增加104個數據塊--調整 recycle 池
在recycle池放置對象的目標是將全表搜索頻率的大表進行分離，為了找到進行全表搜索的大表，我們必須求助于從
access.sql中獲得的全表搜索報告：access_recycle_syntax.sqlselect
'alter table 'p.owner'.'p.name' storage (buffer_pool recyle);'
from
dba_tables t,
dba_segments s,
sqltemp s,
(select distinct
statement_id stid,
object_owner owner,
object_name name
from
plan_table
where
Operation='TABLE ACCESS'
and
options='FULL') p
where
s.addr':'TO_CHAR(s.hashval)=p.stid
and
t.table_name=p.name
and
t.owner=p.owner
and t.buffer_pool<>'RECYCLE'
having s.blocks>1000
group by
p.owner,p.name,t.num_rows,s.blocks
order by
sum(s.executions) desc;
--給表分配recycle池alter table user.table_name storage(buffer_pool recycle);注重：在將任何表加入到RECYCLE池之前，DBA都應該抽取sql源代碼，并且驗證這個查詢是否獲取超過了表中行的40％--高級recycle池調整
下列查詢使用了x$bh.tch來識別具有一次緩沖區接觸計數，但是總量超過了整個緩存的5％的數據緩存中的對象
，這些數據段是潛在的在recycle緩沖池中放置的候選對象，因為他們可能會讓不會重用的數據塊占用大量的緩存空間select object_type mytype,
object_name myname,
blocks,
count(1) buffers,
100*(count(1)/totsize) pct_cache
from
sys.x$bh a,
dba_objects b,
dba_segments s,
()
--取消跟蹤功能
alter system set trace_enabled=false;
--STATISTICS_LEVELThe STATISTICS_LEVEL parameter was introduced in Oracle9i Release 2 (9.2) to control all major statistics collections or advisories in the database. The level of the setting affects the number of statistics and advisories that are enabled:BASIC: No advisories or statistics are collected.TYPICAL: The following advisories or statistics are collected:Buffer cache advisory
MTTR advisory
Shared Pool sizing advisory
Segment level statistics
PGA target advisory
Timed statistics
ALL: All of TYPICAL, plus the following:
Timed operating system statistics
Row source execution statistics
The parameter is dynamic and can be altered using:ALTER SYSTEM SET statistics_level=basic;
ALTER SYSTEM SET statistics_level=typical;
ALTER SYSTEM SET statistics_level=all;
Current settings for parameters can be shown using:SHOW PARAMETER statistics_level
SHOW PARAMETER timed_statistics
Oracle can only manage statistic collections and advisories whose parameter setting is undefined in the spfile.
By default the TIMED_STATISTICS parameter is set to TRUE so this must be reset for it to be controled by the statistics level,
along with any other conflicting parameters:ALTER SYSTEM RESET timed_statistics scope=spfile sid='*';
This setting will not take effect until the database is restarted.At this point the affect of the statistics level can be shown using the following query:COLUMN statistics_name FORMAT A30 HEADING "Statistics Name"
COLUMN session_status FORMAT A10 HEADING "SessionStatus"
COLUMN system_status FORMAT A10 HEADING "SystemStatus"
COLUMN activation_level FORMAT A10 HEADING "ActivationLevel"
COLUMN session_settable FORMAT A10 HEADING "SessionSettable"SELECT statistics_name,
session_status,
system_status,
activation_level,
session_settable
FROM v$statistics_level
ORDER BY statistics_name;
A comparison between the levels can be shown as follows:SQL> ALTER SYSTEM SET statistics_level=basic;System altered.SQL> SELECT statistics_name,
2 session_status,
3 system_status,
4 activation_level,
5 session_settable
6 FROM v$statistics_level
7 ORDER BY statistics_name; Session System Activation Session
Statistics Name Status Status Level Settable
------------------------------ ---------- ---------- ---------- ----------
Buffer Cache Advice DISABLED DISABLED TYPICAL NO
MTTR Advice DISABLED DISABLED TYPICAL NO
PGA Advice DISABLED DISABLED TYPICAL NO
Plan Execution Statistics DISABLED DISABLED ALL YES
Segment Level Statistics DISABLED DISABLED TYPICAL NO
Shared Pool Advice DISABLED DISABLED TYPICAL NO
Timed OS Statistics DISABLED DISABLED ALL YES
Timed Statistics DISABLED DISABLED TYPICAL YES8 rows selected.SQL> ALTER SYSTEM SET statistics_level=typical;System altered.SQL> SELECT statistics_name,
2 session_status,
3 system_status,
4 activation_level,
5 session_settable
6 FROM v$statistics_level
7 ORDER BY statistics_name; Session System Activation Session
Statistics Name Status Status Level Settable
------------------------------ ---------- ---------- ---------- ----------
Buffer Cache Advice ENABLED ENABLED TYPICAL NO
MTTR Advice ENABLED ENABLED TYPICAL NO
PGA Advice ENABLED ENABLED TYPICAL NO
Plan Execution Statistics DISABLED DISABLED ALL YES
Segment Level Statistics ENABLED ENABLED TYPICAL NO
Shared Pool Advice ENABLED ENABLED TYPICAL NO
Timed OS Statistics DISABLED DISABLED ALL YES
Timed Statistics ENABLED ENABLED TYPICAL YES8 rows selected.SQL> ALTER SYSTEM SET statistics_level=all;System altered.SQL> SELECT statistics_name,
2 session_status,
3 system_status,
4 activation_level,
5 session_settable
6 FROM v$statistics_level
7 ORDER BY statistics_name; Session System Activation Session
Statistics Name Status Status Level Settable
------------------------------ ---------- ---------- ---------- ----------
Buffer Cache Advice ENABLED ENABLED TYPICAL NO
MTTR Advice ENABLED ENABLED TYPICAL NO
PGA Advice ENABLED ENABLED TYPICAL NO
Plan Execution Statistics ENABLED ENABLED ALL YES
Segment Level Statistics ENABLED ENABLED TYPICAL NO
Shared Pool Advice ENABLED ENABLED TYPICAL NO
Timed OS Statistics ENABLED ENABLED ALL YES
Timed Statistics ENABLED ENABLED TYPICAL YES8 rows selected.SQL>
Hope this helps. Regards Tim...
