OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » schema stats (11.2.0.4)

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

schema stats [message #635171]

Mon, 23 March 2015 10:34

varunvir
Messages: 389
Registered: November 2007

Senior Member

Experts,

Stats on some of the Tables in one of the production schema is gathered on dec 12 and four of the tables are never gathered stats.
There are fair of amount of tables with schema stats up to date.
We don't have any major performance issues except on some queries that involvles those four tables whose stats have never been gathered.
I was going to gather stats on those tables after testing in UAT.
But i needed your suggestion on this:-

Since overall performance is good but still Is it a good idea to run schema stats as many of the tables are not up to date stats?

Thanks,

Varun

Report message to a moderator

Re: schema stats [message #635173 is a reply to message #635171]

Mon, 23 March 2015 11:15

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

There is no general answer to your question.
As always, the answer is "it depends".

Report message to a moderator

Re: schema stats [message #635174 is a reply to message #635173]

Mon, 23 March 2015 11:22

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

If the data changes very little or not at all then "old" statistics are still valid & collecting the same (or similar) values accomplishes nothing.

Report message to a moderator

Re: schema stats [message #635178 is a reply to message #635174]

Mon, 23 March 2015 11:58

varunvir
Messages: 389
Registered: November 2007

Senior Member

Thanks Blackswan.

Is there any way to see if the old stats on tables are still valid or not?

Thanks,

Varun

Report message to a moderator

Re: schema stats [message #635179 is a reply to message #635178]

Mon, 23 March 2015 12:23

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

SELECT MAX(LAST_ANALYZED) FROM DBA_TABLES;

post results from SQL above

Report message to a moderator

Re: schema stats [message #635181 is a reply to message #635179]

Mon, 23 March 2015 12:58

varunvir
Messages: 389
Registered: November 2007

Senior Member

SELECT MAX(LAST_ANALYZED) FROM DBA_TABLES;

3/22/2015 11:56:07 PM

Thanks,

Varun

Report message to a moderator

Re: schema stats [message #635182 is a reply to message #635181]

Mon, 23 March 2015 13:31

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Which does not prove that all stats are valid.

Report message to a moderator

Re: schema stats [message #635192 is a reply to message #635171]

Tue, 24 March 2015 04:23

John Watson
Messages: 8966
Registered: January 2010
Location: Global Village

Senior Member

Quote:

We don't have any major performance issues except on some queries that involvles those four tables whose stats have never been gathered.

Gather stats on those tables, with histograms. If you don't like the results, delete them. Furthermore, ask your DBA why he disabled the automatic stats gathering job. And ask when he last gathered system stats.

--update: to be precise, I should have said "gather stats auto task" rather than "automatic stats gathering job".

[Updated on: Tue, 24 March 2015 04:25]

Report message to a moderator

Re: schema stats [message #635204 is a reply to message #635192]

Tue, 24 March 2015 12:29

varunvir
Messages: 389
Registered: November 2007

Senior Member

Thanks John,
I imported the database 3 months ago from another server.
I will enable the job gather stats auto task now.

Thanks,

Varun

Report message to a moderator

Re: schema stats [message #635205 is a reply to message #635204]

Tue, 24 March 2015 12:45

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

OK, now I know I have not to answer you as you choose to ignore my post.
I will immediately remove my previous one which gave a direct answer to your question.

Report message to a moderator

Re: schema stats [message #635224 is a reply to message #635205]

Tue, 24 March 2015 17:47

varunvir
Messages: 389
Registered: November 2007

Senior Member

Hey Dear Michel,

Please don't say that. Some how either I did not see your reply or I just could not perceive it somehow.
You have been such a great help to me since I started working on and off Oracle DBA job.
I consider you as my teacher and I honor you always.

Thanks so much for your help for all those years.

-Varun

Report message to a moderator