Restore Table Stats

Posted: March 27, 2014 in Database Administration
Tags:

Greetings, Oracle Enthusiasts!

I promised in a previous post I would re-visit ‘How  Incorrect Stats Can Ruin Your Queries.’

Today, I ran into another situation that baffled me. Over the past couple of months, our very own Tim Gorman (@timothyjgorman) an I have been tuning an incremental process that used to complete in several hours. After adding indexes and refining the process, we cut the time down to about forty minutes.

Two weeks ago, out of nowhere, the job got stuck again and looked as though it would never finish. I killed the process, gathered stats on all of the tables and kicked it off again. Once again, the job completed in forty minutes. Last week, it got stuck again… This time, I added a much needed index, gathered stats, and it finished in… wait for it… forty minutes. This morning, at about 5:17 am, the job kicked off again. It ran for six hours before I gave up on it. I killed the session, identified another opportunity for an index and asked Tim for help. You’ve all heard of AskTom. I decided to Ask Tim. He is consulting for our company and is our resident expert. Here’s what he came up with… It is not at all what I expected…

 

The  jobs running since 5:17am this morning encountered a problem with table- and index-statistics, causing them to be given a bad execution plan.

 

First, we looked at the LAST_ANALYZED date for all of the tables (and associated indexes) involved in these queries…


SQL> select ‘TABLE’ type, owner, table_name name, ” index_name, ” partition_name, last_analyzed from dba_tables where (owner, table_name) in
2 (insert table_names here)
4 union all
5 select ‘TABLE PARTITION’ type, table_owner owner, ” index_name, table_name name, partition_name, last_analyzed from dba_tab_partitions where (table_owner, table_name) in
6 (insert table_owner,table_name here)
8 union all
9 select ‘INDEX’ type, owner, table_name name, index_name, ” partition_name, last_analyzed from dba_indexes where (table_owner, table_name) in
10 (insert table_owner, table_name here),
12 union all
13 select ‘INDEX PARTITION’ type, i.owner owner, i.table_name name, i.index_name, p.partition_name, p.last_analyzed
14 from dba_indexes i, dba_ind_partitions p where (i.table_owner, i.table_name) in
15 (you know the drill), ()
17 and p.index_owner = i.owner
18 and p.index_name = i.index_name
19 order by 6;

63 rows selected.

Please note that the table (and its associated indexes) were last analyzed at 5:54am, which is 37 minutes after the  jobs were started. Looking at the history of stats-gathering on the table, we saw that statistics were gathered four times today, twice in quick succession at 12:36am, once more at 2:00am, and again at 5:54am…

 

SQL> select STATS_UPDATE_TIME from dba_tab_stats_history
2 where owner = ‘SCHEMA’ and table_name = ‘TABLE’
3 order by STATS_UPDATE_TIME;

 

STATS_UPDATE_TIME
—————————————————————————
12-MAR-14 05.55.35.505710 AM -06:00
13-MAR-14 03.54.53.893261 PM -06:00
14-MAR-14 02.01.43.853721 AM -06:00
14-MAR-14 05.54.47.852192 AM -06:00
14-MAR-14 03.13.29.045087 PM -06:00
16-MAR-14 02.03.25.892466 AM -06:00
17-MAR-14 02.01.10.817021 AM -06:00
18-MAR-14 06.20.34.390329 AM -06:00
19-MAR-14 07.07.44.875815 AM -06:00
20-MAR-14 06.13.25.121303 PM -06:00
21-MAR-14 02.07.02.475937 AM -06:00
21-MAR-14 05.54.58.787292 AM -06:00
21-MAR-14 09.29.49.777423 AM -06:00
23-MAR-14 02.07.23.989891 AM -06:00
24-MAR-14 02.23.28.230201 AM -06:00
25-MAR-14 05.11.42.458275 AM -06:00
26-MAR-14 02.00.58.643726 AM -06:00
26-MAR-14 05.54.41.072695 AM -06:00
26-MAR-14 12.36.54.849780 PM -06:00
26-MAR-14 12.36.56.852260 PM -06:00
26-MAR-14 12.40.01.690545 PM -06:00
26-MAR-14 12.40.02.556184 PM -06:00

22 rows selected.

 

We wanted to see what statistics were “active” at the time the jobs were started, so we used the RESTORE_TABLE_STATS procedure in the DBMS_STATS package to briefly restore those statistics so we could look at them…

SQL> exec dbms_stats.restore_table_stats(‘SCHEMA’,’TABLE’,to_timestamp(sysdate-(8/24)),no_invalidate=>TRUE)

PL/SQL procedure successfully completed.

SQL> select owner, table_name, num_rows, blocks, last_analyzed from dba_tab_statistics
2 where owner = ‘SCHEMA’ and table_name = ‘TABLE’;

 

The results showed that the tables (and its associated indexes were empty when the stats were gathered at 2am.

As a result, the execution plan created at 5:17 reflected a plan based on the presumption that the table was empty…

When the optimizer joined all of the other tables to the empty table, most of the other tables (as well as the final output from the query) were estimated to have 1 row also (which really means 0 rows), since the join to the empty table would have ensured that no rows were retrieved from those as well.

Please bear in mind that the Oracle optimizer is a math engine, and the presence of zeroes in its calculations can cause trouble (i.e. divide by zero, etc), so that is why cardinality estimates of “0” rows are replaced by values of “1”.

After the jobs were started, it seems that table- and index-statistics were gathered again, at 5:54am…

SQL> exec dbms_stats.restore_table_stats(”,”,to_timestamp(sysdate-(1/24)),no_invalidate=>TRUE)

PL/SQL procedure successfully completed.

SQL> select owner, table_name, num_rows, blocks, last_analyzed from dba_tab_statistics where owner = ‘OWNER’ and table_name = ‘TABLE’;

…and those statistics showed the table as having 4.2m rows, which is correct at the present time. Thus, re-running the jobs after 5:54am would have gotten a far better execution plan…

This plan estimates to complete in about 10 minutes. In reality, it finished in about 25 minutes! And that is without the other index that I recommended.

Stale stats, bad stats, and incorrect cardinality are three great threats to Database queries and processes. Make sure you are aware and take preventative measures.

**I must give proper credit to Tim Gorman for all of his research and analysis on this topic** Find him on Twitter @timothyjgorman

 

 

Advertisements
Comments
  1. One interesting point that Tim brought out on his Twitter comment…
    Using “dbms_stats.restore_table_stats” is akin to “flashback_query”.
    Very cool!!!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s