• 1. Nov 3,2009                                infra-郭俊龙Oracle 10g 性能监控及日常维护
  • 2. Oracle 10g 性能监控及日常维护Oracle10g awr工具使用及分析 Oracle数据库日常维护
  • 3. Oracle AWR Oracle Database 10g 提供了一个显著改进的工具:自动工作负载信息库 (AWR:Automatic Workload Repository)。Oracle 建议用户用这个取代 Statspack。AWR 实质上是一个 Oracle 的内置工具,它采集与性能相关的统计数据,并从那些统计数据中导出性能量度,以跟踪潜在的问题。与 Statspack 不同,快照由一个称为 MMON 的新的后台进程及其从进程自动采集数据。 $ ps -ef|grep mmon oracle 284072 1 0 Oct 21 - 20:10 ora_mmon_htxxsvc2
  • 4. Oracle AWR    10g中一个新视图v$session_wait_history这个视图保存了每个活动session在v$session_wait中最近10次的等待事件。但这对于一段时期内的数据性能状况的监测是远远不够的,为了解决这个问题,在10g中还新添加了一个视图:v$active_session_history,这就是ASH,ASH缺省每一秒收集一下v$session中活动会话的情况,记录会话等待的事件,不活动的会话不会被采样 ,间隔时间由 _ash_sampling_interval 参数确定 ,由于记录session的活动信息是很费时间和空间的,ASH采用的策略是:保存处于等待状态的活动session的信息,每秒从v$session_wait中采样一次,并将采样信息保存在内存中(ASH的采样数据是保存在内存中)。
  • 5. Oracle AWR    ASH的采样数据是保存在内存中,而分配给ASH的内存空间是有限的,当所分配空间占满后,旧的记录就会被覆盖掉;而且数据库重启后,所有的这些ASH信息都会消失。这样,对于长期检测oracle的性能是不可能的,在Oracle10g中,提供了永久保留ASH信息的方法,这就是AWR, 由于全部保存ASH中的信息是非常耗费时间和空间的,所以AWR采用的策略是:MMON进程每小时对ASH 进行采样一次,并将信息保存到磁盘中,如内存不足,ASH BUFFER满的话MMNL进程就会主动写出,并保留7天,7天后旧的记录才会被覆盖。这些采样信息被保存在表wrh$_active_session_history中。 SQL> select pool, name, bytes/1024/1024 From v$sgastat where name like '%ASH %';     POOL   NAME BYTES/1024/1024     ------------  --------------------------    ---------------     shared pool  ASH buffers 15.5M
  • 6. Oracle AWR      其实,AWR记录的信息不仅是ASH,还可以收集到数据库运行的各方面统计信息和等待信息,用以诊断分析。AWR的采样方式是,以固定的时间间隔为其所有重要的统计信息和负载信息执行一次采样,并将采样信息保存在AWR中。可以这样说:ASH中的信息被保存到了AWR中的视图wrh$_active_session_history中。ASH是AWR的真子集。
  • 7. Oracle AWR    AWR 用几个表来存储采集的性能统计数据,所有的表都存储在 SYSAUX 表空间中的 SYS 模式下,并且以 WRM$_*(5个) 和 WRH$_*(94个)的格式名。 WRM$_*这种类型存储元数据信息(如检查的数据库和采集的快照), WRH$_*这种类型保存实际采集的统计数据。H代表“历史数据 (historical)”,而 M 代表“元数据 (metadata)”。在这些表上构建了几种带前缀 DBA_HIST_ 的视图(dba_hist_snapshot,dba_hist_baseline等),这些视图可以用来编写您自己的性能诊断工具。
  • 8. Oracle AWR 为了节省空间,系统默认采集的数据在 7 天后自动清除。快照频率和保留时间都可以由用户修改:   查看当前的AWR保存策略:    SQL> col SNAP_INTERVAL format a20    SQL> col RETENTION format a20    SQL> select * from dba_hist_wr_control;   DBID    SNAP_INTERVAL RETENTION TOPNSQL   ----------    --------------------   --------------------   ----------   2774909533 +00000 01:00:00.0 +00007 00:00:00.0 DEFAULT   以上结果表示,每小时产生一个SNAPSHOT,保留7天。
  • 9. Oracle AWR AWR配置: 1. 调整AWR产生snapshot的频率和保留策略 AWR配置都是通过dbms_workload_repository包进行配置,如将收集间隔时间改为30 分钟一次,并且保留31天时间(单位都是分钟): SQL> exec dbms_workload_repository.modify_snapshot_settings (interval=>30, retention=>31*24*60); 2. 关闭AWR: 把interval设为0则关闭自动捕捉快照: SQL> exec dbms_workload_repository.modify_snapshot_settings(interval=>0);
  • 10. Oracle AWR 3. 手工创建一个快照: SQL> exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();   如果数据库的某些参数或应用程序做了调整,可以手工即时的创建一个快照更有利于数据的统计分析。 4. 查看快照:  SQL> select * from sys.wrh$_active_session_history   
  • 11. Oracle AWR5. 手工删除指定范围的快照 SQL> exec DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id => 2889, high_snap_id => 3000, dbid => 2774909533); 6. 生成awr报告: awrrpt.sql :生成指定快照区间的统计报表; awrrpti.sql :生成指定数据库实例,并且指定快照区间的统计报表; awrsqlrpt.sql :生成指定快照区间,指定SQL语句(实际指定的是该语句的SQLID)的统计报表; awrsqrpi.sql :生成指定数据库实例,指定快照区间的指定SQL语句的统计报表; awrddrpt.sql :指定两个不同的时间周期,生成这两个周期的统计对比报表; awrddrpi.sql :指定数据库实例,并指定两个的不同时间周期,生成这两个周期的统计对比报表;  
  • 12. Oracle AWRSQL>@$ORACLE_HOME/rdbms/admin/awrrpt.sql 它产生两种类型的输出:文本格式(类似于 Statspack 报表的文本格式但来自于 AWR 信息库)和默认的 HTML 格式(拥有到部分和子部分的所有超链接),从而提供了非常用户友好的报表。直接回车,生成html格式的awr:
  • 13. Oracle AWR 输入你想创建的最近快照天数:
  • 14. Oracle AWR列出近两天的实例的快照ID及时间:
  • 15. Oracle AWR输入快照起始,结束ID及awr报告的名称:
  • 16. Oracle AWR可能运行几秒到几分钟的时间就会将两个快照之间的统计信息输出到html中。
  • 17. Oracle AWR   AWR报告:
  • 18. Oracle AWRAWR报告的主要内容: 1.前言部分:    这是AWR报告的第一段,用于描述环境包括数据库名,DBID,数据库版本,是否为RAC节点,快照报告的采集时间等。 2. 综述部分: 包含等待事件段,Load Profile段,实例效率统计段,Shared Pool统计段,Cache Size段,其中最重要的是等待事件段,它告诉我们在快照时间内数据库遇到哪些性能瓶颈,它们将是性能调整或问题诊断的主要候选对象。
  • 19. Oracle AWR常见等待事件介绍: 1. db file sequential read 文件顺序读取 这一事件通常显示与单个数据块相关的读取操作(如索引读取)。如果这个等待事件比较显著,可能表示在多表连接中,表的连接顺序存在问题,可能没有正确的使用驱动表;或者可能说明不加选择地进行索引。   在大多数情况下我们说,通过索引可以更为快速的获取记录,所以对于一个编码规范、调整良好的数据库,这个等待很大是很正常的。但是在很多情况下,使用索引并不是最佳的选择,比如读取较大表中大量的数据,全表扫描可能会明显快于索引扫描,所以在开发中我们就应该注意,对于这样的查询应该进行避免使用索引扫描。
  • 20. Oracle AWR2. db file scattered read-DB 文件分散读取      这种情况通常显示与全表扫描相关的等待。当数据库进行全表扫时,基于性能的考虑,数据会分散(scattered)读入Buffer Cache。如果这个等待事件比较显著,可能说明对于某些全表扫描的表,没有创建索引或者没有创建合适的索引,我们可能需要检查这些数据表已确定是否进行了正确的设置。然而这个等待事件不一定意味着性能低下,在某些条件下Oracle 会主动使用全表扫描来替换索引扫描以提高性能,这和访问的数据量有关,在CBO 下Oracle 会进行更为智能的选择,在RBO 下Oracle 更倾向于使用索引。因为全表扫描被置于LRU(Least Recently Used,最近最少适用)列表的冷端(cold end),对于频繁访问的较小的数据表,可以选择把他们Cache 到内存中,以避免反复读取。当这个等待事件比较显著时,可以结合v$session_longops 动态性能视图来进行诊断,该视图中记录了长时间(运行时间超过6 秒的)运行的事物,可能很多是全表扫描操作(不管怎样,这部分信息都是值得我们注意的)。
  • 21. Oracle AWR3. db file parallel write DBWR专属等待事件    与其名称暗示相反,该事件不与任何并行DML操作相关,该等待事件属于DBWR进程,DBWR进程负责向数据文件写入脏数据块的唯一进程,即DBWR进程执行对使用SGA的所有数据库写入。阻塞该进程的是操作系统的IO子系统。当然DBWR进程的写入操作也会对同一磁盘操作的其他会话造成影响。     DBWR查找脏块的时机: 每隔三秒一次的查找;当前台提交需要清除缓冲区内容时;当满足_DB_LARGE_DIRTY_QUEUE / _DB_BLOCK_MAX_DIRTY_TARGET /FAST_START_MTTR_TARGET阈值。    缓慢的DBWR操作可以造成前台会话在write complete waits(前台不允许修改正在传输到磁盘的块)或free buffer waits(DBWR不能满足释放缓冲区的需求)事件上。如果平均等待时间大小10cs,则表明IO缓慢。如果不存在db file parallel write事件,很可能初始化参数disk_async_io=FALSE,这种情况一般发生在AIX和HPUX平台上。
  • 22. Oracle AWR 3. db file parallel write DBWR专属等待事件     DBWR将一组脏数据编成"写入批量组",然后发布多个IO请求以将"写入批量组"写入数据文件,然后以此事件等待直到IO请求都完成。但是,当使用异步IO时,DBWR不等待整个批量写入完成,仅等待一定百分比的IO操作完成后,就将空闲缓冲区推到LRU链以使其可用。 解决方法: 如果平均等待时间长,要选择使用正确的IO操作。如果数据文件在裸设备上,并且平台支持异步IO,请应该使用异步IO。如果数据文件位于文件系统上,则应该使用同步写入和直接IO。相关的初始化参数是DISK_ASYNCH_IO和FILESYSTEMIO_OPTIONS; 如果重做位于祼设备上,而数据文件位于文件系统上,则可以设置DISK_ASYNCH_IO=TRUE,FILESYSTEMIO_OPTIONS=DIRECTIO。使用这种方法可以获得对于祼设备使用异步IO,而对于文件系统使用直接IO的效果;使用DB_WRITER_PROCESSES选项产生多个DBWR进程。
  • 23. Oracle AWR4. PX Deq Credit: send blkd     这个等待事件是由于PQ之间通讯引起的,可以忽略。或者是由于PARALLEL 可能并不适合,虽然并行执行,但是工作的SLAVE很少,大多数在等待。   5. gc buffer busy      gc buffer busy等待事件的产生一般不是SQL写得有问题就是应用在RAC上的部署不合理引起,可能通过修改应用或表结构来改变。
  • 24. Oracle AWR 6.direct path read temp      与asynchronous disk operate有关,检查fuser /dev/async,kctune |grep aio_max_ops,show paramete disk_asynch_io 是否正常。继续检查v$session_wait,看file number 在不在dba_data_files中,如不在,问题可能发生在temp tablespace中,随即检查v$sort_usage,查看session,如与v$session_wait中“direct path read”中的session吻合。继续检查pga的使用情况select * from v$pagstat,看pga是否使用已较高,pga使用达到一定程度后,只能使用temp tablespace完成排序操作,由于temp tablespace使用的disk,出现direct path read 也是正常的。解决此问题的方法为使用较大的将workarea_size_policy更改为manual,加大sort_area_size,减少使用temp tablespace的使用。
  • 25. Oracle AWR7.log file sync-日志文件同步:          当一个用户提交或回滚数据时,LGWR将会话期的重做日志缓冲器写入到重做日志中。日志文件同步过程必须等待这一过程成功完成,为了减少这种等待事件,可以尝试一次提交更多的记录(频繁的提交会带来更多的系统开销),将重做日志置于较快的磁盘上,或交替使用不同物理磁盘上的重做日志,以降低归档对LGWR的影响。对于软RAID,一般来说不要使用RAID5,RAID5对于频繁写入得系统会带来较大的性能损失,可以考虑使用文件系统直接输入/输出,或使用裸设备,这样可以获得写入的性能提高。
  • 26. Oracle AWR8.read by other session       read by other session最重要的原因还是由于io的能力太差,一个io读所耗费的时间太长造成的,可能是两种情况,io负载过重,或者io配置太低,当然调整语句减少io读也是一种办法。   以上各点只是一些现象,具体的查看SQL语句,可能通过分析SQL语句的执行计划,查看相关表的索引情况,及SQL语句或存储过程本身合理性,来对相关语句进行相应的调整,通过表的存储结构来确定是否将表分区或存放到IO比较合适的物理位置。
  • 27. Oracle AWR例:   关于10月21号BOSS全省营业系统故障的处理及分析报告中:    数据库出现 enq: TX - allocate ITL entry 等待事件,某些SQL出现堵塞等待的现象。经确认,该异常为应用程序引发。可能是由于突然有大批量数据导入导致出现ITL的WAITS。该异常直接导致CRM程序出现异常。   
  • 28. Oracle AWRAWR报告的主要内容:  3.  SQL部分:    无效的SQL语句是性能不好的主要原因,这部分对这段时间区间内的SQL按照执行时间,逻辑读,磁盘读等指标进行了分类和排序,和STATSPACK不同的是,从AWR报告中可以直接查到该SQL的文本和发送请求的客户端进程信息,极大的提高了SQL分析的效率
  • 29. Oracle AWRSQL部分:  SQL ordered by Elapsed Time:记录了执行总和时间的TOP SQL(请注意是监控范围内该SQL的执行时间总和,而不是单次SQL执行时间 Elapsed Time = CPU Time + Wait Time)。    Elapsed Time(S): SQL语句执行用总时长,此排序就是按照这个字段进行的。注意该时间不是单个SQL跑的时间,而是监控范围内SQL执行次数的总和时间。单位时间为秒。Elapsed Time = CPU Time + Wait Time    CPU Time(s): 为SQL语句执行时CPU占用时间总时长,此时间会小于等于Elapsed Time时间。单位时间为秒。    Executions: SQL语句在监控范围内的执行次数总计。    Elap per Exec(s): 执行一次SQL的平均时间。单位时间为秒。    % Total DB Time: 为SQL的Elapsed Time时间占数据库总时间的百分比。    SQL ID: SQL语句的ID编号,点击之后就能导航到下边的SQL详细列表中,点击IE的返回可以回到当前SQL ID的地方。    SQL Module: 显示该SQL是用什么方式连接到数据库执行的,如果是用SQL*Plus或者PL/SQL链接上来的那基本上都是有人在调试程序。一般用前台应用链接过来执行的sql该位置为空。    SQL Text: 简单的sql提示,详细的需要点击SQL ID。
  • 30. Oracle AWRSQL部分: SQL ordered by CPU Time: 记录了执行占CPU时间总和时间最长的TOP SQL(请注意是监控范围内该SQL的执行占CPU时间总和,而不是单次SQL执行时间)。 SQL ordered by Gets: 记录了执行占总buffer gets(逻辑IO)的TOP SQL(请注意是监控范围内该SQL的执行占Gets总和,而不是单次SQL执行所占的Gets)。 SQL ordered by Reads: 记录了执行占总磁盘物理读(物理IO)的TOP SQL(请注意是监控范围内该SQL的执行占磁盘物理读总和,而不是单次SQL执行所占的磁盘物理读)。 SQL ordered by Executions: 记录了按照SQL的执行次数排序的TOP SQL。该排序可以看出监控范围内的SQL执行次数。 SQL ordered by Parse Calls: 记录了SQL的软解析次数的TOP SQL。说到软解析(soft prase)和硬解析(hard prase),就不能不说一下Oracle对sql的处理过程。 SQL ordered by Sharable Memory: 记录了SQL占用library cache的大小的TOP SQL。Sharable Mem (b):占用library cache的大小,单位是byte。 SQL ordered by Version Count: 记录了SQL的打开子游标的TOP SQL。 SQL ordered by Cluster Wait Time: 记录了集群的等待时间的TOP SQL
  • 31. Oracle AWRAWR报告的主要内容:   点击sql id可以看到具体的sql语句的内容,可以放到查看其具体的执行计划,分析语句的索引使用情况及cost的高低,以便调优sql语句。
  • 32. Oracle AWRAWR报告的主要内容:   针对单独的sql语句我们可以生成独立的awr报告,运行:   SQL>@?/rdbms/admin/awrsqrpt.sql
  • 33. Oracle AWR   例: 调整SQL语句:原语句     SELECT DISTINCT "A2"."SERV_ID", "A2"."AGREEMENT_ID", "A2"."ATTR_ID", "A2"."ATTR_VAL", "A2"."EFF_DATE", "A2"."EXP_DATE", "A2"."STATE", "A2"."STATE_DATE", "A2"."SERV_ATTR_SEQ_ID", "A2"."ATTR_TYPE", "A2"."ATTR_TYPE_ID", "A2"."OPER_SERIAL_NBR", "A2"."EFF_ACCT_MONTH", "A2"."EXP_ACCT_MONTH", "A2"."REGION_ID", "A2"."PARTITION_ID_ATTR", "A2"."PARTITION_ID_REGION", '' FROM "LS65_SID"."SERV_ATTR_T" "A2", "LS65_SID"."SERV_ATTR_T" "A1" WHERE "A2"."ATTR_TYPE" = 'LS_FLD_DOWN_RATE' AND "A2"."ATTR_TYPE" = "A1"."ATTR_TYPE" AND "A2"."SERV_ID" = "A1"."SERV_ID" AND "A2"."ATTR_VAL" <> "A1"."ATTR_VAL" AND "A2"."ATTR_ID" <> "A1"."ATTR_ID" AND "A2"."STATE" = '00A' AND TO_CHAR (“A2”.“EFF_DATE”, ‘yyyy-mm-dd’) =TO_CHAR (SYSDATE@!, ‘yyyy-mm-dd’) AND "A1"."ATTR_TYPE" = 'LS_FLD_DOWN_RATE‘       
  • 34. 将TO_CHAR (“A2”.“EFF_DATE”, ‘yyyy-mm-dd’) =TO_CHAR (SYSDATE@!, ‘yyyy-mm-dd’) 改为如下以提高语句的执行速度:     "A2"."EFF_DATE" >= TO_DATE (TO_CHAR(SYSDATE ,'yyyymmdd' ), 'yyyymmdd' ) AND     "A2"."EFF_DATE" < TO_DATE (TO_CHAR(SYSDATE ,'yyyymmdd' ), 'yyyymmdd' ) + 1
  • 35. Oracle AWR5.段统计部分:     告诉哪些段(包括表和索引)在快照期间经历最高的磁盘读操作,这些信息可以帮助我们决定是否需要重建索引,或对段进行分区来减少发生在这些数据文件上的I/O。   
  • 36. Oracle AWR 7. AWR数据导出/导入:     将awr迁移到其它数据库以便于以后分析,提供两个新工具来完成导出和迁移AWR数据的工作。 DBMS_SWRF_INTERNAL.AWR_EXTRACT用来导出数据, 其使用方法如下: Begin DBMS_SWRF_INTERNAL.AWR_EXTRACT ( dmpfile   => 'awr_20091103.dmp', dmpdir    => ‘AWR_DIR', bid       => 298817, eid       => 298855); end; 其中,dmpfile参数用于指定将要导出的AWR数据文件的名字,dmpdir是指定存放导出文件的目录路径,bid是起始快照编号,eid是结束快照编号。
  • 37. Oracle AWR7. AWR数据导出/导入: DBMS_SWRF_INTERNAL用来迁移AWR数据文件到其他数据库。导入AWR数据的过程分为两个步骤,首先使用DBMS_SWRF_INTERNAL.AWR_LOAD方法把数据导入到一个临时模式中,本例是AWR_TEMP(也可以自己定义名称),具体方法如下: begin DBMS_SWRF_INTERNAL.AWR_LOAD ( SCHNAME => ‘AWR_TEST', dmpfile => 'awr_20091103.dmp', dmpdir =>  ‘AWR_DIR'); end; 接下来把需要把AWR数据转移到SYS模式中,操作方法如下: exec DBMS_SWRF_INTERNAL.MOVE_TO_AWR (SCHNAME => ‘TEST'); 这样AWR数据的导入工作已经完成,可以使用这种方法建立一个专门存放AWR数据的数据库,用于集中管理和分析多个数据库实例的性能统计数据。
  • 38. Oracle AWR8. 收集AWR报告的级别:     AWR的行为受到初始化参数STATISTICS_LEVEL的影响。这个参数有三个值:        BASIC:awr统计的计算和衍生值关闭.只收集少量的数据库统计信息.     TYPICAL:   (默认值)只有部分的统计收集.他们代表需要的典型监控oracle数据库的行为.          ALL : 所有可能的统计都被捕捉. 并且有操作系统的一些信息.这个级别的捕捉应该在很少的情况下,比如你要更多的sql诊断信息的时候才使用.      SQL> show parameter statistics_level      NAME   TYPE VALUE       ------------------------------------ ----------- ------------------------------       statistics_level   string   TYPICAL
  • 39. Oracle AWR9. 基线介绍:    基线(baseline)是一种机制,可以在重要时间的快照信息集做标记。一个基线定义在一对快照之间,快照通过他们的快照序列号识别.每个基线有且只有一对快照。一次典型的性能调整实践从采集量度的基线集合、作出改动、然后采集另一个基线集合开始,可以比较这两个集合来检查所作的改动的效果。在 AWR 中,对现有的已采集的快照可以执行相同类型的比较。    Baseline记录了baseline所指定的快照ID,当维护awr的mmon进程在清除过期的快照时,在baseline中的快照则不会被删除,当数据库做了一段时间的调优后,awr可以用baseline保留的快照与当前系统的awr报告进行对比.
  • 40. Oracle AWR9. 基线介绍:    基线查询:      SQL> select *From dba_hist_baseline/wrm$_baseline;    基线创建:       SQL> exec dbms_workload_repository.create_baseline         (beginsnap_id,endsnap_id , 'baseline_name');    基线删除:       SQL> exec dbms_workload_repository.drop_baseline         (baseline_name=>'baseline_name', cascade=>true);
  • 41. Oracle 日常维护1. 从主机查看Oracle 数据库的进程:   $ ps -ef|grep ora_s    oracle 8450258 1 0 Aug 20 - 142:02 ora_smon_a2svc1   oracle数据库的日志:    位置查看:SQL> show parameter background_dump_dest    NAME TYPE VALUE    ------------------------------------ ----------- ------------------------------------------------------------    background_dump_dest string /home/oracle/app/oracle/admin/sidsvc/bdump     主要日志alert_sidsvc1.log,其中oracle内部错误,数据库启停,物理结构改变,默认参数修改,坏块,死锁等日志信息。
  • 42. Oracle 日常维护 操作系统级监控top sql(cpu/mem)脚本:montopsql.sh export ORACLE_SID=htxxsid1 SDT=`date +"%Y%m%d%H%M$S"` SDT=/tmp/gjl/$ORACLE_SID'_'$SDT SPIDS=`UNIX95= ps -e -o “pcpu,pid,vsz,args”|sort -rn +2|grep LOCAL=NO|head -10|awk ‘ORS=“,”{print “\047”$2“\047”}’` -----对内存排序,换为sort –rn 则是对cpu排序 echo $SPIDS | sed 's/\,$//g‘ echo 'SELECT v$session.paddr, v$session.sql_address, v$sqltext.hash_value,v$sqltext.piece, v$sqltext.sql_text,v$session.program FROM v$session, v$sqltext ' >> /tmp/gjl/tmp.sql echo 'WHERE EXISTS (SELECT \047\061\047 FROM v$process WHERE addr = v$session.paddr and (spid in (' >> /tmp/gjl/tmp.sql echo $SPIDS | sed 's/\,$//g' >> /tmp/gjl/tmp.sql echo '))) AND v$session.sql_address = v$sqltext.address AND v$session.sql_hash_value = v$sqltext.hash_value ORDER BY sql_address, hash_value, piece' >> /tmp/gjl/tmp.sql echo '/' >> /tmp/gjl/tmp.sql
  • 43. Oracle 日常维护sar 1 3 >> /tmp/gjl/tmpcpu.log sqlplus "/as sysdba" <> topsql.sql rm tmpcpu.log mv /tmp/gjl/topsql.sql $SDT.sql
  • 44. Oracle 日常维护3.查看哪个表被锁:   select oracle_username || ' (' || s.osuser || ')' username  ,s.sid || ',' || s.serial# sess_id,owner || '.' ||object_name object  ,object_type ,decode(l.block ,0, 'Not Blocking'1, 'Blocking',2, 'Global') status  ,decode(v.locked_mode ,0, 'None' ,1, 'Null' ,2, 'Row-S (SS)'  ,3, 'Row-X (SX)',4, 'Share',5, 'S/Row-X (SSX)',6, 'Exclusive', TO_CHAR(lmode)) mode_held  from v$locked_object v ,dba_objects d ,v$lock l ,v$session s  where v.object_id = d.object_id and v.object_id = l.id1 and v.session_id = s.sid  order by oracle_username,session_id;  可以用SQL>alter system kill session '344,890';来杀掉此会话 ;  如果杀不掉,就利用V$porcess里的spid这个系统进程号,在数据库的主机上用kill -9 spid干掉!
  • 45. Oracle 日常维护 4.知道PID查相应执行的SQL语句: SELECT /*+ ORDERED */ sql_text FROM v$sqltext a WHERE (a.hash_value, a.address) IN ( SELECT DECODE (sql_hash_value,0, prev_hash_value, sql_hash_value),DECODE (sql_hash_value, 0, prev_sql_addr, sql_address) FROM v$session b WHERE b.paddr = (SELECT addr FROM v$process c WHERE c.spid =2589108)) ORDER BY piece ASC;
  • 46. Oracle 日常维护5.识别‘低效执行’的SQL语句 SELECT EXECUTIONS , DISK_READS, BUFFER_GETS, ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio, ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,SQL_TEXT FROM V$SQLAREA WHERE EXECUTIONS>0AND BUFFER_GETS > 0 AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8 ORDER BY 4 DESC;
  • 47. Oracle 日常维护6. 查运行时间很长的SQL: SELECT Username, Sid, Opname, Round(Sofar * 100 / Totalwork, 0) || '%' AS Progress, Time_Remaining,Sql_Text FROM V$session_Longops, V$sql WHERE Time_Remaining <> 0 AND Sql_Address = Address AND Sql_Hash_Value = Hash_Value;
  • 48. Oracle 日常维护8.如何监控当前数据库谁在运行什么SQL语句:   select osuser, username, sql_text from v$session a, v$sqltext b    where a.sql_address =b.address order by address, piece; 9. 查共享池中SQL语句的数量及占用了多少内存,及每条SQL语句的平均消耗内存情况:   select bytes,sql_count,bytes/sql_count   from (select count(*) sql_count from V$sqlarea),v$sgastat   where name='sql area';
  • 49. Oracle 日常维护10. 查看内存排序量与磁盘排序量: select name,value from V$sysstat where name like '%sort%'; 11. 回滚段的争用情况 select name, waits, gets, waits/gets "Ratio" from v$rollstat a, v$rollname b where a.usn = b.usn; 12.找使用CPU多的用户session select a.sid,spid,status,substr(a.program,1,40)prog,a.terminal, osuser,value/60/100 value from v$session a,v$process b,v$sesstat c where c.statistic#=12 and c.sid=a.sid and a.paddr=b.addr order by value desc;
  • 50. Oracle 日常维护13. 监控 SGA 的命中率 select a.value + b.value "logical_reads", c.value "phys_reads", round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) "BUFFER HIT RATIO" from v$sysstat a, v$sysstat b, v$sysstat c where a.statistic# = 38 and b.statistic# = 39 and c.statistic# = 40; 14. 测量dictionary cache的命中率:V$rowcache select 1- (SUM(getmisses)/SUM(gets)) "Data Dictionary Hit Ratio" from V$rowcache; "Data Dictionary Hit Ratio"的值要 > 85%;
  • 51. Oracle 日常维护15. 测量library cache的命中率:V$librarycache select namespace,gethitratio,pinhitratio,reloads,invalidations from v$librarycache where namespace in ('SQLAREA','TABLE/PROCEDURE','BODY','TRIGGER');   SQL AREA部分的gethitratio 、pinhitratio要 > 90%。 16. 测量dictionary cache的命中率:V$rowcache select 1- (SUM(getmisses)/SUM(gets)) "Data Dictionary Hit Ratio“ from V$rowcache; "Data Dictionary Hit Ratio"的值要 > 85%。
  • 52. Oracle 日常维护17.测量Buffer Cache的命中率:   select 1-((physical.value - direct.value - lobs.value)/logical.value) "Buffer Cache Hit Ratio" from V$SYSSTAT physical, V$SYSSTAT direct,V$SYSSTAT lobs, V$SYSSTAT logical where physical.name = 'physical reads' And direct.name = 'physical reads direct' and lobs.name = 'physical reads direct (lob)' And logical.name = 'session logical reads'; "Buffer Cache Hit Ratio"的值要 > 90%。
  • 53. Oracle 日常维护18. 监视Redo Log Buffer的重试率(<1%) Select retries.value/entries.value "Redo Log Buffer Retry Ratio“   From V$sysstat retries, V$sysstat entries Where retries.name = 'redo buffer allocation retries' And entries.name = 'redo entries'; "Redo Log Buffer Retry Ratio"的值要 < 1%。 Select name,value from V$sysstat where name='redo log space requests'; 如果该值大,需要增加Redo Log Buffer。
  • 54. Oracle 日常维护 19. delete表之后恢复:   表delete了如果undo没有覆盖,可以用timestamp恢复:   SQL>select * from gjl as of timestamp(sysdate-10/1440);   恢复当前时间的前10分钟时刻表的内容!   或者:SQL> alter table gjl enable row movement;   SQL> flashback table gjl to timestamp(sysdate-1/1440);   表delete后空间不释放如果要释放采用如下方法:    SQL>alter table gjl deallocate unused     或者: SQL>alter table gjl move;
  • 55. Oracle 日常维护20. drop表的恢复:  表drop了,如果recycle bin中存在,可用flashback恢复:   SQL>show recyclebin 查看回收站中是否有drop表的信息.   SQL> flashback table gjl to before drop;     表的truncate是DDL语句, 一般无法恢复要慎重使用,在使用时一定不要非法中断,此命令执行后最好用select count(*) from tablename;确认一下是否成功删除干净。     truncate table gjl;此时会释放表空间的,如果不释放可用using storage,例如: truncate table gjl using storage;
  • 56. Oracle 日常维护附录:写一个高效的SQL语句注意: 1. from字句记录数多的表名写到最后:因为from后面跟的表名是从右向左顺序处理 2. where字句能排除大量记录的条件写到最后,连接条件尽量写到前面:因为where可也从后向前解析的。 3. 尽量减少访问数据库的次数,尽量用decode函数,利用rowid删除重复行,不要用*而用列名减少分析 4. 用truncate代替delete,尽量多用commit(可以释放:undo,redo log buffer,锁,管理前三项的内部花费) 5. 用where代替having,用exists代替in,用表连接代替exists,使用别名alias,使用内部定义的函数。 6.索引需要定期重构:alter index rebuild,避免在索引列上使用计算、使用not,用>=代替>
  • 57. Oracle 日常维护7.union-all代替union(union在输出前要排序),使用提示hints(/*FULL(a)*/ CACH/ALL_ROWS/USE_NL/USE_MERGE/USE_HASH/RULE/FIRST_ROW) 8 避免改变索引列的数据类型,用union代替or,用in代替or,不要用is(not)null在索引列上,带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL语句会启动SQL引擎执行耗费资源的排序(SORT)功能。 DISTINCT需要一次排序操作, 而其他的至少需要执行两次排序。 9 提高GROUP BY 语句的效率, 可以通过将不需要的记录在GROUP BY 之前过滤掉,使用较大的BUFFER(比如10MB)可以提高EXPORT和IMPORT的速度。总是将你的表和索引建立在不同的表空间内(TABLESPACES)。 确保数据表空间和索引表空间置于不同的硬盘上。
  • 58. Thank you!! AMDOCS