Cost increases after a SQL statement adds partition filtering conditions.

After a SQL statement is used to add a partition filter condition, the cost increases by adding a filter condition to the query Partition Table in 10.2.0.1.0. The cost increases, because this is an oracle bug. Let's test it below:

SQL> select * from v $ version; BANNER Release Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-ProdPL/SQL Release 10.2.0.1.0-ProductionCORE 10.2.0.1.0 ProductionTNS for 32-bit Windows: version 10.2.0.1.0-ProductionNLSRTL Version 10.2.0.1.0-ProductionSQL> drop table test purge; the table has been deleted. SQL> create table test (cl varchar2 (8), r integer) 2 partition by list (cl) (3 partition big values ('Big '), 4 partition small values ('small'), 5 partition empty values (default) 6); the table has been created. SQL> insert/* + append */into test (cl, r) select case when level between 1 and 4 then 'small' else 'Big 'end, dbms_random.value) from dual connect by level <= 10000; 10000 rows have been created. SQL> commit; submitted completely. SQL> create index I _tr on test (r); the index has been created. SQL> exec dbms_stats.gather_table_stats (user, 'test', cascade => true); PL/SQL process completed successfully. SQL> select count (*) from test where cl = 'Big '; COUNT (*) ---------- 9996SQL> select count (*) from test where cl = 'small '; COUNT (*) ---------- 4 SQL> set autotrace traceonlySQL> select/* + index (test) */* from test where cl = 'small' and r = 549; execution Plan -------------------------------------------------------- Plan hash value: 397514736 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Pstart | Pstop | latency | 0 | select statement | 1 | 10 | 2359 (1) | 00:00:29 | * 1 | table access by global index rowid | TEST | 1 | 10 | 2359 (1) | 00:00:29 | 2 | 2 | * 2 | index range scan | I _TR | 2500 | 7 (0) | 00:00:01 | descripredicate Information (identified by operation id ): ------------------------------------------------- 1-filter ("CL" = 'small') 2-access ("R" = 549) statistics 1 recursive cballs 0 db block gets 4 consistent gets 0 physical reads 0 redo size 460 bytes sent via SQL * Net to client 385 bytes received via SQL * Net from client 2 SQL * net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processedSQL> select/* + index (test) */* from test where r = 549; Execution Plan -------------------------------------------------------- Plan hash value: 885212093 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Pstart | Pstop | latency | 0 | select statement | 2 | 14 | 3 (0) | 00:00:01 | 1 | table access by global index rowid | TEST | 2 | 14 | 3 (0) | 00:00:01 | ROWID | * 2 | index range scan | I _TR | 2 | 1 (0) | 00:00:01 | descripredicate Information (identified by operation id ): ------------------------------------------------ 2-access ("R" = 549) statistics 1 recursive cballs 0 db block gets 5 consistent gets 0 physical reads 0 redo size 511 bytes sent via SQL * Net to client 385 bytes encoded ed via SQL * Net from client 2 SQL * net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 2 rows processed

 

Solution: delete statistics for dynamic collection

SQL> exec dbms_stats.delete_table_stats (user, 'test'); PL/SQL process completed successfully. SQL> select/* + index (test) */* from test where cl = 'small' and r = 549; Execution Plan -------------------------------------------------------- Plan hash value: 397514736 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Pstart | Pstop | latency | 0 | select statement | 1 | 19 | 2 (0) | 00:00:01 | * 1 | table access by global index rowid | TEST | 1 | 19 | 2 (0) | 00:00:01 | 2 | 2 | * 2 | index range scan | I _TR | 2 | 1 (0) | 00:00:01 | descripredicate Information (identified by operation id ): ------------------------------------------------- 1-filter ("CL" = 'small') 2-access ("R" = 549) note ------dynamic sampling used for this statement statistical information defaults 0 recursive cballs 0 db block gets 4 consistent gets 0 physical reads 0 redo size 460 bytes sent via SQL * Net to client 385 bytes encoded ed via SQL * Net from client 2 SQL * Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed [Bug 8971829: wrong global index selectitions/cost on small partitions with filter on part key ____________________________________ Bug Attributes partition Type B-Defect Fixed in Product Version-Severity 2-Severe Loss of Service Product Version 10.2.0.4Status 11-Code bug (Response/Resolution) platform 212-ibm aix on POWER Systems (64-bit) created 29-Sep-2009 Platform Version 5.3 Updated 21--200-2009 Base Bug-Database Version 10.2.0.4 Affects Platforms Generic Product Source Oracle Related Products release Line Oracle Database Products Family Oracle DatabaseArea Oracle Database Product 5-Oracle Server-Enterprise EditionHdr: 8971829 10.2.0.4 RDBMS 10.2.0.4 qry optimizer PRODID-5 PORTID-212Abstract: wrong global index selectitions/cost on small partitions with filter on part key *** 09/29/09 am *** PROBLEM: -------- For small partitions, the computed selectitions and cost of global index access path are wrong-when a filter on partitioning key is present. this causes CBO to choose a full scan of an unsuitable index over a very selective range scan of the correct index which results in performance degradation. WORKAROUND: ----------- set optimizer_features_enable = '9. 2.0 'ordelete statistics on base table and use dynamic samplingRELATED BUGS: ------------- REPRODUCIBILITY: ---------------- reproduced in-house on 10.2.0.4 and 11.1.0.7