当前位置: 首页 > tool, 调优 > 正文

dbms_shared_pool.markhot标记热对象

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数据库超级群 共同学习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语句对应的namespaceID

www.htz.pw > @library_cache_namespace_to_id.sql

 

KGLSTDSC                                                           KGLSTIDN

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

SQL AREA                                                                  0 SQL语句

TABLE/PROCEDURE                                                           1

BODY                                                                      2 包体

 

www.htz.pw > exec dbms_shared_pool.markhot(‘SYS’, ‘DBMS_STANDARD’, 1);

 

PL/SQL procedure successfully completed.

 

www.htz.pw > exec dbms_shared_pool.markhot(‘SYS’, ‘DBMS_SPACE_ADMIN’, 2);

 

PL/SQL procedure successfully completed.

 

在配置SQL HASH VALUE的时候,我们需要注意的是16-byte hash value for the object

 

www.htz.pw > select kglnahsv from x$kglob where kglnahsh=864012087  and kglhdadr =kglhdpar;

 

KGLNAHSV

——————————–

6d11d7de2049577d933d2385337fc737

 

www.htz.pw > exec DBMS_SHARED_POOL.MARKHOT(hash => ‘6d11d7de2049577d933d2385337fc737’, namespace => 0);

 

PL/SQL procedure successfully completed.

4,查询已经配置的HOT对象

www.htz.pw > @db_kgl_hot_object_configured.sql

 

 II OWNER           OBJECT_NAME                                                                                              HASH_VALUE OBJECT_TYPE    SQL_ID          HOT

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

  1 SYS             DBMS_STANDARD                                                                                            2095250116 PACKAGE                        HOT

  1 SYS             DBMS_STANDARD                                                                                            3231142607 CURSOR                         HOT

  1 SYS             DBMS_SPACE_ADMIN                                                                                         2253886596 PACKAGE BODY                   HOT

  1                 select kernelcols, initrans, maxtrans, nvl(tab#, 0), ts#, file#, block#,  property from tab$ where o     1324598591 CURSOR         8d5zjux7g7j9z   HOT

                    bj# = :1

 

  1                 select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maxim      864012087 CURSOR         96g93hntrzjtr   HOT

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

                    intcol#=:2

5 取消已经配置的HOT对象

www.htz.pw > exec DBMS_SHARED_POOL.UNMARKHOT(hash => ‘6d11d7de2049577d933d2385337fc737’, namespace => 0);

 

PL/SQL procedure successfully completed.

www.htz.pw >  @db_kgl_hot_object_configured.sql

 

 II OWNER           OBJECT_NAME                                                                                              HASH_VALUE OBJECT_TYPE    SQL_ID          HOT

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

  1 SYS             DBMS_STANDARD                                                                                            2095250116 PACKAGE                        HOT

  1 SYS             DBMS_STANDARD                                                                                            3231142607 CURSOR                         HOT

  1 SYS             DBMS_SPACE_ADMIN                                                                                         2253886596 PACKAGE BODY                   HOT

  1                 select kernelcols, initrans, maxtrans, nvl(tab#, 0), ts#, file#, block#,  property from tab$ where o     1324598591 CURSOR         8d5zjux7g7j9z   HOT

                    bj# = :1

这里看到864012087这条SQL已经取消了。

本文固定链接: http://www.htz.pw/2014/09/13/dbms_shared_pool-markhot%e6%a0%87%e8%af%86%e7%83%ad%e5%af%b9%e8%b1%a1.html | 认真就输

该日志由 huangtingzhong 于2014年09月13日发表在 tool, 调优 分类下, 你可以发表评论,并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: dbms_shared_pool.markhot标记热对象 | 认真就输
关键字: , , , ,