Hi,

I will explain What is the SQL Performance Analyzer and SPA Report in this article.

 

SQL Performance Analyzer in Oracle

You can read the previous article of Oracle Database Performance Tuning Tutorial series with the following link.

What is the Optimizer Hints and How to Use Hints in Oracle | Oracle Database Performance Tuning Tutorial -16

 

 

 

SQL Performance Analyzer and SPA Report in Oracle

Oracle database has SQL Performance Analyzer ( DBMS_SQLPA Packages and procedures ) utility that assess the performance impact of any system change resulting in changes to SQL execution plans and Database performance. These major changes are as follows.

• Database upgrade ( Oracle 11g—> Oracle 12c, Oracle 12c–> Oracle 19c )
• Database parameter changes
• Gather statistics
• Schema changes
• Create the new Indexes
• Create the new Materalized views.
• Hardware or operating system changes.

 

Now I will explain you how to use SQL Performance Analyzer ( SPA ) with  an example. In this example; I will create the new Index on a test table, and show you Execution Plan Before Change and Execution Plan After Change in the SPA Report.

 

Step-1: Create the new Test table and Insert the test data ( 10.000 rows ) as follows.

 

SQL> create table msdeveci.test
(id number not null);

Table created.

SQL> begin
for i in 1..10000 loop
insert into msdeveci.test(id)
values (i);
end loop;
commit;
end;
/

PL/SQL procedure successfully completed.



SQL> select count(*) from msdeveci.test;

COUNT(*)
----------
10000

 

 

Step-2: Let’s go to run the following SQL Statement, and review the Execution plan.

SQL> set serveroutput off
col id format 99999
select a.id, b.id
from msdeveci.test a, msdeveci.test b
where a.id=b.id
and b.id=500

    ID     ID
------ ------
   500    500

SQL> select * from table(
     dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  7t9sbj6rz1wbn, child number 0
-------------------------------------
select a.id, b.id   from msdeveci.test a, msdeveci.test b  where
a.id=b.id    and b.id=500

Plan hash value: 16165315

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |    15 (100)|          |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|*  1 |  HASH JOIN         |      |     1 |    26 |    15   (7)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| TEST |     1 |    13 |     7   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| TEST |     1 |    13 |     7   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("A"."ID"="B"."ID")
   2 - filter("A"."ID"=500)
   3 - filter("B"."ID"=500)

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


27 rows selected.

 

Run SQL Performance Analyzer

 

Step-3: Create a SQL Tuning Set ( STS ) and move this SQL into this STS.

SQL> BEGIN
  2  DBMS_SQLTUNE.CREATE_SQLSET(
  3      sqlset_name => 'TEST_SPA',
  4      description => 'Index Analyze');
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL> 
SQL> accept sql_id prompt "Enter value for sql_id: "
Enter value for sql_id: 7t9sbj6rz1wbn
SQL> 



SQL> DECLARE
  2    l_cursor  DBMS_SQLTUNE.sqlset_cursor;
  3  BEGIN
  4    OPEN l_cursor FOR
  5        SELECT VALUE(p)
  6          FROM   TABLE (
  7             DBMS_SQLTUNE.select_cursor_cache (
  8               'sql_id = ''&sql_id''', -- basic_filter
  9               NULL, -- object_filter
 10               NULL, -- ranking_measure1
 11               NULL, -- ranking_measure2
 12               NULL, -- ranking_measure3
 13               NULL, -- result_percentage
 14               1)    -- result_limit
 15          ) p;
 16    DBMS_SQLTUNE.load_sqlset (
 17        sqlset_name    => 'TEST_SPA',
 18        populate_cursor => l_cursor);
 19  END;
 20  /
old   8:              'sql_id = ''&sql_id''', -- basic_filter
new   8:              'sql_id = ''7t9sbj6rz1wbn''', -- basic_filter

PL/SQL procedure successfully completed.

SQL> 



SQL> col sql format a50
SQL> set lines 120
SQL> SELECT sql_id,
         substr(sql_text, 1, 50) sql
    FROM TABLE(
        DBMS_SQLTUNE.select_sqlset (
           'TEST_SPA'));

SQL_ID        SQL
------------- --------------------------------------------------
7t9sbj6rz1wbn select a.id, b.id
                from msdeveci.test a, msdeveci

 

 

 

If you don’t know what is the SQL Tuning Set and how to create it, you can read the following article.

https://ittutorial.org/what-is-the-sql-tuning-set-and-how-to-create-the-sql-tuning-sets-in-oracle-oracle-database-performance-tuning-tutorial-9/

 

 

 

Step-4: Now create the analysis_task for the created STS (TEST_SPA) and  execute this analysis task ( store_spa_task_before name ) before Index create.

 

Create the analysis task.

var v_out char(50)
begin
:v_out:=dbms_sqlpa.create_analysis_task(
sqlset_name => 'TEST_SPA',
task_name => 'store_spa_task');
end;
/
print v_out
PL/SQL procedure successfully completed.

SQL>

V_OUT
------------------------------------------------------------------------------------------------------------------------
store_spa_task


List the SPA Tasks.

SQL> 
SQL> col TASK_NAME format a14
SQL> col ADVISOR_NAME format a24
SQL> select TASK_NAME,
ADVISOR_NAME,
created
from DBA_ADVISOR_TASKS
where task_name='store_spa_task';

TASK_NAME ADVISOR_NAME CREATED
-------------- ------------------------ ---------
store_spa_task SQL Performance Analyzer 04-APR-2020



Now execute the analysis task as follows.

SQL> begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'store_spa_task',
execution_type => 'TEST EXECUTE',
execution_name => 'store_spa_task_before');
end;
/

PL/SQL procedure successfully completed.

SQL> 


Check if it is completed or not


SQL> col TASK_NAME format a20
SQL> select execution_name,
2 status,
3 execution_end
4 from DBA_ADVISOR_EXECUTIONS
5 where task_name='store_spa_task'
6 order by execution_end;

EXECUTION_NAME STATUS EXECUTION
------------------------------ ----------- ---------
store_spa_task_before COMPLETED 04-APR-2020

 

 

Step-5: Now create the index on related table as follows.

 

SQL> create unique index test_idx on msdeveci.test(id);

Index created.

 

Step-6: Run the analysis task again with the new store_spa_task_after name.

 

SQL> 
SQL> begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'store_spa_task',
execution_type => 'TEST EXECUTE',
execution_name => 'store_spa_task_after');
end;
/

PL/SQL procedure successfully completed.


SQL>

 

List all SPA tasks as follows.

 

SQL> col TASK_NAME format a20
SQL> select execution_name,
status,execution_end
from DBA_ADVISOR_EXECUTIONS
where task_name='store_spa_task'
order by execution_end;

EXECUTION_NAME STATUS EXECUTION
------------------------------ ----------- ---------
store_spa_task_before COMPLETED 04-APR-2020
store_spa_task_after COMPLETED 04-APR-2020

 

 

 

Step-7: Run the compare analysis task as follows.

 

SQL> begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'store_spa_task',
execution_type => 'COMPARE PERFORMANCE',
execution_name => 'store_spa_task_compare',
execution_params => dbms_advisor.arglist(
'execution_name1',
'store_spa_task_before',
'execution_name2',
'store_spa_task_after')
);
END;
/

PL/SQL procedure successfully completed.

 

 

Step-8: Now run the report analysis task and generate SPA Report as follows.

 

SQL> variable rep CLOB;
begin
:rep := DBMS_SQLPA.REPORT_ANALYSIS_TASK(
task_name=>'store_spa_task',
type=>'HTML',
level=>'ALL',
section=>'ALL');
end;
/

PL/SQL procedure successfully completed.


SQL> 
SET LONG 100000
set LONGCHUNKSIZE 100000
set LINESIZE 200
set head off
set feedback off
set echo off
spool test_spa.html
PRINT :rep

<html>
<head>
<title>
SQL Performance Impact Analyzer Report
</title>
<style type="text/css">
body, table, input, select, textarea
{font:normal normal 8pt Verdana,Arial;text-decoration:none;color:#000000;}
.s8 {font-size:8pt;color:#006699}
-----------------------------------------------------------------------------------------------------------------------------------
</ul>
<hr size="1" width="650" align="left"/>
</body>
</html>


SQL> spool off
SQL> set head on
SQL> 
SQL> exit

 

Step-9: Review the SQL Performance Analyzer Report, it will provide you detailed report about New Index and New Execution plan.

 

 

 

 

 

 

 

 

I will continue to explain Performance tuning tutorial in the next articles.

 

Next post link about Performance Tuning Tutorial is as follows.

Oracle SQLTXPLAIN (SQLT) Tips and Tricks | Oracle Database Performance Tuning Tutorial -18

 

 

 

Do you want to learn Oracle Database Performance Tuning detailed, then read the following articles.

Performance Tuning and SQL Tuning Tutorial in the Oracle Database