Objectives
shared pool tune（shared pool 调优）
After completing this lesson,you should be able to do the following:
- Determine the size of an object and pin it in the shared pool
- Tune the shared pool reserved space
- Describe the user global area (UGA) 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
Major components of the shared pool are:
Oracle处于shared server mode时，UGA才会有用，对于在dedicate server mode时，无用；
- Library cache
- Data dictionary cache
- User global area(UGA) for shared server session.
因为dedicate server mode拥有自己的PGA，不需要UGA；
即使DBA使用的是Shared server mode，存在着UGA，DBA也可以将UGA移动到Large pool中. 通过此方式也可以达到shared pool 性能调优的目的.所以一般情况下，我们在shared pool性能调优的过程中，可以只关注Library cache和Data dictionary cache两部分内容即可.
数据字典存放在系统表空间上（system tablespace）
Library cache中，存放是编译好的SQL；
Shared pool是oracle中影响性能的一个重要部件，是性能调优的重点工作之一.一般情况下shared pool里面只要Libarary cache的性能调优比较正常，dictionary cache基本上也是一致的，比较正常的，二者是统一的、关联的.
Shared Pool
- Defined by SHARED_POOL_SIZE
在oracle 10g中，oracle提供了内存管理技术叫做automtaic memory management,对应的参数为sga_target;
到了oracle11g中，Oracle又提供了memory_target；
- Library cache contains statement text,parsed,code,and execution plan.
- Data dictionary cache contains definitons for tables,columns,and privileges from the data dictionary tables.
- UGA contains session information for Oracle Shared Server users when a large pool is not configured.
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
对于OLAP系统，不需要关系reparsing问题.
Reports error ORA-04031 if the shared pool is out of free memory.
|-解决方案一：扩充内存
|-解决方案二：减少parse
如何查看shared pool大小？
SQL> show parameter share NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ hi_shared_memory_address integer 0 max_shared_servers integer shared_memory_address integer 0 shared_pool_reserved_size big integer 6501171 shared_pool_size big integer 0 shared_server_sessions integer shared_servers integer 1 SQL>
如何查看free memory size?
SQL> select * from v$sgastat where name = 'free memory'; POOL NAME BYTES ------------ -------------------------- ---------- shared pool free memory 20571536 large pool free memory 300000 java pool free memory 4194304
The Library Cache
Hard parse就是将SQL statement转换成oracle server可执行的代码;
soft parse就是当有SQL Statement提交到Library Cache中的时候，首先检查有没有已经parse好的SQL,如果有就使用已经parse好的代码；这个过程就叫做soft parse.
Tuning The library Cache的一个重要方法就是提高soft parse的数量，减少hard parse的数量.
SQL Shareing Criteria
Oracle 是如何判断多个SQL Statement共享同一段内存区域的？
SELECT * FROM employees;
SELECT * FROM Employees;
SELECT * FROM employees;
以上三条SQL语句，在oracle看来，是不同的三条语句，不能共享同一块内存区域（shared SQL area）；
ORACLE PARAMETER CURSOR_SHARING has three values(EXACT、SIMILAR、FORCE)
SQL> show parameter CURSOR_SHARING NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ cursor_sharing string EXACT
SIMILAR DEMO
|-SELECT COUNT(*) FROM employee WHERE manager_id = 121;
|-SELECT COUNT(*) FROM employee WHERE manager_id = 247;
The only exception to this rule is when the parameter CURSOR_SHARING has been set to SIMILAR or FORCE.Similar statements can share SQL areas when the CURSOR_SHARING parameter is set to SIMILAR or FORCE.The costs and benefits involved in using CURSOR_SHAREING are explained later in the section.
Use Bind Variables
A bind variable is a placeholder in a query.For example,to retrieve the record for employee 123,I can use this query:
select * from emp where empno = 123;
Alternatively,I can set the bind variable:empno to 123 and execute the following query:
select * from emp where empno = :empno;
In a typical system,you would query up employee 123 maybe onece and then never agina.Later,you would query employee 456,then 789,and so on.If you use literals(constants) in the query,then each and every query is a brand-new query,never before seen by the database.It will have to be parsed,qualified(names resolved),security checked,optimized,and so on-in short,each and every unique statement you execute will have to be compiled every time it is execurted.
The second query uses a bind variable,:empno,the value of which is supplied query execution time.This query is compiled once,and then the query plan is stored in a shared pool(the libary cache),from which it can be retrieved and reused.The difference between the two in terms of performance and scalability is huge-dramatic,even.
Diagnostic Tools
Parameters affetcing the components:
SHARED_POOL_SIZE(似乎还不能单独调整library cache,shared sql的大小，如果想调整他们的大小，只能调整shared_pool_size的大小)
OPEN_CURSORS
SESSION_CACHED_CURSORS
CURSOR_SPACE_FOR_TIME
CURSOR_SHARING
SHARED_POOL_RESERVED_SIZE
Using Shared Pool Effectively
An important purpose of the shared pool is to cache the executable versions of SQL and PL/SQL statements.This allows multiple executions of the same SQL or PL/SQL code to be performed without the resources required for a hard parse,which results in significant reductions in CPU,memory,and latch usage.
The shared pool is also support unshared SQL in data warehousing applications,which execute low-concurrency,high-resource SQL statements.In this situation,using unshared SQL with literal values is recommended.Using literal values rahter than bind variables allows the optimizer to make good column selectivity estimates ,thus providing an optimal data access plan.
In a data warehousing environment,the SQL query result cache also enables you to optimize the use of the shared pool.
IN an OLTP system,there are a number of ways to ensure efficient use of the shared pool and related resources.Discuss the following items with application developers and agree on strateies to ensure that the shared pool is used
effectively:
Shared Cursors
Single-User Logon and Qualified Table Reference
Use of PL/SQL
Avoid Performing DDL
Cache Sequence Numbers
Cursor Access and Management
Use of Result Cache
Efficient use of the shared pool in high-concurrency OLTP systems significantly reduces the probability of parse-related application scalability issues.