HOL 18c SQL Performance Analyzer

In this section of the Hands-On Lab you will use the SQL Performance Analyzer (SPA) which is part of Real Application Testing (RAT). You will compare statements collected before upgrade to a simulation of these statements after upgrade. You will use the SQL Tuning Sets collected earlier in the lab.

SQL Performance Analyzer

You have collected SQL statements from the first load of HammerDB earlier in this lab into two SQL Tuning Sets:

  • STS_CaptureAWR
  • STS_CaptureCursorCache

You will “test execute” now the statements of one of the SQL Tuning Sets (you can do both if time allows) and generate a comparison report.

At first, check how many statements you collected in the SQL Tuning Sets:

. upgr18
cd /home/oracle/scripts
sqlplus / as sysdba

Run this query:

select count(*), sqlset_name from dba_sqlset_statements group by sqlset_name order by 2;

Then start a completely scripted SQL Performance Analyzer run. It will now:

  • Convert the information from STS_CaptureAWR into the right format
  • Simulate the execution of all statements in STS_CaptureAWR
  • Compare before/after
  • Report on the results – in this case based on CPU_TIME and ELAPSED_TIME
    There are more metrics available. See an overview here.

You will do now two simulations using different comparison metrics for both, CPU_TIME and ELAPSED_TIME.

Start the an initial run for CPU_TIME with the script:

@/home/oracle/scripts/spa_cpu.sql

Afterwards generate the HTML Report containing the results:

@/home/oracle/scripts/spa_report_cpu.sql

Then repeat this for ELAPSED_TIME:

@/home/oracle/scripts/spa_elapsed.sql

Afterwards generate the HTML Report containing the results:

@/home/oracle/scripts/spa_report_elapsed.sql

There will be now two html files in /home/oracle/scripts. Open them with Firefox:

cd /home/oracle/scripts
firefox compare_spa_* &

First of all, see the different comparison metrics in the report’s header:

You may recognize regressed statements and statements with plan changes (rightmost column):

*** This screenshot is just an example – you may see a very different report ***

The statement in the screen shot is slightly better than before measured.

Click in the link with the SQL_ID: 13dn4hkrzfpdy

Scroll down a bit and compare plan before/after. Then go forward to the next chapter.

 


==> NEXT: SQL Plan Management