Tuning the Shared pool(1)

Tuning the Shared pool
培训目标:
* Determine the size of an object and pin it in the shared pool.钉在
* Tune the shared pool reserved space
* Describe the user global area and session memory considerations.
* Measure the library cache hit ratio命中率
* List other tuning issues related to the shared pool.
* Measure the dictionary cache hit ratio.
* Set the large pool.


Shared Pool Contents
Database buffer cache
Redo log buffer
Shared pool
Library cache
Data dictionary cache
Large pool
Large pool


Shared Pool
Define by SHARED_POOL_SIZE
10g后有SGA_TARGET,11g后有MEMORY_TARGET,都减轻了管理员负担。
Library cache contains statement text,parsed code,and execution plan.
Data dictionary cache contains definitions for tables,columns,and privileges from the data dictionary tables.
UGA(用在共享模式，dedicate模式不用，就算是共享模式，也把UGA放在large pool中，这也是一种调优的途径，以避免占用shared pool的位置）。


The Library Cache
Used to store SQL statements and  PL/SQL blocks that are to be shared by users.
Managed by a least recently used（LRU） algorithm
Used to prevent statements reparsing.
预防句子再解析。
Reports error ORA-04031 if the shared pool is out of free memory.


SQL Sharing Criteria
Oracle automatically determines whether a SQL statement or PL/SQL block being issued is identical to another statement currently in the shared pool.


Oracle performs the following steps for the comparison:
1.The text of the statement issued is compared to existing statements in the shared pool.
2.The text of the statement is hashed.If there is no matching hash value,then the SQL statement does not currently exist in the shared pool,and a hard parse is performed.
输入的语句会被hash处理，出来一个较短的特征值，这样就比较容易比对。
3.If there is a matching hash value for an existing SQL statement in the shared pool,then Oracle compares the text of the matched statement to the text of the statement hashed to see if they are identical.The text of the SQL statements or PL/SQL blocks must be identical,character for character,including spaces,case,and comments.
如果hash值是一样的，则把文本逐个字母比对，甚至空间与大小写也要关注（真要关注空格与大小写吗？）


下面这两个是不能共享相同的SQL执行代码的。
SELECT * FROM employees;
SELECT   * FROM Employees;
SELECT   * FROM employees;


desc v$sysstat  关于系统的一些统计指标
select * from v$sysstat;看到一行是parse count (hard) 后面列出的是硬解析的次数。


此时用另一个会话以HR/HR连接数据库
drop table t
create table t(id number);
insert into t values(&i);
输入1，2，3。
然后现在看一下硬解析次数，再select * from t where id=1;再看一下，发现硬解析次数加了1。再试验一下空间数目，大小写不同，发现硬解析都会增加。看来大小写与空间数目都挺重要的。


show parameter cursor
alter system set cursor_sharing='exact';
alter system set cursor_sharing='similar';
据说改成similar或force后，两个sql语句只是字面值不一样的话，也可以用相同的shared sql area.
例如select * from t where id=21与select * from t where id=22;


4.在SQL语句中涉及的对象的统计数据，要与存在的语句的对象的统计数据相同，才能重用执行代码。例如，如果两个不同的用户登录了，都输入select * from employees，也不能认为是相同的，因为他们各自的schema下都有employees表。


5.绑定变量，在SQL语句中，一定要match in name,datatype,and length;
下面两条语句因为绑定变量名字不一样，也认为是两条不同的SQL语句。
select * from employees where department_id=:department_id;
select * from employees where department_id=:dept_id;


Use Bind Variables.