Docstoc

_optimizer_cost_based_transformation2

Document Sample
_optimizer_cost_based_transformation2 Powered By Docstoc
					8/8/12                                                                 Document


          Oracle 10G Plan is Worse than 9i for Same Query [ID 313746.1]
         Modified: Jan 10, 2006       Type: PROBLEM           Status: MODERATED            Priority: 3


         In this Document
          Symptoms
          Cause
          Solution
          References




          This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process, and therefore has not
          been subject to an independent technical review.


         Applies to:
         Oracle Server - Enterprise Edition - Version: 10.1.0.3.0
         This problem can occur on any platform.

         Symptoms

         Oracle 10G query is slow compared to Oracle 9i.

         Here is behaviour:

            create table t1 as select * from all_objects;
            create unique index t1_i on t1(object_id);
            create table t2 as select * from all_objects;
            create index t2_i on t2(object_id);
            update t1 set status = 0;
            update t2 set status = 0;
            create or replace type table_of_number as table of number;
            /
            create or replace function getnumericlist(card_n number) return table_of_number
            as
            l_numeric_list table_of_number;
            begin
            select object_id
            bulk collect into l_numeric_list
            from (select distinct object_id from t1)
            where rownum <= card_n;
            return l_numeric_list;
            end getnumericlist;
            /
            begin
            dbms_stats.delete_table_stats(null, 't1');
            dbms_stats.gather_table_stats(null, 't1',
            estimate_percent => 100,
            method_opt => 'for all columns size 254',
            cascade => true);
            dbms_stats.delete_table_stats(null, 't2');
            dbms_stats.gather_table_stats(null, 't2',
            estimate_percent => 100,
            method_opt => 'for all columns size 254',
            cascade => true);
https://support.oracle.com/epmos/faces/ui/km/DocContentDisplay.jspx?_adf.ctrl-state=y68oz1yd5_306                              1/3
8/8/12                                                                 Document

           end;
           /
           Test query:
           select count(*) from (
           select t1.object_name,t1.object_type, t2.object_name
           from t1, t2
           where t1.object_id in (select /*+ cardinality(nlist 10000) */ *
           from table(cast(getnumericlist(10000) as
           table_of_number)) nlist where rownum > 0)
           and t2.object_id = t1.object_id
           and t1.status = 0
           and t2.status = 0);


           In 10g:


             COUNT(*)
           ----------
               10000

            Execution Plan
            ----------------------------------------------------------
               0     SELECT STATEMENT Optimizer=ALL_ROWS (Cost=879155 Card=1 Bytes=14)
               1 0 SORT (AGGREGATE)
               2 1      FILTER
               3 2        HASH JOIN (Cost=421 Card=47289 Bytes=662046)
               4 3          TABLE ACCESS (FULL) OF 'T2' (TABLE) (Cost=147 Card=47290 Bytes=331030)
               5 3          TABLE ACCESS (FULL) OF 'T1' (TABLE) (Cost=147 Card=47289 Bytes=331023)
               6 2        FILTER
               7 6          COUNT
               8 7           FILTER
               9 8             COLLECTION ITERATOR (PICKLER FETCH) OF 'GETNUMERICLIST'
         In 9i:

             COUNT(*)
           ----------
               10000

           Execution Plan
           ----------------------------------------------------------
              0     SELECT STATEMENT Optimizer=CHOOSE (Cost=185 Card=1 Bytes=27)
              1 0 SORT (AGGREGATE)
              2 1      HASH JOIN (Cost=185 Card=10000 Bytes=270000)
              3 2        HASH JOIN (Cost=106 Card=10000 Bytes=200000)
              4 3          VIEW OF 'VW_NSO_1' (Cost=31 Card=10000 Bytes=130000)
              5 4           SORT (UNIQUE) (Cost=31 Card=10000 Bytes=20000)
              6 5             COUNT
              7 6              FILTER
              8 7                COLLECTION ITERATOR (PICKLER FETCH) OF 'GETNUMERICLIST'
              9 3          TABLE ACCESS (FULL) OF 'T1' (Cost=59 Card=48880 Bytes=342160)
             10 2         TABLE ACCESS (FULL) OF 'T2' (Cost=59 Card=48888 Bytes=342216)

         Cause

         The changes in plan is due to differences in subquery unnesting.

         This is reported as Internal BUG:3621111 10G PLAN WORSE THAN 9I FOR SAME QUERY (CM - PERRPEO1).
https://support.oracle.com/epmos/faces/ui/km/DocContentDisplay.jspx?_adf.ctrl-state=y68oz1yd5_306          2/3
8/8/12                                                                 Document


         Solution

         Set below paramer in oracle 10G.

         alter session set "_optimizer_cost_based_transformation" = OFF;

         References




https://support.oracle.com/epmos/faces/ui/km/DocContentDisplay.jspx?_adf.ctrl-state=y68oz1yd5_306   3/3

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:16
posted:8/8/2012
language:Unknown
pages:3