作者: Maclean Liu, post on August 19th, 2010 做为一个DBA，你大概习惯了定期要抓取数据库中的非绑定变量SQL，这些SQL经常扮演着一箱苹果中蛀虫的角色。 看到下列SQL你必定觉得眼熟:
SELECT substr(sql_text, 1, 80), count(1) FROM v$sql GROUP BY substr(sql_text, 1, 80) HAVING count(1) > 10 ORDER BY 2
是的，以上这段抓取literal sql的脚本大约从8i时代就开始流行了，在那时它很popular也很休闲，使用它或许还会给你的雇主留下一丝神秘感。不过今天我要告诉你的是，它彻底过时了，落伍了，已经不是fashion master了。 10g以后v$SQL动态性能视图增加了FORCE_MATCHING_SIGNATURE列，其官方定义为”The signature used when the CURSOR_SHARING parameter is set to FORCE”，也就是Oracle通过将原SQL_TEXT转换为可能的FORCE模式后计算得到的一个SIGNATURE值。这么说可能不太形象，我们来具体看一下:
SQL> create table YOUYUS (t1 int); Table created. SQL> alter system flush shared_pool; System altered. SQL>select /*test_matching_a*/ * from YOUYUS . . . → Read More: 利用FORCE_MATCHING_SIGNATURE捕获非绑定变量SQL
作者: Maclean Liu, post on August 12th, 2010 这是一套古老的系统，SUNOS 5.8，Oracle 8.1.7.4。最近老革命途遇新问题，告警日志烽烟掠起：
Errors in file /u01/app/oracle/admin/CULPRODB/udump/culprodb_ora_7913.trc: ORA-00600: internal error code, arguments: [17182], [32438472], [], [], [], [], [], [] Thu Jul 15 16:19:29 2010 Errors in file /u01/app/oracle/admin/CULPRODB/udump/culprodb_ora_7913.trc: ORA-00600: internal error code, arguments: [17182], [32438472], [], [], [], [], [], [] Thu Jul 15 16:19:30 2010 Errors in file /u01/app/oracle/admin/CULPRODB/udump/culprodb_ora_7913.trc: ORA-00600: internal error code, . . . → Read More: ora-600 [17182]错误一例
作者: Maclean Liu, post on July 12th, 2010
Applies to:
Oracle Server – Enterprise Edition – Version: 11.1.0.6 Information in this document applies to any platform.
Purpose
This note is for INTERNAL Oracle use. It’s referencing the source of this document on the Development’s Wiki:
Debugging OCI/Client-Side Tools & Applications
This document describes techniques that can be used to diagnose problems . . . → Read More: Debugging OCI/Client-Side Tools & Applications
作者: Maclean Liu, post on June 10th, 2010
Applies to:
Oracle Server – Enterprise Edition – Version: 10.1.0.2 to 11.1.0.7 Information in this document applies to any platform.
Purpose
This script is intended to provide trend analysis information in the form of reports which would be used by the DBA to identify any sessions that over time use significantly more UGA and/or PGA . . . → Read More: Script To Monitor RDBMS Session UGA and PGA Current And Maximum Usage Over Time
作者: Maclean Liu, post on June 2nd, 2010
在Oracle 11g版本中串行的全表扫描可能使用直接路径读取(direct path read)的方式取代之前版本中一直使用的DB FILE SCATTERED READ, 显然direct path read具备更多的优势：
1. 减少了对栓的使用，避免可能的栓争用
2.物理IO的大小不再取决于buffer_cache中所存在的块；试想某个8个块的extent中1,3,5,7号块在高速缓存中，而2,4,6,8块没有被缓存，传统的方式在读取该extent时将会是对2,4,6,8块进行4次db file sequential read,这是一种十分可怕的状况，其效率往往要比单次读取这个区间的所有8个块还要低得多，虽然Oracle为了避免这种情况总是尽可能的不缓存大表的块(读入后总是放在队列最冷的一端);而direct path read则可以完全避免这类问题，尽可能地单次读入更多的物理块。
当然直接路径读取也会引入一些缺点：
1.在直接路径读取某段前需要对该对象进行一次段级的检查点(A segment checkpoint).
2.可能导致重复的延迟块清除操作(我们假设你了解delayed block cleanout是什么).
metalink 文档[ID 793845.1] 对该新版本中的变化进行了描述:
Applies to:
Oracle Server – Enterprise Edition – Version: 11.1.0.6 to 11.1.0.7 This problem can occur on any platform.
Symptoms After migrating an 11g database from a standalone to a 4-node RAC, a noticeable increase of ’direct path read’ waits were observed at times. Here are the Cache sizes and Top 5 events. waits . . . → Read More: 直接路径读取对于延迟块清除的影响
作者: Maclean Liu, post on May 22nd, 2010
as this bug note claimed that:
PROBLEM: ——– Customer frequently receives the following errors while rollback of a transcation using Portal application:
ORA-603: ORACLE server session terminated by fatal error ORA-600: internal error code, arguments: [6856], [0], [0], [], [], [], [], []
ORA-600: internal error code, arguments: [25012], [3], [15], [], [], [], [], . . . → Read More: famous summary stack trace from Oracle Version 8.1.7.4.0 Bug Note
作者: Maclean Liu, post on May 21st, 2010
一位客户的Oracle告警日志中出现了ORA-600 [kddummy_blkchk] [18038]故障，alert中的具体信息：
Errors in file /u01/app/oracle/admin/prdw014a/udump/prdw014a_ora_4377.trc: ORA-00600: internal error code, arguments: [kddummy_blkchk], [222], [5792], [18038], [], [], [], [] Mon May 17 15:27:53 2010 Trace dumping is performing id=[cdmp_20100517152753] Mon May 17 15:27:53 2010 Doing block recovery for file 2 block 504365 Block recovery from logseq 159276, block 166357 to scn 10934615778284 Mon May . . . → Read More: ORA-600 [kddummy_blkchk] [18038] 一例
作者: Maclean Liu, post on April 8th, 2010
oracle 11g r2 on windows platform released today , we can test it now. . . . → Read More: windows平台上的11g release 2终于发布了
|
|
最新评论