[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;
[20250718]WINDOW SORT PUSHED RANK Performance Regression in Oracle 21c.txt
来源：这里教程网
时间：2026-03-03 22:20:55
作者：
编辑推荐：
下一篇：
相关推荐
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效
2 月 9 日消息，雷神 (THUNDEROBOT) 现已宣布推出基于英
-
制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用
2 月 10 日消息，制造商 Musnap 现已在海外推出一款 Oce
热文推荐
- 全球商用PC换代潮到来，DaaS行业步入黄金增长期
全球商用PC换代潮到来，DaaS行业步入黄金增长期
26-03-03 - 一次意想不到的ADG中断
一次意想不到的ADG中断
26-03-03 - 中国技术反哺全球，百奥赛图定义新药研发新范式
中国技术反哺全球，百奥赛图定义新药研发新范式
26-03-03 - 分区表truncate慢处理
分区表truncate慢处理
26-03-03 - 你算的ASM磁盘使用率，可能是错的
你算的ASM磁盘使用率，可能是错的
26-03-03 - 在HP小机上跑了1432天的Oracle RAC，能不能重启？
在HP小机上跑了1432天的Oracle RAC，能不能重启？
26-03-03 - 从RenMice到多靶点授权，百奥赛图演绎中国Biotech价值跃升之路
从RenMice到多靶点授权，百奥赛图演绎中国Biotech价值跃升之路
26-03-03 - 通过 Nuke 为 Dotnet Core 应用构建自动化流程
通过 Nuke 为 Dotnet Core 应用构建自动化流程
26-03-03 - 从零开始，在甲骨文云创建云主机（详细图文教程）
从零开始，在甲骨文云创建云主机（详细图文教程）
26-03-03 - 被“假防晒衣”坑了一把后，这些人决定去买优衣库
被“假防晒衣”坑了一把后，这些人决定去买优衣库
26-03-03