Chinaunix首页 | 论坛 | 博客
  • 博客访问: 749264
  • 博文数量: 105
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 3099
  • 用 户 组: 普通用户
  • 注册时间: 2011-03-30 13:00
个人简介

About me:Oracle ACE,optimistic,passionate and harmonious. Focus on oracle programming,peformance tuning,db design, j2ee,Linux/AIX,web2.0 tech,etc

文章分类

全部博文(105)

文章存档

2020年(70)

2014年(3)

2013年(32)

分类: Oracle

2020-07-13 00:18:03

  昨日,一哥们在微信上问我一条SQL优化问题,说的比较急,他百思不得其解,明明单独测试的时候能够走索引,为什么表一关联,打死都不走索引,就算加了HINTS也不走索引,让我帮忙看看。下面就构造一个类似的SQL,如下所示:

select  *

  from t1

  left join t2

    on t1.name = t2.name

 where t1.name = '09DZ8H3XG8ORAH0HUZQI';

  在这里我要说一下,他原来发的是用PL/SQL Developer发的执行计划,如下所示:

 


  这里的t1,t2表的name都有索引,而且有很好的选择性,那按理说,根据条件:

on t1.name = t2.name

 where t1.name = '09DZ8H3XG8ORAH0HUZQI';

t1.name=’09DZ8H3XG8ORAH0HUZQI’的条件应该应该谓词传递给t2.name,转为t2.name=’ 09DZ8H3XG8ORAH0HUZQI’,那么t2表应该要走索引,然而却没有走索引,通过上面的执行计划可以看出一个问题:

全表扫描那行的cardinality=31010,然而真实的结果是:

select count(*) from t2 where t2.name='09DZ8H3XG8ORAH0HUZQI';

 

  COUNT(*)

----------

         1

 

1 row selected.

  第一眼想到的是,这T2表统计信息不对啊,然而T2表的统计信息是刚收集过的:

select num_rows,sample_size,last_analyzed from dba_tab_statistics where table_name='T2';

 

  NUM_ROWS SAMPLE_SIZE LAST_ANALYZED

---------- ----------- -------------------

   3101013     3101013 2020-07-12 23:32:27

而且是100%收集啊,所以统计信息没有问题。
而且单独测试的时候正常走索引:

回头一想,有个大问题,啥大问题呢?

像我平时做SQL Tuning,我很少用PL/SQL Developer啊,这东西显示的执行计划,一般看的不是很直观,还有很多信息需要你自己添加,上面的执行计划就漏掉了最重要的谓词信息。我们做SQL Tuning最好使用SQL*PLUS,文本格式,便于分析,我又让他把SET AUTOTRACE TRACEONLY 的执行计划弄出来给我看看,如下:

Execution Plan

----------------------------------------------------------

Plan hash value: 2757452810

 

-----------------------------------------------------------------------------------------------

| Id  | Operation                            | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                     |        | 31010 |  2180K|  4757   (3)| 00:00:01 |

|   1 |  NESTED LOOPS OUTER                  |        | 31010 |  2180K|  4757   (3)| 00:00:01 |

|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1     |     1 |    46 |     4   (0)| 00:00:01 |

|*  3 |    INDEX RANGE SCAN                  | IDX_T1 |     1 |       |     3   (0)| 00:00:01 |

|*  4 |   TABLE ACCESS FULL                  | T2     | 31010 |   787K|  4753   (3)| 00:00:01 |

-----------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   3 - access("T1"."NAME"=U'09DZ8H3XG8ORAH0HUZQI')

   4 - filter("T1"."NAME"=SYS_OP_C2C("T2"."NAME"(+)) AND

              SYS_OP_C2C("T2"."NAME"(+))=U'09DZ8H3XG8ORAH0HUZQI')

 

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

      16368  consistent gets

          0  physical reads

          0  redo size

        831  bytes sent via SQL*Net to client

        487  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed


  靠,瞬间发现了问题根源,还是SQL*PLUS亲切啊,明显通过谓词部分发现了一个陌生的函数:SYS_OP_C2C,这TMD明显是ORACLE内部隐式类型转换的函数嘛,然后对于cardinality的计算使用函数计算,所以不是准确的,后面的字符串加了个U’,这明显是NVARCHAR2啊,立马让他DESC T1T2表:

desc t1

 Name                                                  Null?    Type

 ----------------------------------------------------- -------- ------------------------------------

 ID                                                             NUMBER

 NAME                                                           NVARCHAR2(100)

 

desc t2

 Name                                                  Null?    Type

 ----------------------------------------------------- -------- ------------------------------------

 ID                                                             NUMBER

 NAME                                                           VARCHAR2(100)

  很明显这是类型不一致,当t1.name=t2.name,因为NVARCHAR2的优先级高于VARCHAR2,所以把T2.name做了隐式类型转换,所以嘛,有索引也用不上啦。以前的NVARCHAR2前面叫N’,现在改成U’,其实是一样的东西,通过dump可以看出:

select dump('09DZ8H3XG8ORAH0HUZQI') a,

dump(n'09DZ8H3XG8ORAH0HUZQI') b,

dump(u'09DZ8H3XG8ORAH0HUZQI') c,

dump(SYS_OP_C2C('09DZ8H3XG8ORAH0HUZQI')) d

from dual;
A

------------------------------------------------------------------------------------------------

B

------------------------------------------------------------------------------------------------

C

------------------------------------------------------------------------------------------------

D

------------------------------------------------------------------------------------------------

Typ=96 Len=20: 48,57,68,90,56,72,51,88,71,56,79,82,65,72,48,72,85,90,81,73

Typ=96 Len=40: 0,48,0,57,0,68,0,90,0,56,0,72,0,51,0,88,0,71,0,56,0,79,0,82,0,65,0,72,0,48,0,72,0,85,

0,90,0,81,0,73

Typ=96 Len=40: 0,48,0,57,0,68,0,90,0,56,0,72,0,51,0,88,0,71,0,56,0,79,0,82,0,65,0,72,0,48,0,72,0,85,

0,90,0,81,0,73

Typ=96 Len=40: 0,48,0,57,0,68,0,90,0,56,0,72,0,51,0,88,0,71,0,56,0,79,0,82,0,65,0,72,0,48,0,72,0,85,

0,90,0,81,0,73

 

  通过SYS_OP_C2C将VARCHAR2转成了NVARCHAR2,SO,解决这个问题:

1.  改语句,将t1.name加上to_char,这样可以避免t2.name的类型转换,因为t1.name已经在where里有条件,这样也不影响t1.name走索引

select *

  from t1

  left join t2

    on to_char(t1.name) = t2.name

 where t1.name = '09DZ8H3XG8ORAH0HUZQI';

执行计划如下:

Execution Plan

----------------------------------------------------------

Plan hash value: 4205057668

 

-----------------------------------------------------------------------------------------------

| Id  | Operation                            | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                     |        |     1 |    72 |     8   (0)| 00:00:01 |

|   1 |  NESTED LOOPS OUTER                  |        |     1 |    72 |     8   (0)| 00:00:01 |

|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1     |     1 |    46 |     4   (0)| 00:00:01 |

|*  3 |    INDEX RANGE SCAN                  | IDX_T1 |     1 |       |     3   (0)| 00:00:01 |

|   4 |   TABLE ACCESS BY INDEX ROWID BATCHED| T2     |     1 |    26 |     4   (0)| 00:00:01 |

|*  5 |    INDEX RANGE SCAN                  | IDX_T2 |     1 |       |     2   (0)| 00:00:01 |

-----------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   3 - access("T1"."NAME"=U'09DZ8H3XG8ORAH0HUZQI')

   5 - access("T2"."NAME"(+)=SYS_OP_C2C("T1"."NAME"))

 

2.  如果改不了语句,那么就对t2.name建立函数索引,如下所示走了函数索引:

create index idx1_t2 on t2(SYS_OP_C2C(NAME));

Execution Plan

----------------------------------------------------------

Plan hash value: 4208491579

 

-------------------------------------------------------------------------------------------------

| Id  | Operation                             | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                      |         | 31010 |  2180K|  4645   (1)| 00:00:01 |

|   1 |  MERGE JOIN OUTER                     |         | 31010 |  2180K|  4645   (1)| 00:00:01 |

|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED | T1      |     1 |    46 |     4   (0)| 00:00:01 |

|*  3 |    INDEX RANGE SCAN                   | IDX_T1  |     1 |       |     3   (0)| 00:00:01 |

|   4 |   BUFFER SORT                         |         | 31010 |   787K|  4641   (1)| 00:00:01 |

|   5 |    TABLE ACCESS BY INDEX ROWID BATCHED| T2      | 31010 |   787K|  4641   (1)| 00:00:01 |

|*  6 |     INDEX RANGE SCAN                  | IDX1_T2 | 12404 |       |     2   (0)| 00:00:01 |

-------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - access("T1"."NAME"=U'09DZ8H3XG8ORAH0HUZQI')

   6 - access("T2"."SYS_NC00003$"(+)=U'09DZ8H3XG8ORAH0HUZQI')


3.  改表设计,保持t1.namet2.name的一致性,这个应该在做设计的时候就考虑,这里不做阐述

 

至此,问题解决。


  题外话,我们经常会遇到类型转换问题导致索引失效,要么是写SQL时候自己加了TO_CHAR,TO_DATE,TO_NUMBER,要么因为类型不一致,ORACLE做了隐式类型转换导致索引失效。要避免这种问题,还是要在表设计的时候,使用常用类型,避免使用一些不常用的比如NVARCHAR2,TIMESTAMP等,写SQL时候遇到类型不一致的,要先测试好,避免上线后出现问题。

附测试语句:

drop table t1;

drop table t2;

create table t1(id number,name nvarchar2(100));

create table t2(id number,name varchar2(100));

create index idx_t1 on t1(name);

create index idx_t2 on t2(name);

begin

  dbms_stats.gather_table_stats(ownname => user,tabname => 't1',no_invalidate => false);

  dbms_stats.gather_table_stats(ownname => user,tabname => 't2',no_invalidate => false);

end;

/

insert into t1

select level,dbms_random.string(opt => 'x',len => 20)

from dual

connect by level<100000;


insert into t2

select level,dbms_random.string(opt => 'x',len => 20)

from dual

connect by level<1000000;


insert into t2

select * from t1

where rownum<1000;


commit;


--问题语句

select *

  from t1

  left join t2

    on t1.name = t2.name

 where t1.name = '09DZ8H3XG8ORAH0HUZQI';                                                                               


    





阅读(2547) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~