[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
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-2940626/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [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
