oracle sharead sql area private area

原创 2017年02月09日 16:47:36

这里写图片描述

oracle 的SQL解析过程,先会在共享区内查找是否执行过此条SQL,找到了则直接从共享区域内直接返回,如果没有查找到,那么才会去重新执行查找匹配。

内存开辟的大小需要通过SQL的复杂度来定,如果内存不够用则用LRU(least recently used)最近使用的算法来替换旧的SQL。

每个共享的SQL都会在私有区域有一份拷贝。

通过oracle的架构图里的查询,可以知道,shared sql area是在shared pool里,private私有sql area 要看oracle 的服务器模式,如果是专用模式就在PGA里,如果是共享模式则在SGA里。

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

EG:

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.

解析:
B选项, shared sql area里的sql 如果空间充裕的话,一直是会在shared pool里的,所以说它和session是没有关系的。
C选项,必定是在shared pool里。
!!!D和E选项,这个是选项是最难的,私有的区域应该只出现在PGA和share pool中,

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

所以还是要看服务器的模式。

版权声明:本文为博主原创文章,未经博主允许不得转载。

SQL area

文章转载于http://m.blog.csdn.net/article/details?id=9896497 Shared SQL area : Shared SQL Area 包含...
  • oradbm
  • oradbm
  • 2016年12月06日 12:52
  • 409

[汇总表+主从表两个方案]全国省市区联动数据库(mysql版本)

  • 2016年08月17日 09:51
  • 859KB
  • 下载

Shared SQL area && Private SQL area

Shared SQL area : Shared SQL Area 包含了SQL的parse tree 和 execution plan 官方文档描述: A shared SQL area conta...
  • liqing_1109
  • liqing_1109
  • 2013年08月11日 09:01
  • 1224

OCP 1Z0 052 14

14. These are points that describe the contents of different memory components:  1: Descriptive inf...
  • jgmydsai
  • jgmydsai
  • 2014年06月08日 22:31
  • 674

数据库文件area.sql

  • 2014年01月05日 21:39
  • 60KB
  • 下载

15.Which two statements are true about Shared SQL Area and Private SQL Area? (Choose two.)

15.Which two statements are true about Shared SQL Area and Private SQL Area? (Choose two.)
  • dwj19830118
  • dwj19830118
  • 2016年07月15日 22:27
  • 1356

OCP 1Z0 052 15

15. Which two statements are true about Shared SQL Area and Private SQL Area? (Choose two.)  A.Shar...
  • jgmydsai
  • jgmydsai
  • 2014年06月09日 20:14
  • 755

PGA初探

认识PGA PGA是特定于进程或者线程的一段内存,换句话说,他是一个操作系统进程或线程专用的内存,不允许系统的其他它进程(线程)访问。PGA一般通过C语言的运行时调用malloc()或memmap()...
  • ll1058320115
  • ll1058320115
  • 2015年12月29日 10:04
  • 650

'SQL AREA' 非常低的解决方案

原文地址:http://blog.itpub.net/66233/viewspace-994749/尊重原创,请看原文!!! SQL> select gets,gethits, gethit...
  • cuiyan1982
  • cuiyan1982
  • 2017年12月27日 11:17
  • 29

OCP-1Z0-052-V8.02-15题

15. Which two statements are true about Shared SQL Area and Private SQL Area? (Choose two.) A.Share...
  • rlhua
  • rlhua
  • 2013年10月26日 22:56
  • 11466
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:oracle sharead sql area private area
举报原因:
原因补充:

(最多只允许输入30个字)