[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就是子光标的地址。