据大多数的Oracle性能问题,根源无外乎三类:架构设计问题、详细模块设计细节和SQL语句。从数量上看,三类性能问题中,SQL语句问题是我们最常见的一种性能问题点。开发DBA很大一部分日常工作精力是消耗在SQL语句调优和问题调整上。
Oracle数据表列类型是一种强类型数据结构,不同类型的数据有不同的操作使用方法。当不同类型的数据进行直接的操作,没有显式的进行转换时,我们称之为“隐式类型转换”。SQL隐式类型转换可以帮助SQL执行过程,很多时候会引起一些问题。本篇主要想介绍一些问题点。
1、环境准备
我们依然选择Oracle 11R2进行试验。
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 – Production
创建数据表t,其中包括了我们最常见的varchar2和number两种类型。
SQL> create table t as select * from dba_objects;
Table created
SQL> desc t;
Name Type Nullable Default Comments
-------------- ------------- -------- ------- --------
OWNER VARCHAR2(30) Y
OBJECT_NAME VARCHAR2(128) Y
SUBOBJECT_NAME VARCHAR2(30) Y
OBJECT_ID NUMBER Y
DATA_OBJECT_ID NUMBER Y
OBJECT_TYPE VARCHAR2(19) Y
(篇幅原因,有省略……)
NAMESPACE NUMBER Y
EDITION_NAME VARCHAR2(30) Y
2、字符串转数字
我们首先看一下,如果目标列是一个数字类型,但是输入值是一个字符串,应该如何处理?
为了帮助我们理解对SQL执行计划的作用,我们添加一些索引对象。
--object_id列是一个数字类型对象
SQL> create index idx_t_id on t(object_id);
Index created
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);
PL/SQL procedure successfully completed
SQL> explain plan for select * from t where object_id=1000; --类型匹配,无隐式转换;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 514881935
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Ti
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 97 | 2 (0)| 00
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 97 | 2 (0)| 00
|* 2 | INDEX RANGE SCAN | IDX_T_ID | 1 | | 1 (0)| 00
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=1000)
14 rows selected
在没有发生类型转换的时候,是可以正确发挥索引路径的作用。下面我们如果对object_id数字类型的对应一个字符串’1000’,结果如何呢?
SQL> explain plan for select * from t where object_id='1000';
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 514881935
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Ti
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 103 | 2 (0)| 00
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 103 | 2 (0)| 00
|* 2 | INDEX RANGE SCAN | IDX_T_ID | 1 | | 1 (0)| 00
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=1000)
14 rows selected
注意结果:首先,SQL语句发生了隐式转换,而且转换的地方在字符串1000上面,转换为数字1000。这样的变化没有发生在数字列上面。其次,这种转换没有发生在列上面,没有影响到idx_t_id的路径。
如果我们使用绑定变量,现象如何呢?
--定义绑定变量x,是字符串类型;
SQL> var x varchar2(10);
SQL> exec :x := '1000';
PL/SQL procedure successfully completed
x
---------
1000
执行SQL语句。
SQL> select /*+Demo*/count(*) from t where object_id=:x;
COUNT(*)
----------
1
x
---------
1000
SQL> select sql_id, executions from v$sqlarea where sql_text like 'select /*+Demo*/count(*) from t%';
SQL_ID EXECUTIONS
------------- ----------
7cj6jfauhjvua 1
SQL> select * from table(dbms_xplan.display_cursor(sql_id => '7cj6jfauhjvua'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 7cj6jfauhjvua, child number 0
-------------------------------------
select /*+Demo*/count(*) from t where object_id=:x
Plan hash value: 1700799834
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | INDEX RANGE SCAN| IDX_T_ID | 1 | 5 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=TO_NUMBER(:X))
19 rows selected
我们使用dbms_xplan.display_cursor从shared pool中抽取出带绑定变量的SQL语句执行计划。在谓词信息中,看到了access动作中to_number处理。
说明:Oracle发现类型的不匹配之后,如果数据表列是数字类型,而输入值是一个字符串,Oracle会对字符串进行to_number函数处理。这种情况是正向的,不会影响到索引列的使用。
说道to_number,难道Oracle就不检查变换是不是符合标准吗?
SQL> explain plan for select * from t where object_id='kk';
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 514881935
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Ti
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 103 | 2 (0)| 00
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 103 | 2 (0)| 00
|* 2 | INDEX RANGE SCAN | IDX_T_ID | 1 | | 1 (0)| 00
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=TO_NUMBER('kk'))
14 rows selected
SQL> select * from t where object_id='kk';
select * from t where object_id='kk'
ORA-01722: invalid number
在进行类型转换的时候,Oracle依然用to_number处理。但是在执行过程中,会报错。
那么,如果我们把关系返回来,会怎么样?
3、数字转字符串
我们处理一下字符串情况。
--edition_name是字符串类型
SQL> update t set edition_name=to_char(object_id);
72775 rows updated
SQL> commit;
Commit complete
在列上添加索引,判断没有隐式转换的情况。
SQL> create index idx_t_edname on t(edition_name);
Index created
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);
PL/SQL procedure successfully completed
SQL> explain plan for select * from t where edition_name='1000'; --类型匹配过程
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1587954238
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 103 | 2 (0)
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 103 | 2 (0)
|* 2 | INDEX RANGE SCAN | IDX_T_EDNAME | 1 | | 1 (0)
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EDITION_NAME"='1000')
14 rows selected
如果我们在取值上给一个数字类型。
--不匹配情况
SQL> explain plan for select * from t where edition_name=1000;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 103 | 273 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T | 1 | 103 | 273 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("EDITION_NAME")=1000)
13 rows selected
注意,我们看到了不同,如果列是一个字符串类型,输入一个数字类型条件值,就会让Oracle在列上面添加to_number函数。也就是说,会对字符串对象进行处理。
如果我们使用绑定变量方式,如何呢?
SQL> var x number;
SQL> exec :x := 1000;
PL/SQL procedure successfully completed
x
---------
1000
SQL> select /*+Demo2*/count(*) from t where edition_name=:x;
COUNT(*)
----------
1
x
---------
1000
SQL> select sql_id, executions from v$sqlarea where sql_text like 'select /*+Demo2*/count(*) from t%';
SQL_ID EXECUTIONS
------------- ----------
7vbr16s0ra00x 1
SQL> select * from table(dbms_xplan.display_cursor(sql_id => '7vbr16s0ra00x'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 7vbr16s0ra00x, child number 0
-------------------------------------
select /*+Demo2*/count(*) from t where edition_name=:x
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 273 (100)| |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | TABLE ACCESS FULL| T | 1 | 6 | 273 (1)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TO_NUMBER("EDITION_NAME")=:X)
19 rows selected
由于对列进行了to_number处理,索引idx_t_edname不能使用上。
同样,这个处理是对edition_name进行选择to_number处理。如果:x是一个数字,但是edition_name存在一些不能转换的字符串,会如何呢?
--可以执行;
SQL> select edition_name from t where edition_name=1000;
EDITION_NAME
------------------------------
1000
我们对数据进行一些处理,不影响最终结果,但是可能引起执行计划中的矛盾。
SQL> insert into t (edition_name) values ('kkk');
1 row inserted
SQL> commit;
Commit complete
SQL> select * from t where edition_name=1000;
select * from t where edition_name=1000
ORA-01722: invalid number
报错,相同的数据结果。但是使用字符串1000,结果集合可以正常获得。
SQL> select edition_name from t where edition_name='1000';
EDITION_NAME
------------------------------
1000
这个就是由于Oracle执行计划而带来的问题。当发生类型转换的时候,Oracle倾向于对字符串类型进行函数处理转换类型,这种处理可能发生在条件值上,也可能发生在列上。
5、结论
Oracle SQL语句和执行计划是一个非常精巧的体系和结构,虽然提供了类型隐式转化功能,但是对我们开发人员而言,尽量不要使用。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17203031/viewspace-766790/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/17203031/viewspace-766790/