AnySQL.net

OR引起的Join性能问题

    在SQL语句中用了OR之后, 其实很不容易控制, 第一次是想让一个带两个OR的用UNION去执行, 结果用了USE_CONCAT后变成了4个UNION了, 居然没有办法让他按我想象的第一个OR进行UNION. 第二次是在索引上, OR条件导致了不能在索引上进行条件过滤. 现在遇到了第三次, 是同事发现的, 怕自已记不住, 就没有征得他的同意, 在这儿共享出来了.     有下面两个表, TYPE_ID列上值的重复性很高. CREATE TABLE T_SMALL (TYPE_ID, ID, ...); CREATE TABLE T_MIDDLE (TYPE_ID, ID1, ID2, ...);     运行下面的SQL时, 总是很慢, 我们已经指定用HASH JOIN, 并也指定了T_SMALL是驱动表, 百思不得其解. SELECT...

Posted on March 15, 2008 10:25 AM

分析用户所有表之后

    随着Oracle对CBO的进一步增强和改进, 对表进行分析已经成为一种常用的调优的手段, 当发现某个表的相关SQL语句的执行计划有问题时, 首先会想是不是统计信息过旧的问题, 如果的确是过旧, 则对这个表进行分析, 以让Oracle重新选择准确的最优执行计划, 以达到调优的目标. 用不合适的方式对表进分析, 则会造成十分严重的后果, 如对一个用户下的所有表进行分析, 或一下子分析多个表. 看到网友的一篇贴子, 让我想起二年多前帮别人处理过的一个案例, 有位DBA对某用户下的所有表进行了分析.     基本情况是, IBM P570, 16CPU, 32GB内存, 24GB的SGA, 支持不了一个50G的9i数据库(OLTP类型), 二百个以内的会话. 在分析之前CPU利用率是50-70%左右, 在分析后则一直是100%. 面临这样的情况后, 由于没有做统计信息(Statistics)的备份, 因此无法恢复以前的情况, 首先做的是删除所有非分区表的统计信息, 对分区表做更精确的统计信息分析, 使之运行于RULE方式, 情况稍有好转, 但用户还是不能接受, 相信Oracle的CBO没有那么差, 因此性能问题的关键并不在于统计信息了. 在处理了以下几个问题之后,...

Posted on January 15, 2008 10:00 PM

MVIEW引起ORA-04031

    有一个数据库报了ORA-04031错误, 等我们上去看时, 却是好的, 通过查询Statspack有关视图, 发现Shared Pool Free Memory在这之前是逐渐减少的, sql area部分占得很大. 可能的原因是, 当Oracle遇到这个错误后, 自动做了一次Flush Pool操作. 于是写了一段程序去跟踪SQL占用的内存, 发现有两个SQL语句很不正常, 版本数很多, 占用的内存也很大, 差不多占了一半的sql area. ASQL> ora share ADDRESS          CHILDS     BYTES ---------------- ------ --------- 0000040000A328F8   1026 215577124 000004000F7AB058   1026 215503530     来看一下这两个SQL语句是什么? ASQL>...

Posted on December 18, 2007 9:50 AM

FOR UPDATE SKIP LOCKED语句中的逻辑读

    今天发现这样的一个语句逻辑读很高, 在Physical Standby上打开查询, 发现逻辑读是很低的, 只有源库的百分之一, 百思不得其解. 回来后建了如下表做测试: SQL> CREATE TABLE T_SKIPLOCKED   2  (   3     NO NUMBER(2),   4     COL2 CHAR(200),   5     CONSTRAINT PK_T_SKIPLOCKED PRIMARY KEY (NO)   6  ); Table created. SQL> INSERT INTO T_SKIPLOCKED SELECT ROWNUM, OBJECT_NAME    2 FROM ALL_OBJECTS...

Posted on August 6, 2007 8:36 PM

通过改写SQL来优化性能, 如何做?

    网友说这个SQL语句跑了两上小时, 没有什么思路去解决. select tbilltrace_43.bno,tbilltrace_43.SAVETIMESTAMP from    ( select bno,SAVETIMESTAMP      from tbilltrace      where  SAVETIMESTAMP>=monitor_start_date        and SAVETIMESTAMP<=monitor_end_date and opcode=43    ) tbilltrace_43 where not exists (select bno    from ( select bno from tbilltrace           where SAVETIMESTAMP>=monitor_start_date               and SAVETIMESTAMP<=monitor_end_date...

Posted on July 11, 2007 12:03 PM

OR引起的性能问题, 在表上进行行过滤

    对SCOTT用户EMP表的DEPTNO和ENAME字段建了一个复合索引. 在SQL的WHERE子句中用了一个OR条件, 看Plan: SQL> SELECT /*+ first_rows no_expand */ * FROM EMP    2 WHERE deptNO=10 AND (ENAME IS NULL OR ENAME > 'A'); --------------------------------------------------------------- | Id  | Operation                   | Name    | Rows  | Bytes | --------------------------------------------------------------- |   0 | SELECT...

Posted on June 6, 2007 4:44 PM

用MyLOG解出对COL$系统表进行的操作

    在LOGTAB.TXT中加入如下行: 10000,21,COL$,     在LOGCOL.TXT中加入如下行, 不过由于COL$是Cluster表, 因此这里列出来的比真实的表列数少一列, 刚好少Cluster的那列: 10000,1,COL#,NUMBER 10000,2,SEGCOL#,NUMBER 10000,3,SEGCOLLENGTH,NUMBER 10000,4,OFFSET,NUMBER 10000,5,NAME,VARCHAR2 10000,6,TYPE#,NUMBER 10000,7,LENGTH,NUMBER 10000,8,FIXEDSTORAGE,NUMBER 10000,9,PRECISION#,NUMBER 10000,10,SCALE,NUMBER 10000,11,NULL$,NUMBER 10000,12,DEFLENGTH,NUMBER 10000,13,SPARE6,DATE 10000,14,INTCOL#,NUMBER 10000,15,PROPERTY,NUMBER 10000,16,CHARSETID,NUMBER 10000,17,CHARSETFORM,NUMBER 10000,18,SPARE1,NUMBER 10000,19,SPARE2,NUMBER 10000,20,SPARE3,NUMBER 10000,21,SPARE4,VARCHAR2 10000,22,SPARE5,VARCHAR2 10000,23,DEFAULT$,LONG     没有办法知道这个操作是对那个对象进行的, 因为OBJ#列的变更不记录在这儿. 终于明白为什么Shareplex不支持Cluster表了, 不过Single Hash...

Posted on June 3, 2007 3:58 PM

AUL功能改进, 生成建表的SQL文件

    在有SYSTEM的情况下, 生成DMP格式时, 会自动包含一个建表的语句, 而在导出成文本文件时, 则没有生成建表语句. 从以往的经历来看, 文本方式的导出更稳定一些, 另外DBA找不表建表语句的情况也常发生, 出于这两点, 我对AUL作了一点改进, 在以文本方式导出时, 自动生成一个建表的SQL文件.     在AUL中早就可以看表结构了: AUL> desc anysql.emp Storage(OBJ#=10560 OBJD=10560 TS=4 FILE=4 BLOCK=627 CLUSTER=0) No. SEQ INT Column Name                   Type --- --- --- ----------------------------- ----------------   1   1   1...

Posted on May 31, 2007 10:55 PM

解出Oracle日志文件中的Redo SQL语句之十

    已花了两周时间搞日志格式研究, 应当收尾了, 这东西长期搞下去没有出路. 没想到仅花了两周时间, 就可以给大家做个演示了. 我在Oracle中运行了以下角本: SQL> insert into mylog values ('My Log 10g',-1,sysdate); SQL> insert into mylog select object_name, object_id, created    2 from user_objects where rownum < 4; SQL> update mylog set created = created +...

Posted on May 30, 2007 6:43 PM

解出Oracle日志文件中的Redo SQL语句之九

    初步完成了对Quick Multi-Insert操作的解释, 虽然在Oracle中是用一句话来进行Insert的, 但解出来时还是折分成一个一个的Insert语句了. 来看一下解出来的结果: RBA=0x000069.0000008d.0010,  XID=0x0005.006.00000093    RID=AAAClAAAEAAAAJ2AAA    INSERT INTO EMP ( EMPNO , ENAME , JOB , MGR , HIREDATE , SAL , COMM , DEPTNO ) VALUES (7369,'SMITH','CLERK',7902,'1980-12-17 00:00:00',800, NULL ,20);    RID=AAAClAAAEAAAAJ2AAB    INSERT...

Posted on May 30, 2007 10:14 AM

解出Oracle日志文件中的Redo SQL语句之八

    我在Oracle中运行了以下语句: SQL> CREATE TABLE MYLOG10G (COL1 NUMBER, COL2 VARCHAR2(20), COL3 DATE); SQL> insert into mylog10g values (100, 'Fangxin', sysdate); SQL> update mylog10g set col2='FANGXIN' where col1=100; SQL> delete mylog10g where col1=100; SQL> select object_id from dba_objects where object_name='MYLOG10G';...

Posted on May 29, 2007 12:00 PM

解出Oracle日志文件中的Redo SQL语句之七

    对于Delete操作, 取得相应的RowID. SQL> SELECT ROWID,EMPNO FROM EMP WHERE EMPNO=7934; ROWID                   EMPNO ------------------ ---------- AAAClAAAEAAAAJ3AAb       7934 SQL> DELETE EMP WHERE EMPNO=7934; 1 row deleted. Start extract redo SQL ... RBA=0x000062.00000002.0084,  XID=0x0009.01d.00000090, RID=AAAClAAAEAAAAJ3AAb     DELETE OBJ_10560 WHERE ROWID = ?;    ...

Posted on May 28, 2007 10:24 PM

解出Oracle日志文件中的Redo SQL语句之六

    终于找到以前的代码为什么不能处理10g日志的原因了, 在10g中, 不知道为什么DML(Layer 11 Opcode 5)跑到了Undo信息(Layer 5 Opcode 1)的前面去了, 而在9i和8i中, 在DML之前绝对是Undo的信息. 下面是在Oracle中DUMP LOGFILE命令生成的一条日志记录: REDO RECORD - Thread:1 RBA: 0x000059.0000003d.0010 LEN: 0x0270 VLD: 0x0d SCN: 0x0000.0010ca12 SUBSCN:  1 05/08/2007 13:10:34 CHANGE #1 TYP:2 CLS: 1 AFN:1 DBA:0x0040166a OBJ:732 SCN:0x0000.0010ca0f SEQ:  1...

Posted on May 28, 2007 2:29 PM

解出Oracle日志文件中的Redo SQL语句之五

    Oracle 10g的日志文件格式和8i/9i有很大的不同, 这是早就知道的. 一直没有对10g版本的日志格式没有深入研究, 即然最近更深入地研究了日志格式, 不如将10g的也解决了吧. 昨晚大约花了2个多小时, 终于可以将10g日志文件中的Log Record一个一个地分开来了, 9i中区分Change的那部份代码不能直接用在10g上, 还是花点时间研究一下, 估计也就是保留字节变多了或变少了, 应当不难.     下面是用Oracle 10g的DUMP LOGFILE命令生成Trace文件, 然后用grep "RBA:"命令整出来的最前五行和最后五行: REDO RECORD - Thread:1 RBA: 0x000059.00000002.0010 LEN: 0x0070 VLD: 0x05 REDO RECORD - Thread:1 RBA: 0x000059.00000003.0010 LEN: 0x0290...

Posted on May 25, 2007 9:21 AM

解出Oracle日志文件中的Redo SQL语句之四

    要想得到更直观的SQL语句, 就得引入数据字典, 这里面主要需要以下三个方面的信息: Table (Object ID, Table Name, Partition Name) Column (Segment Column ID, Column Name, Column type) Key (The columns of primary key or unique index, for updating and deleting)     在MyLOG中引入了前两个: Table (LOGTAB.TXT)和Column (LOGCOL.TXT) $...

Posted on May 24, 2007 11:37 AM

解出Oracle日志文件中的Redo SQL语句之二

    到目前为止, 形成Redo的SQL语句(先不考虑Row Chain, Row Migration及LOB等情况)是没有问题了. 留下的一个很艰难的任务是如何区分事务. 即解出来的那一些语句是属于一个事务(Transaction)的? 因为Redo SQL解出来已经按照先后顺序了, 只要找出属于同一个事务的Redo记录中的共同点就可以了.     先不考虑日志中的内容, 则会话的SID和SERIAL#值到是很可以用来作为这一样的一根线的, 可惜的是在日志中并没有记录这两个值, 因此这是不行的. 为了找出这一条线, 我们先来看一下标记一个事务为提交的Redo记录(Layer=5, OP=4) : REDO RECORD - Thread:1 RBA: 0x005e30.00000006.0028 LEN: 0x0050 VLD: 0x01 SCN: 0x031f.05c00827 SUBSCN:  1 08/23/2006 19:08:22 CHANGE #1 TYP:0...

Posted on May 22, 2007 9:57 PM

解出Oracle日志文件中的Redo SQL语句之一

    由于精力所限, 一年半之间开始研究Oracle日志文件格式, 到现在都没有突破的成就, 一方面是因为工作越来越忙了, 另一方面是因为个人的精力实在有限, 因此在这一年半内, 基本上没有投入多一点点的时间去研究. 经过这么长的时间, 是应当继续向前走一步了, 今天就改造了一下我的MyLOG小工具, 以如何解出Redo SQL和搞清楚事务控制为中心.     现在不区分事务, 已经可以解出SQL语句了, 不过还需要进行很多的验证, 才能确定其准确性. 现在我是在Windows上打开了一个Solaris下的Oracle的日志文件: LOG> set byte_order big BYTE_ORDER = BIG LOG> open c:\mydul\utility\gbcust1_24112.arc DBID = 0x9d671cf9 = 2640780537 GROUP      = 8, SEQUENCE   =...

Posted on May 21, 2007 8:50 PM

如何创建Single Hash Cluster的表?

    Hash Cluster的表可以在没有索引的情况下, 获得对表的极快访问, 这种访问的逻辑读比维一性索引还有效. 在这儿有一张表T_OBJECTS, 其中的OBJECT_ID是主键, 大量的SQL语句都是根据OBJECT_ID去访问其他字段, 通过索引的情况下每次执行的逻辑读已经只有3了(Index Root->Leaf->Table), 但是就是这样一句简单的SQL的逻辑读占据了大半. 因此考虑到使用Single Hash Cluster表来进行调优. 应当如何来创建这个Cluster呢? 决定Cluster性能的主要有两个因素: SIZE和HASHKEYS.     下面是我的思路, 首先分析一下现在的表, 获得比较准确的块数. SQL> SELECT BLOCKS FROM USER_TABLES WHERE TABLE_NAME='T_OBJECTS';     BLOCKS ----------        118     因为在这个情况下, 数据分布均匀, 因此我只要将HASHKEYS定义为块数就已经比较好了, 实际上为了更安全,...

Posted on April 24, 2007 10:36 PM

选择了错误的实现方法, 其实很简单!

    今天写了下面这一段SQL语句, 在写之前总觉得有更简便的方法, 可就是没有想出来: SELECT    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(       REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TABLE_NAME,'0','#'),          '1','#') ,'2','#') ,'3','#') ,'4','#')              ,'5','#') ,'6','#') ,'7','#') ,'8','#') ,'9','#')  PATTERN_NAME FROM USER_TABLES     下班时坐地铁, 突然想到了Translate函数, 原来可以这么简单: SELECT    TRANSLATE(TABLE_NAME,'0123456789','##########') PATTERN_NAME FROM USER_TABLES     我写这个SQL是用来找出一些表结构应当相同的表, 最终的SQL应当如下: SELECT TABLE_NAME FROM...

Posted on April 24, 2007 8:18 PM

过长的In List引起的共享池内存问题

    开发人员经常想用一个长长的In List来作为Where条件, 但这样的语句如果查执行计划不对的话, 很容易造成共享池(Shared Pool)内存问题, 下面是一个拥有40个值的In List的测试. 占用的内存如下所示: SQL> SELECT HASH_VALUE,SHARABLE_MEM,PERSISTENT_MEM,RUNTIME_MEM   2  FROM V$SQL WHERE HASH_VALUE IN (1981294536 ,3511787793); HASH_VALUE SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM ---------- ------------ -------------- ----------- 1981294536        31836           1088        5336 3511787793       171534           1088      127936     其中1981294536的计划中用的是INLIST ITERATOR方法, 3511787793语句用的是CONCATENATION方法. 源语句如下: SQL>...

Posted on March 22, 2007 6:24 PM

列类型和变量类型不一致引起的性能问题?

    一条不能再简单的SQL引起了性能问题, Where条件中只有一个列, 并且有索引, 这是为什么呢? 请看下面的例子: SQL> CREATE TABLE T_OBJECTS   2  (   3    COL1 NUMBER, COL2 VARCHAR2(20), COL3 VARCHAR2(30)   4  ); Table created. SQL> CREATE INDEX IDX_T_OBJECTS_1 ON T_OBJECTS(COL1); Index created. SQL> CREATE INDEX IDX_T_OBJECTS_2 ON T_OBJECTS(COL2); Index created. SQL> INSERT INTO...

Posted on December 25, 2006 9:19 PM

通过改变SQL来简化工作, 减轻工作量(二)

    Oracle的分析函数功能强大, 可以用于SQL的优化, 往往用普通的SQL需要好几次表扫描的, 用了分析函数后可以一句话轻松搞定. 我们来看一下统计学生成绩的例子吧.     我不想专门建一个成绩表, 就用DBA_OBJECTS来表示吧, 用OBJECT_TYPE来表示班级或年级, 用OBJECT_ID余100的值来作成绩, 为了减少记录显示, 我只查了"TABLE"和"INDEX"两个班级, 通过使用WIDTH_BUCKET函数可以很容易地实现这一要求, 如下所示: ASQL> SELECT CLASS_ID,SCORE_LEVEL,COUNT(*) NROWS     2 FROM(     3 SELECT     4    OBJECT_TYPE CLASS_ID,     5    WIDTH_BUCKET(MOD(OBJECT_ID,100),60,100,4) SCORE_LEVEL     6 FROM DBA_OBJECTS WHERE OBJECT_TYPE IN ('TABLE','INDEX'))     7 GROUP BY CLASS_ID,SCORE_LEVEL...

Posted on September 19, 2006 8:51 PM

通过改变SQL来简化工作, 减轻工作量(一)

    今天接到一个任务, 将在大约二十几个数据库中运行以下的两个SQL来取某个值, 这个表很大, 大约快100个G了, 因此是一件不小的任务. SELECT /*+ FULL(T) PARALLEL(T,4) */  COUNT(*) FROM BIGTABLE T WHERE COL1 < 1000000000   AND ...... / SELECT /*+ FULL(T) PARALLEL(T,4) */  COUNT(*) FROM BIGTABLE T WHERE COL1 > 1000000000   AND ...... /     因此我将这两个SQL进行合并,...

Posted on September 14, 2006 4:32 PM