markhot过程在11gr2中引用,但是在11gr2中是不可见的,在12C中可见。此过程常常用于解决一些由于高并发引起的mutex争用。
下面内容来至12C官方文档
MARKHOT Procedure This procedure marks a library cache object as a hot object. Syntax
Parameters Table 142-4 MARKHOT Procedure Parameters
UNMARKHOT Procedure This procedure unmarks a library cache object as a hot object. Syntax
Parameters Table 142-8 UNMARKHOT Procedure Parameters
其实这里还可以通过_kgl_debug参数来实现 |
下面测试如果在11gr2环境中配置
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号
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已经取消了。 |