Tuning the Shared pool(3)

Shared Cursors


1.少用动态SQL，多用普通SQL。
Avoid application designs that result in large numbers 


of users issuing dynamic,unshared SQL 


statements.Typically,the majority of data required by 


most users can be satisfied using preset queries.Use 


dynamic SQL where such functionality is required.


2.连上来的用户不要修改优化器的设置，如果每个用户都去修


改一下优化器，则共用同一个SQL代码的难度就增加了。
Be sure that users of the application do not change 


the optimization approach and goal for their 


individual sessions.（不能那么自私吗）


3.教开发人员以下事情：
- 对于动态变量要有一套命名规范，而且对于这个空格，大小


写也有标准。
- 尽量用存储过程。Consider using stored procedures 


whenever possible.Multiple users issuing the same 


stored procedure use the same shared PL/SQL area 


automatically.Because stored procedures are stored in 


a parsed form,their use reduces runtime parsing.因为存


储过程是存在数据库里面的，调用它速度更快，而且共用同一


个编译代码的几率大大提高。


4.For SQL statements which are identical but are not 


being shared,you can query V$SQL_SHARED_CURSOR to 


determine why the cursors are not shared.This would 


include optimizer settings and bind variable 


mismatches.可以把相同的但没共享的SQL语句找出来，有可能


是各个session的优化器设置不一样，也有可能是绑定变量不


一样。


Qualified Table Reference and Single-User Logon
限定表的指引与单用户登录
  Large OLTP systems where users log in to the 


database as their own user ID can benefit from 


explicitly qualifying the segment owner,rather than 


using public synonyms.
例如 SELECT employ_id FROM hr.employees WHERE   


department_id=:dept_id;这样有限制的会比使用公共同义词


好。
  An alternative to qualifying table names is to 


connect to the database throught a single user 


ID,rather than individual user IDs.User-level 


validation can take place locally on the middle 


tier.Reducing the number of distinct userIDs also 


reduces the load on the dictionary cache.如果OLTP系统


连上来的用户用同一个名，把区分不同用户的功能放在中间件


或应用服务器上，那会比较好，也可以减少dictionary cache


的压力。


Using Shared pool Effectively
Use of PL/SQL
  Using stored PL/SQL packages can overcome many of 


the scalability issues for systems with thousands of 


users,each with individual user sign-on and public 


synonyms.This is because a package is executed as the 


owner,rather than the caller,which reduces the 


dictionary cache load considerable.
使用PL/SQL包时是用所有者而不是调用者去执行。


Avoid Performing DDL
Avoid performing DDL operations on high-usage segments 


during peak hours.Performing DDL on such segments 


often results in the dependent SQL being invalidated 


and hence reparsed on a later execution.
改变表结构最好在非高峰时候，因为用DDL改了，则那个SQL代


码会变invalidated。


Library Cache Statistics


V$LIBRARYCACHE,要使RELOADS与INVALIDATIONS尽可能接近0.
The statistic that shows the amount of reloading(that 


is,reparsing) of a previously cached SQL statement 


that was aged out of the cache is the RELOADS column 


in the V$LIBRARYCACHE view.In an application that 


reuses SQL effectively,on a system with an optimal 


shared pool size,the RELOADS statistic will have a 


value near zero.


如果做了DDL操作，the number of times library cache 


data was invalidated and had to be reparsed.在高峰时期


，OLTP系统，尽量别做DDL操作。


V$SGASTAT中的free memory也是在高峰时期也是重要的指标。


尽量让free memory在不造成reloads的情况下，尽量低。


最后一个指标，library cache hit ratio.该指标要与其他指


标联合一起用。


实验：
desc v$librarycache
select 


namespace,gethitratio,pinhitratio,reloads,invalidation


s from v$librarycache;


determine which statements users are running:
select sql_text,users_executing,executions,loads from 


v$sqlarea;


select * from v$sqltext where sql_text like 'select * 


from hr.employees where %';


reloads should be less than 1% of the pins:


select sum(pins) "Excutions",SUM(reloads) "Cache 


Misses",SUM(reloads)/SUM(pins)
FROM v$librarycache;


但这些动态性能视图的值是累计值，有时为了更精确，可以像


statespack那样，隔一段时间看一次，然后两次相减，就得出


这两次之间的值。