[20210418]查询v$视图问题.txt

--//昨天看链接https://connor-mcdonald.com/2021/04/12/better-performance-when-querying-the-v-views/，实际上类似的问题我自
--//己也遇到过。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$视图，生成执行计划并反复检查您是否能获得了任何性能好处总是值得的，因为根据您的配置，这些内存结构可能比您
预期的要大得多。
--//也就是好好看看你的执行计划。