Oracle-动态性能视图学习笔记


[三思笔记]学习动态性能表 http://www.5ienet.com QQ:5454589 君三思 学习动态性能表 第一篇--v$sysstat 2007.5.23 按照 OracleDocument 中的描述,v$sysstat 存储自数据库实例运行那刻起就开始累计全 实例(instance-wide)的资源使用情况。 类似于 v$sesstat,该视图存储下列的统计信息: 1>.事件发生次数的统计(如:user commits) 2>.数据产生,存取或者操作的 total 列(如:redo size) 3>.如果 TIMED_STATISTICS 值为 true,则统计花费在执行操作上的总时间(如:CPU used by this session) v$sysstat 视图常用列介绍:  STATISTIC#: 标识  NAME: 统计项名称  VALUE: 资源使用量 该视图还有一列 class-统计类别但极少会被使用,各类信息如下: 1 代表事例活动 2 代表 Redo buffer 活动 4 代表锁 8 代表数据缓冲活动 16 代表 OS 活动 32 代表并行活动 64 代表表访问 128 代表调试信息 注意:Statistic#的值在不同版本中各不相同,使用时要用 Name 做为查询条件而不要以 statistic#的值做为条件。 使用 v$sysstat 中的数据 该视图中数据常被用于监控系统性能。如 buffer cache 命中率、软解析率等都可从该视 图数据计算得出。 该视图中的数据也被用于监控系统资源使用情况,以及系统资源利用率的变化。正因如 此多的性能数据,检查某区间内系统资源使用情况可以这样做,在一个时间段开始时创建一 个视图数据快照,结束时再创建一个,二者之间各统计项值的不同(end value - begin value) 即是这一时间段内的资源消耗情况。这是 oracle 工具的常用方法,诸如 Statspack 以及 BSTAT/ESTAT 都是如此。 为了对比某个区间段的数据,源数据可以被格式化(每次事务,每次执行,每秒钟或每 次登陆),格式化后数据更容易从两者中鉴别出差异。这类的对比在升级前,升级后或仅仅 想看看一段时间内用户数量增长或数据增加如何影响资源使用方面更加实用。 你也可以使用 v$sysstat 数据通过查询 v$system_event 视图来检查资源消耗和资源回收。 [三思笔记]学习动态性能表 http://www.5ienet.com QQ:5454589 君三思 V$SYSSTAT 中的常用统计 V$SYSSTAT 中包含多个统计项,这部分介绍了一些关键的 v$sysstat 统计项,在调优方 面相当有用。下列按字母先后排序: 数据库使用状态的一些关键指标:  CPU used by this session:所有 session 的 cpu 占用量,不包括后台进程。这项统计的单 位是百分之 x 秒.完全调用一次不超过 10ms  db block changes:那部分造成 SGA 中数据块变化的 insert,update 或 delete 操作数 这项 统计可以大概看出整体数据库状态。在各项事务级别,这项统计指出脏缓存比率。  execute count:执行的 sql 语句数量(包括递归 sql)  logons current:当前连接到实例的 Sessions。如果当前有两个快照则取平均值。  logons cumulative:自实例启动后的总登陆次数。  parse count (hard):在 shared pool 中解析调用的未命中次数。当 sql 语句执行并且该语 句不在 shared pool 或虽然在 shared pool 但因为两者存在部分差异而不能被使用时产生硬解 析。如果一条 sql 语句原文与当前存在的相同,但查询表不同则认为它们是两条不同语句, 则硬解析即会发生。硬解析会带来 cpu 和资源使用的高昂开销,因为它需要 oracle 在 shared pool 中重新分配内存,然后再确定执行计划,最终语句才会被执行。  parse count (total):解析调用总数,包括软解析和硬解析。当 session 执行了一条 sql 语 句,该语句已经存在于 shared pool 并且可以被使用则产生软解析。当语句被使用(即共享) 所 有数据相关的现有 sql 语句(如最优化的执行计划)必须同样适用于当前的声明。这两项统计 可被用于计算软解析命中率。  parse time cpu:总 cpu 解析时间(单位:10ms)。包括硬解析和软解析。  parse time elapsed:完成解析调用的总时间花费。  physical reads:OS blocks read 数。包括插入到 SGA 缓存区的物理读以及 PGA 中的直 读这项统计并非 i/o 请求数。  physical writes:从 SGA 缓存区被 DBWR 写到磁盘的数据块以及 PGA 进程直写的数据 块数量。  redo log space requests:在 redo logs 中服务进程的等待空间,表示需要更长时间的 log switch。  redo size:redo 发生的总次数(以及因此写入 log buffer),以 byte 为单位。这项统计显示 出 update 活跃性。  session logical reads:逻辑读请求数。  sorts (memory) and sorts (disk):sorts(memory)是适于在 SORT_AREA_SIZE(因此不需要 在磁盘进行排序) 的排序操作的数量。sorts(disk) 则是由于排序所需空间太大, SORT_AREA_SIZE 不能满足而不得不在磁盘进行排序操作的数量。这两项统计通常用于计 算 in-memory sort ratio。  sorts (rows): 列排序总数。这项统计可被'sorts (total)'统计项除尽以确定每次排序的列。 该项可指出数据卷和应用特征。  table fetch by rowid:使用ROWID 返回的总列数(由于索引访问或 sql语句中使用了'where rowid=&rowid'而产生)  table scans (rows gotten):全表扫描中读取的总列数  table scans (blocks gotten):全表扫描中读取的总块数,不包括那些 split 的列。  user commits + user rollbacks:系统事务起用次数。当需要计算其它统计中每项事务比率 [三思笔记]学习动态性能表 http://www.5ienet.com QQ:5454589 君三思 时该项可以被做为除数。例如,计算事务中逻辑读,可以使用下列公式:session logical reads / (user commits + user rollbacks)。 注:SQL 语句的解析有软解析 soft parse 与硬解析 hard parse 之说,以下是 5 个步骤: 1:语法是否合法(sql 写法) 2:语义是否合法(权限,对象是否存在) 3:检查该 sql 是否在共享池中存在 -- 如果存在,直接跳过 4 和 5,运行 sql. 此时算 soft parse 4:选择执行计划 5:产生执行计划 -- 如果 5 个步骤全做,这就叫 hard parse. 注意物理 I/O oracle 报告物理读也许并未导致实际物理磁盘 I/O 操作。这完全有可能因为多数操作系 统都有缓存文件,可能是那些块在被读取。块也可能存于磁盘或控制级缓存以再次避免实际 I/O。Oracle 报告有物理读也许仅仅表示被请求的块并不在缓存中。 由 V$SYSSTAT 得出实例效率比(Instance Efficiency Ratios) 下列是些典型的 instance efficiency ratios 由 v$sysstat 数据计算得来,每项比率值应该尽可能 接近 1:  Buffer cache hit ratio:该项显示 buffer cache 大小是否合适。 公式:1-((physical reads-physical reads direct-physical reads direct (lob)) / session logical reads) 执行: select 1-((a.value-b.value-c.value)/d.value) from v$sysstat a,v$sysstat b,v$sysstat c,v$sysstat d where a.name='physical reads' and b.name='physical reads direct' and c.name='physical reads direct (lob)' and d.name='session logical reads';  Soft parse ratio:这项将显示系统是否有太多硬解析。该值将会与原始统计数据对比以 确保精确。例如,软解析率仅为 0.2 则表示硬解析率太高。不过,如果总解析量(parse count total)偏低,这项值可以被忽略。 公式:1 - ( parse count (hard) / parse count (total) ) 执行: select 1-(a.value/b.value) from v$sysstat a,v$sysstat b Where a.name='parse count (hard)' and b.name='parse count (total)';  In-memory sort ratio:该项显示内存中完成的排序所占比例。最理想状态下,在 OLTP 系统中,大部分排序不仅小并且能够完全在内存里完成排序。 [三思笔记]学习动态性能表 http://www.5ienet.com QQ:5454589 君三思 公式:sorts (memory) / ( sorts (memory) + sorts (disk) ) 执行: select a.value/(b.value+c.value) from v$sysstat a,v$sysstat b,v$sysstat c where a.name='sorts (memory)' and b.name='sorts (memory)' and c.name='sorts (disk)';  Parse to execute ratio:在生产环境,最理想状态是一条 sql 语句一次解析多数运行。 公式:1 - (parse count/execute count) 执行: select 1-(a.value/b.value) from v$sysstat a,v$sysstat b where a.name='parse count (total)' and b.name='execute count';  Parse CPU to total CPU ratio:该项显示总的 CPU 花费在执行及解析上的比率。如果这 项比率较低,说明系统执行了太多的解析。 公式:1 - (parse time cpu / CPU used by this session) 执行: select 1-(a.value/b.value) from v$sysstat a,v$sysstat b where a.name='parse time cpu' and b.name='CPU used by this session';  Parse time CPU to parse time elapsed:通常,该项显示锁竞争比率。这项比率计算 是否时间花费在解析分配给 CPU 进行周期运算(即生产工作)。解析时间花费不在 CPU 周期 运算通常表示由于锁竞争导致了时间花费 公式:parse time cpu / parse time elapsed 执行: select a.value/b.value from v$sysstat a,v$sysstat b where a.name='parse time cpu' and b.name='parse time elapsed'; 从 V$SYSSTAT 获取负载间档(Load Profile)数据 负载间档是监控系统吞吐量和负载变化的重要部分,该部分提供如下每秒和每个事务的 统计信息:logons cumulative, parse count (total), parse count (hard), executes, physical reads, physical writes, block changes, and redo size. 被格式化的数据可检查'rates'是否过高,或用于对比其它基线数据设置为识别 system profile 在期间如何变化。例如,计算每个事务中 block changes 可用如下公式: db block changes / ( user commits + user rollbacks ) 执行: select a.value/(b.value+c.value) from v$sysstat a,v$sysstat b,v$sysstat c [三思笔记]学习动态性能表 http://www.5ienet.com QQ:5454589 君三思 where a.name='db block changes' and b.name='user commits' and c.name='user rollbacks'; 其它计算统计以衡量负载方式,如下:  Blocks changed for each read:这项显示出 block changes 在 block reads 中的比例。它将 指出是否系统主要用于只读访问或是主要进行诸多数据操作(如:inserts/updates/deletes) 公式:db block changes / session logical reads 执行: select a.value/b.value from v$sysstat a,v$sysstat b where a.name='db block changes' and b.name='session logical reads' ;  Rows for each sort: 公式:sorts (rows) / ( sorts (memory) + sorts (disk) ) 执行: select a.value/(b.value+c.value) from v$sysstat a,v$sysstat b,v$sysstat c where a.name='sorts (rows)' and b.name='sorts (memory)' and c.name='sorts (disk)'; [三思笔记]学习动态性能表 http://www.5ienet.com QQ:5454589 君三思 学习动态性能表 第二篇-(1)-v$sesstat 2007.5.25 按照OracleOnlineBook中的描述,v$sesstat存储session从login到logout的详细资源使用统 计。 类似于 v$sysstat,该视图存储下列类别的统计:  事件发生次数的统计,如用户提交数。  数据产生,存取或者操作的 total 列(如:redo size)  执行操作所花费的时间累积,例如 session CPU 占用(如果 TIMED_STATISTICS 值 为 true) 注意: 如果初始参数 STAT ISTICS_LEVEL 被设置为 TYPICAL 或 ALL,时间统计被数据库自 动收集如果 STATISTICS_LEVEL 被设置为 BASIC,你必须设置 TIMED_STATISTICS 值为 TRUE 以打开收集功能。 如果你已设置了 DB_CACHE_ADVICE,TIMED_STATISTICS 或 TIMED_OS_STATISTICS,或在初始参数文件或使用 ALTER_SYSTEM 或 ALTER SESSION,那么你所设定的值的值将覆盖 STATISTICS_LEVEL 的值。 v$sysstat 和 v$sesstat 差别如下:  v$sesstat 只保存 session 数据,而 v$sysstat 则保存所有 sessions 的累积值。  v$sesstat 只是暂存数据,session 退出后数据即清空。v$sysstat 则是累积的,只有当 实例被 shutdown 才会清空。  v$sesstat 不包括统计项名称,如果要获得统计项名称则必须与 v$sysstat 或 v$statname 连接查询获得。 v$sesstat 可被用于找出如下类型 session:  高资源占用  高平均资源占用比(登陆后资源使用率)  默认资源占用比(两快照之间) 在 V$SESSTAT 中使用统计 多数 v$sesstat 中的统计参考是 v$sysstat 描述的子集,包括 session logical reads, CPU used by this session, db block changes, redo size, physical writes, parse count (hard), parse count (total), sorts (memory), and sorts (disk). V$SESSTAT 常用列说明  SID:session 唯一 ID  STATISTIC#:资源唯一 ID  VALUE:资源使用 [三思笔记]学习动态性能表 http://www.5ienet.com QQ:5454589 君三思 示例 1:下列找出当前 session 中最高的 logical 和 Physical I/O 比率. 下列 SQL 语句显示了所有连接到数据库的 session 逻辑、物理读比率(每秒)。logical 和 physical I/O 比率是通过自登陆后的时间消耗计算得出。对于 sessions 连接到数据库这种长周 期操作而言也许不够精确,不过做个示例却足够了。 先获得 session 逻辑读和物理读统计项的 STATISTIC#值: SELECT name, statistic# FROM V$STATNAME WHERE name IN ('session logical reads','physical reads') ; NAME STATISTIC# ------------------------------ ---------- session logical reads 9 physical reads 40 通过上面获得的 STATISTIC#值执行下列语句: SELECT ses.sid , DECODE(ses.action,NULL,'online','batch') "User" , MAX(DECODE(sta.statistic#,9,sta.value,0)) /greatest(3600*24*(sysdate-ses.logon_time),1) "Log IO/s" , MAX(DECODE(sta.statistic#,40,sta.value,0)) /greatest(3600*24*(sysdate-ses.logon_time),1) "Phy IO/s" , 60*24*(sysdate-ses.logon_time) "Minutes" FROM V$SESSION ses , V$SESSTAT sta WHERE ses.status = 'ACTIVE' AND sta.sid = ses.sid AND sta.statistic# IN (9,40) GROUP BY ses.sid, ses.action, ses.logon_time ORDER BY SUM( DECODE(sta.statistic#,40,100*sta.value,sta.value) ) / greatest(3600*24*(sysdate-ses.logon_time),1) DESC; SID User Log IO/s Phy IO/s Minutes ----- ------ -------- -------- ------- 1951 batch 291 257.3 1 470 online 6,161 62.9 0 730 batch 7,568 43.2 197 2153 online 1,482 98.9 10 [三思笔记]学习动态性能表 http://www.5ienet.com QQ:5454589 君三思 2386 batch 7,620 35.6 35 1815 batch 7,503 35.5 26 1965 online 4,879 42.9 19 1668 online 4,318 44.5 1 1142 online 955 69.2 35 1855 batch 573 70.5 8 1971 online 1,138 56.6 1 1323 online 3,263 32.4 5 1479 batch 2,857 35.1 3 421 online 1,322 46.8 15 2405 online 258 50.4 8 示例 2:又例如通过 v$sesstat 和 v$statname 连接查询某个 SID 各项信息。 select a.*,b.name from v$sesstat a,v$statname b where a.sid=10 and a.statistic#=b.statistic#; 第二篇-(2)-v$mystat 2007.6.8 本视图是 v$sesstat 的一个子集,返回当前 session 的统计项。当通过触发器审计 session 资源使用,可以使用 v$mystat 来捕获资源使用,这将比直接扫描 v$sesstat 的列要节省资源 的多。 [三思笔记]学习动态性能表 http://www.5ienet.com QQ:5454589 君三思 学习动态性能表 第三篇-(1)-v$sql 2007.5.25 V$SQL 中存储具体的 SQL 语句。 一条语句可以映射多个 cursor,因为对象所指的 cursor 可以有不同用户(如例 1)。如果有 多个 cursor(子游标)存在,在 V$SQLAREA 为所有 cursor 提供集合信息。 例 1: 这里介绍以下 child cursor user A: select * from tbl user B: select * from tbl 大家认为这两条语句是不是一样的啊,可能会有很多人会说是一样的,但我告诉你不一 定,那为什么呢? 这个 tblA 看起来是一样的,但是不一定哦,一个是 A 用户的, 一个是 B 用户的,这时 他们的执行计划分析代码差别可能就大了哦,改下写法大家就明白了: select * from A.tbl select * from B.tbl 在个别 cursor 上,v$sql 可被使用。该视图包含 cursor 级别资料。当试图定位 session 或 用户以分析 cursor 时被使用。 PLAN_HASH_VALUE 列存储的是数值表示的 cursor 执行计划。可被用来对比执行计划。 PLAN_HASH_VALUE 让你不必一行一行对比即可轻松鉴别两条执行计划是否相同。 V$SQL 中的列说明:  SQL_TEXT:SQL 文本的前 1000 个字符  SHARABLE_MEM:占用的共享内存大小(单位:byte)  PERSISTENT_MEM:生命期内的固定内存大小(单位:byte)  RUNTIME_MEM:执行期内的固定内存大小  SORTS:完成的排序数  LOADED_VERSIONS:显示上下文堆是否载入,1 是 0 否  OPEN_VERSIONS:显示子游标是否被锁,1 是 0 否  USERS_OPENING:执行语句的用户数  FETCHES:SQL 语句的 fetch 数。  EXECUTIONS:自它被载入缓存库后的执行次数  USERS_EXECUTING:执行语句的用户数  LOADS:对象被载入过的次数  FIRST_LOAD_TIME:初次载入时间  INVALIDATIONS:无效的次数  PARSE_CALLS:解析调用次数  DISK_READS:读磁盘次数  BUFFER_GETS:读缓存区次数  ROWS_PROCESSED:解析 SQL 语句返回的总列数 [三思笔记]学习动态性能表 http://www.5ienet.com QQ:5454589 君三思  COMMAND_TYPE:命令类型代号  OPTIMIZER_MODE:SQL 语句的优化器模型  OPTIMIZER_COST:优化器给出的本次查询成本  PARSING_USER_ID:第一个解析的用户 ID  PARSING_SCHEMA_ID:第一个解析的计划 ID  KEPT_VERSIONS:指出是否当前子游标被使用 DBMS_SHARED_POOL 包标记为 常驻内存  ADDRESS:当前游标父句柄地址  TYPE_CHK_HEAP:当前堆类型检查说明  HASH_VALUE:缓存库中父语句的 Hash 值  PLAN_HASH_VALUE:数值表示的执行计划。  CHILD_NUMBER:子游标数量  MODULE :在第一次解析这条语句是通过调用 DBMS_APPLICATION_INFO.SET_MODULE 设置的模块名称。  ACTION:在第一次解析这条语句是通过调用 DBMS_APPLICATION_INFO.SET_ACTION 设置的动作名称。  SERIALIZABLE_ABORTS:事务未能序列化次数  OUTLINE_CATEGORY:如果 outline 在解释 cursor 期间被应用,那么本列将显示 出 outline 各类,否则本列为空  CPU_TIME:解析/执行/取得等 CPU 使用时间(单位,毫秒)  ELAPSED_TIME:解析/执行/取得等消耗时间(单位,毫秒)  OUTLINE_SID:outline session 标识  CHILD_ADDRESS:子游标地址  SQLTYPE:指出当前语句使用的 SQL 语言版本  REMOTE:指出是否游标是一个远程映象(Y/N)  OBJECT_STATUS:对象状态(VALID or INVALID)  IS_OBSOLETE:当子游标的数量太多的时候,指出游标是否被废弃(Y/N) 第三篇-(2)-V$SQL_PLAN 2007.5.28 本视图提供了一种方式检查那些执行过的并且仍在缓存中的 cursor 的执行计划。 通常,本视图提供的信息与打印出的 EXPLAIN PLAN 非常相似,不过,EXPLAIN PLAN 显示的是理论上的计划,并不一定在执行的时候就会被使用,但 V$SQL_PLAN 中包括的是 实际被使用的计划。获自 EXPLAIN PLAN 语句的执行计划跟具体执行的计划可以不同,因 为 cursor 可能被不同的 session 参数值编译(如,HASH_AREA_SIZE)。 V$SQL_PLAN 中数据可以:  确认当前的执行计划  鉴别创建表索引效果  寻找 cursor 包括的存取路径(例如,全表查询或范围索引查询)  鉴别索引的选择是否最优 [三思笔记]学习动态性能表 http://www.5ienet.com QQ:5454589 君三思  决定是否最优化选择的详细执行计划(如,nested loops join)如开发者所愿。 本视图同时也可被用于当成一种关键机制在计划对比中。计划对比通常用于下列各项发 生改变时:  删除和新建索引  在数据库对象上执行分析语句  修改初始参数值  从 rule-based 切换至 cost-based 优化方式  升级应用程序或数据库到新版本之后 如果之前的计划仍然在(例如,从 V$SQL_PLAN 选择出记录并保存到 oracle 表中供参 考),那么就有可能去鉴别一条 SQL 语句在执行计划改变后性能方面有什么变化。 注意: Oracle 公司强烈推荐你使用 DBMS_STATS 包而非 ANALYZE 收集优化统计。该包可以让你 平行地搜集统计项,收集分区对象(partitioned objects)的全集统计,并且通过其它方式更好 的调整你的统计收集方式。此处,cost-based 优化器将最终使用被 DBMS_STATS 收集的统 计项。浏览 Oracle9i Supplied PL/SQL 包和类型参考以获得关于此包的更多信息。 不过,你必须使用 ANALYZE 语句而非 DBMS_STATS 进行统计收集,不涉及 cost-based 优 化器,就像: ·使用 VA L I D AT E 或 LIST CHAINED ROWS 子句 ·在 freelist blocks 上收集信息。 V$SQL_PLAN 中的常用列: 除了一些新加列,本视图几乎包括所有的 PLAN_TABLE 列,那些同样存在于 PLAN_TABLE 中的列拥有相同的值:  ADDRESS:当前 cursor 父句柄位置  HASH_VALUE:在 library cache 中父语句的 HASH 值。 ADDRESS 和 HASH_VALUE 这两列可以被用于连接 v$sqlarea 查询 cursor-specific 信息。  CHILD_NUMBER:使用这个执行计划的子 cursor 数 列 ADDRESS,HASH_VALUE 以及 CHILD_NUMBER可被用于连接 v$sql 查询子 cursor 信息。  OPERATION: 在各步骤执行内部操作的名称,例如:TABLE ACCESS  OPTIONS: 描述列 OPERATION 在操作上的变种,例如:FULL  OBJECT_NODE: 用于访问对象的数据库链接 database link 的名称对于使用并行 执行的本地查询该列能够描述操作中输出的次序。  OBJECT#: 表或索引对象数量  OBJECT_OWNER: 对于包含有表或索引的架构 schema 给出其所有者的名称  OBJECT_NAME: 表或索引名  OPTIMIZER: 执行计划中首列的默认优化模式;例如,CHOOSE。比如业务是个 存储数据库,它将告知是否对象是最优化的。  ID: 在执行计划中分派到每一步的序号。  PARENT_ID: 对 ID 步骤的输出进行操作的下一个执行步骤的 ID。  DEPTH: 业务树深度(或级)。 [三思笔记]学习动态性能表 http://www.5ienet.com QQ:5454589 君三思  POSITION: 对于具有相同 PARENT_ID 的操作其相应的处理次序。  COST: cost-based 方式优化的操作开销的评估,如果语句使用 rule-based 方式,本 列将为空。  CARDINALITY: 根据 cost-based 方式操作所访问的行数的评估。  BYTES: 根据 cost-based 方式操作产生的字节的评 估, 。  OTHER_TAG: 其它列的内容说明。  PARTITION_START: 范围存取分区中的开始分区。  PARTITION_STOP: 范围存取分区中的停止分区。  PARTITION_ID: 计算 PARTITION_S TART 和 PARTITION_STOP 这对列值的步数  OTHER: 其它信息即执行步骤细节,供用户参考。  DISTRIBUTION: 为了并行查询,存储用于从生产服务器到消费服务器分配列的方 法  CPU_COST: 根据 cost-based 方式 CPU 操作开销的评估。如果语句使用 rule-based 方式,本列为空。  IO_COST: 根据 cost-based 方式 I/O 操作开销的评估。如果语句使用 rule-based 方 式,本列为空。  TEMP_SPACE: cost-based 方式操作(sort or hash-join)的临时空间占用评估。如果语 句使用 rule-based 方式,本列为空。  ACCESS_PREDICATES: 指明以便在存取结构中定位列,例如,在范围索引查询中 的开始或者结束位置。  FILTER_PREDICATES: 在生成数据之前即指明过滤列。 CONNECT BY 操作产生 DEPTH 列替换 LEVEL 伪列,有时被用于在 SQL 脚本中帮助 indent PLAN_TABLE 数据 V$SQL_PLAN 中的连接列 列 ADDRESS,HASH_VALUE 和 CHILD_NUMBER 被用于连接 V$SQL 或 V$SQLAREA 来获取 cursor-specific 信息,例如,BUFFER_GET,或连接 V$SQLTEXT 获取完整的 SQL 语 句。 Column View Joined Column(s) ADDRESS, HASH_VALUE V$SQLAREA ADDRESS, HASH_VALUE ADDRESS,HASH_VALUE,CHILD_NUMBER V$SQL ADDRESS,HASH_VALUE,CHILD_NUMBER ADDRESS, HASH_VALUE V$SQLTEXT ADDRESS, HASH_VALUE 确认 SQL 语句的优化计划 下列语句显示一条指定 SQL 语句的执行计划。查看一条 SQL 语句的执行计划是调整优 化 SQL 语句的第一步。这条被查询到执行计划的 SQL 语句是通过语句的 HASH_VALUE 和 ADDRESS 列识别。分两步执行: 1.SELECT sql_text, address, hash_value FROM v$sql WHERE sql_text like '%TAG%'; SQL_TEXT ADDRESS HASH_VALUE [三思笔记]学习动态性能表 http://www.5ienet.com QQ:5454589 君三思 -------- -------- ---------- 82157784 1224822469 2.SELECT operation, options, object_name, cost FROM v$sql_plan WHERE address = '82157784' AND hash_value = 1224822469; OPERATION OPTIONS OBJECT_NAME COST -------------------- ------------- ------------------ ---- SELECT STATEMENT 5 SORT AGGREGATE HASH JOIN 5 TABLE ACCESS FULL DEPARTMENTS 2 TABLE ACCESS FULL EMPLOYEES 2 [三思笔记]学习动态性能表 http://www.5ienet.com QQ:5454589 君三思 学习动态性能表 第四篇-(1)-V$SQLTEXT 2007.5.29 本视图包括 Shared pool 中 SQL 语句的完整文本,一条 SQL 语句可能分成多个块被保 存于多个记录内。 注:V$SQLAREA 只包括头 1000 个字符。 V$SQLTEXT 中的常用列  HASH_VALUE:SQL 语句的 Hash 值  ADDRESS:sql 语句在 SGA 中的地址  SQL_TEXT:SQL 文本。  PIECE:SQL 语句块的序号 V$SQLTEXT 中的连接列 Column View Joined Column(s) HASH_VALUE, ADDRESS V$SQL, V$SESSION HASH_VALUE, ADDRESS HASH_VALUE. ADDRESS V$SESSION SQL_HASH_VALUE, SQL_ADDRESS 示例:已知 hash_value:3111103299,查询 sql 语句: select * from v$sqltext where hash_value='3111103299' order by piece 第四篇-(2)-V$SQLAREA 2007.5.29 本视图持续跟踪所有 shared pool 中的共享 cursor,在 shared pool 中的每一条 SQL 语句 都对应一列。本视图在分析 SQL 语句资源使用方面非常重要。 V$SQLAREA 中的信息列  HASH_VALUE:SQL 语句的 Hash 值。  ADDRESS:SQL 语句在 SGA 中的地址。 这两列被用于鉴别 SQL 语句,有时,两条不同的语句可能 hash 值相同。这时候,必须连同 ADDRESS 一同使用来确认 SQL 语句。  PARSING_USER_ID:为语句解析第一条 CURSOR 的用户  VERSION_COUNT:语句 cursor 的数量  KEPT_VERSIONS:  SHARABLE_MEMORY:cursor 使用的共享内存总数  PERSISTENT_MEMORY:cursor 使用的常驻内存总数 [三思笔记]学习动态性能表 http://www.5ienet.com QQ:5454589 君三思  RUNTIME_MEMORY:cursor 使用的运行时内存总数。  SQL_TEXT:SQL 语句的文本(最大只能保存该语句的前 1000 个字符)。  MODULE,ACTION:使用了 DBMS_APPLICATION_INFO 时 session 解析第一条 cursor 时的信息 V$SQLAREA 中的其它常用列  SORTS: 语句的排序数  CPU_TIME: 语句被解析和执行的 CPU 时间  ELAPSED_TIME: 语句被解析和执行的共用时间  PARSE_CALLS: 语句的解析调用(软、硬)次数  EXECUTIONS: 语句的执行次数  INVALIDATIONS: 语句的 cursor 失效次数  LOADS: 语句载入(载出)数量  ROWS_PROCESSED: 语句返回的列总数 V$SQLAREA 中的连接列 Column View Joined Column(s) HASH_VALUE, ADDRESS V$SESSION SQL_HASH_VALUE, SQL_ADDRESS HASH_VALUE, ADDRESS V$SQLTEXT, V$SQL, V$OPEN_CURSOR HASH_VALUE, ADDRESS SQL_TEXT V$DB_OBJECT_CACHE NAME 示例: 1.查看消耗资源最多的 SQL: SELECT hash_value, executions, buffer_gets, disk_reads, parse_calls FROM V$SQLAREA WHERE buffer_gets > 10000000 OR disk_reads > 1000000 ORDER BY buffer_gets + 100 * disk_reads DESC; 2.查看某条 SQL 语句的资源消耗: SELECT hash_value, buffer_gets, disk_reads, executions, parse_calls FROM V$SQLAREA WHERE hash_Value = 228801498 AND address = hextoraw('CBD8E4B0'); [三思笔记]学习动态性能表 http://www.5ienet.com QQ:5454589 君三思 学习动态性能表 第五篇--V$SESSION 2007.5.29 在本视图中,每一个连接到数据库实例中的 session 都拥有一条记录。包括用户 session 及后台进程如 DBWR,LGWR,arcchiver 等等。 V$SESSION 中的常用列 V$SESSION 是基础信息视图,用于找寻用户 SID 或 SADDR。不过,它也有一些列会 动态的变化,可用于检查用户。如例: SQL_HASH_VALUE,SQL_ADDRESS:这两列用于鉴别默认被 session 执行的 SQL 语 句。如果为 null 或 0,那就说明这个 session 没有执行任何 SQL 语句。PREV_HASH_VALUE 和 PREV_ADDRESS 两列用来鉴别被 session 执行的上一条语句。 注意:当使用 SQL*Plus 进行选择时,确认你重定义的列宽不小于 11 以便看到完整的 数值。 STATUS:这列用来判断 session 状态是:  Achtive:正执行 SQL 语句(waiting for/using a resource)  Inactive:等待操作(即等待需要执行的 SQL 语句)  Killed:被标注为删除 下列各列提供 session 的信息,可被用于当一个或多个 combination 未知时找到 session。 Session 信息  SID:SESSION 标识,常用于连接其它列  SERIAL#:如果某个 SID 又被其它的 session 使用的话则此数值自增加(当一个 SESSION 结束,另一个 SESSION 开始并使用了同一个 SID)。  AUDSID:审查 session ID 唯一性,确认它通常也用于当寻找并行查询模式  USERNAME:当前 session 在 oracle 中的用户名。 Client 信息 数据库 session 被一个运行在数据库服务器上或从中间服务器甚至桌面通过 SQL*Net 连接到数据库的客户端进程启动,下列各列提供这个客户端的信息  OSUSER:客户端操作系统用户名  MACHINE:客户端执行的机器  TERMINAL:客户端运行的终端  PROCESS:客户端进程的 ID  PROGRAM:客户端执行的客户端程序 要显示用户所连接 PC 的 TERMINAL、OSUSER,需 在该 PC 的 ORACLE.INI 或 Windows 中设置关键字 TERMINAL,USERNAME。 Application 信息 [三思笔记]学习动态性能表 http://www.5ienet.com QQ:5454589 君三思 调用 DBMS_APPLICATION_INFO 包以设置一些信息区分用户。这将显示下 列各列。  CLIENT_INFO:DBMS_APPLICATION_INFO 中设置  ACTION:DBMS_APPLICATION_INFO 中设置  MODULE:DBMS_APPLICATION_INFO 中设置 下列 V$SESSION 列同样可能会被用到:  ROW_WAIT_OBJ#  ROW_WAIT_FILE#  ROW_WAIT_BLOCK#  ROW_WAIT_ROW# V$SESSION 中的连接列 Column View Joined Column(s) SID V$SESSION_WAIT,,V$SESSTAT,,V$LOCK,V$SESSION_EVENT,V$OPEN_CURSOR SID (SQL_HASH_VALUE, SQL_ADDRESS) V$SQLTEXT, V$SQLAREA, V$SQL (HASH_VALUE, ADDRESS) (PREV_HASH_VALUE, PREV_SQL_ADDRESS) V$SQLTEXT, V$SQLAREA, V$SQL (HASH_VALUE, ADDRESS) TADDR V$TRANSACTION ADDR PADDR V$PROCESS ADDR 示例: 1.查找你的 session 信息 SELECT SID, OSUSER, USERNAME, MACHINE, PROCESS FROM V$SESSION WHERE audsid = userenv('SESSIONID'); 2.当 machine 已知的情况下查找 session SELECT SID, OSUSER, USERNAME, MACHINE, TERMINAL FROM V$SESSION WHERE terminal = 'pts/tl' AND machine = 'rgmdbs1'; 3.查找当前被某个指定 session 正在运行的 sql 语句。假设 sessionID 为 100 select b.sql_text from v$session a,v$sqlarea b where a.sql_hash_value=b.hash_value and a.sid=100 寻找被指定 session 执行的 SQL 语句是一个公共需求,如果 session 是瓶颈的主要原因,那 根据其当前在执行的语句可以查看 session 在做些什么。 [三思笔记]学习动态性能表 http://www.5ienet.com QQ:5454589 君三思 学习动态性能表 第六篇-(1)-V$SESSION_WAIT 2007.5.30 这是一个寻找性能瓶颈的关键视图。它提供了任何情况下 session 在数据库中当前正在 等待什么(如果 session 当前什么也没在做,则显示它最后的等待事件)。当系统存在性能问 题时,本视图可以做为一个起点指明探寻问题的方向。 V$SESSION_WAIT 中,每一个连接到实例的 session 都对应一条记录。 V$SESSION_WAIT 中的常用列  SID: session 标识  EVENT: session 当前等待的事件,或者最后一次等待事件。  WAIT_TIME: session 等待事件的时间(单位,百分之一秒)如果本列为 0,说明 session 当前 session 还未有任何等待。  SEQ#: session 等待事件将触发其值自增长  P1, P2, P3: 等待事件中等待的详细资料  P1TEXT, P2TEXT, P3TEXT: 解释说明 p1,p2,p3 事件 附注: 1.State 字段有四种含义﹕ (1)Waiting:SESSION 正等待这个事件。 (2)Waited unknown time:由于设置了 timed_statistics 值为 false,导致不能得到时间信息。 表示发生了等待,但时间很短。 (3)Wait short time:表示发生了等待,但由于时间非常短不超过一个时间单位,所以没 有记录。 (4)Waited knnow time:如果 session 等待然后得到了所需资源,那么将从 waiting 进入本 状态。 2.Wait_time 值也有四种含义: (1)值>0:最后一次等待时间(单位:10ms),当前未在等待状态。 (2)值=0:session 正在等待当前的事件。 (3)值=-1:最后一次等待时间小于 1 个统计单位,当前未在等待状态。 (4)值=-2:时间统计状态未置为可用,当前未在等待状态。 3.Wait_time 和 Second_in_wait 字段值与 state 相关: (1)如果 state 值为 Waiting,那么 wait_time 值无用。Second_in_wait 值是实际的等待时 间(单位:秒)。 (2)如果 state 值为 Wait unknow time,那么 wait_time 值和 Second_in_wait 值都无用。 (3)如果 state 值为 Wait short time,那么 wait_time 值和 Second_in_wait 值都无用。 (4)如果 state 值为 Waiting known time,那么 wait_time 值就是实际等待时间(单位:秒), Second_in_wait 值无用。 [三思笔记]学习动态性能表 http://www.5ienet.com QQ:5454589 君三思 V$SESSION_WAIT 中的连接列 Column View Joined Column(s) SID V$SESSION SID 示例: 1.列出当前系统的等待事件 SELECT event, sum(decode(wait_time,0,1,0)) "Curr", sum(decode(wait_time,0,0,1)) "Prev", count(*)"Total" FROM v$session_wait GROUP BY event ORDER BY count(*); EVENT Prev Curr Tot --------------------------------------------- ---- ----- ----- PL/SQL lock timer 0 1 1 SQL*Net more data from client 0 1 1 smon timer 0 1 1 pmon timer 0 1 1 SQL*Net message to client 2 0 2 db file scattered read 2 0 2 rdbms ipc message 0 7 7 Enqueue 0 12 12 pipe get 0 12 12 db file sequential read 3 10 13 latch free 9 6 15 SQL*Net message from client 835 1380 2215 这个按事件和 wait_time 的分组查询列出下列的信息:  多数的 session 都是空闲事件如:SQL*Net message from client, pipe get, PMON timer 等。  session 的 cpu 占用可以通过上次 session 的非等待事件大致算出,除此问题外:看起来 多数 session 没有在等待什么事情(难道他们都在干活?)但其最后等待事件都是 SQL*Net message from client。 2.列出指定 ID 的等待事件 select * from v$session_wait where sid=100; 3.应用 p1,p2,p3 进行等待事件的分析 v$session_wait 视图的列代表的缓冲区忙等待事件如下: P1—与等待相关的数据文件的全部文件数量。 P2—P1 中的数据文件的块数量。 P3—描述等待产生原因的代码。 例:select p1 "File #", p2 "Block #", p3 "Reason Code" from v$session_wait where event = 'buffer busy waits'; [三思笔记]学习动态性能表 http://www.5ienet.com QQ:5454589 君三思 如果以上查询的结果显示一个块在忙等待,以下的查询将显示这一块的名称和类型: select owner, segment_name, segment_type from dba_extents where file_id = &P1 and &P2 between block_id and block_id + blocks -1; 我们也可以查询 dba_data_files 以确定等待的文件的 file_name ,方法是使用 v$session_wait 中的 P1。 从 v$session_wait 中查询 P3(原因编码)的值可以知道 session 等待的原因。原因编码的范 围从 0 到 300,下列为部分编码所代表的事项: 0 块被读入缓冲区。 100 我们想要 NEW(创建)一个块,但这一块当前被另一 session 读入。 110 我们想将当前块设为共享,但这一块被另一 session 读入,所以我们必须等待 read() 结束。 120 我们想获得当前的块,但其他人已经将这一块读入缓冲区,所以我们只能等待他人 的读入结束。 130 块被另一 session 读入,而且没有找到其它协调的块,所以我们必须等待读的结束。 缓冲区死锁后这种情况也有可能产生。所以必须读入块的 CR。 200 我们想新创建一个 block,但其他人在使用,所以我们只好等待他人使用结束。 210 Session 想读入 SCUR 或 XCUR 中的块,如果块交换或者 session 处于非连续的 TX 模式,所以等待可能需要很长的时间。 220 在缓冲区查询一个块的当前版本,但有人以不合法的模式使用这一块,所以我们只 能等待。 230 以 CR/CRX 方式获得一个块,但块中的更改开始并且没有结束。 231 CR/CRX 扫描找到当前块,但块中的更改开始并且没有结束。 第六篇-(2)-V$SESSION_EVENT 2007.5.30 本视图记录了每个 session 的每一项等待事件。由上文所知 V$SESSION_WAIT 显示了 session 的当前等待事件,而 V$SESSION_EVENT 则记录了 session 自启动起所有的事件。 V$SESSION_EVENT 中的常用列  SID:session 标识  EVENT:session 等待的事件  TOTAL_WAITS:此 session 当前事件的总等待数  TIME_WAITED:此 session 总等待时间(单位,百分之一秒)  AVERAGE_WAIT:此 session 当前事件平均等待时间(单位,百分之一秒)  TOTAL_TIMEOUTS:等待超时次数 其它用法与 V$SESSION_WAIT 相似,不详述了 [三思笔记]学习动态性能表 http://www.5ienet.com QQ:5454589 君三思 附注: Oracle 的等待事件是衡量 Oracle 运行状况的重要依据及指标。等待事件的概念是在 Oracle7.0.1.2 中引入的,大致有 100 个等待事件。在 Oracle 8.0 中这个数目增加到了大约 150 个,在 Oracle8i 中大约有 200 个事件,在 Oracle9i 中大约有 360 个等待事件。主要有两种类 别的等待事件,即空闲(idle)等待事件和非空闲(non-idle)等待事件。 关于空闲事件和非空闲事件目前通过 google 可以搜索到非常多详尽的相关信息,同时 Oracle Database Performance Tuning Guide and Reference 中关于 Wait Events 也有非常详尽的 描述,在此就不多费口舌了。不过我在 itpub 论坛看到有热心人整理的 chm 格式非空闲事件 说明,有兴趣的朋友可以下载,链接如下: 非空闲事件说明 详见:http://www.itpub.net/728733.html [三思笔记]学习动态性能表 http://www.5ienet.com QQ:5454589 君三思 学习动态性能表 第七篇--V$PROCESS 2007.5.30 本视图包含当前系统 oracle 运行的所有进程信息。常被用于将 oracle 或服务进程的操作 系统进程 ID 与数据库 session 之间建立联系。在某些情况下非常有用: 1. 如果数据库瓶颈是系统资源(如:cpu,内存),并且占用资源最多的用户总是停留在 某几个服务进程,那么进行如下诸项:  找出资源进程  找出它们的 session,你必须将进程与会话联系起来。  找出为什么 session 占用了如此多的资源 2. SQL 跟踪文件名是基于服务进程的操作系统进程 ID。要找出 session 的跟踪文件, 你必须将 session 与服务进程联系起来。 3. 某些事件,如 rdbms ipc reply,鉴别 session 进程的 Oracle 进程 ID 在等什么。要发 现这些进程在做什么,你必须找出它们的 session。 4. 你所看到的服务器上的后台进程(DBWR,LGWR,PMON 等)都是服务进程。要想知 道他们在做什么,你必须找到他们的 session。 V$PROCESS 中的常用列  ADDR:进程对象地址  PID:oracle 进程 ID  SPID:操作系统进程 ID V$PROCESS 中的连接列 Column View Joined Column(s) ADDR V$SESSION PADDR 示例: 1. 查找指定系统用户在 oracle 中的 session 信息及进程 id,假设操作系统用户为:junsansi select s.sid,s.SERIAL#, s.username,p.spid from v$session s, v$process p where s.osuser = 'junsansi' and s.PADDR = p.ADDR 2. 查看锁和等待 SELECT /*+ rule */ lpad(' ', decode(l.xidusn, 0, 3, 0)) || l.oracle_username User_name, o.owner,o.object_name,o.object_type,s.sid,s.serial#,p.spid FROM v$locked_object l, dba_objects o, v$session s, v$process p [三思笔记]学习动态性能表 http://www.5ienet.com QQ:5454589 君三思 WHERE l.object_id = o.object_id AND l.session_id = s.sid and s.paddr = p.addr ORDER BY o.object_id, xidusn DESC 3. 附注: 在 linux 环境可以通过 ps 查看进程信息包括 pid,windows 中任务管理器的 PID 与 v$process 中 pid 不能一一对应,这块在 oracleDocument 中也没有找到介绍,后来 google 了 一下,有资料介绍说是由于 windows 是多线程服务器,每个进程包含一系列线程。这点于 unix 等不同,Unix 每个 Oralce 进程独立存在,在 Nt 上所有线程由 Oralce 进程衍生。 要在 windows 中显示 oracle 相关进程 pid,我们可以通过一个简单的 sql 语句来实现。 SELECT s.SID, p.pid, p.spid signaled, s.osuser, s.program FROM v$process p, v$session s WHERE p.addr = s.paddr; SID PID SIGNALED OSUSER PROGRAM 1 2 2452 SYSTEM ORACLE.EXE 2 3 2460 SYSTEM ORACLE.EXE 3 4 2472 SYSTEM ORACLE.EXE 4 5 2492 SYSTEM ORACLE.EXE 5 6 2496 SYSTEM ORACLE.EXE 6 7 2508 SYSTEM ORACLE.EXE 7 8 2520 SYSTEM ORACLE.EXE 8 9 2524 SYSTEM ORACLE.EXE 10 12 1316 JSS\junsansi PlSqlDev.exe 9 13 3420 JSS\junsansi PlSqlDev.exe 13 14 660 JSS\junsansi PlSqlDev.exe 还可以通过和 v$bgprocess 连接查询到后台进程的名字: SELECT s.SID SID, p.spid threadid, p.program processname, bg.NAME NAME FROM v$process p, v$session s, v$bgprocess bg WHERE p.addr = s.paddr AND p.addr = bg.paddr AND bg.paddr <> '00'; SID THREADID PROCESSNAME NAME 1 2452 ORACLE.EXE PMON 2 2460 ORACLE.EXE DBW0 3 2472 ORACLE.EXE LGWR 4 2492 ORACLE.EXE CKPT 5 2496 ORACLE.EXE SMON 6 2508 ORACLE.EXE RECO [三思笔记]学习动态性能表 http://www.5ienet.com QQ:5454589 君三思 7 2520 ORACLE.EXE CJQ0 8 2524 ORACLE.EXE QMN0 Eygle 大师写了一段 sql 脚本 getsql.sql,用来获取指定 pid 正在执行的 sql 语句,在此也附注 上来。 REM getsql.sql REM author eygle REM 在 windows 上,已知进程 ID,得到当前正在执行的语句 REM 在 windows 上,进程 ID 为 16 进制,需要转换,在 UNIX 直接为 10 进制 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 = TO_NUMBER ('&pid', 'xxxx'))) ORDER BY piece ASC / [三思笔记]学习动态性能表 http://www.5ienet.com QQ:5454589 君三思 [三思笔记]学习动态性能表 http://www.5ienet.com QQ:5454589 君三思 学习动态性能表 第八篇-(1)-V$LOCK 2007.5.31 本视图列出 Oracle 服务器当前拥有的锁以及未完成的锁或栓锁请求。如果你觉着 session 在等待等待事件队列那你应该检查本视图。如果你发现 session 在等待一个锁。那么 按如下先后顺序: 1. 使用 V$LOCK 找出 session 持有的锁。 2. 使用 V$SESSION 找出持有锁或等待锁的 session 执行的 sql 语句。 3. 使用 V$SESSION_WAIT 找出什么原因导致 session 持有锁堵塞。 4. 使用 V$SESSION 获取关于持有锁的程序和用户的更多信息。 V$LOCK 中的常用列  SID:表示持有锁的会话信息。  TYPE:表示锁的类型。值包括 TM 和 TX 等。  LMODE:表示会话等待的锁模式的信息。用数字 0-6 表示,和表 1 相对应。  REQUEST:表示 session 请求的锁模式的信息。  ID1,ID2:表示锁的对象标识。 公共锁类型 在 Oracle 数据库中,DML 锁主要包括 TM 锁和 TX 锁,其中 TM 锁称为表级锁,TX 锁称为事务锁或行级锁。 当 Oracle 执行 DML 语句时,系统自动在所要操作的表上申请 TM 类型的锁。当 TM 锁 获得后,系统再自动申请 TX 类型的锁,并将实际锁定的数据行的锁标志位进行置位。这样 在事务加锁前检查 TX 锁相容性时就不用再逐行检查锁标志,而只需检查 TM 锁模式的相容 性即可,大大提高了系统的效率。TM 锁包括了 SS、SX、S、X 等多种模式,在数据库中用 0-6 来表示。不同的 SQL 操作产生不同类型的 TM 锁,如下表 1。 TX:行级锁,事务锁  在改变数据时必须是排它模式(mode 6)。  每一个活动事务都拥有一个锁。它将在事务结束(commit/rollback)时释放。  如果一个块包括的列被改变而没有 ITL(interested transaction list)槽位(entries),那么 session 将锁置于共享模式(mode 4)。当 session 获得块的 ITL 槽位时释放。  当一个事务首次发起一个 DML 语句时就获得一个 TX 锁,该锁保持到事务被提交 或回滚。当两个或多个会话在表的同一条记录上执行 DML 语句时,第一个会话在 该条记录上加锁,其他的会话处于等待状态。当第一个会话提交后,TX 锁被释放, 其他会话才可以加锁。  指出回滚段和事务表项 按下列项以避免竞争:  避免 TX-6 类型竞争,需要根据您的应用而定。 [三思笔记]学习动态性能表 http://www.5ienet.com QQ:5454589 君三思  避免 TX-4 类型竞争,可以考虑增加对象 INITRANS 参数值。 TM:表级锁  数据库执行任何 DDL 语句时必须是排它模式;例如,alter table,drop table。  执行像 insert,update,delete 这类 DML 语句时处于共享模式。它防止其它 session 对 同一个对象同时执行 ddl 语句。  任何对象拥有正被改变的数据,TM 锁都将必须存在。  锁指向对象。 在 TM 队列避免竞争,可以考虑屏蔽对象表级锁,屏蔽表级锁防止对象执行任何 ddl 语 句。 ST:空间事务锁  每个数据库(非实例)拥有一个 ST 锁。  除了本地管理表空间,在 space 管理操作(新建或删除 extents)时必须是排它模式。  对象 creation, dropping, extension, 以及 truncation 都处于这种锁  多数公共原因的争夺,是在磁盘排序(并非使用真正的临时表空间)或回滚段扩展或 收缩。 按如下项以避免竞争:  使用真正的临时表空间(true temporary tablespaces),利用临时文件。临时段在 磁盘排序之后并不创建或删除。  使用本地管理表空间。  指定回滚段避免动态扩展和收缩,或使用自动 undo management。  避免应用执行创建或删除数据库对象。 UL:用户定义锁 用户可以自定义锁。内容较多并与此节关系不大,略过。 V$LOCK 中的连接列 Column View Joined Column(s) SID V$SESSION SID ID1, ID2, TYPE V$LOCK ID1, ID2, TYPE ID1 DBA_OBJECTS OBJECT_ID TRUNCID1/65536) V$ROLLNAME USN 1. 如果 session 在等待锁,这可被用于找出 session 持有的锁,。 2. 可被用于找出 DML 锁类型的被锁对象(type='TM') 3. 可被用于找出行级事务锁(TYPE='TX') 使用中的回滚段,不过,需要通过 V$TRANSACTION 连接查询得到。 [三思笔记]学习动态性能表 http://www.5ienet.com QQ:5454589 君三思 表1 Oracle的TM锁类型 锁模式 锁描述 解释 SQL操作 0 none 1 NULL 空 Select 2 SS(Row-S) 行级共享锁,其他对象 只能查询这些数据行 Select for update、Lock for update、 Lock row share 3 SX(Row-X) 行级排它锁,在提交前 不允许做DML操作 Insert、Update、Delete、Lock row share 4 S(Share) 共享锁 Create index、Lock share 5 SSX(S/Row-X ) 共享行级排它锁 Lock share row exclusive 6 X(Exclusive ) 排它锁 Alter table、Drop able、Drop index、 Truncate table 、Lock exclusive 数字越大锁级别越高, 影响的操作越多。一般的查询语句如 select ... from ... ;是小于 2 的锁, 有时会在 v$locked_object 出现。select ... from ... for update; 是 2 的锁。 当对话使用 for update 子串打开一个游标时,所有返回集中的数据行都将处于行级 (Row-X)独占式锁定,其他对象只能查询这些数据行,不能进行 update、delete 或 select...for update 操作。insert / update / delete ... ; 是 3 的锁。 没有 commit 之前插入同样的一条记录会没有反应, 因为后一个 3 的锁会一直等待上一 个 3 的锁, 我们必须释放掉上一个才能继续工作。 创建索引的时候也会产生 3,4 级别的锁。locked_mode 为 2,3,4 不影响 DML(insert,delete,update,select)操作, 但 DDL(alter,drop 等)操作会提示 ora-00054 错误。有主 外键约束时 update / delete ... ; 可能会产生 4,5 的锁。DDL 语句时是 6 的锁。 如果出现了锁的问题, 某个 DML 操作可能等待很久没有反应。当你采用的是直接连接 数据库的方式,也不要用 OS 系统命令 $kill process_num 或者 $kill -9 process_num 来终止 用户连接,因为一个用户进程可能产生一个以上的锁, 杀 OS 进程并不能彻底清除锁的问题。 记得在数据库级别用 alter system kill session 'sid,serial#';杀掉不正常的锁。 示例: 我按照自己的理解演示的 TX,TM 锁如下: 1.create table TMP1(col1 VARCHAR2(50));--创建临时表 2.select * from v$lock;--关掉当前锁信息 3.select * from tmp1 for update; --加锁 4.select * from v$lock; ---看看现在的锁列表,是不是多了两条记录。Type 分别为 tx,tm, 对照表 1。 5.新开一个连接,然后 select * from tmp1 for update; --呵呵,等待状态了吧 [三思笔记]学习动态性能表 http://www.5ienet.com QQ:5454589 君三思 6.select * from v$lock; --又新增了两条记录,其它一条 type=tx,lmode=0 7.查看当前被锁的 session 正在执行的 sql 语句 select /*+ NO_MERGE(a) NO_MERGE(b) NO_MERGE(c) */ a.username, a.machine, a.sid, a.serial#, a.last_call_et "Seconds", b.id1, c.sql_text "SQL" from v$session a, v$lock b, v$sqltext c where a.username is not null and a.lockwait = b.kaddr and c.hash_value =a.sql_hash_value 8.将之前的 for update 语句 commit 或者 rollback,然后新开连接的 session 拥有锁。有兴趣的 朋友还可以试试两条 for update 的时候,关闭先执行的那个窗口,看看 oracle 会给出什么样 的响应。 这一节是我在自整理 v$系列视图以来花费时间和精力最多的一个,我反复看了 document,又从网上搜索了各种资料实际使用案例等,就是不开窍。这一节至今我也仍未有 把握说尽在掌握,所以在上述文字中除了例子,我如实贴出了收集来的内容,未加任何自我 理解,就是担心万一我的理解有误,会对其它浏览本文的人造成困扰。同时我把在收集过程 中自我感觉对理解 v$lock 可能有帮助的资料地址列出,供有心人参考: Oracle 数据库中的锁机制研究 http://soft.zdnet.com.cn/software_zone/2007/0208/377403.shtml DB2 和 Oracle 的并发控制(锁)比较 http://www.ibm.com/developerworks/cn/db2/library/techarticles/dm-0512niuxzh/ Itpub 论坛的 oracle 专题深入讨论区也有一篇非常精彩的讨论,地址如下: 我对 ORACLE 数据锁的一点体会 http://www.itpub.net/270059.html 学习动态性能表 第八篇-(2)-V$LOCKED_OBJECT 2007.6.4 本视图列出系统上的每个事务处理所获得的所有锁。 V$LOCKED_OBJECT 中的列说明:  XIDUSN:回滚段号  XIDSLOT:槽号  XIDSQN:序列号  OBJECT_ID:被锁对象 ID  SESSION_ID:持有锁的 sessionID  ORACLE_USERNAME:持有锁的 Oracle 用户名 [三思笔记]学习动态性能表 http://www.5ienet.com QQ:5454589 君三思  OS_USER_NAME:持有锁的操作系统 用户名  PROCESS:操作系统进程号  LOCKED_MODE:锁模式,值同上表 1 示例: 1.以 DBA 角色, 查看当前数据库里锁的情况可以用如下 SQL 语句: select object_id,session_id,locked_mode from v$locked_object; select t2.username, t2.sid, t2.serial#, t2.logon_time from v$locked_object t1, v$session t2 where t1.session_id = t2.sid order by t2.logon_time; 如果有长期出现的一列,可能是没有释放的锁。我们可以用下面 SQL 语句杀掉长期没有释 放非正常的锁: alter system kill session 'sid,serial#'; [三思笔记]学习动态性能表 http://www.5ienet.com QQ:5454589 君三思 学习动态性能表 第九篇--V$FILESTAT 2007.6.5 本视图记录各文件物理 I/O 信息。如果瓶颈与 I/O 相关,可用于分析发生的活动 I/O 事 件。V$F I LE S TAT 显示出数据库 I/O 的下列信息(不包括日志文件):  物理读写数  块读写数  I/O 读写总耗时 以上数值自实例启动即开始记录。如果获取了两个快照,那么二者之间的差异即是这一 时间段内活动 I/O 统计。 V$F ILESTAT 中的常用列:  FILE#:文件序号;  PHYRDS:已完成的物理读次数;  PHYBLKRD:块读取数;  PHYWRTS:DBWR 完成的物理写次数;  PHYBLKWRT:写入磁盘的块数; V$F ILESTAT 注意项:  因为 multiblock 读调用,物理读数和数据块读数有可能不同;  因为进程直写,物理写和数据块写也可能不一致;  Sum(physical blocks read) 近似于 v$sysstat 中的 physical reads;  Sum(physical blocks written) 近似于 v$sysstat 中的 physical writes;  数据读(由缓存读比直读好)由服务进程处理。从 buffer cache 写只能由 DBWR 进行, 直写由服务进程处理。 V$F ILESTAT 中的连接列 Column View Joined Column(s) ----------- ------------------------- ------------------------- FILE# DBA_DATA_FILES FILE_ID FILE# V$DATAFILE FILE# 示例: 1.获得数据文件物理读写和数据块读写信息: select df.tablespace_name name, df.file_name "file", f.phyrds pyr, f.phyblkrd pbr, [三思笔记]学习动态性能表 http://www.5ienet.com QQ:5454589 君三思 f.phywrts pyw, f.phyblkwrt pbw from v$filestat f, dba_data_files df where f.file# = df.file_id order by df.tablespace_name; 注意:尽管 oracle 记录的读写次数非常精确,但如果数据库运行在 Unix 文件系统(UFS) 有可能不能表现真实的磁盘读写,例如,读次数可能并非真实的磁盘读,而是 UFS 缓存。 不过裸设备的读写次数应该是比较精准的。 2. [三思笔记]学习动态性能表 http://www.5ienet.com QQ:5454589 君三思 学习动态性能表 第十篇--V$SESSION_LONGOPS 2007.6.7 本视图显示运行超过 6 秒的操作的状态。包括备份,恢复,统计信息收集,查询等等。 要监控查询执行进展状况,你必须使用 cost-based 优化方式,并且:  设置 TIMED_STATISTICS 或 SQL_TRACE 参数值为 true。  通过 ANALYZE 或 DBMS_S TATS 数据包收集对象统计信息。 你可以通过 DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS 过程添加 application-specific 长运行操作信息到本视图。关于 DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS 的更多信息可以浏览:Oracle Supplied PL/SQL Packages and Types Reference。 V$SESSION_LONGOPS 列说明  SID:Session 标识  SERIAL#:Session 串号  OPNAME:操作简要说明  TARGET:操作运行所在的对象  TARGET_DESC:目标对象说明  SOFAR:至今为止完成的工作量  TOTALWORK:总工作量  UNITS:工作量单位  START_ TIME:操作开始时间  LAST_UPDATE_TIME:统计项最后更新时间  TIME_REMAINING:预计完成操作的剩余时间(秒)  ELAPSED_SECONDS:从操作开始总花费时间(秒)  CONTEXT:前后关系  MESSAGE:统计项的完整描述  USERNAME:执行操作的用户 ID  SQL_ADDRESS:用于连接查询的列  SQL_HASH_VALUE:用于连接查询的列  QCSID: 示例: 找一较大表,确认该表查询将超过 6 秒,哎呀让它快咱没把握,让它慢这可是我的强项啊~~ SQL> set timing on SQL> create table ttt as select level lv,rownum rn from dual connect by level<10000000; --创建一个临时表 Table created Executed in 19.5 seconds [三思笔记]学习动态性能表 http://www.5ienet.com QQ:5454589 君三思 SQL> commit; Commit complete Executed in 0 seconds SQL> select * from (select * from ttt order by lv desc) where rownum<2; --执行一个费时的查询 LV RN ---------- ---------- 9999999 9999999 Executed in 9.766 seconds --哈哈,成功超过 6 秒 SQL> select sid,opname,sofar,totalwork,units,sql_hash_value from v$session_longops; ----看看v$session_longops中是不是已经有记录了 SID OPNAME SOFAR TOTALWORK UNITS SQL_HASH_VALUE ---------- ---------------------------------------------------------------- ---------- ---------- -------------------------------- -------------- 10 Table Scan 47276 47276 Blocks 2583310173 Executed in 0.047 seconds SQL> select a.sql_text from v$sqlarea a,v$session_longops b where a.HASH_VALUE=b.SQL_HASH_VALUE; --通过hash_value联系查询出刚执行的查 询语句。 SQL_TEXT ----------------------------------------------------------------- --------------- select * from (select * from ttt order by lv desc) where rownum<2 Executed in 0.063 seconds Ps:itpub 论坛的 fenng 版版数年前有篇文章描述了 v$sessin_longops 的来源,有兴趣的朋友可 以研究研究: http://www.dbanotes.net/database/vsession_longops.html [三思笔记]学习动态性能表 http://www.5ienet.com QQ:5454589 君三思 [三思笔记]学习动态性能表 http://www.5ienet.com QQ:5454589 君三思 学习动态性能表 第十一篇-(1)-V$LATCH 2007.6.7 Oracle Rdbms 应用了各种不同类型的锁定机制,latch 即是其中的一种。Latch 是用于保 护 SGA 区中共享数据结构的一种串行化锁定机制。Latch 的实现是与操作系统相关的,尤其 和一个进程是否需要等待一个 latch、需要等待多长时间有关。Latch 是一种能够极快地被获 取和释放的锁,它通常用于保护描述 buffer cache 中 block 的数据结构。与每个 latch 相联系 的还有一个清除过程,当持有 latch 的进程成为死进程时,该清除过程就会被调用。Latch 还具有相关级别,用于防止死锁,一旦一个进程在某个级别上得到一个 latch,它就不可能 再获得等同或低于该级别的 latch。 本视图保存自实例启动各类栓锁的统计信息。常用于当 v$session_wait 中发现栓锁竞争 时鉴别 SGA 区中问题所在区域。 v$latch 表的每一行包括了对不同类型 latch 的统计,每一列反映了不同类型的 latch 请 求的活动情况。不同类型的 latch 请求之间的区别在于,当 latch 不可立即获得时,请求进程 是否继续进行。按此分类,latch 请求的类型可分为两类:willing-to-wait 和 immediate。  Willing-to-wait:是指如果所请求的 latch 不能立即得到,请求进程将等待一很短的 时间后再次发出请求。进程一直重复此过程直到得到 latch。  Immediate:是指如果所请求的 latch 不能立即得到,请求进程就不再等待,而是继 续执行下去。 V$LATCH 中的常用列:  NAME:latch 名称  IMMEDIATE_GETS:以 Immediate 模式 latch 请求数  IMMEDIATE_MISSES:请求失败数  GETS:以 Willing to wait 请求模式 latch 的请求数  MISSES:初次尝试请求不成功次数  SPIN_GETS:第一次尝试失败,但在以后的轮次中成功  SLEEP[x]:成功获取前 sleeping 次数  WAIT_TIME:花费在等待 latch 的时间 V$LATCH 中的连接列 Column View Joined Column(s) --------------------- ------------------------------ ------------------------ NAME/LATCH# V$LATCH_CHILDREN NAME/LATCH# NAME V$LATCHHOLDER NAME NAME/LATCH# V$LATCHNAME NAME/LATCH# NAME V$LATCH_MISSES PARENT_NAME 示例: [三思笔记]学习动态性能表 http://www.5ienet.com QQ:5454589 君三思 下列的示例中,创建一个表存储查询自 v$latch 的数据: CREATE TABLE snap_latch as SELECT 0 snap_id, sysdate snap_date, a.* FROM V$LATCH a; ALTER TABLE snap_latch add (constraint snap_filestat primary key (snap_id, name)); 最初,snap_id 被置为 0,稍后,snap_latch 表的 snap_id 列被更新为 1: INSERT INTO snap_latch SELECT 1, sysdate, a.* FROM V$LATCH a; 注意你通过 sql 语句插入记录时必须增加 snap_id 的值。 在你连续插入记录之后,使用下列的 select 语句列出统计。注意 0 不能成为被除数。 SELECT SUBSTR(a.name,1,20) NAME, (a.gets-b.gets)/1000 "Gets(K)", (a.gets-b.gets)/(86400*(a.snap_date-b.snap_date)) "Get/s", DECODE ((a.gets-b.gets), 0, 0, (100*(a.misses-b.misses)/(a.gets-b.gets))) MISS, DECODE ((a.misses-b.misses), 0, 0, (100*(a.spin_gets-b.spin_gets)/(a.misses-b.misses))) SPIN, (a.immediate_gets-b.immediate_gets)/1000 "Iget(K)", (a.immediate_gets-b.immediate_gets)/ (86400*(a.snap_date-b.snap_date)) "IGet/s", DECODE ((a.immediate_gets-b.immediate_gets), 0, 0, (100*(a.immediate_misses-b.immediate_misses)/ (a.immediate_gets-b.immediate_gets))) IMISS FROM snap_latch a, snap_latch b WHERE a.name = b.name AND a.snap_id = b.snap_id + 1 AND ( (a.misses-b.misses) > 0.001*(a.gets-b.gets) or (a.immediate_misses-b.immediate_misses) > 0.001*(a.immediate_gets-b.immediate_gets)) ORDER BY 2 DESC; 下例列出 latch 统计项,miss 列小于 0.1%的记录已经被过滤。 NAME Gets(K) Get/s MISS SPIN IGets(K) IGet/s IMISS ------------------ -------- ------- ----- ------ -------- ------- ----- cache buffers chai 255,272 69,938 0.4 99.9 3,902 1,069 0.0 library cache 229,405 62,851 9.1 96.9 51,653 14,151 3.7 shared pool 24,206 6,632 14.1 72.1 0 0 0.0 latch wait list 1,828 501 0.4 99.9 1,836 503 0.5 row cache objects 1,703 467 0.7 98.9 1,509 413 0.2 redo allocation 984 270 0.2 99.7 0 0 0.0 messages 116 32 0.2 100.0 0 0 0.0 cache buffers lru 91 25 0.3 99.0 7,214 1,976 0.3 modify parameter v 2 0 0.1 100.0 0 0 0.0 redo copy 0 0 92.3 99.3 1,460 400 0.0 什么时候需要检查 latch 统计呢?看下列项: [三思笔记]学习动态性能表 http://www.5ienet.com QQ:5454589 君三思  misses/gets 的比率是多少  获自 spinning 的 misses 的百分比是多少  latch 请求了多少次  latch 休眠了多少次 Redo copy latch 看起来有很高的的失误率,高达 92.3%。不过,我们再仔细看的话,Redo copy latches 是获自 immediate 模式。immediate 模式的数值看起来还不错,并且 immediate 模式只有个别数大于 willing to wait 模式。所以 Redo copy latch 其实并不存在竞争。不过, 看起来 shared pool 和 library cache latches 可能存在竞争。考虑执行一条查询检查 latches 的 sleeps 以确认是否确实存在问题。 latch 有 40 余种,但作为 DBA 关心的主要应有以下几种:  Cache buffers chains latch:当用户进程搜索 SGA 寻找 database cache buffers 时需要 使用此 latch。  Cache buffers LRU chain latch:当用户进程要搜索 buffer cache 中包括所有 dirty blocks 的 LRU (least recently used) 链时使用该种 latch。  Redo log buffer latch:这种 latch 控制 redo log buffer 中每条 redo entries 的空间分配。  Row cache objects latch:当用户进程访问缓存的数据字典数值时,将使用 Row cache objects latch。 Latches 调优 不要调整 latches。如果 你发现 latch 存在竞争,它可能是部分 SGA 资源使用反常的征兆。要 修正问题所在,你更多的是去检查那部分 SGA 资源使用的竞争情况。仅仅从 v$latch 是无法 定位问题所在的。 关于 latches 的更多信息可以浏览 Oracle Database Concepts。 第十一篇-(2)-V$LATCH_CHILDREN 2007.6.6 数据库中有些类别的 latches 拥有多个。V$LATCH 中提供了每个类别的总计信息。如果 想看到单个 latch,你可以通过查询本视图。 例如: select name,count(*) ct from v$Latch_children group by name order by ct desc; 与 v$latch 相比,除多 child#列外,其余列与之同,不详述~~ [三思笔记]学习动态性能表 http://www.5ienet.com QQ:5454589 君三思 学习动态性能表 第 12 篇--V$DB_OBJECT_CACHE 2007.6.4 本视图提供对象在 library cache(shared pool)中对象统计,提供比 v$librarycache 更多的 细节,并且常用于找出 shared pool 中的活动对象。 v$db_object_cache 中的常用列:  OWNER:对象拥有者  NAME:对象名称  TYPE:对象类型(如,sequence,procedure,function,package,package body,trigger)  KEPT:告知是否对象常驻 shared pool(yes/no),有赖于这个对象是否已经利用 PL/SQL 过程 DBMS_SHARED_POOL.KEEP“保持”(永久固定在内存中)  SHARABLE_MEM:共享内存占用  PINS:当前执行对象的 session 数  LOCKS:当前锁定对象的 session 数 瞬间状态列: 下列列保持对象自初次加载起的统计信息:  LOADS:对象被加载次数。 示例: 1.shared pool 执行以及内存使用总计 下列查询显示出 shared pool 内存对不同类别的对象 同时也显示是否有对象通过 DBMS_SHARED_POOL.KEEP()过程常驻 shared pool。 SELECT type, kept, COUNT(*), SUM(sharable_mem) FROM V$DB_OBJECT_CACHE GROUP BY type, kept; 2.通过载入次数找出对象 SELECT owner, name sharable_mem, kept, loads FROM V$DB_OBJECT_CACHE WHERE loads > 1 ORDER BY loads DESC; 3.找出使用的内存超过 10M 并且不在常驻内存的对象。 SELECT owner, name, sharable_mem, kept FROM V$DB_OBJECT_CACHE WHERE sharable_mem > 102400 AND kept = 'NO' ORDER BY sharable_mem DESC; [三思笔记]学习动态性能表 http://www.5ienet.com QQ:5454589 君三思 学习动态性能表 第 13 篇--V$OPEN_CURSOR 2007.6.8 本视图列出 session 打开的所有 cursors,很多时候都将被用到,比如:你可以通过它查 看各个 session 打开的 cursor 数。 当诊断系统资源占用时,它常被用于联接 v$sqlarea 和 v$sql 查询出特定 SQL(高逻辑或 物理 I/O)。然后,下一步就是找出源头。在应用环境,基本都是同一类用户登陆到数据库(在 V$SQLAREA 中拥有相同的 PARSING_USER_ID),而通过这个就可以找出它们的不同。 V$SQLAREA 中的统计项在语句完全执行后被更新( 并且从 V$SESSION.SQL_HASH_VALUE 中消失)。因此,你不能直接找到 session 除非语句被再次 执行。不过如果 session 的 cursor 仍然打开着,你可以通过 v$open_cursor 找出执行这个语句 的 session。 V$OPEN_CURSOR 中的连接列 Column View Joined Column(s) ----------------------------- ---------------------------------------- ----------------------------- HASH_VALUE, ADDRESS V$SQLAREA, V$SQL, V$SQLTEXT HASH_VALUE, ADDRESS SID V$SESSION SID 示例: 1.找出执行某语句的 session: SELECT hash_value, buffer_gets, disk_reads FROM V$SQLAREA WHERE disk_reads > 1000000 ORDER BY buffer_gets DESC; HASH_VALUE BUFFER_GETS DISK_READS ---------- ----------- ---------- 1514306888 177649108 3897402 478652562 63168944 2532721 360282550 14158750 2482065 3 rows selected. SQL> SELECT sid FROM V$SESSION WHERE sql_hash_value = 1514306888 ; no rows selected --直接通过 hash_value 查找 v$session,没有记录 SQL> SELECT sid FROM V$OPEN_CURSOR WHERE hash_Value = 1514306888 ; SID [三思笔记]学习动态性能表 http://www.5ienet.com QQ:5454589 君三思 ----- 1125 233 935 1693 531 5 rows selected. --通过 hash_value 在 v$open_cursor 中查找 sid(只有在 session 的 cursor 仍然打开的情况下才 有可能找到) 2.列出拥有超过 400 个 cursor 的 sessionID SQL> SELECT sid, count(0) ct FROM v$open_cursor GROUP BY sid HAVING COUNT(0) > 400 ORDER BY ct desc; 事实上,v$open_cursor 是一个相当常用的视图,特别是 web 开发应用的时候。仅通过它一 个视图你就能分析出当前的连接情况,主要执行语句等。 [三思笔记]学习动态性能表 http://www.5ienet.com QQ:5454589 君三思 学习动态性能表 第 14 篇--V$PARAMETER&V$SYSTEM_PARAMETER 2007.6.11 这两个视图列出的各参数项名称以及参数值。V$PARAMETER 显示执行查询的 session 的参数值。V$SYSTEM_PARAMETER 视图则列出实例的参数值。 例如,下列查询显示执行查询的 session 的 SORT_AREA_SIZE 参数值: SELECT value FROM V$PARAMETER WHERE name = 'sort_area_size'; 呵呵,可能有朋友还是不明白v$parameter和v$system_parameter的区别,我再举个例子,相 信你马上就明白了。 SQL>select value from v$parameter where name = 'global_names'; VALUE ------------------------------------------------------------------------------------------------ TRUE 1 row selected. SQL> alter session set global_names = false; Session altered. SQL> select value from v$parameter where name = 'global_names'; VALUE ------------------------------------------------------------------------------------------------ FALSE 1 row selected. SQL> select value from v$system_parameter where name = 'global_names'; VALUE ------------------------------------------------------------------------------------------------ TRUE 1 row selected. [三思笔记]学习动态性能表 http://www.5ienet.com QQ:5454589 君三思 V$PARAMETER 中的常用列:  NAME:参名  VALUE:参值(session 或实例)  ISDEFAULT:参值是否默认值  ISSES_MODIFIABLE:此参数是否 session 级可修改  ISSYS_MODIFIABLE:此参数在实例启动后是否可由实例修改  ISMODIFIED:自实例启动起,参值是否被修改,如果被修改,session 级或是实例 (系统)级修改(如果执行一条 alter session,则值将被 MODIFIED,如果执行的是 alter system,则值为 SYS_MODIFIED)  ISADJUSTED:  DESCRIPTION:参数简要描述  UPDATE_COMMENT:由 dba 提供的参数说明 使用 v$parameter 以及 v$system_parameter 数据: 在调优期间通过查询 v$parameter 以确认当前参数设置。例如,如果 buffer cache hit ratio 较低,那么通过查询 DB_BLOCK_BUFFERS(或 DB_CACHE_SIZE)可以明确当前的 buffer cache 大小。 SELECT name, value, isdefault, isses_modifiable, issys_modifiable, ismodified FROM V$PARAMETER WHERE name = 'sort_area_size'; NAME VALUE ISDEF ISSES ISSYS_MOD ISMODIFIED -------------------- ---------- ----- ----- --------- ---------- sort_area_size 1048576 TRUE TRUE DEFERRED MODIFIED 前例显示了 SORT_AREA_SIZE 初始参数在实例启动时并非初始值,不过被 session 修改回 了初始值。 注意:当查询 v$parameter 时要注意,如果你想查看实例参数,要查询 v$system_parameter。 [三思笔记]学习动态性能表 http://www.5ienet.com QQ:5454589 君三思 学习动态性能表 第 15 篇--V$ROLLSTAT 2007.6.12 本视图自启动即保持并记录各回滚段统计项。在学习本视图之前,我们先来了解一下回 滚段(rollback segment)的相关概念: 回滚段概述 回滚段用于存放数据修改之前的值(包括数据修改之前的位置和值)。回滚段的头部包 含正在使用的该回滚段事务的信息。一个事务只能使用一个回滚段来存放它的回滚信息,而 一个回滚段可以存放多个事务的回滚信息。 回滚段的作用 1。事务回滚:当事务修改表中数据的时候,该数据修改前的值(即前影像)会存放在 回滚段中,当用户回滚事务(ROLLBACK)时,ORACLE 将会利用回滚段中的数据前影像 来将修改的数据恢复到原来的值。 2。事务恢复:当事务正在处理的时候,例程失败,回滚段的信息保存在重做日志文件 中,ORACLE 将在下次打开数据库时利用回滚来恢复未提交的数据。 3。读一致性:当一个会话正在修改数据时,其他的会话将看不到该会话未提交的修改。 而且,当一个语句正在执行时,该语句将看不到从该语句开始执行后的未提交的修改(语句 级读一致性)。当 ORACLE 执行 SELECT 语句时,ORACLE 依照当前的系统改变号(SYSTEM CHANGE NUMBER-SCN)来保证任何前于当前 SCN 的未提交的改变不被该语句处理。可 以想象:当一个长时间的查询正在执行时,若其他会话改变了该查询要查询的某个数据块, ORACLE 将利用回滚段的数据前影像来构造一个读一致性视图。 事务级的读一致性 ORACLE 一般提供 SQL 语句级(SQL STATEMENT LEVEL)的读一致性,可以用以下 语句来实现事务级的读一致性。 SET TRANSACTION READ ONLY; 或: SET TANNSACTION SERIALIZABLE; 以上两个语句都将在事务开始后提供读一致性。需要注意的是,使用第二个语句对数据 库的并发性和性能将带来影响。 回滚段的种类 1。系统回滚段:当数据库创建后,将自动创建一个系统回滚段,该回滚段只用于存放 系统表空间中对象的前影像。 2。非系统回滚段:拥有多个表空间的数据库至少应该有一个非系统回滚段,用于存放 非系统表空间中对象的数据前影像。非系统回滚段又分为私有回滚段和公有回滚段,私有回 滚段应在参数文件的 ROLLBACK SEGMENTS 参数中列出,以便例程启动时自动使其在线 [三思笔记]学习动态性能表 http://www.5ienet.com QQ:5454589 君三思 (ONLINE)。公有回滚段一般在 OPS(ORACLE 并行服务器)中出现,将在例程启动时自 动在线。 3。DEFERED 回滚段:该回滚段在表空间离线(OFFLINE)时由系统自动创建,当表 空间再次在线(ONLINE)时由系统自动删除,用于存放表空间离线时产生的回滚信息。 回滚段的使用 分配回滚段:当事务开始时,ORACLE 将为该事务分配回滚段,并将拥有最少事务的 回滚段分配给该事务。事务可以用以下语句申请指定的回滚段: SET TRANSTRACTION USE ROLLBACK SEGMENT rollback_segment 事务将以顺序,循环的方式使用回滚段的区(EXTENTS),当当前区用满后移到下一个 区。几个事务可以写在回滚段的同一个区,但每个回滚段的块只能包含一个事务的信息。 例如(两个事务使用同一个回滚段,该回滚段有四个区): 1、事务在进行中,它们正在使用回滚段的第三个区; 2、当两个事务产生更多的回滚信息,它们将继续使用第三个区; 3、当第三个区满后,事务将写到第四个区,当事务开始写到一个新的区时,称为翻转 (WRAP); 4、当第四个区用满时,如果第一个区是空闲或非活动(使用该区的所有事务完成而没 有活动的事务)的,事务将接着使用第一个区。 回滚段的扩张(EXTEND) 当当前回滚段区的所有块用完而事务还需要更多的回滚空间时,回滚段的指针将移到下 一个区。当最后一个区用完,指针将移到第一个区的前面。回滚段指针移到下一个区的前提 是下一个区没有活动的事务,同时指针不能跨区。当下一个区正在使用时,事务将为回滚段 分配一个新的区,这种分配称为回滚段的扩展。回滚段将一直扩展到该回滚段区的个数到达 回滚段的参数 MAXEXTENTS 的值时为止。 回滚段的回收和 OPTIMAL 参数 OPTIMAL 参数指明回滚段空闲时收缩到的位置,指明回滚段的 OPTIMAL 参数可以减 少回滚段空间的浪费。 V$ROLLSTAT 中的常用列  USN:回滚段标识  RSSIZE:回滚段默认大小  XACTS:活动事务数 在一段时间内增量用到的列  WRITES:回滚段写入数(单位:bytes)  SHRINKS:回滚段收缩次数  EXTENDS:回滚段扩展次数  WRAPS:回滚段翻转(wrap)次数  GETS:获取回滚段头次数  WAITS:回滚段头等待次数 [三思笔记]学习动态性能表 http://www.5ienet.com QQ:5454589 君三思 V$ROLLSTAT 中的连接列 Column View Joined Column(s) -------------- ----------------------- ------------------------ USN V$ROLLNAME USN 注意: 通过花费时间除以翻转次数,你可以得到一次回滚段翻转(wrap)的平均用时。此方法常 用于在长查询中指定合适的回滚段大小以避免'Snapshot Too Old'错误。同时,通过查看 extends 和 shrinks 列可以看出 optimal 是否需要增加。 示例: 1.查询回滚段的信息。所用数据字典:DBA_ROLLBACK_SEGS,可以查询的信息:回滚段 的标识(SEGMENT_ID)、名称 (SEGMENT_NAME)、所在表空间(TABLESPACE_NAME)、类 型(OWNER)、状态(STATUS)。 select * from DBA_ROLLBACK_SEGS 2.查看回滚段的统计信息: SELECT n.name, s.extents, s.rssize, s.optsize, s.hwmsize, s.xacts, s.status FROM v$rollname n, v$rollstat s WHERE n.usn = s.usn; 3.查看回滚段的使用情况,哪个用户正在使用回滚段的资源: select s.username, u.name from v$transaction t, v$rollstat r, v$rollname u, v$session s where s.taddr = t.addr and t.xidusn = r.usn and r.usn = u.usn order by s.username; [三思笔记]学习动态性能表 http://www.5ienet.com QQ:5454589 君三思 学习动态性能表 第 16 篇--V$ROWCACHE 2007.6.12 本视图显示数据字典缓存(也叫 rowcache)的各项统计。每一条记录包含不同类型的数据 字典缓存数据统计,注意数据字典缓存有层次差别,因此同样的缓存名称可能不止一次出现。 V$ROWCACHE 常用列  PARAMETER:缓存名  COUNT:缓存项总数  USAGE:包含有效数据的缓存项数  GETS:请求总数  GETMISSES:请求失败数  SCANS:扫描请求数  SCANMISSES:扫描请求失败次数  MODIFICATIONS:添加、修改、删除操作数  DLM_REQUESTS:DLM 请求数  DLM_CONFLICTS:DLM 冲突数  DLM_RELEASES:DLM 释放数 使用 V$ROWCACHE 数据 1>.确认数据字典缓存是否拥有适当的大小。如果 shared pool 过小,那数据字典缓存就 不足以拥有合适的大小以缓存请求信息。 2>.确认应用是否有效访问缓存。如果应用设计未能有效使用数据字典缓存(比如,大数 据字典缓存并不有助于解决性能问题)。例如,DC_USERS 缓存在过去某段时期内出现 大量 GETS,看起来像是数据库中创建了大量的不同用户,并且应用记录下用户频繁登 陆和注销。通过检查 logon 比率以及系统用户数可以验证上述数据。同时解析比率也会 很高,如果这是一个大型的 OLTP 系统的中间层,它可能在中间层更有效的管理个别帐 户,允许中间层以单用户登陆成为应用所有者。通过保持活动连接来减少 logon/logoff 比率也同样有效。 3>. 确认是否发生动态空间分配。DC_SEGMENTS, DC_USED_EXTENTS, 以及 DC_FREE_EXTENTS 大量的类似大小修改将指出存在大量动态空间分配。可行的解决 方案包括指定下一个区大小或者使用本地管理表空间。如果发生空间分配的是临时的表 空间,则可以为其指定真正的临时表空间(If the space allocation is occurring on the temp tablespace, then use a true temporary tablespace for the temp. )。 4>.dc_sequences 值的变化指出是否大量 sequence 号正在产生。 5>.搜集硬解析的证据。硬解析常表现为大量向 DC_COLUMNS, DC_VIEWS 以及 DC_OBJECTS caches 的 gets。 示例: 1.分组统计数据字典统计项 [三思笔记]学习动态性能表 http://www.5ienet.com QQ:5454589 君三思 SELECT parameter,sum("COUNT"),sum(usage),sum(gets),sum(getmisses), sum(scans),sum(scanmisses),sum(modifications), sum(dlm_requests),sum(dlm_conflicts),sum(dlm_releases) FROM V$ROWCACHE GROUP BY parameter; 2.检查数据字典的命中率 select 1 - sum(getmisses) / sum(gets) "data dictionary hitratio" from v$rowcache; [三思笔记]学习动态性能表 http://www.5ienet.com QQ:5454589 君三思 学习动态性能表 第 17 篇-(1)-V$SEGSTAT 2007.6.13 本视图实时监控段级(segment-level)统计项,支持 oracle9ir2 及更高版本 V$SEGSTAT 中的常用列  TS#:表空间标识  OBJ#:字典对象标识  DATAOBJ#:数据对象标识  STATISTIC_NAME:统计项名称  STATISTIC#:统计项标识  VALUE:统计项值 V$SEGSTAT 中的连接列 Column View Joined Column(s) -------------- ----------------------- ------------------------ TS# V$TABLESPACE TS# OBJ# ALL_OBJECTS OBJECT_ID 示例: 1.查询指定对象的统计 select * from v$segstat where ts# = 11 and obj# = (select object_id from user_objects where object_name = 'TMPTABLE1' and owner = 'JSS') 第 17 篇-(2)-V$SEGMENT_STATISTICS 2007.6.13 这是一个友好的视图,支持 Oracle9ir2 及更高版本。实时监测段级(segment-level)统计项, 可用于鉴定性能问题源于表或索引 V$SEGMENT_STATISTICS 中的列  OWNER:对象所有者  OBJECT_NAME:对象名称  SUBOBJECT_NAME:子对象名称  TABLESPACE_NAME:对象所在表空间  TS#:表空间标识  OBJ#:字典对象标识  DATAOBJ#:数据对象标识 [三思笔记]学习动态性能表 http://www.5ienet.com QQ:5454589 君三思  OBJECT_TYPE:对象类型  STATISTIC_NAME:统计项名称  STATISTIC#:统计项标识  VALUE:统计项值 基本与上相同,只是信息更加详细,不再赘述。 [三思笔记]学习动态性能表 http://www.5ienet.com QQ:5454589 君三思 学习动态性能表 第 18 篇--V$SYSTEM_EVENT 2007.6.13 本视图概括了实例各项事件的等待信息。v$session_wait 显示了系统的当前等待项, v$system_event 则提供了自实例启动后各个等待事件的概括。常用于获取系统等待信息的历 史影象。而通过两个 snapshot 获取等待项增量,则可以确定这段时间内系统的等待项。 V$SYSTEM_EVENT 中的常用列  EVENT:等待事件名称  TOTAL_WAITS:此项事件总等待次数  TIME_WAITED:此项事件的总等待时间(单位:百分之一秒)  AVERAGE_WAIT :此项事件的平均等待用时( 单位:百分之一 秒)(time_waited/total_waits)  TOTAL_TIMEOUTS:此项事情总等待超时次数 示例: 1.查看系统的各项等待,按总耗时排序 SELECT event,total_waits waits,total_timeouts timeouts, time_waited total_time,average_wait avg FROM V$SYSTEM_EVENT ORDER BY 4 DESC; 比如,通过 checkpoint completed、log file switch(checkpoint incomplete)可以查看检查点进 程的性能。通过 log file parallel write、log file switch completed 可以查看联机重做日志文件的 性能。通过 log file switch(archiving needed)事件可以检查归档进程的性能。 找出瓶颈: 1。通过 Statspack 列出空闲事件。 2。检查不同事件的等待时间开销。 3。检查每条等待记录的平均用时,因为某些等待事件(比较 log file switch completion)可能周 期性地发生,但发生时却造成了严重的性能损耗。 [三思笔记]学习动态性能表 http://www.5ienet.com QQ:5454589 君三思 学习动态性能表 第 19 篇--V$UNDOSTAT 2007.6.14 本视图监控当前实例中 undo 空间以及事务如何运行。并统计 undo 空间开销,事务开销 以及实例可用的查询长度。 V$UNDOSTAT 中的常用列  Endtime:以 10 分钟为间隔的结束时间  UndoBlocksUsed:使用的 undo 块总数  TxnConcurrency:事务并发执行的最大数  TxnTotal:在时间段内事务执行总数  QueryLength:查询长度的最大值  ExtentsStolen:在时间段内 undo 区必须从一个 undo 段转到另一个的次数  SSTooOldError:在时间段内'Snapshot Too Old'错误发生的次数  UNDOTSN:这段时间内最后活动的 undo 表空间 ID 视图的第一行显示了当前时间段的统计,其它的每一条记录分别以每 10 分钟一个区间。 24 小时循环,一天最多 144 条记录。 示例: 1.本例显示 undo 空间从 16:27 到之前 24 小时内的各项统计。 SQL>select * from v$undostat; End-Time UndoBlocks TxnConcrcy TxnTotal QueryLen ExtentsStolen SSTooOldError -------- ---------- ---------- -------- -------- ------------- ------------- 16:07 252 15 1511 25 2 0 16:00 752 16 1467 150 0 0 15:50 873 21 1954 45 4 0 15:40 1187 45 3210 633 20 1 15:30 1120 28 2498 1202 5 0 15:20 882 22 2002 55 0 0 在统计项收集过程中,undo 消耗最高发生在 15:30-15:40 这个时间段。10 分钟内有 1187 个 undo 块被占用(基本上每秒钟 2 个块)。同时,最高事务并发也是在相同的时间段,45 个事 务被并发执行。执行的最长查询(1202 秒)是在 15:20-15:30 之间,需要注意的是查询实际上 是 15:00-15:10 段即开始并直到 15:20 这个时间段。 [三思笔记]学习动态性能表 http://www.5ienet.com QQ:5454589 君三思 学习动态性能表 第 20 篇--V$WAITSTAT 2007.6.15 本视图保持自实例启动所有的等待事件统计信息。常用于当你发现系统存在大量的 "buffer busy waits"时据此做出适当调整。 V$WAITSTAT 中的常用列  CLASS:块类别  WAITS:本类块的等待次数  TIME:本类块的总等待时间 等待发生的原因: 1.undo 段头部:没有足够的回滚段 2.数据段头部/数据段空闲列:空闲列争夺 3.数据块冲突 4.缓存存在大量的 CR 复制 5.range 检索时,索引列存在大量不连续 6.全表检索的表有大量被删除记录 7.高并发的读写块
还剩52页未读

继续阅读

下载pdf到电脑,查找使用更方便

pdf的实际排版效果,会与网站的显示效果略有不同!!

需要 10 金币 [ 分享pdf获得金币 ] 1 人已下载

下载pdf

pdf贡献者

dbsheng

贡献于2017-09-26

下载需要 10 金币 [金币充值 ]
亲,您也可以通过 分享原创pdf 来获得金币奖励!
下载pdf