.
\nWhen you migrate, you should be prepared to face some execution plan changing. That’s not new. But here I’ll show you a case where you have several bad execution plans because lot of histograms are missing. The version is the same. The system is the same. You’ve migrated with DataPump importing all statistics. You have the same automatic job to gather statistics with all default options. You have repeated the migration several times on a system where you constantly reproduce the load. Have done a lot of regression tests. Everything was ok.<\/p>\n
Then you are ready for the go-live. You migrate production on Sunday. You check everything and you are ready to welcome new connections on Monday.<\/p>\n
But on Monday some queries are going bad. Unexpected nested loops – abnormal CPU usage. Unexpected full table scan – abnormal I\/O usage. What went wrong? I’ll reproduce here the case I encountered this morning during a post-migration performance tuning analysis.<\/p>\n
In order to show you a reproducible case, I’ll create the table:<\/p>\n
SQL> create table DEMO_SKEW as\n 2 select rownum id , case when mod(rownum,21)!=0 then 0 else 1+mod(rownum,10) end status\n 3 from (select * from dual connect by 1000>=level),(select * from dual connect by 1000>level);\n\nTable created.\n<\/code><\/pre>\nWith an index on that STATUS column which is quite skewed (most of status are 0 and a few other status):<\/p>\n
SQL> create index DEMO_SKEW_STATUS on DEMO_SKEW(STATUS);\n\nIndex created.\n<\/code><\/pre>\nI calculate statistics in order to have histograms. I could have used a size for the histograms but the method I use here is a clue to help you figure out the issue…<\/p>\n
SQL> exec dbms_stats.gather_table_stats('DEMO','DEMO_SKEW',method_opt=>'for all columns size skewonly');\n\nPL\/SQL procedure successfully completed.\n<\/code><\/pre>\nOk, so I have frequency histogram with 11 buckets:<\/p>\n
SQL> select num_buckets,histogram,last_analyzed from dba_tab_col_statistics where owner='DEMO' and table_name='DEMO_SKEW' and column_name='STATUS';\n\nNUM_BUCKETS HISTOGRAM LAST_ANA\n----------- --------------- --------\n 11 FREQUENCY 19:34:22\n<\/code><\/pre>\nStatistic gathering<\/h3>\n
Ok, I’m at the same state as on Sunday where everything is imported with success. Because you know that the physical organization of the tables have changed you run a dbms_stats.gather_database_statistics. You run it with the default options – exactly as the automatic job did it before, in previous production or in pre-production migration tests.<\/p>\n
So let’s do the same on our test table:<\/p>\n
SQL> exec dbms_stats.gather_table_stats('DEMO','DEMO_SKEW',method_opt=>'for all columns size auto');\n\nPL\/SQL procedure successfully completed.\n<\/code><\/pre>\nThen you go home as you want to be at the office when the users will start to connect. Just in case…<\/p>\n
Then production starts…<\/h3>\n
So here is the kind of queries that I’ve seen the morning just after a migration.<\/p>\n
SQL> alter session set statistics_level=all;\n\nSession altered.\n\nSQL> select count(*) from DEMO_SKEW where status in (1,2,3,4,5);\n\n COUNT(*)\n----------\n 23809\n\nSQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));\n\nPLAN_TABLE_OUTPUT\n-----------------------------------------------------------------------------\nSQL_ID cbr6yy2vwr6s0, child number 0\n-------------------------------------\nselect count(*) from DEMO_SKEW where status in (1,2,3,4,5)\n\nPlan hash value: 3093139370\n\n-----------------------------------------------------------------------------\n| Id | Operation | Name | Starts | E-Rows | A-Rows | Buffers |\n-----------------------------------------------------------------------------\n| 0 | SELECT STATEMENT | | 1 | | 1 | 1671 |\n| 1 | SORT AGGREGATE | | 1 | 1 | 1 | 1671 |\n|* 2 | TABLE ACCESS FULL| DEMO_SKEW | 1 | 454K| 23809 | 1671 |\n-----------------------------------------------------------------------------\n\nPredicate Information (identified by operation id):\n---------------------------------------------------\n\n 2 - filter((\"STATUS\"=1 OR \"STATUS\"=2 OR \"STATUS\"=3 OR \"STATUS\"=4 OR\n \"STATUS\"=5))\n<\/code><\/pre>\nI’m looking for those very few status and I don’t expect a full table scan. Each execution here is reading thousand of blocks. Look at the right plan when forcing an index access:<\/p>\n
SQL> select \/*+ index(DEMO_SKEW) *\/ count(*) from DEMO_SKEW where status in (1,2,3,4,5);\n\n COUNT(*)\n----------\n 23809\n\nSQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));\n\nPLAN_TABLE_OUTPUT\n------------------------------------------------------------------------------------\nSQL_ID 9nwws35r3vj57, child number 0\n-------------------------------------\nselect \/*+ index(DEMO_SKEW) *\/ count(*) from DEMO_SKEW where status in\n(1,2,3,4,5)\n\nPlan hash value: 3152074358\n\n------------------------------------------------------------------------------------\n| Id | Operation | Name | Starts | E-Rows | A-Rows | Buffers |\n------------------------------------------------------------------------------------\n| 0 | SELECT STATEMENT | | 1 | | 1 | 58 |\n| 1 | SORT AGGREGATE | | 1 | 1 | 1 | 58 |\n| 2 | INLIST ITERATOR | | 1 | | 23809 | 58 |\n|* 3 | INDEX RANGE SCAN| DEMO_SKEW_STATUS | 5 | 454K| 23809 | 58 |\n------------------------------------------------------------------------------------\n\nPredicate Information (identified by operation id):\n---------------------------------------------------\n\n 3 - access((\"STATUS\"=1 OR \"STATUS\"=2 OR \"STATUS\"=3 OR \"STATUS\"=4 OR \"STATUS\"=5))\n<\/code><\/pre>\nThis is the right plan for those low cardinality status, reading much less blocks. Why did the CBO have chosen a full table scan? Because of cardinality estimation. It estimates to read 454000 rows but actual number of rows is 23000 here.<\/p>\n
Bad estimation comes from missing stats<\/h3>\nSQL> select num_buckets,histogram,last_analyzed from dba_tab_col_statistics where owner='DEMO' and table_name='DEMO_SKEW' and column_name='STATUS';\n\nNUM_BUCKETS HISTOGRAM LAST_ANA\n----------- --------------- --------\n 1 NONE 19:34:23\n<\/code><\/pre>\nMy histograms have disappeared. The CBO estimates as if all status were well distributed among the 10 status:<\/p>\n
SQL> select endpoint_number,endpoint_value from dba_tab_histograms where owner='DEMO' and table_name='DEMO_SKEW' and column_name='STATUS';\n\nENDPOINT_NUMBER ENDPOINT_VALUE\n--------------- --------------\n 0 0\n 1 10\n<\/code><\/pre>\nSo what was my solution? Gather the statistics:<\/p>\n
SQL> exec dbms_stats.gather_table_stats('DEMO','DEMO_SKEW',method_opt=>'for all columns size auto');\n\nPL\/SQL procedure successfully completed.\n<\/code><\/pre>\nAnd now I have my histograms:<\/p>\n
SQL> select num_buckets,histogram,last_analyzed from dba_tab_col_statistics where owner='DEMO' and table_name='DEMO_SKEW' and column_name='STATUS';\n\nNUM_BUCKETS HISTOGRAM LAST_ANA\n----------- --------------- --------\n 11 FREQUENCY 19:34:24\n\nSQL> select endpoint_number,endpoint_value from dba_tab_histograms where owner='DEMO' and table_name='DEMO_SKEW' and column_name='STATUS';\n\nENDPOINT_NUMBER ENDPOINT_VALUE\n--------------- --------------\n 957142 1\n 961904 2\n 966666 3\n 971428 4\n 976190 5\n 980952 6\n 985714 7\n 990476 8\n 995238 9\n 1000000 10\n 952381 0\n<\/code><\/pre>\nDid you realize that I’ve gathered statistics with exactly the same options as before? When I’ve run it just after the migration, my histograms have disappeared. Now running exactly the same they are back. Explanation later…<\/p>\n
Invalidate cursors<\/h3>\n
The statistics gathering was run with the default option – rolling invalidation. Which means that my bad execution plans – with full table scan – are still used. Because I don’t want too many hard parses at the same time, I invalidate only the plans that I’ve identified as causing a problem to users:<\/p>\n
SQL> exec for c in (select address,hash_value,users_executing,sql_text from v$sqlarea where sql_id='cbr6yy2vwr6s0') loop sys.dbms_shared_pool.purge(c.address||','||c.hash_value,'...'); end loop;\n\nPL\/SQL procedure successfully completed.\n<\/code><\/pre>\n