[20211220]关于标量子查询问题.txt
--//上个星期发现一条翻页查询语句,感觉很奇怪出现在程序中.在看执行计划过程中,遇到标量子查询的starts很高的情况.
--//自己展开做一个分析,顺便说一下优化这条语句毫无意义,学习与了解并优化标量子查询倒是可以作为案例.
1.环境：
xxxxx> @ 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
2.问题分析:
--//注语句很长，我仅仅显示where条件部分,原始代码很乱,而且我很佩服写代码的人很长的代码就一行没带换行的。我做了一些格式化
--//处理，里面还有7处标量子查询。
--//sql_id=367u7411u9krd.
SELECT *
FROM (SELECT ROWNUM AS NO
,A.ZYH WATENUMBER
,B.MZHM BRID
...
, (SELECT YGXM
FROM GY_YGDM
WHERE YGDM = A.KZYS)
DOCTNAME
...
, (SELECT DMMC
FROM GY_XTPZ
WHERE DMLB = :"SYS_B_13"
AND PZBH = A.YZZT
AND DMSB <> :"SYS_B_14")
THESTATE
...
, :"SYS_B_21" RESERVE3
FROM EMR_YZB A, ZY_BRRY B
WHERE A.ZYH = B.ZYH
AND B.CYPB IN ( :"SYS_B_22", :"SYS_B_23")
AND ROWNUM < :"SYS_B_24")
WHERE NO >= :"SYS_B_25";
--//很简单的翻页语句。不过已经翻到814页。
--//获得绑定变量值:
xxxxx> @ bind_cap 367u7411u9krd ''
SQL_ID CHILD_NUMBER WAS NAME POSITION MAX_LENGTH LAST_CAPTURED DATATYPE_STRING VALUE_STRING
------------- ------------ --- ---------- -------- ---------- ------------------- --------------- ------------
367u7411u9krd 0 YES :SYS_B_09 10 22 2021-12-17 02:00:35 NUMBER 301
YES :SYS_B_10 11 22 2021-12-17 02:00:35 NUMBER 0
YES :SYS_B_13 14 22 2021-12-17 02:00:35 NUMBER 302
YES :SYS_B_14 15 22 2021-12-17 02:00:35 NUMBER 0
YES :SYS_B_22 23 22 2021-12-17 02:00:35 NUMBER 0
YES :SYS_B_23 24 22 2021-12-17 02:00:35 NUMBER 1
YES :SYS_B_24 25 22 2021-12-17 02:00:35 NUMBER 814000
YES :SYS_B_25 26 22 2021-12-17 02:00:35 NUMBER 813000
8 rows selected.
--//执行计划如下:
Plan hash value: 2094450556
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 232K(100)| | 1000 |00:00:59.21 | 481K| 43608 | | | |
| 1 | TABLE ACCESS BY INDEX ROWID | GY_KSDM | 1076 | 1 | 21 | 2 (0)| 00:00:01 | 1076 |00:00:00.01 | 1365 | 0 | | | |
|* 2 | INDEX UNIQUE SCAN | PK_GY_KSDM | 1076 | 1 | | 1 (0)| 00:00:01 | 1076 |00:00:00.01 | 272 | 0 | 1025K| 1025K| |
| 3 | TABLE ACCESS BY INDEX ROWID | GY_YGDM | 40517 | 1 | 12 | 2 (0)| 00:00:01 | 40517 |00:00:00.22 | 82323 | 0 | | | |
|* 4 | INDEX UNIQUE SCAN | PK_GY_YGDM | 40517 | 1 | | 1 (0)| 00:00:01 | 40517 |00:00:00.13 | 39153 | 0 | 1025K| 1025K| |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|* 5 | TABLE ACCESS BY INDEX ROWID | GY_XTPZ | 6 | 1 | 19 | 2 (0)| 00:00:01 | 6 |00:00:00.01 | 9 | 0 | | | |
|* 6 | INDEX RANGE SCAN | IDX_GY_XTPZ_DMLB | 6 | 5 | | 1 (0)| 00:00:01 | 54 |00:00:00.01 | 8 | 0 | 1025K| 1025K| |
| 7 | TABLE ACCESS BY INDEX ROWID | ZY_YPYF | 1260 | 1 | 13 | 1 (0)| 00:00:01 | 1259 |00:00:00.01 | 1263 | 0 | | | |
|* 8 | INDEX UNIQUE SCAN | PK_ZY_YPYF | 1260 | 1 | | 0 (0)| | 1259 |00:00:00.01 | 4 | 0 | 1025K| 1025K| |
| 9 | TABLE ACCESS BY INDEX ROWID | GY_SYPC | 70 | 1 | 8 | 1 (0)| 00:00:01 | 69 |00:00:00.01 | 73 | 0 | | | |
|* 10 | INDEX UNIQUE SCAN | PK_GY_SYPC | 70 | 1 | | 0 (0)| | 69 |00:00:00.01 | 4 | 0 | 1025K| 1025K| |
| 11 | TABLE ACCESS BY INDEX ROWID | GY_SYPC | 70 | 1 | 13 | 1 (0)| 00:00:01 | 69 |00:00:00.01 | 73 | 0 | | | |
|* 12 | INDEX UNIQUE SCAN | PK_GY_SYPC | 70 | 1 | | 0 (0)| | 69 |00:00:00.01 | 4 | 0 | 1025K| 1025K| |
| 13 | TABLE ACCESS BY INDEX ROWID | GY_KSDM | 13985 | 1 | 21 | 2 (0)| 00:00:01 | 7789 |00:00:00.05 | 15464 | 0 | | | |
|* 14 | INDEX UNIQUE SCAN | PK_GY_KSDM | 13985 | 1 | | 1 (0)| 00:00:01 | 7789 |00:00:00.02 | 4425 | 0 | 1025K| 1025K| |
|* 15 | TABLE ACCESS BY INDEX ROWID | GY_XTPZ | 6 | 1 | 19 | 2 (0)| 00:00:01 | 6 |00:00:00.01 | 9 | 0 | | | |
|* 16 | INDEX RANGE SCAN | IDX_GY_XTPZ_DMLB | 6 | 5 | | 1 (0)| 00:00:01 | 42 |00:00:00.01 | 8 | 0 | 1025K| 1025K| |
|* 17 | VIEW | | 1 | 549K| 333M| 232K (1)| 00:46:30 | 1000 |00:00:59.21 | 481K| 43608 | | | |
|* 18 | COUNT STOPKEY | | 1 | | | | | 813K|00:00:57.50 | 381K| 43608 | | | |
| 19 | NESTED LOOPS | | 1 | 549K| 66M| 232K (1)| 00:46:30 | 813K|00:00:57.39 | 381K| 43608 | | | |
| 20 | NESTED LOOPS | | 1 | 549K| 66M| 232K (1)| 00:46:30 | 813K|00:00:00.85 | 20382 | 487 | | | |
| 21 | INLIST ITERATOR | | 1 | | | | | 5126 |00:00:00.01 | 2377 | 0 | | | |
| 22 | TABLE ACCESS BY INDEX ROWID| ZY_BRRY | 2 | 4932 | 83844 | 419 (0)| 00:00:06 | 5126 |00:00:00.01 | 2377 | 0 | | | |
|* 23 | INDEX RANGE SCAN | IDX_BRRY_CYPB | 2 | 4932 | | 18 (0)| 00:00:01 | 5126 |00:00:00.01 | 119 | 0 | 1025K| 1025K| |
|* 24 | INDEX RANGE SCAN | I_EMR_YZB_ZYH | 5126 | 111 | | 3 (0)| 00:00:01 | 813K|00:00:00.75 | 18005 | 487 | 1025K| 1025K| |
| 25 | TABLE ACCESS BY INDEX ROWID | EMR_YZB | 813K| 111 | 12099 | 48 (0)| 00:00:01 | 813K|00:00:56.28 | 360K| 43121 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--//看执行计划时,发现id=4的循环次数达到40517次,id=3的buffers数量达到82323,还有一个细节,最后buffers的数量实际上不算标量子
--//查询的buffers,甚至包括cost成本.我这里buffers已经481K,但是id=3的buffers达到8XK,明显没有计入.
--//开始有点不理解,不就是取1000行吗?循环次数怎么能达到这个高,仔细一想很容易理解,因为先取814000记录然后截取后面的1000条,
--//这样标量子查询的循环次数达到这么高就很正常了,因为存在大量的hash table冲突.
3.优化方案1:
--//如果不修改代码,增加hash table缓存的大小,就可以减少hash冲突.
SYS@192.168.99.46:1521/dyhis> @ hide _query_execution_cache_max_size
NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
------------------------------- --------------------------------- ------------- ------------- ------------ ----- ---------
_query_execution_cache_max_size max size of query execution cache TRUE 131072 131072 TRUE DEFERRED
--//我的测试标量子查询的返回的是字符串YGXM,数据类型定义为,我估计能保存1024,增加3倍看看.
--//131072 * 3 = 393216
xxxxx> @ calc 131072 * 3
DEC HEX
----------------------------------- --------------------
393216.000000 60000
xxxxx> alter session set "_query_execution_cache_max_size"=393216;
Session altered.
Plan hash value: 2094450556
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 232K(100)| | 1000 |00:00:57.29 | 417K| 44359 | | | |
| 1 | TABLE ACCESS BY INDEX ROWID | GY_KSDM | 367 | 1 | 21 | 2 (0)| 00:00:01 | 367 |00:00:00.01 | 578 | 0 | | | |
|* 2 | INDEX UNIQUE SCAN | PK_GY_KSDM | 367 | 1 | | 1 (0)| 00:00:01 | 367 |00:00:00.01 | 206 | 0 | 1025K| 1025K| |
| 3 | TABLE ACCESS BY INDEX ROWID | GY_YGDM | 15893 | 1 | 12 | 2 (0)| 00:00:01 | 15893 |00:00:00.09 | 30972 | 0 | | | |
|* 4 | INDEX UNIQUE SCAN | PK_GY_YGDM | 15893 | 1 | | 1 (0)| 00:00:01 | 15893 |00:00:00.06 | 14788 | 0 | 1025K| 1025K| |
|* 5 | TABLE ACCESS BY INDEX ROWID | GY_XTPZ | 6 | 1 | 19 | 2 (0)| 00:00:01 | 6 |00:00:00.01 | 9 | 0 | | | |
|* 6 | INDEX RANGE SCAN | IDX_GY_XTPZ_DMLB | 6 | 5 | | 1 (0)| 00:00:01 | 54 |00:00:00.01 | 8 | 0 | 1025K| 1025K| |
| 7 | TABLE ACCESS BY INDEX ROWID | ZY_YPYF | 1270 | 1 | 13 | 1 (0)| 00:00:01 | 1269 |00:00:00.01 | 1275 | 0 | | | |
|* 8 | INDEX UNIQUE SCAN | PK_ZY_YPYF | 1270 | 1 | | 0 (0)| | 1269 |00:00:00.01 | 6 | 0 | 1025K| 1025K| |
| 9 | TABLE ACCESS BY INDEX ROWID | GY_SYPC | 53 | 1 | 8 | 1 (0)| 00:00:01 | 52 |00:00:00.01 | 56 | 0 | | | |
|* 10 | INDEX UNIQUE SCAN | PK_GY_SYPC | 53 | 1 | | 0 (0)| | 52 |00:00:00.01 | 4 | 0 | 1025K| 1025K| |
| 11 | TABLE ACCESS BY INDEX ROWID | GY_SYPC | 73 | 1 | 13 | 1 (0)| 00:00:01 | 72 |00:00:00.01 | 76 | 0 | | | |
|* 12 | INDEX UNIQUE SCAN | PK_GY_SYPC | 73 | 1 | | 0 (0)| | 72 |00:00:00.01 | 4 | 0 | 1025K| 1025K| |
| 13 | TABLE ACCESS BY INDEX ROWID | GY_KSDM | 2255 | 1 | 21 | 2 (0)| 00:00:01 | 2196 |00:00:00.01 | 3651 | 0 | | | |
|* 14 | INDEX UNIQUE SCAN | PK_GY_KSDM | 2255 | 1 | | 1 (0)| 00:00:01 | 2196 |00:00:00.01 | 1406 | 0 | 1025K| 1025K| |
|* 15 | TABLE ACCESS BY INDEX ROWID | GY_XTPZ | 6 | 1 | 19 | 2 (0)| 00:00:01 | 6 |00:00:00.01 | 9 | 0 | | | |
|* 16 | INDEX RANGE SCAN | IDX_GY_XTPZ_DMLB | 6 | 5 | | 1 (0)| 00:00:01 | 42 |00:00:00.01 | 8 | 0 | 1025K| 1025K| |
|* 17 | VIEW | | 1 | 549K| 414M| 232K (1)| 00:46:30 | 1000 |00:00:57.29 | 417K| 44359 | | | |
|* 18 | COUNT STOPKEY | | 1 | | | | | 813K|00:00:55.67 | 380K| 44359 | | | |
| 19 | NESTED LOOPS | | 1 | 549K| 66M| 232K (1)| 00:46:30 | 813K|00:00:55.56 | 380K| 44359 | | | |
| 20 | NESTED LOOPS | | 1 | 549K| 66M| 232K (1)| 00:46:30 | 813K|00:00:00.95 | 20751 | 459 | | | |
| 21 | INLIST ITERATOR | | 1 | | | | | 5188 |00:00:00.01 | 2489 | 0 | | | |
| 22 | TABLE ACCESS BY INDEX ROWID| ZY_BRRY | 2 | 4932 | 83844 | 419 (0)| 00:00:06 | 5188 |00:00:00.01 | 2489 | 0 | | | |
|* 23 | INDEX RANGE SCAN | IDX_BRRY_CYPB | 2 | 4932 | | 18 (0)| 00:00:01 | 5188 |00:00:00.01 | 119 | 0 | 1025K| 1025K| |
|* 24 | INDEX RANGE SCAN | I_EMR_YZB_ZYH | 5188 | 111 | | 3 (0)| 00:00:01 | 813K|00:00:00.85 | 18262 | 459 | 1025K| 1025K| |
| 25 | TABLE ACCESS BY INDEX ROWID | EMR_YZB | 813K| 111 | 12099 | 48 (0)| 00:00:01 | 813K|00:00:54.36 | 360K| 43900 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--//可以发现id=4的循环次数减少到15893,还是很不理想。
4.优化方案2:
--//最容易想到的方法就是把标量子查询放在外层,这样取出1000条,然后在外层做标量子查询.修改如下测试看看.
SELECT a.*
, (SELECT YGXM FROM GY_YGDM WHERE YGDM = A.DOCTCODE) DOCTNAME
FROM (SELECT ROWNUM AS NO
,A.ZYH WATENUMBER
,B.MZHM BRID
,'00' SERIAL
,A.YZBXH DAID
,DECODE (YYSX, 00, '00', 00, '00', 00, '00', 00, '00')
THETYPE
,A.KZKS DEPTCODE
, (SELECT KSMC
FROM GY_KSDM
WHERE KSDM = A.KZKS)
DEPTNAME
,A.KZYS DOCTCODE
-- , (SELECT YGXM FROM GY_YGDM WHERE YGDM = A.KZYS) DOCTNAME
....
...
, :"SYS_B_21" RESERVE3
FROM EMR_YZB A, ZY_BRRY B
WHERE A.ZYH = B.ZYH
AND B.CYPB IN ( :"SYS_B_22", :"SYS_B_23")
AND ROWNUM < :"SYS_B_24") a
WHERE NO >= :"SYS_B_25";
Plan hash value: 2805972862
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 232K(100)| | 1000 |00:00:03.54 | 394K| | | |
| 1 | TABLE ACCESS BY INDEX ROWID | GY_KSDM | 930 | 1 | 21 | 2 (0)| 00:00:01 | 930 |00:00:00.01 | 1210 | | | |
|* 2 | INDEX UNIQUE SCAN | PK_GY_KSDM | 930 | 1 | | 1 (0)| 00:00:01 | 930 |00:00:00.01 | 263 | 1025K| 1025K| |
|* 3 | TABLE ACCESS BY INDEX ROWID | GY_XTPZ | 6 | 1 | 19 | 2 (0)| 00:00:01 | 6 |00:00:00.01 | 9 | | | |
|* 4 | INDEX RANGE SCAN | IDX_GY_XTPZ_DMLB | 6 | 5 | | 1 (0)| 00:00:01 | 54 |00:00:00.01 | 8 | 1025K| 1025K| |
| 5 | TABLE ACCESS BY INDEX ROWID | ZY_YPYF | 1256 | 1 | 13 | 1 (0)| 00:00:01 | 1255 |00:00:00.01 | 1259 | | | |
|* 6 | INDEX UNIQUE SCAN | PK_ZY_YPYF | 1256 | 1 | | 0 (0)| | 1255 |00:00:00.01 | 4 | 1025K| 1025K| |
| 7 | TABLE ACCESS BY INDEX ROWID | GY_SYPC | 70 | 1 | 8 | 1 (0)| 00:00:01 | 69 |00:00:00.01 | 73 | | | |
|* 8 | INDEX UNIQUE SCAN | PK_GY_SYPC | 70 | 1 | | 0 (0)| | 69 |00:00:00.01 | 4 | 1025K| 1025K| |
| 9 | TABLE ACCESS BY INDEX ROWID | GY_SYPC | 70 | 1 | 13 | 1 (0)| 00:00:01 | 69 |00:00:00.01 | 73 | | | |
|* 10 | INDEX UNIQUE SCAN | PK_GY_SYPC | 70 | 1 | | 0 (0)| | 69 |00:00:00.01 | 4 | 1025K| 1025K| |
| 11 | TABLE ACCESS BY INDEX ROWID | GY_KSDM | 18135 | 1 | 21 | 2 (0)| 00:00:01 | 5578 |00:00:00.05 | 11399 | | | |
|* 12 | INDEX UNIQUE SCAN | PK_GY_KSDM | 18135 | 1 | | 1 (0)| 00:00:01 | 5578 |00:00:00.03 | 4683 | 1025K| 1025K| |
|* 13 | TABLE ACCESS BY INDEX ROWID | GY_XTPZ | 6 | 1 | 19 | 2 (0)| 00:00:01 | 6 |00:00:00.01 | 9 | | | |
|* 14 | INDEX RANGE SCAN | IDX_GY_XTPZ_DMLB | 6 | 5 | | 1 (0)| 00:00:01 | 42 |00:00:00.01 | 8 | 1025K| 1025K| |
| 15 | TABLE ACCESS BY INDEX ROWID | GY_YGDM | 62 | 1 | 12 | 2 (0)| 00:00:01 | 62 |00:00:00.01 | 138 | | | |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|* 16 | INDEX UNIQUE SCAN | PK_GY_YGDM | 62 | 1 | | 1 (0)| 00:00:01 | 62 |00:00:00.01 | 73 | 1025K| 1025K| |
|* 17 | VIEW | | 1 | 549K| 405M| 232K (1)| 00:46:30 | 1000 |00:00:03.54 | 394K| | | |
|* 18 | COUNT STOPKEY | | 1 | | | | | 813K|00:00:02.10 | 380K| | | |
| 19 | NESTED LOOPS | | 1 | 549K| 66M| 232K (1)| 00:46:30 | 813K|00:00:01.99 | 380K| | | |
| 20 | NESTED LOOPS | | 1 | 549K| 66M| 232K (1)| 00:46:30 | 813K|00:00:00.35 | 20499 | | | |
| 21 | INLIST ITERATOR | | 1 | | | | | 5140 |00:00:00.01 | 2435 | | | |
| 22 | TABLE ACCESS BY INDEX ROWID| ZY_BRRY | 2 | 4932 | 83844 | 419 (0)| 00:00:06 | 5140 |00:00:00.01 | 2435 | | | |
|* 23 | INDEX RANGE SCAN | IDX_BRRY_CYPB | 2 | 4932 | | 18 (0)| 00:00:01 | 5140 |00:00:00.01 | 116 | 1025K| 1025K| |
|* 24 | INDEX RANGE SCAN | I_EMR_YZB_ZYH | 5140 | 111 | | 3 (0)| 00:00:01 | 813K|00:00:00.24 | 18064 | 1025K| 1025K| |
| 25 | TABLE ACCESS BY INDEX ROWID | EMR_YZB | 813K| 111 | 12099 | 48 (0)| 00:00:01 | 813K|00:00:01.40 | 359K| | | |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--//如果我把id=12的出现也遇到外层。
SELECT a.*
, (SELECT YGXM FROM GY_YGDM WHERE YGDM = A.DOCTCODE) DOCTNAME
, (SELECT KSMC FROM GY_KSDM WHERE KSDM = A.DEPTCODE) DEPTNAME
, (SELECT KSMC FROM GY_KSDM WHERE KSDM = A.EXECDEPTCODE) EXECDEPTNAME
FROM (SELECT ROWNUM AS NO,
..
...
, :"SYS_B_21" RESERVE3
FROM EMR_YZB A, ZY_BRRY B
WHERE A.ZYH = B.ZYH
AND B.CYPB IN ( :"SYS_B_22", :"SYS_B_23")
AND ROWNUM < :"SYS_B_24") a
WHERE NO >= :"SYS_B_25";
Plan hash value: 2236731721
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 232K(100)| | 1000 |00:00:02.88 | 381K| | | |
|* 1 | TABLE ACCESS BY INDEX ROWID | GY_XTPZ | 6 | 1 | 19 | 2 (0)| 00:00:01 | 6 |00:00:00.01 | 9 | | | |
|* 2 | INDEX RANGE SCAN | IDX_GY_XTPZ_DMLB | 6 | 5 | | 1 (0)| 00:00:01 | 54 |00:00:00.01 | 8 | 1025K| 1025K| |
| 3 | TABLE ACCESS BY INDEX ROWID | ZY_YPYF | 1254 | 1 | 13 | 1 (0)| 00:00:01 | 1253 |00:00:00.01 | 1257 | | | |
|* 4 | INDEX UNIQUE SCAN | PK_ZY_YPYF | 1254 | 1 | | 0 (0)| | 1253 |00:00:00.01 | 4 | 1025K| 1025K| |
| 5 | TABLE ACCESS BY INDEX ROWID | GY_SYPC | 70 | 1 | 8 | 1 (0)| 00:00:01 | 69 |00:00:00.01 | 73 | | | |
|* 6 | INDEX UNIQUE SCAN | PK_GY_SYPC | 70 | 1 | | 0 (0)| | 69 |00:00:00.01 | 4 | 1025K| 1025K| |
| 7 | TABLE ACCESS BY INDEX ROWID | GY_SYPC | 70 | 1 | 13 | 1 (0)| 00:00:01 | 69 |00:00:00.01 | 73 | | | |
|* 8 | INDEX UNIQUE SCAN | PK_GY_SYPC | 70 | 1 | | 0 (0)| | 69 |00:00:00.01 | 4 | 1025K| 1025K| |
|* 9 | TABLE ACCESS BY INDEX ROWID | GY_XTPZ | 6 | 1 | 19 | 2 (0)| 00:00:01 | 6 |00:00:00.01 | 9 | | | |
|* 10 | INDEX RANGE SCAN | IDX_GY_XTPZ_DMLB | 6 | 5 | | 1 (0)| 00:00:01 | 42 |00:00:00.01 | 8 | 1025K| 1025K| |
| 11 | TABLE ACCESS BY INDEX ROWID | GY_YGDM | 55 | 1 | 12 | 2 (0)| 00:00:01 | 55 |00:00:00.01 | 122 | | | |
|* 12 | INDEX UNIQUE SCAN | PK_GY_YGDM | 55 | 1 | | 1 (0)| 00:00:01 | 55 |00:00:00.01 | 66 | 1025K| 1025K| |
| 13 | TABLE ACCESS BY INDEX ROWID | GY_KSDM | 10 | 1 | 21 | 2 (0)| 00:00:01 | 10 |00:00:00.01 | 32 | | | |
|* 14 | INDEX UNIQUE SCAN | PK_GY_KSDM | 10 | 1 | | 1 (0)| 00:00:01 | 10 |00:00:00.01 | 19 | 1025K| 1025K| |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| 15 | TABLE ACCESS BY INDEX ROWID | GY_KSDM | 40 | 1 | 21 | 2 (0)| 00:00:01 | 31 |00:00:00.01 | 82 | | | |
|* 16 | INDEX UNIQUE SCAN | PK_GY_KSDM | 40 | 1 | | 1 (0)| 00:00:01 | 31 |00:00:00.01 | 45 | 1025K| 1025K| |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|* 17 | VIEW | | 1 | 549K| 377M| 232K (1)| 00:46:30 | 1000 |00:00:02.88 | 381K| | | |
|* 18 | COUNT STOPKEY | | 1 | | | | | 813K|00:00:01.88 | 380K| | | |
| 19 | NESTED LOOPS | | 1 | 549K| 66M| 232K (1)| 00:46:30 | 813K|00:00:01.79 | 380K| | | |
| 20 | NESTED LOOPS | | 1 | 549K| 66M| 232K (1)| 00:46:30 | 813K|00:00:00.29 | 20504 | | | |
| 21 | INLIST ITERATOR | | 1 | | | | | 5140 |00:00:00.01 | 2433 | | | |
| 22 | TABLE ACCESS BY INDEX ROWID| ZY_BRRY | 2 | 4932 | 83844 | 419 (0)| 00:00:06 | 5140 |00:00:00.01 | 2433 | | | |
|* 23 | INDEX RANGE SCAN | IDX_BRRY_CYPB | 2 | 4932 | | 18 (0)| 00:00:01 | 5140 |00:00:00.01 | 115 | 1025K| 1025K| |
|* 24 | INDEX RANGE SCAN | I_EMR_YZB_ZYH | 5140 | 111 | | 3 (0)| 00:00:01 | 813K|00:00:00.20 | 18071 | 1025K| 1025K| |
| 25 | TABLE ACCESS BY INDEX ROWID | EMR_YZB | 813K| 111 | 12099 | 48 (0)| 00:00:01 | 813K|00:00:01.28 | 359K| | | |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--//这样标量子查询的starts减少不少。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-2848592/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [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
