stored outlines迁移成SQL执行计划基线

stored outline迁移
stored outline是对SQL语句的一组hint。hint指示优化器对SQL语句选择一个特定的执行计划。stored outline是一种遗留技术用于提供执行计划的稳定。

stored outline迁移是用户将stored outline转换为SQL执行计划基线的处理过程。SQL执行计划基线是一组能保证提供良好性能的执行计划。

stored outline迁移的目的
假设你依赖stored outlines来维护执行计划的稳定来阻止性能的下降。Oracle提供了一种方便的方法来安全的将stored outlines转换成SQL执行计划基线。在转换后,可以与stored outline一样来维护执行计划的稳定。通过SQL执行计划基线可以使用更多先进的功能。

有以下问题需要解决:
.stored outlines不能随着时间而进行演进。因此stored outline在创建时性能良好,但是在数据库发生改变之后可能就变的性能很差。

.stored outline中的hints可能会变为无效,例如,一个index hint所指定的索引被删除了。在这种情况下,数据库仍然会使用outline,但不会排除无效索引,生成的执行计划通常比原始执行计划或由优化器所生成的当前执行计划性能要差。

.对于一个SQL语句,优化器只能选择在当前指定目录中存储在stored outline中所定义的执行计划。优化器不能从不同目录中选择不同的stored outline或性能所有提高的当前执行计划。

.stored outlines是一种被动的优化技术,它意味着你只能使用stored outline来解决已经出现的性能问题。例如,你可能创建一个stored outline来修正一个高负载SQL语句。在这种情况下,你可以使用stored outline在SQL语句变为高负载语句之前来代替主动对其进行优化。

stored outline迁移PL/SQL API可以使用以下方式来解决以上问题:
.SQL执行计划基线能让优化器使用同样性能良好的执行计划并且会随着时间推移而进行演进。对于一个特定的SQL语句,可以在验证新执行计划不会影响性能之后将其添加到SQL执行计划基线中。

.SQL执行计划基线会阻止因为无效hint而让执行计划性能变差。如果存储在执行计划中的hint变为无效,那么优化器将不能重复生成该执行计划。在这种情况下,优化器选择一种替代的可重复生成的执行计划基线或者由优化器生成当前成本最低的执行坟墓。

.对于特定SQL语句,数据库可以维护多个执行计划基线。优化器会从一组性能良好的执行计划中选择。

stored outline迁移操作
stored outline迁移操作如下:
1.用户调用一个函数指定要被迁移的outline
2.数据库按照以下方式来处理outline:
a.通过执行计划基线数据库从outline中复制所要的信息。数据库基于outline中的信息可以复制或计算。例如,在两个方案中存在的SQL语句文本,数据库可以从outline复制SQL文本到执行计划基线中。

b.数据库为了获得outline中没有的信息需要重新解析hint。plan hash值与plan cost不能从outline中获得,它需要重新解析hint。

c.数据库创建执行计划基线。

3.当数据库第一次执行相同SQL语句时选择SQL执行计划基线时就能获得丢失的信息。编译环境与执行统计信息只有在执行计划基线被解析与编译时才可以使用。

Outline目录与基线模块
outline是一组hint,而SQL执行计划基线是一组执行计划。因为它们是不同的技术,outline的有些功能不会精确映射成执行计划基线的功能。例如,单个SQL语句可以有多个outline,每一个属于不同的outline目录,但对于当前存在的执行计划基线只有一个目录default。

outline目录:对一组stored outlines指定分组。可以使用不同的目录来对SQL语句维护不同的stored outline。例如,单个语句在oltp与dw目录中创建outline。每个stored outline只能属于一个目录。一个语句可以有多个outline存储在不同目录中,但在每个目录中每个语句只能有一个outline。在执行迁移时,数据库将每个outline目录映射为SQL执行计划基线的模块。缺省的目录名为default

基线模块:指定被执行的高级别函数。一个SQL执行计划基线只能属于一个模块。在outline被迁移到SQL执行计划基线后,模块名缺省值为outline目录名。

基线目录:只能有一个SQL执行计划基线目录存在。这个基线目录叫default。在执行stored outline迁移时,SQL执行计划基线的模块名被设置为stored outline的目录名。在default目录中一个SQL语句可以有多个SQL执行计划基线。

当把stored outline迁移成SQL执行计划基线时,Oracle数据库将使用相同的名称将每个outline目录映射成SQL执行计划基线模块。

dbms_spm执行stored outline迁移
dbms_spm包有以下函数用来执行stored outline迁移:
a.dbms_spm.migrate_stored_outline:将现有stored outline迁移为SQL执行计划基线。可以使用以下格式来执行迁移:
.指定outline名称,SQL文本,outline目录或所有stored outlines
.指定outline名称列表

b.dbms_spm.alter_sql_plan_baseline:改变与SQL语句相关的单个或所有执行计划属性。

c.dbms_spm.drop_migrated_stored_outline:,删除已经补迁移为SQL执行计划基线的stored outline。这个函数将找到dba_outlines中的stored outline并标记为migrated,并且从数据库中删除这些otulines。

与stored outline迁移相关的初始化参数:
.create_stored_outlines:决定Oracle数据库是否自动创建与存储outline。

.optimizer_capture_sql_plan_baselines:启用与禁用自动识可重复SQL语句并为这些SQL语句生成SQL执行计划基线。

.use_stored_outlines:判断是否优化器使用stored outline来生成执行计划。

.optimizer_use_sql_plan_baselines:启用与禁用存储在SQL Management Base中的SQL执行计划基线。

与stored outline迁移相关的视图
.dba_outlines:描述数据库中的所有stored outline。migrated列对于outline迁移很重要并且它的值为not-migrated与migrated。当为migrated时,stored outline已经迁移为执行计划基线并且不能再使用。

.dba_sql_plan_baselines:显示为特定SQL语句当前所创建的SQL执行计划基线。origin列指示执行计划基线是怎么创建的。当值为stored-outline时指示执行计划基线是通过迁移outline而创建的。

stored outline迁移的基本操作:
1.stored outline迁移的准备操作:
检查迁移条件并且决定要迁移的执行计划基线的行为

2.选择以下操作之一:
.使用SQL执行计划管理功能来迁移outline
.当完全保留stored outline行为时迁移outline为执行计划基线

3.执行迁移后的确认与清理

stored outline迁移的准备操作
1.使用SQL*Plus以sysdba权限或有dbms_spm执行权限的用户登录数据库

[oracle@db1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sat Oct 29 20:55:52 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

2.查询数据库中的stored outline

SQL> select name,category,sql_text from dba_outlines where migrated='NOT-MIGRATED';

no rows selected

3.决定那个stored outline满足迁移条件:
.语句必须不是insert as select语句
.语句必须没有引用远程对象
.语句必须没有私有stored outline

4.决定是否迁移所有outline,特定stored outline或者属于某个特定outline目录中的outlines。如果不打算迁移所有outline,那么就要列出你要迁移的outline或outline目录。

5.决定stored outline被迁移成SQL执行计划基线时是使用固定执行计划还是非固定执行计划:
.固定执行计划
一个固定执行计划是冻结的。如果一个固定执行计划使用执行计划基线中的hint来重新生成,那么优化器总是选择成本最低的固定执行计划而不是那些不固定的执行计划基线。本质上,一个固定的执行计划基线实际是使用有效hint的stored outline。当数据库基于执行计划基线中的hint并且使用执行计划基线中相同plan hash值来创建执行计划时,这个固定执行计划就能被重新生成。如果多个hint中的一个变为无效,那么数据库不可能使用相同的plan hash值来创建执行计划。在这种情装饰品下,这种执行不可重新生成。当使用hint解析时,如果一个固定的执行计划不能重新生成,那么优化器将选择不同的执行计划,它可能是:
.SQL执行计划基线中的另一个执行计划。
.通过优化器重新生成执行计划

在有些情况下,因为不同的执行计划会出现性能差异,这时就需要进行SQL优化。

.非固定执行计划
如果执行计划基线没有包含固定的执行计划,那么SQL Plan Management将考虑为SQL语句选择一个等价的执行计划。

6.在开始真下迁移之前,确保Oracle满足以下条件:
.数据库必须是企业版本
.数据库必须是open且不能是暂停状态
.数据库必须不能是限制模式来访问,只读或迁移模式

使用SQL Plan Management来迁移outline
这个任务的目标是:
.为了允许SQL Plan Management来从执行计划基线中为一个SQL语句选择所有执行计划来代替在迁移后应用相同的固定执行计划。

.为了允许SQL执行计划基线通过向基线中增加新的执行计划来面对数据库的改变

下面的例子假设以下条件成立:
.迁移所有outline

.想要执行计划基线的模块名与被迁移的outline目录名相同

.不想SQL执行计划被固定
缺省情况下,生成的执行计划是不固定的并且SQL Plan Management当为SQL语句选择执行计划时会考虑所有等价的执行计划。这个方法允许先进的执行计划演进可以为SQL语句捕获新的执行计划,验证它们的性能,并接受新执行计划加入到执行计划基线。

执行dbms_spm.migrate_stored_outline来迁移stored outline
下面的例子执行dbms_spm.migrate_stored_outline来迁移所有stored outline为固定的执行计划基线:

declare
 my_report CLOB;
begin
 my_report:=dbms_spm.migrate_stored_outline(attribute_name=>'all');
end;

迁移outline并保留stored outline行为
这个任务的目标是为了把stored outline迁移成SQL执行计划基线并且通过创建固定执行计划基线来保留stored outline行为。一个固定执行计划比其它执行计划的优先级高。如果执行计划被固定,那么执行计划基线不能被演进。数据库不会向包含固定执行计划的基线增加新的执行计划。

这种情况适用于以下场景:
.想要迁移目录名为firstrow中的stored outline
.想执行计划基线的模块名与被迁移的outline目录名相同

将stored outline迁移为执行计划基线:
1.

declare
 my_report CLOB;
begin
 my_outlines:=dbms_spm.migrate_stored_outline

(attribute_name=>'category',attribute_value=>'firstrow',fixed=>'YES');
end;
/

在完成迁移操作之后,SQL执行计划基线的模块名为firstrow,目录名为default。

执行迁移后的确认与清理
这个任务的目标是:
.为了配置数据库使用执行计划基线来代替使用那些已经被迁移为SQL执行计划基线的stored outline
.为了将来执行的SQL语句创建SQL执行计划基线来代替使用stored outline
.为了删除那些已经迁移为SQL执行计划基线的stored outline

这个任务适用于以下场景:
.已经完成了stored outline迁移的基本步骤
.一些stored outline可能是在oracle 10g之前被创建

下面说明optimizer_capture_sql_plan_baselines与create_stored_outlines参数的组合是如何决定数据库创建stored outline与SQL执行计划基线的:
create_stored_outlines为false,optimizer_capture_sql_plan_baselines为false时,当执行SQL语句时,数据库不会创建stored outline或SQL执行计划基线。

create_stored_outlines为false,optimizer_capture_sql_plan_baselines为true时,数据库会自动识别重复的SQL语句并为这些语句生成SQL执行计划基线。当执行SQL语句时,如果不存在SQL执行计划基线就会使用default目录名来生成新的SQL执行计划基线。

create_stored_outlines为true,optimizer_capture_sql_plan_baselines为false时,Oracle数据库会自动创建与存储outline。当执行SQL语句时,如果不存在outline,就会使用目录名default为SQL语句创建outline。

create_stored_outlines为category,optimizer_capture_sql_plan_baselines为false时,当执行SQL语句时,如果不存在outline,就会使用指定的目录名为SQL语句创建新的stored outlines。

create_stored_outlines为true,optimizer_capture_sql_plan_baselines为true时,Oracle数据库会自动为执行的每个查询语句创建与存储outline。自动识别重复SQL语句与为这些语句生成SQL执行计划基线。当执行SQL语句时,数据库会使用目录名default来创建stored outline与SQL执行计划基线。

create_stored_outlines为category,optimizer_capture_sql_plan_baselines为true,Oracle数据库会自动为执行的每个查询语句创建outline。自动识别重复SQL语句与为这些语句生成SQL执行计划基线。当执行SQL语句时,数据库会使用指定的目录名创建stored outline并使用目录名default来创建SQL执行计划基线。

下面说明optimizer_use_sql_plan_baselines与use_stored_outlines参数的组合是如何决定数据库使用stored outline与SQL执行计划基线的:

use_stored_outlines为false,optimizer_use_sql_plan_baselines为false时,当为SQL语句选择执行计划时,数据库不会使用stored outline或执行计划基线。

use_stored_outlines为false,optimizer_use_sql_plan_baselines为true时,当为SQL语句选择执行计划时,数据库只会使用SQL执行计划基线。

use_stored_outlines为true,optimizer_use_sql_plan_baselines为false时,当为SQL语句选择执行计划时,数据库会使用目录名为default中的stored outline。

use_stored_outlines为category,optimizer_use_sql_plan_baselines为false时,当为SQL语句选择执行计划时,数据库会使用指定目录名中的stored outline。如果指定的目录名中不存在stored outline,如果在default目录中存在stored outline,那么数据库就会使用。

use_stored_outlines为true,optimizer_use_sql_plan_baselines为true时,当为SQL语句选择执行计划时,stored outline的优先级比SQL执行计划基线高。如果在default目录中存在可以用于SQL语句的stored outline,那么数据库会使用stored outline,否则数据库使用SQL执行计划基线。

use_stored_outlines为category,optimizer_use_sql_plan_baselines为true时,当为SQL语句选择执行计划时,stored outline的优先级比SQL执行计划基线高。如果指定目录或default目录中存在可以用于SQL语句的stored outline,那么数据库会使用stored outline。否则,数据库会使用SQL执行计划基线。然而,如果stored outline有migrated属性,那么数据库不会使用outline,如果存在SQL执行计划基线,那么数据库会使用SQL执行计划基线。

在完成stored outline迁移后将数据库置于合适的状态:
1.检查迁移结果,看SQL执行计划基线是否已经创建,确保执行计划被启用与接受:

SQL> select sql_handle,plan_name,origin,enabled,accepted,fixed,module from dba_sql_plan_baselines;
SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENABLED ACCEPTED FIXED MODULE
------------------------------ ------------------------------ -------------- ------- -------- ----- --------------------------------
SQL_d0cb53f0573bcb74           SQL_PLAN_d1kumy1bmrkvnae69e7ae AUTO-CAPTURE   YES     YES      NO    emagent_SQL_oracle_database
SQL_9c0d7998b1d28680           SQL_PLAN_9s3btm2sx51n074830d3a AUTO-CAPTURE   YES     YES      NO    PL/SQL Developer
SQL_91430157076ba9df           SQL_PLAN_92hs1aw3qrafzb96d21b9 AUTO-CAPTURE   YES     YES      NO    JDBC Thin Client
SQL_fbd80d3a7daa592f           SQL_PLAN_grq0d79yunq9g3517892f AUTO-CAPTURE   YES     YES      NO    emagent_SQL_rac_database
SQL_c9327c795e035d87           SQL_PLAN_ckcmwg5g06rc70298c760 AUTO-CAPTURE   YES     YES      NO    emagent_SQL_rac_database
SQL_773b254f11d733b0           SQL_PLAN_7fft59w8xfcxh7d2358ba AUTO-CAPTURE   YES     YES      NO    emagent_SQL_rac_database
SQL_952e0dafe13297d3           SQL_PLAN_9abhdpzhm55ymff175d6b AUTO-CAPTURE   YES     YES      NO    emagent_SQL_rac_database
SQL_5bea1aec47de5c1d           SQL_PLAN_5ruhuxj3xwr0x3517892f AUTO-CAPTURE   YES     YES      NO    emagent_SQL_rac_database
SQL_11489cc82e27c733           SQL_PLAN_12k4wt0r2gjtmf1c17b40 AUTO-CAPTURE   YES     YES      NO    emagent_SQL_rac_database
SQL_da9311fb2fec8c40           SQL_PLAN_dp4sjzcryt320849be660 AUTO-CAPTURE   YES     YES      NO    emagent_SQL_rac_database
SQL_2097b7cb694841d0           SQL_PLAN_215xrtdnnhhfh35e87e58 AUTO-CAPTURE   YES     YES      NO    emagent_SQL_rac_database
SQL_df059c6821f89598           SQL_PLAN_dy1cwd0hzj5cs35e87e58 AUTO-CAPTURE   YES     YES      NO    emagent_SQL_rac_database
SQL_94a4564ac1318120           SQL_PLAN_9992q9b0m30902f8b24ae AUTO-CAPTURE   YES     YES      NO    
SQL_fc093754bbae13fe           SQL_PLAN_gs29rakxuw4zy37db554f AUTO-CAPTURE   YES     YES      NO    
SQL_2fd9b3dc9d848e02           SQL_PLAN_2zqdmvkfs93h25179cde9 AUTO-CAPTURE   YES     YES      NO    
SQL_73b82c249b7d0843           SQL_PLAN_77f1c4kdru223ebdc4e78 AUTO-CAPTURE   YES     YES      NO    
SQL_6184e8ed31386bf0           SQL_PLAN_63178xnsmhuzh561aa499 AUTO-CAPTURE   YES     YES      NO    
SQL_c42198d1d5f324f5           SQL_PLAN_c88csu7az697pebdc4e78 AUTO-CAPTURE   YES     YES      NO    
SQL_674112d2daaadf76           SQL_PLAN_6fh8kubdaprvq83c346df AUTO-CAPTURE   YES     YES      NO    
SQL_ffa1f1f91c5bca64           SQL_PLAN_gz8gjz4f5rkm4f59a06ad AUTO-CAPTURE   YES     YES      NO
SQL_af180c8ff9a1d861           SQL_PLAN_ay60cjzwu3q3182cd7aee AUTO-CAPTURE   YES     YES      NO    
SQL_3ba02daa5cc73416           SQL_PLAN_3r81dp9fcfd0q94b64494 AUTO-CAPTURE   YES     YES      NO    
SQL_9cc94d4239925ef4           SQL_PLAN_9tkad88wt4rrn5976b5eb AUTO-CAPTURE   YES     YES      NO    
SQL_1dc6cbd35acb4efd           SQL_PLAN_1vjqbuddcqmrx5d4b54d5 AUTO-CAPTURE   YES     YES      NO    
SQL_9ed410d70ee4f2fe           SQL_PLAN_9xp0huw7f9wry2f8b24ae AUTO-CAPTURE   YES     YES      NO    
SQL_f7a25d7938972912           SQL_PLAN_gg8kxg4w9fa8kebdc4e78 AUTO-CAPTURE   YES     YES      NO    SEVERITY EVALUATION
SQL_90cda4f1c4064ca9           SQL_PLAN_91md4y720cm5924d38443 AUTO-CAPTURE   YES     YES      NO    SEVERITY EVALUATION
SQL_c42ff7e665ca18ec           SQL_PLAN_c8bzrwtkwn67c55df0880 AUTO-CAPTURE   YES     YES      NO    SEVERITY EVALUATION
SQL_a56d8c52cbac8dc5           SQL_PLAN_aavccab5ut3f5a9b3d668 AUTO-CAPTURE   YES     YES      NO    SEVERITY EVALUATION
SQL_35a61a655e37564d           SQL_PLAN_3b9hucpg3fpkd5454b1ea AUTO-CAPTURE   YES     YES      NO    SEVERITY EVALUATION
SQL_ac3326a11f142cac           SQL_PLAN_asct6n4gj8b5c76def5aa AUTO-CAPTURE   YES     YES      NO    SEVERITY EVALUATION
SQL_581db5ae5093f1d2           SQL_PLAN_5h7dppt897wfk15aad75e AUTO-CAPTURE   YES     YES      NO    SEVERITY EVALUATION
SQL_2531d59ec295a26c           SQL_PLAN_2acfpmv19b8mc6943321d AUTO-CAPTURE   YES     YES      NO    SEVERITY EVALUATION
SQL_09070338bc78471e           SQL_PLAN_0k1s372y7hjsyebdc4e78 AUTO-CAPTURE   YES     YES      NO    SEVERITY EVALUATION
SQL_8bd89c2a8626630d           SQL_PLAN_8rq4w5a32cssdd7a28287 AUTO-CAPTURE   YES     YES      NO    SEVERITY EVALUATION
SQL_c99cfd0ca0ec6d27           SQL_PLAN_cm77x1khfsv97e0d1d869 AUTO-CAPTURE   YES     YES      NO    SEVERITY EVALUATION
SQL_1178b3f40ee9079d           SQL_PLAN_12y5myh7fk1wx7fa68824 AUTO-CAPTURE   YES     YES      NO    SEVERITY EVALUATION
SQL_30c826839bd031c7           SQL_PLAN_31k16hfdx0cf7ebdc4e78 AUTO-CAPTURE   YES     YES      NO    SEVERITY EVALUATION
SQL_75642813e87d55ba           SQL_PLAN_7at182gn7upduc9e4a408 AUTO-CAPTURE   YES     NO       NO    SEVERITY EVALUATION
SQL_75642813e87d55ba           SQL_PLAN_7at182gn7updu4d0fe611 AUTO-CAPTURE   YES     YES      NO    SEVERITY EVALUATION
SQL_0398f5dac9a26bd2           SQL_PLAN_0767pvb4u4uykaa9fb8f2 AUTO-CAPTURE   YES     YES      NO    SEVERITY EVALUATION
SQL_5bf2fdd320991dc8           SQL_PLAN_5rwrxuch9k7f815aad75e AUTO-CAPTURE   YES     YES      NO    SEVERITY EVALUATION
SQL_faff45acb48c010c           SQL_PLAN_gpzu5pku8s08c3b4f0583 AUTO-CAPTURE   YES     NO       NO    SEVERITY EVALUATION
SQL_faff45acb48c010c           SQL_PLAN_gpzu5pku8s08c0a771b57 AUTO-CAPTURE   YES     YES      NO    SEVERITY EVALUATION
SQL_0edbf315864797cd           SQL_PLAN_0xqzm2q34g5ydf06d473d AUTO-CAPTURE   YES     YES      NO    SEVERITY EVALUATION
SQL_6807bab99db0361a           SQL_PLAN_6h1xur6fv0dhu2e8a86b7 AUTO-CAPTURE   YES     YES      NO    PL/SQL Developer
SQL_1a45242d50349a41           SQL_PLAN_1nj945p8396k1c9e4a408 AUTO-CAPTURE   YES     NO       NO    SEVERITY EVALUATION
SQL_1a45242d50349a41           SQL_PLAN_1nj945p8396k14d0fe611 AUTO-CAPTURE   YES     YES      NO    SEVERITY EVALUATION
SQL_4c66704928a28228           SQL_PLAN_4stmh94na50j86943321d AUTO-CAPTURE   YES     YES      NO    SEVERITY EVALUATION
SQL_7757ffeb83333a7e           SQL_PLAN_7fpzzxf1m6fmy68d74995 AUTO-CAPTURE   YES     YES      NO    SEVERITY EVALUATION
SQL_17c90a44687b6622           SQL_PLAN_1gk8a8jn7qtj26943321d AUTO-CAPTURE   YES     YES      NO    SEVERITY EVALUATION
SQL_6d5efcdbb0af4493           SQL_PLAN_6urrwvfsayj4m4efadb75 AUTO-CAPTURE   YES     YES      NO    
SQL_2c0bedfc971b5441           SQL_PLAN_2s2zdzkbjqp212f8b24ae AUTO-CAPTURE   YES     YES      NO    OEM.BoundedPool
SQL_fc7e68bc886477c5           SQL_PLAN_gszm8rk468xy5f4b84801 AUTO-CAPTURE   YES     YES      NO    OEM.BoundedPool
SQL_9425c7639bc97782           SQL_PLAN_989f7cfdwkxw245768591 AUTO-CAPTURE   YES     YES      NO    OEM.BoundedPool
SQL_1205bbc63c6b2eeb           SQL_PLAN_141dvssy6qbrb47a21cb4 AUTO-CAPTURE   YES     YES      NO    OEM.BoundedPool
SQL_1367e948428a55f2           SQL_PLAN_16tz99118npgk7a54464c AUTO-CAPTURE   YES     YES      NO    emagent_SQL_oracle_database
SQL_a47e7f9f186b16f8           SQL_PLAN_a8zmzmwc6q5rs799d6e65 AUTO-CAPTURE   YES     YES      NO    emagent_SQL_oracle_database
SQL_03d675f2172c4dff           SQL_PLAN_07pmpy8bksmgz6d032274 AUTO-CAPTURE   YES     YES      NO    emagent_SQL_oracle_database
59 rows selected

2.可选操作,修改SQL执行计划基线的属性。例如,下面的语句将修改特定SQL语句的执行计划基线为fixed:

SQL> declare
  2   v_cnt PLS_INTEGER;
  3  begin
  4   v_cnt:=dbms_spm.alter_sql_plan_baseline(
  5                           sql_handle=>'SQL_9c0d7998b1d28680',
  6                           attribute_name=>'FIXED',
  7                           attribute_value=>'YES');
  8   dbms_output.put_line('Plans altered: '|| v_cnt);
  9  end;
 10  /
PL/SQL procedure successfully completed

3.检查原stored outline的状态:

select name,owner,category,used,migrated from dba_outlines;

4.删除那些已经被迁移到SQL执行计划基线中的所有stored outline,下面的语将用来删除dba_outlines中状态为migrated的所有stored outline:

declare
 v_cnt PLS_INTEGER;
begin
 v_cnt:=dbms_spm.drop_migrated_stored_outline();
 dbms_output.put_line('Migrated stored outlines dropped: '|| v_cnt);
end;

5.设置初始化参数:
.当执行SQL语句时,数据库创建SQL执行计划基线但不创建stored outline
.当不存在等价的SQL执行计划基线时,数据库只使用stored outline

下面的例子,当执行SQL语句时,指示数据库创建SQL执行计划基线来代替stored outline。并且指示

数据库当目录allrows或default中的没有被迁移到SQL执行计划基线中的stored outline。否则数据

库只使用SQL执行计划基线。
alter system set create_stored_outline = false;
alter system set optimizer_capture_sql_plan_baselines = true;
alter system set optimizer_use_sql_plan_baselines = true;
alter session set use_stored_outlines = allrows;

Oracle in子句过多的硬编码引发的故障

某生产数据库,不能登录,数据库不能归,生产环境为IBM小机,oracle为单实例的10.2.0.4。登录到数据库服务器可以看到磁盘空间被占用完了,归档日志存储在yb_oradata目录下,Oracle软件安装在oracle目录,用户不能登录是因为不能写审计日志,业务不能办理是因为归档目录没有空间,这些是问题的症状。,一般oracle所占的大小不可能达到上百G的大小。那就需要检查是什么文件占用了空间,生产环境为IBM小机。

[IBMP740-1:root:/]#df -g
Filesystem    GB blocks      Free %Used    Iused %Iused Mounted on
/dev/hd4           8.00      4.03   50%    11099     2% /
/dev/hd2           6.00      3.06   49%    57334     8% /usr
/dev/hd9var       16.00      6.87   58%    11305     1% /var
/dev/hd3          10.00      8.38   17%     2008     1% /tmp
/dev/fwdump        1.00      1.00    1%        5     1% /var/adm/ras/platform
/dev/hd1           2.00      2.00    1%        5     1% /home
/dev/hd11admin     10.00     10.00    1%        5     1% /admin
/proc                 -         -    -         -     -  /proc
/dev/hd10opt       2.00      1.65   18%    11518     3% /opt
/dev/livedump      2.00      2.00    1%        4     1% /var/adm/ras/livedump
/dev/oracle_lv    100.00      0.00  100%    66389    84% /oracle
/dev/bak_lv      999.00    520.64   48%       29     1% /bak
/dev/yboradata_lv   1399.00    0    100%      337     1% /yb_oradata

可以看到admin目录占用了79.16G

[IBMP740-1:root:/oracle]#du -sg *
0.00    Mail
79.16   admin
0.00    chapter10_01.sql
0.04    flash_recovery_area
3.06    inst
0.00    jd.log
0.00    lost+found
0.00    oraInventory
8.77    product
0.00    smit.log
0.00    smit.script
0.00    smit.transaction
0.00    sosi.txt
0.00    spcusr.lis
0.00    sqlhc
0.00    sqlnet.log
0.04    sqlt
0.00    sqlt.zip
0.01    sqlt_s10819
0.00    sqlt_s34882_log.zip
0.00    sqlt_s34883_log.zip
0.00    sqlt_s34884_xecute.zip
0.00    sqlt_s34885_xecute.zip
0.00    sqlt_s34886_xecute.zip
0.00    sqlt_s34887_xecute.zip
0.00    test_high_version.txt

可以看到RLZY目录占用了79.16

[IBMP740-1:root:/oracle/admin]#du -sg *
0.00    CAIWU
0.00    ORCL
79.16   RLZY
0.00    chdyl

可以看到cdump与udump目录分别占用了40.94G,38.22G

[IBMP740-1:root:/oracle/admin/RLZY]#du -sg *
0.05    adump
1.66    bdump
40.94   cdump
0.00    dpdump
0.00    pfile
0.00    scripts
38.22   udump

adump :审计信息
bdump :后台进程trace 和alert log ,就是说 alert_sid.log也存在这个目录中
cdump :一般放置一些核心的trace文件,内核实例缓冲区产生的跟踪文件,除非数据库出了问题 否则基本上不会有什么信息
dpdump:是存放一些登录信息的
pfile :初始化参数文件 initSID
udump :一般放置sql trace之后session的trace文件,用户服务器进程产生的跟踪文件,常见的是sql问题

那么我们先查看一下alert_RZLY.log

[IBMP740-1:root:/oracle/admin/RLZY]#tail -f /oracle/admin/RLZY/bdump/alert_RLZY.log
ORA-07445: exception encountered: core dump [qcsAnalyzeBooleanExpr+0010] [SIGSEGV] [Address not mapped to object] [0xFFFFFFFFDFFFFF0] [] []
Wed Oct 26 19:22:35 2016
Thread 1 advanced to log sequence 47133 (LGWR switch)
  Current log# 2 seq# 47133 mem# 0: /yb_oradata/RLZY/RLZY/redo02.log
Wed Oct 26 19:32:51 2016
Errors in file /oracle/admin/RLZY/udump/rlzy_ora_43647120.trc:
ORA-07445: exception encountered: core dump [qcsAnalyzeBooleanExpr+0010] [SIGSEGV] [Address not mapped to object] [0xFFFFFFFFDFFFFF0] [] []
Wed Oct 26 19:42:40 2016
Errors in file /oracle/admin/RLZY/udump/rlzy_ora_13697930.trc:
ORA-07445: exception encountered: core dump [qcsAnalyzeBooleanExpr+0010] [SIGSEGV] [Address not mapped to object] [0xFFFFFFFFDFFFFF0] [] []

可以看到出现了ORA-07445: exception encountered: core dump [qcsAnalyzeBooleanExpr+0010] [SIGSEGV] [Address not mapped to object] [0xFFFFFFFFDFFFFF0] [] []错误,具体的错误信息记录到/oracle/admin/RLZY/udump/rlzy_ora_13697930.trc文件中。如果查看该文件可以看到:

/oracle/admin/RLZY/udump/rlzy_ora_13697930.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /oracle/product/10.2.0/db_1
System name:	AIX
Node name:	IBMP740-1
Release:	1
Version:	6
Machine:	00F7AC3D4C00
Instance name: RLZY
Redo thread mounted by this instance: 1
Oracle process number: 73
Unix process pid: 13697930, image: oracle@IBMP740-1

*** 2016-10-26 19:42:40.925
*** ACTION NAME:() 2016-10-26 19:42:40.915
*** MODULE NAME:(JDBC Thin Client) 2016-10-26 19:42:40.915
*** SERVICE NAME:(SYS$USERS) 2016-10-26 19:42:40.915
*** SESSION ID:(1249.559) 2016-10-26 19:42:40.915
Exception signal: 11 (SIGSEGV), code: 50 (Address not mapped to object), addr: 0xffffffffdfffff0, PC: [0x100be5d90, qcsAnalyzeBooleanExpr+0010]
Registers:
iar: 0000000100be5d90, msr: a00000000000d032
 lr: 0000000100be5dd8,  cr: 0000000048222020
r00: 0000000100be5dd8, r01: 0ffffffffe000080, r02: 000000011022a6e8,
r03: 0ffffffffffee1a0, r04: 0000000110195798, r05: 00000001173f9a58,
r06: 0000000000000001, r07: 0ffffffffffee1a0, r08: 0000000110457778,
r09: 000000000000003f, r10: 0000000000000000, r11: 0000000000000000,
r12: 00000001008c5ce0, r13: 0000000110275b80, r14: 070000059a4fae08,
r15: 07000004dd304d00, r16: 0000000104d83ea8, r17: 07000004ada5ddd8,
r18: 000000000000000a, r19: 07000004dd304c08, r20: 0000000110000a70,
r21: 00000000000000d3, r22: 07000004dd3050e8, r23: 000003a1ecbd50bb,
r24: 0000000000000001, r25: 07000004ab1466f8, r26: 0ffffffffffef590,
r27: 0000000104fbef18, r28: 00000001104554a0, r29: 07000004ab1464f0,
r30: 0000000119360ff8, r31: 00000001173f9a58,
*** 2016-10-26 19:42:40.925
ksedmp: internal or fatal error
ORA-07445: exception encountered: core dump [qcsAnalyzeBooleanExpr+0010] [SIGSEGV] [Address not mapped to object] [0xFFFFFFFFDFFFFF0] [] []
Current SQL statement for this session:
update lv_handle_mdiindiacc_detail ld set ld.push_flag= '1'  where    (  ld.indipayser  in  (485506300,485504469,481520527,481520481,
481203712,484953649,484953901,481203462,481382692,481382673,485061110,485060889,481631327,481628371,481714509,481714502,485422790,
485422743,485505625,485504046,485505138,485422775,483924474,485422767,481537563,481537508,485422795,485422751,485710571,482042800,
482042766,485710537,485233344,485233182,485233274,481470443,481469597,485422785,485422737,481352719,481352131,485422780,485422731,
486517983,483676463,483677515,486516952,486517971,486516920,483676426,483677504,486517984,483676467,483677516,486516956,485834372,
485834335,486517621,483677148,486515894,483675382,482155166,482155007,485341981,485341686,485148741,485148592,485233418,485233219,
485691049,485690926,482024499,482024622,481430441,481429048,485422809,485422738,485003015,485002902,481365287,485108845,485110024,
481364104,481576480,481575936,485345250,481593493,481593394,485345150,481293132,481292888,481870872,481870754,481631719,481628925,
481631720,481628926,484386401,484325919,484279761,481364990,481364170,485108911,485109731,481365343,481364180,485110080,485108921,
485461600,485461588,486518688,483678244,486516141,483675633,481203710,484953644,481203457,484953899,481859623,481858668,485301624,
485301274,485178242,485178159,485341976,485341674,485114334,485114120,481667238,481667062,481896047,485566958,485566922,481896012,
485492025,485491374,481383898,481383891,485525133,485524916,485823685,485823675,482155703,482155693,481919222,481918827,485506045,
485504050,481919226,481918831,481314218,481314176,481372925,481372665,485118141,485117847,482155169,482155010,479090023,479089896,
486519248,483676465,483678822,486516954,481951298,481951203,481342632,481341537,485422816,485422759,485414857,485414807,485114339,
485114125,485114340,485114126,485559817,485559793,481590100,481589780,485038170,485038149,483345446,483345323,481898367,481898066,
485133366,485131992,481514080,481514019,485546660,481868409,481868588,485546483,481631724,481628931,481631725,481628932,485801708,
485801650,486519198,483678762,483676384,486516881,481795700,481795657,481511439,481511400,481954867,481954816,481750064,481750061,
481264623,481264439,483444426,483443970,485121840,485122295,483444424,485121838,485122293,483443968,485801199,485801114,482124023,
482123938,481758852,485465193,485465374,481758679,484985855,481234905,484417829,484349508,484301809,481630369,484369496,484270030,
484316209,484422263,484354647,484306241,485505104,484424634,484359856,484308602,484369483,484316196,484270017,484380924,484277629,
484323787,484394588,484331396,484285322,484443543,484409895,484339940,484293866,484443489,484384989,484324507,484278349,484387781,
484327215,484281141,484422937,484306905,484356645,484377357,484322251,484276093,485365130,481610521,484419539,484303519,484351460,
482097063,485776434,484408090,484338888,484292814,484377349,484322243,484276085,484411456,484295427,484341501,484444428,485491523,
484363227,484309940,484263761,484408140,484292864,484338938,484363949,484264483,484310662,484387017,484326451,484280377,484391296,
484283118,484329192,484363284,484263818,484309997,484363963,484264497,484310676,484391310,484283132,484329206,484419515,484303495,
484351436,485491585,485107518,484372437,484319150,484272992,484364435,484311148,484264969,484363383,484263917,484310096,484372838,
484319551,484273393,484419460,484303440,484351381,484364855,484265389,484311568,484362965,484309678,484263499,484387603,484327037,
484280963,484363999,484264533,484310712,484393931,484284665,484330739,484419551,484303531,484351472,484415488,484299468,484346502,
484387757,484327191,484281117,484375344,484321766,484275608,484394990,484285418,484331492,484413841,484343964,484297806,485505354,
484385059,484278419,484324577,484399421,484287836,484333910,484371169,484271724,484443706,484317882,484419549,484351470,484303529,
485420565,481670219,484414622,484298587,484344785,484364069,484310782,484264603,484415790,484299770,484346804,484392213,484283511,
484329585,484413882,484297847,484344005,484372903,484273458,484319616,484391273,484329169,484283095,484408100,484292824,484338898,
484414128,484298093,484344251,485505040,481808388,485487553,485001985,484364106,484310819,484264640,484411657,484341702,484295628,
484444228,484367730,484314443,484268264,484403066,484289851,484335925,484419597,484351518,484303577,484372506,484319219,484273061,
484422684,484356396,484306656,484422685,484356397,484306657,484419642,484351563,484303622,484419468,484303448,484351389,484364631,
484311344,484265165,481499866,484368214,484314927,484268748,484395063,484331565,484285491,484371188,484443905,484317901,484271743,
484413836,484343959,484297801,484364176,484264710,484310889,484362932,484263466,484309645,484363103,484309816,484263637,484423596,
484307564,484358673,485806313,485806320,484416902,484348176,484300882,484364579,484311292,484265113,484410263,484294234,484340308,
484363437,484310150,484263971,484363289,484263823,484310002,484409819,484339864,484293790,484408046,484338844,484292770,484422080,
484354466,484306060)
...省略500多个 ld.indipayser in子句...
 or   ld.indipayser   in  (483435121,485491921,485491229,481690203,481689916,481520856,481520755,481898439,481898142,481430459,
 481429068,485829180,485828858,483585375,483583501,481898271,481897956,481898395,481898095,481429876,481428052,481430365,481428936,
 485622870,485622845,481953922,481953897,481342272,481341014,481342685,481341591,484412861,484296823,484342897,482228507,482226295,
 482059057,485829120,485828783,484410980,484294951,484341025,485341942,485341626,482104613,482103959,476024026,476023392,479354610,
 479355244,485506208,485504331,485506449,485504649,481630446,482161656,482161030,485414852,485414802,481407018,485139969,485140061,
 481406926,481292994,481292723,481595191,475647095,478940615,481595203,475647108,478940628,481430099,481428372,485506248,485504393,
 483585619,483583745,482202578,482201079,482013649,482013215,485133328,485131954,485524985,485524754,483585630,483583756,485008802,
 485008781,481267747,481267768,485546704,481868233,481868633,485546310,481406992,481406892,485140035,485139935,481599032,481598665,
 481406973,485139916,485140016,481406873,481459790,481459525,481815563,481815423,481459671,481459394,481742591,481742553,481363369,
 481363213,483584338,483582447,481271582,481271283,485012358,485012661,481430472,481429081,485432563,481703015,481702793,485432341,
 484938031,484937972,481576545,481576019,485104415,485104364,485506476,485504681,481659769,481659645,485506511,485504722,485837341,
 485837308,481281837,481281135,485519385,485520085,484914974,484913321,484915019,484913409,475697671,475697595,472375125,467064510,
 460778878,459403120,455152767,451292754,437811847,437811923,440579233,440579309,443323954,443324029,446196115,446196190,451292679,
 408059771,412145899,412146020,416223862,416223945,419135025,419135109,421090466,421090550,424835994,424836077,428415307,428415384,
 432137615,432137692,434943813,434943889,382221955,382222077,385744164,385744286,388759711,388759833,392484675,392484797,396668805,
 396668889,399208174,399208296,401551264,401551386,405030115,405030236,408059650,455152690,459403043,460778801,467064434,469745874,
 469745950,472375049,481823330,481823309,485061440,485060870,481293007,481292736,481791012,481790993,485055047,485054905,481264879,
 481264601,485182951,485181901,482161443,482160773,484915392,484913132,481459595,481459310,485061296,485060685,481632400,481629790,
 484367836,484268370,484314549,481965421,481964565,481333736,481333553,482062253,482055203,481342270,481341012,483444108,485121523,
 483443652,485121978,481282606,485519758,481281510,485520852,482228644,482225455,481742602,481742564,485753875,485750411,483438857,
 483435383,481386674,481386604,481459763,481459498,481238883,481238806,483585061,483583186,485465383,485465204,481459807,481459542,
 485820222,482151739,485820213,482151748,481506957,481506888,485829083,485828735,481553536,481553461,485290131,485290208,480813211,
 480811525,480809839,480802533,480803095,480804219,480804781,480805905,480806467,480807591,480808153,480809277,481195925,481196477,
 480800847,480801409,480810963,480812649,475662583,475662548,472347913,472347948,469363482,469363447,481807284,481807269,479116742,
 479116726,481157590,481157509,478569302,478569415,485505891,485503650,485797256,485797164,481925900,484370496,484271051,484317209,
 475662579,475662559,472347924,469363458,469363478,472347944,485366698,485366602,481459761,481459496,485829059,485828711,482104644,
 482104015,479355288,479354666,476023448,476024070,485061553,485061004,485061195,485060567,486518406,483677963,483675157,486515666,
 481342717,481012977,481012976,481341623,481215908,478529522,485628896,481960727,485432467,485432171,481702919,481702623,485752755,
 483437727,483434118,485749152,483345478,483345358,483345510,483345391,483345528,483345410,484400959,484334893,484288819,485506343,
 485504525,481742595,481742557,484938040,484937944,485071415,485071319,481859721,481858934,485567428,485567290,483585457,483583583,
 485133565,485132233,482161564,482160926,482104558,476024093,476023480,479354698,482103861,479355311,485829141,485828810,485190223,
 481453746,481453533,485190010,485368352,485368337,481613708,481613723,481632457,481629847,481407010,485140053,481406918,485139961,
 485829305,485828985,481856037,482097135,485776506,482096955,485776326,485831653,485831650,485492087,485491438,484945744,481408635,
 481407982,481408804,481408266,482085026,482084946,481383961,481383927,481207531,484958546,484958564,481207513,481342353,481341097,
 481815515,481815353,481519532,481519463,484945762,484945777,485824095,485824000,485116115,485116067,481370920,481370968,485829030,
 485828770,481632418,481629808,485829281,485828960,485829088,485828740,482202678,482201289,485432590,481702496,485432044,481703042,
 481753320,481753297,482104329,476023838,476023326,479354544,482104005,479355056,481467287,481467197,485506301,485504470,481430387,
 481428981,484915992,484914171,485492004,485491346,484398623,484333459,484287385,484413427,484297389,484343463,484390123,484282715,
 484328789,481365566,485110303,485109362,481364625,481576451,481575901,482017867,482017518)  )

最终这个update子句使用500多个ld.indipayser in子句,这些in子句之间使用or进行连接,等于in子句最终的参数个数有40多万个。这就是udump目录中占用38.22G空间中消耗了34G空间的跟踪文件内容。

下面来查看cdump目录中的

[IBMP740-1:root:/oracle/admin/RLZY/cdump]#ls -lrt
...省略了大多数内容...
drwxr-x---    2 oracle   dba             256 Oct 26 14:29 core_28377398
drwxr-x---    2 oracle   dba             256 Oct 26 14:52 core_6685364
drwxr-x---    2 oracle   dba             256 Oct 26 15:12 core_63635536
drwxr-x---    2 oracle   dba             256 Oct 26 15:35 core_17301998
drwxr-x---    2 oracle   dba             256 Oct 26 16:04 core_47317412
drwxr-x---    2 oracle   dba             256 Oct 26 16:29 core_7144314
drwxr-x---    2 oracle   dba             256 Oct 26 16:54 core_33030282
drwxr-x---    2 oracle   dba             256 Oct 26 17:29 core_27918436
drwxr-x---    2 oracle   dba             256 Oct 26 17:39 core_12321324
drwxr-x---    2 oracle   dba             256 Oct 26 17:49 core_45089220
drwxr-x---    2 oracle   dba             256 Oct 26 18:15 core_12517416
drwxr-x---    2 oracle   dba             256 Oct 26 18:35 core_3539788
drwxr-x---    2 oracle   dba             256 Oct 26 19:00 core_14549222
drwxr-x---    2 oracle   dba             256 Oct 26 19:26 core_60817918
drwxr-x---    2 oracle   dba             256 Oct 26 19:42 core_43647120
drwxr-x---    2 oracle   dba             256 Oct 26 19:52 core_13697930

查看最近生成的core_13697930目录中生成的core文件可以看到与/oracle/admin/RLZY/udump/rlzy_ora_13697930.trc文件中一样的SQL语句:

update lv_handle_mdiindiacc_detail ld set ld.push_flag= '1'  where    (  ld.indipayser  in  (485506300,485504469,481520527,481520481,
481203712,484953649,484953901,481203462,481382692,481382673,485061110,485060889,481631327,481628371,481714509,481714502,485422790,
485422743,485505625,485504046,485505138,485422775,483924474,485422767,481537563,481537508,485422795,485422751,485710571,482042800,
482042766,485710537,485233344,485233182,485233274,481470443,481469597,485422785,485422737,481352719,481352131,485422780,485422731,
486517983,483676463,483677515,486516952,486517971,486516920,483676426,483677504,486517984,483676467,483677516,486516956,485834372,
485834335,486517621,483677148,486515894,483675382,482155166,482155007,485341981,485341686,485148741,485148592,485233418,485233219,
485691049,485690926,482024499,482024622,481430441,481429048,485422809,485422738,485003015,485002902,481365287,485108845,485110024,
481364104,481576480,481575936,485345250,481593493,481593394,485345150,481293132,481292888,481870872,481870754,481631719,481628925,
481631720,481628926,484386401,484325919,484279761,481364990,481364170,485108911,485109731,481365343,481364180,485110080,485108921,
485461600,485461588,486518688,483678244,486516141,483675633,481203710,484953644,481203457,484953899,481859623,481858668,485301624,
485301274,485178242,485178159,485341976,485341674,485114334,485114120,481667238,481667062,481896047,485566958,485566922,481896012,
485492025,485491374,481383898,481383891,485525133,485524916,485823685,485823675,482155703,482155693,481919222,481918827,485506045,
485504050,481919226,481918831,481314218,481314176,481372925,481372665,485118141,485117847,482155169,482155010,479090023,479089896,
486519248,483676465,483678822,486516954,481951298,481951203,481342632,481341537,485422816,485422759,485414857,485414807,485114339,
485114125,485114340,485114126,485559817,485559793,481590100,481589780,485038170,485038149,483345446,483345323,481898367,481898066,
485133366,485131992,481514080,481514019,485546660,481868409,481868588,485546483,481631724,481628931,481631725,481628932,485801708,
485801650,486519198,483678762,483676384,486516881,481795700,481795657,481511439,481511400,481954867,481954816,481750064,481750061,
481264623,481264439,483444426,483443970,485121840,485122295,483444424,485121838,485122293,483443968,485801199,485801114,482124023,
482123938,481758852,485465193,485465374,481758679,484985855,481234905,484417829,484349508,484301809,481630369,484369496,484270030,
484316209,484422263,484354647,484306241,485505104,484424634,484359856,484308602,484369483,484316196,484270017,484380924,484277629,
484323787,484394588,484331396,484285322,484443543,484409895,484339940,484293866,484443489,484384989,484324507,484278349,484387781,
484327215,484281141,484422937,484306905,484356645,484377357,484322251,484276093,485365130,481610521,484419539,484303519,484351460,
482097063,485776434,484408090,484338888,484292814,484377349,484322243,484276085,484411456,484295427,484341501,484444428,485491523,
484363227,484309940,484263761,484408140,484292864,484338938,484363949,484264483,484310662,484387017,484326451,484280377,484391296,
484283118,484329192,484363284,484263818,484309997,484363963,484264497,484310676,484391310,484283132,484329206,484419515,484303495,
484351436,485491585,485107518,484372437,484319150,484272992,484364435,484311148,484264969,484363383,484263917,484310096,484372838,
484319551,484273393,484419460,484303440,484351381,484364855,484265389,484311568,484362965,484309678,484263499,484387603,484327037,
484280963,484363999,484264533,484310712,484393931,484284665,484330739,484419551,484303531,484351472,484415488,484299468,484346502,
484387757,484327191,484281117,484375344,484321766,484275608,484394990,484285418,484331492,484413841,484343964,484297806,485505354,
484385059,484278419,484324577,484399421,484287836,484333910,484371169,484271724,484443706,484317882,484419549,484351470,484303529,
485420565,481670219,484414622,484298587,484344785,484364069,484310782,484264603,484415790,484299770,484346804,484392213,484283511,
484329585,484413882,484297847,484344005,484372903,484273458,484319616,484391273,484329169,484283095,484408100,484292824,484338898,
484414128,484298093,484344251,485505040,481808388,485487553,485001985,484364106,484310819,484264640,484411657,484341702,484295628,
484444228,484367730,484314443,484268264,484403066,484289851,484335925,484419597,484351518,484303577,484372506,484319219,484273061,
484422684,484356396,484306656,484422685,484356397,484306657,484419642,484351563,484303622,484419468,484303448,484351389,484364631,
484311344,484265165,481499866,484368214,484314927,484268748,484395063,484331565,484285491,484371188,484443905,484317901,484271743,
484413836,484343959,484297801,484364176,484264710,484310889,484362932,484263466,484309645,484363103,484309816,484263637,484423596,
484307564,484358673,485806313,485806320,484416902,484348176,484300882,484364579,484311292,484265113,484410263,484294234,484340308,
484363437,484310150,484263971,484363289,484263823,484310002,484409819,484339864,484293790,484408046,484338844,484292770,484422080,
484354466,484306060)
...省略500多个 ld.indipayser in子句...
 or   ld.indipayser   in  (483435121,485491921,485491229,481690203,481689916,481520856,481520755,481898439,481898142,481430459,
 481429068,485829180,485828858,483585375,483583501,481898271,481897956,481898395,481898095,481429876,481428052,481430365,481428936,
 485622870,485622845,481953922,481953897,481342272,481341014,481342685,481341591,484412861,484296823,484342897,482228507,482226295,
 482059057,485829120,485828783,484410980,484294951,484341025,485341942,485341626,482104613,482103959,476024026,476023392,479354610,
 479355244,485506208,485504331,485506449,485504649,481630446,482161656,482161030,485414852,485414802,481407018,485139969,485140061,
 481406926,481292994,481292723,481595191,475647095,478940615,481595203,475647108,478940628,481430099,481428372,485506248,485504393,
 483585619,483583745,482202578,482201079,482013649,482013215,485133328,485131954,485524985,485524754,483585630,483583756,485008802,
 485008781,481267747,481267768,485546704,481868233,481868633,485546310,481406992,481406892,485140035,485139935,481599032,481598665,
 481406973,485139916,485140016,481406873,481459790,481459525,481815563,481815423,481459671,481459394,481742591,481742553,481363369,
 481363213,483584338,483582447,481271582,481271283,485012358,485012661,481430472,481429081,485432563,481703015,481702793,485432341,
 484938031,484937972,481576545,481576019,485104415,485104364,485506476,485504681,481659769,481659645,485506511,485504722,485837341,
 485837308,481281837,481281135,485519385,485520085,484914974,484913321,484915019,484913409,475697671,475697595,472375125,467064510,
 460778878,459403120,455152767,451292754,437811847,437811923,440579233,440579309,443323954,443324029,446196115,446196190,451292679,
 408059771,412145899,412146020,416223862,416223945,419135025,419135109,421090466,421090550,424835994,424836077,428415307,428415384,
 432137615,432137692,434943813,434943889,382221955,382222077,385744164,385744286,388759711,388759833,392484675,392484797,396668805,
 396668889,399208174,399208296,401551264,401551386,405030115,405030236,408059650,455152690,459403043,460778801,467064434,469745874,
 469745950,472375049,481823330,481823309,485061440,485060870,481293007,481292736,481791012,481790993,485055047,485054905,481264879,
 481264601,485182951,485181901,482161443,482160773,484915392,484913132,481459595,481459310,485061296,485060685,481632400,481629790,
 484367836,484268370,484314549,481965421,481964565,481333736,481333553,482062253,482055203,481342270,481341012,483444108,485121523,
 483443652,485121978,481282606,485519758,481281510,485520852,482228644,482225455,481742602,481742564,485753875,485750411,483438857,
 483435383,481386674,481386604,481459763,481459498,481238883,481238806,483585061,483583186,485465383,485465204,481459807,481459542,
 485820222,482151739,485820213,482151748,481506957,481506888,485829083,485828735,481553536,481553461,485290131,485290208,480813211,
 480811525,480809839,480802533,480803095,480804219,480804781,480805905,480806467,480807591,480808153,480809277,481195925,481196477,
 480800847,480801409,480810963,480812649,475662583,475662548,472347913,472347948,469363482,469363447,481807284,481807269,479116742,
 479116726,481157590,481157509,478569302,478569415,485505891,485503650,485797256,485797164,481925900,484370496,484271051,484317209,
 475662579,475662559,472347924,469363458,469363478,472347944,485366698,485366602,481459761,481459496,485829059,485828711,482104644,
 482104015,479355288,479354666,476023448,476024070,485061553,485061004,485061195,485060567,486518406,483677963,483675157,486515666,
 481342717,481012977,481012976,481341623,481215908,478529522,485628896,481960727,485432467,485432171,481702919,481702623,485752755,
 483437727,483434118,485749152,483345478,483345358,483345510,483345391,483345528,483345410,484400959,484334893,484288819,485506343,
 485504525,481742595,481742557,484938040,484937944,485071415,485071319,481859721,481858934,485567428,485567290,483585457,483583583,
 485133565,485132233,482161564,482160926,482104558,476024093,476023480,479354698,482103861,479355311,485829141,485828810,485190223,
 481453746,481453533,485190010,485368352,485368337,481613708,481613723,481632457,481629847,481407010,485140053,481406918,485139961,
 485829305,485828985,481856037,482097135,485776506,482096955,485776326,485831653,485831650,485492087,485491438,484945744,481408635,
 481407982,481408804,481408266,482085026,482084946,481383961,481383927,481207531,484958546,484958564,481207513,481342353,481341097,
 481815515,481815353,481519532,481519463,484945762,484945777,485824095,485824000,485116115,485116067,481370920,481370968,485829030,
 485828770,481632418,481629808,485829281,485828960,485829088,485828740,482202678,482201289,485432590,481702496,485432044,481703042,
 481753320,481753297,482104329,476023838,476023326,479354544,482104005,479355056,481467287,481467197,485506301,485504470,481430387,
 481428981,484915992,484914171,485492004,485491346,484398623,484333459,484287385,484413427,484297389,484343463,484390123,484282715,
 484328789,481365566,485110303,485109362,481364625,481576451,481575901,482017867,482017518)  )

所以应该是该语句中的in子句的硬编码值过多引起的,in子句硬编码值有40多万个。这样的语句解析将消耗的很多的shared_pool中的空间,从而导致系统异常。

处理方法:
应用改写语句,将in子句中的这些值存放到某个临时表中,将使用临时表与要被更新的表进行关联。

oracle跨版本与平台执行传输表空间

将aix(10.2.0.4)平台上的源数据库中的tspitr表空间传到linux(11.2.0.4)平台,并在源主机上使用目录/yb_oradata/transport来存储被转换的数据文件。操作步骤如下:
1.将要被传输的表空间tspitr设置为只读

SQL> alter tablespace tspitr read only;

Tablespace altered.

SQL> select tablespace_name,status from dba_tablespaces where tablespace_name='TSPITR';
TABLESPACE_NAME                STATUS
------------------------------ ---------
TSPITR                         READ ONLY

2.检查源平台与目标平台信息看是滞支持传输操作,数据库所支持的平台信息如下:

SQL> select platform_name,endian_format from v$transportable_platform;
PLATFORM_NAME                                                                    ENDIAN_FORMAT
-------------------------------------------------------------------------------- --------------
Solaris[tm] OE (32-bit)                                                          Big
Solaris[tm] OE (64-bit)                                                          Big
Microsoft Windows IA (32-bit)                                                    Little
Linux IA (32-bit)                                                                Little
AIX-Based Systems (64-bit)                                                       Big
HP-UX (64-bit)                                                                   Big
HP Tru64 UNIX                                                                    Little
HP-UX IA (64-bit)                                                                Big
Linux IA (64-bit)                                                                Little
HP Open VMS                                                                      Little
Microsoft Windows IA (64-bit)                                                    Little
IBM zSeries Based Linux                                                          Big
Linux x86 64-bit                                                                 Little
Apple Mac OS                                                                     Big
Microsoft Windows x86 64-bit                                                     Little
Solaris Operating System (x86)                                                   Little
IBM Power Based Linux                                                            Big
Solaris Operating System (x86-64)                                                Little
HP IA Open VMS                                                                   Little

源平台:

SQL> select d.platform_name,endian_format from v$transportable_platform tp,v$database d where tp.platform_name=d.platform_name;
PLATFORM_NAME                                                                    ENDIAN_FORMAT
-------------------------------------------------------------------------------- --------------
AIX-Based Systems (64-bit)                                                       Big

目标平台:

SQL> select d.platform_name,endian_format from v$transportable_platform tp,v$database d where tp.platform_name=d.platform_name;

PLATFORM_NAME                                                                                         ENDIAN_FORMAT
----------------------------------------------------------------------------------------------------- --------------
Linux x86 64-bit                                                                                      Little

3.确认要被传输的表空间是否是自包含表空间(TSPITR):

SQL> exec sys.dbms_tts.transport_set_check('TSPITR',true);

PL/SQL procedure successfully completed.

SQL> select * from sys.transport_set_violations;

no rows selected

没有记录,表示该表空间只包含表数据,可以传输。

4.记录表空间传输前表tspitr中的记录:

SQL> select count(*) from tspitr.tspitr;

  COUNT(*)
----------
     50315

5.使用RMAN将源数据库中的表空间tspitrt转换为目标平台字节序格式,使用format参数来控制被转换后数据文件的文件名和存储目录.

[IBMP740-1:oracle:/yb_oradata/transport]$export NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'
[IBMP740-1:oracle:/yb_oradata/transport]$rman target/

Recovery Manager: Release 10.2.0.4.0 - Production on Mon Oct 24 17:07:59 2016

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: RLZY (DBID=1589671076)

RMAN> convert tablespace "TSPITR" to platform 'Linux x86 64-bit'  format ='/yb_oradata/transport/%U';

Starting backup at 2016-10-24 17:09:31
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=1265 devtype=DISK
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00021 name=/yb_oradata/transport_after/TSPITR01.DBF
converted datafile=/yb_oradata/transport/data_D-RLZY_I-1589671076_TS-TSPITR_FNO-21_orrj67ic
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03
Finished backup at 2016-10-24 17:09:35

[IBMP740-1:oracle:/yb_oradata/transport]$ls -lrt
total 102416
-rw-r-----    1 oracle   dba        52436992 Oct 24 17:09 data_D-RLZY_I-1589671076_TS-TSPITR_FNO-21_orrj67ic

6.使用导出工具创建传输表空间元数据dump文件

SQL> create or replace directory test_dump as '/yb_oradata/transport';

Directory created.

SQL> grant read,write on directory test_dump to public;

Grant succeeded.

-rw-r-----    1 oracle   dba        52436992 Oct 24 17:09 data_D-RLZY_I-1589671076_TS-TSPITR_FNO-21_orrj67ic
[IBMP740-1:oracle:/yb_oradata/transport]$ expdp \'sys/admin_7817600@RLZY as sysdba\' directory=test_dump dumpfile=tspitr.dmp logfile=tspitr.log 

transport_tablespaces=TSPITR                                                       < Export: Release 10.2.0.4.0 - 64bit Production on Monday, 24 October, 2016 17:12:42

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01":  'sys/********@RLZY AS SYSDBA' directory=test_dump dumpfile=tspitr.dmp logfile=tspitr.log transport_tablespaces=TSPITR 
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TRANSPORTABLE_01 is:
  /yb_oradata/transport/tspitr.dmp
Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 17:13:41

导出除表之外的用户tspitr中的其它对象的元数据

[IBMP740-1:oracle:/yb_oradata/transport]$expdp tspitr/tspitr directory=test_dump dumpfile=tspitr_metadata_only.dmp logfile=tspitr_metadata_only.log content=metadata_only exclude=table                                         

Export: Release 10.2.0.4.0 - 64bit Production on Monday, 24 October, 2016 17:45:22

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "TSPITR"."SYS_EXPORT_SCHEMA_01":  tspitr/******** directory=test_dump dumpfile=tspitr_metadata_only.dmp logfile=tspitr_metadata_only.log content=metadata_only 

exclude=table 
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Master table "TSPITR"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TSPITR.SYS_EXPORT_SCHEMA_01 is:
  /yb_oradata/transport/tspitr_metadata_only.dmp
Job "TSPITR"."SYS_EXPORT_SCHEMA_01" successfully completed at 17:45:25

7.将转换后存储在/u02/transport目录中的数据文件与导出的元数据文件,传输到目标主机的目录/home/transport中

SQL> create or replace directory test_dump as '/home/transport';

Directory created.

SQL> grant read,write on directory test_dump to public;

Grant succeeded.


[oracle@sjjh transport]$ ftp 10.138.129.3
Connected to 10.138.129.3.
220 IBMP740-1 FTP server (Version 4.2 Mon Nov 28 14:12:02 CST 2011) ready.
502 authentication type cannot be set to GSSAPI
Name (10.138.129.3:oracle): oracle
331 Password required for oracle.
Password:
230-Last unsuccessful login: Mon Dec  1 16:07:13 BEIST 2014 on ftp from ::ffff:10.138.135.235
230-Last login: Mon Oct 24 14:51:18 BEIST 2016 on /dev/pts/0 from 10.138.133.203
230 User oracle logged in.
Remote system type is UNIX.
Using binary mode to transfer files.
ftp> cd /yb_oradata/transport
250 CWD command successful.
ftp> lcd /home/transport
Local directory now /home/transport
ftp> bin
200 Type set to I.
ftp> get data_D-RLZY_I-1589671076_TS-TSPITR_FNO-21_orrj67ic
local: data_D-RLZY_I-1589671076_TS-TSPITR_FNO-21_orrj67ic remote: data_D-RLZY_I-1589671076_TS-TSPITR_FNO-21_orrj67ic
227 Entering Passive Mode (10,138,129,3,215,220)
150 Opening data connection for data_D-RLZY_I-1589671076_TS-TSPITR_FNO-21_orrj67ic (52436992 bytes).
226 Transfer complete.
52436992 bytes received in 0.5 seconds (1e+05 Kbytes/s)
ftp> get tspitr.dmp
local: tspitr.dmp remote: tspitr.dmp
227 Entering Passive Mode (10,138,129,3,215,222)
150 Opening data connection for tspitr.dmp (86016 bytes).
226 Transfer complete.
86016 bytes received in 0.0028 seconds (3e+04 Kbytes/s)

ftp> get tspitr_metadata_only.dmp
local: tspitr_metadata_only.dmp remote: tspitr_metadata_only.dmp
227 Entering Passive Mode (10,138,129,3,217,16)
150 Opening data connection for tspitr_metadata_only.dmp (147456 bytes).
226 Transfer complete.
147456 bytes received in 0.0036 seconds (4e+04 Kbytes/s)

8.将要被传输的表空间附加到目标数据库中

SQL> create user tspitr identified by "tspitr";

User created.

SQL> grant dba,connect,resource to tspitr;

Grant succeeded.

SQL> select username,default_tablespace,temporary_tablespace from dba_users where username='TSPITR';
USERNAME                       DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
TSPITR                         USERS                          TEMP

[oracle@sjjh transport]$ impdp \'sys/xxzx7817600@SJJH as sysdba\' directory=test_dump dumpfile=tspitr.dmp transport_datafiles=/home/transport/data_D-RLZY_I-

1589671076_TS-TSPITR_FNO-21_orrj67ic

Import: Release 11.2.0.4.0 - Production on Mon Oct 24 17:22:29 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01":  "sys/********@SJJH AS SYSDBA" directory=test_dump dumpfile=tspitr.dmp transport_datafiles=/home/transport/data_D-

RLZY_I-1589671076_TS-TSPITR_FNO-21_orrj67ic 
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Mon Oct 24 17:22:37 2016 elapsed 0 00:00:04

导入用户tspitr下其它对象的元数据:

SQL> select tablespace_name,status from dba_tablespaces where tablespace_name='TSPITR';

TABLESPACE_NAME                STATUS
------------------------------ ---------
TSPITR                         READ ONLY

SQL> alter tablespace tspitr read write;

Tablespace altered.

[oracle@sjjh transport]$ impdp tspitr/tspitr directory=test_dump dumpfile=tspitr_metadata_only.dmp

Import: Release 11.2.0.4.0 - Production on Mon Oct 24 17:47:38 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "TSPITR"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "TSPITR"."SYS_IMPORT_FULL_01":  tspitr/******** directory=test_dump dumpfile=tspitr_metadata_only.dmp 
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"TSPITR" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Job "TSPITR"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Mon Oct 24 17:47:40 2016 elapsed 0 00:00:01

SQL> select owner,view_name,text from dba_views where owner='TSPITR';
OWNER                          VIEW_NAME                      TEXT
------------------------------ ------------------------------ --------------------------------------------------------------------------------
TSPITR                         TSPITR_VIEW                    select "OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","DATA_OBJECT_ID","OBJE

查询tspitr表中的记录,与源数据库中的记录数一致。

SQL> select count(*) from tspitr.tspitr;
  COUNT(*)
----------
     50315

现在用户tspitr的缺省表空间不是tspitr

SQL> select username,default_tablespace,temporary_tablespace from dba_users where username='TSPITR';
USERNAME                       DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
TSPITR                         USERS                          TEMP

将用户tspitr的缺省表空间修改为tspitr

SQL> alter user tspitr default tablespace tspitr;
User altered


SQL> select username,default_tablespace,temporary_tablespace from dba_users where username='TSPITR';
USERNAME                       DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
TSPITR                         TSPITR                         TEMP

SQL> select count(*) from tspitr.tspitr;
  COUNT(*)
----------
     50315

现在表tspitr的存储表空间也变为tspitr

SQL> select owner,table_name,tablespace_name from dba_tables where table_name='TSPITR';
OWNER                          TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
TSPITR                         TSPITR                         TSPITR

9.将源数据库中的tspitr表空间修改为读写模式

SQL> alter tablespace tspitr read write;

Tablespace altered.

SQL> select tablespace_name,status from dba_tablespaces where tablespace_name='TSPITR';
TABLESPACE_NAME                STATUS
------------------------------ ---------
TSPITR                         ONLINE

Oracle Real Time SQL Monitor

Oracle Real Time SQL Monitor
Oracle数据库的实时SQL监控能让你用来监控正在执行SQL语句的性能。缺省情况下,当SQL语句使用 并行执行或当SQL语句的单个操作消耗了5秒的CPU或I/O时间就会自动启动SQL监控。可以使用v $sql_monitor与v$sql_plan_monitor视图来监控SQL语句执行的统计信息。可以使用这些视图时行关 联来获得被监控到的关于执行的其它信息:
.v$active_session_history
.v$session
.v$session_longops
.v$sql
.v$sql_plan

在监控初始化之后,数据库将会向动态性能视图v$sql_monitor中增加条目。这个条目跟踪SQL执行的 关键性能,包括:执行时间,CPU时间,读取与写入的次数,I/O等待时间和各种其它等待时间。这些 统计信息当SQL语句执行时间会被实时刷新,每秒生成一次。在执行结束后,监控信息不会立即被删 除,但在v$sql_monitor中至少保留一分钟。当需要为新的SQL语句腾出空间时,会评估条目是否需要 被删除来回收空间。

v$sql_monitor视图包含v$sql视图中统计信息的一组子集。然而,不像v$sql,监控统计信息不会对 多次执行进行累加。相反,v$sql_monitor中的一个条目被关联到SQL语句的一个单独操作。如果数据 库监控相同SQL语句执行两次,那么在v$sql_monitor中会有两次执行的统计信息。

为了唯一标识相同SQL语句的两次执行,会生成一个叫执行键的复合键。执行键是由三个属性组成, 它们分别为v$sql_monitor中的:
.SQL标识符用来标识SQL语句(SQL_ID)
.开始执行时间(SQL_EXEC_START)
.一个内部生成的标识符用来确保主键是唯一的(SQL_EXEC_ID)

SQL执行计划监控
真时SQL监控也包括监控SQL语句的执行计划中的每一个操作的统计信息。这些数据在v $sql_plan_monitor视图中可以看到。类似于v$sql_monitor视图,v$sql_plan_monitor视图中的统计 信息当SQL语句被执行时每秒会更新一次。这些统计信息在SQL执行结束后是存在的,它的生命周期与 v$sql_monitor是一样的。对于每个被监控的SQL语句,在v$sql_plan_monitor视图中将会有多个条目 ,每个条目关联执行计划中的一个操作。

并行执行监控
Oracle数据库会当SQL语句开始执行时会自动监控并行查询,DML与DDL语句。v$sql_monitor与v $sql_plan_monitor视图将会以单独的条目来记录并行执行中每个操作的监控信息。

v$sql_monitor对于并行执行协调进程和每个并行执行服务器进程有一个条目。对于每一个条目在v $sql_plan_monitor视图中也有相关的条目。因为对于SQL语句并行执行的所分配的进程是相互协作的 ,这些条目共享相同的执行键(由sql_id,sql_exec_start与sql_exec_id组成)。因此可以聚合执行 键来判断并行执行的整个统计信息。

生成SQL监控报告
可以使用SQL监控报告来查看SQL监控数据。SQL监控报告使用以下视图中的数据:
.gv$sql_monitor
.gv$sql_plan_monitor
.gv$sql
.gv$sql_plan
.gv$active_session_history
.gv$session_longops

为了生成SQL监控报告,运行dbms_sqltune.report_sql_monitor过程:

SQL> set long 10000000
SQL> set longchunksize 10000000
SQL> set linesize 200
SQL> variable my_rept clob;
SQL> begin
  2   :my_rept:=dbms_sqltune.report_sql_monitor();
  3  end;
  4  /
print :my_rept

PL/SQL procedure successfully completed.

SQL>
MY_REPT
---------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------- ------------------------
SQL Monitoring Report

SQL Text
------------------------------
/* SQL Analyze(1) */ select /*+ full(t) no_parallel(t) no_parallel_index(t) dbms_stats  cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring no_substrb_pad  */to_char(count("AAE064"
)),to_char(substrb(dump(min("AAE064"),16,0,32),1,120)),to_char(substrb(dump(max ("AAE064"),16,0,32),1,120)),to_char(count("AAB001")),to_char(substrb(dump(min ("AAB001"),16,0,32),1,120)),to_char(substrb(
dump(max("AAB001"),16,0,32),1,120)),to_char(count("AAB191")),to_char(substrb(dump(min ("AAB191"),16,
0,32),1,120)),to_char(substrb(dump(max("AAB191"),16,0,32),1,120)),to_char(count ("AAB190")),to_char(substrb(dump(min("AAB190"),16,0,32),1,120)),to_char(substrb(dump (max("AAB190"),16,0,32),1,120)),to_ch
ar(count("AAB211")),to_char(substrb(dump(min("AAB211"),16,0,32),1,120)),to_char(substrb (dump(max("AAB211"),16,0,32),1,120)),to_char(count("AAB212")),to_char(substrb(dump(min ("AAB212"),16,0,32),1,120))
,to_char(substrb(dump(max("AAB212"),16,0,32),1,120)),to_char(count("AAB213")),to_char (substrb(dump(
min("AAB213"),16,0,32),1,120)),to_char(substrb(dump(max ("AAB213"),16,0,32),1,120)),to_char(count("AAB214")),to_char(substrb(dump(min ("AAB214"),16,0,32),1,120)),to_char(substrb(dump(max("AAB214"),16,0,
32),1,120)),to_char(count("AAB215")),to_char(substrb(dump(min ("AAB215"),16,0,32),1,120)),to_char(substrb(dump(max("AAB215"),16,0,32),1,120)),to_char (count("AAE011")),to_char(substrb(dump(min("AAE011")
,16,0,32),1,120)),to_char(substrb(dump(max("AAE011"),16,0,32),1,120)),to_char(count ("AAE036")),to_c
har(substrb(dump(min("AAE036"),16,0,32),1,120)),to_char(substrb(dump(max ("AAE036"),16,0,32),1,120)),to_char(count("AAE017")),to_char(substrb(dump(min ("AAE017"),16,0,32),1,120)),to_char(substrb(dump(ma
x("AAE017"),16,0,32),1,120)),to_char(count("SJCQNY")),to_char(substrb(dump(min ("SJCQNY"),16,0,32),1,120)),to_char(substrb(dump(max("SJCQNY"),16,0,32),1,120)) from  "LEMIS_4307"."AB13" t /*
NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV, NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL*/

Global Information
------------------------------
 Status              :  DONE (ALL ROWS)
 Instance ID         :  1
 Session             :  SYS (1837:1553)
 SQL ID              :  5k014mmtyr7qw
 SQL Execution ID    :  16777216
 Execution Started   :  10/17/2016 22:02:26
 First Refresh Time  :  10/17/2016 22:02:32
 Last Refresh Time   :  10/17/2016 22:02:55
 Duration            :  29s
 Module/Action       :  DBMS_SCHEDULER/ORA$AT_OS_OPT_SY_1
 Service             :  SYS$USERS
 Program             :  oracle@sjjh (J001)
 Fetch Calls         :  1

Global Stats
=================================================
| Elapsed |   Cpu   |  Other   | Fetch | Buffer |
| Time(s) | Time(s) | Waits(s) | Calls |  Gets  |
=================================================
|      28 |      28 |     0.09 |     1 |   307K |
=================================================

SQL Plan Monitoring Details (Plan Hash Value=3367417341)
======================================================================================== ===========================================
| Id |          Operation           | Name |  Rows   | Cost  |   Time    | Start  |  Execs |   Rows   | Activity | Activity Detail |
|    |                              |      | (Estim) |       | Active(s) | Active |        | (Actual) |   (%)    |   (# samples)   |
======================================================================================== ===========================================
|  0 | SELECT STATEMENT             |      |         |       |        24 |     +6 |      1 |        1 |          |                 |
|  1 |   SORT AGGREGATE             |      |       1 |       |        28 |     +2 |      1 |        1 |    34.48 | Cpu (10)        |
|  2 |    APPROXIMATE NDV AGGREGATE |      |     29M | 89482 |        29 |     +1 |      1 |      27M |    55.17 | Cpu (16)        |
|  3 |     TABLE ACCESS FULL        | AB13 |     29M | 89482 |        25 |     +5 |      1 |      29M |    10.34 | Cpu (3)         |
======================================================================================== ===========================================

dbms_sqltune.report_sql_monitor过程接受几种输入参数来指定执行,报告的详细级别与报告类型 (***216;TEXT***217;,***217;HTML***217;或***217;XML***217;)。缺省情况下,如果没有指定参数将会对最后所监控的到SQL生成文本类型 的SQL监控报告。

SQL> desc dbms_sqltune.report_sql_monitor
Parameter           Type     Mode Default?
------------------- -------- ---- --------
(RESULT)            CLOB
SQL_ID              VARCHAR2 IN   Y
SESSION_ID          NUMBER   IN   Y
SESSION_SERIAL      NUMBER   IN   Y
SQL_EXEC_START      DATE     IN   Y
SQL_EXEC_ID         NUMBER   IN   Y
INST_ID             NUMBER   IN   Y
START_TIME_FILTER   DATE     IN   Y
END_TIME_FILTER     DATE     IN   Y
INSTANCE_ID_FILTER  NUMBER   IN   Y
PARALLEL_FILTER     VARCHAR2 IN   Y
PLAN_LINE_FILTER    NUMBER   IN   Y
EVENT_DETAIL        VARCHAR2 IN   Y
BUCKET_MAX_COUNT    NUMBER   IN   Y
BUCKET_INTERVAL     NUMBER   IN   Y
BASE_PATH           VARCHAR2 IN   Y
LAST_REFRESH_TIME   DATE     IN   Y
REPORT_LEVEL        VARCHAR2 IN   Y
TYPE                VARCHAR2 IN   Y
SQL_PLAN_HASH_VALUE NUMBER   IN   Y

也可以使用以下方式来生成SQL监控报告:

SQL> set long 10000000
SQL> set longchunksize 10000000
SQL> set linesize 200
SQL> select dbms_sqltune.report_sql_monitor from dual;

REPORT_SQL_MONITOR
---------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------- ------------------------
SQL Monitoring Report

SQL Text
------------------------------
/* SQL Analyze(1) */ select /*+ full(t) no_parallel(t) no_parallel_index(t) dbms_stats  cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring no_substrb_pad  */to_char(count("MONEY_N
O")),to_char(substrb(dump(min("MONEY_NO"),16,0,32),1,120)),to_char(substrb(dump(max ("MONEY_NO"),16,0,32),1,120)),to_char(count("PAY_INFO_NO")),to_char(substrb(dump(min ("PAY_INFO_NO"),16,0,32),1,120)),
to_char(substrb(dump(max("PAY_INFO_NO"),16,0,32),1,120)),to_char(count ("PAY_MONEY")),to_char(substr
b(dump(min("PAY_MONEY"),16,0,32),1,120)),to_char(substrb(dump(max ("PAY_MONEY"),16,0,32),1,120)),to_char(count("MONEY_TYPE")),to_char(substrb(dump(min ("MONEY_TYPE"),16,0,32),1,120)),to_char(substrb(dum
p(max("MONEY_TYPE"),16,0,32),1,120)),to_char(count("CALC_MAN_SUM")),to_char(substrb (dump(min("CALC_MAN_SUM"),16,0,32),1,120)),to_char(substrb(dump(max ("CALC_MAN_SUM"),16,0,32),1,120)),to_char(count("C
ALC_BASE")),to_char(substrb(dump(min("CALC_BASE"),16,0,32),1,120)),to_char(substrb(dump (max("CALC_B
ASE"),16,0,32),1,120)),to_char(count("MONEY_ID")),to_char(substrb(dump(min ("MONEY_ID"),16,0,32),1,120)),to_char(substrb(dump(max ("MONEY_ID"),16,0,32),1,120)),to_char(count("CORP_ID")),to_char(substrb(
dump(min("CORP_ID"),16,0,32),1,120)),to_char(substrb(dump(max ("CORP_ID"),16,0,32),1,120)),to_char(count("PAYED_MONEY")),to_char(substrb(dump(min ("PAYED_MONEY"),16,0,32),1,120)),to_char(substrb(dump(ma
x("PAYED_MONEY"),16,0,32),1,120)),to_char(count("CALC_PRD")),to_char(substrb(dump(min ("CALC_PRD"),1
6,0,32),1,120)),to_char(substrb(dump(max("CALC_PRD"),16,0,32),1,120)),to_char(count ("SRC_TYPE")),to_char(substrb(dump(min("SRC_TYPE"),16,0,32),1,120)),to_char(substrb (dump(max("SRC_TYPE"),16,0,32),1,1
20)),to_char(count("PAYED_FLAG")),to_char(substrb(dump(min ("PAYED_FLAG"),16,0,32),1,120)),to_char(substrb(dump(max("PAYED_FLAG"),16,0,32),1,120))  from "SJGX_YB"."LV_CROPFUNDPAR" t /*
ACL,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV, NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL*/

Global Information
------------------------------
 Status              :  DONE (ALL ROWS)
 Instance ID         :  1
 Session             :  SYS (146:19)
 SQL ID              :  6sbw88979vmcv
 SQL Execution ID    :  16777216
 Execution Started   :  10/18/2016 22:01:04
 First Refresh Time  :  10/18/2016 22:01:11
 Last Refresh Time   :  10/18/2016 22:01:38
 Duration            :  34s
 Module/Action       :  DBMS_SCHEDULER/ORA$AT_OS_OPT_SY_4
 Service             :  SYS$USERS
 Program             :  oracle@sjjh (J001)
 Fetch Calls         :  1


Global Stats
=================================================
| Elapsed |   Cpu   |  Other   | Fetch | Buffer |
| Time(s) | Time(s) | Waits(s) | Calls |  Gets  |
=================================================
|      34 |      34 |     0.11 |     1 |   294K |
=================================================

SQL Plan Monitoring Details (Plan Hash Value=3534073399)
======================================================================================== =====================================================
| Id |          Operation           |      Name      |  Rows   | Cost  |   Time    |  Start  | Execs |   Rows   | Activity | Activity Detail |
|    |                              |                | (Estim) |       | Active(s) |  Active |       | (Actual) |   (%)    |   (# samples)   |
======================================================================================== =====================================================
|  0 | SELECT STATEMENT             |                |         |       |        28 |      +7 |     1 |        1 |          |                 |
|  1 |   SORT AGGREGATE             |                |       1 |       |        33 |      +2 |     1 |        1 |    40.00 | Cpu (14)        |
|  2 |    APPROXIMATE NDV AGGREGATE |                |     24M | 80111 |        35 |      +0 |     1 |      39M |    54.29 | Cpu (19)        |
|  3 |     MAT_VIEW ACCESS FULL     | LV_CROPFUNDPAR |     24M | 80111 |        28 |      +7 |     1 |      39M |     2.86 | Cpu (1)         |
======================================================================================== =====================================================

在Global Information部分,Status显示为DONE,说明SQL语句已经执行完成了。Time Active(s)列 显示操作已经执行了多长时间。Start Active列,以秒为单位,代表SQL语句执行的开始。在这个报 告中,MAT_VIEW ACCESS FULL是ID 3是第一个被执行的(+7s Start Active)并且执行了28秒。

Start列显示该操作被执行的次数。Rows(Actual)列显示被处理的行数,Rows(Estim)列显示优化器所 评估的行数。Memory与Temp列指示每个操作所消耗的内存与临时空间的大小。

Activity(percent)与Activity Detail(sample #)是通过连接v$sql_plan_monitor与v $active_session_history视图而得到。Activity(percent)显示执行计划中每个操作所占数据库时间 的百分比。Activity Detail(sample#)显示了活动的属性(比如CPU或等待事件)。在这个报告中,ID 2消耗了的数据库时间百分比为54.29%(APPROXIMATE NDV AGGREGATE)。活动由19samples组成。最 后一列,Progress,显示了v$session_longops视图所监控到的操作信息。

启用与禁用SQL监控
SQL监控功能当statistics _level参数被设置为all或typical(缺省值)时缺省是开启的。额外, control_management_pack_access参数必须设置为diagnostic+tuning(缺省值),因为SQL监控是 Oracle数据库优化包的一个功能。SQL监控对于所有运行时间长的查询会自动启动。

有两种语句级别的hint可以用来强制或者阻止SQL语句被监控。为了强制SQL监控,使用monitor hint:

select /*+ MONITOR */ from dual;

这种hint只有当control_management_pack_access参数被设置为diagnostic+tuning时才生效。为了 阻止SQL语句被监控可以使用no_monitor hint。

Oracle Orion Calibration Tool

Oracle Orion Calibration Tool
Oracle Orion是一种用来在没有安装Oracle或没有创建数据库的情况下来预测Oracle数据库性能的工 具。不像其它的I/O校准工具,Oracle Orion是特定为模拟Oracle数据库I/O工作量而设计的。Orion 可以通过Oracle ASM来模拟条带的性能。Orion可以使用不同的I/O负载来检测性能指标,比如 MBPS,IOPS与I/O延迟。

执行Orion需要注意以下两个问题:
.当存储是空闲或者接近空闲时执行。Orion校准存储性能是基于它所生成的I/O负载,如果在执行 Orion的同时也执行了其它非Orion的I/O工作量,那么Orion将不能正确评估性能指标。

.如果数据库已经在存储中创建,执行dbms_resource_manager.calibrate_io()时存储是一种选择。

执行Orion的步骤
1.使用-testname参数来指定一个测试名。这个参数用来唯一标识运行的Orion

2.基于测试名创建一个Orion输入文件,并且它的文件名与-testname参数所指定的测试名要相同,其 缺省的输入文件为orion.lun,例如,创建一个jytest.lun。在输入文件中列出裸设备或要测试的文 件。在输入文件的每一行添加一个卷名,不要加入任何注释。例如:

[oracle@jyrac1 bin]$ vi jytest.lun
/dev/raw/raw1
/dev/raw/raw2
/dev/raw/raw3

3.验证输入文件中所指定的所有卷,例如jytest.lun。对于在linux系统中执行以下命令进行检查:

[oracle@jyrac1 bin]$ dd if=/dev/raw/raw1 of=/dev/null bs=32k count=1024
1024+0 records in
1024+0 records out
33554432 bytes (34 MB) copied, 1.76808 seconds, 19.0 MB/s
[oracle@jyrac1 bin]$ dd if=/dev/raw/raw2 of=/dev/null bs=32k count=1024
1024+0 records in
1024+0 records out
33554432 bytes (34 MB) copied, 1.91884 seconds, 17.5 MB/s
[oracle@jyrac1 bin]$ dd if=/dev/raw/raw3 of=/dev/null bs=32k count=1024
1024+0 records in
1024+0 records out
33554432 bytes (34 MB) copied, 1.51707 seconds, 22.1 MB/s

4.第一次执行Orion时,使用oltp或dss选项的-run参数。如果数据库主要用于OLTP,那么使用-run oltp。如果数据库主要用于DSS,那么使用-run dss。

例如

[oracle@jyrac1 bin]$ ./orion -run oltp -testname jytest
ORION: ORacle IO Numbers -- Version 11.2.0.4.0
jytest_20161011_2122
Calibration will take approximately 24 minutes.
Using a large value for -cache_size may take longer.


Maximum Small IOPS=911 @ Small=60 and Large=0
Small Read Latency: avg=65784 us, min=2256 us, max=721805 us, std dev=58565 us @  Small=60 and Large=0

Minimum Small Latency=10292 usecs @ Small=3 and Large=0
Small Read Latency: avg=10292 us, min=968 us, max=164951 us, std dev=7615 us @ Small=3  and Large=0
Small Read / Write Latency Histogram @ Small=3 and Large=0
        Latency:                # of IOs (read)          # of IOs (write)
        0 - 1           us:             0                       0
        2 - 4           us:             0                       0
        4 - 8           us:             0                       0
        8 - 16          us:             0                       0
       16 - 32          us:             0                       0
       32 - 64          us:             0                       0
       64 - 128         us:             0                       0
      128 - 256         us:             0                       0
      256 - 512         us:             0                       0
      512 - 1024        us:             2                       0
     1024 - 2048        us:             17                      0
     2048 - 4096        us:             1291                    0
     4096 - 8192        us:             7044                    0
     8192 - 16384       us:             6899                    0
    16384 - 32768       us:             1870                    0
    32768 - 65536       us:             279                     0
    65536 - 131072      us:             25                      0
   131072 - 262144      us:             5                       0
   262144 - 524288      us:             0                       0
   524288 - 1048576     us:             0                       0
  1048576 - 2097152     us:             0                       0
  2097152 - 4194304     us:             0                       0
  4194304 - 8388608     us:             0                       0
  8388608 - 16777216    us:             0                       0
 16777216 - 33554432    us:             0                       0
 33554432 - 67108864    us:             0                       0
 67108864 - 134217728   us:             0                       0
134217728 - 268435456   us:             0                       0

在orion命令执行完后会生成以下文件:

[oracle@jyrac1 bin]$ ls -lrt jytest*
-rw-r--r-- 1 oracle oinstall 43524 Oct 11 21:42 jytest_20161011_2122_trace.txt
-rw-r--r-- 1 oracle oinstall  1930 Oct 11 21:42 jytest_20161011_2122_summary.txt
-rw-r--r-- 1 oracle oinstall   570 Oct 11 21:42 jytest_20161011_2122_mbps.csv
-rw-r--r-- 1 oracle oinstall   800 Oct 11 21:42 jytest_20161011_2122_lat.csv
-rw-r--r-- 1 oracle oinstall   742 Oct 11 21:42 jytest_20161011_2122_iops.csv
-rw-r--r-- 1 oracle oinstall 19369 Oct 11 21:42 jytest_20161011_2122_hist.txt

orion执行的汇总结果存储在*_summary.txt文件中:

[oracle@jyrac1 bin]$ cat jytest_20161011_2122_summary.txt
ORION VERSION 11.2.0.4.0

Command line:
-run oltp -testname jytest

These options enable these settings:
Test: jytest
Small IO size: 8 KB
Large IO size: 1024 KB
IO types: small random IOs, large random IOs
Sequential stream pattern: one LUN per stream
Writes: 0%
Cache size: not specified
Duration for each data point: 60 seconds
Small Columns:,      3,      6,      9,     12,     15,     18,     21,     24,     27,      30,     33,     36,     39,     42,     45,     48,     51,     54,     57,     60
Large Columns:,      0
Total Data Points: 23

Name: /dev/raw/raw1     Size: 5368709120
Name: /dev/raw/raw2     Size: 5368709120
Name: /dev/raw/raw3     Size: 10737418240
3 files found.


Maximum Small IOPS=911 @ Small=60 and Large=0
Small Read Latency: avg=65784 us, min=2256 us, max=721805 us, std dev=58565 us @  Small=60 and Large=0

Minimum Small Latency=10292.33 usecs @ Small=3 and Large=0
Small Read Latency: avg=10292 us, min=968 us, max=164951 us, std dev=7615 us @ Small=3  and Large=0
Small Read / Write Latency Histogram @ Small=60 and Large=0
        Latency:                # of IOs (read)          # of IOs (write)
        0 - 1           us:             0                       0
        2 - 4           us:             0                       0
        4 - 8           us:             0                       0
        8 - 16          us:             0                       0
       16 - 32          us:             0                       0
       32 - 64          us:             0                       0
       64 - 128         us:             0                       0
      128 - 256         us:             0                       0
      256 - 512         us:             0                       0
      512 - 1024        us:             2                       0
     1024 - 2048        us:             17                      0
     2048 - 4096        us:             1291                    0
     4096 - 8192        us:             7044                    0
     8192 - 16384       us:             6899                    0
    16384 - 32768       us:             1870                    0
    32768 - 65536       us:             279                     0
    65536 - 131072      us:             25                      0
   131072 - 262144      us:             5                       0
   262144 - 524288      us:             0                       0
   524288 - 1048576     us:             0                       0
  1048576 - 2097152     us:             0                       0
  2097152 - 4194304     us:             0                       0
  4194304 - 8388608     us:             0                       0
  8388608 - 16777216    us:             0                       0
 16777216 - 33554432    us:             0                       0
 33554432 - 67108864    us:             0                       0
 67108864 - 134217728   us:             0                       0
134217728 - 268435456   us:             0                       0

Orion输入文件
当指定Orion -testname 参数时,这将设置Orion输入与输出的文件名的前缀。-testname 选项的缺省值为orion

Orion参数
Orion提供了命令参数来指定I/O工作量类型与其它Orion选项。
-run参数是Orion命令必须要指定的。

-run level选项,指定测试运行的级别level。这个选项提供了运行级别并且允许复杂命令来指定高 级级别。如果没有设置 -run advanced,那么设置任何其它参数,比如-cache_size或-verbose,都会 报错。

除了高级级别之外,所有其它的-run level设置将使用一组预定义参数设置。

level必须是以下之一:
.oltp
测试在增加负载时使用随机小(8K)I/O来判断最大的IOPS。这个参数相关的Orion调用如下:

/orion -run advanced \
-num_large 0 -size_small 8 -type rand \
-simulate concat -write 0 -duration 60 \
-matrix row

.dss
测试在增加负载时使用随机大(1M)I/O来判断最大的吞吐量。这个参数相关的Orion调用如下:

./orion -run advanced \
-num_small 0 -size_large 1024 -type rand \
-simulate concat -write 0 -duration 60   \
-matrix column

.simple
对负载生成小的随机I/O与大的随机I/O工作量。在这个选项中,小I/O与大I/O是隔离进行测试。唯一 能指定的选项参数是-cache_size与-verbose。这个参数相关的Orion调用如下:

./orion -run advanced \
-size_small 8 -size_large 1024 -type rand \
-simulate concat -write 0 -duration 60 \
-matrix basic

.normal
与simple相同,但也会生成小的随机I/O与大的随机I/O工作量。对于这个级别唯一能指定的选项参数 是-cache_size与-verbose。这个参数相关的Orion调用如下:

./orion -run advanced \
-size_small 8 -size_large 1024 -type rand \
-simulate concat -write 0 -duration 60 \
-matrix detailed

.advanced
使用自定义的选项参数来测试工作量。对于这个级别可以指定任何选项参数。

Orion的选项参数
-cache_size:指定存储阵列读取或写入的缓存大小(in MB)。对于大的连续 I/O工作量,Orion在每个 数据点之前执行随机大I/O来加热缓存。Orion使用缓存的大小来决定缓存加热的持续周期。如果设置 为0,将不会执行缓存加热。除非这个选项设置为0,Orion在每个大的连续的数据点之前执行随机I/O。 这些I/O将会填充到存储阵列的缓存中。使用随机数据,因此从一个数据点的I/O请求不会在下一个数 据点的命中。它的缺省值,如果没有指定,缺值为2分钟。

-duration num_seconds:测试每个数据点的测试持续时间,缺省值是60

-help:打印Orion帮助信息。

-matrix type:设置混合工作量类型。一个Orion测试可以由多个数据点测试组成。数据点测试可以用 两个维度的混合组成。 在混合工作量类型中每一列代表相同小I/O负载的数据点测试。每一行代表 相同大I/O负载的数据点测试,Orion测试可以是单点,单行,单列或整个混合工作量,依赖于混合类 型:
.basic:不是混合工作量。小的随机与大的随机/连续工作量是分别进行测试。

.detailed:小的随机与大的随机/连续工作量是组合进行测试,测试整个混合工作量。

.point:使用S指示使用小的随机I/O或连续流的单个数据点与使用L指示使用大的随机I/O或连续流的 单个数据点。S是通过-num_small参数进行设置。L是通过-num_large参数进行设置。使用-num_small 测试小I/O,-num_large测试大I/O。

.col:只用于大的随机/连续工作量。使用-num_large大I/O来测试不同大小的小I/O负载。

.row:只用于小的随机工作量。使用-num_large大I/O来测试不同大小的小I/O负载

.max:与deatiled一样,但只在最大负载测试工作量,指定-num_small与-num_large参数。测试的不 同大小负载受限于-num_small与-num_large。

它的缺省值是basic。

-num_disks value:指定测试的物理磁盘数。为了生成一个范围的负载。指定磁盘数。这里的value被 用来保存一定范围的负载。增加这个参数会造成严重的I/O负载。它的缺省值是testname.lun中的lun 数。

-num_large value:控制大I/O负载。注意,它是当指定-matrix时的唯一选项:row,point或max。当- type选项被设置rand,这个参数value指定显著数量的大I/O。
当-type选项设置为seq,这个参数value 指定连续I/O流的数量。

-num_small:指定小随机I/O工作量的显著I/O的最大数量。注意这个选项只能在指定-matrix 为:col,point或max时才能使用。

-simulate type:数据分布来模拟大的连续I/O工作量。Orion通过以以下方式来组合特定的LUN来以虚 拟LUN的格式来进行测试:
.concat:通过连续链接的特定LUN来模拟虚拟卷组。连续测试是一个LUN接着一个LUN的进行。

.raid0:通过跨指定的LUN条带化来模拟虚拟卷组。每个连续流使用raid0条带来跨所有LUN执行I/O。 缺省的条带深度是1M,为了匹配ASM的条带深度,可以使用-stripe参数进行修改。

I/O的偏移量由以下因素决定:
对于小的随机与大的随机工作量:
.LUN被链接成单个虚拟LUN(VLUN)并且对于VLUN选择随机偏移量。

对于大的连续工作量:
.使用条带(-simulate raid0)。LUN被用来创建单个条带化的VLUN。不使用并发的小的随机工作量, 连续流在条带VLUN时使用固定偏移量。对于n streams,stream 1在偏移量VLUN size*(i+1)/(n+1), 除非n为1,在这种情况下,单个流在offset 0开始。使用并发小的随机工作量,流在使用条带VLUN使 用随机偏移量。

.不使用条带(-simulate concat)。LUN被连接成单个VLUN。流从单个VLUN的随机偏移量开始。

这个参数通常只用于-type为seq。

-size_large num:指定大的随机或连续I/O工作量的I/O大小以KB为单位,缺省值为1024。

-size_small num:指定小的随机或连续I/O工作量的I/O大小以KB为单位,缺省值为8。

-testname tname:指定运行测试的标识符。当指定时,包含LUN磁盘的输入文件,或文件必须命名为 .lun。输出文件名以_.为前缀。缺省值为:orion

-type[rand | seq]:大I/O工作量类型,rand:随机分布的大I/O,seq:连续流大I/O,缺省值为rand

-verbose:打印状态与跟踪信息来标准输出。缺省值为option not set

-write num_write:指定被写入的I/O百分比。对于大的连续I/O,每个流要么是只读,要么是写,这个参数指定只写的百分比。缺省值为0。

Orion命令行示例
1.对于OLTP数据库来评估存储的IO性能

[oracle@jyrac1 bin]$ ./orion -run oltp -testname jytest
ORION: ORacle IO Numbers -- Version 11.2.0.4.0
jytest_20161011_2122
Calibration will take approximately 24 minutes.
Using a large value for -cache_size may take longer.


Maximum Small IOPS=911 @ Small=60 and Large=0
Small Read Latency: avg=65784 us, min=2256 us, max=721805 us, std dev=58565 us @  Small=60 and Large=0

Minimum Small Latency=10292 usecs @ Small=3 and Large=0
Small Read Latency: avg=10292 us, min=968 us, max=164951 us, std dev=7615 us @ Small=3  and Large=0
Small Read / Write Latency Histogram @ Small=3 and Large=0
        Latency:                # of IOs (read)          # of IOs (write)
        0 - 1           us:             0                       0
        2 - 4           us:             0                       0
        4 - 8           us:             0                       0
        8 - 16          us:             0                       0
       16 - 32          us:             0                       0
       32 - 64          us:             0                       0
       64 - 128         us:             0                       0
      128 - 256         us:             0                       0
      256 - 512         us:             0                       0
      512 - 1024        us:             2                       0
     1024 - 2048        us:             17                      0
     2048 - 4096        us:             1291                    0
     4096 - 8192        us:             7044                    0
     8192 - 16384       us:             6899                    0
    16384 - 32768       us:             1870                    0
    32768 - 65536       us:             279                     0
    65536 - 131072      us:             25                      0
   131072 - 262144      us:             5                       0
   262144 - 524288      us:             0                       0
   524288 - 1048576     us:             0                       0
  1048576 - 2097152     us:             0                       0
  2097152 - 4194304     us:             0                       0
  4194304 - 8388608     us:             0                       0
  8388608 - 16777216    us:             0                       0
 16777216 - 33554432    us:             0                       0
 33554432 - 67108864    us:             0                       0
 67108864 - 134217728   us:             0                       0
134217728 - 268435456   us:             0                       0

2.对于DSS数据库评估存储IO性能

[oracle@jyrac1 bin]$ ./orion -run dss -testname jytest
ORION: ORacle IO Numbers -- Version 11.2.0.4.0
jytest_20161012_1736
Calibration will take approximately 73 minutes.
Using a large value for -cache_size may take longer.

ORION VERSION 11.2.0.4.0

Command line:
-run dss -testname jytest

These options enable these settings:
Test: jytest
Small IO size: 8 KB
Large IO size: 1024 KB
IO types: small random IOs, large random IOs
Sequential stream pattern: one LUN per stream
Writes: 0%
Cache size: not specified
Duration for each data point: 240 seconds
Small Columns:,      0
Large Columns:,      3,      6,      9,     12,     15,     18,     21,     24,     27,      30,     33,     36,     39,     42,     45
Total Data Points: 18

Name: /dev/raw/raw1     Size: 5368709120
Name: /dev/raw/raw2     Size: 5368709120
Name: /dev/raw/raw3     Size: 10737418240
3 files found.

Maximum Large MBPS=141.56 @ Small=0 and Large=27

3.对于基本的数据集评估存储IO性能

[oracle@jyrac1 bin]$ ./orion -run normal -testname jytest
ORION: ORacle IO Numbers -- Version 11.2.0.4.0
jytest_20161012_1923
Calibration will take approximately 113 minutes.
Using a large value for -cache_size may take longer.

Maximum Large MBPS=124.02 @ Small=0 and Large=6

Maximum Small IOPS=691 @ Small=15 and Large=0
Small Read Latency: avg=21670 us, min=1325 us, max=430186 us, std dev=21687 us @  Small=15 and Large=0

Minimum Small Latency=7141 usecs @ Small=1 and Large=0
Small Read Latency: avg=7141 us, min=934 us, max=212964 us, std dev=5383 us @ Small=1  and Large=0
Small Read / Write Latency Histogram @ Small=1 and Large=0
        Latency:                # of IOs (read)          # of IOs (write)
        0 - 1           us:             0                       0
        2 - 4           us:             0                       0
        4 - 8           us:             0                       0
        8 - 16          us:             0                       0
       16 - 32          us:             0                       0
       32 - 64          us:             0                       0
       64 - 128         us:             0                       0
      128 - 256         us:             0                       0
      256 - 512         us:             0                       0
      512 - 1024        us:             4                       0
     1024 - 2048        us:             24                      0
     2048 - 4096        us:             1398                    0
     4096 - 8192        us:             4993                    0
     8192 - 16384       us:             1672                    0
    16384 - 32768       us:             250                     0
    32768 - 65536       us:             20                      0
    65536 - 131072      us:             5                       0
   131072 - 262144      us:             1                       0
   262144 - 524288      us:             0                       0
   524288 - 1048576     us:             0                       0
  1048576 - 2097152     us:             0                       0
  2097152 - 4194304     us:             0                       0
  4194304 - 8388608     us:             0                       0
  8388608 - 16777216    us:             0                       0
 16777216 - 33554432    us:             0                       0
 33554432 - 67108864    us:             0                       0
 67108864 - 134217728   us:             0                       0
134217728 - 268435456   us:             0                       0

4.为了理解存储性能使用只读,小与大的随机I/O工作量:

[oracle@jyrac1 bin]$ ./orion -run simple -testname jytest
ORION: ORacle IO Numbers -- Version 11.2.0.4.0
jytest_20161012_2122
Calibration will take approximately 23 minutes.
Using a large value for -cache_size may take longer.

Maximum Large MBPS=125.09 @ Small=0 and Large=6

Maximum Small IOPS=696 @ Small=15 and Large=0
Small Read Latency: avg=21501 us, min=807 us, max=351786 us, std dev=22448 us @ Small=15  and Large=0

Minimum Small Latency=6916 usecs @ Small=1 and Large=0
Small Read Latency: avg=6916 us, min=774 us, max=174704 us, std dev=4991 us @ Small=1  and Large=0
Small Read / Write Latency Histogram @ Small=1 and Large=0
        Latency:                # of IOs (read)          # of IOs (write)
        0 - 1           us:             0                       0
        2 - 4           us:             0                       0
        4 - 8           us:             0                       0
        8 - 16          us:             0                       0
       16 - 32          us:             0                       0
       32 - 64          us:             0                       0
       64 - 128         us:             0                       0
      128 - 256         us:             0                       0
      256 - 512         us:             0                       0
      512 - 1024        us:             3                       0
     1024 - 2048        us:             23                      0
     2048 - 4096        us:             1480                    0
     4096 - 8192        us:             5299                    0
     8192 - 16384       us:             1611                    0
    16384 - 32768       us:             196                     0
    32768 - 65536       us:             22                      0
    65536 - 131072      us:             4                       0
   131072 - 262144      us:             3                       0
   262144 - 524288      us:             0                       0
   524288 - 1048576     us:             0                       0
  1048576 - 2097152     us:             0                       0
  2097152 - 4194304     us:             0                       0
  4194304 - 8388608     us:             0                       0
  8388608 - 16777216    us:             0                       0
 16777216 - 33554432    us:             0                       0
 33554432 - 67108864    us:             0                       0
 67108864 - 134217728   us:             0                       0
134217728 - 268435456   us:             0                       0

5.为了理解存储性能使用小与大混合的随机I/O工作量:

[oracle@jyrac1 bin]$ ./orion -run normal -testname jytest
ORION: ORacle IO Numbers -- Version 11.2.0.4.0
jytest_20161012_1923
Calibration will take approximately 113 minutes.
Using a large value for -cache_size may take longer.

Maximum Large MBPS=124.02 @ Small=0 and Large=6

Maximum Small IOPS=691 @ Small=15 and Large=0
Small Read Latency: avg=21670 us, min=1325 us, max=430186 us, std dev=21687 us @  Small=15 and Large=0

Minimum Small Latency=7141 usecs @ Small=1 and Large=0
Small Read Latency: avg=7141 us, min=934 us, max=212964 us, std dev=5383 us @ Small=1  and Large=0
Small Read / Write Latency Histogram @ Small=1 and Large=0
        Latency:                # of IOs (read)          # of IOs (write)
        0 - 1           us:             0                       0
        2 - 4           us:             0                       0
        4 - 8           us:             0                       0
        8 - 16          us:             0                       0
       16 - 32          us:             0                       0
       32 - 64          us:             0                       0
       64 - 128         us:             0                       0
      128 - 256         us:             0                       0
      256 - 512         us:             0                       0
      512 - 1024        us:             4                       0
     1024 - 2048        us:             24                      0
     2048 - 4096        us:             1398                    0
     4096 - 8192        us:             4993                    0
     8192 - 16384       us:             1672                    0
    16384 - 32768       us:             250                     0
    32768 - 65536       us:             20                      0
    65536 - 131072      us:             5                       0
   131072 - 262144      us:             1                       0
   262144 - 524288      us:             0                       0
   524288 - 1048576     us:             0                       0
  1048576 - 2097152     us:             0                       0
  2097152 - 4194304     us:             0                       0
  4194304 - 8388608     us:             0                       0
  8388608 - 16777216    us:             0                       0
 16777216 - 33554432    us:             0                       0
 33554432 - 67108864    us:             0                       0
 67108864 - 134217728   us:             0                       0
134217728 - 268435456   us:             0                       0

6.为了生成32KB与1MB的读取组合的随机I/O执行以下命令:

[oracle@jyrac1 bin]$ ./orion -run advanced -size_small 32 -size_large 1024 -type rand - matrix detailed -testname jytest
ORION: ORacle IO Numbers -- Version 11.2.0.4.0
jytest_20161012_2153
Calibration will take approximately 113 minutes.
Using a large value for -cache_size may take longer.

Maximum Large MBPS=122.87 @ Small=0 and Large=6

Maximum Small IOPS=578 @ Small=15 and Large=0
Small Read Latency: avg=25892 us, min=2162 us, max=390074 us, std dev=24858 us @  Small=15 and Large=0

Minimum Small Latency=7386 usecs @ Small=1 and Large=0
Small Read Latency: avg=7386 us, min=1196 us, max=140649 us, std dev=4656 us @ Small=1  and Large=0
Small Read / Write Latency Histogram @ Small=1 and Large=0
        Latency:                # of IOs (read)          # of IOs (write)
        0 - 1           us:             0                       0
        2 - 4           us:             0                       0
        4 - 8           us:             0                       0
        8 - 16          us:             0                       0
       16 - 32          us:             0                       0
       32 - 64          us:             0                       0
       64 - 128         us:             0                       0
      128 - 256         us:             0                       0
      256 - 512         us:             0                       0
      512 - 1024        us:             0                       0
     1024 - 2048        us:             5                       0
     2048 - 4096        us:             940                     0
     4096 - 8192        us:             4859                    0
     8192 - 16384       us:             2060                    0
    16384 - 32768       us:             204                     0
    32768 - 65536       us:             15                      0
    65536 - 131072      us:             6                       0
   131072 - 262144      us:             1                       0
   262144 - 524288      us:             0                       0
   524288 - 1048576     us:             0                       0
  1048576 - 2097152     us:             0                       0
  2097152 - 4194304     us:             0                       0
  4194304 - 8388608     us:             0                       0
  8388608 - 16777216    us:             0                       0
 16777216 - 33554432    us:             0                       0
 33554432 - 67108864    us:             0                       0
 67108864 - 134217728   us:             0                       0
134217728 - 268435456   us:             0                       0

7.为了生成多路1M写入流,模拟1MB的RAID-0条带:

./orion -run advanced -simulate raid0 -stripe 1024 -write 100 -type seq -matrix col - num_small 0 -testname jytest

8.为了生成32KB与1MB的组合随机读取:

./orion -run advanced -size_small 32 -size_large 1024 -type rand -matrix detailed - testname jytest

9.为了生成多路连续IMB的写入流,并模拟RAID0条带:

./orion -run advanced -simulate raid0 -write 100 -type seq -matrix col -num_small 0 - testnmae jytest

Proudly powered by WordPress | Indrajeet by Sus Hill.