Re: performance issue after upgrade to oracle 220.127.116.11 linux 32 bit.
Date: Sun, 8 Nov 2009 08:22:06 -0800 (PST)
On Nov 8, 2:36 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> > I use below command to gather statistics.
> > exec dbms_stats.gather_schema_stats
> > ('TEST',estimate_percent=>100,cascade=> TRUE);
> OK, the above collects table and index statistics, but I am not sure
> if that will collect the statistics on the hidden columns (Randolf or
> Jonathan should be able to answer this question).
Charles, it depends on the configuration of DBMS_STATS via SET_PARAM resp. SET_*_PREFS in 11g, but the unmodified default settings will gather statistics on hidden columns.
> > Below are histogram of SYS_NC00017$
> What is the purpose of this index? This is the same index that I
> mentioned previously that showed a strange IO cost in the 10053 trace
Obviously this is a virtual column belonging to the function-based index SETDETAILS_SETID_IX on SETDETAILS which covers at least serv_prov_code and upper(sd.set_id).
I think the strange output in the 10053 trace is a known oddity introduced with 11.1 for virtual columns. It doesn't seem to harm the actual calculation however.
To the OP: How many distinct values of "upper(sd.set_id)" are there?
Oracle related stuff blog: