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

Nov 19, 2012 / By Yury Velikanov

Tags: , ,

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 APPS@PRDB:1>

Explained.

12:32:20 APPS@PRDB:1> select count(*) from apps.plan_table where STATEMENT_ID = 'SQL_BAD_PLAN_01';

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

1 row selected.

12:32:38 APPS@PRDB: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.

View Yury Velikanov's profile on LinkedIn

5 Responses to “Moving Optimizer Statistics for all Objects Involved in an Execution Plan (between databases)”

  • GregG says:

    How about ‘ FAQ: SQLT (SQLTXPLAIN) Frequently Asked Questions [ID 1454160.1]’

    What is the SQLT Testcase Generator?
    When SQLT gathers information about a candidate SQL statement, it automatically builds a testcase to enable the current plan in the SQL to be reproduced. It does this by storing the metadata of all the objects involved (including views and materialized views) in the query in a testcase schema so that it can be recreated on a test schema. No user data is stored, but the optimizer statistics are recorded so that the plan can be recreated. Most of the time data is not necessary to reproduce the plan (typically,it is only necessary to reproduce performance or incorrect results). See the following for more details:

    • GregG thanks to the hint. I wonder if it is documented well how we (end DBAs) would use SQLT to reproduce the case on test envrionment. Need to have a careful look on SQLT. Thanks again.

  • If any tables will be eliminated from transformated query, they wouldn’t be in the plan_table, but, imho, may be needed for cbo. So it may affect the creating of the new plan.

  • Thanks for the feedback. It keeps me going :)

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>