[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Will statistics affect update also?

Posted on 2012-09-19
Medium Priority
Last Modified: 2012-09-21
I am doing an update on a table, but that is having very old statistics,

Whether this will affect the performance?
Question by:sakthikumar

Expert Comment

ID: 38414016
Statistics are considered stale when the data has changed by 20%.  At this time, by default, the auto update statistics should take care of it.  You could also manually rebuild the statistics by using the sp_updatestats stored procedure.
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38414045
>>using the sp_updatestats stored procedure.

That looks like a SQL Server procedure.  This was posted in an Oracle zone.

As far as the question:

What are the concerns with the update and statistics with performance?  The update statement itself of future selects/DML against  the table after the update?

Expert Comment

by:Greg Clough
ID: 38414536
Just for clarification, I presume you're asking:

"Will having old statistics on a table affect the speed of an update performed on that table?"

To which my answer is Yes, because if you've got a "WHERE" clause then the optimizer will use the statistics on the table and the indexes available to find the best query plan to return those rows that need updating. (It also uses "System Statistics", but that's another topic)  If it used to be a small table  of say 1000 rows, and now it's got 1 Billion rows but the stats are the same then Oracle will likely choose the wrong path and it will run horribly.

You can confirm this with an explain plan.
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38414560
>>To which my answer is Yes

Shouldn't the answer be "It might" instead of "Yes"?

Old statistics does not necessarily mean 'bad' statistics.  It depends of how much the table has changed since the stats were last generated and if those changes would affect the execution plan.

Expert Comment

by:Greg Clough
ID: 38414650
>> Old statistics does not necessarily mean 'bad' statistics.

Yes, I agree. Thanks for the clarification.  I guess I tried to explain that later in the comment, maybe with too much babble. :-)

Author Comment

ID: 38416632
(Oracle 10g)

The table was having 3 million rows with no triggers on it.
It was having statistics collected by 2009.

I ran the update stmt to update all rows without any where clause.
It took more than 30 minutes.

After this, I collected statistics,
ran the update again. that time it took only 3 mins.

Is collection of statistics making the difference?

Accepted Solution

Greg Clough earned 2000 total points
ID: 38418367
More than likely the difference is that the first time you ran the update it loaded all of the rows into the cache, so you were doing much less I/O the second time.  If you're doing an update on all rows in the table then statistics won't matter, as it's always going to choose a full table scan.

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question