Improving Statspack: Add Support for Plan Stability
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.
My webinar “The good plan and the bad plan” for DOAG 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  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 .
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.
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
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;
- Küchler, Uwe, Historical SQL Plan from Statspack using DBMS_XPLAN in 12c, https://oraculix.com/2016/06/21/historical-sql-plan-from-statspack-using-dbms_xplan-in-12c/
- Colgan, Maria, Using SQL Patch to add hints to a packaged application, https://blogs.oracle.com/optimizer/using-sql-patch-to-add-hints-to-a-packaged-application
- Gelling, Kevin, Any_value Set function, https://community.oracle.com/ideas/13039#comment-1069168
- Oracle , User-Defined Aggregate Functions Interface, https://docs.oracle.com/database/121/ADDCI/ext_agg_ref.htm#ADDCI4220
- Lothar Flatz, The good Plan and the bad Plan, DOAG Webinar, https://www.doag.org/de/eventdetails?tx_doagevents_single[id]=534779