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.
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