Shared SQL area && Private SQL area

Shared SQL area : Shared SQL Area 包含了SQL的parse tree 和 execution plan

官方文档描述:

A shared SQL area contains the parse tree and execution plan for a given SQL statement. Oracle saves memory by using one shared SQL area for SQL statements run multiple times, which often happens when many users run the same application.

Oracle allocates memory from the shared pool when a new SQL statement is parsed, to store in the shared SQL area. The size of this memory depends on the complexity of the statement. If the entire shared pool has already been allocated, Oracle can deallocate items from the pool using a modified LRU (least recently used) algorithm until there is enough free space for the new statement’s shared SQL area. If Oracle deallocates a shared SQL area, the associated SQL statement must be reparsed and reassigned to another shared SQL area at its next execution.


Private SQL area: Private SQL area包含SQL中的绑定变量的信息还有一些运行时的内存结构;一个Shared SQL area 能与多个Private SQL area做关联; Private SQL area 存在的地方取决于数据库的链接方式如果是使用独立服务器(Dedicated Server)则把Private SQL area

存放在PGA中, 如果使用共享服务器(Shared Server)则保持在SGA中。

官方文档描述:

A private SQL area contains data such as bind information and runtime memory structures. Each session that issues a SQL statement has a private SQL area. Each user that submits the same SQL statement has his or her own private SQL area that uses a single shared SQL area. Thus, many private SQL areas can be associated with the same shared SQL area.


Shared SQL area 和 Private SQL area的区别

Oracle represents each SQL statement it runs with a shared SQL area and a private SQL area. Oracle recognizes when two users are executing the same SQL statement and reuses the shared SQL area for those users. However, each user must have a separate copy of the statement’s private SQL area.

---附:ocp考题1z0-052 q209的一个题--

27:Which two statements are true about Shared SQLArea and Private SQLArea? (Choose two.)
选项
A.Shared SQLArea will be allocated in the shared pool.
B.Shared SQLArea will be allocated when a session starts.
C.Shared SQLArea will be allocated in the large pool always.
D.The whole of Private SQLArea will be allocated in the Program Global Area (PGA) always.
E.Shared SQLArea and Private SQLArea will be allocated in the PGA or large pool.
F.The number of Private SQLArea allocations is dependent on the OPEN_CURSORS parameter.
Correct Answers: A F

关于 OPEN_CURSORS 参数,查看下面链接,感觉和F选项说的没多大关联

http://blog.163.com/yanenshun@126/blog/static/1283881692013317102832596/

省市联动,-- Table structure for province -- ---------------------------- DROP TABLE IF EXISTS `province`; CREATE TABLE `province` ( `id` int(5) NOT NULL auto_increment, `name` varchar(255) default '', `pid` int(5) default NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of province -- ---------------------------- INSERT INTO `province` VALUES ('82', '北京市', '0'); INSERT INTO `province` VALUES ('83', '天津市', '0'); INSERT INTO `province` VALUES ('84', '河北省', '0'); INSERT INTO `province` VALUES ('85', '山西省', '0'); INSERT INTO `province` VALUES ('86', '内蒙古自治区', '0'); INSERT INTO `province` VALUES ('87', '辽宁省', '0'); INSERT INTO `province` VALUES ('88', '吉林省', '0'); INSERT INTO `province` VALUES ('89', '黑龙江省', '0'); INSERT INTO `province` VALUES ('90', '上海市', '0'); INSERT INTO `province` VALUES ('91', '江苏省', '0'); INSERT INTO `province` VALUES ('92', '浙江省', '0'); INSERT INTO `province` VALUES ('93', '安徽省', '0'); INSERT INTO `province` VALUES ('94', '福建省', '0'); INSERT INTO `province` VALUES ('95', '江西省', '0'); INSERT INTO `province` VALUES ('96', '山东省', '0'); INSERT INTO `province` VALUES ('97', '河南省', '0'); INSERT INTO `province` VALUES ('98', '湖北省', '0'); INSERT INTO `province` VALUES ('99', '湖南省', '0'); INSERT INTO `province` VALUES ('100', '广东省', '0'); INSERT INTO `province` VALUES ('101', '广西壮族自治区', '0'); INSERT INTO `province` VALUES ('102', '海南省', '0'); INSERT INTO `province` VALUES ('103', '重庆市', '0'); INSERT INTO `province` VALUES ('104', '四川省', '0'); INSERT INTO `province` VALUES ('105', '贵州省', '0'); INSERT INTO `province` VALUES ('106', '云南省', '0'); INSERT INTO `province` VALUES ('107', '西藏自治区', '0'); INSERT INTO `province` VALUES ('108', '陕西省', '0'); INSERT INTO `province` VALUES ('109', '甘肃省', '0'); INSERT INTO `province` VALUES ('110', '青海省', '0'); INSERT INTO `province` VALUES ('111', '宁夏回族自治区', '0'); INSERT INTO `province` VALUES ('112', '新疆维吾尔自治区', '0'); INSERT INTO `province` VALUES ('113', '台
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值