SPA  有这样的一个功能,变更前的 STS 与变更后的 STS 做对比.  我们现在就来测试一下


1.构造 STS


SQL> create table system.wh as select * from dba_objects;


表已创建。


SQL> execute dbms_stats.gather_table_stats('SYSTEM','WH');


PL/SQL 过程已成功完成。




SQL> select /* find_me */ count(*) from system.wh where object_id <= 100;
        99


SQL> select /* find_me */ count(*) from system.wh where object_id = 100;
         1


SQL> select /* find_me */ count(*) from system.wh where object_id <= 1000;
       998


SQL> select /* find_me */ count(*) from system.wh where object_id = 1000;
         1


SQL> select /* find_me */ count(*) from system.wh where object_id between 100 and 1000;
       900






SQL> EXEC DBMS_SQLTUNE.create_sqlset(sqlset_name => 'sqlset0322');


PL/SQL 过程已成功完成。


定位上面SQL
select sql_id,sql_text from v$sql s where s.SQL_TEXT like '%find_me%';
8mpqkvxb6s05q
8wf171jv9453m
3pyth9hu809ar
c1pfcnk4hx5ab
b1n5c9qpy5h9c
5mkzjrkwjxxvc
ar7ckh2pwmazd






DECLARE
  my_cursor  DBMS_SQLTUNE.sqlset_cursor;
BEGIN
  OPEN my_cursor FOR
     SELECT VALUE(a) FROM TABLE
           (
             DBMS_SQLTUNE.select_cursor_cache(basic_filter=>'sql_id in (''8mpqkvxb6s05q'',''8wf171jv9453m'',''3pyth9hu809ar'',''c1pfcnk4hx5ab'',''b1n5c9qpy5h9c'',''5mkzjrkwjxxvc'',''ar7ckh2pwmazd'') and parsing_schema_name = ''SYS''',attribute_list => 'ALL')
           ) a;
   DBMS_SQLTUNE.load_sqlset(sqlset_name=> 'sqlset0322',populate_cursor => my_cursor);
END;
/




PL/SQL 过程已成功完成。


进行一下验证


SQL> SELECT sql_text FROM   dba_sqlset_statements WHERE  sqlset_name = 'sqlset0322';
select /* find_me */ count(*) from system.wh where object_id = 100
 select /* find_me */ count(*) from system.wh where object_id <= 1000
select /* find_me */ count(*) from system.wh where object_id <= 100
 select /* find_me */ count(*) from system.wh where object_id = 1000
 select /* find_me */ count(*) from system.wh where object_id between 100 and 1000




that is cool


2.执行一次SPA


DBMS_SQLPA.CREATE_ANALYSIS_TASK(
  sql_text         IN CLOB,
  bind_list        IN sql_binds := NULL,
  parsing_schema   IN VARCHAR2  := NULL,
  task_name        IN VARCHAR2  := NULL,
  description      IN VARCHAR2  := NULL)
RETURN VARCHAR2;
 


DBMS_SQLPA.CREATE_ANALYSIS_TASK(
  sql_id           IN VARCHAR2,
  plan_hash_value  IN NUMBER    := NULL,
  task_name        IN VARCHAR2  := NULL,
  description      IN VARCHAR2  := NULL)
RETURN VARCHAR2;
 


DBMS_SQLPA.CREATE_ANALYSIS_TASK(
  begin_snap       IN NUMBER,
  end_snap         IN NUMBER,
  sql_id           IN VARCHAR2,
  plan_hash_value  IN NUMBER    := NULL,
  task_name        IN VARCHAR2  := NULL,
  description      IN VARCHAR2  := NULL)
RETURN VARCHAR2;
 


DBMS_SQLPA.CREATE_ANALYSIS_TASK(
  sqlset_name       IN VARCHAR2,
  basic_filter      IN VARCHAR2 :=  NULL,
  order_by          IN VARCHAR2 :=  NULL,
  top_sql           IN VARCHAR2 :=  NULL,
  task_name         IN VARCHAR2 :=  NULL,
  description       IN VARCHAR2 :=  NULL
  sqlset_owner      IN VARCHAR2 :=  NULL)
RETURN VARCHAR2;




SQL> VARIABLE v_task VARCHAR2(64);
SQL> EXEC :v_task :=  DBMS_SQLPA.create_analysis_task(sqlset_name => 'sqlset0322');


PL/SQL 过程已成功完成。


SQL>  PRINT :v_task
TASK_17092






SQL> BEGIN
  2    DBMS_SQLPA.execute_analysis_task(
  3      task_name       => 'TASK_17092',
  4      execution_type  => 'test execute',
  5      execution_name  => 'before_change');
  6  END;
  7  /


PL/SQL procedure successfully completed.








3.表建立索引


SQL> create index system.ll on system.wh(object_id);


Index created.


SQL> execute dbms_stats.gather_table_stats('SYSTEM','WH');


PL/SQL procedure successfully completed.




4.再执行一次SPA




BEGIN
  DBMS_SQLPA.execute_analysis_task(
    task_name       => 'TASK_17092',
    execution_type  => 'test execute',
    execution_name  => 'after_change');
END;
/




5.进行前向的SPA对比


DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
   task_name         IN VARCHAR2,
   execution_type    IN VARCHAR2               := 'test execute',
   execution_name    IN VARCHAR2               := NULL,
   execution_params  IN dbms_advisor.argList   := NULL,
   execution_desc    IN VARCHAR2               := NULL)
 RETURN VARCHAR2;
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
   task_name         IN VARCHAR2,
   execution_type    IN VARCHAR2               := 'test execute',
   execution_name    IN VARCHAR2               := NULL,
   execution_params  IN dbms_advisor.argList   := NULL,
   execution_desc    IN VARCHAR2               := NULL);


 看一下 execution_params 内容


APPLY_CAPTURED_COMPILENV: indicates whether the advisor could use the compilation environment captured with the SQL statements. The default is 0 (that is, NO).


BASIC_FILTER: basic filter for SQL tuning set


COMPARISON_METRIC: specify an expression of execution statistics to use in performance comparison (Example: buffer_gets, cpu_time + buffer_gets * 10)


DATABASE_LINK: can be set to the global name of a PUBLIC database link. When it is set, SPA will use the database link for all TEST EXECUTE and EXPLAIN PLAN operations by sending the SQL statements to the remote database to be processed remotely. The analysis results will still be stored on the local database.


DAYS_TO_EXPIRE: number of days until the task is deleted


DEFAULT_EXECUTION_TYPE: the task will default to this type of execution when none is specified by the EXECUTE_ANALYSIS_TASK Function & Procedure.


EXECUTION_DAYS_TO_EXPIRE: number of days until the tasks's executions will be deleted (without deleting the task)


EXECUTION_NAME1: name of the first task execution to analyze


EXECUTION_NAME2: name of the second task execution to analyze


LOCAL_TIME_LIMIT: per-statement time out (seconds)


PLAN_FILTER: plan filter for SQL tuning set (see SELECT_SQLSET for possible values)


RANK_MEASURE1: first ranking measure for SQL tuning set


RANK_MEASURE2: second possible ranking measure for SQL tuning set


RANK_MEASURE3: third possible ranking measure for SQL tuning set


RESUME_FILTER: a extra filter for SQL tuning sets besides BASIC_FILTER


SQL_IMPACT_THRESHOLD: threshold of a change impact on a SQL statement. Same as the previous parameter, but at the level of the SQL statement.


SQL_LIMIT: maximum number of SQL statements to tune


SQL_PERCENTAGE: percentage filter of SQL tuning set statements


TIME_LIMIT: global time out (seconds)


WORKLOAD_IMPACT_THRESHOLD: threshold of a SQL statement impact on a workload. Statements which workload change impact is below the absolute value of this threshold will be ignored and not considered for improvement or regression.






BEGIN
  DBMS_SQLPA.execute_analysis_task(
    task_name        => 'TASK_17092',
    execution_type   => 'compare performance', 
    execution_params => dbms_advisor.arglist('execution_name1','before_change','execution_name2','after_change')
    );
END;
/




6.查看一下报告


SET PAGESIZE 0
SET LINESIZE 1000
SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET TRIMSPOOL ON
SET TRIM ON


SPOOL /tmp/execute_comparison_report.htm


SELECT DBMS_SQLPA.report_analysis_task('TASK_17092', 'HTML', 'ALL') FROM   dual;


SPOOL OFF