第五讲--共享SQL减少硬解析

SQL要共享就是让SQL完全相同，空格，数值，回车，大小写等等，统统认为是SQL语句不一样。不能共享就会发生硬解析。

SQL共享的方法：

1.      统一书写风格；

2.      使用绑定变量；

SQL语句可以分为动态部分和静态部分，where不同的输入是动态部分。

 

查看library cache的解析命中率：

SQL>  select sum(pinhits)/sum(pins)*100 fromv$librarycache; SUM(PINHITS)/SUM(PINS)*100--------------------------                78.4868863

解析命中率越高，软解析次数越多，oracle性能越好。一般先让oracle跑一会儿再检查解析命中率。达到99%以上才算正常。

解决ora-4031错误的办法：

1.             alter system flush shared pool，执行之后sharedpool的library cache里面所有的chunk会释放，会有大量的chunk到free里面去。这是治标不治本的方法；

2.             找出哪些语句没有共享SQL，然后把问题告诉开发人员，让开发人员改代码；

3.             alter system setcursor_sharing='force'，执行之后，oracle会强行给字面值赋予绑定变量，但是依然无法解决书写不规范（大小写、空格等不同）造成的SQL不共享；

4.             保留区，select request_misses from v$shared_pool_reserved;

5.             增加shared pool空间

 

Shared pool保留区概念：

Shared pool里面有三大区域：free、librarycache、raw cache。为了硬解析增多free空间越来越少越来越碎片化，oracle在shared pool里面划出了一块保留区（reserved size）专门缓存SQL大对象，如果SQL对象尺寸超过阈值时，就不会去free找空间，而是去保留区找空间。如果把保留区设置的足够大，就能避免很多ora-4031错误的产生。

查询在保留区中请求失败的次数，只要有一次，就肯定会发生ora-4031错误。因为到保留区找空间了，肯定是大对象，如果在保留区都找不到必然出错。

查询在保留区中请求失败的次数：

SQL> selectrequest_misses from v$shared_pool_reserved; REQUEST_MISSES--------------             0

查看保留区大小：

SQL> showparameter shared; NAME                                 TYPE--------------------------------------------------------------------VALUE------------------------------hi_shared_memory_address             integer0max_shared_servers                   integer shared_memory_address                integer0shared_pool_reserved_size            big integer6710886shared_pool_size                     big integer NAME                                 TYPE--------------------------------------------------------------------VALUE------------------------------0shared_server_sessions               integer shared_servers                       integer1

有两个参数：sga_target、sga_max_size。Sga_target是动态参数，可以随时调整，sga_max_size是静态参数（修改需重启才生效），sga_target<=sga_max_size。

 

查看shared pool内存大小：

SQL> showparameter shared_pool; NAME                                 TYPE--------------------------------------------------------------------VALUE------------------------------shared_pool_reserved_size            big integer6710886shared_pool_size                     big integer0

查看oracle各大内存块的大小：

SQL> select COMPONENT,CURRENT_SIZE fromv$sga_dynamic_components; COMPONENT                                                       CURRENT_SIZE----------------------------------------------------------------------------shared pool                                                        138412032large pool                                                           4194304java pool                                                            4194304streams pool                                                                0DEFAULT buffer cache                                               130023424KEEP buffer cache                                                          0RECYCLE buffer cache                                                        0DEFAULT 2K buffer cache                                                    0DEFAULT 4K buffer cache                                                    0DEFAULT 8K buffer cache                                                    0DEFAULT 16K buffer cache                                                   0 COMPONENT                                                       CURRENT_SIZE----------------------------------------------------------------------------DEFAULT 32K buffer cache                                                   0ASM Buffer Cache                                                           0 13 rows selected.