[20150513]函数索引与CURSOR_SHARING=FORCE

[20150513]函数索引与CURSOR_SHARING=FORCE.txt

--经常awr报表,大量听到的建议是你们的应用没有使用绑定变量.国内的许多项目这个问题更加严重,我敢打赌国内80%甚至更高的比例在
--应用中没有绑定变量(OLTP系统).

--如果一个新项目我只要看一下程序使用绑定变量的情况,就知道这个项目是垃圾还是豆腐渣工程.到目前为止我接触的项目仅仅有1个做
--的稍微好一点.

--如果不修改代码,一个最简单的方式就是修改参数CURSOR_SHARING = FORCE(补充一点:我个人不建议修改SIMILAR,实际上11G这个参数还
可以设置,但是无效的.),但是在这种情况下如果存在函数索引,可能导致这个参数修改可能带了另外的问题,就是索引无效,选择全表扫描.

--我这里举一个例子,说明另外的情况,参考链接如下,我仅仅重复测试:
http://oracle-randolf.blogspot.com/2015/04/function-based-indexes-and.html

SCOTT@test> @ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.3.0     Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

create table t
as
select * from all_objects;

create index t_idx on t (owner || ' ' || object_name);

exec dbms_stats.gather_table_stats(null, 't')

set echo on linesize 200 pagesize 0

alter session set cursor_sharing = force;

select /*+ full(t) */ * from t where owner || ' ' || object_name = 'BLA';
select * from table(dbms_xplan.display_cursor);

SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
SQL_ID  ar3tw7r1rvawk, child number 0
-------------------------------------
select /*+ full(t) */ * from t where owner || :"SYS_B_0" || object_name
= :"SYS_B_1"
Plan hash value: 1601196873
---------------------------------------------------------------------------
| Id  | Operation         | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |        |       |   193 (100)|          |
|*  1 |  TABLE ACCESS FULL| T    |      1 |   118 |   193   (2)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OWNER"||' '||"OBJECT_NAME"=:SYS_B_1)
--注意看语句已经转换为select /*+ full(t) */ * from t where owner || :"SYS_B_0" || object_name = :"SYS_B_1".
--但是下面的filter条件filter("OWNER"||' '||"OBJECT_NAME"=:SYS_B_1),没有转换.

select /*+ index(t) */ * from t where owner || ' ' || object_name = 'BLA';
select * from table(dbms_xplan.display_cursor);
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  6kzz3vw5x8x3b, child number 0
-------------------------------------
select /*+ index(t) */ * from t where owner || :"SYS_B_0" ||
object_name = :"SYS_B_1"
Plan hash value: 470836197
--------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |        |       |     4 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |      1 |   118 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_IDX |      1 |       |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / T@SEL$1
   2 - SEL$1 / T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T"."SYS_NC00016$"=:SYS_B_1)

--可以发现可以使用索引.奇怪的是语句已经转化为
--select /*+ index(t) */ * from t where owner || :"SYS_B_0" ||object_name = :"SYS_B_1".

select /*+ index(t) */ * from t where owner || 'A' || object_name = 'BLA';
select * from table(dbms_xplan.display_cursor);

SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  6kzz3vw5x8x3b, child number 1
-------------------------------------
select /*+ index(t) */ * from t where owner || :"SYS_B_0" ||
object_name = :"SYS_B_1"
Plan hash value: 3778778741
--------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |        |       | 53682 (100)|          |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T     |    752 | 88736 | 53682   (1)| 00:00:02 |
|   2 |   INDEX FULL SCAN           | T_IDX |  75193 |       |   432   (1)| 00:00:01 |
--------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / T@SEL$1
   2 - SEL$1 / T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OWNER"||:SYS_B_0||"OBJECT_NAME"=:SYS_B_1)

--依旧可以使用索引,也许是这个条件特殊.

--10g下我也测试看看:
SCOTT@test> @ &r/ver1

PORT_STRING                    VERSION        BANNER
------------------------------ -------------- ----------------------------------------------------------------
x86_64/Linux 2.4.xx            10.2.0.4.0     Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

--建表过程略.

select /*+ full(t) */ * from t where owner || ' ' || object_name = 'BLA';

SCOTT@test> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
SQL_ID  ar3tw7r1rvawk, child number 0
-------------------------------------
select /*+ full(t) */ * from t where owner || :"SYS_B_0" || object_name
= :"SYS_B_1"

Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |   159 (100)|          |
|*  1 |  TABLE ACCESS FULL| T    |     1 |    98 |   159   (2)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OWNER"||' '||"OBJECT_NAME"=:SYS_B_1)

select /*+ index(t) */ * from t where owner || ' ' || object_name = 'BLA';
SCOTT@test> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
SQL_ID  6kzz3vw5x8x3b, child number 0
-------------------------------------
select /*+ index(t) */ * from t where owner || :"SYS_B_0" || object_name =
:"SYS_B_1"
Plan hash value: 470836197
-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |    98 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_IDX |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T"."SYS_NC00014$"=:SYS_B_1)

select /*+ index(t) */ * from t where owner || 'A' || object_name = 'BLA';
select * from table(dbms_xplan.display_cursor);

SCOTT@test> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  6kzz3vw5x8x3b, child number 1
-------------------------------------
select /*+ index(t) */ * from t where owner || :"SYS_B_0" || object_name =
:"SYS_B_1"
Plan hash value: 3778778741
-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |       |       | 35550 (100)|          |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T     |   502 | 49196 | 35550   (1)| 00:07:07 |
|   2 |   INDEX FULL SCAN           | T_IDX | 50234 |       |   288   (1)| 00:00:04 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OWNER"||:SYS_B_0||"OBJECT_NAME"=:SYS_B_1)

时间： 2024-10-23 18:23:02

[20150513]函数索引与CURSOR_SHARING=FORCE的相关文章

[20130313]cursor_sharing=force的问题.txt

[20130313]cursor_sharing=force的问题.txt 前一阵子,我把生产系统的cursor_sharing设置为force, 大概按照链接: http://space.itpub.net/267265/viewspace-754003 我发现一个奇怪的问题,自己在测试环境重复测试看看. 1.建立测试环境: SQL> select * from v$version where rownum BANNER ------------------------------------

PHP函数索引（3）

函数|索引 mcrypt_ecb: 使用 ECB 将资料加/解密. mcrypt_get_block_size: 取得编码方式的区块大小. mcrypt_get_cipher_name: 取得编码方式的名称. mcrypt_get_key_size: 取得编码钥匙大小. mcrypt_ofb: 使用 OFB 将资料加/解密. md5: 计算字符串的 MD5 哈稀. mhash: 计算哈稀值. mhash_count: 取得哈稀 ID 的最大值. mhash_get_block_size: 取得哈

在Informix中创建并使用函数索引

随着数据量以惊人速度不断增长,数据库管理系统将继续关注性能问题.本文主要介绍一种名为函数索引(functional index)的性能调优技术.根据数据库使用情况的统计信息创建并使用函数索引,可以显著提升SELECT 查询的性能.通过本文了解如何在IBM Informix Dynamic Server 中创建和使用函数索引并最大限度提升查询性能. 简介 在选择数据库管理系统(DBMS)时,性能是一个关键的考虑因素.在执行SELECT.INSERT.UPDATE 和 DELETE 操作时,很多因素

由cursor_sharing=force导致的ora-600错误

1.在alert_lxdb.log日志中报600错误 Errors in file /u01/app/oracle/admin/lxdb/udump/lxdb_ora_50379.trc: ORA-00600: internal error code, arguments: [kkslhsh1], [101], [], [], [], [], [], [] 注意这个问题可能会导致产生非常大的trc 文件而导致 文件系统满! 2.本问题的产生根本原因: 设置了 cursor_sharing = f

A Case about cursor_sharing=FORCE can introduce a execute plan stale

今天一位同事带着非常忐忑的心情来找我,棋牌项目的ORACLE数据库负载过高. 我连上服务器一看,果然,LOAD 100多.IOWAIT非常低. 再TOP一下,发现ACTIVE进程非常多,单个消耗CPU在20%左右. 然后这位同事跟我描述了一下,今天上了一个推广的活动,可能导致业务量猛增,我开始怀疑是正常的业务请求. --- 此处省略1000字. 于是开始抓STATSPACK报告,不过LOAD 100多抓起来非常呛. 最终发现以下SQL消耗CPU过多. 因为开启了cursor_sharing=FO

警惕！自定义函数索引的那些陷阱及避坑术

作者介绍 丁俊,DBAplus社群联合发起人,新炬网络专家团成员,性能优化专家,Oracle ACEA,ITPUB开发版资深版主.十年电信行业从业经验,从事过系统开发与维护.业务架构和数据分析.系统优化等工作.电子工业出版社终身荣誉作者,<剑破冰山-Oracle开发艺术>副主编.   当我们对列使用了函数运算之后,如果此列没有函数索引,那么普通索引是无效的.比如where substr(name,1,3)='abc';如果建立了create INDEX idx_t ON t(NAME);  

[20171202]关于函数索引的状态.txt

[20171202]关于函数索引的状态.txt --//我曾经在一篇贴子提到索引可以disable吗?链接: --//http://blog.itpub.net/267265/viewspace-2123537/ --//实际上仅仅函数索引能disable,为什么呢?实际上自己以前并不搞清楚实际上这个跟oracle使用函数的特殊性有关. --//如果一个表删除某个字段,对应的索引也会删除.如果定义的函数删除了,对应的函数索引呢?通过例子来说明问题: 1.环境: SCOTT@test01p> @

[20170402]函数索引standard_hash.txt

[20170402]函数索引standard_hash.txt --//这几天放假,在家里看书<<Apress.Expert.Oracle.Indexing.and.Access.Paths.Maximum.Performance.for.Your.Database.2nd.Edition.148421983X.pdf>> --//这本书比较合适初学者,我以前看过第一版,所以这个版本看的很快. --//里面提到函数standard_hash,可以用来满足一般的查询,做一个记录. 1

[20150803]使用函数索引注意的问题.txt

[20150803]使用函数索引注意的问题.txt --昨天在10g下优化时遇到一个奇怪的问题,做一个记录: --首先说明一下,最近一段时间在做一个项目的优化,这个项目实际上ETL项目: http://baike.baidu.com/link?url=OlbL-2LIVu06toxpf5-PxgekWlOtRgrdwPhGYNx9TgCnCC5WdAGiwOWQXcfUbujcUNwUU6ojdanwP1wSbC_Vf95sgbq7PonHaEZWBVrqkQm ETL,是英文 Extract-