leading(t2, t1, t3) use_nl(t1) use_nl(t3) 10046
Applies to:
Oracle Database - Enterprise Edition - Version 11.2.0.4 to 12.1.0.2 [Release 11.2 to 12.1]
Oracle Database Cloud Schema Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.
Symptoms
NOTE: In the images and/or the document content below, the user information and data used represents fictitious data from the Oracle sample schema(s)
or Public Documentation delivered with an Oracle database product. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.
When selecting from a composite partitioned table, the following recursive query is taking too long to execute.
SELECT distinct TBL$OR$IDX$PART$NUM("<<TABLE>"."<<COL1>>", 0, 2, 2971,
"<COL1>>")
FROM
(SELECT "<COL1>>" FROM....
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 163 0.10 0.10 0 0 0 0
Execute 163 0.13 0.13 0 0 0 0
Fetch 326 898.37 898.37 0 330727 0 163
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 652 898.61 898.61 0 330727 0 163
This issue is similar to bug 17572606. The bug does not cover the case of a
composite partitioned table (which is the customer's use case).
Cause
Performance issue by building high number of cursors on TBL$OR$IDX$PART$NUM due to Nested loop join.
Here is a simple test case that demonstrates the problem.
conn <USER> / <PASSWORD>
drop table t1;
drop table t2;
drop table t3;
create table t1 (c1 number, c2 varchar2(2), c3 varchar(2))
partition by range (c1)
subpartition by hash (c2) subpartitions 4
(partition p1 values less than (10),
partition p2 values less than (30));
create table t2 (c1 number, c2 varchar(2));
create table t3 (c1 number, c2 varchar(2));
begin
for i in 15..20 loop
insert into t1 values (i,to_char(i),to_char(i));
insert into t2 values (i,'19');
insert into t3 values (i,to_char(i));
end loop;
end;
/
commit;
create unique index t3idx on t3(c1,c2);
execute dbms_stats.gather_schema_stats('<<schema name>>')
ALTER SESSION SET events '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';
ALTER SESSION SET tracefile_identifier='MYTRACE_4';
explain plan for
select /*+ leading(t2, t1, t3) use_nl(t1) use_nl(t3) */ *
from t1, t2, t3
where t1.c2='19' and t2.c2='19' and t1.c2=t3.c2 and t3.c1=19 ;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL,NULL, 'advanced'));
应该是用 TBL$OR$IDX$PART$NUM 看是不是在分区中，不在直接裁剪掉
Execution plan:
--------------
| Id | Operation | Name | Pstart| Pstop |
-----------------------------------------------------------
| 0 | SELECT STATEMENT | | | |
| 1 | NESTED LOOPS | | | |
| 2 | NESTED LOOPS | | | |
|* 3 | TABLE ACCESS FULL | T2 | | |
| 4 | PARTITION RANGE ALL | | 1 | 2 |
| 5 | PARTITION HASH SINGLE| | KEY(AP)|KEY(AP)|
|* 6 | TABLE ACCESS FULL | T1 | KEY | KEY |
|* 7 | INDEX UNIQUE SCAN | T3IDX | | |
-----------------------------------------------------------
Solution
Workaround:
_subquery_pruning_enabled=FALSE on a session or system level. OR use the no_subquery_pruning hint on the problematic queries.
It's a table-level hint so it would be "/*+ no_subquery_pruning(t1) ..
OR
Apply Patch 19889960 if available for your version and platform.
Note: Fix for bug 17572606 is superseded by fix for patch 19889960. Patch 19889960 covers for both partitions and subpartitions.
原文地址：https://blog.csdn.net/jnrjian/article/details/143024996
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！