dbms_shared_pool.markhot标记热对象

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 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_OBJECTSand 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         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号

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已经取消了。