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.