oracle查询数据做标记,【学习笔记】Oracle如何使用dbms_shared_pool.markhot标记热对象...

【学习笔记】Oracle如何使用dbms_shared_pool.markhot标记热对象

时间:2016-10-30 20:17   来源:Oracle研究中心   作者:HTZ   点击:

次

天萃荷净

Oracle研究中心学习笔记：分享一篇关于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

IfTRUE (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

IfTRUE (default), unmark the object hot on all Oracle RAC instances

hash

16-byte hash value for the object

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

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

1，配置_kgl_hot_object_copies参数的值

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

oracleplus.net> @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

oracleplus.net> alter system set "_kgl_hot_object_copies"=100 scope=spfile;

System altered.

2，查找HOT对象

oracleplus.net> @db_kgl_hot_object.sql

oracleplus.net> 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号

oracleplus.net> @library_cache_namespace_to_id.sql

KGLSTDSC KGLSTIDN

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

SQL AREA 0 SQL语句

TABLE/PROCEDURE 1 包

BODY 2 包体

oracleplus.net> exec dbms_shared_pool.markhot(‘SYS’, ‘DBMS_STANDARD’, 1);

PL/SQL procedure successfully completed.

oracleplus.net> 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

oracleplus.net> select kglnahsv from x$kglob where kglnahsh=864012087 and kglhdadr =kglhdpar;

KGLNAHSV

——————————–

6d11d7de2049577d933d2385337fc737

oracleplus.net> exec DBMS_SHARED_POOL.MARKHOT(hash => ‘6d11d7de2049577d933d2385337fc737’, namespace => 0);

PL/SQL procedure successfully completed.

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

oracleplus.net> @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对象

oracleplus.net> exec DBMS_SHARED_POOL.UNMARKHOT(hash => ‘6d11d7de2049577d9Oracleoracleplus.net33d2385337fc737’, namespace => 0);

PL/SQL procedure successfully completed.

oracleplus.net> @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 | 认真就输

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle如何使用dbms_shared_pool.markhot标记热对象