ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle 小结

oracle 小结

原创 Linux操作系统 作者:47328983 时间:2012-04-15 14:04:24 0 删除 编辑
1 buffer cache 中有两个队列,一个是free buffer list,当数据被修改后,状态
变为dirty,移动到dirty list,dirty list上的都是侯选的可以被DBWR写出
到数据文件的buffer

2 buffer cache会存在latch竞争

3 oracle还提供了buffer bucket结构,就象图书馆寻找图书的索引卡片,用于快速判断数据块是否在
buffer cache中,抽屉中的每张卡片就是一个buffer header,实际上是以链表的形式存在闩在一起(cache
buffer chain),这就会产生cache buffer chain竞争,而增加bucket一点,可以快速进行搜索,但也
不是增加得越多越就越好

4 从8I后,每个buffer latch需要管理多个bucket,每个bucket上的buffer数量降低,latch性能提升

5 查询当前数据库最繁忙的buffer
    在x$bh中有个字段tch ,表明一个buffer的访问次数,越多越表明存在竞争:

SELECT *
  FROM (SELECT   addr, ts#, file#, dbarfil, dbablk, tch
            FROM x$bh
        ORDER BY tch DESC)
WHERE ROWNUM < 11;

再查询出这些热点buffer来自哪些对象:

SELECT e.owner, e.segment_name, e.segment_type
  FROM dba_extents e,
       (SELECT *
          FROM (SELECT   addr, ts#, file#, dbarfil, dbablk, tch
                    FROM x$bh
                ORDER BY tch DESC)
         WHERE ROWNUM < 11) b
WHERE e.relative_fno = b.dbarfil
   AND e.block_id <= b.dbablk
   AND e.block_id + e.blocks > b.dbablk;

  找出热点对象的相关信息:
SELECT distinct e.owner, e.segment_name, e.segment_type
           FROM dba_extents e,
                (SELECT *
                   FROM (SELECT   addr, ts#, file#, dbarfil, dbablk, tch
                             FROM x$bh
                         ORDER BY tch DESC)
                  WHERE ROWNUM < 11) b
          WHERE e.relative_fno = b.dbarfil
            AND e.block_id <= b.dbablk
            AND e.block_id + e.blocks > b.dbablk;



6 SHARD POOL的基本原理:
     通过shard pool实现SQL共享,减少代码硬解析等,从而提高数据库的性能。包括了库缓存(v$librarychace视图,
保存了SQL语句执行计划,执行的代码块等)
,数据字典缓存(v$rowcache)查询。
   从11G开始多了一个result cache memory,主要是将查询的结果集缓存起来,如果同一SQL多次执行,就把结果直接返回。

服务端的result cache memory由两部分组成:sql query result cache,存储SQL查询的结果集
通过参数result_cache_max_size设置大小,如果=0,则禁止该特性。
  设置有auto(自动),manual(通过查询hints判断),设置force(尽可能缓存)结果
比如
    select /*+ result_cache */ ...............
   查看cache 使用情况
    select * from v$result_cache_memory where free='no';
查询result cache的统计信息
     select * from v$result_cache_statisitcs;

7 oracle 9i中,增加了子缓冲池的管理,shared pool划分为多个子缓冲池,9I中每个子缓冲池至少为128MB,SUBPOOL
最多有7个;在10G中,每个子缓冲为256MB,每4个CPU分配一个子缓冲。11G中,每个至少为512MB

8 oracle中,如果SQL语句大小写不同,也会认为是不同的SQL,不会重用

9 ORA-04031错误:当尝试分配大块连续内存失败(碎片过多),先清除共享池中当前没用的所有对象,是空闲块合拼,如果
依然没足够内存满足需要,产生该错误

10 查询硬解析次数
  1)select name,value from v$mystat a,v$statname b where a.statistic#=b.statistic# and
name lile '%parse%';
  JAVA程序中使用preparestament 绑定变量;或者服务端的参数cursor_sharing,默认为exact,还有force,simliar.

11) 调整日志文件大小
   alter database add logfile group 4 'xxxxxx/xxx.dbf' size 1m;
  再强制切换日志
     alter system switch logfile;
  将当前status为inactive的日志组删除
     alter database drop logfile group 1;

12) REDO日志恢复,丢失非活动日志组的恢复
       如果丢失非活动(inactive)日志组,由于已经完成了检查点,数据库不会发生数据损失,通过clear重建日志组即可恢复
      清除该日志组即可启动
       alter database clear logfile group 2(假设2是有问题的日志组)
       alter database open;
     如果为归档模式,且该日志组没完成归档则需要使用如下命令强制清除
      alter database clear unarchived logfile group 2;



13) 在statkspack报告的开头,有rollback per transcation(事务回滚率)=rollbaks/(commit+rollbacks),如果比例太高,
证明回滚高,性能不大好

14)回滚段中:对于insert操作,回滚段只记录插入记录的rowid,如果回退,则只需要将该记录根据rowid删除即可;
对于update,回滚段只需要记录旧值即可;对于delete, 必须记录整行的数据,因此产生的undo最多。

15)从9I开始自动管理UNO表空间
    show parameter undo;
    其中undo_retention:表示当回滚段变非激活后,数据在被覆盖前保留多少秒;
   查询回滚段信息
    select * from v$rollname;默认创建10个回滚段;
从10G开始,在share pool中开独立的区域存放undo信息,增强效率;
    在11G开始,RMAN UNO的新特性,可以在备份UNDO空间时,已经提交了的UNO信息将不再备份,增强效率;
 
16) 当更新完数据后,在事务提交后,orcle要清除掉在数据块上的存储了的ITL和锁等信息,叫块清除;
如果提交事务的时候,修改过的数据块已经被写回到数据文件上,再读出修改资源昂贵,选择延迟清除,等到
下一次访问该数据块的时候再来清除。
   更新完后,强制将buffer cache的写到数据文件
   alter session set events='immediate trace name flush_cache';
   commit;
17) Oracle ORA-01555快照过旧的错误
   假设有一张6000万行数据的testdb表,预计testdb全表扫描1次需要2个小时,参考过程如下:
1、在1点钟,用户A发出了select * from testdb;此时不管将来testdb怎么变化,正确的结果应该是用户A会看到在1点钟这个时刻的

内容。
2、在1点30分,用户B执行了update命令,更新了testdb表中的第4100万行的这条记录,这时,用户A的全表扫描还没有到达第4100万

条。毫无疑问,这个时候,第4100万行的这条记录是被写入了回滚段,假设是回滚段UNDOTS1,如果用户A的全表扫描到达了第4100万

行,是应该会正确的从回滚段UNDOTS1中读取出1点钟时刻的内容的。
3、这时,用户B将他刚才做的操作提交了,但是这时,系统仍然可以给用户A提供正确的数据,因为那第4100万行记录的内容仍然还在

回滚段UNDOTS1里,系统可以根据SCN到回滚段里找到正确的数据,但要注意到,这时记录在UNDOTS1里的第4100万行记录已经发生了重

大的改变:就是第4100万行在回滚段UNDOTS1里的数据有可能随时被覆盖掉,因为这条记录已经被提交了!
4、由于用户A的查询时间漫长,而业务在一直不断的进行,UNDOTS1回滚段在被多个不同的transaction使用着,这个回滚段里的

extent循环到了第4100万行数据所在的extent,由于这条记录已经被标记提交了,所以这个extent是可以被其他transaction覆盖掉的


5、到了1点45分,用户A的查询终于到了第4100万行,而这时已经出现了第4条说的情况,需要到回滚段UNDOTS1去找数据,但是已经被

覆盖掉了,这时就出现了ORA-01555错误


   解决方法:1、扩大回滚段,因为回滚段是循环使用的,如果回滚段足够大,那么那些被提交的数据就能保存足够长的时间,使那

些大事务完成一致性读取
    2  增加undo_retention时间,因为UNDO回滚段是循环使用,里面的数据可能随时被循环覆盖掉,如果设置undo_retention时间更

长,那么在retention规定的时间内,任何其他事务都不能覆盖这些数据
   3  最重要的一点就是优化程序相关查询语句,减少查询语句的一致性读,降低读取不到回滚段数据的风险。


18)  oracle 中的块清除:
    数据库块的最前面有一个“开销”空间(overhead),这里会存放该块的一个事务表,对于锁定了该块中某些数据的各个“实际”

事务,在这个事务表中都有一个相应的条目。->http://blog.csdn.net/fw0124/article/details/6899223
1)首先当一个事务开始时,需要在回滚段事务表上分配一个事务槽;
2)在数据块头部获得一个ITL事务槽,该事务槽指向回滚段段头的事务槽;
3)在修改数据之前,需要在回滚段中记录前镜像信息,回滚段头事务槽指向该记录;
4)  锁定修改行,修改行锁定位(lb-lock block)指向ITL事务槽;
5)  数据修改可以进行。
COMMIT时候Oracle需要将回滚段上的事务表信息标记为非活动,以便空间可以重用;此外所做的一个操作是块清除(Block cleanout

),如果事务修改的某些块还在缓冲区缓存中,会清除块首部的ITL事务信息(包括提交标志、SCN等)和锁定信息。

在与我们的事务相关的提交列表中,Oracle会记录已修改的块列表(每个列表可以有20个块指针),Oracle会根据需要分配多个这样

的列表,直至达到某个临界点。如果我们修改的块加起来超过了块缓冲区缓存大小的10%,Oracle 会停止为我们分配新的列表。例如

,如果缓冲区缓存设置为可以缓存3,000个块,Oracle 会为我们维护最多300个块。
COMMIT时,Oracle会通过这些列表找到块,如果块仍在块缓冲区中,Oracle会执行一个很快的清理,这叫做快速块清除(FAST BLOCK

CLEANOUT)。
所以,只要我们修改的块数没有超过缓存中总块数的10%,而且块仍在块缓存区中(如果已经被写回到数据文件上再次读出该数据块进

行修改成本过于昂贵),Oracle就会在COMMIT时清理这些块。否则,就会延迟块清除到下次访问该块的时候。通过延迟块清除

(DELAYED BLOCK CLEANOUT)可以提高数据库的性能,加快提交操作。

所以如果执行一个大的INSERT、UPDATE或DELETE,影响数据库中的许多块,就有可能在此之后,第一个“接触”块的查询会需要修改

某些块首部并把块弄脏,生成REDO日志,会导致DBWR把这些块写入磁盘。
如果Oracle不对块完成这种延迟清除,那么COMMIT的处理可能很长,COMMIT必须重新访问每一个块,可能还要从磁盘将块再次读入(

它们可能已经刷新输出)。

在一个OLTP系统中,可能从来不会看到这种情况发生,因为OLTP系统的特点是事务都很短小,只会影响为数不多的一些块。
如果你有如下的处理,就会受到块清除的影响:
· 将大量新数据批量加载到数据仓库中;
· 在刚刚加载的所有数据上运行UPDATE(产生需要清理的块);
· 让别人查询这些数据
比较好的做法是:在批量加载了数据后,通过运行DBMS_STATS实用程序来收集统计信息,就能自然的完成块清除工作。


19) oracle 11g中,提供了对UNDO数据的归档,闪回数据归档,新增的后台进程FBDA用于对闪回数据进行归档
ps -ef|grep fbda|grep -v grep


20) 等待事件
   1 v$session:记录数据库当前连接的session信息;v$session_wait视图记录的是当前数据库连接的活动session正在等待的
资源或事件信息,但10G起,很多东西都合拼到v$session中了。
       v$system_event记录的是整体情况,记录数据库自启动以来的所有等待信息
 
   2  oracle 10g中,引入了v$event_histogram,可以看到等待事件的分布,比如
        select event,wait_time_milli,wait_count from v$event_histogram where event='latch: shard pool';
   3 oracle 11g中实时SQL监控:超过5秒的SQL记录在v$sql_monitor视图中。但要强制开statistics_level为TYPICAL和ALL
   4 强制对某个SQL实时监控
      select /*+ monitor */ from xxxxxxx
      select /*+no monitor */ from xxxxxxx
   5 select * from v$session_wait ;//获得各进程的等待事件
   6 捕捉慢的SQL:
     SELECT   sql_text
    FROM v$sqltext a
   WHERE a.hash_value = (SELECT sql_hash_value
                           FROM v$session b
                          WHERE b.SID = '&sid')
ORDER BY piece ASC
    输入已知的sesssion的ID
(select sid from v$mystat where rownum=1)
获得最慢的SQL
   

7 从10G开始,新增加了v$session_wait_history视图,记录了最近10次的等待事件,
    select * from v$session_wait_history where sid=120;
  8 ASH报告,每秒采集1次活动会话,通过SGA中分配内存
  9 ASH调用方式:/rdbms/admin/ashrpt.sql 脚本即可生成,OEM方式也可以看到ASH报告

20) 重要的等待事件
   1 db file sequential read(数据文件顺序读取)
       读取一个索引块或通过索引读取时,都会有这个等待。P1代表oracle要读取的文件的绝对文件号;
P2代表ORACLE从这个文件中开始读取的起始数据块号,P3读取BLOCK数量。
     如果这个等待事件比较显著,可能表示多表连接中,连接顺序存在问题,或者索引存在问题;

   2)db file scattered read
       表明用户进程正在读数据到buffer cache中,其实表示FULL SCAN时,读数据到buffer cache时,连续的
数据在内存中的存储位置不连续,所以叫scattered read.一般来说,大量这样的事件可能是索引缺少。
可以结合v$session_longops(记录超过6秒的事务)来查看。

     获得全表扫描的语句:
    SELECT   sql_text
    FROM v$sqltext t, v$sql_plan p
   WHERE t.hash_value = p.hash_value
     AND p.operation = 'TABLE ACCESS'
     AND p.options = 'FULL'
ORDER BY p.hash_value, t.piece;

   3) direct path read write(直接路径读写)
       多发生在磁盘排序IO中,都是直接从PGA写或读数据到数据文件的。

  4)日志文件相关的等待
       log file switch (日志文件切换),包括log file switch(archiving needed)和log file switch(checkpoint imcomplete)
造成的日志组循环写满后,覆盖前的等待;
     log file sync(日志文件同步):LGWR写效率低,将缓冲区的REDO写到日志遇到等待,不要把
redo log file放RAID 5ZHONG  ,LGWR被过度频繁激活。

  5)log buffer space-日志缓冲空间
      当数据库产生日志的速度比LGWR写出速度快时,或者日志切换太小时,会出现该等待,表明redo log buffer过小,
可以考虑增大日志文件大小,或者增加日志缓冲区的大小,尽量使用RAID10放日志文件。

21 TX锁和TM锁
    1)TX锁叫事务锁,当执行DDL,DML时,先获得之,在行级获得的,每个数据行上都有一个锁定位,用来
判断数据是否被锁定,有一个ITL的数据结构,用来记录事务信息,只有排他锁而没有共享模式。
       TM叫表锁,通过LOCK获得,或者DML操作以及SELECT FOR UPDATE获得,可以防止其他进程对表加X排他锁。
可以通过v$lock来看这些信息,TYPE字段表示锁定的类型。

   LMODE含义  2 ROW-S(SS)
              3  ROX-X(SX)
              4  SHARE--S
              5  S/ROW-X (SSX)
              6  EXCLUSIVE (X)
     
   查看方法: select * from v$lock where sid=xxxxx;(select sid,username from v$session where username='scott');


22 Latch free(闩锁释放)
    latch是一低级的排队(串行)机制,保护SGA中的共享内存结构,是一快速被获取和释放的的内存锁,用来防止共
享内存结构被多个用户同时访问。
   willing to wait:如果请求的latch不能立即得到,请求进程等待很短时间后再次发出请求,一直重复直到得到LATCH
   IMMEDIATE:如果所请求的latch不能立即得到,请求进程不再等待,继续执行下去

  
23 10G起查看执行计划的步骤
   1)explain plan set statement_id='test'(随便给一个名) for sql 语句
  
   2)select plan_table_output from table(DBMS_XPLAN_DISPLAY('PLAN_TABLE','TEST','ALL'));
    3)还可以通过AWR报告中的SQLID去查
      select * from table(dbms_xplan.display_awr('sqlid'));


24  找SQL语句慢的一般步骤
    1)select * from v$session_wait;
    找出SID

   2) SELECT   sql_text
    FROM v$sqltext a
   WHERE a.hash_value = (SELECT sql_hash_value
                           FROM v$session b
                          WHERE b.SID = '&sid')
   输入上一步的SID

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/241379/viewspace-721207/,如需转载,请注明出处,否则将追究法律责任。

上一篇: BLUK COLLECT
请登录后发表评论 登录
全部评论

注册时间:2009-03-07

  • 博文量
    111
  • 访问量
    329835