[20200416]關於軟軟解析的問題.txt

[20200416]關於軟軟解析的問題.txt

--//別人問的問題,如果在一個會話裡面如果一條語句存在2個子游標快取,這樣v$open_cursor看到幾條.
--//透過測試說明問題:

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

2.測試:
--//sessionn 1:
SCOTT@book> @ spid
       SID    SERIAL# PROCESS                  SERVER    SPID       PID  P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------
       295       1945 39670                    DEDICATED 39671       21        183 alter system kill session '295,1945' immediate;

select * from dept where deptno=10;
select * from dept where deptno=10;
select * from dept where deptno=10;
select * from dept where deptno=10;
select * from dept where deptno=10;

SCOTT@book> @ hash
HASH_VALUE SQL_ID        CHILD_NUMBER HASH_HEX
---------- ------------- ------------ ---------
 911274289 4xamnunv51w9j            0  3650f131

--//sql_id=4xamnunv51w9j
--//session 2:
SYS@book> column sql_text format a40
SYS@book> select * from v$open_cursor where sql_id='4xamnunv51w9j' and sid=295;
SADDR                   SID USER_NAME ADDRESS          HASH_VALUE SQL_ID        SQL_TEXT                                 LAST_SQL_ACTIVE_TIM SQL_EXEC_ID CURSOR_TYPE
---------------- ---------- --------- ---------------- ---------- ------------- ---------------------------------------- ------------------- ----------- ---------------------
0000000085EC7D20        295 SCOTT     000000007C253FE8  911274289 4xamnunv51w9j select * from dept where deptno=10                                       SESSION CURSOR CACHED

--//sessionn 1:
SCOTT@book> alter session set optimizer_index_cost_adj=99;
Session altered.

select * from dept where deptno=10;
select * from dept where deptno=10;
select * from dept where deptno=10;
select * from dept where deptno=10;
select * from dept where deptno=10;

--//session 2:
SYS@book> select * from v$open_cursor where sql_id='4xamnunv51w9j' and sid=295;
SADDR                   SID USER_NAME ADDRESS          HASH_VALUE SQL_ID        SQL_TEXT                                 LAST_SQL_ACTIVE_TIM SQL_EXEC_ID CURSOR_TYPE
---------------- ---------- --------- ---------------- ---------- ------------- ---------------------------------------- ------------------- ----------- ---------------------
0000000085EC7D20        295 SCOTT     000000007C253FE8  911274289 4xamnunv51w9j select * from dept where deptno=10                                       SESSION CURSOR CACHED
0000000085EC7D20        295 SCOTT     000000007C253FE8  911274289 4xamnunv51w9j select * from dept where deptno=10                                       SESSION CURSOR CACHED

SYS@book> select distinct * from v$open_cursor where sql_id='4xamnunv51w9j' and sid=295;
SADDR                   SID USER_NAME ADDRESS          HASH_VALUE SQL_ID        SQL_TEXT                                 LAST_SQL_ACTIVE_TIM SQL_EXEC_ID CURSOR_TYPE
---------------- ---------- --------- ---------------- ---------- ------------- ---------------------------------------- ------------------- ----------- ---------------------
0000000085EC7D20        295 SCOTT     000000007C253FE8  911274289 4xamnunv51w9j select * from dept where deptno=10                                       SESSION CURSOR CACHED
--//兩行內容一樣。

SYS@book> @ tpt/sql_id 4xamnunv51w9j %
Show SQL text, child cursors and execution stats for SQLID 4xamnunv51w9j child %
HASH_VALUE  CH# SQL_TEXT
---------- ---- ----------------------------------
 911274289    0 select * from dept where deptno=10
 911274289    1 select * from dept where deptno=10

old  24:        sql_id = ('&1')
new  24:        sql_id = ('4xamnunv51w9j')
old  25: and child_number like '&2'
new  25: and child_number like '%'
 CH# PARENT_HANDLE    OBJECT_HANDLE     PLAN_HASH     PARSES   H_PARSES EXECUTIONS    FETCHES ROWS_PROCESSED     CPU_MS     ELA_MS       LIOS       PIOS      SORTS USERS_EXECUTING
---- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- -------------- ---------- ---------- ---------- ---------- ---------- ---------------
   0 000000007C253FE8 000000007C252B68 2852011669          5          1          5          5              5      4.999      5.048         87          7          0               0
   1 000000007C253FE8 000000007C206860 2852011669          5          1          5          5              5      1.999      2.232         10          0          0               0
--//PARENT_HANDLE對應v$open_cursor的ADDRESS。實際上v$open_cursor訪問的基表是x$kgllk。
SYS@book> @ sharepool/shp4 4xamnunv51w9j 0
old  20:  WHERE kglobt03 = '&1'  or kglhdpar='&1' or kglhdadr='&1' or KGLNAHSH= &2
new  20:  WHERE kglobt03 = '4xamnunv51w9j'  or kglhdpar='4xamnunv51w9j' or kglhdadr='4xamnunv51w9j' or KGLNAHSH= 0
TEXT                  KGLHDADR         KGLHDPAR         C40                                        KGLHDLMD   KGLHDPMD   KGLHDIVC KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLOBT09
--------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
child handle address  000000007C252B68 000000007C253FE8 select * from dept where deptno=10                1          0          0 000000007C252AB0 000000007C253638       4528      12144       4347     21019      21019  911274289 4xamnunv51w9j          0
child handle address  000000007C206860 000000007C253FE8 select * from dept where deptno=10                1          0          0 000000007C202300 000000007C253C00       4528      12144       4347     21019      21019  911274289 4xamnunv51w9j          1
parent handle address 000000007C253FE8 000000007C253FE8 select * from dept where deptno=10                1          0          0 000000007C253F30 00                     4720          0          0      4720       4720  911274289 4xamnunv51w9j      65535

SELECT inst_id
      ,kgllkuse
      ,kgllksnm
      ,user_name
      ,kglhdpar
      ,kglnahsh
      ,kgllksqlid
      ,kglnaobj
      ,kgllkest
      ,DECODE (kgllkexc, 0, TO_NUMBER (NULL), kgllkexc)
      ,kgllkctp
      ,kgllkhdl
  FROM x$kgllk
 WHERE     kglhdnsp = 0
       AND kglhdpar != kgllkhdl
       AND kgllksqlid = '4xamnunv51w9j'
       AND kgllksnm = 295;

INST_ID KGLLKUSE           KGLLKSNM USER_NAME KGLHDPAR           KGLNAHSH KGLLKSQLID    KGLNAOBJ                           KGLLKEST DECODE(KGLLKEXC,0,TO_NUMBER(NULL),KGLLKEXC) KGLLKCTP              KGLLKHDL
------- ---------------- ---------- --------- ---------------- ---------- ------------- ---------------------------------- -------- ------------------------------------------- --------------------- ----------------
      1 0000000085EC7D20        295 SCOTT     000000007C253FE8  911274289 4xamnunv51w9j select * from dept where deptno=10                                                      SESSION CURSOR CACHED 000000007C252B68
      1 0000000085EC7D20        295 SCOTT     000000007C253FE8  911274289 4xamnunv51w9j select * from dept where deptno=10                                                      SESSION CURSOR CACHED 000000007C206860
--//後面的KGLLKHDL就是子游標的地址。