Re: why does a higher cost run faster?

  • From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
  • To: haroon@xxxxxxxxxxxx
  • Date: Thu, 15 Dec 2005 15:12:35 -0700

Regarding the sys stats, you are mistaking system statistics (dbms_stats.gather_system_stats) with object statistics on sys objects (dbms_stats.gather_schema_stats('SYS',...). Two completely different things.

The organization_id doesn't appear to be skewed enough to explain the difference. I'd be curious about a few things:
a) the table, index, and column statistics of the table.
b) the actual row counts of the execution plan (from v$sql_plan_statistics or tkprof from a sql_trace).
c) How you gathered the statistics. Since this is an Oracle apps I presume you (need to) use the fnd. package. Doe it select its own sampling rate, or can you set/override that?

Haroon A. Qureshi wrote:

i removed the function call and replaced it with the returned
value.  the plan is the same (full table scan) with the same

regarding the sys stats, we are running oracle 11i. the rule
of thumb has been not to gather stats on the sys/system
objects (along with it depends :) ). going with the it
depends scenario, we've seen performance to be better without
the stats on the system objects.

the organization_id is a primary key in the table.  the data
is broken out below.  the function call returns org_id 86.

--------------- ----------
             86     172394
            101     150699
            102      31681

-- Regards

Wolfgang Breitling
Centrex Consulting Corporation

Other related posts: