[20210418]查詢v$檢視問題.txt

[20210418]查詢v$檢視問題.txt

--//昨天看連結，實際上類似的問題我自
--//己也遇到過。x需要在會話級別上設定cursor_sharing =force。裡面有一段程式碼：

-- Get user SID information
SELECT SID
  INTO v_sid
  FROM v$mystat
 WHERE ROWNUM = 1;

-- Get Program executable,OSUSER Details,Machine Details for this session
SELECT LOWER (program)
      ,osuser
      ,machine
      ,module
  INTO v_exe
      ,v_osuser
      ,v_machine
      ,v_module
  FROM v$session
 WHERE SID = v_sid;

--//我當時修改如下：

SELECT LOWER (program)
      ,osuser
      ,machine
      ,module
  INTO v_exe
      ,v_osuser
      ,v_machine
      ,v_module
  FROM v$session
 WHERE SID = ( SELECT SID FROM v$mystat WHERE ROWNUM = 1);

--//一樣是很"慢",注意這裡慢要打上引號，實際上在awr報表上能看到我改寫的語句（以前看不到）。實際上我在最佳化過程中就出現了鏈
--//接看到的奇怪現象,我後來還是分開寫。

http://blog.itpub.net/267265/viewspace-2740080/ =>[20201204]為什麼返回2行記錄.txt
http://blog.itpub.net/267265/viewspace-2740213/ =>[20201208]為什麼返回2行記錄補充.txt

1.環境：
SYS@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

SYS@book> @ spid
       SID    SERIAL# PROCESS                  SERVER    SPID       PID  P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------
        58         13 47135                    DEDICATED 47136       28          6 alter system kill session '58,13' immediate;

2.測試：
SYS@book> select * from v$session where sid=58;
Plan hash value: 1627146547
--------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name            | E-Rows |E-Bytes| Cost (%CPU)|  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                 |        |       |     1 (100)|       |       |          |
|   1 |  MERGE JOIN CARTESIAN     |                 |      1 |  1378 |     0   (0)|       |       |          |
|   2 |   NESTED LOOPS            |                 |      1 |   370 |     0   (0)|       |       |          |
|*  3 |    FIXED TABLE FIXED INDEX| X$KSLWT (ind:1) |      1 |   161 |     0   (0)|       |       |          |
|*  4 |    FIXED TABLE FIXED INDEX| X$KSLED (ind:2) |      1 |   209 |     0   (0)|       |       |          |
|   5 |   BUFFER SORT             |                 |      1 |  1008 |     0   (0)|  2048 |  2048 | 2048  (0)|
|*  6 |    FIXED TABLE FIXED INDEX| X$KSUSE (ind:1) |      1 |  1008 |     0   (0)|       |       |          |
--------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$5C160134
   3 - SEL$5C160134 / W@SEL$3
   4 - SEL$5C160134 / E@SEL$3
   6 - SEL$5C160134 / S@SEL$3
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("W"."KSLWTSID"=58)
   4 - filter("W"."KSLWTEVT"="E"."INDX")
   6 - filter(("S"."INDX"=58 AND "S"."INST_ID"=USERENV('INSTANCE') AND BITAND("S"."KSSPAFLG",1)<>0
              AND BITAND("S"."KSUSEFLG",1)<>0))

SYS@book> select * from v$session where sid=userenv('SID');
Plan hash value: 2422122865
--------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name            | E-Rows |E-Bytes| Cost (%CPU)|  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                 |        |       |     1 (100)|       |       |          |
|   1 |  MERGE JOIN CARTESIAN     |                 |      1 |  1378 |     0   (0)|       |       |          |
|   2 |   NESTED LOOPS            |                 |      1 |   370 |     0   (0)|       |       |          |
|*  3 |    FIXED TABLE FULL       | X$KSLWT         |      1 |   161 |     0   (0)|       |       |          |
|*  4 |    FIXED TABLE FIXED INDEX| X$KSLED (ind:2) |      1 |   209 |     0   (0)|       |       |          |
|   5 |   BUFFER SORT             |                 |      1 |  1008 |     0   (0)|  2048 |  2048 | 2048  (0)|
|*  6 |    FIXED TABLE FULL       | X$KSUSE         |      1 |  1008 |     0   (0)|       |       |          |
--------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$5C160134
   3 - SEL$5C160134 / W@SEL$3
   4 - SEL$5C160134 / E@SEL$3
   6 - SEL$5C160134 / S@SEL$3
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("W"."KSLWTSID"=USERENV('SID'))
   4 - filter("W"."KSLWTEVT"="E"."INDX")
   6 - filter(("S"."INDX"=USERENV('SID') AND "S"."INST_ID"=USERENV('INSTANCE') AND
              BITAND("S"."KSSPAFLG",1)<>0 AND BITAND("S"."KSUSEFLG",1)<>0))
--//你可以發現id =3, oracle沒有把USERENV('SID')當作常量處理。
--//使用繫結變數呢？

SYS@book> variable n number;
SYS@book> exec :n := 58
PL/SQL procedure successfully completed.

SYS@book> select * from v$session where sid= :n;
Plan hash value: 1627146547
--------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name            | E-Rows |E-Bytes| Cost (%CPU)|  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                 |        |       |     1 (100)|       |       |          |
|   1 |  MERGE JOIN CARTESIAN     |                 |      1 |  1378 |     0   (0)|       |       |          |
|   2 |   NESTED LOOPS            |                 |      1 |   370 |     0   (0)|       |       |          |
|*  3 |    FIXED TABLE FIXED INDEX| X$KSLWT (ind:1) |      1 |   161 |     0   (0)|       |       |          |
|*  4 |    FIXED TABLE FIXED INDEX| X$KSLED (ind:2) |      1 |   209 |     0   (0)|       |       |          |
|   5 |   BUFFER SORT             |                 |      1 |  1008 |     0   (0)|  2048 |  2048 | 2048  (0)|
|*  6 |    FIXED TABLE FIXED INDEX| X$KSUSE (ind:1) |      1 |  1008 |     0   (0)|       |       |          |
--------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$5C160134
   3 - SEL$5C160134 / W@SEL$3
   4 - SEL$5C160134 / E@SEL$3
   6 - SEL$5C160134 / S@SEL$3
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("W"."KSLWTSID"=:N)
   4 - filter("W"."KSLWTEVT"="E"."INDX")
   6 - filter(("S"."INDX"=:N AND "S"."INST_ID"=USERENV('INSTANCE') AND BITAND("S"."KSSPAFLG",1)<>0
              AND BITAND("S"."KSUSEFLG",1)<>0))

--//OK.
--//作者給出了一個物化檢視的方式解決這類問題：

with mysid as ( select /*+ materialize */ userenv('SID') n from dual )
select * from v$session, mysid
where sid = n;

with mysid as
 ( select /*+ result_cache */ userenv('SID') n from dual )
select * from v$session, mysid
where sid = n;

--//另外這樣寫也不是最優的：
with mysid as ( select /*+ materialize */ userenv('SID') n from dual )
select * from v$session
where sid in (select n from mysid);

--//作者給出一個建議：

If you're querying the V$ views, it is always worth generating an execution plan and double-checking that you are
getting any performance benefits you can, because those memory structures could be a lot larger than you expect
depending on your configuration

如果您正在查詢V$檢視，生成執行計劃並反覆檢查您是否能獲得了任何效能好處總是值得的，因為根據您的配置，這些記憶體結構可能比您
預期的要大得多。
--//也就是好好看看你的執行計劃。