The importance of table statistics was driven home to me today. I've always known this, but sometimes you forget how important it is to keep fresh statistics on your objects. We are using an optimization mode of choose which resolves to the cost based optimizer. There was a large join operation that usually takes 2-3 hours which was finishing in 8 hours. So, we took a few minutes trying to figure out why the increase in runtime. The problem with analyzing this change was with the current load on the server. We were hammering the box. Our first thought was the server was overloaded which caused the job to run longer than normal.
Then I had an epiphany from God. It could be stale table statistics for both or one of the tables in the join condition. So this is what I did:
ANALYZE TABLE OWNER.TABLENAME DELETE STATISTICS;
BEGIN
DBMS_STATS.GATHER_TABLE_STATS ('OWNER', 'TABLENAME', NULL, 0.5);
END;
/
I did this for both tables in the join. Now, the runtime for the job is back to 2 hours. Now, you more experienced DBAs will comment about using DBMS_STATS to automate statistics gathering. However, we always assumed our processing cycle and the size of our databases ruled out the automatic statistics gathering option.
Yes, I do need to visit the automatic statistics gathering options available in 10g. It has been quite a few years since I looked at gathering object statistics. Expect this to be another post later on ;-)
No comments:
Post a Comment