大 db_block_size; 大; db_cache_size使用多個(gè)塊大小多個(gè)數(shù)據(jù)庫(kù)寫入(DBWR)進(jìn)程大sort_area_size大的在線重作日志Oracle; 對(duì)象在數(shù)據(jù)庫(kù)內(nèi)部(表和索引的設(shè)置可以減少物理磁盤IO)低pctused; pctuseed的值越小,隨后的sql插入中出現(xiàn)的io就越少低pctfree 假如設(shè)置了pctfree,以答應(yīng)在沒有分割的情況下擴(kuò)展所有行,那么在隨后的sql選擇中就會(huì)產(chǎn)生更少的磁盤io使用索引將表重新組織成簇行; 假如以最常使用索引的相同物理次序放置表3 oracle; sql 在sql語(yǔ)句內(nèi),有許多技術(shù)可以減少物理磁盤io使用索引或提示(hint)防止不必要的全表搜索使用位映射(bitmapped)索引應(yīng)用sql提示oracle; 內(nèi)部結(jié)構(gòu)和磁盤io;--查找稀疏表(自由表失去平衡!)select substr(dt.table_name,1,10) c3,ds.extentsc5,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;dtwhere --調(diào)整oracle數(shù)據(jù)庫(kù)實(shí)例接下來我們要調(diào)整oracle數(shù)據(jù)庫(kù)實(shí)例,以及查看所有影響性能的 參數(shù),配置和設(shè)定用STATAPACK檢測(cè)實(shí)例潛在的性能問題1.oracle實(shí)例概述2.調(diào)整oracle 數(shù)據(jù)緩沖區(qū)3.調(diào)整共享池概述4.調(diào)整庫(kù)高速緩存5.調(diào)整oracle排序6.調(diào)整回滾段7.oracle 9i RAM 內(nèi)存調(diào)整通常的角度看; oracle實(shí)例包括了兩個(gè)組件:; 系統(tǒng)全局區(qū)(SGA) 以及 oracle后臺(tái)進(jìn)程我們通常通過調(diào)整oracle參數(shù)來控制SGA和后臺(tái)進(jìn)程當(dāng)oracle啟動(dòng)時(shí) oracle就會(huì)使用malloc()命令去建立一個(gè)RAM內(nèi)存區(qū)域,這個(gè)SGA通常也稱為oracle區(qū)域oracle DBA 可以控制SGA的規(guī)模; 正確的SGA治理可以極大的影響性能盡管初始化參數(shù)成百上千但是只有很少的oracle9i參數(shù)對(duì)調(diào)整非常重要:buffer_pool_keep這個(gè)數(shù)據(jù)緩沖池用于存儲(chǔ)執(zhí)行全表掃描的小表buffer_pool_recycle這個(gè)池用來保存進(jìn)行全表掃描的非常大的表的表塊db_cache_size;這個(gè)參數(shù)會(huì)決定ORACLE; SGA; 中數(shù)據(jù)庫(kù)塊緩沖區(qū)的數(shù)量,它是oracle內(nèi)存的最重要的參數(shù)db_block_size 數(shù)據(jù)庫(kù)塊大小能夠?qū)π阅墚a(chǎn)生(作為一個(gè)一般的規(guī)則,塊尺寸越大,物理IO就越少,整體性能就越快)db_file_multiblock_read_count; 這個(gè)參數(shù)用于全表搜索或者大表范圍掃描的時(shí)候,進(jìn)行多塊讀入large_pool_szie 這是一個(gè)使用多線程服務(wù)器的時(shí)候,保留用于SGA使用的共享池中的非凡區(qū)域.最大池也用于并行查詢RAM進(jìn)程log_buffer這個(gè)參數(shù)會(huì)決定為oracle重作日志緩沖區(qū)分配的內(nèi)存數(shù)量.假如具有大量的更新活動(dòng)，就應(yīng)該給log_buffer分配更多的空間shared_pool_size這個(gè)參數(shù)會(huì)定義系統(tǒng)中所有用戶的共享池,包括SQL區(qū)域和數(shù)據(jù)字典高速緩存.--有三個(gè)oracle參數(shù)可以影響數(shù)據(jù)緩沖區(qū)的大小db_cache_sizebuffer_pool_keepbuffer_pool_recycleoracle建議緩沖區(qū)的命中率要超過90％ DBA可以通過給初始化參數(shù)增加數(shù)據(jù)塊數(shù)量來控制數(shù)據(jù)緩沖區(qū)命中率數(shù)據(jù)庫(kù)緩沖池的內(nèi)部結(jié)構(gòu)--使用statspack監(jiān)視緩沖池的使用--緩沖池命中率和statpackselect * from stats$buffer_pool_statisticsSGA_MAX_SIZE=6000MDB_BLOCK_SIZE=16384DB_CACHE_SIZE=5000MBUFFER_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);--高級(jí)KEEP池候選識(shí)別除了進(jìn)行全表掃描的小表之外，keep緩沖池還非常適合放置頻繁使用的數(shù)據(jù)段的數(shù)據(jù)塊--使用x$bh視圖來識(shí)別平均塊接觸次數(shù)超過5次，并且在緩存中占用超過20個(gè)數(shù)據(jù)塊的對(duì)象--hot_buffer.sql--識(shí)別熱點(diǎn)對(duì)象;select object_typemytype,object_name;myname ,blocks,count(1) buffers,avg(tch) avg_toUChesfromsys.x$bh a,dba_objects; b,dba_segments swhere a.obj=b.object_idand b.object_name=s.segment_nameand b.owner; not in('SYS','SYSTEM')GROUP BY object_name,object_type,blocks,objhaving; avg(tch)>5and count(1)>20;識(shí)別出熱點(diǎn)對(duì)象后，可以決定將對(duì)象隔離放入keep池中作為一般的規(guī)則，應(yīng)該有足夠的RAM存儲(chǔ)可以用于整個(gè)表或者索引列如，假如希望為keep池增加頁(yè)表，就需要給init.ora的buffer_pool_keep; 參數(shù)增加104個(gè)數(shù)據(jù)塊--調(diào)整 recycle 池在recycle池放置對(duì)象的目標(biāo)是將全表搜索頻率的大表進(jìn)行分離，為了找到進(jìn)行全表搜索的大表，我們必須求助于從Access.sql中獲得的全表搜索報(bào)告：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_namenamefrom plan_table where operation='TABLE ACCESS' and options='FULL') pwhere s.addr':'TO_CHAR(s.hashval)=p.stidand t.table_name=p.nameand t.owner=p.ownerand t.buffer_pool<>'RECYCLE'having; s.blocks>1000group by p.owner,p.name,t.num_rows,s.blocksorder by sum(s.executions) desc;--給表分配recycle池altertableuser.table_name storage(buffer_pool; recycle);注重：在將任何表加入到RECYCLE池之前，DBA都應(yīng)該抽取sql源代碼，并且驗(yàn)證這個(gè)查詢是否獲取超過了表中行的40％--高級(jí)recycle池調(diào)整下列查詢使用了x$bh.tch來識(shí)別具有一次緩沖區(qū)接觸計(jì)數(shù)，但是總量超過了整個(gè)緩存的5％的數(shù)據(jù)緩存中的對(duì)象，這些數(shù)據(jù)段是潛在的在recycle緩沖池中放置的候選對(duì)象，因?yàn)樗麄兛赡軙?huì)讓不會(huì)重用的數(shù)據(jù)塊占用大量的緩存空間select object_type; mytype,object_name; myname,blocks,count(1) buffers,100*(count(1)/totsize); pct_cachefrom sys.x$bha,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_levelSHOW PARAMETER timed_statisticsOracle 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_settableFROMv$statistics_levelORDER 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; FROMv$statistics_level 7; ORDER BY statistics_name;;;;;Session;System;;Activation SessionStatistics Name;;;;;Status;;Status;;Level;;;Settable------------------------------ ---------- ---------- ---------- ---------- Buffer Cache Advice;DISABLEDDISABLEDTYPICAL;NOMTTR Advice;DISABLEDDISABLEDTYPICAL;NOPGA Advice;;DISABLEDDISABLEDTYPICAL;NOPlan Execution Statistics;;;DISABLEDDISABLEDALL;;;;;YESSegment Level Statistics;;;;DISABLEDDISABLEDTYPICAL;NOShared Pool Advice;;DISABLEDDISABLEDTYPICAL;NOTimed OS Statistics;DISABLEDDISABLEDALL;;;;;YESTimed Statistics;;;;DISABLEDDISABLEDTYPICAL;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; FROMv$statistics_level 7; ORDER BY statistics_name;;;;;Session;System;;Activation SessionStatistics Name;;;;;Status;;Status;;Level;;;Settable------------------------------ ---------- ---------- ---------- ----------Buffer Cache Advice;ENABLED;ENABLED;TYPICAL;NOMTTR Advice;ENABLED;ENABLED;TYPICAL;NOPGA Advice;;ENABLED;ENABLED;TYPICAL;NO Plan Execution Statistics;;;DISABLEDDISABLEDALL;;;;;YESSegment Level Statistics;;;;ENABLED;ENABLED;TYPICAL;NOShared Pool Advice;;ENABLED;ENABLED;TYPICAL;NOTimed OS Statistics;DISABLEDDISABLEDALL;;;;;YESTimed 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; FROMv$statistics_level 7; ORDER BY statistics_name;;;;;Session;System;;Activation SessionStatistics Name;;;;;Status;;Status;;Level;;;Settable------------------------------ ---------- ---------- ---------- ----------Buffer Cache Advice;ENABLED;ENABLED;TYPICAL;NOMTTR Advice;ENABLED;ENABLED;TYPICAL;NOPGA Advice;;ENABLED;ENABLED;TYPICAL;NOPlan Execution Statistics;;;ENABLED;ENABLED;ALL;;;;;YESSegment Level Statistics;;;;ENABLED;ENABLED;TYPICAL;NOShared Pool Advice;;ENABLED;ENABLED;TYPICAL;NO Timed OS Statistics;ENABLED;ENABLED;ALL;;;;;YESTimed Statistics;;;;ENABLED;ENABLED;TYPICAL;YES8 rows selected.SQL>Hope this helps. Regards Tim...