感覺就是用來把正式環境的資料捕獲後放到測試環境，測試環境再修改一些配置，看這些捕獲的資料的效能有什麼改變，和replay差不多的功能

SQL Performance Analyzer
SQL Performance Analyzer enables you to assess the impact of system changes on the response time of SQL statements.
SQL效能分析器使您可以評估系統更改對SQL語句響應時間的影響。
You can run SQL Performance Analyzer on a production system or a test system that closely resembles(類似) the production system.Testing a system change on a production system will impact the system's throughput because SQL Performance Analyzer must execute the SQL statements that you are testing. Any global changes made on the system to test the performance effect may also affect other users of the system. If the system change does not impact many sessions or SQL statements, then running SQL Performance Analyzer on the production system may be acceptable.However, for systemwide changes—such as a database upgrade—using a production system is not recommended. Instead, consider running SQL Performance Analyzer on a separate test system so that you can test the effects of the system change without affecting the production system.Using a test system also ensures that other workloads running on the production system will not affect the analysis performed by SQL Performance Analyzer. Running SQL Performance Analyzer on a test system is the recommended approach and the methodology described here. If you choose to run the SQL Performance Analyzer on the production system, then substitute the production system for the test system where applicable.

SQL Performance Analyzer的大致步驟
1.Capturing the SQL Workload
You can store captured SQL statements in a SQL tuning set and use it as an input source for SQL Performance Analyzer
可以使用SQL tuning set來捕獲SQL Workload

2.Setting Up the Test System
After the test system is properly configured, export the SQL tuning set from the production system to a staging table, then import it from the staging table into the test system.

3.Creating a SQL Performance Analyzer Task
When creating a SQL Performance Analyzer task, you will need to select a SQL tuning set as its input source.
使用DBMS_SQLPA.CREATE_ANALYSIS_TASK

4.Measuring the Pre-Change SQL Performance
使用DBMS_SQLPA.EXECUTE_ANALYSIS_TASK

5.Making a System Change

6.Measuring the After-Change SQL Performance
使用DBMS_SQLPA.EXECUTE_ANALYSIS_TASK

7.生成上面4、6步的報表，檢視第5步進行修改的話有什麼影響
使用DBMS_SQLPA.REPORT_ANALYSIS_TASK
SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK(task_name => 'XX', execution_name=>'YY') FROM DUAL;

SQL tuning set
A SQL tuning set (STS) is a database object that includes one or more SQL statements along with their execution statistics and execution context, and could include a user priority ranking(優先順序排名). You can load SQL statements into a SQL tuning set from different SQL sources, such as AWR, the shared SQL area, or customized SQL provided by the user. You can export SQL tuning sets from one database to another, enabling transfer of SQL workloads between databases for remote performance diagnostics and tuning(遠端效能診斷和調優). When poorly performing SQL statements occur on a production database, developers may not want investigate and tune directly on the production database(不想直接調查和調整生產資料庫).The DBA can transport the problematic SQL statements to a test database where the developers can safely analyze and tune them. To transport SQL tuning sets, use the DBMS_SQLTUNE package.

檢視SQL tuning set的資訊
select * from dba_sqlset

檢視SQL tuning set包含的SQL語句的資訊
select * from dba_sqlset_statements

檢視某個SQLSET包含的SQL語句的資訊
select * from dba_sqlset_statements where sqlset_name='SQLSET_NAME'
或
select * from table(dbms_sqltune.select_sqlset(sqlset_name=>'SQLSET_NAME',sqlset_owner=>'SQLSET_OWNER'));
上述語句如果不指定sqlset_owner在預設是當前使用者

SQL tuning set的大致步驟
1.Creating a SQL Tuning Set
建立sqlset(DBMS_SQLTUNE.CREATE_SQLSET)

2.Loading a SQL Tuning Set
載入執行的SQL到SQLSET裡面，sql可以來自AWR或直接來自v$sql(DBMS_SQLTUNE.LOAD_SQLSET)
來自v$sql的話（就是直接捕獲執行中的SQL）使用DBMS_SQLTUNE.SELECT_CURSOR_CACHE
來自awr的話使用DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY

3.Transporting a SQL Tuning Set
3.1 create a staging table where the SQL tuning sets will be exported
建立存放SQLSET資訊的表(DBMS_SQLTUNE.create_stgtab_sqlset)
3.2 export SQL tuning sets into the staging table
匯入SQLSET的資訊至建立的表內(DBMS_SQLTUNE.pack_stgtab_sqlset)
3.3 Move the staging table to the target database such as expdp
expdp匯出建立的表
3.4 On the target database where the SQL tuning sets will be imported
impdp匯出表資訊，並把表資訊匯出至sqlset(DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET)

一個使用SQL tuning set進行SQL Performance Analyzer的案例
IN PROD1 execute the following SQLs:
conn hr/hr
select /*LOAD_STS*/ * from hr.locations
select /*LOAD_STS*/ * from hr.employees
select /*LOAD_STS*/ * from hr.departments
Then create a SQL tuning set name STS_OCM_TEST to include these 3 statements,and move the STS_OCM_TEST to PROD2.ON PROD2 using SQL Performance Analyzer to test the performance impact for the STS_OCM_TEST when optimizer_features_enable is change to 11.2.0.1 from 10.2.0.1

第一步：建立SQLSET,名稱為STS_OCM_TEST(以下1-6步都是在ORACLE_SID=PROD1的環境中)
實驗使用的是oracle的樣例使用者hr和它下面的樣例表，使用其他使用者和其他SQL一樣的
ORACLE_SID=PROD1
sqlplus / as sysdba
grant administer sql tuning set to hr;
conn hr/hr
select /*LOAD_STS*/ * from hr.locations
select /*LOAD_STS*/ * from hr.employees
select /*LOAD_STS*/ * from hr.departments
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => 'STS_OCM_TEST',
description => 'STS_OCM_TEST');
END;
/

第二步：LOAD_SQLSET即載入執行的SQL到SQLSET裡面
這一步偷懶，直接給hr使用者賦予了dba許可權
grant dba to hr;
DECLARE
baseline_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
OPEN baseline_cursor FOR
SELECT VALUE(p)
FROM TABLE (DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
'sql_text like ''select /*LOAD_STS*/%''',
NULL,NULL,NULL,NULL,NULL,NULL,'ALL')) p;

DBMS_SQLTUNE.LOAD_SQLSET(
sqlset_name => 'STS_OCM_TEST',
populate_cursor => baseline_cursor);
END;
/

第三步：建立存放SQLSET資訊的表STS_OCM_TEST_TAB
下面不寫schema_name的話，預設就是當前使用者，實驗情況下當前使用者就是HR，所以schema_name可寫可不寫
BEGIN
DBMS_SQLTUNE.create_stgtab_sqlset(
table_name => 'STS_OCM_TEST_TAB',
schema_name => 'HR');
END;
/

第四步：匯入SQLSET的資訊至第三步建立的表STS_OCM_TEST_TAB中
BEGIN
DBMS_SQLTUNE.pack_stgtab_sqlset(
sqlset_name => 'STS_OCM_TEST',
sqlset_owner => 'HR',
staging_table_name => 'STS_OCM_TEST_TAB',
staging_schema_owner => 'HR');
END;
/

第五步：匯出表STS_OCM_TEST_TAB
expdp hr/hr directory=DATA_PUMP_DIR dumpfile=STS_OCM_TEST_TAB.dmp tables=STS_OCM_TEST_TAB

第六步：複製檔案到PROD2下面的dump目錄
cp /db/oracle/admin/PROD1/dpdump/STS_OCM_TEST_TAB.dmp /db/oracle/admin/PROD2/dpdump/

第7步：匯入表STS_OCM_TEST_TAB(以下都是在ORACLE_SID=PROD2的環境中)
ORACLE_SID=PROD2
impdp hr/hr directory=DATA_PUMP_DIR dumpfile=STS_OCM_TEST_TAB.dmp

第8步：匯出表STS_OCM_TEST_TAB資訊給到SQLSET
conn hr/hr
BEGIN
DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(
sqlset_name => '%',
replace => TRUE,
staging_table_name => 'STS_OCM_TEST_TAB');
END;
/

第9步：建立SQL Performance Analyzer的Analysis Task即Creating a SQL Performance Analyzer Task
VARIABLE t_name VARCHAR2(100);
EXEC :t_name := DBMS_SQLPA.CREATE_ANALYSIS_TASK(sqlset_name => 'STS_OCM_TEST', -
task_name => 'STS_OCM_TEST_task');

第10步：Measuring the Pre-Change SQL Performance
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'STS_OCM_TEST_task', -
execution_type => 'TEST EXECUTE', -
execution_name => 'my_exec_BEFORE_change');

第11步：修改會影響SQL效果的配置即Making a System Change
alter system set optimizer_features_enable='10.2.0.1';

第12步：建立修改配置後的ANALYSIS_TASK即Measuring the After-Change SQL Performance
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'STS_OCM_TEST_task', -
execution_type => 'TEST EXECUTE', -
execution_name => 'my_exec_AFTER_change');

第13步：生成兩個ANALYSIS_TASK的報告
SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK(task_name => 'STS_OCM_TEST_task', execution_name=>'my_exec_BEFORE_change') FROM DUAL;
SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK(task_name => 'STS_OCM_TEST_task', execution_name=>'my_exec_AFTER_change') FROM DUAL;

SPA_SQL Performance Analyzer_SQL Tuning Set

相關文章