简单分析shared pool(三)

时间：2015-07-15 02:30:05 阅读：236 评论：0 收藏：0 [点我收藏+]

提到shared pool，都会不由得和sql语句的解析过程联系起来，因为shared pool所做的主要工作就是解析sql语句，生成执行计划，在之前的两篇中对于shared pool的存储进行了简单的分析，在10g，11g都是保留了255个bucket，可见这个值还是一个最优的默认值了。
在第二篇中分析了一些关于绑定变量的内容，一般一提到sql语句的解析，都会多多少少提到绑定变量，其实有时候也给我们带来一些困扰，其实直接使用绑定是一种情况，还可以通过其它的方式间接使用。
比如下面的例子。
declare
cursor test_cur is select object_id,object_name from t ;
begin
for i in test_cur loop
insert into t values(i.object_id,i.object_name);
end loop;
commit;
end;
/

我们在pl/sql中使用游标的方式，可能我们都没有意识到我们已经在使用了。不过还有一个细节之处就是在pl/sql里面直接调用sql语句的时候，
shared pool里都是转换成大写来处理的。
select hash_value,sql_id,child_latch,version_count,sql_text,parse_calls from v$sqlarea where sql_text like ‘INSERT%‘;

HASH_VALUE SQL_ID        CHILD_LATCH VERSION_COUNT SQL_TEXT PARSE_CALLS
---------- ------------- ----------- ------------- --------------------
1681598159 c0rddkpk3q9qg           3             1 INSERT INTO T VALUES(:B2 ,:B1 )   1

如果在另一种场景中使用绑定变量的方式，结果会略有不同

declare
cursor test_cur is select object_id,object_name from t ;
begin
for i in test_cur loop
execute immediate ‘insert into t values(:a,:b)‘ using i.object_id,i.object_name;
end loop;
commit;
end;
/

使用下面的语句就不会得到需要的信息了。
select hash_value,sql_id,child_latch,version_count,sql_text,parse_calls from v$sqlarea where sql_text like ‘INSERT%‘
而是需要使用
select hash_value,sql_id,child_latch,version_count,sql_text,parse_calls from v$sqlarea where sql_text like ‘insert%‘
HASH_VALUE SQL_ID        CHILD_LATCH VERSION_COUNT SQL_TEXT PARSE_CALLS
---------- ------------- ----------- ------------- --------------------
4026877341 54wqkdbs0ajcx           1             1 insert into t values (:a,:b)    1

得到的sql_id也会大大不同。

如果更近一步我们再来考虑一些额外的影响，我们会发现同样的sql语句在不同的上下文环境中还会有一些不同，
因为在cursor_sharing=EXTRACT的默认模式下反复尝试，Oracle都处理的很好，不会产生大量的child cursor。
为了验证，我们只好动用一些其他的方法，比如修改优化器的一些默认配置，使得两条sql语句运行中的上下文环境会一些明显的不同。
SQL> alter system flush shared_pool; --先来刷新shared pool

System altered.

SQL> alter session set optimizer_index_caching=100 ; --然后修改这个优化器参数

Session altered.

SQL> select hash_value,sql_id,version_count,parse_calls,sql_text from v$sqlarea where sql_text like ‘select sum(object_id) from test.test%‘; --sql语句还么有运行，所以没有任何记录

no rows selected

SQL> select sum(object_id) from test.test; --引用了sum函数，使得优化器参数生效

SUM(OBJECT_ID)
--------------
20545604
这个时候去查看父游标的信息，发现version_count只有一个

SQL> select hash_value,sql_id,version_count,parse_calls,sql_text from v$sqlarea where sql_text like ‘select sum(object_id) from test.test%‘;

HASH_VALUE SQL_ID VERSION_COUNT PARSE_CALLS SQL_TEXT
--------------------------------------------------------------------------------
4120698675 afrus1buttrtm 1 1 select sum(object_id) from test.test
然后我们修改优化器参数，运行同样的sql语句
SQL> alter session set optimizer_index_caching=60;

Session altered.

SQL> select sum(object_id) from test.test;

SUM(OBJECT_ID)
--------------
20545604
这个时候会发现还是出现了不同之处。version_count变为了2

SQL> select hash_value,sql_id,version_count,parse_calls,sql_text from v$sqlarea where sql_text like ‘select sum(object_id) from test.test%‘;

HASH_VALUE SQL_ID VERSION_COUNT PARSE_CALLS SQL_TEXT
--------------------------------------------------------------------------------
4120698675 afrus1buttrtm 2 2 select sum(object_id) from test.test
如果这个时候查看子游标的信息，就会发现child_number会有2个。这也是我们希望看到的不同之处。
SQL> select child_address,hash_value,sql_id,child_number from v$sql where hash_value=‘4120698675‘;

CHILD_AD HASH_VALUE SQL_ID        CHILD_NUMBER
-------- ---------- ------------- ------------
2F155C88 4120698675 afrus1buttrtm            0
2F3A26D8 4120698675 afrus1buttrtm            1

总结一下，在sql语句的执行中，cursor是一个很重要的概念，可能会贯穿整个shared pool的各个层面，通过一些补充的实验可以验证我们原本固有的思想，可能会发现绑定变量在特定的场景下使用效果是很明显的，有时候我们可能都没有意识到本身就在使用。

简单分析shared pool(三)

原文：http://blog.itpub.net/23718752/viewspace-1730139/

踩

(0)

评论一句话评论（0）

分享档案

更多>

2021年09月23日 (328)
2021年09月24日 (313)
2021年09月17日 (191)
2021年09月15日 (369)
2021年09月16日 (411)
2021年09月13日 (439)
2021年09月11日 (398)
2021年09月12日 (393)
2021年09月10日 (160)
2021年09月08日 (222)