Re: why does a higher cost run faster?

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
> performance.
>
> 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.
>
> ORGANIZATION_ID COUNT(*)
> --------------- ----------
> 86 172394
> 101 150699
> 102 31681
>

-- 
Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com
--
http://www.freelists.org/webpage/oracle-l