Re: performance issue after upgrade to oracle linux 32 bit.

From: Randolf Geist <>
Date: Sun, 8 Nov 2009 08:22:06 -0800 (PST)
Message-ID: <>

On Nov 8, 2:36 pm, Charles Hooper <> 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
> file:

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:

SQLTools++ for Oracle (Open source Oracle GUI for Windows): Received on Sun Nov 08 2009 - 10:22:06 CST

Original text of this message