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

来源：这里教程网时间：2026-03-03 16:37:20 作者：

[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$视图，生成执行计划并反复检查您是否能获得了任何性能好处总是值得的，因为根据您的配置，这些内存结构可能比您预期的要大得多。 --//也就是好好看看你的执行计划。