QUESTION 224--SPA

The workload on your database remains the same between 9AM and 6PM on all days of the week.

However, you notice performance degradation between 3PM and 5PM on some days.

You want to identify regressed statements by comparing the workload between these two periods.

Examine these step:

1. Create a SQL Tuning Set(STS) for the statements cached in the library cache for both periods.

2. Generate a compared Period AWR report for both periods.

3. Run the SQL Performance Analyzer (SPA) on the STS captured for both periods

4. Run the SQL Tuning Analyzer (SPA) on the STS captured for both periods.

5. Run the SQL Access Advisor on the STS captured for both periods.

6. Run the Compare Period ADDM report for both periods.Identify the required steps to accomplish the task.

A. 1 and 4

B. 1 and 5

C. only 6

D. 1 and 3

E. 1 and 2

F. only 2

Correct Answer: D

1.为两个期间缓存在库缓存中的语句创建SQL优化集（STS）。

3.对两个时段捕获的STS运行SQL性能分析器（SPA）

SPA oracle的SQL性能优化分析器，SQL Performance Analyzer。说到SPA就不得不提Oracle关于SQL优化的几个常见的术语，SQL Tuning Advisor, SQL Tuning Set，SQL Performance Analyzer。

STS=SQL Tuning Set，SQL调优集，是一系列Oracle SQL的集合，这些SQL可以从AWR或者cursor cache中获得。可以使用DBMS_SQLTUNE包中SQL Tuning Set相关的函数和过程进行操作。

STA=SQL Tuning Advisor，SQL优化顾问，是针对SQL调优集或者SPA甚至单纯的SQL进行优化。可以使用DBMS_SQLTUNE包中SQL Tuning Advisor相关的函数和过程进行操作。

SPA=SQL Performance Analyzer，SQL性能分析器。针对数据库变更进行的性能分析。变更包括数据库升级，平台/版本/架构变更，参数调整，统计信息收集等等。SPA其实也是Oracle Real Application Testing的一个组件，另外一个组件就是前面介绍过的Database Replay。SPA的原理是通过变更前对收集的STS进行执行，获取基线数据，变更后再次进行执行，然后进行对比，从多个维度比如CPU时间，I/O，buffer get等生成详细的变更对比报告。

典型用法是捕捉STS，然后通过SPA生成对比报告，然后对变更后衰减的SQL调用STA进行优化。