hash join???
A reader, December 25, 2002 - 12:18 pm UTC
"the hash join just cannot do what you want it to do. It has NO idea before it
begins what partition MIGHT be involved -- you are making it hash the two tables
and join them -- therefore, it has to do the entire thing"
Tom,
I did not understand the above statement? what does it mean?
December 25, 2002 - 12:43 pm UTC
umm, which part isn't clear?
They forced a hash join.
As far as the database is concerned, ALL partitions from the partitioned table are involved
explain plan for select /*+ ORDERED USE_hash(b) */ *
from submission_file1 a, submission_detail1 b
where a.latest_status_dt between to_date('12/01/2002','mm/dd/yyyy') and
to_date('12/05/2002','mm/dd/yyyy')
and a.dt_cr = b.dt_cr
and a.subm_id = b.subm_id
(and guess what - they are! all partitions are involved in this HASH join)
See -- the table B is partitioned by dt_cr. Now, you tell me -- before this query begins -- what partitions do you need to hash in order to do the hash join? Well all of them -- why? there is no predicate on b.dt_cr, sure there is a JOIN condition -- but there isn't any predicate that could be used for partition elimination.
They did not care to share the nested loops plan with us - show I cannot comment on that but there it may well be doing a full scan of A followed by a full scan of a single partition in B for each and every row (in which case, the hash join would be infinitely preferred).
BUT ....
A reader, December 25, 2002 - 1:12 pm UTC
according to oracle document: 179518.1
In 8.1.6 the code was enhanced to allow elimination to occur with a hash join. It was recognized that the non-partitioned table contains the information to eliminate partitions. If this information is compared with the dictionary information regarding partition ranges, then a list of partitions to be accessed can be generated. This activity only occurs if certain cost and selectivity criteria are met. The elimination IS NOT done in the join step itself, rather it is performed as recursive SQL independently of the join itself and therefore elimination is not join order dependant. In this case the following plan can be seen:
The example which is given does a partition elimination for a hash join. I tried that example. It works. However for my case it does not. Might be because all my partitions belong to same tablespace ????????
December 25, 2002 - 1:51 pm UTC
tablespace has nothing to do with it. nothing.
So, look to the note -- "if certain cost and selectivity criteria are met".
Look at your query:
explain plan for select /*+ ORDERED USE_hash(b) */ *
from submission_file1 a, submission_detail1 b
where a.latest_status_dt between to_date('12/01/2002','mm/dd/yyyy') and
to_date('12/05/2002','mm/dd/yyyy')
and a.dt_cr = b.dt_cr
and a.subm_id = b.subm_id
b is partitioned on dt_cr.
You are selecting some (possibly all) set of dt_cr's from A based on that between. So tell me, does the optimizer have the ability to see that given your predicate on A, this makes sense? to do partition elimination? apparently -- not.
it is doing what it is doing.
If you would care to share howto setup your test (eg: populated off of data dictionary data or some procedural process), we can look further.
cannot get partition elimination
A reader, December 25, 2002 - 1:34 pm UTC
Tom,
I tried that every possible option. But cannot get it to work in my case (using hash join to eliminate partition).
I will post the use_NL plan on this page after sometime.
Thanks for all the help.
Please look at this ....
A reader, December 26, 2002 - 11:41 am UTC
Tom,
Please look at the below results:
First the NL query path.
Q1) why did it eliminate the partitions in case of nested loop?
Plan Table
--------------------------------------------------------------------------------
| Operation | Name | Rows | Bytes| Cost | TQ |IN-OUT|
PQ Distrib | Pstart| Pstop |
--------------------------------------------------------------------------------
----------------------------
| SELECT STATEMENT | | 999K| 263M| 36M| | |
| | |
| NESTED LOOPS | | 999K| 263M| 36M| | |
| | |
Plan Table
--------------------------------------------------------------------------------
| TABLE ACCESS FULL |SUBMISSIO | 9K| 1M| 29 | | |
| | |
| PARTITION RANGE ITERATOR | | | | | | |
| KEY | KEY |
| TABLE ACCESS FULL |SUBMISSIO | 2M| 443M| 3995 | | |
| KEY | KEY |
--------------------------------------------------------------------------------
Plan Table
--------------------------------------------------------------------------------
----------------------------
8 rows selected.
Q2) select column_name, num_distinct from user_tab_columns
SQL> /
DEPTNO 4
DNAME 4
LOC 4
SQL> alter session set "_subquery_pruning_cost_factor"=1;
Session altered.
SQL> alter session set "_subquery_pruning_reduction"=100;
Session altered.
SQL>
explain plan for
select /*+ ordered use_hash(p) */ col1
from dept d, range_part p
where d.deptno = p.col1
and d.loc='DALLAS';SQL> 2 3 4 5
Explained.
SQL> @?/rdbms/admin/utlxplp.sql
Plan Table
--------------------------------------------------------------------------------
| Operation | Name | Rows | Bytes| Cost | TQ |IN-OUT|
PQ Distrib | Pstart| Pstop |
--------------------------------------------------------------------------------
----------------------------
| SELECT STATEMENT | | 1 | 22 | 3 | | |
| | |
| HASH JOIN | | 1 | 22 | 3 | | |
| | |
Plan Table
--------------------------------------------------------------------------------
| TABLE ACCESS FULL |DEPT | 1 | 9 | 1 | | |
| | |
| PARTITION RANGE ITERATOR | | | | | | |
| KEY | KEY |
| TABLE ACCESS FULL |RANGE_PAR | 84 | 1K| 1 | | |
| KEY | KEY |
--------------------------------------------------------------------------------
Plan Table
--------------------------------------------------------------------------------
----------------------------
*** 2002-12-25 21:56:10.634
*** SESSION ID:(10.437) 2002-12-25 21:56:10.630
Partition Iterator Information:
partition level = PARTITION
call time = RUN
order = ASCENDING
Partition iterator for level 1:
iterator = SQL {count= 1, text = SELECT distinct TBL$OR$IDX$PART$NUM("RANGE_P
ART", 0, "COL1") FROM (SELECT "D"."DEPTNO" COL1 FROM "DEPT" "D" WHERE "D"."LOC"
='DALLAS') ORDER BY 1}
index = 0
current partition: part# = 2, subp# = 65535, abs# = 2
SQL> insert into dept values(50,'RESEARCH','OPERATIONS');
1 row created.
SQL> commit;
Commit complete.
SQL> analyze table dept compute statistics;
Table analyzed.
SQL> alter session set "_subquery_pruning_cost_factor"=1;
Session altered.
SQL> alter session set "_subquery_pruning_reduction"=100;
Session altered.
SQL> select /*+ ordered use_hash(p) */ col1
from dept d, range_part p
where d.deptno = p.col1
and d.loc='DALLAS'; 2 3 4
no rows selected
SQL> explain plan for
select /*+ ordered use_hash(p) */ col1
from dept d, range_part p
where d.deptno = p.col1
and d.loc='DALLAS'; 2 3 4 5
Explained.
SQL> @?/rdbms/admin/utlxplp.sql
Plan Table
--------------------------------------------------------------------------------
| Operation | Name | Rows | Bytes| Cost | TQ |IN-OUT|
PQ Distrib | Pstart| Pstop |
--------------------------------------------------------------------------------
----------------------------
| SELECT STATEMENT | | 1 | 23 | 3 | | |
| | |
| HASH JOIN | | 1 | 23 | 3 | | |
| | |
Plan Table
--------------------------------------------------------------------------------
| TABLE ACCESS FULL |DEPT | 1 | 10 | 1 | | |
| | |
| PARTITION RANGE ITERATOR | | | | | | |
| KEY | KEY |
| TABLE ACCESS FULL |RANGE_PAR | 84 | 1K| 1 | | |
| KEY | KEY |
--------------------------------------------------------------------------------
Plan Table
--------------------------------------------------------------------------------
----------------------------
8 rows selected.
why did the optimizer do a partition elimination in this case when using hash join?
q3) I have
select column_name, num_distinct from user_tab_columns
2 where table_name='SUBMISSION_FILE'
3 and column_name in ('DT_CR','LATEST_STATUS_CHANGE_DT');
DT_CR 9120
LATEST_STATUS_CHANGE_DT 9120
We get around 10000 submission files and around 8 million transaction details associated with those submission files.
partition key is dt_cr. But the biggest problem is
DT_CR on submission files and transaction details can be same or have a difference of 1 day between them. The files get there status changed in between and for reporting purposes we have to look at latest_status_change_dt and not dt_cr. So how can we write queries so that it does partition elimination when doing a join? NL will not help ...
Thanks
December 26, 2002 - 12:27 pm UTC
q1) you have to think about how NL's are *done*
for x in ( select * from outer_table )
loop
do some query against inner table
end loop
vs how a hash join is done
hash t1
hash t2
put them together
totally -- very different logic.... It is trivial to use partition elimination against the inner table using NL join - it is like running a correlated subquery. hash operations are "en-mass", bulk operations. We would have to:
select distinct dt_cr from outer table
rewrite query against inner table to say "where dt_cr = :x or dt_cr = :y...."
sometimes we do, sometimes we say "not worth it"
q2) because your artificially induced it to with the undocumented parameters. There isn't enough data in any of those tables to warrant what work we are doing unless you artificially make it do it that way. You have no data, no stats maybe even -- worth doing this extra work for.
q3) Do you have STATS on these objects??? Here is my test case:
ops$tkyte@ORA817DEV> drop table small_table;
Table dropped.
ops$tkyte@ORA817DEV> drop table big_table;
Table dropped.
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> create table small_table ( latest_status_dt date, dt_cr date, subm_id number );
Table created.
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> create table big_table ( dt_cr date, subm_id number, data char(255) )
2 partition by range( dt_cr )
3 ( partition p01 values less than (to_date('01012002','ddmmyyyy') ),
4 partition p02 values less than (to_date('01022002','ddmmyyyy') ),
5 partition p03 values less than (to_date('01032002','ddmmyyyy') ),
6 partition p04 values less than (to_date('01042002','ddmmyyyy') ),
7 partition p05 values less than (to_date('01052002','ddmmyyyy') ),
8 partition p06 values less than (to_date('01062002','ddmmyyyy') ),
9 partition p07 values less than (to_date('01072002','ddmmyyyy') ),
10 partition p08 values less than (to_date('01082002','ddmmyyyy') ),
11 partition p09 values less than (to_date('01092002','ddmmyyyy') ),
12 partition p10 values less than (to_date('01102002','ddmmyyyy') ),
13 partition p11 values less than (to_date('01112002','ddmmyyyy') ),
14 partition p12 values less than (to_date('01122002','ddmmyyyy') )
15 )
16 /
Table created.
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> exec dbms_stats.set_table_stats( user, 'SMALL_TABLE', numrows => 10000, numblks => 100 );
PL/SQL procedure successfully completed.
ops$tkyte@ORA817DEV> exec dbms_stats.set_table_stats( user, 'BIG_TABLE', numrows => 10000000, numblks => 100000 );
PL/SQL procedure successfully completed.
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> delete from plan_table;
5 rows deleted.
ops$tkyte@ORA817DEV> set echo off
Explained.
Plan Table
--------------------------------------------------------------------------------
| Operation | Name | Rows | Bytes| Cost | Pstart| Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT | | 26K| 3M| 3073 | | |
| HASH JOIN | | 26K| 3M| 3073 | | |
| TABLE ACCESS FULL |SMALL_TAB | 26 | 806 | 3 | | |
| PARTITION RANGE ITERATOR| | | | | KEY | KEY |
| TABLE ACCESS FULL |BIG_TABLE | 10M| 953M| 2879 | KEY | KEY |
--------------------------------------------------------------------------------
8 rows selected.
I get the *same* results in 817 and 9iR2.
I'm guessing you don't have any or they are totally wrong.
what does this mean?
A reader, December 26, 2002 - 11:43 am UTC
Tom,
"(eg: populated off of data
dictionary data or some procedural process)" what does this mean?
December 26, 2002 - 1:27 pm UTC
statistics basically.
sorry, mismatch in cut and paste
A reader, December 26, 2002 - 11:52 am UTC
SQL> select * from dept;
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 RESEARCH OPERATIONS
SQL> update dept set loc='DALLAS' where deptno=50;
1 row updated.
SQL> commit;
Commit complete.
SQL> select column_name, num_distinct from user_tab_columns
2 where table_name='DEPT';
DEPTNO 5
DNAME 4
LOC 5
SQL> analyze table dept compute statistics;
Table analyzed.
SQL> /
Table analyzed.
SQL> select column_name, num_distinct from user_tab_columns
2 where table_name='DEPT';
DEPTNO 5
DNAME 4
LOC 4
SQL> select /*+ ordered use_hash(p) */ col1
from dept d, range_part p
where d.deptno = p.col1
and d.loc='DALLAS'; 2 3 4
no rows selected
SQL> explain plan for
select /*+ ordered use_hash(p) */ col1
from dept d, range_part p
where d.deptno = p.col1
and d.loc='DALLAS'; 2 3 4 5
Explained.
SQL> @?/rdbms/admin/utlxplp.sql
| Operation | Name | Rows | Bytes| Cost | TQ |IN-OUT|
PQ Distrib | Pstart| Pstop |
--------------------------------------------------------------------------------
----------------------------
| SELECT STATEMENT | | 2 | 44 | 3 | | |
| | |
| HASH JOIN | | 2 | 44 | 3 | | |
| | |
| TABLE ACCESS FULL |DEPT | 2 | 18 | 1 | | |
| | |
| PARTITION RANGE ALL | | | | | | |
| 1 | 4 |
| TABLE ACCESS FULL |RANGE_PAR | 84 | 1K| 1 | | |
| 1 | 4 |
--------------------------------------------------------------------------------
----------------------------
8 rows selected.
SQL> alter session set "_subquery_pruning_cost_factor"=1;
Session altered.
SQL> > alter session set "_subquery_pruning_reduction"=100;
SP2-0734: unknown command beginning "> alter s..." - rest of line ignored.
SQL> alter session set "_subquery_pruning_reduction"=100;
Session altered.
SQL> explain plan for
select /*+ ordered use_hash(p) */ col1
from dept d, range_part p
where d.deptno = p.col1
and d.loc='DALLAS'; 2 3 4 5
Explained.
SQL> alter system flush shared_pool;
System altered.
SQL> explain plan for
select /*+ ordered use_hash(p) */ col1
from dept d, range_part p
where d.deptno = p.col1
and d.loc='DALLAS'; 2 3 4 5
Explained.
SQL> @?/rdbms/admin/utlxplp.sql
| Operation | Name | Rows | Bytes| Cost | TQ |IN-OUT|
PQ Distrib | Pstart| Pstop |
--------------------------------------------------------------------------------
----------------------------
| SELECT STATEMENT | | 2 | 44 | 3 | | |
| | |
| HASH JOIN | | 2 | 44 | 3 | | |
| | |
| TABLE ACCESS FULL |DEPT | 2 | 18 | 1 | | |
| | |
| PARTITION RANGE ITERATOR | | | | | | |
| KEY | KEY |
| TABLE ACCESS FULL |RANGE_PAR | 84 | 1K| 1 | | |
| KEY | KEY |
--------------------------------------------------------------------------------
----------------------------
8 rows selected.
Thanks
December 26, 2002 - 1:31 pm UTC
see above -- me thinks you have no stats or the stats are wrong or the stats are telling us "don't do that"
Excellent!!!
A reader, December 26, 2002 - 2:16 pm UTC
Tom,
Thanks for your very very valuable input. I did analyze all the partitions....but think somehow my stats got screwed up.
It now does use hash join.
Thanks again....
Another case....
A reader, December 26, 2002 - 3:19 pm UTC
Tom,
For a given dt_cr of submission file, The details can be either
a) on the date created as of submission file (dt_cr)
b) one date prior the submission file dt_cr (dt_cr - 1)
c) one day after the submission file (dt_cr + 1)
so i query:
explain plan for select /*+ ordered use_hash(b) */ *
from submission_file1 a, submission_detail1 b
where a.latest_status_change_dt between to_date('12/01/2002','mm/dd/yyyy') and
to_date('12/05/2002','mm/dd/yyyy')
and a.subm_id = b.subm_id
and b.dt_cr between trunc(a.dt_cr) - 1 and trunc(a.dt_cr) + 1;
SQL> @?/rdbms/admin/utlxplp.sql
| Operation | Name | Rows | Bytes| Cost | TQ |IN-OUT|
PQ Distrib | Pstart| Pstop |
--------------------------------------------------------------------------------
----------------------------
| SELECT STATEMENT | | 6K| 1M| 123872 | | |
| | |
| HASH JOIN | | 6K| 1M| 123872 | | |
| | |
| TABLE ACCESS FULL |SUBMISSIO | 26 | 2K| 16 | | |
| | |
| PARTITION RANGE ALL | | | | | | |
| 1 | 12 |
| TABLE ACCESS FULL |SUBMISSIO | 10M| 1G| 15178 | | |
| 1 | 12 |
--------------------------------------------------------------------------------
----------------------------
does the partition elimination not work for this?
December 26, 2002 - 3:38 pm UTC
Here -- it would be even less likely.
you are performing functions on columns (stats are almost useless there)
We have no idea what domain of b.dt_cr's are likely or possible here.
Now, since submission_file1 is a "small table" -- only 10k rows -- you can do this easily enough:
ops$tkyte@ORA920> delete from plan_table;
5 rows deleted.
ops$tkyte@ORA920> explain plan
2 for
3 select * <b>/* Look ma -- no hints!!!*/</b>
4 from small_table a, big_table b,<b>
5 ( select min(trunc(dt_cr)-1) min_dt_cr,
6 max(trunc(dt_cr)+1) max_dt_cr
7 from small_table
8 where latest_status_dt between to_date('12/01/2002','mm/dd/yyyy')
9 and to_date('12/05/2002','mm/dd/yyyy')
10 ) parms</b>
11 where a.latest_status_dt between to_date('12/01/2002','mm/dd/yyyy')
and to_date('12/05/2002','mm/dd/yyyy')
12 and b.dt_cr between trunc(a.dt_cr)-1 and trunc(a.dt_cr)+1<b>
13 and b.dt_cr between parms.min_dt_cr and parms.max_dt_cr</b>
14 and a.subm_id = b.subm_id
15 /
Explained.
ops$tkyte@ORA920>
ops$tkyte@ORA920> set echo off
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------
| Id | Operation | N| Rows | Bytes | Cost | Pstart| Pstop |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 149 | 9642 | | |
|* 1 | HASH JOIN | | 1 | 149 | 9642 | | |
|* 2 | TABLE ACCESS FULL | SM| 25 | 775 | 11 | | |
| 3 | NESTED LOOPS | | 25000 | 2880K| 9630 | | |
| 4 | VIEW | | 1 | 18 | 11 | | |
|* 5 | FILTER | | | | | | |
| 6 | SORT AGGREGATE | | 1 | 18 | | | |
|* 7 | TABLE ACCESS FULL | SM} 25 | 450 | 11 | | |
| 8 | PARTITION RANGE ITERATOR| | | | | KEY | KEY |
|* 9 | TABLE ACCESS FULL | BI| 3 | 300 | 9619 | KEY | KEY |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."SUBM_ID"="B"."SUBM_ID")
filter("B"."DT_CR">=TRUNC("A"."DT_CR")-1 AND "B"."DT_CR"<=TRUNC("A"."DT_CR")+1)
2 - filter("A"."LATEST_STATUS_DT">=TO_DATE('2002-12-01 00:00:00', 'yyyy-mm-dd hh24:mi
:ss') AND "A"."LATEST_STATUS_DT"<=TO_DATE('2002-12-05 00:00:00', 'yyyy-mm-
dd hh24:mi:ss'))
5 - filter(TO_DATE('2002-12-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')<=TO_DATE('2002-12-
05 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
7 - filter("SMALL_TABLE"."LATEST_STATUS_DT">=TO_DATE('2002-12-01 00:00:00', 'yyyy-mm-
dd hh24:mi:ss') AND "SMALL_TABLE"."LATEST_STATUS_DT"<=TO_DATE('2002-12-05
00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
9 - filter("B"."DT_CR">="PARMS"."MIN_DT_CR" AND "B"."DT_CR"<="PARMS"."MAX_DT_CR")
Note: cpu costing is off
32 rows selected.
Question on example tables
Tommy Williamson, February 26, 2003 - 6:01 pm UTC
Tom,
I created the small_table and big_table like your example and set the statistics. However I was suprised at the explain plan when I ran this SQL:
SQL> explain plan for SELECT *
2 FROM big_table b
3 WHERE b.dt_cr BETWEEN TO_DATE('11/01/2002','mm/dd/yyyy') AND TO_DATE('11/20/2002','mm/dd/yyyy');
Explained.
SQL> @?/RDBMS/ADMIN/UTLXPLS
Plan Table
--------------------------------------------------------------------------------
| Operation | Name | Rows | Bytes| Cost | Pstart| Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT | | 25K| 2M| 1266 | | |
| TABLE ACCESS FULL |BIG_TABLE | 25K| 2M| 1266 | 12 | 12 |
--------------------------------------------------------------------------------
It appears to only be reading the last partition like I expected (Pstart = 12 Pstop = 12), but why doesn't PARTITION RANGE show up in the plan?
Thanks
February 27, 2003 - 7:25 am UTC
it is implied -- pstart/pstop is showing it. it is doing the right thing.
Partition elemination - what's wrong???
Michael, November 05, 2003 - 4:53 am UTC
Hi Tom!
I did the following:
SQL> create table parttest (
2 part_key number(38) not null,
3 data varchar2(10)
4 )
5 partition by range (part_key) (
6 partition p1 values less than(1000) tablespace tbs_1,
7 partition p2 values less than(2000) tablespace tbs_2
8 );
Tabelle wurde angelegt.
SQL> analyze table parttest compute statistics
2 for table for all columns for all indexes;
Tabelle wurde analysiert.
SQL> alter tablespace tbs_1 offline normal;
Tablespace wurde geändert.
SQL> var a number
SQL> var b number
SQL> var c number
SQL> execute :a := 1000; :b := 500; :c := 1500;
PL/SQL-Prozedur wurde erfolgreich abgeschlossen.
SQL> select count(*) from parttest
2 where part_key >= :a
3 and part_key between :b and :c;
COUNT(*)
----------
0
This statement works, but:
SQL> select count(*)
2 from (select * from parttest
3 where part_key >= :a)
4 where part_key between :b and :c;
from (select * from parttest
*
FEHLER in Zeile 2:
ORA-00376: Datei 16 kann zur Zeit nicht gelesen werden
ORA-01110: Datendatei 16: 'E:\APPS\ORACLE\ORADATA\ORA92DB\TBS_1.ORA'
Seldom, what's the difference?
SQL> explain plan for
2 select count(*) from parttest
3 where part_key >= :a
4 and part_key between :b and :c;
EXPLAIN PLAN ausgeführt.
SQL> @?/RDBMS/ADMIN/UTLXPLS
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 2 | | |
| 1 | SORT AGGREGATE | | 1 | 13 | | | |
|* 2 | FILTER | | | | | | |
| 3 | PARTITION RANGE ITERATOR| | | | | KEY | KEY |
|* 4 | TABLE ACCESS FULL | PARTTEST | 1 | 13 | 2 | KEY | KEY |
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TO_NUMBER(:Z)<=TO_NUMBER(:Z) AND TO_NUMBER(:Z)<=TO_NUMBER(:Z))
4 - filter("PARTTEST"."PART_KEY">=TO_NUMBER(:Z) AND
"PARTTEST"."PART_KEY">=TO_NUMBER(:Z) AND "PARTTEST"."PART_KEY"<=TO_NUMBER(:Z))
Note: cpu costing is off
19 Zeilen ausgewählt.
SQL> explain plan for
2 select count(*)
3 from (select * from parttest
4 where part_key >= :a)
5 where part_key between :b and :c;
EXPLAIN PLAN ausgeführt.
SQL> @?/RDBMS/ADMIN/UTLXPLS
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 2 | | |
| 1 | SORT AGGREGATE | | 1 | 13 | | | |
|* 2 | FILTER | | | | | | |
| 3 | PARTITION RANGE ITERATOR| | | | | KEY | KEY |
|* 4 | TABLE ACCESS FULL | PARTTEST | 1 | 13 | 2 | KEY | KEY |
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TO_NUMBER(:Z)>=TO_NUMBER(:Z) AND TO_NUMBER(:Z)<=TO_NUMBER(:Z))
4 - filter("PARTTEST"."PART_KEY">=TO_NUMBER(:Z) AND
"PARTTEST"."PART_KEY"<=TO_NUMBER(:Z) AND "PARTTEST"."PART_KEY">=TO_NUMBER(:Z))
Note: cpu costing is off
19 Zeilen ausgewählt.
So, what is the difference between these queries??
I would like to use a view which is identical with the inline view of the second (failing) query, so that i can take offline partitions and modify the view so that the endusers (which are using this view) don't notice the change (and moreover don't get any ORA-00376 error messages).
Regards.
November 05, 2003 - 9:14 am UTC
the first query is filtering based on the "where part_key >= :a" first AND THEN on the between.
The second query is filtering on the between AND THEN the "where part_key >= :a"
the "where part_key >= :a" precluded the first partition.
the "between" does not.
it is the difference between:
4 - filter("PARTTEST"."PART_KEY">=TO_NUMBER(:Z) AND
<b>"PARTTEST"."PART_KEY">=TO_NUMBER(:Z) AND
"PARTTEST"."PART_KEY"<=TO_NUMBER(:Z))</b>
and
4 - filter(<b>"PARTTEST"."PART_KEY">=TO_NUMBER(:Z) AND
"PARTTEST"."PART_KEY"<=TO_NUMBER(:Z) AND </b>
"PARTTEST"."PART_KEY">=TO_NUMBER(:Z))
<b>Now, as much as I "don't like it" -- since it will have side effects on the optimizer in other places, we can request that the "view" not be merged into</b>
It can be very tricky hiding hints in views, it can lead to suboptimal plans further on down the road. It accomplishes your goal, but that might not always be "good".
Here is a small simulation based on your case. Since you cannot "bind" into a view, i'll use an application context -- that is what your end uses will have to do...
ops$tkyte@ORA920PC> alter tablespace users offline normal;
Tablespace altered.
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> var a number
ops$tkyte@ORA920PC> var b number
ops$tkyte@ORA920PC> var c number
ops$tkyte@ORA920PC> execute :a := 1000; :b := 500; :c := 1500;
PL/SQL procedure successfully completed.
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> exec dbms_application_info.set_client_info( 1000 );
PL/SQL procedure successfully completed.
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> create or replace view v
2 as
3 select /*+ NO_MERGE */ *
4 from parttest
5 where part_key >= to_number( sys_context( 'userenv', 'client_info' ) )
6 /
View created.
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> select count(*) from parttest
2 where part_key >= :a
3 and part_key between :b and :c;
COUNT(*)
----------
0
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> select *
2 from (select /*+ NO_MERGE */ part_key from parttest
3 where part_key >= :a)
4 where part_key between :b and :c
5 /
no rows selected
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> select count(*)
2 from (select /*+ NO_MERGE */ part_key from parttest
3 where part_key >= :a)
4 where part_key between :b and :c;
COUNT(*)
----------
0
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> select count(*)
2 from v
3 where part_key between :b and :c;
COUNT(*)
----------
0
Another idea
Michael, November 07, 2003 - 4:17 am UTC
Hi Tom!
The trick with the hint works great.
But there seems to be a way to do it without the hint, but i don't know if it is better than the hint ...
First i added antoher partition to the table parttest:
1 alter table parttest
2 add partition p3 values less than (3000)
3* tablespace tbs_2
SQL> /
Tabelle wurde geändert.
(There a still no rows in the table.)
SQL> analyze table parttest compute statistics
2 for table for all columns for all indexes;
Tabelle wurde analysiert.
Now i turn tablespace TBS_1 offline again.
SQL> alter tablespace tbs_1 offline normal;
Tablespace wurde geändert.
Now i know that partition p1 is not available. Then i modify the view v from:
create or replace view v as
select part_key, data
from parttest partition (p1)
union all
select part_key, data
from parttest partition (p2)
union all
select part_key, data
from parttest partition (p3);
To:
create or replace view v as
/* select part_key, data
from parttest partition (p1)
union all */
select part_key, data
from parttest partition (p2)
union all
select part_key, data
from parttest partition (p3);
I explicitly tell Oracle which partitions it should (not) use.
SQL> var b number
SQL> var c number
SQL> execute :b := 500; :c := 1500;
PL/SQL-Prozedur wurde erfolgreich abgeschlossen.
SQL> set autotrace on
SQL> select *
2 from v
3 where part_key between :b and :c;
Es wurden keine Zeilen ausgewählt
Ausführungsplan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=20)
1 0 FILTER
2 1 VIEW OF 'V' (Cost=2 Card=1 Bytes=20)
3 2 UNION-ALL (PARTITION)
4 3 FILTER
5 4 TABLE ACCESS (FULL) OF 'PARTTEST' (Cost=2 Card=1 Bytes=20)
6 3 FILTER
7 6 TABLE ACCESS (FULL) OF 'PARTTEST' (Cost=2 Card=1 Bytes=20)
Statistiken
----------------------------------------------------------
96 recursive calls
0 db block gets
22 consistent gets
0 physical reads
0 redo size
283 bytes sent via SQL*Net to client
368 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
0 rows processed
This works, but why is the third partition also scanned, since the data simply cannot be in there? I think only partition p2 should be scanned.
Are there other dangers involved in this "name-the- partitons-exclicitly-in-the-view"-approach except that it is not easy to administer and "ugly".
Regards.
November 07, 2003 - 9:33 am UTC
this seems to be an apples/oranges comparision? where is the predicate in the view?
Predicate necessary?
Michael, November 08, 2003 - 8:22 am UTC
The second view (with the partitions) doesn't have the predicate because i exclicitly omit the unavailable partition with all the rows with partkey < 1000.
It is simply not necessary. (Right?)
What i want to achieve through the view is, that the unavailable partition is not used.
November 08, 2003 - 10:28 am UTC
Oh, i see, you want to do DDL -- i overlooked that.
well, what I'm seeing here is that you are silently removing data that should be queried from being queried!
user asked for between 500 and 1500 -- you take away 500..999 and dont tell them! that seems like "a bad idea"??
i thought this was just a bad predicate at first (where partkey > 1000 and partkey between 500 and 1500) but now I see you want to "hide" data from them.
wy not just exchange the partition with an empty table -- work on the "full table" and exchange it back in?
Michael, November 10, 2003 - 3:24 am UTC
Yes, if the user wouldn't know about the missing data, it would be "a really bad idea"!
But the user will know that the data with partkey < 1000 isn't queried.
In reality partkey is a date and each partition contains the data from one year. The users know: We are in 2003, the data from the actual year and the data from the last two years is online, so the data from year 2000 and before is offline.
For example in january 2004 i want to offline the data from 2001. To achive this i plan to do the following:
1. Inform the end users about the "offlining" of the 2001 data.
2. create or replace v, so that the data from 2001 isn't queried. Here i don't know, which method is better: predicate or union all on the available partitions (See my previous reply)???
3. alter tablespace DATA_2001 offline normal;
4. Archiving the data files from the DATA_2001 tablespace.
If an end users needs the data from 2001 (or before) - rare - i will restore the required data files, turn the tablespace DATA_2001 online again and then change the view so that the data is visible and can be queried by the user.
When the end user finish the work with the old data, i archive it again.
Is this a "bad idea"?
November 10, 2003 - 7:43 am UTC
you say
"But the user will know that the data with partkey < 1000 isn't queried"
how??? their predicate was between 500 and 1500 !!! i would surely expect that partkey < 1000 would in fact be queried.
if you can inform the users of everything, fine -- just smells "fishy" to me. end users are notorious for ignoring emails, alert/warning screens, etc. (ask me, I'm an enduser as we all are. I just hit delete on sendmails (broadcasts), those little flash screens on applications? ignore them, etc -- its our nature)
I would have the queries fail -- so the user knows "oh, i was asking for stuff that ain't around, better change my question so that the report doesn't provide misleading information as it gets printed and sent around"
but you know your environment better then me.
Other part of the story ...
Michael, November 10, 2003 - 8:53 am UTC
Well, you are right - an error message is definitly better than a "wrong" (= here: not the expected data was queried) query result.
But - i know it's "funny" -, it were the end users, that said: "We don't want to see an error message about unavailable data, we just want results!"
I think the best i can do is to change the end users attitude about this issue, so that they understand why an error message can be better than a (wrong!) result.
(Even when this probably means more work for me, because then they will immediately realize that they need the old data ;-)
Get MIN value on the partition key
A reader, June 14, 2004 - 12:12 pm UTC
Hi Tom,
I have a table TXNS which is partitioned on a DATE column TXNDT.
PARTITION BY RANGE (TXNDT)
(
PARTITION Y2005 VALUES LESS THAN (TO_DATE(' 2005-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
TABLESPACE DUCO_OWNER
,
PARTITION Y2010 VALUES LESS THAN (TO_DATE(' 2010-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
TABLESPACE DUCO_OWNER
,
...
...
PARTITION Y2080 VALUES LESS THAN (MAXVALUE)
NOLOGGING
NOCOMPRESS
TABLESPACE DUCO_OWNER
)
I want to get the MIN(TXNDT) from this table, but surprisingly the query is not pruned to the partition containing the smallest range.
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
SELECT STATEMENT Optimizer Mode=CHOOSE 1 4398
SORT AGGREGATE 1 8
TABLE ACCESS FULL TXNS 480 K 3 M 4398
Do you have any idea why?
June 14, 2004 - 1:16 pm UTC
create that table.
then insert a single row and only this row -- with a date of something in the year 2080....
now does it make sense?
re: Get MIN value on the partition key
A reader, June 14, 2004 - 1:40 pm UTC
Thanks. Now I understand. However I was expecting Oracle to do sth. smart, just like "Hey, I have found some values in the lowest range, so I will just skip the other ranges." Just like what it does when scanning an index.
Is there a hint that I can use to direct the query to use only one partition, like the PARTIION clause?
June 14, 2004 - 2:42 pm UTC
if it used only one partition -- it would get the wrong answer in the above case! that was my point.
if you know the first partition will always have data
select min(x) from p partition(partition_lowest);
Query scans all partitions...
Kiran Shah, July 05, 2004 - 4:34 am UTC
We have one table which is partitioned. Its definition is
*******************************************************
CREATE TABLE "CCIS"."LITIGANT"
("MAINCASE" "CCIS"."CASEOBJECT",
"LITIGANT_TYPE" CHAR(1) ,
"SERIALNUMBER" NUMBER(4),
"GENERATIONNUMBER" NUMBER(1),
"MEMBERNUMBER" NUMBER(2),
"ADVOCATE" "CCIS"."ADVOCATENTYPE" ,
"LITIGANTNAME" VARCHAR2(60) NOT NULL,
)
PARTITION BY RANGE (MAINCASE.SIDE)
(PARTITION CIVILSIDE VALUES less than ('D')
TABLESPACE LOBISCIVIL,
PARTITION OJSIDE VALUES less than ('P')
TABLESPACE LOBIS ,
PARTITION LOBISCRIMINAL VALUES less than ('S')
TABLESPACE LOBISCRIMINAL)
TABLESPACE "LOBIS"
NESTED TABLE "ADVOCATE" STORE AS ADVOCATE_STORE_NTAB ;
The CASEOBJECT has following structure...
CREATE OR REPLACE TYPE "CCIS"."CASEOBJECT" AS OBJECT
( "SIDE" CHAR(1),
"CASETYPE" VARCHAR2(5),
"CASEYEAR" NUMBER(4),
"CASENUMBER" NUMBER(6),
"CASESUBTYPE" VARCHAR2(2)
)
And nested table ADVOCATE has following sturcture...
CREATE OR REPLACE TYPE "CCIS"."ADVOCATEOBJECT" AS OBJECT
( "ADVOCATECODE" NUMBER(6),
"REPRESENTINGCAPACITY" NUMBER(2)
)
************************************************************
The litigant table has a primary key on maincase,litigant_type,serialnumber,generationnumber and membernumber
The nested table is indexed on nested_table_id and advocatecode
Now when we try to insert one row into nested table column ADVOCATE, it took too much time.
Plan table output is...
Plan Table
--------------------------------------------------------------------------------
| Operation | Name | Rows | Bytes| Cost | Pstart| Pstop |
--------------------------------------------------------------------------------
| INSERT STATEMENT | | 987K| 35M| 1836 | | |
--------------------------------------------------------------------------------
The SQL trace and tkprof shows following result...
********************************************************************************
insert into table(select advocate from litigant l1
where l1.maincase.side='C' and l1.maincase.casetype='ST' and
l1.maincase.caseyear=2004 and l1.maincase.casenumber=40 and
l1.serialnumber = 1 and l1.membernumber = 0 and l1.generationnumber = 1
and litigant_type='P')
values(1237,'1')
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 18 0 0
Execute 1 0.00 0.14 2 9 4 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.01 0.15 2 27 4 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 53
********************************************************************************
SELECT 1
FROM
"CCIS"."LITIGANT" WHERE "SYS_NC0001400015$" = :1 FOR UPDATE
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 5.51 49.63 66347 66363 62 0
Fetch 1 5.47 12.99 66347 66363 60 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 10.98 62.62 132694 132726 122 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 FOR UPDATE
2 PARTITION RANGE ALL PARTITION: START=1 STOP=3
2 TABLE ACCESS FULL LITIGANT PARTITION: START=1 STOP=3
*********************************************************************************
The least thing I understood from above is that my query scan all partition instead of single partition.
That is why query took so much time .
How can we force to search into one partition and improve query performance?
Thanks,
Kiran Shah
July 05, 2004 - 10:16 am UTC
concise, yet 100% complete is what is needed here. I cannot run your example without making lots of guesses and fixes.
you've sort of hacked out bits and pieces -- the sql isn't all sql, missing bits and pieces, no way for me to reproduce.....
I can say -- if I were serious enough to be using partitioning, I would not be using nested tables. nested tables are just parent child relations in disguise, they add yet another unique key to the parent table -- a 16 byte raw field. they add a psuedo foreign key to your nested table (that you have to remember to index yourself).
I would rethink "nested tables" as a true parent child relationship and build the tables yourself.
Continue from previous one...
Kiran Shah, July 06, 2004 - 7:05 am UTC
Sorry Tom for incomplete info. Giving below more detail info. so that you can re-produce them easily...
We are using Oracle 9.1 database.
Following is the definition of objects....
CREATE OR REPLACE TYPE "CCIS"."CASEOBJECT" AS OBJECT
( "SIDE" CHAR(1),
"CASETYPE" VARCHAR2(5),
"CASEYEAR" NUMBER(4),
"CASENUMBER" NUMBER(6),
"CASESUBTYPE" VARCHAR2(2)
)
CREATE OR REPLACE TYPE "CCIS"."ADVOCATEOBJECT" AS OBJECT
( "ADVOCATECODE" NUMBER(6),
"REPRESENTINGCAPACITY" NUMBER(2)
)
CREATE OR REPLACE TYPE "CCIS"."ADVOCATENTYPE"
AS TABLE OF "CCIS"."ADVOCATEOBJECT"
CREATE TABLE "CCIS"."LITIGANT"
("MAINCASE" "CCIS"."CASEOBJECT",
"LITIGANT_TYPE" CHAR(1) ,
"SERIALNUMBER" NUMBER(4),
"GENERATIONNUMBER" NUMBER(1),
"MEMBERNUMBER" NUMBER(2),
"ADVOCATE" "CCIS"."ADVOCATENTYPE" ,
"LITIGANTNAME" VARCHAR2(60) NOT NULL
)
PARTITION BY RANGE (MAINCASE.SIDE)
(PARTITION CIVILSIDE VALUES less than ('D')
TABLESPACE LOBISCIVIL,
PARTITION OJSIDE VALUES less than ('P')
TABLESPACE LOBIS ,
PARTITION LOBISCRIMINAL VALUES less than ('S')
TABLESPACE LOBISCRIMINAL)
TABLESPACE "LOBIS"
NESTED TABLE "ADVOCATE" STORE AS ADVOCATE_STORE_NTAB ;
(Note : Actual table contains more fields than shown here. However they are not relevant here, so they have been kept out.)
(Note : This LITIGANT table contains 3million records right now. And it is expanded day by day. And nested table also contains roughly same no. of rows)
Now I am firing following insert query first on LITIGANT table...
insert into litigant
values(caseobject('C','ST',2004,40,NULL),'P',1,1,0,advocatentype(),'KIRAN SHAH');
And then inserting advocate codes into nested table...using folowing query...
insert into table(select advocate from litigant l1
where l1.maincase.side='C' and l1.maincase.casetype='ST' and
l1.maincase.caseyear=2004 and l1.maincase.casenumber=40 and
l1.serialnumber = 1 and l1.membernumber = 0 and l1.generationnumber = 1
and litigant_type='P')
values(1237,'1')
Above query (insertion into nested table) takes too much time.
Plan table output shows only following one line.
Plan Table
--------------------------------------------------------------------------------
| Operation | Name | Rows | Bytes| Cost | Pstart| Pstop |
--------------------------------------------------------------------------------
| INSERT STATEMENT | | 987K| 35M| 1836 | | |
--------------------------------------------------------------------------------
The SQL trace and tkprof shows following result...
********************************************************************************
insert into table(select advocate from litigant l1
where l1.maincase.side='C' and l1.maincase.casetype='ST' and
l1.maincase.caseyear=2004 and l1.maincase.casenumber=40 and
l1.serialnumber = 1 and l1.membernumber = 0 and l1.generationnumber = 1
and litigant_type='P')
values(1237,'1')
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 18 0 0
Execute 1 0.00 0.14 2 9 4 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.01 0.15 2 27 4 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 53
********************************************************************************
SELECT 1
FROM
"CCIS"."LITIGANT" WHERE "SYS_NC0001400015$" = :1 FOR UPDATE
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 5.51 49.63 66347 66363 62 0
Fetch 1 5.47 12.99 66347 66363 60 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 10.98 62.62 132694 132726 122 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 FOR UPDATE
2 PARTITION RANGE ALL PARTITION: START=1 STOP=3
2 TABLE ACCESS FULL LITIGANT PARTITION: START=1 STOP=3
*********************************************************************************
I hope this is as per your requirement.
As per my understanding, tkprof output shows that query scans all partitions. And that is why it takes more time.
So what modification is required for better performance ?
Thanks,
Kiran Shah
July 06, 2004 - 7:54 am UTC
what indexes are available.
You see, that column:
SELECT 1
FROM
"CCIS"."LITIGANT" WHERE "SYS_NC0001400015$" = :1 FOR UPDATE
is the "magic" column added to your base table by this nested table thingy. It is a hidden, 16 byte raw column. It is "unique". It is to have a unique index on it (my query plan shows:)
SELECT 1
FROM
"CCIS"."LITIGANT" WHERE "SYS_NC0001100012$" = :1 FOR UPDATE
...
Rows Row Source Operation
------- ---------------------------------------------------
1 FOR UPDATE (cr=0 r=0 w=0 time=2 us)
1 INDEX UNIQUE SCAN SYS_C007722 (cr=1 r=0 w=0 time=14 us)(object id 129018)
Soooo, I'm thinking someone dropped the unique constraint on this hidden column for whatever reason.
Please look into that -- make sure that hidden column has the proper constraint on it and that the unique index that needs be in place -- is.
Perfect analysis...
Kiran Shah, July 09, 2004 - 7:56 am UTC
Dear Tom,
Thanks for perfect - to-the point diagnosis...
I put UNIQUE constraint on hidden column and everything works fine...
Thanks again,
Kiran Shah
July 09, 2004 - 8:11 am UTC
Now, you need to find the person(s) that removed it and ask them "what were you THINKING"
Partition Pruning
Wayne, November 01, 2004 - 11:13 pm UTC
Tom,
I am seeking your expert idea here (Solaris 5.8, Oracle 9.2.0.5):
I have a 50M row table analyzed using
DBMS_STATS.GATHER_TABLE_STATS with the following settings:
CASCADE=>TRUE,
ESTIMATE_PERCENT => 5);
Then I issued the following query against it:
SELECT COUNT(*), CLAIM_TYPE_KEY FROM CLAIM_FACT PARTITION (P200409) -- about 2M rows
GROUP BY CLAIM_TYPE_KEY
Sure enough, it's executing the best plan by using the bitmap index on the CLAIM_TYPE_KEY (fast full scan and bitmap conversion), it returns in 0.03 seconds;
In real world, however, we cannot ask users to specify partition names, since the table is range partitioned monthly by TRANS_DATE, I changed it to:
SELECT COUNT(*), CLAIM_TYPE_KEY FROM CLAIM_FACT
WHERE TRANS_DATE BETWEEN '1-SEP-2004' AND '30-SEP-2004'
GROUP BY CLAIM_TYPE_KEY
But this time, it's doing a full table scan and return the result in 18 seconds.
My question is:
What could have prevented it from finding the right plan (partition elimination)?
I noticed that the following settings on the DB:
optimizer_index_caching integer 80
optimizer_index_cost_adj integer 100
(We probably should ask DBA to lower the second one to make it leaning more toward using index).
Thanks,
November 02, 2004 - 7:24 am UTC
do this:
delete from plan_table;
explain plan for <that query>;
select * from table(dbms_xplan.display);
and format the results so it fits and put it here.
Partition Pruning
Wayne, November 02, 2004 - 1:43 pm UTC
Tom,
Thanks in advance for looking into that.
Here's actual SQL and their explain plans (reformatted).
The whole table has 58M rows.
That 200409 partition has about 1.9M rows.
---------------------------------------------
SELECT COUNT(*), CLM_RESP_KEY FROM XPS_PTCLAIM_FACT PARTITION (P200409)
GROUP BY CLM_RESP_KEY
<<OUTPUT>>
COUNT(*) CLM_RESP_KEY
---------- ------------
937866 1
88333 2
946687 3
4005 4
Elapsed: 00:00:00.01
-----------------------------------------------------------------------------
|Id | Operation | Name |Rows|Bytes|Cost|Pstart|Pstop|
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 12 |69 | |
| 1 | SORT GROUP BY NOSORT | | 4 | 12 |69 | |
| 2 | BITMAP CONVERSION COUNT| | | | | |
| 3 | BITMAP INDEX FULL SCAN| CLMRESP_BIDX | | | | 40 | 40
-----------------------------------------------------------------------------
Note: cpu costing is off
11 rows selected.
SELECT COUNT(*), CLM_RESP_KEY FROM XPS_PTCLAIM_FACT
WHERE TRANS_DATE BETWEEN '1-sep-2004' AND '30-sep-2004'
GROUP BY CLM_RESP_KEY
<< OUTPUT>>
COUNT(*) CLM_RESP_KEY
---------- ------------
937866 1
88333 2
946687 3
4005 4
Elapsed: 00:00:19.02
------------------------------------------------------------------------------
| Id | Operation |Name |Rows |Bytes|Cost |Pstart|Pstop |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 44 |4682 | | |
| 1 | SORT GROUP BY | | 4 | 44 |4682 | | |
|* 2 | TABLE ACCESS FULL |XPS_PTCLAIM_FACT|1983K| 20M|3199 | 40 | 40|
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("XPS_PTCLAIM_FACT"."TRANS_DATE"<=TO_DATE('2004-09-30 00:00:00', 'yyyy-mm-dd
hh24:mi:ss'))
Note: cpu costing is off
16 rows selected.
November 03, 2004 - 6:25 am UTC
Ok, it is doing the partition elimination, that is not the issue.
Now, put to_date() on the strings and redo this -- compare a date to a date, not to a string, lets see what then.
Partition Pruning/Date Conversion
Wayne, November 03, 2004 - 12:08 pm UTC
Tom,
I applied the to_date('9/1/2004', 'mm/dd/yyyy') functions and xplan is exactly the same as before and it takes about 17-23 seconds to run (versus 0.01 seconds with hardcoded partition name).
Here're some of the parameters:
NAME TYPE VALUE
optimizer_dynamic_sampling integer 1
optimizer_features_enable string 9.2.0
optimizer_index_caching integer 80
optimizer_index_cost_adj integer 100
optimizer_max_permutations integer 2000
optimizer_mode string CHOOSE
bitmap_merge_area_size integer 1048576
create_bitmap_area_size integer 8388608
November 04, 2004 - 9:23 am UTC
if you export the table with rows=no grants=no
and you export the statistics
and you compress those files
and send them to me, I'll see if I see anything "obvious", but I would suggest concurrently filing a tar with support.
partition pruning
Rob K, November 04, 2004 - 11:18 am UTC
I believe Oracle is executing the WayneÂ’s query correctly.
The first query contains the partition name, I believe Oracle executes the query as if it were an isolated table, thus allowing a full bitmap scan on CLMRESP_BIDX for that partition.
The second query contains a filter on the trans_date, while this is part of the partition key Oracle has no way to determine if all the rows will be returned for the partition, thus it cannot use a bitmap conversion count, so it does a full table scan on the partition.
November 05, 2004 - 1:26 pm UTC
sound of HAND SMACKING FOREHEAD
of course, the date isn't in the bitmap! and not every row would count in the predicate given.
sometimes it is the easiest things -- the two queries are not even the same. we'd have to go from the bitmap to each row in order to answer the query with the dates.
Partition Pruning
Wayne, November 04, 2004 - 7:43 pm UTC
Hi Tom,
I tried to send you the exp files for that data and the stats table, but it was rejected because of the zip files (not accepting .zip or .Z files).
What should I do?
to wayne
A reader, November 05, 2004 - 1:57 pm UTC
just rename them (file.zip to file.zip.rename) oracle
email rejectss zipped files due to some security reason.
November 05, 2004 - 5:41 pm UTC
rejects lots of file types due to outlook end users :)
pretty much anything that could be "executed" is rejected due to viruses running rampant.
Partition Elimination
Wayne, November 05, 2004 - 3:59 pm UTC
Tom,
Thanks for the followup.
You are right that these queries are not the same. My questions are:
1. The table is partitioned by TRANS_DATE. Oracle might think the condition:
TRANS_DATE BETWEEN '1-SEP-2004' AND '30-SEP-2004'
might leave some records off with trans_dates between '30-sep-2004' and '30-sep-2004'+0.99999 (the date is actually truncated so it is not a problem), but even if I changed the query to:
select count(*), clm_resp_key
from xps_ptclaim_fact
where trans_date >= first_date_of_partition
and trans_date < first_date_of_next_partition
group by clm_resp_key
It's still taking 20 seconds. Will the optimizer be smart enough to know that this is exactly the condition it uses to find the right partition to put incoming new records in so use the same condition to eliminate the partition when executing this query (i.e. execute exactly the same as if the partition name is hardcoded)?
2. More importantly, what suggestions do you recommend on the index structure or query structure so that we can make it run the bitmap conversion (returns within mili seconds)? Most of our queries use the TRANS_DATE as a condition and group by something else.
(Well, 19 seconds is not a bad response time anyway).
Thanks in advance.
November 05, 2004 - 5:56 pm UTC
1) I'm filing two bugs on this this weekend -- it "should" be able to -- but it is not. The developers have already looked at this and identified two issues that need looking at.
The fact is -- it eliminated the partition 100% (just fixing terminology here). What it didn't do is realize that every row in the partition would be processed, would meet the predicate -- hence the bitmap index could be used.
this is not a partition pruning issue.
2) do your end users ALWAYS hit:
o a single partition
o all rows in that partition
Partition Elimination
Wayne, November 05, 2004 - 7:33 pm UTC
Tom,
You are right, the partition elimination does work, and we are discussing here is what happend after the elimination is done.
Most of our reports are monthly (partition-wise), some are weekly (so they hit single partition most of the weeks in a month).
In annual reports when they need to hit 12 partitions and assuming the aggregate MVs are not there yet (so no query rewrite), I think the optimizer should still be able to run(in parallel) 12 partition-wise queries and adds them togher, if it is faster than doing a whole 12 partition group-by operation.
Your second question is right to the point (That's why you are the best!).
No, we don't. In most cases, we only run reports for a specific client (or a group of several), so in these cases, we cannot use the simple bitmap conversion anyway.
What I am also wondering is why a query like the following is not doing a bitmap conversion (Do it and throw 4 away):
SELECT COUNT(*), CLM_RESP_KEY
FROM CLAIM_FACT PARTITION (P200409)
WHERE CLM_RESP_KEY IN (1,2,3) --Only 4 values: 1,2,3,4 and it is bitmap indexed
GROUP BY CLM_RESP_KEY
Thanks again for looking into that and talking to the development team.
November 06, 2004 - 10:05 am UTC
1 SELECT COUNT(*), CLM_RESP_KEY
2 FROM XPS_PTCLAIM_FACT PARTITION (P200409)
3 WHERE CLM_RESP_KEY IN (1,2,3) --Only 4 values: 1,2,3,4 and it is bitmap indexed
4* GROUP BY CLM_RESP_KEY
dw_xps@ORA9IR2> /
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1536 Card=3 Bytes=9)
1 0 SORT (GROUP BY) (Cost=1536 Card=3 Bytes=9)
2 1 BITMAP CONVERSION (COUNT)
3 2 BITMAP INDEX (FAST FULL SCAN) OF 'PTCLM_CLM_RESP_KEY_BIDX'
it is for me given the table/indexes/stats you sent, what do you see?
Bitmap Conversion
Wayne, November 06, 2004 - 10:33 am UTC
Tom,
You are right (as usual). Sorry for the wrong information. Yesterday we were migrating and upgrading from 8.1.7.4 to 9.2.0.5. Before the 9i was ready, I logged in 8i. In 8i, it did not use the bitmap conversion, but in 9iR2, it does, just like what you just showed me.
Thank you very much!
By the way, how do you generate the explain plan without seeing any data?
November 06, 2004 - 11:04 am UTC
SQL> set autotrace traceonly explain
List partition pruning not happening with joins
Dilip Patel, May 26, 2005 - 10:25 am UTC
Hi,
I have Fact table with partition on Range day_key and subpartition by List on client_key.
I am joining client_key with another look up table to restrict the set of clients from the single partition at RUN Time.
But my query is visiting all the subpartitions. ( Partition pruning not happening for the list subpartition )
The explain plan and the trace with level 12 shows all subpartition getting scaned.
Is there any method to enable the List partition pruning at run time.
BO_LIST is Partitioned on Range (date1) and Subpartition on List (client_key). (There are 9 sub partitions )
SELECT COUNT(acckey),SUM(curr_bal),date1, snum
FROM bo_list b , client c
WHERE b.client_key=c.client_key
AND c.snum=9119
AND b.date1 =20050108
GROUP BY date1, snum
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
1 SORT (GROUP BY)
340672 HASH JOIN
79 TABLE ACCESS (BY INDEX ROWID) OF 'CLIENT'
454 BITMAP CONVERSION (TO ROWIDS)
454 BITMAP INDEX (FULL SCAN) OF 'CLIENT_CLIENT_KEY_BIDX'
601585 PARTITION LIST (ALL) PARTITION: START=1 STOP=9
601585 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'BO_LIST'
PARTITION: START=19 STOP=27
Thanks in advance
Dilip Patel.
May 26, 2005 - 10:39 am UTC
without indentation, I cannot read that.
but I don't see why it shouldn't -- it doesn't KNOW that client_key will be for a single partition - how could it? You are getting client_keys from lots of partitions.
but that plan isn't readable to me, I cannot really see what is going on
Thanks for quick repsponse
Dilip Patel, May 26, 2005 - 11:17 am UTC
Here is the explain plan in readable format. It is confirmed that all client_key for the c.snum=9119 are in the same sub partition.
I also checked in the trace file, each block with db scattered read corresponds to the all 9 subpartitions.
Can't understand why it's not doing visit to only the subpartion required.
SELECT COUNT(acckey),SUM(curr_bal),date1, snum
FROM bo_list b , client c
WHERE b.client_key=c.client_key
AND c.snum=9119
AND b.date1 =20050108
GROUP BY date1, snum
---------------------------------------------------------------------------------------------------------
Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------
0 | SELECT STATEMENT | | 8 | 464 | 92 | | |
1 | SORT GROUP BY | | 8 | 464 | 92 | | |
* 2 | HASH JOIN | | 8 | 464 | 20 | | |
* 3 | TABLE ACCESS BY INDEX ROWID | CLIENT | 7 | 133 | 2 | | |
4 | BITMAP CONVERSION TO ROWIDS| | | | | | |
5 | BITMAP INDEX FULL SCAN | CLIENT_CLIENT_KEY_BIDX | | | | | |
6 | PARTITION LIST ALL | | | | | 1 | 9 |
* 7 | TABLE ACCESS FULL | BO_LIST | 327 | 12753 | 17 | 19 | 27 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
--------------------------------------------------
2 - access("B"."CLIENT_KEY"="C"."CLIENT_KEY")
3 - filter(TO_NUMBER("C"."SNUM")=9119)
7 - filter("B"."DATE1"=20050108)
Thanks
Dilip Patel
Satyam Computers Services Limited
www.satyam.com
May 26, 2005 - 12:51 pm UTC
It is confirmed that all client_key
for the c.snum=9119 are in the same sub partition.
You know that, I know that, but so what? The database cannot know that.
Now, look at those cardinalities -- 7 rows, 327 rows -- are they accurate?
and is the query *SLOW*.
why does oracle need to scan table when the index has all information
jianhui, November 21, 2006 - 7:52 pm UTC
Hi Tom,
This is very puzzling from below test case #2, I have hard time to figure out why Oracle still needs to scan the table in order to find the result set, since the index has all it needs. It seems to me that oracle is smart enough to determint when (i<2), it looks for part1, but why isn't it smart enough to get data out of index, instead, it does tablescan? Both test case#1 & #3 are expected though.
Many thanks!
SQL> create table dropme (i int, c char(10))
2 partition by range (i)
3 (partition p1 values less than (2),
4 partition p2 values less than (3),
5 partition p3 values less than (4)
6 );
Table created.
SQL> create index dropme_i on dropme (c) local;
Index created.
SQL> insert into dropme values (1, 'p1');
1 row created.
SQL> insert into dropme values (2, 'p2');
1 row created.
SQL> insert into dropme values (3, 'p3');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from dropme partition (p1);
I C
---------- ----------
1 p1
SQL> select * from dropme partition (p2);
I C
---------- ----------
2 p2
SQL> /
I C
---------- ----------
2 p2
SQL> select * from dropme partition (p3);
I C
---------- ----------
3 p3
SQL> explain plan for
2 select c from dropme where c='p1';
Explained.
----------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------
--test1, no partition pruning, oracle is smart enough to get data from index structure without table scan
----------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------
SQL> @?/rdbms/admin/utlxpls
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Pstart| Pstop |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 24 | 2 (50)| | |
| 1 | PARTITION RANGE ALL | | | | | 1 | 3 |
|* 2 | INDEX RANGE SCAN | DROPME_I | 2 | 24 | 4 (0)| 1 | 3 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DROPME"."C"='p1')
13 rows selected.
SQL>
----------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------
--test2, with partition pruning, oracle is NOT able to get data from index structure, it has to do table scan(why???)
----------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------
SQL> explain plan for
2 select c from dropme where c='p1' and i<2;
Explained.
SQL> @?/rdbms/admin/utlxpls
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Pstart| Pstop |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 2 (50)| | |
|* 1 | TABLE ACCESS BY LOCAL INDEX ROWID| DROPME | 1 | 25 | 2 (50)| 1 | 1 |
|* 2 | INDEX RANGE SCAN | DROPME_I | 1 | | 1 (0)| 1 | 1 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DROPME"."I"=1)
2 - access("DROPME"."C"='p1')
14 rows selected.
----------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------
--test3, with partition pruning, oracle is able to get data from index without table scan
----------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------
SQL> explain plan for
2 select c from dropme partition (p1) where c='p1';
Explained.
SQL> @?/rdbms/admin/utlxpls
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Pstart| Pstop |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 2 (50)| | |
|* 1 | INDEX RANGE SCAN | DROPME_I | 1 | 12 | 1 (0)| 1 | 1 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("DROPME"."C"='p1')
12 rows selected.
SQL>
November 22, 2006 - 4:53 pm UTC
try testing with a real sized data set???
I didn't really look very closely to see if something obvious was wrong, but you have 3 rows - who cares if you use an index or not???
the question was why oracle scans table when all information can be found in the indes
jianhui, November 23, 2006 - 4:21 am UTC
Hi Tom,
The number of records is irrelavent to my question. I can load it up with many rows but it does not change the question I was asking.
For this SQL below
select c from dropme where c='p1' and i<2;
Why does Oracle need to scan the table segment(execution plan has table access by rowid)? All the information is in the index column (C) already, no need to scan the table at all.
It seems oracle knows partition pruning to check part1 only by looking at (i<2) but it's not smart enough to know that all rows and only rows in part1 meet the criteria.
Because of this reason, there are extra buffer gets for table access of this type of SQL in our system, and due to extream high execution rate per second, it's overloading the system. If we can some how get rid of this table access by rowid, we could have save the system from hardware upgrade.
Also because the partition keys are in a certain range(more than one partition involved) so we can not use the syntax like
select c from dropme partition (part2) where c='p1'
as a alternative, which does not need a table scan by rowid, as you can see in previous post.
We want the SQL to be like
select c from dropme where c='P1' and i>1 and i<5, however, it has to read the table segment, which looks unnecessary and wrong.
Thank you and happy holiday.
November 24, 2006 - 6:20 pm UTC
no, small tables - very small tables - you cannot tell a thing.
Oracle decided the small table full tablescan was either
a) as cheap as
b) cheaper than
using the index. You have a teeny tiny table, please use "something a tad larger"
that is all, use something REAL WORLD, then I'll look, before that - no.
Alberto Dell'Era, November 23, 2006 - 7:13 pm UTC
It doesn't reproduce in 10.2.0.2 (with statistics in place):
dellera@ORACLE10> create table dropme (i int, c char(10))
2 partition by range (i)
3 (partition p1 values less than (2),
4 partition p2 values less than (3),
5 partition p3 values less than (4)
6 );
Table created.
dellera@ORACLE10> create index dropme_i on dropme (c) local;
Index created.
dellera@ORACLE10> insert into dropme values (1, 'p1');
1 row created.
dellera@ORACLE10> insert into dropme values (2, 'p2');
1 row created.
dellera@ORACLE10> insert into dropme values (3, 'p3');
1 row created.
dellera@ORACLE10> commit;
Commit complete.
dellera@ORACLE10> exec dbms_stats.gather_table_stats (user, 'dropme', cascade=>true, method_opt=>'for all columns size 1');
PL/SQL procedure successfully completed.
dellera@ORACLE10> set autotrace traceonly explain
dellera@ORACLE10> select c from dropme where c='p1' and i<2;
Execution Plan
----------------------------------------------------------
Plan hash value: 1964748326
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 2 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE| | 1 | 14 | 1 (0)| 00:00:01 | 1 | 1 |
|* 2 | INDEX RANGE SCAN | DROPME_I | 1 | 14 | 1 (0)| 00:00:01 | 1 | 1 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C"='p1')
It does reproduce in 9.2.0.6, anyway the above test case shows that Oracle was aware of the problem/limitation and removed it in the current release (maybe also in 9.2.0.7 or 9.2.0.8, I have neither handy to check).
Also, as a workaround, you can create an index such as
create index dropme_2_idx on dropme (c,i) local;
this avoids the table access.
HTH
Alberto
we are running 9205 version
jianhui, November 24, 2006 - 1:42 pm UTC
Thank you, I'll need to open a TAR with Oracle to see whether this is bug related. For the workaround you mentioned, we have being evaluated whether to add partition key to the index, but we will have to make a decision before we know that's a bug or not.
no luck with 10.2.0.1 either
jianhui, November 24, 2006 - 2:42 pm UTC
JIANZHANG@O10GR2>begin
JIANZHANG@O10GR2> for i in 1..10000 loop
JIANZHANG@O10GR2> insert into dropme values (mod(i,3)+1, 'p'||to_char(i));
JIANZHANG@O10GR2> end loop;
JIANZHANG@O10GR2>end;
JIANZHANG@O10GR2>/
PL/SQL procedure successfully completed.
JIANZHANG@O10GR2>commit;
Commit complete.
JIANZHANG@O10GR2>exec dbms_stats.gather_table_stats (user, 'dropme', cascade=>true, method_opt=>'for
all columns size 1');
PL/SQL procedure successfully completed.
JIANZHANG@O10GR2>explain plan for select c from dropme where c='p1' and i<2;
Explained.
JIANZHANG@O10GR2>@?/rdbms/admin/utlxpls
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1386302003
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 2 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE | | 1 | 14 | 2 (0)| 00:00:01 | 1 | 1 |
|* 2 | TABLE ACCESS BY LOCAL INDEX ROWID| DROPME | 1 | 14 | 2 (0)| 00:00:01 | 1
|* 3 | INDEX RANGE SCAN | DROPME_I | 1 | | 1 (0)| 00:00:01 | 1 | 1 |
----------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("I"<2)
3 - access("C"='p1')
16 rows selected.
JIANZHANG@O10GR2>select i, count(*) from dropme group by i;
I COUNT(*)
---------- ----------
1 3334
2 3335
3 3334
JIANZHANG@O10GR2>select count(*) from dropme where c='p1';
COUNT(*)
----------
2
JIANZHANG@O10GR2>select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
JIANZHANG@O10GR2>show parameter optim
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
object_cache_optimal_size integer 102400
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 10.2.0.1
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_mode string ALL_ROWS
optimizer_secure_view_merging boolean TRUE
plsql_optimize_level integer 2
JIANZHANG@O10GR2>
November 24, 2006 - 6:56 pm UTC
select name || '=' || value from v$parameter where isdefault = 'FALSE'
I cannot reproduce your test on 9i or 10g.
ops$tkyte%ORA9IR2> create table dropme (i int, c char(10))
2 partition by range (i)
3 (partition p1 values less than (2),
4 partition p2 values less than (3),
5 partition p3 values less than (4)
6 );
Table created.
ops$tkyte%ORA9IR2> create index dropme_i on dropme (c) local;
Index created.
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> begin
2 for i in 1..10000 loop
3 insert into dropme values (mod(i,3)+1, 'p'||to_char(i));
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
ops$tkyte%ORA9IR2> commit;
Commit complete.
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> exec dbms_stats.gather_table_stats (user, 'dropme', cascade=>true, method_opt=>'for all columns size 1');
PL/SQL procedure successfully completed.
ops$tkyte%ORA9IR2> @at
ops$tkyte%ORA9IR2> column PLAN_TABLE_OUTPUT format a72 truncate
ops$tkyte%ORA9IR2> set autotrace traceonly explain
ops$tkyte%ORA9IR2> select c from dropme where c='p1' and i<2;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=14)
1 0 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'DROPME' (Cost=2 Card=1 Bytes=14)
2 1 INDEX (RANGE SCAN) OF 'DROPME_I' (NON-UNIQUE) (Cost=1 Card=1)
ops$tkyte%ORA9IR2> select /*+ INDEX(dropme dropme_i) */ c from dropme where c='p1' and i<2;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=14)
1 0 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'DROPME' (Cost=2 Card=1 Bytes=14)
2 1 INDEX (RANGE SCAN) OF 'DROPME_I' (NON-UNIQUE) (Cost=1 Card=1)
I have all defaults.
Alberto Dell'Era, November 24, 2006 - 7:35 pm UTC
Just for completeness - I've repeated the test in 10GR2 using the above data set
dellera@ORACLE10> begin
2 for i in 1..10000 loop
3 insert into dropme values (mod(i,3)+1, 'p'||to_char(i));
4 end loop;
5 end;
6 /
And the plan doesn't show the access to the table (as we saw with a mere row per partition):
dellera@ORACLE10> column PLAN_TABLE_OUTPUT format a72 truncate
dellera@ORACLE10> set autotrace traceonly explain
dellera@ORACLE10> select c from dropme where c='p1' and i<2;
Execution Plan
----------------------------------------------------------
Plan hash value: 1964748326
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 2 (0)|
| 1 | PARTITION RANGE SINGLE| | 1 | 14 | 1 (0)|
|* 2 | INDEX RANGE SCAN | DROPME_I | 1 | 14 | 1 (0)|
------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C"='p1')
Pstart = Pstop = 1 of course
November 25, 2006 - 9:13 am UTC
you are right - in 10.2.0.2 it did not reproduce:
ops$tkyte%ORA10GR2> explain plan for &1;
old 1: explain plan for &1
new 1: explain plan for select c from dropme where c='p1' and i<2
Explained.
ops$tkyte%ORA10GR2> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
Plan hash value: 1964748326
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 500 | 12500 | 5 (0)|
| 1 | PARTITION RANGE SINGLE| | 500 | 12500 | 1 (0)|
|* 2 | INDEX RANGE SCAN | DROPME_I | 500 | 12500 | 1 (0)|
------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C"='p1')
14 rows selected.
Looks like it was something corrected/added/enhanced in 10.2.0.2.
you just reproduced in 9iR2
jianhui, November 24, 2006 - 10:50 pm UTC
look for this line in the execution plan
<
TABLE ACCESS BY LOCAL INDEX ROWID| DROPME
>
since all the information column C already in the index, there should not be necessary to do table scan. From your 10GR2 example, the plan is expected, only index scan, no table scan by rowid.
a test example with more data
jianhui, November 25, 2006 - 1:49 am UTC
Hello Tom,
As you recommended, I loaded the table with more data, you can see the difference of the execution plan. While I specify partition key (i)'s range, execution plan has one unnecessary step
<
TABLE ACCESS BY LOCAL INDEX ROWID
>
But Oracle knows how to do partition pruning, why does it need to read the table when all column C is already presented in the index??? (also the same result after gathering the stats)
Could you take a look? Thanks a lot!
1 begin
2 for i in 1..100000 loop
3 insert into dropme values (mod(i,3)+1, 'p'||to_char(i));
4 end loop;
5* end;
SQL> /
PL/SQL procedure successfully completed.
SQL> select i, count(*) from dropme group by i;
I COUNT(*)
---------- ----------
1 33333
2 33334
3 33333
SQL> select count(*) from dropme where c='p1';
COUNT(*)
----------
1
SQL> show parameter opti
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
filesystemio_options string asynch
object_cache_optimal_size integer 102400
optimizer_dynamic_sampling integer 1
optimizer_features_enable string 9.2.0
optimizer_index_caching integer 75
optimizer_index_cost_adj integer 25
optimizer_max_permutations integer 10000
optimizer_mode string CHOOSE
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
PL/SQL Release 9.2.0.5.0 - Production
CORE 9.2.0.6.0 Production
TNS for Solaris: Version 9.2.0.5.0 - Production
NLSRTL Version 9.2.0.5.0 - Production
SQL> explain plan for select c from dropme partition (p1) where c='p1';
Explained.
SQL> @?/rdbms/admin/utlxpls
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Pstart| Pstop |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 2 (50)| | |
|* 1 | INDEX RANGE SCAN | DROPME_I | 1 | 12 | 1 (0)| 1 | 1 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("DROPME"."C"='p1')
12 rows selected.
SQL> explain plan for select c from dropme where c='p1' and i<2;
Explained.
SQL> @?/rdbms/admin/utlxpls
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Pstart| Pstop |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 2 (50)| | |
|* 1 | TABLE ACCESS BY LOCAL INDEX ROWID| DROPME | 1 | 25 | 2 (50)| 1 | 1 |
|* 2 | INDEX RANGE SCAN | DROPME_I | 1 | | 1 (0)| 1 | 1 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DROPME"."I"<2)
2 - access("DROPME"."C"='p1')
14 rows selected.
SQL> exec dbms_stats.gather_table_stats (user, 'dropme', cascade=>true, method_opt=>'for all columns size 1');
PL/SQL procedure successfully completed.
SQL> explain plan for select c from dropme where c='p1' and i<2;
Explained.
SQL> @?/rdbms/admin/utlxpls
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Pstart| Pstop |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 2 (50)| | |
|* 1 | TABLE ACCESS BY LOCAL INDEX ROWID| DROPME | 1 | 14 | 2 (50)| 1 | 1 |
|* 2 | INDEX RANGE SCAN | DROPME_I | 1 | | 1 (0)| 1 | 1 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DROPME"."I"<2)
2 - access("DROPME"."C"='p1')
14 rows selected.
November 25, 2006 - 9:17 am UTC
see above, it would appear to be an enhancement added in 10.2.0.2
is there a workaround in 9205
jianhui, November 25, 2006 - 11:45 am UTC
Hi Tom,
Thanks for the reply. So other then building new index with partition key in it, is there other workaround such as hint or something else to let the CBO not to scan the table?
Regards,
November 25, 2006 - 11:52 am UTC
you'd have to contact support for that - it is doing what it does. The partition elimination is kicking in - it just did not push the partition elimination into the predicate itself (did not take the leap that the predicate filter was already satisfied).
min value
abz, January 04, 2007 - 7:22 am UTC
If say, table emp is range partitioned on salary column.
and there are some rows in the first partition , and
there may or may not be rows in other partitions.
Will the query SELECT MIN(SALARY) FROM EMP will only
scan first partition and stop or will it scan all the
partitions.
any body can answer the above
abz, January 08, 2007 - 4:27 am UTC
Can any body answer the above.
January 08, 2007 - 12:31 pm UTC
it currently will full scan all of them
ops$tkyte%ORA10GR2> CREATE TABLE t
2 (
3 dt date,
4 salary int,
5 y varchar2(30)
6 )
7 PARTITION BY RANGE (salary)
8 (
9 PARTITION part1 VALUES LESS THAN (1),
10 PARTITION part2 VALUES LESS THAN (2),
11 PARTITION junk VALUES LESS THAN (MAXVALUE)
12 )
13 /
Table created.
ops$tkyte%ORA10GR2> insert into t select created, mod(rownum,3), rpad('*',30,'*') from all_objects;
49951 rows created.
ops$tkyte%ORA10GR2> exec dbms_stats.gather_table_stats( user, 'T' );
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> set autotrace traceonly explain
ops$tkyte%ORA10GR2> select min(salary) from t;
Execution Plan
----------------------------------------------------------
Plan hash value: 3225603066
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 76 (3)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 3 | | | | |
| 2 | PARTITION RANGE ALL| | 51250 | 150K| 76 (3)| 00:00:01 | 1 | 3 |
| 3 | TABLE ACCESS FULL | T | 51250 | 150K| 76 (3)| 00:00:01 | 1 | 3 |
---------------------------------------------------------------------------------------------
ops$tkyte%ORA10GR2> set autotrace off
Alberto Dell'Era, January 08, 2007 - 4:01 pm UTC
If one is willing to list the partitions explicitly in increasing order, a nice workaround is to exploit the short-circuit properties of coalesce(). In 10.2.0.2:
dellera@ORACLE10> select coalesce (
2 (select min(salary) from t partition (part1)),
3 (select min(salary) from t partition (part2)),
4 (select min(salary) from t partition (junk))
5 ) as min_salary
6 from dual;
MIN_SALARY
----------
0
Execution Plan
----------------------------------------------------------
Plan hash value: 3618731627
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 (0)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 2 | | | | |
| 2 | PARTITION RANGE SINGLE | | 19439 | 38878 | 41 (0)| 00:00:01 | 1 | 1 |
| 3 | TABLE ACCESS FULL | T | 19439 | 38878 | 41 (0)| 00:00:01 | 1 | 1 |
| 4 | SORT AGGREGATE | | 1 | 3 | | | | |
| 5 | PARTITION RANGE SINGLE | | 19440 | 58320 | 41 (0)| 00:00:01 | 2 | 2 |
| 6 | TABLE ACCESS FULL | T | 19440 | 58320 | 41 (0)| 00:00:01 | 2 | 2 |
| 7 | SORT AGGREGATE | | 1 | 3 | | | | |
| 8 | PARTITION RANGE SINGLE| | 19439 | 58317 | 41 (0)| 00:00:01 | 3 | 3 |
| 9 | TABLE ACCESS FULL | T | 19439 | 58317 | 41 (0)| 00:00:01 | 3 | 3 |
| 10 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | | |
------------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
(snip)
157 consistent gets
(snip)
dellera@ORACLE10> select min(salary) from t;
MIN(SALARY)
-----------
0
Execution Plan
----------------------------------------------------------
Plan hash value: 3225603066
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 121 (1)| 00:00:02 | | |
| 1 | SORT AGGREGATE | | 1 | 3 | | | | |
| 2 | PARTITION RANGE ALL| | 58111 | 170K| 121 (1)| 00:00:02 | 1 | 3 |
| 3 | TABLE ACCESS FULL | T | 58111 | 170K| 121 (1)| 00:00:02 | 1 | 3 |
---------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
(snip)
503 consistent gets
(snip)
dellera@ORACLE10> select min(salary) from t partition (part1);
MIN(SALARY)
-----------
0
Execution Plan
----------------------------------------------------------
Plan hash value: 2993254470
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 41 (0)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 2 | | | | |
| 2 | PARTITION RANGE SINGLE| | 19439 | 38878 | 41 (0)| 00:00:01 | 1 | 1 |
| 3 | TABLE ACCESS FULL | T | 19439 | 38878 | 41 (0)| 00:00:01 | 1 | 1 |
------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
(snip)
157 consistent gets
(snip)
So - it stopped as soon as the first partition was scanned. Note also the (very nice) indentetion of the three partition accesses on the first plan, that suggests the dependency.
It is semantically equivalent even when the table is empty or salary is null over all rows.
I'm sure that Tom knows about this technique, I'm just adding to the answer :)
correction
Alberto Dell'Era, January 09, 2007 - 6:03 pm UTC
Jonathan Lewis has suggested me (and He was right of course) that the indentation (which is based on the "depth" column of plan_table) of the first plan does not convey the id/parent_id hierarchy:
dellera@ORACLE10> explain plan for
2 select coalesce (
3 (select min(salary) from t partition (part1)),
4 (select min(salary) from t partition (part2)),
5 (select min(salary) from t partition (junk))
6 ) as min_salary
7 from dual;
dellera@ORACLE10> select id, parent_id, depth, OPERATION, options from plan_table order by id;
ID PARENT_ID DEPTH OPERATION OPTIONS
---------- ---------- ---------- ------------------------------ -------------
0 0 SELECT STATEMENT
1 0 1 SORT AGGREGATE
2 1 2 PARTITION RANGE SINGLE
3 2 3 TABLE ACCESS FULL
4 0 4 SORT AGGREGATE
5 4 5 PARTITION RANGE SINGLE
6 5 6 TABLE ACCESS FULL
7 0 7 SORT AGGREGATE
8 7 8 PARTITION RANGE SINGLE
9 8 9 TABLE ACCESS FULL
10 0 1 FAST DUAL
So - we cannot say that the indentation, in this case, means "dependency" between the three scalar subquery.
The short-circuit dependency exists (as shown above in the test case) but its existence is not reflected in the plan.
Join between Dimension and Fact table
A reader, March 18, 2007 - 3:01 pm UTC
Hi
I am working with several databases which have DWH like nature. What I am finding is that the execution plan are so wrong that a query which takes seconds usually takes hours and ends up with Snapshot Too Old
For example:
Time Dimension D1 has 3000 rows
Fact Table F1 has 300 Million of rows, 30 partition, 10 million per partition, partitioned by date
the query looks like this
select
from F1, D1
where F1.p_date > trunc(sysdate - 10)
and F1.p_Date = D1.p_date
Instead of eliminating partitions in F1, the Optimizer chooses the predicate for D1, instead of F1.p_date it changed to
where D1.p_date > trunc(sysdate - 10)
So instead of elimination partition a Nested Loop is used
for i in (Select * from D1)
loop
select * from F1
end loop
I wonder has this soemthing to do with star_transformation_enabled? We have it set to FALSE
March 18, 2007 - 7:49 pm UTC
trunc(sysdate-10) is considered "a function"
is there a chance we can use literals here - so the optimizer gets the right estimated cardinalities?
hash partions and pruning
abz, April 05, 2007 - 1:43 pm UTC
I understand very well partition pruning when we
do LIST or RANGE partitions. But I am unable to understand
how partitions will be pruned with HASH PARTITION.
April 05, 2007 - 1:57 pm UTC
create table t1 ( ... ) partition by hash(id) partitions 8
create table t2 ( ... ) partition by hash(id) partitions 8
select *
from t1, t2
where t1.id = t2.id;
imagine the opportunities for a partitionwise join there.
or
select * from t1 where id = 42;
and no index exists on ID....
or
select * from t1 where id = 42 and name = 'KYTE';
and there is an index on NAME (a local index), it can do index partition elimination - read just one index partition by name and find the rows of interest.
Using USE_HASH for a long running query
kumar, April 17, 2007 - 1:15 pm UTC
Tom,
If you could please find some time to look into this query and the explain plan that follows:
Will there be any improvement using /*+ USE_HASH(e) */?
The query takes 10+ hours to run. The number of records in each table is:
t 20
t1 2mill
t2 2mill
t3 100k
t4 13mill and hash partitioned
t5 2mill
Can you comment on the query?
select count(*) from
t ,t1 , t2,t3 ,t4 ,t5
WHERE t.type = 'ABC'
AND t1.dt = t.last_eff_dt
AND t1.id = t2.id
AND t4.a_id = t1.a_id
AND t2.sec_id = t3.sec_id
AND t5.ast_id = t3.ast_id;
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 159 | 11 | | |
| 1 | SORT AGGREGATE | | 1 | 159 | | | |
| 2 | NESTED LOOPS | | 1 | 159 | 11 | | |
| 3 | NESTED LOOPS | | 1 | 150 | 9 | | |
| 4 | NESTED LOOPS | | 1 | 43 | 8 | | |
| 5 | NESTED LOOPS | | 1 | 34 | 5 | | |
| 6 | NESTED LOOPS | | 1 | 28 | 4 | | |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
| 7 | TABLE ACCESS BY INDEX ROWID | t | 1 | 12 | 2 | | |
| 8 | INDEX SKIP SCAN | xpk_t | 1 | | 1 | | |
| 9 | TABLE ACCESS BY INDEX ROWID | t1 | 1 | 16 | 2 | | |
| 10 | INDEX RANGE SCAN | UIXt1 | 794K| | 2 | | |
| 11 | PARTITION HASH ITERATOR | | | | | KEY | KEY |
| 12 | INDEX UNIQUE SCAN | XPKt4 | 1 | 6 | 1 | KEY | KEY |
| 13 | TABLE ACCESS BY INDEX ROWID | t2 | 6 | 54 | 3 | | |
| 14 | INDEX RANGE SCAN | IXt2 | 6 | | 2 | | |
| 15 | PARTITION HASH ITERATOR | | | | | KEY | KEY |
| 16 | TABLE ACCESS BY LOCAL INDEX ROWID| t3 | 1 | 107 | 1 | KEY | KE
| 17 | INDEX UNIQUE SCAN | XPKt3 | 1 | | | KEY | KEY |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
| 18 | INDEX RANGE SCAN | IXt5 | 17 | 153 | 2 | | |
----------------------------------------------------------------------------------------------------
April 18, 2007 - 11:07 am UTC
are those estimated cardinality figures even close to reality?
I say "no, they cannot be", else this query would have been instantaneous.
are your stats accurate
Not up-to-date statistics and execution plan
ken, July 13, 2007 - 3:38 pm UTC
hi Tom, below is a very slow query that runs for several hours for only a few hundred records.
The wrk_csf_billing_account_xref table is a temp table containing the records we want to process(~100,000 records), and the BILL_CHARGE_DETAIL table is a partitioned table containing millions of detail records, partitioning on Bill_Dt.
From the plan, I see the problem is that the detail table is used as a driving table(ID 8) for the query. I think the temp table should have been used instead, as it has much less records, also when we hint the query with /*+ Leading(wrk_csf_billing_account_xref) */, it came back almost immediately.
I think the reason that Oracle used the detail table to drive the query is because the statistics are not up to date, but our envirnonment team do not want to update these statistic, which was generated from last last year, when it only has 10% of data. How do I prove the statistic is the problem?
Another problem I have is, the whole batch job usually completes in 11 hours for 150K bills on the same bill_dt. On certain dates, we rerun for 100 records, where the bill_dt contains several dates within the month, and this process ran at a multiple of 11 hours. for example, if the rerun is for July 4, 8 11, 15, 23, it would ran for 55 hours!! We notice the trend that the jobs runs longer at the end of the month, than the beginning of the month. It appears every run has the same cost, regardless how many bills being processed. Where should I look to confirm this? Or what causes could be associated with these type of problems?
Thanks again Tom ! I hope I have provided enough information, otherwise please let me know.
SQL> explain Plan for
2 SELECT bill_charge_detail.bill_charge_dtl_id, bill_charge_detail.bill_dt,
3 bill_charge_detail.bill_document_id,
4 bill_charge_detail.bill_charge_dtl_type_cd,
5 bill_charge_detail.price_plan_grp_cd,
6 bill_charge_detail.cmpnt_subscrptn_key_id,
7 DECODE (bill_charge_detail.sum_dtl_bill_itm_ind, 'Y', '1', '2'),
8 bill_charge_detail.free_unit_qty
9 FROM bill_charge_detail,
10 bill_recurring_charge_dtl,
11 wrk_csf_billing_account_xref,
12 wrk_csf_bill_cycle_date
13 WHERE bill_charge_detail.bill_dt >= wrk_csf_bill_cycle_date.min_bill_dt
14 AND bill_charge_detail.bill_dt <= wrk_csf_bill_cycle_date.max_bill_dt
15 AND bill_charge_detail.bill_dt = wrk_csf_billing_account_xref.bill_dt
16 AND bill_charge_detail.bill_document_id =
17 wrk_csf_billing_account_xref.bill_document_id
18 AND bill_charge_detail.bill_dt = bill_recurring_charge_dtl.bill_dt(+)
19 AND bill_charge_detail.bill_charge_dtl_id = bill_recurring_charge_dtl.bill_charge_dtl_id(+
)
20 AND bill_charge_detail.bill_itm_sum_lvl_cd != '01'
21 AND bill_charge_detail.charge_rev_cd = 'RC'
22 AND ( bill_charge_detail.paper_bill_dsply_ind = 'Y'
23 OR bill_charge_detail.ebill_dsply_ind = 'Y'
24 OR bill_charge_detail.intrnl_portal_dsply_ind = 'Y'
25 )
26 AND bill_charge_detail.bill_sect_cd IS NOT NULL
27 ORDER BY bill_sect_cd, root_bundle_price_plan_cd, price_plan_grp_cd, var_cd;
Explained.
SQL>
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1064781282
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 102 | 5 (20)| 00:00:01 | | |
| 1 | SORT ORDER BY | | 1 | 102 | 5 (20)| 00:00:01 | | |
| 2 | NESTED LOOPS OUTER | | 1 | 102 | 4 (0)| 00:00:01 | |
| 3 | NESTED LOOPS | | 1 | 88 | 3 (0)| 00:00:01 | | |
| 4 | NESTED LOOPS | | 1 | 74 | 3 (0)| 00:00:01 | | |
| 5 | INDEX FULL SCAN | WRK_CSF_BILL_CYCLE_DATE_PK | 1 | 16 | 1 (0)| 00:
| 6 | PARTITION RANGE ITERATOR | | 1 | 58 | 2 (0)| 00:00:01 | KEY
|* 7 | TABLE ACCESS BY LOCAL INDEX ROWID| BILL_CHARGE_DETAIL | 1 | 58 | 2 (0
|* 8 | INDEX RANGE SCAN | BILL_CHARGE_DETAIL_IX1 | 1 | | 2 (0)|
|* 9 | INDEX UNIQUE SCAN | WRK_CSF_BILLING_ACCT_XREF_PK | 1 | 14 | 0 (0)| 00
| 10 | PARTITION RANGE ITERATOR | | 1 | 14 | 1 (0)| 00:00:01 | KEY |
|* 11 | INDEX RANGE SCAN | BILL_RECURRING_CHARGE_DTL_PK | 1 | 14 | 1 (0)| 00:
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
7 - filter("BILL_CHARGE_DETAIL"."CHARGE_REV_CD"='RC' AND "BILL_CHARGE_DETAIL"."BILL_SECT_CD" IS N
("BILL_CHARGE_DETAIL"."INTRNL_PORTAL_DSPLY_IND"='Y' OR "BILL_CHARGE_DETAIL"."EBILL_DSPLY_IND"
"BILL_CHARGE_DETAIL"."PAPER_BILL_DSPLY_IND"='Y') AND "BILL_CHARGE_DETAIL"."BILL_ITM_SUM_LVL_C
8 - access("BILL_CHARGE_DETAIL"."BILL_DT">="WRK_CSF_BILL_CYCLE_DATE"."MIN_BILL_DT" AND
"BILL_CHARGE_DETAIL"."BILL_DT"<="WRK_CSF_BILL_CYCLE_DATE"."MAX_BILL_DT")
9 - access("BILL_CHARGE_DETAIL"."BILL_DT"="WRK_CSF_BILLING_ACCOUNT_XREF"."BILL_DT" AND
"BILL_CHARGE_DETAIL"."BILL_DOCUMENT_ID"="WRK_CSF_BILLING_ACCOUNT_XREF"."BILL_DOCUMENT_ID")
11 - access("BILL_CHARGE_DETAIL"."BILL_DT"="BILL_RECURRING_CHARGE_DTL"."BILL_DT"(+) AND
"BILL_CHARGE_DETAIL"."BILL_CHARGE_DTL_ID"="BILL_RECURRING_CHARGE_DTL"."BILL_CHARGE_DTL_ID"(+)
31 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=25 Card=8168 Bytes
=16336)
1 0 COLLECTION ITERATOR (PICKLER FETCH) OF 'DISPLAY' (PROCEDUR
E)
July 13, 2007 - 7:51 pm UTC
... How do I prove the statistic is the problem? ...
as I say to my kids, osh kosh b'gosh.
give me a break.
sorry - don't really know what to say.
guess you could
a) export stats
b) import them into a test instance (with or without any data in tables)
c) verify 'bad plan'
d) use dbms_stats.set_ to set REAL stats based on your knowledge
e) show different plan
Issues when data is populated into new partition
Momen, April 08, 2008 - 9:13 am UTC
To brief you, we have a data warehouse database running on Oracle 10.2.0.3. Mostly all the tables are partitioned by month and few have two partitions for a month.
Daily at night, data is populated from multiple feeder databases (Oracle, SQL Server, Main Frames, text files,....) into this database.
Whenever, batch loads data into new partition, it takes very long to complete. After a day or two the batch functions smootly, i.e. completes on time.
I have captured Execution Plans for data loads when the batch completes on time (i.e, at the month end) and when it suffers performance issues (i.e. loading new partitions).
I see the executions plans differ and the elapsed time differs in multiples as a result. Attached you can see the plans for the same query.
Following is the background information, I think I should share with you:
Database: Oracle 10g (10.2.0.3)
OS: IBM - Aix 5.3 - ML -5
Processors: 10 Physical (40 Logical)
Memory: 14 GB
Initialization Parameter that might be of significance:
CURSOR_SHARING = FORCE
Snapshot Interval: 30 Minutes
Table Name: Authorization
Paritions: 49 (13 partitions contain full months data and remaining partitions have only the last day's data)
Partitioned on: As_Of_Date (column)
Partition Type: One Gregorian Month
Tablesize: 13.5 GB
Active Partitions Size: 1 GB (for the 13 partitions)
Rows: 80 Million
Total Indexes: 3
Partitioned Indexes: 2
Global Indexes: 1
Index Name: SYS_C0010238
Unique: Yes
Partitioned: No
Index Columns: AUTH_SEQ_KEY & AS_OF_DATE
CF: 91452068
Blocks: 375419
Rows: 91452068
Size (GB): 5.75
Index Name: IDX_AUTHORIZATION_1
Unique: No
Partitioned: Yes
Index Columns: AS_OF_DATE
CF: 940360
Blocks: 153460
Rows: 81106590
Size (GB): 2.59
Index Name: IDX_AUTHORIZATION_2
Unique: No
Partitioned: Yes
Index Columns: CUST_CODE
CF: 42264920
Blocks: 116710
Rows: 79968760
Size (GB): 2.03
Below is the *GOOD* plan
WORKLOAD REPOSITORY SQL Report
Snapshot Period Summary
DB Name DB Id Instance Inst Num Release RAC Host
------------ ----------- ------------ -------- ----------- --- ------------
OFDMP 2543030685 OFDMP 1 10.2.0.3.0 NO ofdmprd
Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 4187 01-ÇÈÑêä -08 04:30: 98 2.9
End Snap: 4188 01-ÇÈÑêä -08 05:00: 98 2.9
Elapsed: 29.85 (mins)
DB Time: 39.65 (mins)
SQL Summary DB/Inst: OFDMP/OFDMP Snaps: 4187-4188
Elapsed
SQL Id Time (ms)
------------- ----------
6k5jkqzvfz7gs 3,807
Module: uvsh.exe
UPDATE AUTHORIZATION SET AMORTZ_UNIT_AMT = :B5 , INCON_SECURITY_AMT = :B4 , OVER
STEP_DATE = :B3 WHERE AUTH_SEQ_KEY = :B2 AND AS_OF_DATE = :B1
-------------------------------------------------------------
SQL ID: 6k5jkqzvfz7gs DB/Inst: OFDMP/OFDMP Snaps: 4187-4188
-> 1st Capture and Last Capture Snap IDs
refer to Snapshot IDs witin the snapshot range
-> UPDATE AUTHORIZATION SET AMORTZ_UNIT_AMT = :B5 , INCON_SECURITY_AMT = ...
Plan Hash Total Elapsed 1st Capture Last Capture
# Value Time(ms) Executions Snap ID Snap ID
--- ---------------- ---------------- ------------- ------------- --------------
1 1474318199 3,807 22,5629 4188 4188
-------------------------------------------------------------
Plan 1(PHV: 1474318199)
-----------------------
Plan Statistics DB/Inst: OFDMP/OFDMP Snaps: 4187-4188
-> % Total DB Time is the Elapsed Time of the SQL statement divided
into the Total Database Time multiplied by 100
Stat Name Statement Per Execution % Snap
---------------------------------------- ---------- -------------- -------
Elapsed Time (ms) 3,807 0.0 0.2
CPU Time (ms) 3,807 0.0 0.4
Executions 225,629 N/A N/A
Buffer Gets 676,889 3.0 8.2
Disk Reads 1 0.0 0.0
Parse Calls 1 0.0 0.0
Rows 0 0.0 N/A
User I/O Wait Time (ms) 7 N/A N/A
Cluster Wait Time (ms) 0 N/A N/A
Application Wait Time (ms) 0 N/A N/A
Concurrency Wait Time (ms) 0 N/A N/A
Invalidations 0 N/A N/A
Version Count 1 N/A N/A
Sharable Mem(KB) 22 N/A N/A
-------------------------------------------------------------
Execution Plan
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | 3 (100)| |
| 1 | UPDATE | AUTHORIZATION | | | | |
| 2 | INDEX UNIQUE SCAN| SYS_C0010238 | 1 | 27 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Full SQL Text
SQL ID SQL Text
------------ -----------------------------------------------------------------
6k5jkqzvfz7g UPDATE AUTHORIZATION SET AMORTZ_UNIT_AMT = :B5 , INCON_SECURITY_A
AMT = :B4 , OVERSTEP_DATE = :B3 WHERE AUTH_SEQ_KEY = :B2 AND AS_O
F_DATE = :B1
Here's what happens when data is populated into new partition:
WORKLOAD REPOSITORY SQL Report
Snapshot Period Summary
DB Name DB Id Instance Inst Num Release RAC Host
------------ ----------- ------------ -------- ----------- --- ------------
OFDMP 2543030685 OFDMP 1 10.2.0.3.0 NO ofdmprd
Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 4235 02-ÇÈÑêä -08 04:30: 98 3.1
End Snap: 4236 02-ÇÈÑêä -08 05:00: 98 3.2
Elapsed: 30.51 (mins)
DB Time: 85.49 (mins)
SQL Summary DB/Inst: OFDMP/OFDMP Snaps: 4235-4236
Elapsed
SQL Id Time (ms)
------------- ----------
6k5jkqzvfz7gs 683,494
Module: uvsh.exe
UPDATE AUTHORIZATION SET AMORTZ_UNIT_AMT = :B5 , INCON_SECURITY_AMT = :B4 , OVER
STEP_DATE = :B3 WHERE AUTH_SEQ_KEY = :B2 AND AS_OF_DATE = :B1
-------------------------------------------------------------
SQL ID: 6k5jkqzvfz7gs DB/Inst: OFDMP/OFDMP Snaps: 4235-4236
-> 1st Capture and Last Capture Snap IDs
refer to Snapshot IDs witin the snapshot range
-> UPDATE AUTHORIZATION SET AMORTZ_UNIT_AMT = :B5 , INCON_SECURITY_AMT = ...
Plan Hash Total Elapsed 1st Capture Last Capture
# Value Time(ms) Executions Snap ID Snap ID
--- ---------------- ---------------- ------------- ------------- --------------
1 3727740582 683,494 3381 4236 4236
-------------------------------------------------------------
Plan 1(PHV: 3727740582)
-----------------------
Plan Statistics DB/Inst: OFDMP/OFDMP Snaps: 4235-4236
-> % Total DB Time is the Elapsed Time of the SQL statement divided
into the Total Database Time multiplied by 100
Stat Name Statement Per Execution % Snap
---------------------------------------- ---------- -------------- -------
Elapsed Time (ms) 683,494 202.2 13.3
CPU Time (ms) 643,617 190.4 20.7
Executions 3,381 N/A N/A
Buffer Gets 8,727,370 2,581.3 5.8
Disk Reads 2,572 0.8 0.6
Parse Calls 1 0.0 0.1
Rows 0 0.0 N/A
User I/O Wait Time (ms) 1,648 N/A N/A
Cluster Wait Time (ms) 0 N/A N/A
Application Wait Time (ms) 0 N/A N/A
Concurrency Wait Time (ms) 1 N/A N/A
Invalidations 0 N/A N/A
Version Count 1 N/A N/A
Sharable Mem(KB) 22 N/A N/A
-------------------------------------------------------------
Execution Plan
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Psto
------------------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | 1 (100)| | |
| 1 | UPDATE | AUTHORIZATION | | | | | |
| 2 | PARTITION RANGE SINGLE | | 1 | 56 | 1 (0)| 00:00:01 | KEY | KE
| 3 | TABLE ACCESS BY LOCAL INDEX ROWID| AUTHORIZATION | 1 | 56 | 1 (0)| 00:00:01 | KEY | KE
| 4 | INDEX RANGE SCAN | IDX_AUTHORIZATION_1 | 1 | | 1 (0)| 00:00:01 | KEY | KE
------------------------------------------------------------------------------------------------------------------------
Full SQL Text
SQL ID SQL Text
------------ -----------------------------------------------------------------
6k5jkqzvfz7g UPDATE AUTHORIZATION SET AMORTZ_UNIT_AMT = :B5 , INCON_SECURITY_A
AMT = :B4 , OVERSTEP_DATE = :B3 WHERE AUTH_SEQ_KEY = :B2 AND AS_O
F_DATE = :B1
I can upload the table (rows=no) with statistics, if required.
When I change CURSOR_SHARING=EXACT on my test database (before executing the UPDATE statement), it picks the right index. As this is the production database and performance issues happen once in 15 days, I would like to make sure that I pick the right change.
Below is the script of my test case:
***********************************************************************************
Worst Plan
***********************************************************************************
comment on table authorization is 'worse_2';
alter session set cursor_sharing = force;
alter session set tracefile_identifier = 'Worse';
alter session set events '10053 trace name context forever, level 1';
UPDATE AUTHORIZATION
SET AMORTZ_UNIT_AMT = 1 , INCON_SECURITY_AMT = 2 , OVERSTEP_DATE = to_date('04/01/08', 'MM/DD/YY')
WHERE AUTH_SEQ_KEY = 'CPT22113622211362C02' AND AS_OF_DATE = to_date('04/01/08', 'MM/DD/YY');
UPDATE AUTHORIZATION
SET AMORTZ_UNIT_AMT = 1 , INCON_SECURITY_AMT = 2 , OVERSTEP_DATE = to_date('04/06/08', 'MM/DD/YY')
WHERE AUTH_SEQ_KEY = 'CPT22113622211362C02' AND AS_OF_DATE = to_date('04/06/08', 'MM/DD/YY');
alter session set events '10053 trace name context off';
exit
Execution plan from the trace file:
------------------------------------------------------------------+-----------------------------------+---------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time | Pstart| Pstop |
------------------------------------------------------------------+-----------------------------------+---------------+
| 0 | UPDATE STATEMENT | | | | 1 | | | |
| 1 | UPDATE | AUTHORIZATION | | | | | | |
| 2 | PARTITION RANGE SINGLE | | 1 | 56 | 1 | 00:00:01 | KEY | KEY |
| 3 | TABLE ACCESS BY LOCAL INDEX ROWID | AUTHORIZATION | 1 | 56 | 1 | 00:00:01 | KEY | KEY |
| 4 | INDEX RANGE SCAN | IDX_AUTHORIZATION_1| 1 | | 1 | 00:00:01 | KEY | KEY |
------------------------------------------------------------------+-----------------------------------+---------------+
Predicate Information:
----------------------
3 - filter("AUTH_SEQ_KEY"=:SYS_B_4)
4 - access("AS_OF_DATE"=TO_DATE(:SYS_B_5,:SYS_B_6))
***********************************************************************************
Best Plan
***********************************************************************************
comment on table authorization is 'best';
alter session set tracefile_identifier = 'Best';
alter session set events '10053 trace name context forever, level 1';
UPDATE AUTHORIZATION
SET AMORTZ_UNIT_AMT = 1 , INCON_SECURITY_AMT = 2 , OVERSTEP_DATE = to_date('04/01/08', 'MM/DD/YY')
WHERE AUTH_SEQ_KEY = 'CPT22113622211362C02' AND AS_OF_DATE = to_date('04/01/08', 'MM/DD/YY');
UPDATE AUTHORIZATION
SET AMORTZ_UNIT_AMT = 1 , INCON_SECURITY_AMT = 2 , OVERSTEP_DATE = to_date('04/06/08', 'MM/DD/YY')
WHERE AUTH_SEQ_KEY = 'CPT22113622211362C02' AND AS_OF_DATE = to_date('04/06/08', 'MM/DD/YY');
alter session set events '10053 trace name context off';
Execution plan from the trace file:
============
Plan Table
============
-------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-------------------------------------------+-----------------------------------+
| 0 | UPDATE STATEMENT | | | | 3 | |
| 1 | UPDATE | AUTHORIZATION| | | | |
| 2 | INDEX UNIQUE SCAN | SYS_C0010238 | 1 | 53 | 2 | 00:00:01 |
-------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - access("AUTH_SEQ_KEY"='CPT22113622211362C02' AND "AS_OF_DATE"=TO_DATE('04/01/08','MM/DD/YY'))
I hope, I have furnished all the required information for you to analyze my situation. If you need any other information, please let me know.
Your comments are appreciated.
Take care !!!
Warm Regards
Momen
April 09, 2008 - 2:20 pm UTC
when i have to page down multiple times for a review followup, it ceases being a review followup and becomes a new question - but it cannot be a new question because I'm in the review followup section and hence too busy to be processing new questions.
that and all we see here are relatively useless explain plans, no tkprofs to compare them again (since "empty partition" pops up, likely culprit is "statistics")
Explain plan Understanding in Partitioned tables.
Rajeshwaran, Jeyabal, February 02, 2011 - 8:26 am UTC
rajesh@10GR2> create table sales
2 (
3 SALES_ID ,
4 CUST_ID ,
5 QUANTITY_SOLD ,
6 AMOUNT_SOLD ,
7 CUST_DATE
8 )
9 partition by range(CUST_DATE)
10 subpartition by hash(cust_id)
11 subpartition template
12 (
13 subpartition sp1,
14 subpartition sp2,
15 subpartition sp3,
16 subpartition sp4
17 )
18 (
19 partition p_q1_2005 values less than ( to_date('01-apr-2005','dd-mon-yyyy') ),
20 partition p_q2_2005 values less than ( to_date('01-jul-2005','dd-mon-yyyy') ),
21 partition p_q3_2005 values less than ( to_date('01-oct-2005','dd-mon-yyyy') ),
22 partition p_q4_2005 values less than ( to_date('01-jan-2006','dd-mon-yyyy') ),
23 partition p_q1_2006 values less than ( to_date('01-apr-2006','dd-mon-yyyy') ),
24 partition p_q2_2006 values less than ( to_date('01-jul-2006','dd-mon-yyyy') ),
25 partition p_q3_2006 values less than ( to_date('01-oct-2006','dd-mon-yyyy') ),
26 partition p_q4_2006 values less than ( to_date('01-jan-2007','dd-mon-yyyy') ),
27 partition p_q1_2007 values less than ( to_date('01-apr-2007','dd-mon-yyyy') ),
28 partition p_q2_2007 values less than ( to_date('01-jul-2007','dd-mon-yyyy') ),
29 partition p_q3_2007 values less than ( to_date('01-oct-2007','dd-mon-yyyy') ),
30 partition p_q4_2007 values less than ( to_date('01-jan-2008','dd-mon-yyyy') ),
31 partition p_q1_2008 values less than ( to_date('01-apr-2008','dd-mon-yyyy') ),
32 partition p_q2_2008 values less than ( to_date('01-jul-2008','dd-mon-yyyy') ),
33 partition p_q3_2008 values less than ( to_date('01-oct-2008','dd-mon-yyyy') ),
34 partition p_q4_2008 values less than ( to_date('01-jan-2009','dd-mon-yyyy') ),
35 partition p_q1_2009 values less than ( to_date('01-apr-2009','dd-mon-yyyy') ),
36 partition p_q2_2009 values less than ( to_date('01-jul-2009','dd-mon-yyyy') ),
37 partition p_q3_2009 values less than ( to_date('01-oct-2009','dd-mon-yyyy') ),
38 partition p_q4_2009 values less than ( to_date('01-jan-2010','dd-mon-yyyy') ),
39 partition p_q1_2010 values less than ( to_date('01-apr-2010','dd-mon-yyyy') ),
40 partition p_q2_2010 values less than ( to_date('01-jul-2010','dd-mon-yyyy') ),
41 partition p_q3_2010 values less than ( to_date('01-oct-2010','dd-mon-yyyy') ),
42 partition p_q4_2010 values less than ( to_date('01-jan-2011','dd-mon-yyyy') ),
43 partition p_q1_2011 values less than ( to_date('01-apr-2011','dd-mon-yyyy') ),
44 partition p_q2_2011 values less than ( to_date('01-jul-2011','dd-mon-yyyy') ),
45 partition p_q3_2011 values less than ( to_date('01-oct-2011','dd-mon-yyyy') ),
46 partition p_q4_2011 values less than ( to_date('01-jan-2012','dd-mon-yyyy') )
47 )
48 nologging
49 as
50 select SALES_ID ,
51 CUST_ID ,
52 QUANTITY_SOLD ,
53 AMOUNT_SOLD ,
54 trunc(CUST_DATE)
55 from sales_data;
Table created.
Elapsed: 00:01:50.23
rajesh@10GR2>
rajesh@10GR2> select cust_id,sum(amount_sold)
2 from sales
3 group by cust_id
4 having sum(amount_sold) > 10000
5 /
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 3947202833
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 13043 (9)| 00:02:37 | | |
|* 1 | FILTER | | | | | | | |
| 2 | PARTITION HASH ALL | | 1 | 9 | 13043 (9)| 00:02:37 | 1 | 4 |
| 3 | HASH GROUP BY | | 1 | 9 | 13043 (9)| 00:02:37 | | |
| 4 | PARTITION RANGE ALL| | 11M| 101M| 12107 (2)| 00:02:26 | 1 | 28 |
| 5 | TABLE ACCESS FULL | SALES | 11M| 101M| 12107 (2)| 00:02:26 | 1 | 112 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(SUM("AMOUNT_SOLD")>10000)
rajesh@10GR2>
rajesh@10GR2> set autotrace off;
Tom:
The tables sales is partitioned by quarter by RANGE for 7 years (so 28 Range partitions) and each is sub-partitioned on Hash by 4 (so total 112 Hash partitions).
The step (id=2) say that its started at 1 (Pstart =1) and ended at 4 (Pstop=4), What does they convey? Are they represents 4 Range partitions (or) 4 Hash subpartitions?
February 02, 2011 - 8:48 am UTC
I've said this before... your examples SHOULD BE, definitely COULD BE, much much much MUCH smaller. Strive for conciseness coupled with completeness.
they are four partitions - your four hash partitions (it says partition hash all - they cannot be your range partitions). It is hitting your 28 range partitions and within them - all four of their hash partitions.
Dynamic partition pruning
A reader, June 30, 2011 - 11:29 am UTC
I was try to find the performance disparity between static partition pruning and dynamic partition pruning but the result could tell the difference. Would you please tell me if there is any performance difference between static pruning and dynamic pruning in theory?
Thanks.
July 01, 2011 - 8:57 am UTC
there would not be - if we prune - we prune and we do less work. Doesn't matter if we prune at compile time or during run time - if we only hit N partitions - it would take about the same amount of time for static or dynamic.
With static pruning we can sometimes get a *better plan* - since we can use local statistics on a single partition perhaps. But as long as the plans do the same amount of work - it won't really matter.
JOIN FILTER USE is missing in 19.11 Vs 12.1.0.2
A reader, December 15, 2021 - 8:36 am UTC
I am seeing after upgrade from 12.1.0.2 to 19.11, explain plan has changed. Oracle query is running for long. We see that Oracle is doing full san partitions. in 12.1.0.2, Oracle was using "JOIN FILTER USE" but its missing in 19.11 plan.
What is the reason for this missing in 19.11 ?
12.1.0.2 plan:
26 | JOIN FILTER USE | :BF0001 | 23G| 891G| | 6703K (11)| | | | |
| 27 | PARTITION RANGE JOIN-FILTER | | 23G| 891G| | 6703K (11)| | | | |
December 15, 2021 - 11:48 am UTC
There are a huge number of possible reasons why the plan has changed - it's pretty much impossible for us to know exactly which by only looking at two lines of one plan!
If you can share all of both plans we may be able to help diagnose further.
JOIN FILTER USE is missing in 19.11 Vs 12.1.0.2
A reader, December 16, 2021 - 7:55 am UTC
Hey Chris,
Please find attached below the execution plan .
On 12.1.0.2:
**********
SQL_ID 9vcnv1knmv6a6
--------------------
WITH RUNDATE AS ( SELECT TO_DATE(:1 ) AS RUN_DATE, :2 AS
LIQ_LOOKBACK FROM DUAL ), ALL_BATCHES AS ( SELECT
BATCH_ID, SNAP_ID, BATCH_NAME, BATCH_DATE, IS_ACTIVE FROM
OP_METRICS.E_BATCH_JOB WHERE BATCH_NAME IN
('USIG_1600NY_Pricing', 'USHY_1600NY_Pricing', '2200NY_LIQ')
AND BATCH_DATE BETWEEN (SELECT RUN_DATE - LIQ_LOOKBACK FROM
RUNDATE) AND (SELECT RUN_DATE FROM RUNDATE) AND BATCH_STATUS
!= 'SCHEDULED' ), BATCHES AS ( SELECT /*+
materialize */ BATCH_ID, SNAP_ID, BATCH_NAME, BATCH_DATE, IS_ACTIVE
FROM ALL_BATCHES WHERE BATCH_NAME IN
('USIG_1600NY_Pricing', 'USHY_1600NY_Pricing') AND BATCH_DATE = (SELECT
RUN_DATE FROM RUNDATE) UNION ALL SELECT /*+
materialize */ BATCH_ID, SNAP_ID, BATCH_NAME, BATCH_DATE, IS_ACTIVE
FROM ALL_BATCHES WHERE BATCH_NAME IN ('2200NY_LIQ')
AND BATCH_DATE = ( SELECT
MAX(BATCH_DATE) FROM ALL_BATCHES
WHERE BATCH_NAME IN ('2200NY_LIQ') ) ),
PRICES AS ( SELECT P.INSTRUMENT_ID,
GREATEST(S.MODIFIED_DURATION, 0)
AS MODIFIED_DURATION,
MIN(CASE WHEN S.MODIFIED_DURATION != 0 THEN S.MODIFIED_DURATION
END) OVER ()
AS
MIN_DURATION, GREATEST(P.BID_YIELD, 0)
AS BID_YIELD, PERCENTILE_DISC(0.97) WITHIN GROUP (ORDER
BY GREATEST(P.BID_YIELD, 0) ASC) OVER
(PARTITION BY CASE WHEN P.BID_YIELD IS NULL THEN 0 ELSE 1 END)
AS BID_YIELD_97TH, PERCENT_RANK()
OVER (PARTITION BY CASE WHEN P.BID_YIELD IS NULL
THEN 0 ELSE 1 END ORDER BY GREATEST(P.BID_YIELD, 0) ASC) AS
BID_YIELD_RANK FROM BATCHES B JOIN
EODP.FF_INC_PRICE P ON B.SNAP_ID
= P.SNAP_ID AND B.BATCH_ID = P.BATCH_ID AND P.SNAP_DATE = B.BATCH_DATE
AND P.PRICE_TYPE_ID = 5 LEFT JOIN
EODP.F_SENSITIVITY S ON P.SENSITIVITY_ID = S.SENSITIVITY_ID AND
P.SNAP_ID = S.SNAP_ID AND P.INSTRUMENT_ID = S.INSTRUMENT_ID
WHERE B.BATCH_NAME IN ('USIG_1600NY_Pricing', 'USHY_1600NY_Pricing')
), INSTRUMENTS AS ( SELECT DISTINCT INSTRUMENT_ID
FROM PRICES ), LIQ AS ( SELECT
LB.INSTRUMENT_ID, LB.AMIHUD, U.LIQ_SCORE FROM BATCHES B
JOIN LIQ.F_LIQ_BASE LB
ON B.BATCH_ID = LB.BATCH_ID AND B.SNAP_ID = LB.SNAP_ID AND
B.BATCH_DATE = LB.SNAP_DATE JOIN
LIQ.F_LIQ_UNIVERSE U ON
LB.BATCH_ID = U.BATCH_ID AND LB.SNAP_ID = U.SNAP_ID AND LB.SNAP_DATE =
U.SNAP_DATE AND LB.INSTRUMENT_ID =
U.INSTRUMENT_ID AND U.UNIVERSE_GROUP_ID = 2 WHERE B.BATCH_NAME
= '2200NY_LIQ' ), FI AS ( SELECT
INSTRUMENT_ID, OUTSTANDING_AMOUNT, ISSUE_AMOUNT, CURRENT_COUPON_RATE,
MATURITY_DATE FROM IG.F_INCOME ), MASTER AS
( SELECT INSTRUMENT_ID, ISSUE_DATE, ORGANIZATION_ID
FROM IG.INSTR_MASTER ), RATINGS AS ( SELECT
R.INSTRUMENT_ID, R.RATING,
T.DESCRIPTION
AS AGENCY, ROW_NUMBER() OVER
(PARTITION BY R.INSTRUMENT_ID, R.RATING_AGENCY_CODE ORDER BY
R.RATING_DATE DESC) AS RN FROM IG.INSTR_RATINGS R
JOIN IG.RATING_AGENCY_TYPES T ON R.RATING_AGENCY_CODE
= T.VALUE
Plan hash value: 2447948827
----------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 7492K(100)| | | |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | | | |
| 2 | LOAD AS SELECT | | | | | | | | |
| 3 | INLIST ITERATOR | | | | | | | | |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED | E_BATCH_JOB | 15 | 825 | | 23 (0)| 00:00:01 | | |
| 5 | INDEX RANGE SCAN | E_BATCH_JOB_UK1 | 20 | | | 5 (0)| 00:00:01 | | |
| 6 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 | | |
| 7 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 | | |
| 8 | LOAD AS SELECT | | | | | | | | |
| 9 | UNION-ALL | | | | | | | | |
| 10 | VIEW | | 15 | 1350 | | 2 (0)| 00:00:01 | | |
| 11 | TABLE ACCESS STORAGE FULL | SYS_TEMP_0FD9FC8C7_FE44BEBD | 15 | 660 | | 2 (0)| 00:00:01 | | |
| 12 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 | | |
| 13 | VIEW | | 15 | 1350 | | 2 (0)| 00:00:01 | | |
| 14 | TABLE ACCESS STORAGE FULL | SYS_TEMP_0FD9FC8C7_FE44BEBD | 15 | 660 | | 2 (0)| 00:00:01 | | |
| 15 | SORT AGGREGATE | | 1 | 61 | | | | | |
| 16 | VIEW | | 15 | 915 | | 2 (0)| 00:00:01 | | |
| 17 | TABLE ACCESS STORAGE FULL | SYS_TEMP_0FD9FC8C7_FE44BEBD | 15 | 660 | | 2 (0)| 00:00:01 | | |
| 18 | LOAD AS SELECT | | | | | | | | |
| 19 | WINDOW SORT | | 30 | 4500 | | 6714K (12)| 00:04:23 | | |
| 20 | NESTED LOOPS OUTER | | 30 | 4500 | | 6714K (12)| 00:04:23 | | |
| 21 | HASH JOIN | | 30 | 3810 | | 6713K (12)| 00:04:23 | | |
| 22 | JOIN FILTER CREATE | :BF0001 | 30 | 2610 | | 2 (0)| 00:00:01 | | |
| 23 | PART JOIN FILTER CREATE | :BF0000 | 30 | 2610 | | 2 (0)| 00:00:01 | | |
| 24 | VIEW | | 30 | 2610 | | 2 (0)| 00:00:01 | | |
| 25 | TABLE ACCESS STORAGE FULL | SYS_TEMP_0FD9FC8C8_FE44BEBD | 30 | 2700 | | 2 (0)| 00:00:01 | | |
| 26 | JOIN FILTER USE | :BF0001 | 23G| 890G| | 6606K (11)| 00:04:19 | | |
| 27 | PARTITION RANGE JOIN-FILTER | | 23G| 890G| | 6606K (11)| 00:04:19 |:BF0000|:BF0000|
| 28 | PARTITION HASH ALL | | 23G| 890G| | 6606K (11)| 00:04:19 | 1 | LAST |
| 29 | TABLE ACCESS STORAGE FULL | FF_INC_PRICE | 23G| 890G| | 6606K (11)| 00:04:19 | 1 |1048575|
| 30 | TABLE ACCESS BY GLOBAL INDEX ROWID | F_SENSITIVITY | 1 | 23 | | 4 (0)| 00:00:01 | ROWID | ROWID |
| 31 | INDEX UNIQUE SCAN | F_SENSITIVITY_PK | 1 | | | 3 (0)| 00:00:01 | | |
| 32 | LOAD AS SELECT | | | | | | | | |
| 33 | WINDOW SORT | | 16M| 4174M| 4473M| 756K (2)| 00:00:30 | | |
| 34 | HASH JOIN | | 16M| 4174M| | 63406 (16)| 00:00:03 | | |
| 35 | JOIN FILTER CREATE | :BF0000 | 6 | 1488 | | 4 (0)| 00:00:01 | | |
| 36 | MERGE JOIN CARTESIAN | | 6 | 1488 | | 4 (0)| 00:00:01 | | |
| 37 | TABLE ACCESS STORAGE FULL | RATING_TYPES | 1 | 124 | | 2 (0)| 00:00:01 | | |
| 38 | BUFFER SORT | | 6 | 744 | | 2 (0)| 00:00:01 | | |
| 39 | TABLE ACCESS STORAGE FULL | RATING_AGENCY_TYPES | 6 | 744 | | 2 (0)| 00:00:01 | | |
| 40 | JOIN FILTER USE | :BF0000 | 368M| 8791M| | 61749 (13)| 00:00:03 | | |
| 41 | TABLE ACCESS STORAGE FULL | INSTR_RATINGS | 368M| 8791M| | 61749 (13)| 00:00:03 | | |
| 42 | HASH JOIN OUTER | | 3 | 1890 | | 22355 (6)| 00:00:01 | | |
| 43 | HASH JOIN OUTER | | 2 | 946 | | 14957 (6)| 00:00:01 | | |
| 44 | NESTED LOOPS OUTER | | 1 | 316 | | 7559 (6)| 00:00:01 | | |
| 45 | HASH JOIN OUTER | | 1 | 288 | | 7407 (6)| 00:00:01 | | |
| 46 | HASH JOIN OUTER | | 1 | 210 | | 7405 (6)| 00:00:01 | | |
| 47 | NESTED LOOPS OUTER | | 1 | 53 | | 7 (15)| 00:00:01 | | |
| 48 | NESTED LOOPS OUTER | | 1 | 30 | | 5 (20)| 00:00:01 | | |
| 49 | VIEW | | 1 | 13 | | 3 (34)| 00:00:01 | | |
| 50 | HASH UNIQUE | | 1 | 7 | | 3 (34)| 00:00:01 | | |
| 51 | VIEW | | 1 | 7 | | 2 (0)| 00:00:01 | | |
| 52 | TABLE ACCESS STORAGE FULL | SYS_TEMP_0FD9FC8C9_FE44BEBD | 1 | 72 | | 2 (0)| 00:00:01 | | |
| 53 | TABLE ACCESS BY INDEX ROWID | INSTR_MASTER | 1 | 17 | | 2 (0)| 00:00:01 | | |
| 54 | INDEX UNIQUE SCAN | INSTR_MASTER_PK | 1 | | | 1 (0)| 00:00:01 | | |
| 55 | TABLE ACCESS BY INDEX ROWID | F_INCOME | 1 | 23 | | 2 (0)| 00:00:01 | | |
| 56 | INDEX UNIQUE SCAN | F_INCOME_PK | 1 | | | 1 (0)| 00:00:01 | | |
| 57 | VIEW | | 16M| 2400M| | 7326 (5)| 00:00:01 | | |
| 58 | TABLE ACCESS STORAGE FULL | SYS_TEMP_0FD9FC8CA_FE44BEBD | 16M| 2217M| | 7326 (5)| 00:00:01 | | |
| 59 | VIEW | | 1 | 78 | | 2 (0)| 00:00:01 | | |
| 60 | TABLE ACCESS STORAGE FULL | SYS_TEMP_0FD9FC8C9_FE44BEBD | 1 | 72 | | 2 (0)| 00:00:01 | | |
| 61 | VIEW PUSHED PREDICATE | | 1 | 28 | | 151 (0)| 00:00:01 | | |
| 62 | NESTED LOOPS | | 1 | 156 | | 151 (0)| 00:00:01 | | |
| 63 | NESTED LOOPS | | 30 | 156 | | 151 (0)| 00:00:01 | | |
| 64 | NESTED LOOPS | | 30 | 3690 | | 62 (0)| 00:00:01 | | |
| 65 | VIEW | | 30 | 2610 | | 2 (0)| 00:00:01 | | |
| 66 | TABLE ACCESS STORAGE FULL | SYS_TEMP_0FD9FC8C8_FE44BEBD | 30 | 2700 | | 2 (0)| 00:00:01 | | |
| 67 | PARTITION RANGE ITERATOR | | 1 | 36 | | 2 (0)| 00:00:01 | KEY | KEY |
| 68 | TABLE ACCESS BY LOCAL INDEX ROWID| F_LIQ_BASE | 1 | 36 | | 2 (0)| 00:00:01 | KEY | KEY |
| 69 | INDEX UNIQUE SCAN | F_LIQ_BASE_UNQ_CONST | 1 | | | 1 (0)| 00:00:01 | KEY | KEY |
| 70 | PARTITION RANGE ITERATOR | | 1 | | | 2 (0)| 00:00:01 | KEY | KEY |
| 71 | INDEX UNIQUE SCAN | F_LIQ_UNIVERSE_UNQ_CONST | 1 | | | 2 (0)| 00:00:01 | KEY | KEY |
| 72 | TABLE ACCESS BY LOCAL INDEX ROWID | F_LIQ_UNIVERSE | 1 | 33 | | 3 (0)| 00:00:01 | 1 | 1 |
| 73 | VIEW | | 16M| 2400M| | 7326 (5)| 00:00:01 | | |
| 74 | TABLE ACCESS STORAGE FULL | SYS_TEMP_0FD9FC8CA_FE44BEBD | 16M| 2217M| | 7326 (5)| 00:00:01 | | |
| 75 | VIEW | | 16M| 2400M| | 7326 (5)| 00:00:01 | | |
| 76 | TABLE ACCESS STORAGE FULL | SYS_TEMP_0FD9FC8CA_FE44BEBD | 16M| 2217M| | 7326 (5)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$7C70BD48
2 - SEL$22
4 - SEL$22 / E_BATCH_JOB@SEL$22
5 - SEL$22 / E_BATCH_JOB@SEL$22
6 - SEL$CBF63DBF / DUAL@SEL$21
7 - SEL$11594485 / DUAL@SEL$21
8 - SET$1
10 - SEL$96EC8427 / ALL_BATCHES@SEL$25
11 - SEL$96EC8427 / T1@SEL$96EC8427
12 - SEL$CEF22074 / DUAL@SEL$21
13 - SEL$96EC8426 / ALL_BATCHES@SEL$27
14 - SEL$96EC8426 / T1@SEL$96EC8426
15 - SEL$28
16 - SEL$96EC8425 / ALL_BATCHES@SEL$28
17 - SEL$96EC8425 / T1@SEL$96EC8425
18 - SEL$45D930ED
24 - SEL$298E7975 / B@SEL$19
25 - SEL$298E7975 / T1@SEL$298E7975
29 - SEL$45D930ED / P@SEL$19
30 - SEL$45D930ED / S@SEL$20
31 - SEL$45D930ED / S@SEL$20
32 - SEL$B21719C5
37 - SEL$B21719C5 / Y@SEL$16
39 - SEL$B21719C5 / T@SEL$15
41 - SEL$B21719C5 / R@SEL$15
49 - SEL$30 / I@SEL$2
50 - SEL$30
51 - SEL$B75AFF3E / PRICES@SEL$30
52 - SEL$B75AFF3E / T1@SEL$B75AFF3E
53 - SEL$7C70BD48 / INSTR_MASTER@SEL$33
54 - SEL$7C70BD48 / INSTR_MASTER@SEL$33
55 - SEL$7C70BD48 / F_INCOME@SEL$32
56 - SEL$7C70BD48 / F_INCOME@SEL$32
57 - SEL$E2979EE2 / R_SP@SEL$11
58 - SEL$E2979EE2 / T1@SEL$E2979EE2
59 - SEL$B75AFF3D / P@SEL$1
60 - SEL$B75AFF3D / T1@SEL$B75AFF3D
61 - SEL$F84B98B6 / L@SEL$13
62 - SEL$F84B98B6
65 - SEL$298E7974 / B@SEL$17
66 - SEL$298E7974 / T1@SEL$298E7974
68 - SEL$F84B98B6 / LB@SEL$17
69 - SEL$F84B98B6 / LB@SEL$17
71 - SEL$F84B98B6 / U@SEL$18
72 - SEL$F84B98B6 / U@SEL$18
73 - SEL$E2979EE1 / R_FITCH@SEL$9
74 - SEL$E2979EE1 / T1@SEL$E2979EE1
75 - SEL$E2979EE0 / R_MOODY@SEL$7
76 - SEL$E2979EE0 / T1@SEL$E2979EE0
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
DB_VERSION('12.1.0.2')
OPT_PARAM('_optimizer_use_feedback' 'false')
OPT_PARAM('_px_adaptive_dist_method' 'off')
OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')
OPT_PARAM('_optimizer_gather_feedback' 'false')
OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$CBF63DBF")
MERGE(@"SEL$BFEE6DA6")
OUTLINE_LEAF(@"SEL$11594485")
MERGE(@"SEL$BFEE6DA5")
OUTLINE_LEAF(@"SEL$22")
OUTLINE_LEAF(@"SEL$CEF22074")
MERGE(@"SEL$21")
OUTLINE_LEAF(@"SEL$96EC8427")
OUTLINE_LEAF(@"SEL$25")
OUTLINE_LEAF(@"SEL$96EC8425")
MATERIALIZE(@"SEL$22")
OUTLINE_LEAF(@"SEL$28")
OUTLINE_LEAF(@"SEL$96EC8426")
OUTLINE_LEAF(@"SEL$27")
OUTLINE_LEAF(@"SET$1")
OUTLINE_LEAF(@"SEL$298E7975")
OUTLINE_LEAF(@"SEL$45D930ED")
MERGE(@"SEL$D2DE2538")
OUTLINE_LEAF(@"SEL$B21719C5")
MERGE(@"SEL$803786B2")
OUTLINE_LEAF(@"SEL$B75AFF3E")
OUTLINE_LEAF(@"SEL$30")
OUTLINE_LEAF(@"SEL$B75AFF3D")
MATERIALIZE(@"SEL$45D930ED")
OUTLINE_LEAF(@"SEL$E2979EE0")
MATERIALIZE(@"SEL$B21719C5")
OUTLINE_LEAF(@"SEL$E2979EE1")
OUTLINE_LEAF(@"SEL$E2979EE2")
OUTLINE_LEAF(@"SEL$298E7974")
MATERIALIZE(@"SET$1")
OUTLINE_LEAF(@"SEL$F84B98B6")
PUSH_PRED(@"SEL$7C70BD48" "L"@"SEL$13" 1)
OUTLINE_LEAF(@"SEL$7C70BD48")
MERGE(@"SEL$32")
MERGE(@"SEL$33")
OUTLINE(@"SEL$23")
OUTLINE(@"SEL$BFEE6DA6")
OUTLINE(@"SEL$24")
OUTLINE(@"SEL$BFEE6DA5")
OUTLINE(@"SEL$26")
OUTLINE(@"SEL$21")
OUTLINE(@"SEL$22")
OUTLINE(@"SET$1")
OUTLINE(@"SEL$7153C5F2")
ANSI_REARCH(@"SEL$29")
OUTLINE(@"SEL$D2DE2538")
MERGE(@"SEL$19")
OUTLINE(@"SEL$34")
OUTLINE(@"SEL$803786B2")
MERGE(@"SEL$15")
OUTLINE(@"SEL$45D930ED")
MERGE(@"SEL$D2DE2538")
OUTLINE(@"SEL$B21719C5")
MERGE(@"SEL$803786B2")
OUTLINE(@"SEL$ED95C77E")
MERGE(@"SEL$D50F4D64")
OUTLINE(@"SEL$7C70BD48")
MERGE(@"SEL$32")
MERGE(@"SEL$33")
OUTLINE(@"SEL$28ED9374")
MERGE(@"SEL$4E060E5B")
OUTLINE(@"SEL$32")
OUTLINE(@"SEL$33")
OUTLINE(@"SEL$29")
OUTLINE(@"SEL$2FEECD4C")
ANSI_REARCH(@"SEL$20")
OUTLINE(@"SEL$19")
OUTLINE(@"SEL$16")
OUTLINE(@"SEL$15")
OUTLINE(@"SEL$31")
OUTLINE(@"SEL$D50F4D64")
MERGE(@"SEL$17")
OUTLINE(@"SEL$35")
OUTLINE(@"SEL$4E060E5B")
MERGE(@"SEL$13")
MERGE(@"SEL$72AEFE3E")
OUTLINE(@"SEL$20")
OUTLINE(@"SEL$18")
OUTLINE(@"SEL$17")
OUTLINE(@"SEL$14")
OUTLINE(@"SEL$13")
OUTLINE(@"SEL$72AEFE3E")
MERGE(@"SEL$11")
MERGE(@"SEL$B97648DD")
OUTLINE(@"SEL$12")
OUTLINE(@"SEL$11")
OUTLINE(@"SEL$B97648DD")
MERGE(@"SEL$096E5AED")
MERGE(@"SEL$9")
OUTLINE(@"SEL$10")
OUTLINE(@"SEL$096E5AED")
MERGE(@"SEL$15E987C1")
MERGE(@"SEL$7")
OUTLINE(@"SEL$9")
OUTLINE(@"SEL$8")
OUTLINE(@"SEL$15E987C1")
MERGE(@"SEL$5")
MERGE(@"SEL$7237DA6D")
OUTLINE(@"SEL$7")
OUTLINE(@"SEL$6")
OUTLINE(@"SEL$5")
OUTLINE(@"SEL$7237DA6D")
MERGE(@"SEL$3")
MERGE(@"SEL$58A6D7F6")
OUTLINE(@"SEL$4")
OUTLINE(@"SEL$3")
OUTLINE(@"SEL$58A6D7F6")
MERGE(@"SEL$1")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$1")
NO_ACCESS(@"SEL$7C70BD48" "I"@"SEL$2")
INDEX_RS_ASC(@"SEL$7C70BD48" "INSTR_MASTER"@"SEL$33" ("INSTR_MASTER"."INSTRUMENT_ID"))
INDEX_RS_ASC(@"SEL$7C70BD48" "F_INCOME"@"SEL$32" ("F_INCOME"."INSTRUMENT_ID"))
NO_ACCESS(@"SEL$7C70BD48" "R_SP"@"SEL$11")
NO_ACCESS(@"SEL$7C70BD48" "P"@"SEL$1")
NO_ACCESS(@"SEL$7C70BD48" "L"@"SEL$13")
NO_ACCESS(@"SEL$7C70BD48" "R_FITCH"@"SEL$9")
NO_ACCESS(@"SEL$7C70BD48" "R_MOODY"@"SEL$7")
LEADING(@"SEL$7C70BD48" "I"@"SEL$2" "INSTR_MASTER"@"SEL$33" "F_INCOME"@"SEL$32" "R_SP"@"SEL$11" "P"@"SEL$1" "L"@"SEL$13"
"R_FITCH"@"SEL$9" "R_MOODY"@"SEL$7")
USE_NL(@"SEL$7C70BD48" "INSTR_MASTER"@"SEL$33")
USE_NL(@"SEL$7C70BD48" "F_INCOME"@"SEL$32")
USE_HASH(@"SEL$7C70BD48" "R_SP"@"SEL$11")
USE_HASH(@"SEL$7C70BD48" "P"@"SEL$1")
USE_NL(@"SEL$7C70BD48" "L"@"SEL$13")
USE_HASH(@"SEL$7C70BD48" "R_FITCH"@"SEL$9")
USE_HASH(@"SEL$7C70BD48" "R_MOODY"@"SEL$7")
FULL(@"SEL$E2979EE2" "T1"@"SEL$E2979EE2")
FULL(@"SEL$E2979EE1" "T1"@"SEL$E2979EE1")
FULL(@"SEL$E2979EE0" "T1"@"SEL$E2979EE0")
FULL(@"SEL$B75AFF3D" "T1"@"SEL$B75AFF3D")
NO_ACCESS(@"SEL$30" "PRICES"@"SEL$30")
USE_HASH_AGGREGATION(@"SEL$30")
NO_ACCESS(@"SEL$F84B98B6" "B"@"SEL$17")
INDEX_RS_ASC(@"SEL$F84B98B6" "LB"@"SEL$17" ("F_LIQ_BASE"."SNAP_DATE" "F_LIQ_BASE"."INSTRUMENT_ID"
"F_LIQ_BASE"."SNAP_ID"))
INDEX(@"SEL$F84B98B6" "U"@"SEL$18" ("F_LIQ_UNIVERSE "."SNAP_DATE" "F_LIQ_UNIVERSE "."INSTRUMENT_ID"
"F_LIQ_UNIVERSE "."SNAP_ID" "F_LIQ_UNIVERSE "."UNIVERSE_GROUP_ID"))
LEADING(@"SEL$F84B98B6" "B"@"SEL$17" "LB"@"SEL$17" "U"@"SEL$18")
USE_NL(@"SEL$F84B98B6" "LB"@"SEL$17")
USE_NL(@"SEL$F84B98B6" "U"@"SEL$18")
NLJ_BATCHING(@"SEL$F84B98B6" "U"@"SEL$18")
FULL(@"SEL$298E7974" "T1"@"SEL$298E7974")
FULL(@"SEL$B75AFF3E" "T1"@"SEL$B75AFF3E")
FULL(@"SEL$B21719C5" "Y"@"SEL$16")
FULL(@"SEL$B21719C5" "T"@"SEL$15")
FULL(@"SEL$B21719C5" "R"@"SEL$15")
LEADING(@"SEL$B21719C5" "Y"@"SEL$16" "T"@"SEL$15" "R"@"SEL$15")
USE_MERGE_CARTESIAN(@"SEL$B21719C5" "T"@"SEL$15")
USE_HASH(@"SEL$B21719C5" "R"@"SEL$15")
PX_JOIN_FILTER(@"SEL$B21719C5" "R"@"SEL$15")
NO_ACCESS(@"SEL$45D930ED" "B"@"SEL$19")
FULL(@"SEL$45D930ED" "P"@"SEL$19")
INDEX_RS_ASC(@"SEL$45D930ED" "S"@"SEL$20" ("F_SENSITIVITY"."SENSITIVITY_ID"))
LEADING(@"SEL$45D930ED" "B"@"SEL$19" "P"@"SEL$19" "S"@"SEL$20")
USE_HASH(@"SEL$45D930ED" "P"@"SEL$19")
USE_NL(@"SEL$45D930ED" "S"@"SEL$20")
PX_JOIN_FILTER(@"SEL$45D930ED" "P"@"SEL$19")
INDEX_RS_ASC(@"SEL$22" "E_BATCH_JOB"@"SEL$22" ("E_BATCH_JOB"."BATCH_NAME" "E_BATCH_JOB"."BATCH_DATE"
"E_BATCH_JOB"."BATCH_INSTANCE_NO"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$22" "E_BATCH_JOB"@"SEL$22")
PUSH_SUBQ(@"SEL$11594485")
PUSH_SUBQ(@"SEL$CBF63DBF")
ORDER_SUBQ(@"SEL$22" "SEL$CBF63DBF" "SEL$11594485")
NO_ACCESS(@"SEL$27" "ALL_BATCHES"@"SEL$27")
PUSH_SUBQ(@"SEL$28")
NO_ACCESS(@"SEL$25" "ALL_BATCHES"@"SEL$25")
PUSH_SUBQ(@"SEL$CEF22074")
FULL(@"SEL$96EC8427" "T1"@"SEL$96EC8427")
FULL(@"SEL$96EC8426" "T1"@"SEL$96EC8426")
NO_ACCESS(@"SEL$28" "ALL_BATCHES"@"SEL$28")
FULL(@"SEL$96EC8425" "T1"@"SEL$96EC8425")
FULL(@"SEL$298E7975" "T1"@"SEL$298E7975")
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
1 - :1 (DATE): 08/27/2021 00:00:00
2 - :2 (NUMBER): 7
19.11.0.0 execution plan continuation from previous post
A reader, December 16, 2021 - 7:57 am UTC
On 19.11.0.0:
=========
SQL_ID 9vcnv1knmv6a6
--------------------
WITH RUNDATE AS ( SELECT TO_DATE(:1 ) AS RUN_DATE, :2 AS
LIQ_LOOKBACK FROM DUAL ), ALL_BATCHES AS ( SELECT
BATCH_ID, SNAP_ID, BATCH_NAME, BATCH_DATE, IS_ACTIVE FROM
OP_METRICS.E_BATCH_JOB WHERE BATCH_NAME IN
('USIG_1600NY_Pricing', 'USHY_1600NY_Pricing', '2200NY_LIQ')
AND BATCH_DATE BETWEEN (SELECT RUN_DATE - LIQ_LOOKBACK FROM
RUNDATE) AND (SELECT RUN_DATE FROM RUNDATE) AND BATCH_STATUS
!= 'SCHEDULED' ), BATCHES AS ( SELECT /*+
materialize */ BATCH_ID, SNAP_ID, BATCH_NAME, BATCH_DATE, IS_ACTIVE
FROM ALL_BATCHES WHERE BATCH_NAME IN
('USIG_1600NY_Pricing', 'USHY_1600NY_Pricing') AND BATCH_DATE = (SELECT
RUN_DATE FROM RUNDATE) UNION ALL SELECT /*+
materialize */ BATCH_ID, SNAP_ID, BATCH_NAME, BATCH_DATE, IS_ACTIVE
FROM ALL_BATCHES WHERE BATCH_NAME IN ('2200NY_LIQ')
AND BATCH_DATE = ( SELECT
MAX(BATCH_DATE) FROM ALL_BATCHES
WHERE BATCH_NAME IN ('2200NY_LIQ') ) ),
PRICES AS ( SELECT P.INSTRUMENT_ID,
GREATEST(S.MODIFIED_DURATION, 0)
AS MODIFIED_DURATION,
MIN(CASE WHEN S.MODIFIED_DURATION != 0 THEN S.MODIFIED_DURATION
END) OVER ()
AS
MIN_DURATION, GREATEST(P.BID_YIELD, 0)
AS BID_YIELD, PERCENTILE_DISC(0.97) WITHIN GROUP (ORDER
BY GREATEST(P.BID_YIELD, 0) ASC) OVER
(PARTITION BY CASE WHEN P.BID_YIELD IS NULL THEN 0 ELSE 1 END)
AS BID_YIELD_97TH, PERCENT_RANK()
OVER (PARTITION BY CASE WHEN P.BID_YIELD IS NULL
THEN 0 ELSE 1 END ORDER BY GREATEST(P.BID_YIELD, 0) ASC) AS
BID_YIELD_RANK FROM BATCHES B JOIN
EODP.FF_INC_PRICE P ON B.SNAP_ID
= P.SNAP_ID AND B.BATCH_ID = P.BATCH_ID AND P.SNAP_DATE = B.BATCH_DATE
AND P.PRICE_TYPE_ID = 5 LEFT JOIN
EODP.F_SENSITIVITY S ON P.SENSITIVITY_ID = S.SENSITIVITY_ID AND
P.SNAP_ID = S.SNAP_ID AND P.INSTRUMENT_ID = S.INSTRUMENT_ID
WHERE B.BATCH_NAME IN ('USIG_1600NY_Pricing', 'USHY_1600NY_Pricing')
), INSTRUMENTS AS ( SELECT DISTINCT INSTRUMENT_ID
FROM PRICES ), LIQ AS ( SELECT
LB.INSTRUMENT_ID, LB.AMIHUD, U.LIQ_SCORE FROM BATCHES B
JOIN LIQ.F_LIQ_BASE LB
ON B.BATCH_ID = LB.BATCH_ID AND B.SNAP_ID = LB.SNAP_ID AND
B.BATCH_DATE = LB.SNAP_DATE JOIN
LIQ.F_LIQ_UNIVERSE U ON
LB.BATCH_ID = U.BATCH_ID AND LB.SNAP_ID = U.SNAP_ID AND LB.SNAP_DATE =
U.SNAP_DATE AND LB.INSTRUMENT_ID =
U.INSTRUMENT_ID AND U.UNIVERSE_GROUP_ID = 2 WHERE B.BATCH_NAME
= '2200NY_LIQ' ), FI AS ( SELECT
INSTRUMENT_ID, OUTSTANDING_AMOUNT, ISSUE_AMOUNT, CURRENT_COUPON_RATE,
MATURITY_DATE FROM IG.F_INCOME ), MASTER AS
( SELECT INSTRUMENT_ID, ISSUE_DATE, ORGANIZATION_ID
FROM IG.INSTR_MASTER ), RATINGS AS ( SELECT
R.INSTRUMENT_ID, R.RATING,
T.DESCRIPTION
AS AGENCY, ROW_NUMBER() OVER
(PARTITION BY R.INSTRUMENT_ID, R.RATING_AGENCY_CODE ORDER BY
R.RATING_DATE DESC) AS RN FROM IG.INSTR_RATINGS R
JOIN IG.RATING_AGENCY_TYPES T ON R.RATING_AGENCY_CODE
= T.VALUE
Plan hash value: 2545680846
----------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 7504K(100)| | | |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | | | |
| 2 | LOAD AS SELECT (CURSOR DURATION MEMORY) | SYS_TEMP_0FD9F544E_C777D925 | | | | | | | |
| 3 | INLIST ITERATOR | | | | | | | | |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED | E_BATCH_JOB | 17 | 918 | | 24 (0)| 00:00:01 | | |
| 5 | INDEX RANGE SCAN | E_BATCH_JOB_UK1 | 22 | | | 5 (0)| 00:00:01 | | |
| 6 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 | | |
| 7 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 | | |
| 8 | LOAD AS SELECT (CURSOR DURATION MEMORY) | SYS_TEMP_0FD9F544F_C777D925 | | | | | | | |
| 9 | UNION-ALL | | | | | | | | |
| 10 | VIEW | | 17 | 1530 | | 2 (0)| 00:00:01 | | |
| 11 | TABLE ACCESS STORAGE FULL | SYS_TEMP_0FD9F544E_C777D925 | 17 | 731 | | 2 (0)| 00:00:01 | | |
| 12 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 | | |
| 13 | VIEW | | 17 | 1530 | | 2 (0)| 00:00:01 | | |
| 14 | TABLE ACCESS STORAGE FULL | SYS_TEMP_0FD9F544E_C777D925 | 17 | 731 | | 2 (0)| 00:00:01 | | |
| 15 | SORT AGGREGATE | | 1 | 61 | | | | | |
| 16 | VIEW | | 17 | 1037 | | 2 (0)| 00:00:01 | | |
| 17 | TABLE ACCESS STORAGE FULL | SYS_TEMP_0FD9F544E_C777D925 | 17 | 731 | | 2 (0)| 00:00:01 | | |
| 18 | LOAD AS SELECT (CURSOR DURATION MEMORY) | SYS_TEMP_0FD9F5450_C777D925 | | | | | | | |
| 19 | WINDOW SORT | | 34 | 5100 | | 6667K (10)| 00:04:21 | | |
| 20 | NESTED LOOPS OUTER | | 34 | 5100 | | 6667K (10)| 00:04:21 | | |
| 21 | HASH JOIN | | 34 | 4318 | | 6667K (10)| 00:04:21 | | |
| 22 | VIEW | | 34 | 2958 | | 2 (0)| 00:00:01 | | |
| 23 | TABLE ACCESS STORAGE FULL | SYS_TEMP_0FD9F544F_C777D925 | 34 | 3060 | | 2 (0)| 00:00:01 | | |
| 24 | PARTITION RANGE ALL | | 24G| 910G| | 6583K (9)| 00:04:18 | 1 |1048575|
| 25 | PARTITION HASH ALL | | 24G| 910G| | 6583K (9)| 00:04:18 | 1 | LAST |
| 26 | TABLE ACCESS STORAGE FULL | FF_INC_PRICE | 24G| 910G| | 6583K (9)| 00:04:18 | 1 |1048575|
| 27 | TABLE ACCESS BY GLOBAL INDEX ROWID | F_SENSITIVITY | 1 | 23 | | 4 (0)| 00:00:01 | ROWID | ROWID |
| 28 | INDEX UNIQUE SCAN | F_SENSITIVITY_PK | 1 | | | 3 (0)| 00:00:01 | | |
| 29 | LOAD AS SELECT (CURSOR DURATION MEMORY) | SYS_TEMP_0FD9F5451_C777D925 | | | | | | | |
| 30 | WINDOW SORT | | 17M| 4484M| 4806M| 813K (2)| 00:00:32 | | |
| 31 | HASH JOIN | | 17M| 4484M| | 69636 (12)| 00:00:03 | | |
| 32 | MERGE JOIN CARTESIAN | | 6 | 1488 | | 4 (0)| 00:00:01 | | |
| 33 | TABLE ACCESS STORAGE FULL | RATING_TYPES | 1 | 124 | | 2 (0)| 00:00:01 | | |
| 34 | BUFFER SORT | | 6 | 744 | | 2 (0)| 00:00:01 | | |
| 35 | TABLE ACCESS STORAGE FULL | RATING_AGENCY_TYPES | 6 | 744 | | 2 (0)| 00:00:01 | | |
| 36 | TABLE ACCESS STORAGE FULL | INSTR_RATINGS | 396M| 9445M| | 68265 (10)| 00:00:03 | | |
| 37 | HASH JOIN OUTER | | 1 | 630 | | 23721 (5)| 00:00:01 | | |
| 38 | JOIN FILTER CREATE | :BF0000 | 1 | 473 | | 15874 (5)| 00:00:01 | | |
| 39 | NESTED LOOPS OUTER | | 1 | 473 | | 15874 (5)| 00:00:01 | | |
| 40 | HASH JOIN OUTER | | 1 | 445 | | 15703 (5)| 00:00:01 | | |
| 41 | HASH JOIN OUTER | | 1 | 367 | | 15701 (5)| 00:00:01 | | |
| 42 | JOIN FILTER CREATE | :BF0001 | 1 | 210 | | 7854 (5)| 00:00:01 | | |
| 43 | HASH JOIN OUTER | | 1 | 210 | | 7854 (5)| 00:00:01 | | |
| 44 | JOIN FILTER CREATE | :BF0002 | 1 | 53 | | 7 (15)| 00:00:01 | | |
| 45 | NESTED LOOPS OUTER | | 1 | 53 | | 7 (15)| 00:00:01 | | |
| 46 | NESTED LOOPS OUTER | | 1 | 30 | | 5 (20)| 00:00:01 | | |
| 47 | VIEW | | 1 | 13 | | 3 (34)| 00:00:01 | | |
| 48 | HASH UNIQUE | | 1 | 7 | | 3 (34)| 00:00:01 | | |
| 49 | VIEW | | 1 | 7 | | 2 (0)| 00:00:01 | | |
| 50 | TABLE ACCESS STORAGE FULL | SYS_TEMP_0FD9F5450_C777D925 | 1 | 72 | | 2 (0)| 00:00:01 | | |
| 51 | TABLE ACCESS BY INDEX ROWID | INSTR_MASTER | 1 | 17 | | 2 (0)| 00:00:01 | | |
| 52 | INDEX UNIQUE SCAN | INSTRT_MASTER_PK | 1 | | | 1 (0)| 00:00:01 | | |
| 53 | TABLE ACCESS BY INDEX ROWID | F_INCOME | 1 | 23 | | 2 (0)| 00:00:01 | | |
| 54 | INDEX UNIQUE SCAN | F_INCOME_PK | 1 | | | 1 (0)| 00:00:01 | | |
| 55 | VIEW | | 17M| 2579M| | 7788 (4)| 00:00:01 | | |
| 56 | JOIN FILTER USE | :BF0002 | 17M| 2381M| | 7788 (4)| 00:00:01 | | |
| 57 | TABLE ACCESS STORAGE FULL | SYS_TEMP_0FD9F5451_C777D925 | 17M| 2381M| | 7788 (4)| 00:00:01 | | |
| 58 | VIEW | | 17M| 2579M| | 7788 (4)| 00:00:01 | | |
| 59 | JOIN FILTER USE | :BF0001 | 17M| 2381M| | 7788 (4)| 00:00:01 | | |
| 60 | TABLE ACCESS STORAGE FULL | SYS_TEMP_0FD9F5451_C777D925 | 17M| 2381M| | 7788 (4)| 00:00:01 | | |
| 61 | VIEW | | 1 | 78 | | 2 (0)| 00:00:01 | | |
| 62 | TABLE ACCESS STORAGE FULL | SYS_TEMP_0FD9F5450_C777D925 | 1 | 72 | | 2 (0)| 00:00:01 | | |
| 63 | VIEW PUSHED PREDICATE | | 1 | 28 | | 171 (0)| 00:00:01 | | |
| 64 | NESTED LOOPS | | 1 | 156 | | 171 (0)| 00:00:01 | | |
| 65 | NESTED LOOPS | | 34 | 156 | | 171 (0)| 00:00:01 | | |
| 66 | NESTED LOOPS | | 34 | 4182 | | 70 (0)| 00:00:01 | | |
| 67 | VIEW | | 34 | 2958 | | 2 (0)| 00:00:01 | | |
| 68 | TABLE ACCESS STORAGE FULL | SYS_TEMP_0FD9F544F_C777D925 | 34 | 3060 | | 2 (0)| 00:00:01 | | |
| 69 | PARTITION RANGE ITERATOR | | 1 | 36 | | 2 (0)| 00:00:01 | KEY | KEY |
| 70 | TABLE ACCESS BY LOCAL INDEX ROWID| F_LIQ_BASE | 1 | 36 | | 2 (0)| 00:00:01 | KEY | KEY |
| 71 | INDEX UNIQUE SCAN | F_LIQ_BASE_UNQ_CONST | 1 | | | 1 (0)| 00:00:01 | KEY | KEY |
| 72 | PARTITION RANGE ITERATOR | | 1 | | | 2 (0)| 00:00:01 | KEY | KEY |
| 73 | INDEX UNIQUE SCAN | F_LIQ_UNIVERSE_UNQ_CONST | 1 | | | 2 (0)| 00:00:01 | KEY | KEY |
| 74 | TABLE ACCESS BY LOCAL INDEX ROWID | F_LIQ_UNIVERSE | 1 | 33 | | 3 (0)| 00:00:01 | 1 | 1 |
| 75 | VIEW | | 17M| 2579M| | 7788 (4)| 00:00:01 | | |
| 76 | JOIN FILTER USE | :BF0000 | 17M| 2381M| | 7788 (4)| 00:00:01 | | |
| 77 | TABLE ACCESS STORAGE FULL | SYS_TEMP_0FD9F5451_C777D925 | 17M| 2381M| | 7788 (4)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$7C70BD48
2 - SEL$22
4 - SEL$22 / E_BATCH_JOB@SEL$22
5 - SEL$22 / E_BATCH_JOB@SEL$22
6 - SEL$CBF63DBF / DUAL@SEL$21
7 - SEL$11594485 / DUAL@SEL$21
8 - SET$1
10 - SEL$96EC8427 / ALL_BATCHES@SEL$25
11 - SEL$96EC8427 / T1@SEL$96EC8427
12 - SEL$CEF22074 / DUAL@SEL$21
13 - SEL$96EC8426 / ALL_BATCHES@SEL$27
14 - SEL$96EC8426 / T1@SEL$96EC8426
15 - SEL$28
16 - SEL$96EC8425 / ALL_BATCHES@SEL$28
17 - SEL$96EC8425 / T1@SEL$96EC8425
18 - SEL$45D930ED
22 - SEL$298E7975 / B@SEL$19
23 - SEL$298E7975 / T1@SEL$298E7975
26 - SEL$45D930ED / P@SEL$19
27 - SEL$45D930ED / S@SEL$20
28 - SEL$45D930ED / S@SEL$20
29 - SEL$B21719C5
33 - SEL$B21719C5 / Y@SEL$16
35 - SEL$B21719C5 / T@SEL$15
36 - SEL$B21719C5 / R@SEL$15
47 - SEL$30 / I@SEL$2
48 - SEL$30
49 - SEL$3E1B8096 / PRICES@SEL$30
50 - SEL$3E1B8096 / T1@SEL$3E1B8096
51 - SEL$7C70BD48 / INSTR_MASTER@SEL$33
52 - SEL$7C70BD48 / INSTR_MASTER@SEL$33
53 - SEL$7C70BD48 / F_INCOME@SEL$32
54 - SEL$7C70BD48 / F_INCOME@SEL$32
55 - SEL$E4433E7A / R_SP@SEL$11
57 - SEL$E4433E7A / T1@SEL$E4433E7A
58 - SEL$E4433E79 / R_FITCH@SEL$9
60 - SEL$E4433E79 / T1@SEL$E4433E79
61 - SEL$3E1B8095 / P@SEL$1
62 - SEL$3E1B8095 / T1@SEL$3E1B8095
63 - SEL$F84B98B6 / L@SEL$13
64 - SEL$F84B98B6
67 - SEL$298E7974 / B@SEL$17
68 - SEL$298E7974 / T1@SEL$298E7974
70 - SEL$F84B98B6 / LB@SEL$17
71 - SEL$F84B98B6 / LB@SEL$17
73 - SEL$F84B98B6 / U@SEL$18
74 - SEL$F84B98B6 / U@SEL$18
75 - SEL$E4433E78 / R_MOODY@SEL$7
77 - SEL$E4433E78 / T1@SEL$E4433E78
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
OPT_PARAM('_px_adaptive_dist_method' 'off')
OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')
OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$CBF63DBF")
MERGE(@"SEL$BFEE6DA6" >"SEL$23")
OUTLINE_LEAF(@"SEL$11594485")
MERGE(@"SEL$BFEE6DA5" >"SEL$24")
OUTLINE_LEAF(@"SEL$22")
OUTLINE_LEAF(@"SEL$CEF22074")
MERGE(@"SEL$21" >"SEL$26")
OUTLINE_LEAF(@"SEL$96EC8427")
OUTLINE_LEAF(@"SEL$25")
OUTLINE_LEAF(@"SEL$96EC8425")
MATERIALIZE(@"SEL$22")
OUTLINE_LEAF(@"SEL$28")
OUTLINE_LEAF(@"SEL$96EC8426")
OUTLINE_LEAF(@"SEL$27")
OUTLINE_LEAF(@"SET$1")
OUTLINE_LEAF(@"SEL$298E7975")
OUTLINE_LEAF(@"SEL$45D930ED")
MERGE(@"SEL$D2DE2538" >"SEL$7153C5F2")
OUTLINE_LEAF(@"SEL$B21719C5")
MERGE(@"SEL$803786B2" >"SEL$34")
OUTLINE_LEAF(@"SEL$3E1B8096")
OUTLINE_LEAF(@"SEL$30")
OUTLINE_LEAF(@"SEL$3E1B8095")
MATERIALIZE(@"SEL$29")
OUTLINE_LEAF(@"SEL$E4433E78")
MATERIALIZE(@"SEL$34")
OUTLINE_LEAF(@"SEL$E4433E79")
OUTLINE_LEAF(@"SEL$E4433E7A")
OUTLINE_LEAF(@"SEL$298E7974")
MATERIALIZE(@"SET$1")
OUTLINE_LEAF(@"SEL$F84B98B6")
PUSH_PRED(@"SEL$7C70BD48" "L"@"SEL$13" 1)
OUTLINE_LEAF(@"SEL$7C70BD48")
MERGE(@"SEL$32" >"SEL$28ED9374")
MERGE(@"SEL$33" >"SEL$28ED9374")
OUTLINE(@"SEL$23")
OUTLINE(@"SEL$BFEE6DA6")
OUTLINE(@"SEL$24")
OUTLINE(@"SEL$BFEE6DA5")
OUTLINE(@"SEL$26")
OUTLINE(@"SEL$21")
OUTLINE(@"SEL$7153C5F2")
ANSI_REARCH(@"SEL$29")
OUTLINE(@"SEL$D2DE2538")
MERGE(@"SEL$19" >"SEL$2FEECD4C")
OUTLINE(@"SEL$34")
OUTLINE(@"SEL$803786B2")
MERGE(@"SEL$15" >"SEL$16")
OUTLINE(@"SEL$29")
OUTLINE(@"SEL$ED95C77E")
MERGE(@"SEL$D50F4D64" >"SEL$31")
OUTLINE(@"SEL$28ED9374")
MERGE(@"SEL$4E060E5B" >"SEL$35")
OUTLINE(@"SEL$32")
OUTLINE(@"SEL$33")
OUTLINE(@"SEL$2FEECD4C")
ANSI_REARCH(@"SEL$20")
OUTLINE(@"SEL$19")
OUTLINE(@"SEL$16")
OUTLINE(@"SEL$15")
OUTLINE(@"SEL$31")
OUTLINE(@"SEL$D50F4D64")
MERGE(@"SEL$17" >"SEL$18")
OUTLINE(@"SEL$35")
OUTLINE(@"SEL$4E060E5B")
MERGE(@"SEL$13" >"SEL$14")
MERGE(@"SEL$72AEFE3E" >"SEL$14")
OUTLINE(@"SEL$20")
OUTLINE(@"SEL$18")
OUTLINE(@"SEL$17")
OUTLINE(@"SEL$14")
OUTLINE(@"SEL$13")
OUTLINE(@"SEL$72AEFE3E")
MERGE(@"SEL$11" >"SEL$12")
MERGE(@"SEL$B97648DD" >"SEL$12")
OUTLINE(@"SEL$12")
OUTLINE(@"SEL$11")
OUTLINE(@"SEL$B97648DD")
MERGE(@"SEL$096E5AED" >"SEL$10")
MERGE(@"SEL$9" >"SEL$10")
OUTLINE(@"SEL$10")
OUTLINE(@"SEL$096E5AED")
MERGE(@"SEL$15E987C1" >"SEL$8")
MERGE(@"SEL$7" >"SEL$8")
OUTLINE(@"SEL$9")
OUTLINE(@"SEL$8")
OUTLINE(@"SEL$15E987C1")
MERGE(@"SEL$5" >"SEL$6")
MERGE(@"SEL$7237DA6D" >"SEL$6")
OUTLINE(@"SEL$7")
OUTLINE(@"SEL$6")
OUTLINE(@"SEL$5")
OUTLINE(@"SEL$7237DA6D")
MERGE(@"SEL$3" >"SEL$4")
MERGE(@"SEL$58A6D7F6" >"SEL$4")
OUTLINE(@"SEL$4")
OUTLINE(@"SEL$3")
OUTLINE(@"SEL$58A6D7F6")
MERGE(@"SEL$1" >"SEL$2")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$1")
NO_ACCESS(@"SEL$7C70BD48" "I"@"SEL$2")
INDEX_RS_ASC(@"SEL$7C70BD48" "INSTR_MASTER"@"SEL$33" ("INSTR_MASTER"."INSTRUMENT_ID"))
INDEX_RS_ASC(@"SEL$7C70BD48" "F_INCOME"@"SEL$32" ("F_INCOME"."INSTRUMENT_ID"))
NO_ACCESS(@"SEL$7C70BD48" "R_SP"@"SEL$11")
NO_ACCESS(@"SEL$7C70BD48" "R_FITCH"@"SEL$9")
NO_ACCESS(@"SEL$7C70BD48" "P"@"SEL$1")
NO_ACCESS(@"SEL$7C70BD48" "L"@"SEL$13")
NO_ACCESS(@"SEL$7C70BD48" "R_MOODY"@"SEL$7")
LEADING(@"SEL$7C70BD48" "I"@"SEL$2" "INSTR_MASTER"@"SEL$33" "F_INCOME"@"SEL$32" "R_SP"@"SEL$11" "R_FITCH"@"SEL$9" "P"@"SEL$1"
"L"@"SEL$13" "R_MOODY"@"SEL$7")
USE_NL(@"SEL$7C70BD48" "INSTR_MASTER"@"SEL$33")
USE_NL(@"SEL$7C70BD48" "F_INCOME"@"SEL$32")
USE_HASH(@"SEL$7C70BD48" "R_SP"@"SEL$11")
USE_HASH(@"SEL$7C70BD48" "R_FITCH"@"SEL$9")
USE_HASH(@"SEL$7C70BD48" "P"@"SEL$1")
USE_NL(@"SEL$7C70BD48" "L"@"SEL$13")
USE_HASH(@"SEL$7C70BD48" "R_MOODY"@"SEL$7")
PX_JOIN_FILTER(@"SEL$7C70BD48" "R_SP"@"SEL$11")
PX_JOIN_FILTER(@"SEL$7C70BD48" "R_FITCH"@"SEL$9")
PX_JOIN_FILTER(@"SEL$7C70BD48" "R_MOODY"@"SEL$7")
FULL(@"SEL$E4433E7A" "T1"@"SEL$E4433E7A")
FULL(@"SEL$E4433E79" "T1"@"SEL$E4433E79")
FULL(@"SEL$E4433E78" "T1"@"SEL$E4433E78")
FULL(@"SEL$3E1B8095" "T1"@"SEL$3E1B8095")
NO_ACCESS(@"SEL$30" "PRICES"@"SEL$30")
USE_HASH_AGGREGATION(@"SEL$30")
NO_ACCESS(@"SEL$F84B98B6" "B"@"SEL$17")
INDEX_RS_ASC(@"SEL$F84B98B6" "LB"@"SEL$17" ("F_LIQ_BASE"."SNAP_DATE" "F_LIQ_BASE"."INSTRUMENT_ID"
"F_LIQ_BASE"."SNAP_ID"))
INDEX(@"SEL$F84B98B6" "U"@"SEL$18" ("F_LIQ_UNIVERSE"."SNAP_DATE" "F_LIQ_UNIVERSE"."INSTRUMENT_ID"
"F_LIQ_UNIVERSE"."SNAP_ID" "F_LIQ_UNIVERSE"."UNIVERSE_GROUP_ID"))
LEADING(@"SEL$F84B98B6" "B"@"SEL$17" "LB"@"SEL$17" "U"@"SEL$18")
USE_NL(@"SEL$F84B98B6" "LB"@"SEL$17")
USE_NL(@"SEL$F84B98B6" "U"@"SEL$18")
NLJ_BATCHING(@"SEL$F84B98B6" "U"@"SEL$18")
FULL(@"SEL$298E7974" "T1"@"SEL$298E7974")
FULL(@"SEL$3E1B8096" "T1"@"SEL$3E1B8096")
FULL(@"SEL$B21719C5" "Y"@"SEL$16")
FULL(@"SEL$B21719C5" "T"@"SEL$15")
FULL(@"SEL$B21719C5" "R"@"SEL$15")
LEADING(@"SEL$B21719C5" "Y"@"SEL$16" "T"@"SEL$15" "R"@"SEL$15")
USE_MERGE_CARTESIAN(@"SEL$B21719C5" "T"@"SEL$15")
USE_HASH(@"SEL$B21719C5" "R"@"SEL$15")
NO_ACCESS(@"SEL$45D930ED" "B"@"SEL$19")
FULL(@"SEL$45D930ED" "P"@"SEL$19")
INDEX_RS_ASC(@"SEL$45D930ED" "S"@"SEL$20" ("F_SENSITIVITY"."SENSITIVITY_ID"))
LEADING(@"SEL$45D930ED" "B"@"SEL$19" "P"@"SEL$19" "S"@"SEL$20")
USE_HASH(@"SEL$45D930ED" "P"@"SEL$19")
USE_NL(@"SEL$45D930ED" "S"@"SEL$20")
INDEX_RS_ASC(@"SEL$22" "E_BATCH_JOB"@"SEL$22" ("E_BATCH_JOB"."BATCH_NAME" "E_BATCH_JOB"."BATCH_DATE"
"E_BATCH_JOB"."BATCH_INSTANCE_NO"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$22" "E_BATCH_JOB"@"SEL$22")
PUSH_SUBQ(@"SEL$11594485")
PUSH_SUBQ(@"SEL$CBF63DBF")
NO_ACCESS(@"SEL$27" "ALL_BATCHES"@"SEL$27")
PUSH_SUBQ(@"SEL$28")
NO_ACCESS(@"SEL$25" "ALL_BATCHES"@"SEL$25")
PUSH_SUBQ(@"SEL$CEF22074")
FULL(@"SEL$96EC8427" "T1"@"SEL$96EC8427")
FULL(@"SEL$96EC8426" "T1"@"SEL$96EC8426")
NO_ACCESS(@"SEL$28" "ALL_BATCHES"@"SEL$28")
FULL(@"SEL$96EC8425" "T1"@"SEL$96EC8425")
FULL(@"SEL$298E7975" "T1"@"SEL$298E7975")
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
1 - :1 (DATE): 11/29/2021 00:00:00
2 - :2 (NUMBER): 7
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2
---------------------------------------------------------------------------
0 - SEL$25
- materialize
0 - SEL$27
- materialize
December 16, 2021 - 9:31 am UTC
OK, so the 19c plan isn't creating a bloom filter on lines 22/23. As:
The 12c plan uses this to filter which partitions it accesses from ff_inc_price
The optimizer estimates you're fetching over 20 billion rows from ff_inc_price
This is likely why you're so much slower in 19c.
I'm not sure why this is - it might relate to the CTEs now being CURSOR DURATION MEMORY tables (added in 12.2), but that's a guess.
That said, I think the query could benefit from some restructuring. The batches subquery unions to queries. But from what I can tell, the rest of the query only uses values from one union branch or the other.
It may help to split these into two separate CTEs instead of unioning them.