[20250718]WINDOW SORT PUSHED RANK Performance Regression in Oracle 21c.txt
[20250718]WINDOW SORT PUSHED RANK Performance Regression in Oracle 21c.txt
https://nenadnoveljic.com/blog/window-sort-pushed-rank-performance-regression-in-oracle-21c/
--//重复测试看看。
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.测试例子建立：
create table t1 (n1 number, c1 varchar2(10)) ;
create table t2 (n1 number, c1 varchar2(10), n2 number) ;
insert into t1 select level, null from dual connect by level <= 5*1e5 ;
insert into t2 select 5*1e5 + level, 'X', 1 from dual connect by level <= 4*1e5 ;
commit ;
exec dbms_stats.gather_table_stats(null, 'T1');
exec dbms_stats.gather_table_stats(null, 'T2');
$ cat g8.txt
merge /*+ gather_plan_statistics monitor */ --nenad
into t1 using
( select n1,c1 from
( select n1,c1,
row_number() over (partition by n1 order by n2 desc) rn
from t2
) where rn = 1
) v1
on ( v1.n1 = t1.n1 )
when matched then
update set t1.c1 = v1.c1 ;
3.测试：
SCOTT@book01p> set timing on
SCOTT@book01p> @ g8.txt
0 rows merged.
Elapsed: 00:02:32.95
--//差不多150秒。
SCOTT@book01p> @ dpc '' outline ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 2r8rr3pht5cvn, child number 0
-------------------------------------
merge /*+ gather_plan_statistics monitor */ --nenad into t1 using (
select n1,c1 from ( select n1,c1, row_number() over (partition
by n1 order by n2 desc) rn from t2 ) where rn = 1 ) v1 on ( v1.n1 =
t1.n1 ) when matched then update set t1.c1 = v1.c1
Plan hash value: 3917951292
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 1 | | | | 3356 (100)| | 0 |00:02:32.95 | 1683 | | | |
| 1 | MERGE | T1 | 1 | | | | | | 0 |00:02:32.95 | 1683 | | | |
| 2 | VIEW | | 1 | | | | | | 0 |00:02:32.95 | 1683 | | | |
|* 3 | HASH JOIN | | 1 | 1 | 38 | 8304K| 3356 (2)| 00:00:01 | 0 |00:02:32.95 | 1683 | 33M| 8026K| 32M (0)|
| 4 | TABLE ACCESS FULL | T1 | 1 | 500K| 2441K| | 243 (3)| 00:00:01 | 500K|00:00:00.02 | 805 | | | |
|* 5 | VIEW | | 1 | 400K| 12M| | 1850 (2)| 00:00:01 | 400K|00:02:33.14 | 878 | | | |
|* 6 | WINDOW SORT PUSHED RANK| | 1 | 400K| 3906K| 7856K| 1850 (2)| 00:00:01 | 400K|00:02:32.93 | 878 | 25M| 1830K| 22M (0)|
| 7 | TABLE ACCESS FULL | T2 | 1 | 400K| 3906K| | 243 (3)| 00:00:01 | 400K|00:00:00.01 | 878 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - MRG$1
3 - SEL$F5BB74E1
4 - SEL$F5BB74E1 / "T1"@"SEL$1"
5 - SEL$3 / "from$_subquery$_007"@"SEL$2"
6 - SEL$3
7 - SEL$3 / "T2"@"SEL$3"
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('21.1.0')
DB_VERSION('21.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$3")
OUTLINE_LEAF(@"SEL$F5BB74E1")
MERGE(@"SEL$2" >"SEL$1")
OUTLINE_LEAF(@"SEL$5")
OUTLINE_LEAF(@"SEL$4")
OUTLINE_LEAF(@"MRG$1")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
NO_ACCESS(@"MRG$1" "from$_subquery$_009"@"MRG$1")
NO_ACCESS(@"MRG$1" "V1"@"MRG$1")
FULL(@"MRG$1" "T1"@"MRG$1")
LEADING(@"MRG$1" "from$_subquery$_009"@"MRG$1" "V1"@"MRG$1" "T1"@"MRG$1")
USE_MERGE_CARTESIAN(@"MRG$1" "V1"@"MRG$1")
USE_MERGE_CARTESIAN(@"MRG$1" "T1"@"MRG$1")
NO_ACCESS(@"SEL$F5BB74E1" "from$_subquery$_007"@"SEL$2")
FULL(@"SEL$F5BB74E1" "T1"@"SEL$1")
LEADING(@"SEL$F5BB74E1" "from$_subquery$_007"@"SEL$2" "T1"@"SEL$1")
USE_HASH(@"SEL$F5BB74E1" "T1"@"SEL$1")
SWAP_JOIN_INPUTS(@"SEL$F5BB74E1" "T1"@"SEL$1")
NO_ACCESS(@"SEL$4" "from$_subquery$_004"@"SEL$4")
FULL(@"SEL$5" "T2"@"SEL$5")
FULL(@"SEL$3" "T2"@"SEL$3")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("N1"="T1"."N1")
5 - filter("RN"=1)
6 - filter(ROW_NUMBER() OVER ( PARTITION BY "N1" ORDER BY INTERNAL_FUNCTION("N2") DESC )<=1)
73 rows selected.
--//时间在id=6上。
SCOTT@book01p> @ dpccpu 2r8rr3pht5cvn 0
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 2r8rr3pht5cvn, child number 0
-------------------------------------
merge /*+ gather_plan_statistics monitor */ --nenad into t1 using (
select n1,c1 from ( select n1,c1, row_number() over (partition
by n1 order by n2 desc) rn from t2 ) where rn = 1 ) v1 on ( v1.n1 =
t1.n1 ) when matched then update set t1.c1 = v1.c1
Plan hash value: 3917951292
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | %ASH SAMPLES
--------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | | | | 3356 (100)| |
| 1 | MERGE | T1 | | | | | |
| 2 | VIEW | | | | | | |
|* 3 | HASH JOIN | | 1 | 38 | 8304K| 3356 (2)| 00:00:01 | 1% ( 1% CPU 0% I/O)
| 4 | TABLE ACCESS FULL | T1 | 500K| 2441K| | 243 (3)| 00:00:01 |
|* 5 | VIEW | | 400K| 12M| | 1850 (2)| 00:00:01 |
|* 6 | WINDOW SORT PUSHED RANK| | 400K| 3906K| 7856K| 1850 (2)| 00:00:01 | 99% ( 99% CPU 0% I/O)
| 7 | TABLE ACCESS FULL | T2 | 400K| 3906K| | 243 (3)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("N1"="T1"."N1")
5 - filter("RN"=1)
6 - filter(ROW_NUMBER() OVER ( PARTITION BY "N1" ORDER BY
INTERNAL_FUNCTION("N2") DESC )<=1)
--//99%的CPU资源在id=6。
--//作者通过bpftrace定位smbgetqbPart频繁调用800001次。而19c调用0.
--//通过pstack观察也可以同样定位：
$ seq 10000 | xargs -IQ pstack 6849 >| gg.txt
--//注：我的测试环境密集执行pstack，按ctrl+c中断后，相关进程很容易出现挂起情况，不知道原因。
--//sql执行结束按ctrl+c中断pstack的执行。
awk '/#0/{print $4}' gg.txt | sort |uniq -c | sort -nr
226 smbgetqbPart
3 __read_nocancel
1 rwosmf
1 qerstUpdateStats
1 expeal
--//大部分调用函数smbgetqbPart。
$ pstack 6849
#0 0x000000000e250f7d in smbgetqbPart ()
#1 0x000000001500e027 in sorgetqbf ()
#2 0x0000000004d9b504 in qerwnFetch ()
#3 0x000000000a261506 in qerstFetch ()
#4 0x0000000004cd1903 in qervwFetch ()
#5 0x000000000a261506 in qerstFetch ()
#6 0x0000000014dfbda7 in rwsfcd ()
#7 0x000000000a261506 in qerstFetch ()
#8 0x000000001516b8a5 in qerhnFetch ()
#9 0x000000000a261506 in qerstFetch ()
#10 0x0000000004cd1903 in qervwFetch ()
#11 0x000000000a261506 in qerstFetch ()
#12 0x0000000014dfbda7 in rwsfcd ()
#13 0x000000000a261506 in qerstFetch ()
#14 0x0000000004dd99dd in qerusFetch ()
#15 0x000000000a261506 in qerstFetch ()
#16 0x0000000014ffbf49 in updaul ()
#17 0x0000000014ff8f90 in updThreePhaseExe ()
#18 0x00000000041ab3e0 in upsexe ()
#19 0x0000000014ef6ab5 in opiexe ()
#20 0x0000000014f6cf9d in kpoal8 ()
#21 0x0000000014eefdf8 in opiodr ()
#22 0x0000000015294359 in ttcpip ()
#23 0x00000000030025c2 in opitsk ()
#24 0x0000000003007920 in opiino ()
#25 0x0000000014eefdf8 in opiodr ()
#26 0x0000000002ffe2ab in opidrv ()
#27 0x0000000003e8bd15 in sou2o ()
#28 0x0000000000e91ae0 in opimai_real ()
#29 0x0000000003e9915c in ssthrdmain ()
#30 0x0000000000e91924 in main ()
SCOTT@book01p> @ fix "PUSHED RANK"
SCOTT@book01p> @pr
==============================
SESSION_ID : 147
BUGNO : 30822446
VALUE : 1
SQL_FEATURE : QKSFM_EXECUTION_30822446
DESCRIPTION : enable window pushed rank optimization with partition by keys
OPTIMIZER_FEATURE_ENABLE : 21.1.0
EVENT : 0
IS_DEFAULT : 1
CON_ID : 3
PL/SQL procedure successfully completed.
SCOTT@book01p> alter session set "_FIX_CONTROL"='30822446:OFF' ;
Session altered.
SCOTT@book01p> @ g8.txt
0 rows merged.
Elapsed: 00:00:00.60
SCOTT@book01p> @ dpc '' '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 2r8rr3pht5cvn, child number 1
-------------------------------------
merge /*+ gather_plan_statistics monitor */ --nenad into t1 using (
select n1,c1 from ( select n1,c1, row_number() over (partition
by n1 order by n2 desc) rn from t2 ) where rn = 1 ) v1 on ( v1.n1 =
t1.n1 ) when matched then update set t1.c1 = v1.c1
Plan hash value: 3917951292
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 1 | | | | 3356 (100)| | 0 |00:00:00.56 | 1683 | | | |
| 1 | MERGE | T1 | 1 | | | | | | 0 |00:00:00.56 | 1683 | | | |
| 2 | VIEW | | 1 | | | | | | 0 |00:00:00.56 | 1683 | | | |
|* 3 | HASH JOIN | | 1 | 1 | 38 | 8304K| 3356 (2)| 00:00:01 | 0 |00:00:00.56 | 1683 | 33M| 8026K| 32M (0)|
| 4 | TABLE ACCESS FULL | T1 | 1 | 500K| 2441K| | 243 (3)| 00:00:01 | 500K|00:00:00.02 | 805 | | | |
|* 5 | VIEW | | 1 | 400K| 12M| | 1850 (2)| 00:00:01 | 400K|00:00:00.56 | 878 | | | |
|* 6 | WINDOW SORT PUSHED RANK| | 1 | 400K| 3906K| 7856K| 1850 (2)| 00:00:01 | 400K|00:00:00.46 | 878 | 25M| 1830K| 22M (0)|
| 7 | TABLE ACCESS FULL | T2 | 1 | 400K| 3906K| | 243 (3)| 00:00:01 | 400K|00:00:00.01 | 878 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - MRG$1
3 - SEL$F5BB74E1
4 - SEL$F5BB74E1 / "T1"@"SEL$1"
5 - SEL$3 / "from$_subquery$_007"@"SEL$2"
6 - SEL$3
7 - SEL$3 / "T2"@"SEL$3"
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("N1"="T1"."N1")
5 - filter("RN"=1)
6 - filter(ROW_NUMBER() OVER ( PARTITION BY "N1" ORDER BY INTERNAL_FUNCTION("N2") DESC )<=1)
--//逻辑读一样。执行计划一样。
4.附上测试使用dpccpu.sql脚本：
$ cat dpccpu.sql
with
"sql" as (select SQL_ID,CHILD_NUMBER,PLAN_HASH_VALUE,'' FORMAT from v$sql where sql_id='&1'),
"ash" as (
select sql_id,sql_plan_line_id,child_number,sql_plan_hash_value
,round(count(*)/"samples",2) load
,nvl(round(sum(case when session_state='ON CPU' then 1 end)/"samples",2),0) load_cpu
,nvl(round(sum(case when session_state='WAITING' and wait_class='User I/O' then 1 end)/"samples",2),0) load_io
from "sql" join
(
select sql_id,sql_plan_line_id,sql_child_number child_number,sql_plan_hash_value,session_state,wait_class,count(*) over (partition by sql_id,sql_plan_hash_value) "samples"
FROM V$ACTIVE_SESSION_HISTORY
) using(sql_id,child_number) group by sql_id,sql_plan_line_id,child_number,sql_plan_hash_value,"samples"
),
"plan" as (
-- get dbms_xplan result
select
sql_id,child_number,n,plan_table_output
-- get plan line id from plan_table output
,case when regexp_like (plan_table_output,'^[|][*]? *([0-9]+) *[|].*[|]$') then
regexp_replace(plan_table_output,'^[|][*]? *([0-9]+) *[|].*[|]$','\1')
END SQL_PLAN_LINE_ID
from (select rownum n,plan_table_output,SQL_ID,CHILD_NUMBER from "sql", table(dbms_xplan.display_cursor("sql".SQL_ID,"sql".CHILD_NUMBER,"sql".FORMAT)))
)
select PLAN_TABLE_OUTPUT||CASE
-- ASH load to be displayed
WHEN LOAD >0 THEN TO_CHAR(100*LOAD,'999')||'% (' || TO_CHAR(100*LOAD_CPU,'999')||'% CPU'|| TO_CHAR(100*LOAD_IO,'999')||'% I/O)'
-- header
WHEN REGEXP_LIKE (PLAN_TABLE_OUTPUT,'^[|] *Id *[|]') THEN ' %ASH SAMPLES'
end plan_table_output
from "plan" left outer join "ash" using(sql_id,child_number,sql_plan_line_id) order by sql_id,child_number,n;
https://nenadnoveljic.com/blog/window-sort-pushed-rank-performance-regression-in-oracle-21c/
--//重复测试看看。
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.测试例子建立：
create table t1 (n1 number, c1 varchar2(10)) ;
create table t2 (n1 number, c1 varchar2(10), n2 number) ;
insert into t1 select level, null from dual connect by level <= 5*1e5 ;
insert into t2 select 5*1e5 + level, 'X', 1 from dual connect by level <= 4*1e5 ;
commit ;
exec dbms_stats.gather_table_stats(null, 'T1');
exec dbms_stats.gather_table_stats(null, 'T2');
$ cat g8.txt
merge /*+ gather_plan_statistics monitor */ --nenad
into t1 using
( select n1,c1 from
( select n1,c1,
row_number() over (partition by n1 order by n2 desc) rn
from t2
) where rn = 1
) v1
on ( v1.n1 = t1.n1 )
when matched then
update set t1.c1 = v1.c1 ;
3.测试：
SCOTT@book01p> set timing on
SCOTT@book01p> @ g8.txt
0 rows merged.
Elapsed: 00:02:32.95
--//差不多150秒。
SCOTT@book01p> @ dpc '' outline ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 2r8rr3pht5cvn, child number 0
-------------------------------------
merge /*+ gather_plan_statistics monitor */ --nenad into t1 using (
select n1,c1 from ( select n1,c1, row_number() over (partition
by n1 order by n2 desc) rn from t2 ) where rn = 1 ) v1 on ( v1.n1 =
t1.n1 ) when matched then update set t1.c1 = v1.c1
Plan hash value: 3917951292
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 1 | | | | 3356 (100)| | 0 |00:02:32.95 | 1683 | | | |
| 1 | MERGE | T1 | 1 | | | | | | 0 |00:02:32.95 | 1683 | | | |
| 2 | VIEW | | 1 | | | | | | 0 |00:02:32.95 | 1683 | | | |
|* 3 | HASH JOIN | | 1 | 1 | 38 | 8304K| 3356 (2)| 00:00:01 | 0 |00:02:32.95 | 1683 | 33M| 8026K| 32M (0)|
| 4 | TABLE ACCESS FULL | T1 | 1 | 500K| 2441K| | 243 (3)| 00:00:01 | 500K|00:00:00.02 | 805 | | | |
|* 5 | VIEW | | 1 | 400K| 12M| | 1850 (2)| 00:00:01 | 400K|00:02:33.14 | 878 | | | |
|* 6 | WINDOW SORT PUSHED RANK| | 1 | 400K| 3906K| 7856K| 1850 (2)| 00:00:01 | 400K|00:02:32.93 | 878 | 25M| 1830K| 22M (0)|
| 7 | TABLE ACCESS FULL | T2 | 1 | 400K| 3906K| | 243 (3)| 00:00:01 | 400K|00:00:00.01 | 878 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - MRG$1
3 - SEL$F5BB74E1
4 - SEL$F5BB74E1 / "T1"@"SEL$1"
5 - SEL$3 / "from$_subquery$_007"@"SEL$2"
6 - SEL$3
7 - SEL$3 / "T2"@"SEL$3"
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('21.1.0')
DB_VERSION('21.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$3")
OUTLINE_LEAF(@"SEL$F5BB74E1")
MERGE(@"SEL$2" >"SEL$1")
OUTLINE_LEAF(@"SEL$5")
OUTLINE_LEAF(@"SEL$4")
OUTLINE_LEAF(@"MRG$1")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
NO_ACCESS(@"MRG$1" "from$_subquery$_009"@"MRG$1")
NO_ACCESS(@"MRG$1" "V1"@"MRG$1")
FULL(@"MRG$1" "T1"@"MRG$1")
LEADING(@"MRG$1" "from$_subquery$_009"@"MRG$1" "V1"@"MRG$1" "T1"@"MRG$1")
USE_MERGE_CARTESIAN(@"MRG$1" "V1"@"MRG$1")
USE_MERGE_CARTESIAN(@"MRG$1" "T1"@"MRG$1")
NO_ACCESS(@"SEL$F5BB74E1" "from$_subquery$_007"@"SEL$2")
FULL(@"SEL$F5BB74E1" "T1"@"SEL$1")
LEADING(@"SEL$F5BB74E1" "from$_subquery$_007"@"SEL$2" "T1"@"SEL$1")
USE_HASH(@"SEL$F5BB74E1" "T1"@"SEL$1")
SWAP_JOIN_INPUTS(@"SEL$F5BB74E1" "T1"@"SEL$1")
NO_ACCESS(@"SEL$4" "from$_subquery$_004"@"SEL$4")
FULL(@"SEL$5" "T2"@"SEL$5")
FULL(@"SEL$3" "T2"@"SEL$3")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("N1"="T1"."N1")
5 - filter("RN"=1)
6 - filter(ROW_NUMBER() OVER ( PARTITION BY "N1" ORDER BY INTERNAL_FUNCTION("N2") DESC )<=1)
73 rows selected.
--//时间在id=6上。
SCOTT@book01p> @ dpccpu 2r8rr3pht5cvn 0
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 2r8rr3pht5cvn, child number 0
-------------------------------------
merge /*+ gather_plan_statistics monitor */ --nenad into t1 using (
select n1,c1 from ( select n1,c1, row_number() over (partition
by n1 order by n2 desc) rn from t2 ) where rn = 1 ) v1 on ( v1.n1 =
t1.n1 ) when matched then update set t1.c1 = v1.c1
Plan hash value: 3917951292
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | %ASH SAMPLES
--------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | | | | 3356 (100)| |
| 1 | MERGE | T1 | | | | | |
| 2 | VIEW | | | | | | |
|* 3 | HASH JOIN | | 1 | 38 | 8304K| 3356 (2)| 00:00:01 | 1% ( 1% CPU 0% I/O)
| 4 | TABLE ACCESS FULL | T1 | 500K| 2441K| | 243 (3)| 00:00:01 |
|* 5 | VIEW | | 400K| 12M| | 1850 (2)| 00:00:01 |
|* 6 | WINDOW SORT PUSHED RANK| | 400K| 3906K| 7856K| 1850 (2)| 00:00:01 | 99% ( 99% CPU 0% I/O)
| 7 | TABLE ACCESS FULL | T2 | 400K| 3906K| | 243 (3)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("N1"="T1"."N1")
5 - filter("RN"=1)
6 - filter(ROW_NUMBER() OVER ( PARTITION BY "N1" ORDER BY
INTERNAL_FUNCTION("N2") DESC )<=1)
--//99%的CPU资源在id=6。
--//作者通过bpftrace定位smbgetqbPart频繁调用800001次。而19c调用0.
--//通过pstack观察也可以同样定位：
$ seq 10000 | xargs -IQ pstack 6849 >| gg.txt
--//注：我的测试环境密集执行pstack，按ctrl+c中断后，相关进程很容易出现挂起情况，不知道原因。
--//sql执行结束按ctrl+c中断pstack的执行。
awk '/#0/{print $4}' gg.txt | sort |uniq -c | sort -nr
226 smbgetqbPart
3 __read_nocancel
1 rwosmf
1 qerstUpdateStats
1 expeal
--//大部分调用函数smbgetqbPart。
$ pstack 6849
#0 0x000000000e250f7d in smbgetqbPart ()
#1 0x000000001500e027 in sorgetqbf ()
#2 0x0000000004d9b504 in qerwnFetch ()
#3 0x000000000a261506 in qerstFetch ()
#4 0x0000000004cd1903 in qervwFetch ()
#5 0x000000000a261506 in qerstFetch ()
#6 0x0000000014dfbda7 in rwsfcd ()
#7 0x000000000a261506 in qerstFetch ()
#8 0x000000001516b8a5 in qerhnFetch ()
#9 0x000000000a261506 in qerstFetch ()
#10 0x0000000004cd1903 in qervwFetch ()
#11 0x000000000a261506 in qerstFetch ()
#12 0x0000000014dfbda7 in rwsfcd ()
#13 0x000000000a261506 in qerstFetch ()
#14 0x0000000004dd99dd in qerusFetch ()
#15 0x000000000a261506 in qerstFetch ()
#16 0x0000000014ffbf49 in updaul ()
#17 0x0000000014ff8f90 in updThreePhaseExe ()
#18 0x00000000041ab3e0 in upsexe ()
#19 0x0000000014ef6ab5 in opiexe ()
#20 0x0000000014f6cf9d in kpoal8 ()
#21 0x0000000014eefdf8 in opiodr ()
#22 0x0000000015294359 in ttcpip ()
#23 0x00000000030025c2 in opitsk ()
#24 0x0000000003007920 in opiino ()
#25 0x0000000014eefdf8 in opiodr ()
#26 0x0000000002ffe2ab in opidrv ()
#27 0x0000000003e8bd15 in sou2o ()
#28 0x0000000000e91ae0 in opimai_real ()
#29 0x0000000003e9915c in ssthrdmain ()
#30 0x0000000000e91924 in main ()
SCOTT@book01p> @ fix "PUSHED RANK"
SCOTT@book01p> @pr
==============================
SESSION_ID : 147
BUGNO : 30822446
VALUE : 1
SQL_FEATURE : QKSFM_EXECUTION_30822446
DESCRIPTION : enable window pushed rank optimization with partition by keys
OPTIMIZER_FEATURE_ENABLE : 21.1.0
EVENT : 0
IS_DEFAULT : 1
CON_ID : 3
PL/SQL procedure successfully completed.
SCOTT@book01p> alter session set "_FIX_CONTROL"='30822446:OFF' ;
Session altered.
SCOTT@book01p> @ g8.txt
0 rows merged.
Elapsed: 00:00:00.60
SCOTT@book01p> @ dpc '' '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 2r8rr3pht5cvn, child number 1
-------------------------------------
merge /*+ gather_plan_statistics monitor */ --nenad into t1 using (
select n1,c1 from ( select n1,c1, row_number() over (partition
by n1 order by n2 desc) rn from t2 ) where rn = 1 ) v1 on ( v1.n1 =
t1.n1 ) when matched then update set t1.c1 = v1.c1
Plan hash value: 3917951292
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 1 | | | | 3356 (100)| | 0 |00:00:00.56 | 1683 | | | |
| 1 | MERGE | T1 | 1 | | | | | | 0 |00:00:00.56 | 1683 | | | |
| 2 | VIEW | | 1 | | | | | | 0 |00:00:00.56 | 1683 | | | |
|* 3 | HASH JOIN | | 1 | 1 | 38 | 8304K| 3356 (2)| 00:00:01 | 0 |00:00:00.56 | 1683 | 33M| 8026K| 32M (0)|
| 4 | TABLE ACCESS FULL | T1 | 1 | 500K| 2441K| | 243 (3)| 00:00:01 | 500K|00:00:00.02 | 805 | | | |
|* 5 | VIEW | | 1 | 400K| 12M| | 1850 (2)| 00:00:01 | 400K|00:00:00.56 | 878 | | | |
|* 6 | WINDOW SORT PUSHED RANK| | 1 | 400K| 3906K| 7856K| 1850 (2)| 00:00:01 | 400K|00:00:00.46 | 878 | 25M| 1830K| 22M (0)|
| 7 | TABLE ACCESS FULL | T2 | 1 | 400K| 3906K| | 243 (3)| 00:00:01 | 400K|00:00:00.01 | 878 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - MRG$1
3 - SEL$F5BB74E1
4 - SEL$F5BB74E1 / "T1"@"SEL$1"
5 - SEL$3 / "from$_subquery$_007"@"SEL$2"
6 - SEL$3
7 - SEL$3 / "T2"@"SEL$3"
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("N1"="T1"."N1")
5 - filter("RN"=1)
6 - filter(ROW_NUMBER() OVER ( PARTITION BY "N1" ORDER BY INTERNAL_FUNCTION("N2") DESC )<=1)
--//逻辑读一样。执行计划一样。
4.附上测试使用dpccpu.sql脚本：
$ cat dpccpu.sql
with
"sql" as (select SQL_ID,CHILD_NUMBER,PLAN_HASH_VALUE,'' FORMAT from v$sql where sql_id='&1'),
"ash" as (
select sql_id,sql_plan_line_id,child_number,sql_plan_hash_value
,round(count(*)/"samples",2) load
,nvl(round(sum(case when session_state='ON CPU' then 1 end)/"samples",2),0) load_cpu
,nvl(round(sum(case when session_state='WAITING' and wait_class='User I/O' then 1 end)/"samples",2),0) load_io
from "sql" join
(
select sql_id,sql_plan_line_id,sql_child_number child_number,sql_plan_hash_value,session_state,wait_class,count(*) over (partition by sql_id,sql_plan_hash_value) "samples"
FROM V$ACTIVE_SESSION_HISTORY
) using(sql_id,child_number) group by sql_id,sql_plan_line_id,child_number,sql_plan_hash_value,"samples"
),
"plan" as (
-- get dbms_xplan result
select
sql_id,child_number,n,plan_table_output
-- get plan line id from plan_table output
,case when regexp_like (plan_table_output,'^[|][*]? *([0-9]+) *[|].*[|]$') then
regexp_replace(plan_table_output,'^[|][*]? *([0-9]+) *[|].*[|]$','\1')
END SQL_PLAN_LINE_ID
from (select rownum n,plan_table_output,SQL_ID,CHILD_NUMBER from "sql", table(dbms_xplan.display_cursor("sql".SQL_ID,"sql".CHILD_NUMBER,"sql".FORMAT)))
)
select PLAN_TABLE_OUTPUT||CASE
-- ASH load to be displayed
WHEN LOAD >0 THEN TO_CHAR(100*LOAD,'999')||'% (' || TO_CHAR(100*LOAD_CPU,'999')||'% CPU'|| TO_CHAR(100*LOAD_IO,'999')||'% I/O)'
-- header
WHEN REGEXP_LIKE (PLAN_TABLE_OUTPUT,'^[|] *Id *[|]') THEN ' %ASH SAMPLES'
end plan_table_output
from "plan" left outer join "ash" using(sql_id,child_number,sql_plan_line_id) order by sql_id,child_number,n;
浙公网安备 33010602011771号