[20230320]Trim CPU.txt

来源：这里教程网时间：2026-03-03 18:30:09 作者：

[20230320]Trim CPU.txt --//参考连接：https://jonathanlewis.wordpress.com/2022/07/22/trim-cpu/ --//仅仅重复我的测试结果。 --//首先一点trim不要乱用，注意nls的格式可以导致不期望的结果。另外谓词尽量避免在查询字段使用函数。 1.环境： SCOTT@test01p> @ver1 PORT_STRING VERSION BANNER CON_ID -------------------- ---------- -------------------------------------------------------------------------------- ---------- IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0 2.建立测试环境： create table tab1 as select * from all_Objects where owner != 'PUBLIC' and object_type != 'SYNONYM' and owner='SYS' and rownum <= 128 ; insert into tab1 select * from all_Objects where owner != 'PUBLIC' and object_type != 'SYNONYM' and owner<>'SYS' and rownum <= 72 ; create table tab2 as select * from all_Objects where owner != 'PUBLIC' and object_type != 'SYNONYM' ; --//不知道作者如何建立的表，按照他的操作建立不出对应的数据分布效果。我改写一下。 --//分析表略。 SCOTT@test01p> select owner, count(*) from tab1 group by owner order by 2 desc; OWNER COUNT(*) ------ -------- SYS 128 SYSTEM 65 OUTLN 7 SCOTT@test01p> select owner, count(*) from tab2 group by owner order by 2 desc; OWNER COUNT(*) ---------------------- -------- SYS 11159 SYSTEM 427 XDB 422 WMSYS 374 GSMADMIN_INTERNAL 184 SCOTT 78 DBSNMP 54 HR 34 AUDSYS 14 REMOTE_SCHEDULER_AGENT 12 OUTLN 8 DBSFWUSER 8 ORACLE_OCM 6 APPQOSSYS 5 TEST 3 15 rows selected. 3.测试： @sl all select count(*) from tab1 inner join tab2 on tab1.owner = tab2.owner --and trim(tab1.object_name) > trim(tab2.object_name) --and rtrim(tab1.object_name) > rtrim(tab2.object_name) and ltrim(tab1.object_name) > ltrim(tab2.object_name) --and tab1.object_name > tab2.object_name ; --//select * from table(dbms_xplan.display_cursor(format=>'projection allstats last')); @ dpc '' '' --//no trim ----------------------------------------------------------------------------------------------------------------------------- | 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| | | 73 (100)| | 1 |00:00:00.14| 253| | | | | 1| SORT AGGREGATE | | 1| 1| 43 | | | 1 |00:00:00.14| 253| | | | |* 2| HASH JOIN | | 1| 59365| 2492K| 73 (3)|00:00:01| 741K|00:00:00.14| 253|1476K|1476K| 923K (0)| | 3| TABLE ACCESS FULL|TAB1| 1| 200| 3800 | 4 (0)|00:00:01| 200 |00:00:00.01| 7| | | | | 4| TABLE ACCESS FULL|TAB2| 1| 12788| 299K| 67 (0)|00:00:01| 12788 |00:00:00.01| 246| | | | ----------------------------------------------------------------------------------------------------------------------------- --//trim ----------------------------------------------------------------------------------------------------------------------------- | 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| | | 73 (100)| | 1 |00:00:00.66| 253| | | | | 1| SORT AGGREGATE | | 1| 1| 43 | | | 1 |00:00:00.66| 253| | | | |* 2| HASH JOIN | | 1| 8525| 357K| 73 (3)|00:00:01| 741K|00:00:00.65| 253|1476K|1476K| 867K (0)| | 3| TABLE ACCESS FULL|TAB1| 1| 200| 3800 | 4 (0)|00:00:01| 200 |00:00:00.01| 7| | | | | 4| TABLE ACCESS FULL|TAB2| 1| 12788| 299K| 67 (0)|00:00:01| 12788 |00:00:00.01| 246| | | | ----------------------------------------------------------------------------------------------------------------------------- --//rtrim ----------------------------------------------------------------------------------------------------------------------------- | 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| | | 73 (100)| | 1 |00:00:00.65| 253| | | | | 1| SORT AGGREGATE | | 1| 1| 43 | | | 1 |00:00:00.65| 253| | | | |* 2| HASH JOIN | | 1| 8525| 357K| 73 (3)|00:00:01| 741K|00:00:00.65| 253|1476K|1476K| 873K (0)| | 3| TABLE ACCESS FULL|TAB1| 1| 200| 3800 | 4 (0)|00:00:01| 200 |00:00:00.01| 7| | | | | 4| TABLE ACCESS FULL|TAB2| 1| 12788| 299K| 67 (0)|00:00:01| 12788 |00:00:00.01| 246| | | | ----------------------------------------------------------------------------------------------------------------------------- --//ltrim ----------------------------------------------------------------------------------------------------------------------------- | 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| | | 73 (100)| | 1 |00:00:00.38| 253| | | | | 1| SORT AGGREGATE | | 1| 1| 43 | | | 1 |00:00:00.38| 253| | | | |* 2| HASH JOIN | | 1| 8525| 357K| 73 (3)|00:00:01| 741K|00:00:00.38| 253|1476K|1476K| 871K (0)| | 3| TABLE ACCESS FULL|TAB1| 1| 200| 3800 | 4 (0)|00:00:01| 200 |00:00:00.01| 7| | | | | 4| TABLE ACCESS FULL|TAB2| 1| 12788| 299K| 67 (0)|00:00:01| 12788 |00:00:00.01| 246| | | | ----------------------------------------------------------------------------------------------------------------------------- no trim – 0.14 seconds trim() – 0.66 seconds rtrim() – 0.65 seconds ltrim() – 0.38 seconds. --//结果与作者有一点点不同,但是使用函数都是有点慢,主要调用函数次数太多. --//感觉作者机器性能也不是很好,还是那里存在差异.注，最后发现tab2的owner='SYS'的记录数量太少。 4.测试调用trim次数： --//如何确定调用次数. create or replace package p1 as n1 number; function f1(v1 in varchar2) return varchar2 deterministic; end; / create or replace package body p1 as function f1 (v1 in varchar2) return varchar2 deterministic is begin p1.n1 := p1.n1 + 1; return trim(v1); end; end; / @ sl all exec p1.n1 := 0 select count(*) from tab1 inner join tab2 on tab1.owner = tab2.owner and p1.f1(tab1.object_name) > p1.f1(tab2.object_name) -- and p1.f1(tab1.object_name) > trim(tab2.object_name) -- and trim(tab1.object_name) > p1.f1(tab2.object_name) ; --//select * from table(dbms_xplan.display_cursor(format=>'projection allstats last')); ------------------------------------------------------------------------------------------------------------------------------------ | 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| | | 73 (100)| | 1 |00:00:14.15| 635| 5| | | | | 1| SORT AGGREGATE | | 1| 1| 43 | | | 1 |00:00:14.15| 635| 5| | | | |* 2| HASH JOIN | | 1| 8525| 357K| 73 (3)|00:00:01| 741K|00:00:14.10| 635| 5|1476K|1476K| 889K (0)| | 3| TABLE ACCESS FULL|TAB1| 1| 200| 3800 | 4 (0)|00:00:01| 200 |00:00:00.01| 7| 0| | | | | 4| TABLE ACCESS FULL|TAB2| 1| 12788| 299K| 67 (0)|00:00:01| 12788 |00:00:00.01| 246| 0| | | | ------------------------------------------------------------------------------------------------------------------------------------ SCOTT@test01p> set serveroutput on SCOTT@test01p> execute dbms_output.put_line(p1.n1); 2912326 PL/SQL procedure successfully completed. SCOTT@test01p> set serveroutput off --//作者测试的说明： set the variable to zero run a query that does one of use my function twice use my function once – on the build table use my function once – on the probe table report the execution plan with stats print the value of the variable select v1.owner, ct1, ct2, ct1 * ct2, sum(ct1 * ct2) over() tot_ct from (select owner, count(object_name) ct1 from tab1 group by owner) v1, (select owner, count(object_name) ct2 from tab2 group by owner) v2 where v2.owner = v1.owner / OWNER CT1 CT2 CT1*CT2 TOT_CT ------- --- ----- ------- ------- OUTLN 7 8 56 1456163 SYSTEM 65 427 27755 1456163 SYS 128 11159 1428352 1456163 --//调用了1456163*2 = 2912326. --//作者的调用次数5160466 ,差异在这里相差3倍.主要是owner='SYS'的记录差别很大. OWNER CT1 CT2 CT1*CT2 TOT_CT --------------- ---------- ---------- ---------- ---------- SYS 128 40104 5133312 5160466 SYSTEM 65 417 27105 5160466 OUTLN 7 7 49 5160466 --//以上作者原始数据。 exec p1.n1 := 0 select count(*) from tab1 inner join tab2 on tab1.owner = tab2.owner --and p1.f1(tab1.object_name) > p1.f1(tab2.object_name) and p1.f1(tab1.object_name) > trim(tab2.object_name) -- and trim(tab1.object_name) > p1.f1(tab2.object_name) ; SCOTT@test01p> execute dbms_output.put_line(p1.n1); 1456163 exec p1.n1 := 0 select count(*) from tab1 inner join tab2 on tab1.owner = tab2.owner --and p1.f1(tab1.object_name) > p1.f1(tab2.object_name) -- and p1.f1(tab1.object_name) > trim(tab2.object_name) and trim(tab1.object_name) > p1.f1(tab2.object_name) ; SCOTT@test01p> execute dbms_output.put_line(p1.n1); 1456163 PL/SQL procedure successfully completed. --//从测试可以看出尽量避免oracle函数的调用次数. --//补充一些数据重复测试。 SCOTT@test01p> insert into tab2 select tab2.* from tab2 ,(select 1 from dual connect by level<=3) where owner='SYS'; 33477 rows created. SCOTT@test01p> commit ; Commit complete. select count(*) from tab1 inner join tab2 on tab1.owner = tab2.owner --and trim(tab1.object_name) > trim(tab2.object_name) --and rtrim(tab1.object_name) > rtrim(tab2.object_name) and ltrim(tab1.object_name) > ltrim(tab2.object_name) --and tab1.object_name > tab2.object_name ; ----------------------------------------------------------------------------------------------------------------------------- | 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| | | 73 (100)| | 1 |00:00:01.55| 886| | | | | 1| SORT AGGREGATE | | 1| 1| 43 | | | 1 |00:00:01.55| 886| | | | |* 2| HASH JOIN | | 1| 8525| 357K| 73 (3)|00:00:01| 2916K|00:00:01.55| 886|1476K|1476K| 852K (0)| | 3| TABLE ACCESS FULL|TAB1| 1| 200| 3800 | 4 (0)|00:00:01| 200 |00:00:00.01| 7| | | | | 4| TABLE ACCESS FULL|TAB2| 1| 12788| 299K| 67 (0)|00:00:01| 46265 |00:00:00.01| 879| | | | ----------------------------------------------------------------------------------------------------------------------------- select count(*) from tab1 inner join tab2 on tab1.owner = tab2.owner --and trim(tab1.object_name) > trim(tab2.object_name) --and rtrim(tab1.object_name) > rtrim(tab2.object_name) --and ltrim(tab1.object_name) > ltrim(tab2.object_name) and tab1.object_name > tab2.object_name ; ------------------------------------------------------------------------------------------------------------------------------ | 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| | | 73 (100)| | 1 |00:00:00.55| 886| | | | | 1| SORT AGGREGATE | | 1| 1 | 43 | | | 1 |00:00:00.55| 886| | | | |* 2| HASH JOIN | | 1| 59365 | 2492K| 73 (3)|00:00:01| 2916K|00:00:00.55| 886|1476K|1476K| 871K (0)| | 3| TABLE ACCESS FULL|TAB1| 1| 200 | 3800 | 4 (0)|00:00:01| 200 |00:00:00.01| 7| | | | | 4| TABLE ACCESS FULL|TAB2| 1| 12788 | 299K| 67 (0)|00:00:01| 46265 |00:00:00.01| 879| | | | ------------------------------------------------------------------------------------------------------------------------------ select v1.owner, ct1, ct2, ct1 * ct2, sum(ct1 * ct2) over() tot_ct from (select owner, count(object_name) ct1 from tab1 group by owner) v1, (select owner, count(object_name) ct2 from tab2 group by owner) v2 where v2.owner = v1.owner / OWNER CT1 CT2 CT1*CT2 TOT_CT ------- --- ----- ------- ------- OUTLN 7 8 56 5741219 SYSTEM 65 427 27755 5741219 SYS 128 44636 5713408 5741219 --//这样基本接近了. exec p1.n1 := 0 select count(*) from tab1 inner join tab2 on tab1.owner = tab2.owner and p1.f1(tab1.object_name) > p1.f1(tab2.object_name) --and p1.f1(tab1.object_name) > trim(tab2.object_name) -- and trim(tab1.object_name) > p1.f1(tab2.object_name) ; ----------------------------------------------------------------------------------------------------------------------------- | 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| | | 252 (100)| | 1 |00:01:04.05| 1269| | | | | 1| SORT AGGREGATE | | 1| 1| 44 | | | 1 |00:01:04.05| 1269| | | | |* 2| HASH JOIN | | 1| 30843| 1325K| 252 (3)|00:00:01| 2916K|00:01:03.77| 1269|1476K|1476K| 887K (0)| | 3| TABLE ACCESS FULL|TAB1| 1| 200| 3800 | 4 (0)|00:00:01| 200 |00:00:00.01| 7| | | | | 4| TABLE ACCESS FULL|TAB2| 1| 46265| 1129K| 243 (1)|00:00:01| 46265 |00:00:00.02| 879| | | | ----------------------------------------------------------------------------------------------------------------------------- SCOTT@test01p> set serveroutput on SCOTT@test01p> execute dbms_output.put_line(p1.n1); 11482438 PL/SQL procedure successfully completed. SCOTT@test01p> set serveroutput off

编辑推荐：

返回列表

[20230320]Trim CPU.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推