Spa Best Practices

Spa Best Practices

The official spa image is perfect, but there are still many pitfalls in practice. The following shows the various pitfalls and solutions I encountered in my spa practice project.

Optimizer_mode

The actual running of SQL in the target database must be consistent with that in the source database. Taking the Siebel database as an example, SQL sets the following parameters that affect CBO at the session level.

alter session set optimizer_mode=FIRST_ROWS_10;alter session set "_hash_join_enabled"=FALSE;alter session set "_optimizer_sortmerge_join_enabled"=FALSE;alter session set "_optimizer_join_sel_sanity_check"=TRUE;

The official method is as follows:

exec dbms_sqlpa.set_analysis_task_parameter(‘SPA_TASK_NAME‘,‘APPLY_CAPTURED_COMPILENV‘, 1);

In actual tests, it is found that this method has no effect on the method of using dblink to connect to the previous execution (the target database version of the source database is 10.2.0.5 spa test database version 11.2.0.3 ). Therefore, you can only obtain one STs for the SQL statement of the optimer user at the session level when taking the STS, and then run it after modifying the parameters at the system level of the test database. There is no good solution for using dblink.

SQL runs for a long time and is uncontrollable

• The actual execution of SQL statements by SPA is a serial execution. For parallel execution, refer to this brother's blog (http://hongyedba.cn/2014/05/20/thought_about_parallel_spa/), Paste several self-drawn flowchart.
• Set parameters to limit the execution time of each SQL statement. For example, set the maximum execution time of each SQL statement to 1 minute.dbms_advisor.arglist(‘LOCAL_TIME_LIMIT‘,‘60‘)

The spa plan_change item is incorrect.

The spa analysis report lists the SQL statements that change the execution plan for subsequent optimization. However, when the SPA determines whether the Plan is the same, the standard isPLAN_HASH_VALUE. However, PHV is affected by the size, that is, the PHV calculated by the same plan on the big end is different from that calculated on the small end. (The article can refer to: http://carlos-sierra.net/tag/plan-hash-value/) More pitfall is that this standard can not be changed. But what should we do if we want to know the SQL statements planned to change in actual execution. Oracle does not provide a good solution in the spa. You can only use it by yourself.dba_advisor_sqlplansAnddba_sqlset_plansThe two tables are compared.

Solution:

create or replace function plan_change(p_sql_id IN VARCHAR2,p_task_name  IN VARCHAR2,p_execution_name IN VARCHAR2,p_sqlset_nameIN VARCHAR2)  return VARCHAR2 is  l_plan_changed number := 0;begin  select count(1)into l_plan_changedfrom (select id, operation, options, object_owner, object_namefrom dba_advisor_sqlplans   where sql_id = p_sql_id and task_name = p_task_name and execution_name = p_execution_name  minus  select id, operation, options, object_owner, object_namefrom dba_sqlset_plans   where sql_id = p_sql_id and sqlset_name = p_sqlset_name) diff;  if l_plan_changed <> 0 thenreturn ‘Y‘;  end if;  select count(1)into l_plan_changedfrom (select id, operation, options, object_owner, object_namefrom dba_sqlset_plans   where sql_id = p_sql_id and sqlset_name = p_sqlset_name  minus  select id, operation, options, object_owner, object_namefrom dba_advisor_sqlplans   where sql_id = p_sql_id and task_name = p_task_name and execution_name = p_execution_name) diff;  if l_plan_changed <> 0 thenreturn ‘Y‘;  elsereturn ‘N‘;  end if;end plan_change;

Without considering the business, the above functions are more accurate. For star queries in the business, the primary table is correct, then the join sequence of other tables is switched or the index of some tables is changed. The above function will think that the plan is different. However, you don't need to worry too much about such SQL statements, as long as the following predicates remain unchanged. Buffer gets does not increase much.

Find and analyze the problem SQL

Analyze which SQL statements have problems when executing in the target database and are not as simple as they are shown in the official report. Let's take a look at the difficulties.

• SQL statement for execution plan change (inaccurate in the report)
• SQL statements with lower performance (select the dimension for comparison)
  • Elasped time because no cache is available during spa execution, this is not allowed.
  • The CPU time source database is a concurrent environment, and the spa uses serial execution by default. This is not accurate either.
  • Buffer gets is relatively accurate, but the SQL statistics of the source database are average, and the target database is the actual value of a single time. In fact, the difference is quite big. Therefore, the SQL statements that show performance degradation through buffer gets must also bind the variable value to the source database. The target database will actually run it and compare the actual buffer gets.
• Time out SQL
  • This type of SQL statement is important. In fact, it depends on the plan change, that is, the execution plan changes after the time-out.
  • In spa, the SQL Execution time limit can only be a fixed time. You cannot dynamically set the execution time for each SQL statement based on the historical execution time of each SQL statement, therefore, some SQL statements have been executed for a long time, and the mat plan has not changed. In fact, there is no problem.
  • Time out SQL queries to bind variable values are a little more troublesome. No variable values are bound in the report, and the source database may not have the SQL information. When the test database is crawled, it will also find that the SQL statement run by SPA will add some comments before the SQL text to change the SQL _id. If you look for it directly by text, it may be a line break, space and other reasons cannot be found. Solution: re-run the time out SQL statement in the test database, and then pressSQL _fulltext like '% condition 1%'Result set intersectSQL _fulltext like '% condition 2%'Or more conditions.