[20190102]DBMS_SHARED_POOL.MARKHOT与表.txt

最新推荐文章于 2021-04-04 20:48:43 发布

cuixun7780

最新推荐文章于 2021-04-04 20:48:43 发布

阅读量139

点赞数

[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

------ ---------- -------------------------------- ---------------- ----------- --------- ---------- -------------