OraFAQ Forum: Performance Tuning » Use of Histogram and Statistics for All Index columns or All columns?

Home » RDBMS Server » Performance Tuning » Use of Histogram and Statistics for All Index columns or All columns?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Use of Histogram and Statistics for All Index columns or All columns? [message #167202]

Tue, 11 April 2006 18:02

SMISHRA
Messages: 5
Registered: April 2006
Location: Canada

Junior Member

Hi All,

I am new doing this App Tuning task and, we are in preliminary stag at this time.

We are currently running the Oracle 10g Release1 in CBO (Cost Base Optimizer) Mode, and DBA have gathered the statistics for tables, columns and indexes.
We have generated the statistics for all columns using 'FOR ALL COLUMNS SIZE AUTO'.

I have noticed we have issues with query involving the order by Clause i.e. ORDER BY LAST_NAME,FIRST_NAME,LOCATION.

I was wondering what If I gathered the statistics on 'All Index Columns' only as oppose to having 'FOR ALL COLUMNS SIZE AUTO'.

Please help me understand, which option is better as far as stat generation is concerned as well as use/benefit/disadvantage of histogram.

Please provide me any doc around the HISTOGRAM and best practise of gathering Statistics.

Thanks in Advance. This is my first posting on this site and I hope to get some good information.

Report message to a moderator

Re: Use of Histogram and Statistics for All Index columns or All columns? [message #167203 is a reply to message #167202]

Tue, 11 April 2006 19:15

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

First start here
http://www.orafaq.com/forum/t/51267/42800/
>>Please provide me any doc
Refer Oracle Performance tuning guide.
Please search the forum with keyword 'histograms'. Several different views are already discussed
which may be useful for you.

First to answer your question,
>>'FOR ALL COLUMNS SIZE AUTO'
This depends on STATISTICS_LEVEL. May not be very useful always. A proper bucket size is always important.
Start with size 250 as said in the sticky.
Measure and compare. May down the road, you can make use of dynamic sampling.
>>'All Index Columns'
To be in the safer side, try this.

SQL> EXEC dbms_stats.gather_table_stats('OWNER','TABLE',METHOD_OPT=>'FOR ALL INDEXED COLUMNS SIZE 250',CASCADE=>TRUE);

The CBO may or may not use the collected histogram. If the data is skewed, presence of histogram is very helpful.

Report message to a moderator

Re: Use of Histogram and Statistics for All Index columns or All columns? [message #167410 is a reply to message #167203]

Wed, 12 April 2006 20:03

SMISHRA
Messages: 5
Registered: April 2006
Location: Canada

Junior Member

Hi Mahesh,

Thanks for your reply. I'll go thru some documentation around the histogram. I think is good starting point.
I am not sure about the approach toward tuning yet but will I would like to start on the right foot.

FYI, we have gathered the stat on schema level for all index columns not on the table level. Previously, It was 'all columns size auto' What I noticed today is that some of my query run faster from SQL*PLUS but the same from UI (User Interface (Siebel) firing the same query is slower...).

I am still trying to understand the difference between the various method of stat gathering and their benefits.

Once again Thanks for your reply.

- SM

Report message to a moderator

Re: Use of Histogram and Statistics for All Index columns or All columns? [message #167531 is a reply to message #167410]

Thu, 13 April 2006 10:17

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

>>same from UI (User Interface (Siebel) firing the same query is slower...).
Enable tracing and see what is different.
I have no idea about SIEBEL. May be it is forcing RBO somewhere.
Did you compare the plans?

Report message to a moderator

Re: Use of Histogram and Statistics for All Index columns or All columns? [message #167548 is a reply to message #167531]

Thu, 13 April 2006 15:39

SMISHRA
Messages: 5
Registered: April 2006
Location: Canada

Junior Member

Hi Mahesh,

One more thing, I like to ask. We have gathered the stats using the below procedure.

begin
dbms_stats.gather_schema_stats(
ownname => 'CONV',
method_opt => 'FOR ALL INDEXED COLUMNS',
granularity => 'ALL',
cascade => TRUE,
degree => DBMS_STATS.DEFAULT_DEGREE);
end;
/

Please let me if you see any issue in gathered stats.

Thanks for your time.

Report message to a moderator

Re: Use of Histogram and Statistics for All Index columns or All columns? [message #167552 is a reply to message #167548]

Thu, 13 April 2006 16:39

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Without understanding your data, we cannot comment. It may be good or bad or just useless. The bucketsize is very important.

Report message to a moderator

Previous Topic:	partitioned table indexes, rebuild
Next Topic:	Tool

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 23 15:47:27 CST 2024