问题描述
团队:
请在下面找到测试用例 (像我们的业务需求一样建模) 以及执行计划 (显示谓词部分)
和sql-monitor报告的时间，这是在Exacc平台的Oracle 18c上运行的。
请帮助我们理解为什么优化器在plan中的步骤 #2生成了 “过滤器”？
在我们的实际执行中，我们可以看到-特定的步骤大约12分钟到15分钟 (结果集得到缓冲，过滤器得到验证)
那么我们怎样才能摆脱那个 “过滤器” 呢？
请在下面找到测试用例 (像我们的业务需求一样建模) 以及执行计划 (显示谓词部分)
和sql-monitor报告的时间，这是在Exacc平台的Oracle 18c上运行的。
请帮助我们理解为什么优化器在plan中的步骤 #2生成了 “过滤器”？
在我们的实际执行中，我们可以看到-特定的步骤大约12分钟到15分钟 (结果集得到缓冲，过滤器得到验证)
那么我们怎样才能摆脱那个 “过滤器” 呢？
drop table driver purge;
drop table t1 purge;
drop table t2 purge;
drop table t3 purge;
create table driver as select a.* from all_objects a,
(select rownum from dual connect by level <=10 ) ;
create table t1 as select a.* from all_objects a,
(select rownum from dual connect by level <=100 );
create table t2 as select t1.* from t1;
create table t3( object_id number, data_object_id number );
exec dbms_parallel_execute.create_task(task_name=>'DEMO_TASK');
begin
dbms_parallel_execute.create_chunks_by_rowid(
task_name=>'DEMO_TASK',
table_owner=>user,
table_name=>'DRIVER',
by_row=>false,
chunk_size=>100);
end;
/
select count(*) from user_parallel_execute_chunks where task_name='DEMO_TASK';
declare
l_sql long;
begin
l_sql :=' insert into t3( object_id, data_object_id)
select t1.object_id, t2.data_object_id
from t1, t2, driver t3
where t1.object_id = t2.object_id
and t2.object_id = t3.object_id
and t3.rowid between :start_id and :end_id ';
dbms_parallel_execute.run_task(
task_name=>'DEMO_TASK',
sql_stmt=>l_sql,
language_flag=>dbms_sql.native,
parallel_level=>4);
end;
/
demo@PDB1> select * from table( dbms_xplan.display_cursor('9cbx808m23rkg'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
SQL_ID 9cbx808m23rkg, child number 0
-------------------------------------
insert into t3( object_id, data_object_id) select t1.object_id,
t2.data_object_id from t1, t2, driver t3 where t1.object_id =
t2.object_id and t2.object_id = t3.object_id and t3.rowid between
:start_id and :end_id
Plan hash value: 3603417986
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | | 303K(100)| |
| 1 | LOAD TABLE CONVENTIONAL | T3 | | | | | |
|* 2 | FILTER | | | | | | |
|* 3 | HASH JOIN | | 61M| 1825M| 22M| 303K (1)| 00:00:12 |
|* 4 | HASH JOIN | | 622K| 15M| | 148K (1)| 00:00:06 |
|* 5 | TABLE ACCESS STORAGE BY ROWID RANGE| DRIVER | 6267 | 104K| | 13586 (1)| 00:00:01 |
| 6 | TABLE ACCESS STORAGE FULL | T2 | 25M| 215M| | 135K (1)| 00:00:06 |
| 7 | TABLE ACCESS STORAGE FULL | T1 | 25M| 119M| | 135K (1)| 00:00:06 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(CHARTOROWID(:END_ID)>=CHARTOROWID(:START_ID))
3 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
4 - access("T2"."OBJECT_ID"="T3"."OBJECT_ID")
5 - storage("T3".ROWID>=CHARTOROWID(:START_ID) AND "T3".ROWID<=CHARTOROWID(:END_ID))
30 rows selected.
SQL Monitoring Report
SQL Text
------------------------------
insert into t3( object_id, data_object_id) select t1.object_id, t2.data_object_id from t1, t2, driver t3 where t1.object_id = t2.object_id and t2.object_id = t3.object_id and t3.rowid between :start_id and :end_id
Global Information
------------------------------
Status : DONE
Instance ID : 4
Session : DEMO (196:63770)
SQL ID : 9cbx808m23rkg
SQL Execution ID : 67108882
Execution Started : 11/05/2020 04:45:43
First Refresh Time : 11/05/2020 04:45:46
Last Refresh Time : 11/05/2020 04:46:14
Duration : 31s
Module/Action : SQL*Plus/-
Service : pdb1
Program : sqlplus.exe
Binds
========================================================================================================================
| Name | Position | Type | Value |
========================================================================================================================
| :START_ID | 1 | VARCHAR2(32) | AABRIxAADAADAJ0AAA |
| :END_ID | 2 | VARCHAR2(32) | AABRIxAADAADALXH// |
========================================================================================================================
Global Stats
===========================================================================================================================
| Elapsed | Cpu | IO | Other | Buffer | Read | Read | Uncompressed | Offload | Offload | Cell |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | Bytes | Elig Bytes | Returned Bytes | Offload |
===========================================================================================================================
| 31 | 23 | 7.70 | 0.59 | 2M | 25547 | 8GB | 566MB | 8GB | 30MB | 99.62% |
===========================================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=3603417986)
==============================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (Max) | (%) | (# samples) |
==============================================================================================================================================================================================
| 0 | INSERT STATEMENT | | | | 29 | +3 | 1 | 0 | | | . | | |
| 1 | LOAD TABLE CONVENTIONAL | T3 | | | 30 | +2 | 1 | 0 | 17659 | 138MB | . | 77.42 | log buffer space (1) |
| | | | | | | | | | | | | | Cpu (14) |
| | | | | | | | | | | | | | cell single block physical read (9) |
| 2 | FILTER | | | | 29 | +3 | 1 | 55M | | | . | | |
| 3 | HASH JOIN | | 62M | 303K | 29 | +3 | 1 | 55M | | | 49MB | 19.35 | Cpu (6) |
| 4 | HASH JOIN | | 622K | 149K | 3 | +1 | 1 | 550K | | | 6MB | 3.23 | Cpu (1) |
| 5 | TABLE ACCESS STORAGE BY ROWID RANGE | DRIVER | 6267 | 13586 | 1 | +3 | 1 | 5504 | 2 | 784KB | 1MB | | |
| 6 | TABLE ACCESS STORAGE FULL | T2 | 25M | 135K | 1 | +3 | 1 | 25M | 3898 | 4GB | 6MB | | |
| 7 | TABLE ACCESS STORAGE FULL | T1 | 25M | 135K | 29 | +3 | 1 | 25M | 3898 | 4GB | 6MB | | |
==============================================================================================================================================================================================
专家解答
我们能在 * real * 查询中看到一些SQL监视信息吗，因为在您的示例中，过滤器时间看起来完全可以忽略不计的延迟，当我在这里重复您的演示时，我也看到了同样的情况。
所以我有5亿行的3秒开销。
SQL> create table driver as select a.* from dba_objects a,
2 (select rownum from dual connect by level <=10 ) ;
Table created.
SQL> create table t1 as select a.* from dba_objects a,
2 (select rownum from dual connect by level <=100 );
Table created.
SQL> create table t2 as select t1.* from t1;
Table created.
SQL> create table t3( object_id number, data_object_id number );
Table created.
SQL> variable start_id varchar2(100)
SQL> variable END_ID varchar2(100)
SQL> exec :start_id := 'AAARfBAAAAAAAOLAAA'; :end_id := 'AAARfBAAAAAABSZAIE';
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> select /*+ gather_plan_statistics */ max(t1.object_id), max(t2.data_object_id)
2 from t1, t2, driver t3
3 where t1.object_id = t2.object_id
4 and t2.object_id = t3.object_id
5 and t3.rowid >= chartorowid(:start_id) and t3.rowid<=chartorowid(:end_id)
6 /
MAX(T1.OBJECT_ID) MAX(T2.DATA_OBJECT_ID)
----------------- ----------------------
71612 71602
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:54.93 | 11106 | 10311 | | | |
| 1 | RESULT CACHE | 3910hufqshac7cv5ahmutw6kt1 | 1 | 1 | 1 |00:00:54.93 | 11106 | 10311 | 65536 | 1024 | |
| 2 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:54.93 | 11106 | 10311 | | | |
|* 3 | FILTER | | 1 | | 517M|00:00:50.03 | 11106 | 10311 | | | |
|* 4 | HASH JOIN | | 1 | 12M| 517M|00:00:47.83 | 11106 | 10311 | 255M| 23M| 292M (0)|
| 5 | JOIN FILTER CREATE | :BF0000 | 1 | 126K| 5177K|00:00:00.49 | 5940 | 5148 | | | |
|* 6 | HASH JOIN RIGHT SEMI | | 1 | 126K| 5177K|00:00:00.36 | 5940 | 5148 | 27M| 6846K| 24M (0)|
| 7 | JOIN FILTER CREATE | :BF0001 | 1 | 1294 | 516K|00:00:00.02 | 789 | 0 | | | |
|* 8 | TABLE ACCESS STORAGE BY ROWID RANGE| DRIVER | 1 | 1294 | 516K|00:00:00.01 | 789 | 0 | 1025K| 1025K| |
| 9 | JOIN FILTER USE | :BF0001 | 1 | 5177K| 5177K|00:00:00.23 | 5151 | 5148 | | | |
|* 10 | TABLE ACCESS STORAGE FULL | T2 | 1 | 5177K| 5177K|00:00:00.22 | 5151 | 5148 | 1025K| 1025K| 8227K (0)|
| 11 | JOIN FILTER USE | :BF0000 | 1 | 5177K| 5177K|00:00:00.12 | 5166 | 5163 | | | |
|* 12 | TABLE ACCESS STORAGE FULL | T1 | 1 | 5177K| 5177K|00:00:00.10 | 5166 | 5163 | 1025K| 1025K| 9256K (0)|
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
所以我有5亿行的3秒开销。
文章转载自ASKTOM，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
