SPA_SQL Performance Analyzer_SQL Tuning Set
感觉就是用来把正式环境的数据捕获后放到测试环境，测试环境再修改一些配置，看这些捕获的数据的性能有什么改变，和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;
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;
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/30126024/viewspace-2142412/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
批量导入某大张表数据的时候的最佳实践
下一篇：
recover三种方式的说明
请登录后发表评论
登录
全部评论
