[20250729]关于访问系统视图v$sqlXXXX的问题思考.txt
--//前几天做测试，几个会话同时访问v$sql，出现library cache: bucket mutex X等待，猜测执行select count(*) from v$sql where
--//rownum<=1;先从library cache bucket=0开始/扫描。如果存在对象给对应的bucket加上mutex X,扫描找到满足条件的记录停止。
--//让我思考访问这些视图的一些问题，一些情况如果不加提示不会使用索引。
1.环境：
SCOTT@book01p> @ver2
==============================
PORT_STRING : x86_64/Linux 2.4.xx
VERSION : 21.0.0.0.0
BANNER : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID : 0
PL/SQL procedure successfully completed.
2.测试：
SCOTT@book01p> select sql_id,sql_text from v$sql where sql_id ='a' ;
no rows selected
SCOTT@book01p> @ dpca
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 9hxah9m8su3fh, child number 0
-------------------------------------
select sql_id,sql_text from v$sql where sql_id ='a'
Plan hash value: 1346707802
-------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)|
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)|
|* 1 | FIXED TABLE FIXED INDEX| X$KGLCURSOR_CHILD (ind:2) | 1 | 536 | 0 (0)|
-------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$AF73C875 / "X$KGLCURSOR_CHILD"@"SEL$5"
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("KGLOBT03"='a' AND INTERNAL_FUNCTION("CON_ID") AND
"INST_ID"=USERENV('INSTANCE')))
--//访问v$sql的底层X$是 x$kglcursor_child，执行里面记录(ind：2)表示可以使用第2个"索引"。
SYS@book> @ xind x\$kglcursor_child$
IF $ OCCUR IN MIDDLE USING \$ !! ,for example:
@ xind x\$table_name
@ xind x\$kglob$|x\$kgldp$
DERIVED_TABLES TABLE_NAME INDEX_NUMBER COLUMN_NAME COLUMN_POSITION CON_ID
------------------------------ ------------------------------ ------------ ------------------------------ --------------- ----------
X$KGLCURSOR_CHILD X$KGLOB 1 KGLNAHSH 0 0
X$KGLCURSOR_CHILD X$KGLOB 2 KGLOBT03 0 0
SCOTT@book01p> select sql_id,sql_text from v$sql where sql_id ='a' or sql_id='b';
no rows selected
SCOTT@book01p> @ dpca
PLAN_TABLE_OUTPUT
------------------------------------
SQL_ID fn2dbtsqhs6y3, child number 1
-------------------------------------
select sql_id,sql_text from v$sql where sql_id ='a' or sql_id='b'
Plan hash value: 903671040
----------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)|
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)|
|* 1 | FIXED TABLE FULL| X$KGLCURSOR_CHILD | 1 | 536 | 0 (0)|
----------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$AF73C875 / "X$KGLCURSOR_CHILD"@"SEL$5"
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter((INTERNAL_FUNCTION("KGLOBT03") AND
INTERNAL_FUNCTION("CON_ID") AND "INST_ID"=USERENV('INSTANCE')))
--//如果采用or访问无法使用索引。你可以测试设置or_expand无效。
--//select sql_id,sql_text from v$sql where sql_id in ('a' , 'b' );类似，执行计划不再贴出。
--//仅仅采用use_concat提示有效。
SCOTT@book01p> select /*+ use_concat(@"SEL$AF73C875" 8 OR_PREDICATES(1)) */ sql_id,sql_text from v$sql where sql_id ='a' or sql_id='b';
no rows selected
SCOTT@book01p> @ dpca
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 219ch1ww2nds2, child number 1
-------------------------------------
select /*+ use_concat(@"SEL$AF73C875" 8 OR_PREDICATES(1)) */
sql_id,sql_text from v$sql where sql_id ='a' or sql_id='b'
Plan hash value: 1674555743
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time |
---------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 1 (100)| 0 |00:00:00.01 |
| 1 | CONCATENATION | | 1 | | | | 0 |00:00:00.01 |
|* 2 | FIXED TABLE FIXED INDEX| X$KGLCURSOR_CHILD (ind:2) | 1 | 1 | 536 | 0 (0)| 0 |00:00:00.01 |
|* 3 | FIXED TABLE FIXED INDEX| X$KGLCURSOR_CHILD (ind:2) | 1 | 1 | 536 | 0 (0)| 0 |00:00:00.01 |
---------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$AF73C875
2 - SEL$AF73C875_1 / "X$KGLCURSOR_CHILD"@"SEL$5"
3 - SEL$AF73C875_2 / "X$KGLCURSOR_CHILD"@"SEL$AF73C875_2"
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("KGLOBT03"='b' AND ("CON_ID"=0 OR "CON_ID"=3) AND "INST_ID"=USERENV('INSTANCE')))
3 - filter(("KGLOBT03"='a' AND ("CON_ID"=0 OR "CON_ID"=3) AND "INST_ID"=USERENV('INSTANCE')))
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
1 - SEL$AF73C875
- use_concat(@"SEL$AF73C875" 8 OR_PREDICATES(1))
3.类似的情况也出现在其他试图的访问上，比如v$session试图。
SCOTT@book01p> select sid,serial#, substr(program,1,7),action from v$session where sid = userenv('sid');
SID SERIAL# SUBSTR_PROGRAM ACTION
---------- ---------- -------------- ----------------------------------------------------------------
270 28901 sqlplus
SCOTT@book01p> @dpca
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 378chs807f9pk, child number 0
-------------------------------------
select sid,serial#, substr(program,1,7),action from v$session where sid
= userenv('sid')
Plan hash value: 2422122865
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 1 (100)| 1 |00:00:00.01 |
| 1 | MERGE JOIN CARTESIAN | | 1 | 1 | 39 | 0 (0)| 1 |00:00:00.01 |
| 2 | NESTED LOOPS | | 1 | 1 | 12 | 0 (0)| 1 |00:00:00.01 |
|* 3 | FIXED TABLE FULL | X$KSLWT | 1 | 1 | 8 | 0 (0)| 1 |00:00:00.01 |
|* 4 | FIXED TABLE FIXED INDEX| X$KSLED (ind:2) | 1 | 1 | 4 | 0 (0)| 1 |00:00:00.01 |
| 5 | BUFFER SORT | | 1 | 1 | 27 | 0 (0)| 1 |00:00:00.01 |
|* 6 | FIXED TABLE FULL | X$KSUSE | 1 | 1 | 27 | 0 (0)| 1 |00:00:00.01 |
------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$AF73C875
3 - SEL$AF73C875 / "W"@"SEL$5"
4 - SEL$AF73C875 / "E"@"SEL$5"
6 - SEL$AF73C875 / "S"@"SEL$5"
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 BITAND("S"."KSUSEFLG",1)<>0 AND BITAND("S"."KSSPAFLG",1)<>0
AND INTERNAL_FUNCTION("S"."CON_ID") AND "S"."INST_ID"=USERENV('INSTANCE')))
--//id=3，filter("W"."KSLWTSID"=USERENV('SID'))。
SYS@book> @ xind X\$KSLWT
IF $ OCCUR IN MIDDLE USING \$ !! ,for example:
@ xind x\$table_name
@ xind x\$kglob$|x\$kgldp$
DERIVED_TABLES TABLE_NAME INDEX_NUMBER COLUMN_NAME COLUMN_POSITION CON_ID
------------------------------ ------------------------------ ------------ ------------------------------ --------------- ----------
X$KSLWT 1 KSLWTSID 0 0
--//KSLWTSID字段是有索引的，oracle并不把userenv('sid')认为是绑定变量。
--//如果分开2段，采用绑定变量带入。
SCOTT@book01p> variable my_sid number
SCOTT@book01p> exec :my_sid := userenv('sid');
PL/SQL procedure successfully completed.
SCOTT@book01p> select sid,serial#, substr(program,1,7),action from v$session where sid = :my_sid;
SID SERIAL# SUBSTR(PROGRAM ACTION
---------- ---------- -------------- ----------------------------------------------------------------
270 28901 sqlplus
SCOTT@book01p> @ dpca
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID fu67uwrft05dd, child number 0
-------------------------------------
select sid,serial#, substr(program,1,7),action from v$session where sid
= :my_sid
Plan hash value: 1627146547
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 1 (100)| 1 |00:00:00.01 |
| 1 | MERGE JOIN CARTESIAN | | 1 | 1 | 39 | 0 (0)| 1 |00:00:00.01 |
| 2 | NESTED LOOPS | | 1 | 1 | 12 | 0 (0)| 1 |00:00:00.01 |
|* 3 | FIXED TABLE FIXED INDEX| X$KSLWT (ind:1) | 1 | 1 | 8 | 0 (0)| 1 |00:00:00.01 |
|* 4 | FIXED TABLE FIXED INDEX| X$KSLED (ind:2) | 1 | 1 | 4 | 0 (0)| 1 |00:00:00.01 |
| 5 | BUFFER SORT | | 1 | 1 | 27 | 0 (0)| 1 |00:00:00.01 |
|* 6 | FIXED TABLE FIXED INDEX| X$KSUSE (ind:1) | 1 | 1 | 27 | 0 (0)| 1 |00:00:00.01 |
------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$AF73C875
3 - SEL$AF73C875 / "W"@"SEL$5"
4 - SEL$AF73C875 / "E"@"SEL$5"
6 - SEL$AF73C875 / "S"@"SEL$5"
Peeked Binds (identified by position):
--------------------------------------
1 - (NUMBER): 270
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("W"."KSLWTSID"=:MY_SID)
4 - filter("W"."KSLWTEVT"="E"."INDX")
6 - filter(("S"."INDX"=:MY_SID AND BITAND("S"."KSUSEFLG",1)<>0 AND BITAND("S"."KSSPAFLG",1)<>0 AND
INTERNAL_FUNCTION("S"."CON_ID") AND "S"."INST_ID"=USERENV('INSTANCE')))
40 rows selected.
--//可以发现就可以采用索引。
--//实际上琐碎细节问题，平时自己维护临时写的查询脚本可以不必这么在意，但是如果作为后台维护脚本经常调用执行就应该好好考虑
--//这些细节问题，规避这些因素。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-3092522/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261008]验证Shared Memory Segments key以及Semaphore Arrays Key(11g).txt
- [20261008]gdb引入结构体是否可行.txt
- [20261008]bpftrace定义结构体例子.txt
- [20261007]关于oracle sskgph2函数.txt
- [20261007]oracle Semaphore Arrays Key值的冲突.txt
- [20261004]oracle sskgph2函数等价c以及python脚本.txt
- [20261004]bpftrace查看oracle函数调用堆栈问题.txt
- [20261002]验证sskgphpjw函数参与运算的字符串是否存在规律.txt
- [20261002]oracle Semaphore Arrays Key值的计算2.txt
- [20261001]oracle Semaphore Arrays Key值的计算.txt
