[20190102]DBMS_SHARED_POOL.MARKHOT与表.txt

来源：这里教程网时间：2026-03-03 12:49:46 作者：

[20190102]DBMS_SHARED_POOL.MARKHOT与表.txt --//昨天看链接http://www.anbob.com/archives/3195.html,讲表设置为MARKHOT导致不能insert和grant. --//好奇心,测试看看. --//我以前也做过类似的测试使用DBMS_SHARED_POOL.MARKHOT标识sql语句,参考链接: --//http://blog.itpub.net/267265/viewspace-2146632/ => [20171031]markhot.txt --//http://blog.itpub.net/267265/viewspace-2147197/ => [20171110]sql语句相同sql_id可以不同吗 --//实际上就是通过将标记hot的分散开来(或者叫hotcopy也许更合适一些),建立多个父子光标.减少争用. --//我记得我的测试并没有加快sql语句的执行,反而出现大量的存在大量library cache: mutex X等待事件. --//我个人也从来不再生产系统使用这项技术. 1.环境: SCOTT@book> @ ver1 PORT_STRING VERSION BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production SCOTT@book> create table depty as select * from dept; Table created. SCOTT@book> select * from depty where deptno=10; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK --//执行多次. 2.确定表depty的full_hash_value值并且设置markhot. SCOTT@book> select name,hash_value, full_hash_value, namespace, child_latch, property hot_flag, executions, invalidations from v$db_object_cache where name='DEPTY'; NAME HASH_VALUE FULL_HASH_VALUE NAMESPACE CHILD_LATCH HOT_FLAG EXECUTIONS INVALIDATIONS ------ ---------- -------------------------------- --------------- ----------- -------- ---------- ------------- DEPTY 395841086 a8a510f67cf725a57662736417980e3e TABLE/PROCEDURE 3646 0 0 --//确定FULL_HASH_VALUE='a8a510f67cf725a57662736417980e3e'.A SCOTT@book> select object_name,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE,NAMESPACE from DBA_OBJECTS where object_name='DEPTY' and owner=user; OBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE NAMESPACE -------------------- ---------- -------------- ------------------- ---------- DEPTY 90472 90472 TABLE 1 --//确定NAMESPACE=1,设置markhot. SCOTT@book> exec dbms_shared_pool.markhot( hash=>'a8a510f67cf725a57662736417980e3e', namespace=>1, global=>true); BEGIN dbms_shared_pool.markhot( hash=>'a8a510f67cf725a57662736417980e3e', namespace=>1, global=>true); END; * ERROR at line 1: ORA-06550: line 1, column 7: PLS-00201: identifier 'DBMS_SHARED_POOL.MARKHOT' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored --//scott权限不足.换成sys用户看看. SYS@book> exec dbms_shared_pool.markhot( hash=>'a8a510f67cf725a57662736417980e3e', namespace=>1, global=>true); PL/SQL procedure successfully completed. --//ok. SCOTT@book> select name,hash_value, full_hash_value, namespace, child_latch, property hot_flag, executions, invalidations from v$db_object_cache where name='DEPTY'; NAME HASH_VALUE FULL_HASH_VALUE NAMESPACE CHILD_LATCH HOT_FLAG EXECUTIONS INVALIDATIONS ----- ---------- -------------------------------- --------------- ----------- -------- ---------- ------------- DEPTY 395841086 a8a510f67cf725a57662736417980e3e TABLE/PROCEDURE 3646 HOT 0 0 SCOTT@book> insert into depty values (50,'aaa','bbb'); 1 row created. SCOTT@book> commit ; Commit complete. SCOTT@book> select name,hash_value, full_hash_value, namespace, child_latch, property hot_flag, executions, invalidations from v$db_object_cache where name='DEPTY'; NAME HASH_VALUE FULL_HASH_VALUE NAMESPACE CHILD_LATCH HOT_FLAG EXECUTIONS INVALIDATIONS -------------------- ---------- -------------------------------- ---------------- ----------- --------- ---------- ------------- DEPTY 395841086 a8a510f67cf725a57662736417980e3e TABLE/PROCEDURE 3646 HOT 0 0 DEPTY 1083859227 69ee7e6c9b0afcb92ebe64a8409a611b TABLE/PROCEDURE 24859 HOTCOPY11 0 0 --//执行授权看看. SYS@book> grant select ,insert on scott.depty to SH; grant select ,insert on scott.depty to SH * ERROR at line 1: ORA-03113: end-of-file on communication channel Process ID: 59448 Session ID: 41 Serial number: 95 --//alert出现如下错误: Wed Jan 02 10:56:02 2019 Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x60] [PC:0x94168B2, kss_first_child()+8] [flags: 0x0, count: 1] Errors in file /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_59448.trc (incident=2440518): ORA-07445: exception encountered: core dump [kss_first_child()+8] [SIGSEGV] [ADDR:0x60] [PC:0x94168B2] [Address not mapped to object] [] Incident details in: /u01/app/oracle/diag/rdbms/book/book/incident/incdir_2440518/book_ora_59448_i2440518.trc Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Wed Jan 02 10:56:04 2019 Dumping diagnostic data in directory=[cdmp_20190102105604], requested by (instance=1, osid=59448), summary=[incident=2440518]. Wed Jan 02 10:56:07 2019 Sweep [inc][2440518]: completed Sweep [inc2][2440518]: completed --//换成scott用户ok. SCOTT@book> grant select ,insert on scott.depty to SH; Grant succeeded. SCOTT@book> revoke select ,insert on scott.depty from SH; Revoke succeeded. --//继续使用sys或者system授权看看. SYS@book> grant select ,insert on scott.depty to SH; grant select ,insert on scott.depty to SH * ERROR at line 1: ORA-03113: end-of-file on communication channel Process ID: 59479 Session ID: 41 Serial number: 99 SYSTEM@book> grant select ,insert on scott.depty to SH; grant select ,insert on scott.depty to SH * ERROR at line 1: ORA-03113: end-of-file on communication channel Process ID: 59492 Session ID: 41 Serial number: 101 --//继续插入数据看看.遇到1次有点慢外,其它一切正常. SCOTT@book> insert into depty values (60,'aaa','bbb'); 1 row created. SCOTT@book> commit; Commit complete. --//折腾多次后,访问v$db_object_cache 出现如下: SCOTT@book> select name,hash_value, full_hash_value, namespace, child_latch, property hot_flag, executions, invalidations from v$db_object_cache where name='DEPTY'; NAME HASH_VALUE FULL_HASH_VALUE NAMESPACE CHILD_LATCH HOT_FLAG EXECUTIONS INVALIDATIONS ----- ---------- -------------------------------- ---------------- ----------- --------- ---------- ------------- DEPTY 395841086 a8a510f67cf725a57662736417980e3e TABLE/PROCEDURE 3646 HOT 0 0 DEPTY 3426232565 5c63091cfbd5024c30a78e7ecc3828f5 TABLE/PROCEDURE 10485 HOTCOPY6 0 0 DEPTY 255476228 15e38da223e527fd6becbfbe0f3a4204 TABLE/PROCEDURE 16900 HOTCOPY10 0 0 DEPTY 1083859227 69ee7e6c9b0afcb92ebe64a8409a611b TABLE/PROCEDURE 24859 HOTCOPY11 0 0 DEPTY 1922721636 21785cbf54349f3abd6e07b6729a6764 TABLE/PROCEDURE 26468 HOTCOPY1 0 0 DEPTY 442541055 2eccf41c3f0d45a3b65be4381a60a3ff TABLE/PROCEDURE 41983 HOTCOPY7 0 0 DEPTY 1144453167 b2a3a2a6a51cef51a17b04ed4436f82f TABLE/PROCEDURE 63535 HOTCOPY12 0 0 DEPTY 3150641877 2c455b9d7e5b2e4ce2de7562bbcafad5 TABLE/PROCEDURE 64213 HOTCOPY3 0 0 DEPTY 625156129 293228ad9a7ce38054dd4a9a25432021 TABLE/PROCEDURE 73761 HOTCOPY2 0 0 DEPTY 1162717419 4226e8d7cbacfe16d7892eb9454da8eb TABLE/PROCEDURE 108779 HOTCOPY5 0 0 DEPTY 617329663 b44fa35f27222ae713f06c6d24cbb3ff TABLE/PROCEDURE 111615 HOTCOPY4 0 0 DEPTY 2284960259 5aa84726b0bea7f5f262af138831ba03 TABLE/PROCEDURE 113155 HOTCOPY8 0 0 DEPTY 2585914079 a160ff29f3fb911219eb8f8e9a21eadf TABLE/PROCEDURE 125663 HOTCOPY9 0 0 13 rows selected. --//理论讲如果应用大量语句访问该表,把这个分散有一定缓解争用的情况.不过我没有遇到无法插入的情况. SCOTT@book> analyze table depty validate structure cascade; Table analyzed. --//真心不知道出现这种情况如何分析,定位问题. --//取消markhot.授权正常. SYS@book> exec dbms_shared_pool.unmarkhot( hash=>'a8a510f67cf725a57662736417980e3e', namespace=>1, global=>true); PL/SQL procedure successfully completed. SYS@book> grant select ,insert on scott.depty to SH; Grant succeeded. SYS@book> revoke select ,insert on scott.depty from SH; Revoke succeeded. SCOTT@book> select name,hash_value, full_hash_value, namespace, child_latch, property hot_flag, executions, invalidations from v$db_object_cache where name='DEPTY'; NAME HASH_VALUE FULL_HASH_VALUE NAMESPACE CHILD_LATCH HOT_FLAG EXECUTIONS INVALIDATIONS ------ ---------- -------------------------------- ---------------- ----------- --------- ---------- ------------- DEPTY 395841086 a8a510f67cf725a57662736417980e3e TABLE/PROCEDURE 3646 0 0 DEPTY 3426232565 5c63091cfbd5024c30a78e7ecc3828f5 TABLE/PROCEDURE 10485 HOTCOPY6 0 0 DEPTY 255476228 15e38da223e527fd6becbfbe0f3a4204 TABLE/PROCEDURE 16900 HOTCOPY10 0 0 DEPTY 1083859227 69ee7e6c9b0afcb92ebe64a8409a611b TABLE/PROCEDURE 24859 HOTCOPY11 0 0 DEPTY 1922721636 21785cbf54349f3abd6e07b6729a6764 TABLE/PROCEDURE 26468 HOTCOPY1 0 0 DEPTY 442541055 2eccf41c3f0d45a3b65be4381a60a3ff TABLE/PROCEDURE 41983 HOTCOPY7 0 0 DEPTY 1144453167 b2a3a2a6a51cef51a17b04ed4436f82f TABLE/PROCEDURE 63535 HOTCOPY12 0 0 DEPTY 3150641877 2c455b9d7e5b2e4ce2de7562bbcafad5 TABLE/PROCEDURE 64213 HOTCOPY3 0 0 DEPTY 625156129 293228ad9a7ce38054dd4a9a25432021 TABLE/PROCEDURE 73761 HOTCOPY2 0 0 DEPTY 1162717419 4226e8d7cbacfe16d7892eb9454da8eb TABLE/PROCEDURE 108779 HOTCOPY5 0 0 DEPTY 617329663 b44fa35f27222ae713f06c6d24cbb3ff TABLE/PROCEDURE 111615 HOTCOPY4 0 0 DEPTY 2284960259 5aa84726b0bea7f5f262af138831ba03 TABLE/PROCEDURE 113155 HOTCOPY8 0 0 DEPTY 2585914079 a160ff29f3fb911219eb8f8e9a21eadf TABLE/PROCEDURE 125663 HOTCOPY9 0 0 13 rows selected. --//FULL_HASH_VALUE=a8a510f67cf725a57662736417980e3e,已经取消了HOT_FLAG.其它刷新共享池就可以了. SCOTT@book> alter system flush shared_pool ; System altered. SCOTT@book> select name,hash_value, full_hash_value, namespace, child_latch, property hot_flag, executions, invalidations from v$db_object_cache where name='DEPTY'; no rows selected SCOTT@book> @ desc depty; Name Null? Type ------- -------- ------------- 1 DEPTNO NUMBER(2) 2 DNAME VARCHAR2(14) 3 LOC VARCHAR2(13) SCOTT@book> select name,hash_value, full_hash_value, namespace, child_latch, property hot_flag, executions, invalidations from v$db_object_cache where name='DEPTY'; NAME HASH_VALUE FULL_HASH_VALUE NAMESPACE CHILD_LATCH HOT_FLAG EXECUTIONS INVALIDATIONS -------------------- ---------- -------------------------------- ---------------- ----------- --------- ---------- ------------- DEPTY 395841086 a8a510f67cf725a57662736417980e3e TABLE/PROCEDURE 3646 0 0