dbms_shared_pool.markhot标记热对象

huangtingzhong 发布于 2014-09-13 15:42工具, 调优抢沙发

markhot过程在11gr2中引用，但是在11gr2中是不可见的，在12C中可见。此过程常常用于解决一些由于高并发引起的mutex争用。

下面内容来至12C官方文档

MARKHOT Procedure

This procedure marks a library cache object as a hot object.

Syntax

DBMS_SHARED_POOL.MARKHOT (
   schema         VARCHAR2,
   objname        VARCHAR2,
   namespace      NUMBER DEFAULT 1,   global         BOOLEAN DEFAULT TRUE);

DBMS_SHARED_POOL.MARKHOT (
   hash          VARCHAR2,
   namespace     NUMBER DEFAULT 1,
   global        BOOLEAN DEFAULT TRUE);

Parameters

Table 142-4 MARKHOT Procedure Parameters

Parameter	Description
schema	User name or the schema to which the object belongs
objname	Name of the object
namespace	Number indicating the library cache namespace in which the object is to be searched. Views, such asUSER_OBJECTS and DBA_OBJECTS, reflect the namespace as a number column, as do most dictionary tables such as OBJ$.
global	If TRUE (default), mark the object hot on all Oracle RAC instances
hash	16-byte hash value for the object

UNMARKHOT Procedure

This procedure unmarks a library cache object as a hot object.

Syntax

DBMS_SHARED_POOL.UNMARKHOT (
   schema         VARCHAR2,
   objname        VARCHAR2,
   namespace      NUMBER DEFAULT 1,   global         BOOLEAN DEFAULT TRUE);

DBMS_SHARED_POOL.UNMARKHOT (
   hash          VARCHAR2,
   namespace     NUMBER DEFAULT 1,
   global        BOOLEAN DEFAULT TRUE);

Parameters

Table 142-8 UNMARKHOT Procedure Parameters

Parameter	Description
schema	User name or the schema to which the object belongs
objname	Name of the object
namespace	Number indicating the library cache namespace in which the object is to be searched
global	If TRUE (default), unmark the object hot on all Oracle RAC instances
hash	16-byte hash value for the object

其实这里还可以通过_kgl_debug参数来实现

下面测试如果在11gr2环境中配置

欢迎大家加入QQ群: 共同学习ORACLE技术

1，配置_kgl_hot_object_copies参数的值

此参数默认值为CPU的个数，参数是静态参数，修改后需要重启数据库才生效

www.htz.pw > @parameter_hide.sql

Enter Search Parameter (i.e. max|all) : _kgl_hot_object_copies

PARAMETER SESSION_VALUE INSTANCE_VALUE DESCRIPTION

—————————————- ——————– ——————– ————————————

_kgl_hot_object_copies 0 0 Number of copies for the hot object

www.htz.pw > alter system set "_kgl_hot_object_copies"=100 scope=spfile;

System altered.

2，查找HOT对象

www.htz.pw > @db_kgl_hot_object.sql

www.htz.pw > set echo off;

Enter value for order_locked_or_pinned: pinned

CURSOR ADDRESS NAME HASH_VALUE TYPE LOCKED_TOTAL PINNED

———- —————- —————————————————————————————————- ————- ————— —————- ———-

Child 0 00000000DE707410 select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maxim 864012087 CURSOR 15 1146

um, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and

intcol#=:2

………….

Parent 00000000DE6FAA28 DBMS_STANDARD 2095250116 PACKAGE 35 35

Parent 00000000DE52B3C8 DBMS_SPACE_ADMIN 2253886596 PACKAGE BODY 1 35

这里以包，包体，SQL语句为例来配置

3，配置hot对象

这里需要查询对包，包体，SQL语句对应的namespace的ID号