Oracle-buffer cache、shared pool

http://blog.csdn.net/panfelix/article/details/38347059   buffer pool 和shared pool 详解

 http://blog.csdn.net/panfelix/article/details/38347137 shared pool 和buffer pool 详解（之二， Cache Buffers LRU Chain、Cache Buffers LRU Chain闩锁竞争与解决）

http://blog.csdn.net/notbaron/article/details/52007934 shared pool原理

1 shared pool

oracle引入Shared Pool就是为了帮助我们实现代码的共享和重用。

Shared Pool主要由两部分组成，一部分是库缓存（Library Cahce），另一部分是数据字典缓存（Data Dictionary Cache）。Library Cache主要用于存储SQL语句、SQL语句相关的解析树、执行计划、PL/SQL程序块（包括匿名程序块、存储过程、包、函数等）以及它们转换后能够被Oracle执行的代码等，这部分信息可以通过v$librarycache视图查询；至于DataDictionary Cache主要用于存放数据字典信息，包括表、视图等对象的结构信息，用户以及对象权限信息，这部分信息相对稳定，在Shared Pool中通过字典缓存单独存放，字典缓存的内容是按行（Row）存储的（其他数据通常按Buffer存储），所以又被称为Row Cache，其信息可以通过V$ROWCACHE查询。

Shared Pool各个部分协同工作以及与Buffer Cache的配合。如下图1：

 

从Oracle Database 11g开始，在Shared Pool中划出了另外一块内存用于存储SQL查询的结果集，称为ResultCache Memory。以前Shared Pool的主要功能是共享SQL，减少硬解析，从而?高性能，但是SQL共享之后，执行查询同样可能消耗大量的时间和资源，现在Oracle尝试将查询的结果集缓存起来，如果同一SQL或PL/SQL函数多次执行（特别是包含复杂运算的SQL），那么缓存的查询结果可以直接返回给用户，不需要真正去执行运算，这样就又为性能带来了极大的提升。

    结果集缓存（Result Cache）是Oracle Database 11g新引入的功能，除了可以在服务器端缓存结果集（ServerResultCache）之外，还可以在客户端缓存结果集（Client Result Cache）。

服务器端的ResultCache Memory由两部分组成:

(1) SQL Query Result Cache：存储SQL查询的结果集。

(2) PL/SQL Function Result Cache：用于存储PL/SQL函数的结果集。

Oracle通过一个新引入初始化参数result_cache_max_size 来控制该Cache的大小。如果result_cache_max_size=0 则表示禁用该特性。参数result_cache_max_result 则控制单个缓存结果可以占总的ServerResult Cache大小的百分比。

初始地，数据库启动以后，Shared Pool多数是连续内存块。但是当空间分配使用以后,内存块开始被分割，碎片开始出现，Bucket列表开始变长。

Oracle请求Shared Pool空间时，首先进入相应的Bucket进行查找。如果找不到，则转向下一个非空的Bucket，获取第一个Chunk。分割这个Chunk，剩余部分会进入相应的Bucket，进一步增加碎片

最终的结果是，由于不停分割，每个Bucket上的内存块会越来越多，越来越碎小。通常Bucket 0的问题会最为显著，在这个测试数据库上，Bucket 0上的碎片已经达到9030个，而shared_pool_size设置仅为150MB。

通常如果每个Bucket上的Chunk多于2000个，就被认为是Shared Pool碎片过多。Shared Pool的碎片过多，是Shared Pool产生性能问题的主要原因。

碎片过多会导致搜索Free Lists的时间过长，而我们知道，Free Lists的管理和搜索都需要获得和持有一个非常重要的Latch，就 是Shared Pool Latch。Latch是Oracle数据库内部提供的一种低级锁，通过串行机制保护共享内存不被并发更新/修改所损坏。Latch的持有通常都非常短暂（通常微秒级），但是对于一个繁忙的数据库，这个串行机制往往会成为极大的性能瓶颈。

如果Free Lists链表过长，搜索这个Free Lists的时间就会变长，从而可能导致SharedPool Latch被长时间持有，在一个繁忙的系统中，这会引起严重的Shared PoolLatch的竞争。在Oracle 9i之前，这个重要的Shared PoolLatch只有一个，所以长时间持有将会导致严重的性能问题。

操作系统来打交道来分配或者回收内存。但是呢，在shared pool中，内存是预先分配的，Heap Manager管理所有的空闲内存。当某个进程需要分配shared pool的内存的时候，Heap Manager就满足该请求，Heap Manager也和其他ORACLE模块一起工作来回收shared pool的空闲内存。Library cache Manager 可以看做是Heap Manager的客户端，因为library cache manager是根据Heap Manager来分配内存从而存放library cache objects。Library cache Manager控制所有的library cache object，包括package/procedure, cursor, trigger等等。

算法，从而得到一个hash值，根据相应的hash值到相应的hash bucket中去寻找。这里的hash算法原理与buffer cache中快速定位block的原理是一样的。如果library cache object在内存中，那么这个library cache handle就会被找到。有时候，当shared pool不够大，library cache handle会保留在内存中，然而library cache heap由于内存不足被age out，这个时候我们请求的object heap就会被重载。最坏的情况下，library cache handle在内存中没有找到，这个时候就必须分配一个新的library cache handle，同时object heap也会被加载到内存中。

算法来确定library cache object handle是由哪个子latch来保护的：

       latch#= mod(bucket#,#latches)

       也就是说用哪个子latch去保护某个handle是根据那个handle所在的bucket号，以及总共有多少个子latch来进行hash运算得到的。

数据库中存在被应用大量频繁访问的procedure，当依赖的表发生变更时，导致该procedure失效，这时会出现大量的librarycache lock和library cache pin的等待，堵塞应用访问，造成了重大故障。出现这个问题的原因是：当procedure失效后，所有访问该对象的进程都尝试去编译，大量进程尝试获取exclusive类型的lock和pin，出现了大量的等待。后续的Oracle版本作出了改进，当出现这种情况时，只允许第一个进程尝试去编译该对象，编译通过后，所有的进程就可以并发访问，避免了大量进程同时尝试编译的情况出现。

http://dbsnake.com/2010/06/solve-library-cache-pin.html

           （2）Mutex和latch是互相独立，没有任何关系的：Latches and mutexes are independent mechanisms i.e. a processcanhold a latch and a mutex at the same time. In the case of processdeath,latches are always cleaned up before mutexes. There is no genericmutexdeadlock detection (unlike latches). There is no mutex/latch hierarchy.

           从11gR1开始，Oracle用mutex替换了librarycache latches，并引了一个新的等待事件：librarycache: mutex *，我们来看一下这个知识点：

Connected to Oracle Database 11g EnterpriseEdition Release11.2.0.1.0

Connected as nbs

SQL>selectname,level#,gets,misses,sleeps,immediate_gets,immediate_misses fromv$latchwhere name like 'library%';

NAME LEVEL# GETS MISSES SLEEPSIMMEDIATE_GETS IMMEDIATE_MISSES

------------------------------------------------------------------------------------ ---------- ---------- -------------- ----------------

library cache load lock 5 0 0 0 0 0

SQL> selectname,level#,gets,misses,sleeps,immediate_gets,immediate_missesfromv$latch_children where name like 'library%';

NAME LEVEL# GETS MISSES SLEEPSIMMEDIATE_GETS IMMEDIATE_MISSES

------------------------------------------------------------------------------------ ---------- ---------- -------------- ----------------

           从结果里我们可以看到，在11.2.0.1里，各种library cache latches都没有了，只剩下了library cache loadlocklatch，而且Oracle还没有使用这个latch，因为gets是0。

Oracle 参数分类和参数的查看方法

http://blog.csdn.net/tianlesoftware/article/details/5583655

SQL> select * from scott.emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMMDEPTNO

----- ---------- --------- ---------------- --------- ---------------

7981 CUIHUA 7981

7369 SMITH CLERK 7902 1980-12-17 800.00 20

7499 ALLEN SALESMAN 7698 1981-2-20 1600.00300.00 30

......省略显示部分内容

7800 JAME3 CLERK 7698 1981-12-3 950.00 30

13 rows selected

SQL> select hash_value,sql_text fromv$sqlarea where sql_textlike 'select * from scott.emp%';

HASH_VALUE SQL_TEXT

------------------------------------------------------------------------------------------

52404428 select * from scott.emp

SQL> select to_char(52404428,'XXXXXXXX')from dual;

TO_CHAR(52404428,'XXXXXXXX')

----------------------------

31FA0CC

           现在我们要来trace针对上述sql的library cache pin和library cache lock，方法我之前已经说了，就是用event10049，用10049的难点在于如何确定level。

确定10049针对单个sql的level值的算法如下：

首先，10049的level可能会有如下一些值：

#define KGLTRCLCK 0x0010/* trace lock operations */

#define KGLTRCPIN 0x0020/* trace pin operations */

#define KGLTRCOBF 0x0040 /* trace objectfreeing */

#define KGLTRCINV 0x0080 /* traceinvalidations */

#define KGLDMPSTK 0x0100 /* DUMP CALL STACKWITH TRACE */

#define KGLDMPOBJ 0x0200 /* DUMP KGL OBJECTWITH TRACE */

#define KGLDMPENQ 0x0400 /* DUMP KGL ENQUEUE WITH TRACE */

#define KGLTRCHSH 0x2000/* DUMP BY HASH VALUE */

           其次，我们是要针对单个sql，所以需要用到这个sql的hash value，以便将10049和这个sql联系起来，即我们一定要用到KGLTRCHSH，也就是0x2000；

另外我们是要tracelibrarycache lock和library cache pin，所以我们一定要用到KGLTRCLCK和KGLTRCPIN，即0x0010和0x0020；

           最后就是我们需要把这个sql的hash value的16进制的后两个byte拿出来，作为10049的level的前缀。

           从上面结果中我们可以看到，select * from scott.emp的hash value的16进制的后两个byte是0xA0CC。

            另外KGLTRCHSH | KGLTRCLCK | KGLTRCPIN ＝ 0x2000 | 0x0010 | 0x0020 = 0x2030。按照上述算法，select * from scott.emp的10049的最终level值就是0xa0cc2030，也就是2697732144：

SQL> selectto_number('a0cc2030','XXXXXXXXXXXX') from dual;

TO_NUMBER('A0CC2030','XXXXXXXX

------------------------------

2697732144

            现在我们设置好10049后再执行一遍上述sql，以观察10.2.0.1下sql的软解析时library cache pin和library cache lock：

SQL> oradebug setmypid

已处理的语句

SQL> oradebug event10049 trace name context forever,level2697732144

已处理的语句

SQL> select * from scott.emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMMDEPTNO

----- ---------- --------- ---------------- --------- ---------------

7981 CUIHUA 7981

7369 SMITH CLERK 7902 1980-12-17 800.00 20

7499 ALLEN SALESMAN 7698 1981-2-20 1600.00300.00 30

......省略显示部分内容

7800 JAME3 CLERK 7698 1981-12-3 950.00 30

13 rows selected

SQL> oradebug tracefile_name

d:\oracle\admin\cuihua\udump\cuihua_ora_5808.trc

相应的trace文件（d:\oracle\admin\cuihua\udump\cuihua_ora_5808.trc）的内容为：

*** 2011-06-01 11:59:35.500

KGLTRCLCK kglget hd = 0x33938118 KGL Lock addr = 0x3174A99C mode= N

KGLTRCLCK kglget hd = 0x33938034 KGL Lock addr = 0x31716F50 mode= N

KGLTRCPIN kglpin hd = 0x33938034 KGL Pinaddr = 0x31718A28 mode =S

KGLTRCPIN kglpndl hd = 0x33938034 KGL Pinaddr = 0x31718A28 mode= S

KGLTRCLCK kgllkdl hd = 0x33938034 KGL Lockaddr = 0x31716F50 mode= N

KGLTRCLCK kgllkdl hd = 0x33938118 KGL Lockaddr = 0x3174A99C mode= N

hd = 0x33938118所对应的library cache object的name就是select * from scott.emp：

SQL> select sql_text from v$sqlareawhere address='33938118';

SQL_TEXT

--------------------------------------------------------------------------------

select * from scott.emp

hd = 0x33938034就是hd = 0x33938118的child cursor：

SQL> select kglhdadr,kglhdpar,kglnaobjfrom x$kglob wherekglhdadr='33938034';

KGLHDADR KGLHDPAR KGLNAOBJ

------------------------------------------------------------------------------------------------

33938034 33938118 select * from scott.emp

从上述trace文件中我们可以得出如下结论：

           1、10.2.0.1中，sql软解析时，针对cursor的library cachelock的lock mode确实是null；

           2、10.2.0.1中，sql软解析时，针对cursor的library cachepin的lock mode确实是S；

现在我们来观察10.2.0.1下sql的硬解析时librarycache pin和library cache lock：

SQL> shutdown immediate

数据库已经关闭。

已经卸载数据库。

ORACLE 例程已经关闭。

SQL> startup

ORACLE 例程已经启动。

Total System Global Area 608174080 bytes

Fixed Size 1250404 bytes

Variable Size 318770076 bytes

Database Buffers 281018368 bytes

Redo Buffers 7135232 bytes

数据库装载完毕。

数据库已经打开。

SQL> select hash_value,sql_text fromv$sqlarea where sql_textlike 'select * from scott.emp%';

HASH_VALUE SQL_TEXT

------------------------------------------------------------------------------------------

SQL> oradebug setmypid

已处理的语句

SQL> oradebug event 10049 trace namecontext forever,level2697732144

已处理的语句

SQL> select * from scott.emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMMDEPTNO

----- ---------- --------- ---------------- --------- ---------------

7981 CUIHUA 7981

7369 SMITH CLERK 7902 1980-12-17 800.00 20

7499 ALLEN SALESMAN 7698 1981-2-20 1600.00300.00 30

......省略显示部分内容

7800 JAME3 CLERK 7698 1981-12-3 950.00 30

13 rows selected

SQL> oradebug tracefile_name

d:\oracle\admin\cuihua\udump\cuihua_ora_5016.trc

相应的trace文件（d:\oracle\admin\cuihua\udump\cuihua_ora_5016.trc）的内容为：

KGLTRCLCK kglget hd = 0x206ECF90 KGL Lock addr = 0x3174E068 mode= N

KGLTRCPIN kglpin hd = 0x206ECF90 KGL Pinaddr = 0x317187C0 mode =X

KGLTRCPIN kglpndl hd = 0x206ECF90 KGL Pinaddr = 0x317187C0 mode= X

KGLTRCLCK kglget hd = 0x33B19238 KGL Lock addr = 0x3174E618 mode= N

KGLTRCPIN kglpin hd = 0x33B19238 KGL Pinaddr = 0x31717F28 mode =X

KGLTRCPIN kglpndl hd =0x33B19238 KGL Pin addr = 0x31717F28 mode= S

KGLTRCLCK kgllkdl hd = 0x33B19238 KGL Lockaddr = 0x3174E618 mode= N

KGLTRCLCK kgllkdl hd = 0x206ECF90 KGL Lockaddr = 0x3174E068 mode= N

SQL> select kglhdadr,kglhdpar,kglnaobjfrom x$kglob wherekglhdadr='33B19238';

KGLHDADR KGLHDPAR KGLNAOBJ

------------------------------------------------------------------------------------------------

33B19238 206ECF90 select * from scott.emp

从上述trace文件中我们可以得出如下结论：

           1、10.2.0.1中，sql硬解析时，针对cursor的library cachelock的lock mode依然是null；

           2、10.2.0.1中，sql硬解析时，针对cursor的library cachepin的lock mode一般是X，但也存在lock mode为S的library cachepin，且这个S是针对子cursor的。

Oracle Shared pool 详解

            http://blog.csdn.net/tianlesoftware/article/details/6560956

           这里重点看一下Library Cache的一个管理机制。参考了如下2篇blog，重新进行了整理：

           Librarycache内部机制详解

            http://www.hellodb.net/2010/07/oracle-library-cache.html

           Oracle中执行计划的存储位置

            http://dbsnake.com/2011/05/exe-plan-stor-location.html

Oracle Namespace 说明

            http://blog.csdn.net/tianlesoftware/article/details/6624122

            所以对Library cache中所有对象的访问是通过利用library cache handle来实现的，也就是说我们想要访问library cache object，我们必须先找到librarycache handle。

           因为Object handle保存了lock 和pin 的信息，即记录哪个用户在这个这个handle上有lock，或者是哪个用户正在等待获得这个lock。那么这里我们也知道了library cache lock是发生在handle上的。

           当一个进程请求library cache object, librarycache manager就会应用一个hash 算法，从而得到一个hash值，根据相应的hash值到相应的hash bucket中去寻找。

           如果library cache object在内存中，那么这个library cache handle就会被找到。有时候，当sharedpool不够大，library cache handle会保留在内存中，然而library cache heap由于内存不足被ageout，这个时候我们请求的object heap就会被重载。最坏的情况下，library cache handle在内存中没有找到，这个时候就必须分配一个新的library cachehandle，同时object heap也会被加载到内存中。

7. Library Cache Object(LCO: Heap 0)

           它的结构信息如下图。这个图需要认真的去理解。

如下图5

 

DSI 的说明：

（1）Internally, most ofthe objectidentity is represented by structures of type kglob.

（2）These arethestructures stored in heap 0.

（3）Object structureshave thefollowing components:

                       Type

                       Name

                       Flags

                       Tables

                       Datablocks

           LibraryCache 存储SQL或者shared cursors 等。这些信息就是通过Heap0 这个LCO 来保存的。

Oracle 高Version counts 问题说明

http://blog.csdn.net/tianlesoftware/article/details/6628232

10.3.12 7.4.3.authorizationtable

对象的授权信息。

7.5 Object Data Blocks

（1）The remainder ofanobject’s data is stored in other independent data heaps.

（2）The objectstructure contains anarray of data block structures.

（3）The datablockstructures have a pointer to a different data heap.

（4）An object structurehas room for 16data block structures but not all of them are in use.

           Heap0 也仅仅保存是一个结构，它不保存实际的data。而实际data 的存储Heap 的指针就存放在这个DataBlocks里。这个也可以通过dump来查看。这个Data Blocks指向的Heap 结构如下图：

如下图6

 

           这里要注意的，就是我们SQL的执行计划就是存放在这个Heap 6：SQL Context 中。

11  data dictionary cache

Oracle SGA是oracle的内存结构，存放着oracle通过oracle进程读写的内存数据。sga分为好多组件，比如shared pool,buffer cache,redo log buffer等等。shared pool又分为library cache,datadictionary cache. 

library cache是存sql语句及其分析结果的内存结构

data dictionary cache是存放数据字典的内存结构

buffer cache存的就是数据。

11.1      测试

           在上面的说明中，提到，对于多child cursor，所有的child cursor 都保存在child table里，并且SQL 的执行计划也保存在child cursor的Heap 6中。下面进行相关的测试，证明以上结论。

SYS@anqing1(rac1)> create table lct asselect * fromdba_objects;

Table created.

分别用SYSTEM和Dave 用户执行如下SQL:

/* Formatted on 2011/7/24 15:07:20(QP5v5.163.1008.3004) */

DECLARE

   x     VARCHAR2 (20);

   str   VARCHAR2 (200);

BEGIN

   x := 'I_PROXY_ROLE_DATA$_1';

   str := 'select* from sys.lct where object_name=:x';

   EXECUTE IMMEDIATE str USING x;

END;

 查看SQL 的versioncount信息

SYS@anqing1(rac1)> selectsql_id,version_count from v$sqlareawhere sql_text like 'select * from sys.lctwhere %';

SQL_ID        VERSION_COUNT

------------- -------------

5d8tu19t1fug2            2

--查看parentcursor和 childcursor的handleaddress：

SYS@anqing1(rac1)> selectaddress,child_address from v$sql wheresql_id='5d8tu19t1fug2';

ADDRESS CHILD_AD

-------- --------

30A9DCDC 2CBA64AC

30A9DCDC 2A32C000

将library cache dump出来：

SQL> alter session set events 'immediatetrace namelibrary_cache level 11';

Session altered.

SQL> oradebug setmypid

Statement processed.

SQL> oradebug tracefile_name

/oradata/XEZF/admin/udump/xezf_ora_14041.trc

--查看trace 文件：搜索30A9DCDC

BUCKET 92642:

  LIBRARY OBJECT HANDLE:handle=30a9dcdcmtx=0x30a9dd90(2) cdp=2

 --object handle 的内存地址

name=select * from sys.lct whereobject_name=:x

--object 的名称

 hash=55555e74e494aa0356a33a0a721769e2 timestamp=07-24-201115:03:04

--hash值和timestamp

 namespace=CRSRflags=RON/KGHP/TIM/PN0/SML/KST/DBN/MTX/[120100d0]

--namespace 和 flags 

kkkk-dddd-llll=0000-0001-0001 lock=N pin=0latch#=1 hpc=0006hlc=0006

--Heaps loaded and kept,lock, pin, and latch modes

 lwt=0x30a9dd38[0x30a9dd38,0x30a9dd38]ltm=0x30a9dd40[0x30a9dd40,0x30a9dd40]

 pwt=0x30a9dd1c[0x30a9dd1c,0x30a9dd1c]ptm=0x30a9dd24[0x30a9dd24,0x30a9dd24]

 ref=0x30a9dd58[0x30a9dd58,0x30a9dd58]lnd=0x30a9dd64[0x30a9dd64,0x30a9dd64]

--以上是：Linklistsof lock waiters, temporary locks, pin waiters, temporary pins andreferences

   LOCK OWNERS:

       lock    user  session count mode flags

   -------- -------- -------- ----- ----------------------------

   2d1a0cac 30efbd2030efbd20     2N    [00]

   2d10501c 30f10e7830f10e78     2N    [00]

    LIBRARY OBJECT: object=2aa6cf2c

--Memory address of theobject (heap 0)

   type=CRSR flags=EXS[0001] pflags=[0000] status=VALDload=0

--Object type, flags, andstatus

  CHILDREN: size=16

   child#    tablereference   handle

   ------ -------- --------- --------

        02c08c08c 2c08bd5c 2cba64ac

        1 2c08c08c 2c08bec8 2a32c000

--这个就是所有child cursor 的handle 的内存地址

   DATA BLOCKS:

   data#     heap pointer   status pins change whr

   ----- -------- -------- --------- ---- ---------

       0 2b8839b4 2aa6cfc4I/P/A/-/-    0NONE   00

--Object data structures(heap descriptors)

 BUCKET 92642 total object count=1

查看其中一个child cursor 地址，在trace 里搜索：2cba64ac

 LIBRARY OBJECT HANDLE: handle=2cba64acmtx=0x2cba6560(0)cdp=0

 namespace=CRSR flags=RON/KGHP/PN0/EXP/[10010100]

 kkkk-dddd-llll=0000-0001-0001 lock=N pin=0 latch#=1hpc=0002 hlc=0002

 lwt=0x2cba6508[0x2cba6508,0x2cba6508]ltm=0x2cba6510[0x2cba6510,0x2cba6510]

 pwt=0x2cba64ec[0x2cba64ec,0x2cba64ec]ptm=0x2cba64f4[0x2cba64f4,0x2cba64f4]

 ref=0x2cba6528[0x2c08bd5c,0x2c08bd5c]lnd=0x2cba6534[0x2cba6534,0x2cba6534]

   CHILD REFERENCES:

   reference latch flags

   --------- ----- -------------------

    2c08bd5c     0CHL[02]

   LOCK OWNERS:

       lock    user  session count mode flags

   -------- -------- -------- ----- ----------------------------

   2d345160 30efbd2030efbd20     1N    [00]

   2d1a0bdc 30f10e7830f10e78     1N    [00]

   LIBRARY OBJECT: object=2c1a3280

   type=CRSR flags=EXS/RIV[0201] pflags=[0000]status=VALD load=0

   DEPENDENCIES: count=1 size=16

   dependency#   tablereference   handle position flags

   ----------- -------- --------- ---------------- -------------------

             0 2db7d838  2db7d770 2bb25644       18DEP[01]

   AUTHORIZATIONS: count=1 size=16 minimumentrysize=16

   00000000 30000000 00020000 00000000

   ACCESSES: count=1 size=16

   dependency# types

   ----------- -----

             0 0009

   SCHEMA: count=1 size=262144

   0000003d

   DATA BLOCKS:

   data#     heap pointer   status pins change whr

   ----- -------- -------- --------- ---- ------ ---

       0 2a078574 2c1a3318I/P/A/-/-    0NONE   00

        6 2aa6d2542c4f9cf0I/-/A/-/E    0 NONE   00

           这个结构和Heap 0的差不多。但是这个child cursor 没有name了。因为我们访问时先通过parentcursor。在到child cursor。所以parent cursor 有name 就可以了。

            这里的Data blocks 有2个： data block 0和data block 6. 对应heap 0 和heap 6.  关于Heap 0的说明如下：

           The data block structure for a heap, stored in heap 0,contains a pointer to thefirst data block that is allocated for the heap, astatus indicator, the pinunder which the heap is loaded, and so on.

            执行计划保存在Heap 6里，我们把Heap 6 dump 出来，看一下：

SYS@anqing1(rac1)> alter session setevents 'immediate tracename heapdump_addr level 2,addr 0x2aa6d254';

Session altered.

--注意，这里的addr前面加了0x。

SYS@anqing1(rac1)> oradebug setmypid

Statement processed.

SYS@anqing1(rac1)> oradebugtracefile_name

/u01/app/oracle/admin/anqing/udump/anqing1_ora_1533.trc

 在trace 里搜索：0x2aa6d254

HEAP DUMP heapname="sql area" desc=0x2aa6d254

 extent sz=0xff4 alt=32767 het=156 rec=0 flg=2opc=2

 parent=0x2000002c owner=0x2aa6d1c8 nex=(nil)xsz=0xff4

EXTENT 0 addr=0x2a73d5e8

 Chunk 2a73d5f0 sz=     788   free     "              "

Dump of memory from 0x2A73D5F0 to0x2A73D904

2A73D5F0 C0000315 00000000 2AA6D2B42AA6D2B4 [...........*...*]

2A73D600 2B10E1FC C0000051 000000002C49E55C [...+Q.......\.I,]

2A73D610 2AEA8820 00314E00 0000800400010035  [..*.N1.....5...]

2A73D620 00930024 00020001 0019003A00020074 [$.......:...t...]

2A73D630 001A0019 000200BF 007400B000500004 [..........t...P.]

2A73D640 001A0024 00BF0033 0003001200010022 [$...3......."...]

2A73D650 62000006 0000024D 2A73D6040C9D040C [...bM.....s*....]

2A73D660 00000000 2A73D8A0 0035000000240001 [......s*..5...$.]

2A73D670 00010093 003A0002 0074001900190002 [......:...t.....]

2A73D680 00BF001B 00B00002 0004007400240039 [........t...9.$.]

2A73D690 0033001B 001200BF 0022000300060001 [..3.......".....]

2A73D6A0 12021400 00010022 0A000006006B0802 [....".........k.]

2A73D6B0 03671F85 EF042C04 001C004E000C0000 [..g..,..N.......]

2A73D6C0 001D0400 001C0056 000002E0001D0040 [....V.......@...]

2A73D6D0 00B10028 000D0028 008C00260026005E [(...(...&...^.&.]

2A73D6E0 00A40026 000E000C 0E00002612011201 [&.......&.......]

2A73D6F0 00010022 AC000009 00AF0A0000010021 ["...........!...]

2A73D700 00020074 00210009 001500BF00780026 [t.....!.....&.x.]

....

 dbsnake blog里的总结如下：

           heap6实际上就是sql area（这个和上图中描述的heap 6是sql context不尽相同）。 Oracle把sql的执行计划存在了这个sql的子cursor的heap 6（也就是sql area）中,只不过存储的形式是编译好的二进制格式。

MOS 上的相关说明：

           Parsinga cursor builds four different library cache structures, if they do notalreadyexist, within the library cache:

1、parent cursor handle

2、parent cursor object, containing the child dependency list

3、child cursor handle, inserted in the child dependency list oftheparent object

4、childcursor object, containing the compilation and run-timeexecution plan for thecompiled SQL statement.

12  视图

12.1  X$KSMSP视图

Shared Pool 的空间分配和使用情况，可以通过一个内部视图来观察，这个视图就是X$KSMSP。

X$KSMSP的名称含义为: [K]ernal [S]torage [M]emory Management [S]GA Hea[P]其中每一行都代表着Shared Pool中的一个Chunk。以下是x$ksmsp的结构:

12.2  V$SQLAREA 视图 

这个视图保存了在数据库中执行的SQL 语句和PL/SQL 块的信息。下面的SQL 语句可以显示给你带有literal 的语句或者是带有绑定变量的语句：

SELECT  SUBSTR (sql_text, 1, 40) "SQL", COUNT (*),SUM (executions)"TotExecs"    FROM v$sqlarea WHERE executions < 5GROUP BY SUBSTR (sql_text, 1, 40)
  HAVING COUNT (*) > 30 ORDER BY 2;
注: Having 后的数值 "30" 可以根据需要调整以得到更为详细的信息。

12.3  X$KSMLRU 视图

·        X$KSMLRU 视图 这个固定表x$ksmlru 跟踪共享池中导致其它对象换出(age out)的应用。这个固定表可以用来标记是什么导致了大的应用。如果很多对象在共享池中都被阶段性的刷新可能导致响应时间问题并且有可能在对象重载入共享池中的时候导致库高速缓冲闩竞争问题。关于这个x$ksmlru 表的一个不寻常的地方就是如果有人从表中选取内容这个表的内容就会被擦除。这样这个固定表只存储曾经发生的最大的分配。这个值在选择后被重新设定这样接下来的大的分配可以被标记，即使它们不如先前的分配过的大。因为这样的重置，在查询提交后的结果不可以再次得到，从表中的输出的结果应该小心的保存。监视这个固定表运行如下操作：

SELECT * FROM X$KSMLRU WHEREksmlrsiz > 0;

·        这个表只可以用SYS用户登录进行查询。

12.4  X$KSMSP 视图

·        X$KSMSP 视图 (类似堆Heapdump信息)使用这个视图能找出当前分配的空闲空间，有助于理解共享池碎片的程度。如我们在前面的描述，查找为游标分配的足够的大块内存的第一个地方是空闲列表( free list)。下面的语句显示了空闲列表中的大块内存:
SELECT  '0 (<140)' bucket, ksmchcls, 10 * TRUNC (ksmchsiz / 10) "From",
             COUNT (*) "Count", MAX (ksmchsiz) "Biggest",
             TRUNC (AVG (ksmchsiz)) "AvgSize", TRUNC (SUM (ksmchsiz))"Total"
FROM x$ksmsp WHERE ksmchsiz < 140 AND ksmchcls = 'free'
GROUP BY ksmchcls, 10 * TRUNC (ksmchsiz / 10)
UNION ALL
SELECT   '1 (140-267)' bucket, ksmchcls, 20 * TRUNC (ksmchsiz / 20),
             COUNT (*), MAX (ksmchsiz), TRUNC (AVG (ksmchsiz)) "AvgSize",
             TRUNC (SUM (ksmchsiz)) "Total"
FROM x$ksmsp
WHERE ksmchsiz BETWEEN 140 AND 267 AND ksmchcls = 'free'
GROUP BY ksmchcls, 20 * TRUNC (ksmchsiz / 20)
UNION ALL
SELECT   '2 (268-523)' bucket, ksmchcls, 50 * TRUNC (ksmchsiz / 50),
             COUNT (*), MAX (ksmchsiz), TRUNC (AVG (ksmchsiz)) "AvgSize",
             TRUNC (SUM (ksmchsiz)) "Total"
FROM x$ksmsp
WHERE ksmchsiz BETWEEN 268 AND 523 AND ksmchcls = 'free'
GROUP BY ksmchcls, 50 * TRUNC (ksmchsiz / 50)
UNION ALL
SELECT   '3-5 (524-4107)' bucket, ksmchcls, 500 * TRUNC (ksmchsiz /500),
             COUNT (*), MAX (ksmchsiz), TRUNC (AVG (ksmchsiz)) "AvgSize",
             TRUNC (SUM (ksmchsiz)) "Total"
FROM x$ksmsp
WHERE ksmchsiz BETWEEN 524 AND 4107 AND ksmchcls = 'free'
GROUP BY ksmchcls, 500 * TRUNC (ksmchsiz / 500)
UNION ALL
SELECT   '6+ (4108+)' bucket, ksmchcls, 1000 * TRUNC (ksmchsiz /1000),
             COUNT (*), MAX (ksmchsiz), TRUNC (AVG (ksmchsiz)) "AvgSize",
             TRUNC (SUM (ksmchsiz)) "Total"
FROM x$ksmsp
WHERE ksmchsiz >= 4108 AND ksmchcls = 'free'
GROUP BY ksmchcls, 1000 * TRUNC (ksmchsiz / 1000);

13  dc_histogram_data

14  命令

清除shared_pool缓存。

alter system flush shared_pool;

14.1  判断碎片率：

SELECT free_space, avg_free_size,used_space, avg_used_size,request_failures, last_failure_size FROM v$shared_pool_reserved;

如果：REQUEST_FAILURES > 0 并且 LAST_FAILURE_SIZE

或者  REQUEST_FAILURES 等于0 并且 LAST_FAILURE_SIZE

这时候就需要去调整了

官方文档Diagnosing and ResolvingError ORA-04031 [ID 146599.1]详解关于ORA-04031的如下:把所有可能产生ORA04031的原因都讲到了还列举了很多bug和解决方法。不过现在10g已经很普遍了ora-04031的错误应该不多了。

14.2  LIBRARY CACHE命中率

               SELECT SUM(PINS)"EXECUTIONS",SUM(RELOADS) "CACHE MISSES WHILE EXECUTING"  FROMV$LIBRARYCACHE; --如果丢失超过1%,那么尝试通过加大共享池的大小来减少库高速缓冲丢失。

14.3  查看shared pool中librery cache的大小

select* from v$sgastat a where a.NAME like '%cache%';    

select* from v$sgastat a where a.NAME like '%library%'; 

14.4  查看shared pool中free momery的大小

   select * from v$sgastat a where a.pool = 'shared pool' and a.NAME = 'freememory';

14.5  v$libraercache中PINHITRATIO的值    

    selectsum(pinhits)/sum(pins)*100 from v$librarycache;

14.6  查看data dictionary cache中各部件的命中率

select parameter,sum(gets),sum(getmisses),100*sum(gets-getmisses)/sum(gets),sum(modifications)
from v$rowcache
where gets>0 group by parameter;

14.7  用如下sql判断其性能：

selectl.namespace,l.GETHITRATIO,l.PINHITRATIO,l.reloads,l.invalidations fromv$librarycache l;

14.8  查看锁住的对象

select xidusn, object_id, session_id,locked_mode from v$locked_object;

14.9  等待事件：

SQL> select event,sid,p1,p2,p3 fromv$session_wait where event not like 'SQL*%' and event not like 'rdbms%';

SQL> select event,sid,p1,p2,p3 fromv$session_wait where event like '%library cache lock%';

SELECT sid, username, event, p1text, p1,p2text, p2, p3text, p3, seconds_in_wait FROM gv$session WHERE event = 'librarycache lock';

14.10     等待解析的SQL语句状态

col operation format a50

col cost      format 999999

col kbytes    format 999999

col object    format a20

select hash_value,child_number,

       lpad('',2*depth)

       ||operation

       ||''

       ||options

      ||decode(id,0,substr(optimizer,1,6)||'Cost='||to_char(cost))operation,

      object_name,object_type,cost,round(bytes/1024) kbytes

from v$sql_plan where hash_value in(

                    select a.sql_hash_value

                    from v$session a,v$session_wait b

                    where a.sid=b.sid

                    and b.event='db filesequential read')

order by hash_value,child_number,ID;

14.11     解析

 select name,value from v$sysstat where namelike '%cursor%';

selectname,value from v$sysstat where name like '%parse%';

parse count（total）就是总的parse次数中，session cursor cache hits就是在session cursor cache中找到的次数，所占比例越高，性能越好。如果比例比较低，并且有剩余内存的话，可以考虑加大该参数。

14.12     统计信息查看

select * fromdba_tables where table_name = TABLE

--查看表的stats

select * from user_tab_statistics wheretable_name = 'STUDENT'

--查看列的stats

select * from user_tab_col_statistics wheretable_name = 'STUDENT'

--查看索引的stats

select * from user_ind_statistics wheretable_name = 'STUDENT'

15  定位思路

l SQL> select * from (select *from v$sys_time_model order by value desc ) where rownum <=10;

花在了sql executeelapsed time指标上，记录下stat_id=372226525

l Select * from (select sid,stat_name,valuefrom v$sess_time_model where stat_id=372226525 order by value desc ) whererownum <= 10;

时间(微秒)，同时获得SID。

SID号对应的PID

Select a.sid,a.username,a.status,a.process,b.spidfrom v$session a,v$process b where a.paddr=b.addr  and sid=2116;

l 查询v$session或v$session_wait视图来获得实际的执行事件event信息。

SQL> selectsid,event,wait_time,seconds_in_wait,state,wait_class from v$session_wait wheresid=144;

l 当然也可以查询v$session_wait_class来查询具体的wait_class信息：

SQL> selectwait_class_id,wait_class,total_waits,time_waited from v$session_wait_class where sid=144;

l 找到占用系统资源特别大的Oracle的Session及执行的SQL语句。

select sid,sql_text from v$session s,v$sql q wheresid = 144 and (q.sql_id=s.sql_id or q.sql_id = s.prev_sql_id);

select sid,sql_text from v$session s,v$sql q wheresid = 86 and (q.sql_id=s.sql_id or q.sql_id = s.prev_sql_id);

Oracle管理

相关

标签