Home » RDBMS Server » Server Administration » schema stats (11.2.0.4)
schema stats [message #635171] Mon, 23 March 2015 10:34 Go to next message
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
Re: schema stats [message #635173 is a reply to message #635171] Mon, 23 March 2015 11:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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

Re: schema stats [message #635174 is a reply to message #635173] Mon, 23 March 2015 11:22 Go to previous messageGo to next message
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.
Re: schema stats [message #635178 is a reply to message #635174] Mon, 23 March 2015 11:58 Go to previous messageGo to next message
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
Re: schema stats [message #635179 is a reply to message #635178] Mon, 23 March 2015 12:23 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
SELECT MAX(LAST_ANALYZED) FROM DBA_TABLES;

post results from SQL above
Re: schema stats [message #635181 is a reply to message #635179] Mon, 23 March 2015 12:58 Go to previous messageGo to next message
varunvir
Messages: 389
Registered: November 2007
Senior Member
SELECT MAX(LAST_ANALYZED) FROM DBA_TABLES;

3/22/2015 11:56:07 PM

Thanks,

Varun
Re: schema stats [message #635182 is a reply to message #635181] Mon, 23 March 2015 13:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Which does not prove that all stats are valid.

Re: schema stats [message #635192 is a reply to message #635171] Tue, 24 March 2015 04:23 Go to previous messageGo to next message
John Watson
Messages: 8922
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 Go to previous messageGo to next message
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
Re: schema stats [message #635205 is a reply to message #635204] Tue, 24 March 2015 12:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
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.


Re: schema stats [message #635224 is a reply to message #635205] Tue, 24 March 2015 17:47 Go to previous messageGo to next message
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
Re: schema stats [message #635227 is a reply to message #635224] Wed, 25 March 2015 01:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So my answer was the following.

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


See script at http://www.orafaq.com/forum/mv/msg/122554/336831/#msg_336831

Re: schema stats [message #635325 is a reply to message #635227] Thu, 26 March 2015 14:13 Go to previous message
varunvir
Messages: 389
Registered: November 2007
Senior Member
Thanks a lot Michel.
Previous Topic: regarding deleting 80 million records
Next Topic: Setting environmental variable for SID failed
Goto Forum:
  


Current Time: Thu Mar 28 02:54:56 CDT 2024