[20250729]关于访问系统视图v$sqlXXXX的问题思考.txt

[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. --//可以发现就可以采用索引。 --//实际上琐碎细节问题，平时自己维护临时写的查询脚本可以不必这么在意，但是如果作为后台维护脚本经常调用执行就应该好好考虑 --//这些细节问题，规避这些因素。