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内部表和视图和锁的应用机制还是有点模糊（能力经验）--恢复过程的交互操作影响不是很清楚！（风险很大）--提高系统交互和系统对数据库影响的理解！