[20190102]DBMS_SHARED_POOL.MARKHOT與表.txt

--//昨天看連結講表設定為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

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