Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

        
 

 Oracle DBMS_SQLPA Package
Oracle Tips by Burleson Consulting

Advanced Oracle Utilities: The Definitive Reference by Rampant TechPress is written by the top Oracle database experts (Bert Scalzo, Donald Burleson, and Steve Callan).  The following is an excerpt from the book.

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

     


For more details on Oracle utilities, see the book "Advanced Oracle Utilities" by Bert Scalzo, Donald K. Burleson, and Steve Callan.

You can buy it direct from the publisher for 30% off directly from Rampant TechPress.

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.