Chapter 04-Tuning the shared Pool

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.