Improve Oracle statspack - add support for plan stability

Improving Statspack: Add Support for Plan Stability

from

Fundamentals: Change Statspack Code

Deutsche Version hier :Oracle Statspack verbessern: Historische Pläne – Teil 2

Changes to the statspack code needs to be carefully considered. Do the benefits prevail the potential risk?
The change proposed here will allow you to reestablish an execution plan from the past quickly and without much reconsideration.

Under the condition of frequent plan changes, such an option really helps. In this case, you should seriously consider the changes described here.

You have the statspack source code. You should keep the original code version and can therefore quickly restore the original code at any time. The additional optional column does not make a difference.

Starting Point

My webinar “The good plan and the bad plan” for DOAG[5] was about reactivating good plans from the past.
If you can read the AWR data, you can, for example, create an outline from the DBA_HIST data using dbms_xplan.display_awr.

This outline can then be planted in current plans.
At that time I was asked whether such a method also works on a Standard Edition.

With the SQL_PATCH [2] there is a legal possibility to plant an outline in a sql command.
The only question is how to get an outline of an old plan on a Standard Edition. Does Statspack have a function similar to dbms_xplan.display_awr?

Uwe Küchler, aka Oraculix has a very creative solution ready [1].
You simply use dbms_xplan.display in connection with a search function:

select * from table(dbms_xplan.display(
  table_name   => 'perfstat.stats$sql_plan',
  statement_id => null,
  format       => 'ALL -predicate -note',
  filter_preds => 'plan_hash_value = '|| &&phv
);

To use this workaround, you must add a column to the stats$sql_plan table, the details can be found in the Oraculix blog.

Can you simply specify + OUTLINE in the format parameter using the solution above? Then you would have to get the outline. You could then write a statement about a SQL_PATCH and voila, you have the old plan back.

If you try will see that, the format instruction is apparently simply ignored.

The reason for this is that some columns are missing in the statspack plans. Specifically, to obtain an outline, you would need the OTHER_XML column.

The Column Other_xml

In this column you will find the outline as main content. If you want to have the very important function of the historical plans, do not be afraid to change the Statspack code.

If Oracle already suggests in MOS Note 2182680.1 change to change statspack, why shouldn’t we go a little further?

We start by adding the column.

ALTER TABLE perfstat.stats$sql_plan ADD timestamp INVISIBLE AS (cast(NULL AS DATE));
ALTER TABLE perfstat.stats$sql_plan ADD OTHER_XML CLOB;

In the next step we have to adjust the code. Analogous to the procedure in MOS Note 2182680.1, I first made a copy of the current code.

Again, it is the script spcpkg.sql that needs to be adjusted.

Inside spcpkg look for this insert:

insert into stats$sql_plan
                 ( plan_hash_value
                 , id
                 , operation
                 , options
                 , object_node
                 , object#

Her we must add support for the column other_xml. It is tricky because in the select part of the insert a maximum function is used for deduplication, like this:

                 , max(sp.operation)
                 , max(sp.options)
                 , max(sp.object_node)
                 , max(sp.object#)
                 , max(sp.object_owner)
                 , max(sp.object_name)
                 , max(sp.object_alias)
                 , max(sp.object_type)

So why not try: Max (other_xml), should we not? ORA-00932 comes promptly. The reason for this is that CLOBs are not sortable, so no maximum can be calculated.

ANY_VALUE

Of course, there are other to deduplicate the rows rather than a group function like max. We could use an analytical function like row_number () for instance.

However, I don’t want to change the original code as much.

I came up with an idea from the Oracle Technology Network, which was about introducing a new type of group function for precisely such problems, called ANY_VALUE [3.].

The idea is apparently implemented with version 20c. Too bad. With version 19 it would be better.
However, as a former Oracle employee, I know that sometimes the previous versions already contain undocumented changes to the subsequent version.

And indeed: any_value already exists. Unfortunately, this implementation also throws ORA-00932 completely unnecessarily.
I made Chris Saxon aware of this and hope that Oracle will change the implementation once it becomes official. Well if Oracle doesn’t deliver I have to do it myself.
I defined the function myself via the User-Defined Aggregate Functions Interface and called it any_lob.

You can find the code at the end of this blog. Here is the complete insert command.

All changes that deviate from the standard are marked with red. I described the hints in the previous blog about statspack.

            insert into stats$sql_plan
                 ( plan_hash_value
                 , id
                 , operation
                 , options
                 , object_node
                 , object#
                 , object_owner
                 , object_name
                 , object_alias
                 , object_type
                 , optimizer
                 , parent_id
                 , depth
                 , position
                 , search_columns
                 , cost
                 , cardinality
                 , bytes
                 , other_tag
                 , partition_start
                 , partition_stop
                 , partition_id
                 , other
                 , other_xml                 
                 , distribution
                 , cpu_cost
                 , io_cost
                 , temp_space
                 , access_predicates
                 , filter_predicates
                 , projection
                 , time
                 , qblock_name
                 , remarks
                 , snap_id
                 )
            select /*+ leading(spu@np ssp@sq  s sp) */
                   new_plan.plan_hash_value
                 , sp.id
                 , max(sp.operation)
                 , max(sp.options)
                 , max(sp.object_node)
                 , max(sp.object#)
                 , max(sp.object_owner)
                 , max(sp.object_name)
                 , max(sp.object_alias)
                 , max(sp.object_type)
                 , max(sp.optimizer)
                 , max(sp.parent_id)
                 , max(sp.depth)
                 , max(sp.position)
                 , max(sp.search_columns)
                 , max(sp.cost)
                 , max(sp.cardinality)
                 , max(sp.bytes)
                 , max(sp.other_tag)
                 , max(sp.partition_start)
                 , max(sp.partition_stop)
                 , max(sp.partition_id)
                 , max(sp.other)
                 , any_lob(sp.other_xml)
                 , max(sp.distribution)
                 , max(sp.cpu_cost)
                 , max(sp.io_cost)
                 , max(sp.temp_space)
                 , 0 -- should be max(sp.access_predicates) (2254299)
                 , 0 -- should be max(sp.filter_predicates)
                 , max(sp.projection)
                 , max(sp.time)
                 , max(sp.qblock_name)
                 , max(sp.remarks)
                 , max(new_plan.snap_id)
              from (select /*+ QB_NAME(NP)  */  
                           spu.plan_hash_value
                         , spu.hash_value    hash_value
                         , spu.address       address
                         , spu.text_subset   text_subset
                         , spu.snap_id       snap_id
                      from stats$sql_plan_usage spu
                     where spu.snap_id         = l_snap_id
                       and spu.dbid            = p_dbid
                       and spu.instance_number = p_instance_number
                       and not exists (select /*+ QB_NAME(SQ)  */ *
                                         from stats$sql_plan ssp
                                        where ssp.plan_hash_value 
                                            = spu.plan_hash_value
                                      )
                   )          new_plan
                 , v$sql      s      -- join reqd to filter already known plans
                 , v$sql_plan sp
             where s.address         = new_plan.address
               and s.plan_hash_value = new_plan.plan_hash_value
               and s.hash_value      = new_plan.hash_value
               and sp.hash_value     = new_plan.hash_value
               and sp.address        = new_plan.address
               and sp.hash_value     = s.hash_value
               and sp.address        = s.address
               and sp.child_number   = s.child_number
             group by 
                   new_plan.plan_hash_value, sp.id
             order by
                   new_plan.plan_hash_value, sp.id; -- deadlock avoidance

Summary

The code seems to work well.

Only one thing was a little annoying: Whenever I had syntax errors in the insert, I also had to recompile the any_lob function.

Note: You have to switch the Snap Level to 6 or higher for the Statspack to collect plans. E.g .:

EXECUTE statspack.snap(i_snap_level => 7);
Create or replace type any_lob_type as object
(
v_clob CLOB,

static function ODCIAggregateInitialize
( sctx in out any_lob_type )
return number ,

member function ODCIAggregateIterate
( self in out any_lob_type ,
value in CLOB
) return number ,

member function ODCIAggregateTerminate
( self in any_lob_type,
returnvalue out CLOB,
flags in number
) return number ,

member function ODCIAggregateMerge
( self in out any_lob_type,
ctx2 in any_lob_type
) return number
);
/

create or replace type body any_lob_type
is

static function ODCIAggregateInitialize
( sctx in out any_lob_type )
return number
is
begin

sctx := any_lob_type( null ) ;

return ODCIConst.Success ;

end;

member function ODCIAggregateIterate
( self in out any_lob_type ,
value in CLOB
) return number
is
begin

self.v_clob := value ;

return ODCIConst.Success;

end;

member function ODCIAggregateTerminate
( self in any_lob_type ,
returnvalue out CLOB ,
flags in number
) return number
is
begin

returnvalue := self.v_clob;
return ODCIConst.Success;
end;

member function ODCIAggregateMerge
( self in out any_lob_type ,
ctx2 in any_lob_type
) return number
is
begin

return ODCIConst.Success;
end;

end;
/

create or replace function any_lob
( input CLOB )
return CLOB
deterministic
parallel_enable
aggregate using any_lob_type
;
grant execute on any_lob to public;
create public sysnonym for sys.any_lob;

Sources