简单分析shared pool(二) (r3笔记48天)

对于shared pool的学习,发现越尝试去了解,发现自己对它越不了解。里面的东西很杂。 自己想用几个问题来作为引子来说明更加会有条理一些。 shared pool的大小设置 对于shared pool的大小设置,从早期版本到现在一直都带有争论。 从操作上来说,需要设置shared_pool_size就可以了,如果启用了sga_target或者11g里的memory_target,那shared pool的大小设置都是自动管理的了。 还有shared_pool_reserved_size会在shared pool中保留一块固定的内存区域留给一些大对象使用,它的空间是独立的。 从理论上来说,shared pool中含有的free list,或者是bucket,上面有很多的chunk,如果一条sql语句进入library cache,需要申请一块新的内存空间的时候,就需要遍历free list,如果free list很长,在这个过程中也会持有latch,直到解析完成。所以从这个角度来说,设置小的shared pool可能能减少latch的持有时间,但是反过来说,如果shared pool太小,可能空间老是紧张,会有频繁的换入换出的chunk操作,无论扫描还是对chunk的管理都是需要持有latch的,都在一定程度上影响性能,如果设置shared Pool大一些,可能能够延缓一下latch的争用,但是如果随着free list中的碎片增多。导致free类型的chunk越来越多,最后也还是会造成争用。 shared pool的sub pool 这个问题可以从第一个问题得到延伸,如果单纯设置shared pool过大存在问题,设置太小也有问题,从oracle的设计角度来说,就根据系统的情况,可以指定sub pool,比如我的机器配置足够好。可以设置多个sub pool来,每个sub pool都是都有单独的free list,和保留区域,但是彼此之间还是通过latch来并发共享。这样也可以在一定程度上提高shared pool的性能。 11g开始,每个sub pool都为512M 可以通过隐含参数来查看当前的库中sub pool的设置。

1* select a.ksppinm,b.ksppstvl from x$ksppi a,x$ksppsv b where a.indx=b.indx and a.ksppinm='_kghdsidx_count' SQL> /

KSPPINM KSPPSTVL -------------------- ---------------------------------------- _kghdsidx_count 1

我本地的环境配置比较差,目前只有一个sub pool,因为shared_pool的大小是200M,没有足够的资源。 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ shared_pool_reserved_size big integer 10M shared_pool_size big integer 200M 关于绑定变量和硬解析 提到shared pool,不得不提绑定变量,在线业务系统中是很关键的一个指标。 可以举一个例子来简单说明一下。 首先创建一个表test_var,然后传入两个变量值,类型不同,看看执行的情况。 create table test_var as select object_id id,object_name name from user_objects where rownum<3; --只以两条数据为例子 update test_var set name='aaa' where rownum<2; --修改一条数据 update test_var set name='bbb' where name!='aaa'; --修改另外一条数据

SQL> alter system flush shared_pool;

System altered.

SQL> variable name varchar2(100); SQL> exec :name:='aaa';

PL/SQL procedure successfully completed.

SQL> select *from test_var where name=:name;

ID NAME ---------- -------------------------------------------------------------------------------------------------------------------------------- 21014 aaa 运行语句之后,查看sql_id,和hash值,从v$sqlarea中可以查看version_count,如果发生了硬解析,version_count就会递增。 SQL> select sql_id,hash_value,address,child_address from v$sql where sql_text like 'select *from test_var%';

SQL_ID HASH_VALUE ADDRESS CHILD_ADDRESS ------------- ---------- ---------------- ---------------- 3vm96qwzm0mg2 1060130274 000000006B751178 0000000069764850

SQL> col sql_text format a50 SQL> select sql_text,version_count from v$sqlarea where sql_text like 'select *from test_var%';

SQL_TEXT VERSION_COUNT -------------------------------------------------- ------------- select *from test_var where name=:name 1

然后再来赋另外一个值,看看version_count会不会递增。

SQL> exec :name:='bbb';

PL/SQL procedure successfully completed.

SQL> select *from test_var where name=:name;

ID NAME ---------- -------------------------------------------------------------------------------------------------------------------------------- 15287 bbb

SQL> col sql_text format a50 SQL> select sql_id,hash_value,address,child_address from v$sql where sql_text like 'select *from test_var%';

SQL_ID HASH_VALUE ADDRESS CHILD_ADDRESS ------------- ---------- ---------------- ---------------- 3vm96qwzm0mg2 1060130274 000000006B751178 0000000069764850

SQL> SQL> select sql_text,version_count from v$sqlarea where sql_text like 'select *from test_var%';

SQL_TEXT VERSION_COUNT -------------------------------------------------- ------------- select *from test_var where name=:name 1

可以看到,没有任何的变化,说明绑定变量起作用了,没用再次硬解析。 来改一下数据类型,看看效果。我把变量类型从varchar2改为了char

SQL> variable name char(3); SQL> exec :name:='aaa';

PL/SQL procedure successfully completed.

SQL> select *from test_var where name=:name;

ID NAME ---------- -------------------------------------------------------------------------------------------------------------------------------- 21014 aaa

SQL> select sql_text,version_count from v$sqlarea where sql_id='3vm96qwzm0mg2' 2 /

SQL_TEXT VERSION_COUNT -------------------------------------------------- ------------- select *from test_var where name=:name 2

SQL> select sql_id,hash_value,address,child_address from v$sql where sql_text like 'select *from test_var%';

SQL_ID HASH_VALUE ADDRESS CHILD_ADDRESS ------------- ---------- ---------------- ---------------- 3vm96qwzm0mg2 1060130274 000000006B751178 0000000069764850 3vm96qwzm0mg2 1060130274 000000006B751178 000000006A039F40

不同之处就是child_address,说明走了两次硬解析。 可以想象如果在繁忙的业务系统中如果大量的sql语句走反复解析的话,会耗费大量的cpu资源和时间。导致系统性能的下降。

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2014-11-06

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏JavaEdge

MySql 全方位基础优化定位执行效率低的SQL语句存储过程与触发器的区别面试回答数据库优化问题从以下几个层面入手

4228
来自专栏Linyb极客之路

Java面试中常问的数据库方面问题

B+树是一个平衡的多叉树,从根节点到每个叶子节点的高度差值不超过1,而且同层级的节点间有指针相互链接,是有序的

1073
来自专栏三流程序员的挣扎

Android 优化——存储优化

Google 推出的 Protocal Buffers 是一种更轻便高效的存储结构,但消耗内存较大。

882
来自专栏互联网杂技

SQL注入攻防入门详解

毕业开始从事winfrm到今年转到 web ,在码农届已经足足混了快接近3年了,但是对安全方面的知识依旧薄弱,事实上是没机会接触相关开发……必须的各种借口。这几...

49410
来自专栏Rgc

mysql数据库优化(二)

https://www.cnblogs.com/sevck/p/6733702.html

1902
来自专栏PHP在线

MySQL的语句执行顺序

MySQL的语句一共分为11步,如下图所标注的那样,最先执行的总是 FROM操作,最后执行的是LIMIT操作。其中每一个操作都会产生一张虚拟的表,这个虚拟的表作...

65810
来自专栏逸鹏说道

SQL Server 使用全文索引进行页面搜索

概述 全文引擎使用全文索引中的信息来编译可快速搜索表中的特定词或词组的全文查询。全文索引将有关重要的词及其位置的信息存储在数据库表的一列或多列中。全文索引是一...

3527
来自专栏微信公众号:Java团长

Java面试中常问的数据库方面问题

B+树是一个平衡的多叉树,从根节点到每个叶子节点的高度差值不超过1,而且同层级的节点间有指针相互链接,是有序的

1013
来自专栏林欣哲

MySQL数据库开发规范知识点速查

数据库设计规范 命名规范 基本设计规范 索引设计规范 字段设计规范 SQL开发规范 操作行为规范 命名规范 对象名称使用小写字母并用下划线分割 禁止使用MySQ...

39611
来自专栏乐沙弥的世界

RMAN 提示符下执行SQL语句

       在手动恢复数据库时,有时候需要在SQL*Plus提示符以及操作系统提示符,RMAN提示符下来回切换显得有些繁琐。实际上RMAN为我们提供了命令行下...

1083

扫码关注云+社区