Moving Optimizer Statistics for all Objects Involved in an Execution Plan (between databases)

Have you ever been asked to troubleshoot a SQL performance problem and found that execution plans are different in production and development environments for the same SQL? I just did. There could be a lot of different reasons why the execution plans differs. The most typical would be difference in init.ora parameters and Oracle Optimizer statistics. In this blog post, I would like to share with you how I have transferred optimizer statistics and, as a result, managed to get exactly the same execution plans in both production and development environments. My goal was to transfer statistics for objects that are involved in the SQL execution, only leaving other objects’ statistics in the development environment untouched. I am not going to go through all the details in this blog post as I am limited in time today. You can find all the steps I have used in my github.com script repository.

The main piece of code I am kind of proud of at the moment and would like to share with you extracts information about all objects involved in the SQL’s execution plan from plan_table and exports objects’ optimizer statistics in a separate table.
Here’s how you do it:

Explain plan for SQL

EXPLAIN PLAN
SET STATEMENT_ID = 'SQL_BAD_PLAN_01'
INTO apps.plan_table
FOR SELECT
....
12:30:50 [email protected]:1>

Explained.

12:32:20 [email protected]:1> select count(*) from apps.plan_table where STATEMENT_ID = 'SQL_BAD_PLAN_01';

       COUNT(*)
---------------
             87

1 row selected.

12:32:38 [email protected]:1>

Create a table to store statistics

begin sys.dbms_stats.create_stat_table(ownname=>'APPS',stattab=>'phy_pln_obj_01',tblspace=>'perfstat'); end;
/

Export relative objects statistics

declare
	v_ownname varchar2(30):='APPS';
	v_stattab varchar2(30):='phy_pln_obj_01';
	cursor c_all_pln_obj is
--#C#B#########################################
with all_pln_obj as (select
	pt.object_owner,
	pt.object_name,
	pt.object_type
from
	apps.plan_table pt
where 1=1
	and pt.STATEMENT_ID = 'SQL_BAD_PLAN_01'
	and pt.object_name is not null
	and pt.object_type!='VIEW')
--==============================================
select distinct owner, table_name from
(
-- All tables used in the SQL plan
select object_owner owner, object_name table_name from all_pln_obj
where object_type='TABLE'
union all
-- All tables used in the SQL plan via indexes
select table_owner, table_name from dba_indexes di, all_pln_obj ao
where 1=1
	and di.owner=ao.object_owner
	and di.index_name=ao.object_name
	and ao.object_type like '%INDEX%'
) order by owner, table_name;
--#C#E#########################################
begin
  for r_stat in c_all_pln_obj
  loop
    begin
      dbms_output.put_line('exec dbms_stats.delete_table_stats(ownname=>'''||r_stat.owner||''',tabname=>'''||r_stat.table_name||''',CASCADE_PARTS=>TRUE, CASCADE_COLUMNS=> TRUE, CASCADE_INDEXES=> TRUE);

	  DBMS_STATS.EXPORT_TABLE_STATS(ownname=>r_stat.owner,
	  				tabname=>r_stat.table_name,
					stattab=>v_stattab,
					cascade=>TRUE,
					statown=>v_ownname);
      exception
        when others then
        dbms_output.put_line('Fail to export table: '||r_stat.owner||'.'||r_stat.table_name);
    end;
  end loop;
end;
/

The code will generate a set of commands that you can use to clean statistics on the destination before importing the source’s stats. As some of the objects used in the execution plan may not have any statistics in the source environment, it may be a good idea to clean all statistics on the destination before the import to be 100% sure that the statistics’ layout is the same in both environments after the import.

The only steps left to execute are (the full script is here):

• Transfer the statistics table to the destination (I have used a DB link).
• Backup existing statistics in the development envrionment (optional but recommended).
• Clean statistics on the destination using the commands generated by the code above.
• Import statistics.
• Test to see if it made the execution plans equal.

This approach helped me today. I hope it will be useful to you one day!
Now I am about to troubleshoot the Oracle optimizer behavior. That is the funnest part of the process. ;)

BTW: Don’t forget to share with me what you think about this blog post by commenting below, reaching me on twitter (FB), or endorsing me on LinkedIn. :)

Yury

PS: You can have an impact on my blogging backlog priority list here.