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
Wolfgang Breitling Centrex Consulting Corporation www.centrexcc.com -- http://www.freelists.org/webpage/oracle-l