[20201207]12c v$open_cursor檢視.txt

[20201207]12c v$open_cursor檢視.txt

--//12cv$open_cursor檢視定義與11g有點不同,透過測試說明問題.注我使用18c，並不影響測試結果。

1.環境:
TTT@192.168.X.X:1521/orcl> select BANNER from v$version ;
BANNER
----------------------------------------------------------------------
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
--//測試使用18c也是一樣的。

2.測試:
--//session 1:
TTT@192.168.X.X:1521/orcl> @ spid
       SID    SERIAL# PROCESS SERVER             SPID       PID  P_SERIAL# C50
---------- ---------- ------- ------------------ ------ ------- ---------- --------------------------------------------------
       395      21260 32697   DEDICATED          28776       43     163433 alter system kill session '395,21260' immediate;

TTT@192.168.X.X:1521/orcl> select * from dept where deptno=10;
    DEPTNO DNAME                        LOC
---------- ---------------------------- --------------------------
        10 ACCOUNTING                   NEW YORK
--//執行5次以上.

TTT@192.168.X.X:1521/orcl> @ tpt/hash
HASH_VALUE SQL_ID                     CHILD_NUMBER HASH_HEX
---------- -------------------------- ------------ ------------------
 911274289 4xamnunv51w9j                         0  3650f131

--//session 2:
SYS@192.168.X.X:1521/orcl> select * from v$open_cursor where sid=395 and sql_id='4xamnunv51w9j'
  2  @ prxx
==============================
SADDR                         : 00000000924558D0
SID                           : 395
USER_NAME                     : TTT
ADDRESS                       : 00000001064A04D8
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
HASH_VALUE                    : 911274289
SQL_ID                        : 4xamnunv51w9j
SQL_TEXT                      : select * from dept where deptno=10
LAST_SQL_ACTIVE_TIME          : 2020-12-07 08:47:28
SQL_EXEC_ID                   :
CURSOR_TYPE                   : SESSION CURSOR CACHED
CHILD_ADDRESS                 : 00000000E5D4F420
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CON_ID                        : 3
PL/SQL procedure successfully completed.
--//注意看下劃線,增加了CHILD_ADDRESS,CON_ID列。

SYS@192.168.X.X:1521/orcl> @ sharepool/shp4 4xamnunv51w9j 0
TEXT                  KGLHDADR         KGLHDPAR         C40                                        KGLHDLMD   KGLHDPMD   KGLHDIVC KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03                     KGLOBT09
--------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- -------------------------- ----------
child handle address  00000000E5D4F420 00000001064A04D8 select * from dept where deptno=10                1          0          0 0000000076F94EE0 00000000BFBB9948       4072      16200       3219     23491      23491  911274289 4xamnunv51w9j                       0
parent handle address 00000001064A04D8 00000001064A04D8 select * from dept where deptno=10                1          0          0 0000000100C653E0 00                     4072          0          0      4072       4072  911274289 4xamnunv51w9j                   65535
--//上面查詢v$open_cursor的CHILD_ADDRESS :00000000E5D4F420,正好對上子游標的KGLHDADR=00000000E5D4F420。

SYS@192.168.X.X:1521/orcl> set desc linenum on
SYS@192.168.X.X:1521/orcl> @ desc v$open_cursor
           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    1      SADDR                                    RAW(8)
    2      SID                                      NUMBER
    3      USER_NAME                                VARCHAR2(128)
    4      ADDRESS                                  RAW(8)
    5      HASH_VALUE                               NUMBER
    6      SQL_ID                                   VARCHAR2(13)
    7      SQL_TEXT                                 VARCHAR2(60)
    8      LAST_SQL_ACTIVE_TIME                     DATE
    9      SQL_EXEC_ID                              NUMBER
   10      CURSOR_TYPE                              VARCHAR2(64)
   11      CHILD_ADDRESS                            RAW(8)
   12      CON_ID                                   NUMBER

--//可以發現12c比11g檢視v$open_cursor增加2列.CHILD_ADDRESS,CON_ID.
--//也就是12c檢視裡面增加子游標handle address.

--//對比11g的情況：
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> set desc linenum on
SCOTT@book> @ desc v$open_cursor
           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    1      SADDR                                    RAW(8)
    2      SID                                      NUMBER
    3      USER_NAME                                VARCHAR2(30)
    4      ADDRESS                                  RAW(8)
    5      HASH_VALUE                               NUMBER
    6      SQL_ID                                   VARCHAR2(13)
    7      SQL_TEXT                                 VARCHAR2(60)
    8      LAST_SQL_ACTIVE_TIME                     DATE
    9      SQL_EXEC_ID                              NUMBER
   10      CURSOR_TYPE                              VARCHAR2(64)

3.繼續測試:
--//session 1:
TTT@192.168.X.X:1521/orcl> alter session set optimizer_index_caching=1;
Session altered.

select * from dept where deptno=10;
--//執行多次.

--//session 2:
SYS@192.168.X.X:1521/orcl> select * from v$open_cursor where sid=395 and sql_id='4xamnunv51w9j'
  2  @ prxx
==============================
SADDR                         : 00000000924558D0
SID                           : 395
USER_NAME                     : TTT
ADDRESS                       : 00000001064A04D8
HASH_VALUE                    : 911274289
SQL_ID                        : 4xamnunv51w9j
SQL_TEXT                      : select * from dept where deptno=10
LAST_SQL_ACTIVE_TIME          : 2020-12-07 08:47:28
SQL_EXEC_ID                   :
CURSOR_TYPE                   : SESSION CURSOR CACHED
CHILD_ADDRESS                 : 00000000E5D4F420
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CON_ID                        : 3
==============================
SADDR                         : 00000000924558D0
SID                           : 395
USER_NAME                     : TTT
ADDRESS                       : 00000001064A04D8
HASH_VALUE                    : 911274289
SQL_ID                        : 4xamnunv51w9j
SQL_TEXT                      : select * from dept where deptno=10
LAST_SQL_ACTIVE_TIME          : 2020-12-07 08:53:03
SQL_EXEC_ID                   :
CURSOR_TYPE                   : SESSION CURSOR CACHED
CHILD_ADDRESS                 : 00000001031D80B8
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CON_ID                        : 3

PL/SQL procedure successfully completed.

SYS@192.168.X.X:1521/orcl> set linesize 300
SYS@192.168.X.X:1521/orcl> @ sharepool/shp4 4xamnunv51w9j 0
TEXT                  KGLHDADR         KGLHDPAR         C40                                        KGLHDLMD   KGLHDPMD   KGLHDIVC KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03                     KGLOBT09
--------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- -------------------------- ----------
child handle address  00000000E5D4F420 00000001064A04D8 select * from dept where deptno=10                1          0          0 0000000076F94EE0 00000000BFBB9948       4072      16200       5035     25307      25307  911274289 4xamnunv51w9j                       0
child handle address  00000001031D80B8 00000001064A04D8 select * from dept where deptno=10                1          0          0 00000001031D7EF8 00000000E5C9A580       4072      16200       5035     25307      25307  911274289 4xamnunv51w9j                       1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
parent handle address 00000001064A04D8 00000001064A04D8 select * from dept where deptno=10                1          0          0 0000000100C653E0 00                     8144          0          0      8144       8144  911274289 4xamnunv51w9j                   65535
--//子游標控制程式碼地址能與前面的檢視對上.正是透過這個子游標控制程式碼地址,能很快定位語句的執行計劃,減少latch 或者mutex的消耗.

SYS@192.168.X.X:1521/orcl> column VIEW_DEFINITION format a80
SYS@192.168.X.X:1521/orcl> select * from V$FIXED_VIEW_DEFINITION where view_name='GV$OPEN_CURSOR';
VIEW_NAME       VIEW_DEFINITION                                                                      CON_ID
--------------- -------------------------------------------------------------------------------- ----------
GV$OPEN_CURSOR  select inst_id,kgllkuse, kgllksnm, user_name, kglhdpar, kglnahsh,                         0
                     kgllksqlid, kglnaobj, kgllkest,
                   decode(kgllkexc, 0, to_number(NULL), kgllkexc), kgllkctp,
                 kgllkhdl, con_id                                                       from x$k
                gllk where kglhdnsp = 0 and kglhdpar != kgllkhdl
--//12c v$open_cutsor增加CON_ID 欄位外,還增加CHILD_ADDRESS欄位.這樣更加清晰指示執行計劃使用那個子游標.
--//上官方網站.以下是11.1的參考：


V$OPEN_CURSOR
V$OPEN_CURSOR lists cursors that each user session currently has opened and parsed.
Column                  Datatype        Description
-----------------------------------------------------------------------------------------------------------------------------------------------
SADDR                   RAW(4 | 8)      Session address
SID                     NUMBER          Session identifier
USER_NAME               VARCHAR2(30)    User that is logged in to the session
ADDRESS                 RAW(4 | 8)      Used with HASH_VALUE to uniquely identify the SQL statement being executed in the session
HASH_VALUE              NUMBER          Used with ADDRESS to uniquely identify the SQL statement being executed in the session
SQL_ID                  VARCHAR2(13)    SQL identifier of the SQL statement being executed in the session
SQL_TEXT                VARCHAR2(60)    First 60 characters of the SQL statement that is parsed into the open cursor
LAST_SQL_ACTIVE_TIME    DATE            Time when this cursor was last executed
SQL_EXEC_ID             NUMBER          If the open cursor is executing, then the SQL execution identifier for that execution (see V$SQL_MONITOR)
--------------------------------------------------------------------------------------------------------------------------------------------------
--//11.2還增加了cursor_type欄位.
--//這樣就可以很好避免我以前遇到的困惑.連結:
--//http://blog.itpub.net/267265/viewspace-2682615/=>[20200326]dbms_monitor跟蹤與SQL語句分析.txt