OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » Analyze Table

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

Analyze Table [message #159549]

Mon, 20 February 2006 04:25

vikashbansal
Messages: 2
Registered: February 2006

Junior Member

Hi Gurus ,

I have analyzed two base tables in oracle schema by using the conventional SQL as

analyze table table_name compute statistics

After that when i try to fetch data from the views that are using the base tables , repond much slowly.

Ideally the fetch time should get decreased or remail same .But in mine case it is increased.

So please suggest me what could be the possible reasons for the same.

Report message to a moderator

Re: Analyze Table [message #159569 is a reply to message #159549]

Mon, 20 February 2006 06:03

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

Senior Member
Account Moderator

>>Ideally the fetch time should get decreased or remail same .But in mine case it is increased.
Not a hardrule. CBO will not magically increase the performance.
It will just identify the best plan according to the available statistics.
Post your explain plan output and you need to collect histograms.
Either use DBMS_STATS
or
atleast
analyze table tname compute statistics for all indexes for all indexed columns size 250
(refer sticky)

Regards

Report message to a moderator

Re: Analyze Table [message #159671 is a reply to message #159569]

Mon, 20 February 2006 20:40

vikashbansal
Messages: 2
Registered: February 2006

Junior Member

Hi Mahesh,

I am attaching the execution plan observed after generating the DBMS stats and after deleting the DBMS stats.

Thanks in advance
Vikash

Attachment: Stats.zip
(Size: 2.16KB, Downloaded 1195 times)

Report message to a moderator

Re: Analyze Table [message #159751 is a reply to message #159671]

Tue, 21 February 2006 04:17

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

Senior Member
Account Moderator

why would you delete the statistics? It will not help the query anyhow. how did you delete the statistics?
As said before,
CBO decides the plan based on statistics available.
It considers a lot of patterns in data and decides the plan.
Histogram is most important thing.

Report message to a moderator

Previous Topic:	fragmented tables and indexes
Next Topic:	Unique constraint versus unique index

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 17 23:11:22 CDT 2024