The next steps are to get a task name for use
within the call to
DBMS_SQLPA.EXECUTE_ANALYSIS.
VARIABLE v_task
VARCHAR2(64);
EXEC :v_task :=
DBMS_SQLPA.CREATE_ANALYSIS_TASK
(sqlset_name
=> 'my_obj_sqlset');
PRINT :v_task
In the working example, the task name is
TASK_35. Call the before change
run
before_index and the after change
run
after_index. It is known that
something is being changed, i.e. adding an
index, so use something more descriptive than
before_change and
after_change, but that is up to
the DBA. Substitute the task name or take
advantage of the bind variable construct.
BEGIN
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name
=> :v_task,
execution_type
=> 'test execute',
execution_name
=> 'before_index');
END;
/
Now at Step 3, make the change, which is to add
an index to the table as well as gather table
statistics.
CREATE INDEX
idx_my_objects_type on my_objects(object_type);
exec
DBMS_STATS.GATHER_TABLE_STATS
('scott','my_objects',cascade=>TRUE);
The after change job of
after_index is ready to be run.
BEGIN
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name
=> :v_task,
execution_type
=> 'test execute',
execution_name
=> 'after_index');
END;
/
Compare the runs by execution name, or if those
are left out, the last two
EXECUTE_ANALYSIS_TASK calls will
be used.
BEGIN
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name
=> :v_task,
execution_type
=> 'compare performance',
execution_params => dbms_advisor.arglist(
'execution_name1',
'before_index',
'execution_name2',
'after_index'));
END;
/
Finally, the moment one has been waiting for:
what is
the impact of adding an index to the
MY_OBJECTS table? The API to
extract this information is the
REPORT_ANALYSIS_TASK function of
DBMS_SQLPA. There are several
options, so it is worth a moment to look at some
of them.
First, how does one need the report to appear?
The choices are text (the default), HTML and
XML. Next is the level of detail. The choices
are shown below.
|
Level
|
Description
|
|
BASIC
|
Same as typical
|
|
TYPICAL (default)
|
Information about all
statements
|
|
ALL
|
Details of all SQL
|
|
IMPROVED
|
Only improved SQL
|
|
REGRESSED
|
Only regressed SQL
|
|
CHANGED
|
SQL with changed
performance
|
|
UNCHANGED
|
Opposite of CHANGED
|
|
CHANGED_PLANS
|
Only SQL with plan
changes
|
|
UNCHANGED_PLANS
|
Opposite of above
|
|
ERRORS
|
SQL with errors only
|
Table 8.1:
DBMS_SQLPA Options
In the working example, several of the level
choices would fit since it would not be
unreasonable to presume there will be some
improvement and change. Also, maybe there will
be some degradation.
The function is shown below. Oracle’s
documentation shows an extra trailing right
parenthesis, so delete that character if cutting
and pasting from that source.
DBMS_SQLPA.REPORT_ANALYSIS_TASK(
task_name
IN VARCHAR2,
type
IN VARCHAR2 := 'text',
level
IN VARCHAR2 := 'typical',
section
IN VARCHAR2 := 'summary',
object_id
IN NUMBER
:= NULL,
top_sql
IN NUMBER
:= 100,
task_owner
IN VARCHAR2 := NULL,
execution_name
IN VARCHAR2 := NULL)
RETURN CLOB;
For these purposes, go with a report in HTML
format. The simplest case would be to pass the
task name in and default to everything else. Set
up the session for spooling to include a path
and file name and invoke the function by
selecting from DUAL.
SET LONG 1000000
SET PAGESIZE 0
SET LINESIZE 200
SET LONGCHUNKSIZE 200
SET TRIMSPOOL ON
SPOOL C:\temp\spa_index_test.html
SPOOL
C:\temp\spa_index_test.html
SELECT
DBMS_SQLPA.REPORT_ANALYSIS_TASK
('TASK_35','HTML','ALL','ALL')
FROM
dual;
SPOOL OFF
