Re: performance issue after upgrade to oracle 11.2.0.1 linux 32 bit.
Date: Sun, 8 Nov 2009 08:22:06 -0800 (PST)
Message-ID: <0b4c03c0-231b-43d4-b0f4-4968240bb2e0_at_v30g2000yqm.googlegroups.com>
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
> 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?
Regards,
Randolf
Oracle related stuff blog:
http://oracle-randolf.blogspot.com/
SQLTools++ for Oracle (Open source Oracle GUI for Windows):
http://www.sqltools-plusplus.org:7676/
http://sourceforge.net/projects/sqlt-pp/
Received on Sun Nov 08 2009 - 10:22:06 CST