markhot过程在11gr2中引用，但是在11gr2中是不可见的，在12C中可见。此过程常常用于解决一些由于高并发引起的mutex争用。
下面内容来至12C官方文档
| MARKHOT ProcedureThis procedure marks a library cache object as a hot object.Syntax ParametersTable 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 ProcedureThis procedure unmarks a library cache object as a hot object.Syntax ParametersTable 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.sqlEnter 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 objectwww.htz.pw > alter system set "_kgl_hot_object_copies"=100 scope=spfile; System altered. |
|---|
2，查找HOT对象
| www.htz.pw > @db_kgl_hot_object.sqlwww.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 35Parent 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已经取消了。 |
|---|
