Oracle 如何设置shared pool 和sga大细, 应该设置几大

川长思鸟来 2022-03-19 13:12 509阅读 0赞

这篇文章是参考甲骨论老相老师的教学视频:  
http://v.youku.com/v\_show/id\_XMzkyMTg4Njg0.html  
所做的学习笔记  
  
  
  
  
前面已经提过shared pool(共享池)的作用和结构了, 详细看这里:  
[http://nvd11.blog.163.com/blog/static/200018312201301875752730/][http_nvd11.blog.163.com_blog_static_200018312201301875752730]  
  
  
  
总的来讲:  
        shared pool 就是用来缓存sql语句和对应执行计划的, 当一条sql第二次执行时能找到shared pool的共享sql的执行计划的就避免了硬解析, 大大提高了数据库的运行效率.  
  
        而现在内存白菜价, 一般的笔记本都随便上8g内存了, 很多服务器都64g内存起步.  
        理论上来讲, shared pool越大, 能缓存的sql语句和执行计划就越多, 能避免硬解析的几率就越高,那么是不是shared pool的容量越大越好呢,  其实不是这样的.  
  
**1.shared pool设置过大也有缺点:**  
硬解析在shared pool运作机制可以参考下图啦:

![Image 1][]

大致上1条sql **硬解析**的语句会执行一下步骤:  
  
**1. SQL传入shared pool  
2. SQL语句 会转换成ASCII码 经过一系列计算算出hash 值和For library cache的chain号码  
3. 到library cache 对应chain号码的内存链chain上面根据hash值遍历每个chunk.  
4. 找不到hash值相同的chunk啊(找到就是软解析了)  
5. 到free memory空间找1个足够大的chunk.  
6. 执行硬解析, 并把算出的执行计划放入这个chunk中,  
7. 把这个chunk挂到library cache对应链中, 作为缓存,下次执行就能软解析了.**  
  
可以观察出,真正执行硬解析所需的只有1,5,6步,  其余都是为了下次软解析服务的.  
  
所以说 **,假如oracle由于总总原因, 每次传入来的sql都不同,都不能共享sql,那么shared pool的这些动作实在是个累赘, 还不如不用shared pool**, 当然这种情况几乎不可能,而且oracle也不能禁用shared pool的.  
  
所以程序员做好SQL共享是1个很重要的事情啊.  
参考:  
http://nvd11.blog.163.com/blog/static/200018312201301945631729/  
  
好了, 假如程序代码写得很规范, 而且都用绑定变量去实现SQL共享, 那么是否shared pool越大越好呢?  
  
         其实也不是,  **仔细看看第3步, 是1个遍历线性表的动作,  如果shared pool很大, 则library cache也很大,那么library的数据链可能会很长,同时缓存了很多很不常执行的sql语句, 这样的遍历起来可能会变慢**.(也不是很慢啦, 比短chain慢而已)  
  
所以Oracle 在9g的时候建议shared pool大小最好不要超过1G  
  
但是Oracle 10g后 应用了新技术, 如果shared pool很大, 则会对其分成1个个小部分, 那么数据链就不会很长了, 所以10g 可以设置大点, 老相老师他说见过生产数据库将shared pool设成10G的.  
  
**手动设置shared pool 大小:**  
  
其实一般来讲我们只需设置sga-target的大小就可以了,  oracle会自动管理shared pool的size.  
  
但是当服务器内存不能增加的时候, 也就是说sga-target被指定的情况下,怎么手动设置shared pool的size  
  
  
其实oracle有个系统动态视图 v$shared\_pool\_advice    叫共享池建议..  
我们可以借助它算出 大概的shared pool最佳大小(当前sga-target下)  
**select** 'shared pool' component,     
            shared\_pool\_size\_for\_estimate estd\_sp\_size,  
            estd\_lc\_time\_saved\_factor parse\_time\_factor,  
            **case when** current\_parse\_time\_elapsed\_s + adjustment\_s < 0  
            **then** 0  
            **else **     current\_parse\_time\_elapsed\_s + adjustment\_s  
            **end**  response\_time  
            **from** ( **select**a.shared\_pool\_size\_for\_estimate,  
                                   a.estd\_lc\_time\_saved\_factor,  
                                   a.estd\_lc\_time\_saved,  
                                   e.value/100 current\_parse\_time\_elapsed\_s,  
                                   c.estd\_lc\_time\_saved - a.estd\_lc\_time\_saved adjustment\_s  
                         **from **  **v$shared\_pool\_advice** a,  
                        ( **select** \* from v$sysstat where name ='parse time elapsed') e,  
                        ( **select** estd\_lc\_time\_saved  
                          **from**   **v$shared\_pool\_advice**  
                         **where** shared\_pool\_size\_factor = 1) c  
                         );  
  
执行试下:

![6597636015145272743.jpg][]  
  
一般来讲,  当PARSE\_TIME\_FACTOR 为 1, 也就是说shared pool设置为240m是最合理的.

查看当前系统自动管理下的shared pool大小:

![6597140135402467440.jpg][]  
  
的确比较接近这个值了.  
  
  
**手动设置SGA 大小:**  
当然可用  
alter system set sga\_target =xxx； 来设置了  
注意设置为0 时,是交由ORACLE自动管理大小啦,  
  
但是当手动设置时,如何获得最佳大小值呢,可以借助em管理页面 ,里面有ORACLE的推荐..  
  
首先启动em服务你懂的:

![6597853718447578447.jpg][]  
  
接下来,在防火墙开发1158端口:  
修改/etc/sysconfig/iptables

![6597671199517361930.jpg][]  
  
跟住重启防火墙服务:  
service restart iptables  
  
就可以在客户端浏览器登陆em了,地址是  
**https://192.168.0.112:1158/em/  
  
注意服务器ip啦**  
  
  
登陆后,点击首页下方的指导中心:

![6597743767284794127.jpg][]  
  
  
  
**再点击内存指导:**

![6597953774005709632.jpg][]  
  
  
进入内存指导页面,默认情况下sga\_target 是自动设置(sga\_target =0), 是看不到建议的, 我们要禁用自动管理,  
点击禁用:

![6597974664726634704.jpg][]  
  
  
这是可以手动设置了, 也可以设置sga里面各大池大小, 如下图 点击建议:

![6597965868633616952.jpg][]  
  
  
**可以见到建议, 640m左右是最佳的,实际上也是oracle系统自动管理那个值啦...**

![6598228651912429301.jpg][]

[http_nvd11.blog.163.com_blog_static_200018312201301875752730]: http://nvd11.blog.163.com/blog/static/200018312201301875752730/
[Image 1]: 
[6597636015145272743.jpg]: http://img3.ph.126.net/XOZXhdK97OGTT3w8bR00IQ==/6597636015145272743.jpg
[6597140135402467440.jpg]: http://img0.ph.126.net/cIFISw9RIuVUNjXHEFO1Xw==/6597140135402467440.jpg
[6597853718447578447.jpg]: http://img7.ph.126.net/-ma-rQm2vNbtMUARGm1FHQ==/6597853718447578447.jpg
[6597671199517361930.jpg]: http://img0.ph.126.net/0B9auvj2uC0vVcMT88SpwQ==/6597671199517361930.jpg
[6597743767284794127.jpg]: http://img7.ph.126.net/tuxjSu-QHIAFcSz-btUh0A==/6597743767284794127.jpg
[6597953774005709632.jpg]: http://img2.ph.126.net/xaiZDjf-M16FOYW0ZU4GAg==/6597953774005709632.jpg
[6597974664726634704.jpg]: http://img4.ph.126.net/9KR2UjwWcNzUChpRHk0pTQ==/6597974664726634704.jpg
[6597965868633616952.jpg]: http://img2.ph.126.net/tGkvtXd756rdaLEBPsENWw==/6597965868633616952.jpg
[6598228651912429301.jpg]: http://img1.ph.126.net/a2U71L9mMp44tuAN-7VvUw==/6598228651912429301.jpg