Oracle 11G 記憶體內部管理全攻略

Oracle 11G 記憶體內部管理全攻略

1. 查詢資料庫快取區記憶體結構select * from x$bh;

--查詢熱快！select e.owner, e.segment_name, e.segment_type from dba_extents e, (select addr, ts#, file#, dbarfil, dbablk, tch from x$bh where rownum < 22 order by tch desc) bwhere e.relative_fno = b.dbarfil and e.block_id <= b.dbablk and e.block_id + e.blocks > b.dbablk

--查詢熱塊物件select a.* from v$sqltext a, (select e.owner, e.segment_name, e.segment_type from dba_extents e, (select addr, ts#, file#, dbarfil, dbablk, tch from x$bh where rownum < 22 order by tch desc) b where e.relative_fno = b.dbarfil and e.block_id <= b.dbablk and e.block_id + e.blocks > b.dbablk) b where a.SQL_TEXT like '%' || b.segment_name || '%'

select * from undo$;

這個是回滾的內部檢視

2. 查詢所有的資料庫內部字典資訊select * from v$fixed_table;

3. buffer cache原理，下面的兩個引數關係到快取取的應用闋值

select * from x$kvit where kvittag='kcbldq'; select * from x$kvit where kvittag='kcbfsp';

4. 資料庫快取取分配不合理

select * from v$latch a where name='cache buffers chains'; select a.gets, a.misses, a.immediate_gets, a.immediate_misses from v$latch_children a where name = 'cache buffers chains' order by 4 desc;

記憶體內部databuffer連結串列， 所有的BUFFER都要初始到連結串列上！select kvittag,kvitval,kvitdsc from x$kvit where kvittag='kcbldq';alter session set events 'immediate trace name buffers level 4';select * from v$latch where name = 'cache buffers lru chain';select * from v$latch_children where name ='cache buffers chain''_db_block_lru_latches'出現競爭一般解決辦法是增加 buffer'_db_bkock_hash_latches'鎖的競爭主要是熱點塊的競爭！任何競爭都是雙向的，要從兩點去解決問題和思考問題！競爭是由於檢視還是修改·！檢視內部的結構

5. 共享內部的資訊

select * from x$ksmsp; select count(*) from dba_objects; select count(*) from x$ksmsp; select * from x$ksmsp;

x$ksmsp 表資料的變化反映內部結構的變化和解析過程的進行,和碎片的程度!

--共享內部統計 select ksmchcom, sum(recr), sum(freeble), sum(sum) from (select a.ksmchcom, count(ksmchcom) chunk, decode(ksmchcls, 'rerc', sum(ksmchsiz), null) recr, decode(ksmchcls, 'freeabl', sum(ksmchsiz), null) freeble, sum(ksmchsiz) sum from x$ksmsp a group by ksmchcom, ksmchcls) group by ksmchcom; 這個查詢顯示了共享內部應用情況 ora-04031 :alter system flash shared_pool,這個是沒辦法的辦法但決不是好辦法! select * from v$shared_pool_reserved

由於9I共享LATCH的增加，競爭主要修改 ！alter session set events 'immediate trace name buffer level 10';select * from x$bh;select * from x$kvit;

shared poolalter session set events 'immediate trace name heapdump level 2'select * from v$latch_children where name ='shared pool';--共享池的使用和分配情況 ！select * from x$ksmsp;

6. 隱含引數是禁止日誌

_disable_logging這個引數在實際的過程千萬不要用

--系統擁有的日誌量！ select a.NAME,b.VALUE from v$statname a, v$mystat b where a.STATISTIC#=b.STATISTIC# and a.NAME='redo size';

“redo study ***********”redo改變向量，重做記錄，DBA 資料塊的地址！記錄了很多詳細的資訊，但是僅僅是在資料庫恢復的時候使用，如果資料庫物件向前回滾，怎樣？

redo valuesselect a.NAME, b.VALUE from v$statname a, v$mystat b where a.STATISTIC# = b.STATISTIC# and a.NAME = 'redo size';

select name, value from v$sysstat where name = 'redo size';select * from x$ksppsv

-- _log_io_size-- _wait_for_sync(事件內部操作)-- defer ! 可能引起等待select max(lebsz) from x$kccle;alter session set events 'immediate trace name redohdr level 10';--unix 工具 ：bdfsize

-完全沒有日誌 完全沒有回滾 都基本有了思路和解決辦法~ '_allow_resetlogs_corruption'select * from x$ksppi where ksppinm like '_allow%' or ksppinm like '%roll%'-透過上面的語句，修改對應的隱含引數！ 這個是沒有辦法的辦法 ！！

7. --“undo study ***********”--undo_retentionselect * from v$transaction;select * from v$rollstat;

--分配事務槽--（表的事務引數）--資料塊，事務槽，--資料轉化！16進位制，2進位制，10進位制select to_char('9','0x') from dual--在資料讀取的時候需要判斷回滾，出現異常怎樣解決！--直接切換回滾空間會不會讀取異常！！！！！！！--在有事務的時候確實會出現異常！ 暫時不知道怎樣解決問題！

--最好做資料檔案的備份！段的損壞不知道怎樣模擬！但是如果其他檔案沒有損壞可以做恢復資料select * from v$datafile;select * from v$recover_file;recover datafile 3alter database datafile 3 onlineselect * from impcms.t_ums_dataright

--操作步驟！--我把資料刪除了！但是沒有提交，恢復後的資料資料沒有刪除！事務處於掛起狀態！

select * from v$archived_log order by 11 desc;select * from v$thread

select * from v$transaction;select * from v$rollstat;select * from v$rollname;alter system dump undo header '_SYSSMU4_1238554601$'alter session set events 'immediate trace name flash_cache';

--強制重新整理buffer cache！alter system set events '10203 trace name context forever' scope=spfile;select * from v$rollstat;--使用_corrupted_rollback_segments 啟動資料庫後要重新建立資料庫！--怎樣理解所謂資料不一致！影響？？

--回滾的記憶體在SGA中，

select * from undo$--這裡邊記錄的時間和UNDO的關係到底是怎樣·但是裡有UNDO語句select * from x$ktuqqry where TABLE_OWNER='IMPCMS' AND TABLE_NAME='T_UMS_DATARIGHT'select min(commit_timestamp) from x$ktuqqry where TABLE_OWNER='IMPCMS' AND TABLE_NAME='T_UMS_DATARIGHT'；select count(*) from x$ktuqqryselect * from flashback_transaction_queryalter system set events '1555 trace name errorstack level 4'select * from flashback_transaction_query;select * from v$transaction;select * from v$rollstat;alter system dump undo header '_SYSSMU7_1238554601$';select * from dba_data_filesalter system dump datafile 3 block 1476

--orale內部檢視不要隨便查詢，不瞭解內部機制，會產生很多的內部效能問題！，--在資料庫不是很繁忙的時候 可以試著查詢，但是要儘量去過濾更多條件！使得可選擇性很高才可以

======================================================================

以下的尚未完全整理出來!

--研究轉儲檔案的辦法，根據轉儲檔案分析資料庫問題--alter session set events 'immedaite trace name buffers level4';

--！--buffer header!--alter session set events 'immediate trace name buffers level 10';

--熱點塊競爭！a.tch被使用的次數！select a.tch,a.* from x$bh a order by 1 desc ;select count(1) from v$bh;

--alter session set events 'immediate trace name heapdump level 2'

--這個資料是固定的！select * from v$latch_children where name='shared pool';

--

select * from v$session_wait a where a.EVENT like '%library%';select a.*from v$session a, x$kglpn bwhere a.SADDR = b.kglpnuseand b.kglpnhdl = '1CC8CE90'and b..kglpnmod <> 0;select * from v$session_wait where sid='123';select * from v$session where sid='123';select * from v$sqlarea where hash_value='1413697536'select * from x$kglob where kglhdadr='1CC8CE90'alter session set events 'immediate trace name LIBRARY_CACHE level 32';select * from x$kcccp;alter session set events 'immediate trace name CONTROLF level 10';select dbms_flashback.get_system_change_number from dual;--alter system dump logfile 'D:APPADMINISTRATORORADATABAK1REDO03.LOG';select a.CHECKPOINT_TIME,a.CHECKPOINT_CHANGE#,a.LAST_TIME,a.LAST_CHANGE# from v$datafile a;

select a.* ,b.*from x$ksppi a ,x$ksppcv bwhere a.indx=b.indx;select * from x$kvit;select * from gv$instance;select * from v$fixed_view_definition;select userenv('instance') from dual;select * from v$fixed_table;select * from X$KQFVI;

select * from v$buffer_pool ;

--檢視統計表select * from tab;select * from v$process;select * from v$logfilealter system dump logfile 'D:APPADMINISTRATORORADATABAK1REDO01.LOG';

--10046事件select * from v$archived_log;-- _log_io_sizeselect * from x$ksppsv;--查詢資料庫內部系統引數，這個對資料庫的深層的控制和資料庫的恢復等操作需要深入瞭解的檢視--下面的查詢是資料庫在線上日誌和線上混滾異常丟失恢復資料庫的兩個隱含引數，用時要特別注意！select ksppinm,ksppdesc from x$ksppi where ksppinm like '%resetlogs_%' or ksppinm like '%roll%';--研究，修改這些引數在做資料庫操作的時候會有怎樣的影響，尤其是隱含引數，一定要清楚·--保留現場，但是如果資料非常大的時候應該怎樣！select dump(9) from dual;--隱含引數要在pfile中操作！可以清楚select * from v$archived_log;select * from v$database_block_corruption;select * from v$database;--系統事件設定alter session set events '10046 trace name context forever';-- ora-00604-- spatial-- 內部檢視出現異常

--下面結合起來！

select * from v$session_wait;select * from v$event_name;--邏輯讀！相對於全表而言，降低掃描資料量！--對於oracle內部表和檢視和鎖的應用機制還是有點模糊（能力經驗）--恢復過程的互動操作影響不是很清楚！（風險很大）--提高系統互動和系統對資料庫影響的理解！