Oracle 11g New Features

SQL Performance Analyzer

It is used to compare the performance of Statement in a tuning set before and after a database change.

EXEC DBMS_SQLTUNE.create_sqlset(sqlset_name => ‘SQLSET1');

Next, the SELECT_CURSOR_CACHE table function is used to retrieve a cursor containing all SQL statements that were parsed by the SPA_TEST_USER schema and contain the word "my_objects". The resulting cursor is loaded into the tuning set using the LOAD_SQLSET procedure.

DECLARE

  l_cursor  DBMS_SQLTUNE.sqlset_cursor;

BEGIN

  OPEN l_cursor FOR

     SELECT VALUE(a)

     FROM   TABLE(

              DBMS_SQLTUNE.select_cursor_cache(

                basic_filter   => 'sql_text LIKE ''%my_objects%'' and parsing_schema_name = ''SPA_TEST_USER''',

                attribute_list => 'ALL')

            ) a;

  DBMS_SQLTUNE.load_sqlset(sqlset_name     => 'spa_test_sqlset',

                           populate_cursor => l_cursor);

END;

/

VARIABLE v_task VARCHAR2(64);

EXEC :v_task :=  DBMS_SQLPA.create_analysis_task(sqlset_name => 'SQLSET1’);

BEGIN

  DBMS_SQLPA.execute_analysis_task(

    task_name       => :v_task,

    execution_type  => 'test execute',

    execution_name  => 'before_change');

END;

/

Now we make certain changes to the table my_object e.g. add index to it.

BEGIN

  DBMS_SQLPA.execute_analysis_task(

    task_name       => :v_task,

    execution_type  => 'test execute',

    execution_name  => 'after_change');

END;

/

BEGIN

  DBMS_SQLPA.execute_analysis_task(

    task_name        => :v_task,

    execution_type   => 'compare performance', 

    execution_params => dbms_advisor.arglist(

                          'execution_name1', 

                          'before_change', 

                          'execution_name2', 

                          'after_change'));

END;

/

SPOOL /tmp/execute_comparison_report.htm

SELECT DBMS_SQLPA.report_analysis_task(:v_task, 'HTML', 'ALL')

FROM   dual;

SPOOL OFF

Other functions includes
It cancels the currently running analysis task.
dbms_sqlpa.cancel_analysis_task(task_name=>t1)