Different Metrics for SPA (SQL Performance Analyzer)

I’m more the command line type of person. Once I’ve understand what’s going on behind the curtains I certainly switch to the GUI-click-click tools. But in the case of Real Application Testing – even though the support via the OEM GUI is excellent – sometimes I prefer to run my procedures from the command line and check my reports in the browser.

Recently Thomas, a colleague from Oracle ACS Support, and I were asking ourselves about the different comparison metrics for the SQL Performance Analyzer reporting We did scan the documentation but we found only examples but no complete list. Then we did ask a colleague but thanks to OEM we got an incomplete list as well.

Finally Thomas dug it out – it’s stored in the dictionary in the table V$SQLPA_METRIC:

SQL> SELECT metric_name FROM v$sqlpa_metric;

METRIC_NAME   
-------------------------
PARSE_TIME               
ELAPSED_TIME             
CPU_TIME                 
USER_IO_TIME             
BUFFER_GETS              
DISK_READS               
DIRECT_WRITES            
OPTIMIZER_COST           
IO_INTERCONNECT_BYTES  
 
9 rows selected.

What do you do with these metrics now?

You can use them in such a way:

set timing on

begin
dbms_sqlpa.execute_analysis_task(
   task_name=>'SPA_TASK_MR07PLP_11107_12102',
   execution_name=>'Compare workload Elapsed',
   execution_type=>'compare performance',
   execution_params=>dbms_advisor.arglist(
                     'comparison_metric','elapsed_time',
                     'execution_name1','EXEC_SPA_TASK_MR07PLP_11107',
                     'execution_name2','TEST 11107 workload'),
   execution_desc=>'Compare 11107 Workload on 12102 Elapsed');
end;
/

You can vary the elapsed_time in my example with the various comparison metrics mentioned in v$sqlpa_metric.

–Mike