DETAILED ADDM REPORT FOR TASK 'ADDM:2821911605_1_5929' WITH ID 45748
-------------------------------------------------------------------- Analysis Period: 30-6月 -2011 from 13:00:22 to 14:01:02
Database ID/Instance: 2821911605/1
Database/Instance Names: MTK/mtk
Host Name: SL-1B1AFC8202AD
Database Version: 10.2.0.1.0
Snapshot Range: from 5928 to 5929
Database Time: 1990 seconds
Average Database Load: .5 active sessions
FINDING 1: 100% impact (1990 seconds)
-------------------------------------
在主机操作系统中检测到大量虚拟内存写入/写出。 RECOMMENDATION 1: Host Configuration, 100% benefit (1990 seconds)
ACTION: 主机操作系统出现大量内存写入/写出, 但未检测到根本原因。请研究不属于 (在消耗了大量虚拟内存的主机上运行的)
此实例的进程。还可以考虑在主机中添加更多物理内存。FINDING 2: 96% impact (1913 seconds)
------------------------------------
发现 SQL 语句消耗了大量数据库时间。 RECOMMENDATION 1: SQL Tuning, 100% benefit (2057 seconds)
ACTION: 对 SQL_ID 为 "7b481nzpdya3s" 的 SQL 语句运行 SQL Tuning Advisor。
RELEVANT OBJECT: SQL statement with SQL_ID 7b481nzpdya3s and
PLAN_HASH 777295479
select syncorder0_.ID as ID53_, syncorder0_.MOBILE as MOBILE53_,
syncorder0_.FEE as FEE53_, syncorder0_.ORDER_TIME as ORDER4_53_,
syncorder0_.ORDER_PORT as ORDER5_53_, syncorder0_.ORDER_CMD as
ORDER6_53_, syncorder0_.TERMINAL_ID as TERMINAL7_53_,
syncorder0_.PROJECT_ID as PROJECT8_53_, syncorder0_.SP_ID as SP9_53_,
syncorder0_.STATE_REPORT as STATE10_53_, syncorder0_.LINK_ID as
LINK11_53_, syncorder0_.MID as MID53_, syncorder0_.STATE_RESULT as
STATE13_53_, syncorder0_.COMMAND_ID as COMMAND14_53_,
syncorder0_.PRODUCT_ID as PRODUCT15_53_, syncorder0_.ALLOW_PAY as
ALLOW16_53_, syncorder0_.AREACODE as AREACODE53_, syncorder0_.SP_IP
as SP18_53_, syncorder0_.CARRIER as CARRIER53_ from MTK.SYNC_ORDER
syncorder0_ where syncorder0_.LINK_ID=:1 order by
syncorder0_.ORDER_TIME desc
RATIONALE: SQL_ID 为 "7b481nzpdya3s" 的 SQL 语句执行了 450 次, 每次执行平均用时 4.2 秒。FINDING 3: 88% impact (1752 seconds)
------------------------------------
发现个别数据库段造成了大量的用户 I/O 等待。 RECOMMENDATION 1: Segment Tuning, 88% benefit (1752 seconds)
ACTION: 在 TABLE "MTK.SYNC_ORDER" (对象 ID 为 51659) 上运行 "Segment Advisor"。
RELEVANT OBJECT: database object with id 51659
ACTION: 调查涉及 TABLE "MTK.SYNC_ORDER" (对象 ID 为 51659) 的 I/O 的应用程序逻辑。
RELEVANT OBJECT: database object with id 51659
RATIONALE: 对象的 I/O 使用统计信息为: 450 完整对象扫描, 9789443 物理读取, 35 物理写入和 0 直接读取。
RATIONALE: SQL_ID 为 "7b481nzpdya3s" 的 SQL 语句在等待热对象的用户 I/O 上消耗了大量时间。
RELEVANT OBJECT: SQL statement with SQL_ID 7b481nzpdya3s
select syncorder0_.ID as ID53_, syncorder0_.MOBILE as MOBILE53_,
syncorder0_.FEE as FEE53_, syncorder0_.ORDER_TIME as ORDER4_53_,
syncorder0_.ORDER_PORT as ORDER5_53_, syncorder0_.ORDER_CMD as
ORDER6_53_, syncorder0_.TERMINAL_ID as TERMINAL7_53_,
syncorder0_.PROJECT_ID as PROJECT8_53_, syncorder0_.SP_ID as SP9_53_,
syncorder0_.STATE_REPORT as STATE10_53_, syncorder0_.LINK_ID as
LINK11_53_, syncorder0_.MID as MID53_, syncorder0_.STATE_RESULT as
STATE13_53_, syncorder0_.COMMAND_ID as COMMAND14_53_,
syncorder0_.PRODUCT_ID as PRODUCT15_53_, syncorder0_.ALLOW_PAY as
ALLOW16_53_, syncorder0_.AREACODE as AREACODE53_, syncorder0_.SP_IP
as SP18_53_, syncorder0_.CARRIER as CARRIER53_ from MTK.SYNC_ORDER
syncorder0_ where syncorder0_.LINK_ID=:1 order by
syncorder0_.ORDER_TIME desc SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: 等待类别 "用户 I/O" 消耗了大量数据库时间。 (89% impact [1763 seconds])FINDING 4: 88% impact (1745 seconds)
------------------------------------
发现个别 SQL 语句造成了大量的用户 I/O 等待。 RECOMMENDATION 1: SQL Tuning, 100% benefit (2057 seconds)
ACTION: 对 SQL_ID 为 "7b481nzpdya3s" 的 SQL 语句运行 SQL Tuning Advisor。
RELEVANT OBJECT: SQL statement with SQL_ID 7b481nzpdya3s and
PLAN_HASH 777295479
select syncorder0_.ID as ID53_, syncorder0_.MOBILE as MOBILE53_,
syncorder0_.FEE as FEE53_, syncorder0_.ORDER_TIME as ORDER4_53_,
syncorder0_.ORDER_PORT as ORDER5_53_, syncorder0_.ORDER_CMD as
ORDER6_53_, syncorder0_.TERMINAL_ID as TERMINAL7_53_,
syncorder0_.PROJECT_ID as PROJECT8_53_, syncorder0_.SP_ID as SP9_53_,
syncorder0_.STATE_REPORT as STATE10_53_, syncorder0_.LINK_ID as
LINK11_53_, syncorder0_.MID as MID53_, syncorder0_.STATE_RESULT as
STATE13_53_, syncorder0_.COMMAND_ID as COMMAND14_53_,
syncorder0_.PRODUCT_ID as PRODUCT15_53_, syncorder0_.ALLOW_PAY as
ALLOW16_53_, syncorder0_.AREACODE as AREACODE53_, syncorder0_.SP_IP
as SP18_53_, syncorder0_.CARRIER as CARRIER53_ from MTK.SYNC_ORDER
syncorder0_ where syncorder0_.LINK_ID=:1 order by
syncorder0_.ORDER_TIME desc
RATIONALE: SQL_ID 为 "7b481nzpdya3s" 的 SQL 语句执行了 450 次, 每次执行平均用时 4.2 秒。
RATIONALE: 每次执行在用户 I/O 等待事件上花费的平均时间为 3.8 秒。 SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: 等待类别 "用户 I/O" 消耗了大量数据库时间。 (89% impact [1763 seconds])FINDING 5: 16% impact (312 seconds)
-----------------------------------
实例在 CPU 上花费的时间占据了数据库时间中的大部分。 RECOMMENDATION 1: SQL Tuning, 100% benefit (2057 seconds)
ACTION: 对 SQL_ID 为 "7b481nzpdya3s" 的 SQL 语句运行 SQL Tuning Advisor。
RELEVANT OBJECT: SQL statement with SQL_ID 7b481nzpdya3s and
PLAN_HASH 777295479
select syncorder0_.ID as ID53_, syncorder0_.MOBILE as MOBILE53_,
syncorder0_.FEE as FEE53_, syncorder0_.ORDER_TIME as ORDER4_53_,
syncorder0_.ORDER_PORT as ORDER5_53_, syncorder0_.ORDER_CMD as
ORDER6_53_, syncorder0_.TERMINAL_ID as TERMINAL7_53_,
syncorder0_.PROJECT_ID as PROJECT8_53_, syncorder0_.SP_ID as SP9_53_,
syncorder0_.STATE_REPORT as STATE10_53_, syncorder0_.LINK_ID as
LINK11_53_, syncorder0_.MID as MID53_, syncorder0_.STATE_RESULT as
STATE13_53_, syncorder0_.COMMAND_ID as COMMAND14_53_,
syncorder0_.PRODUCT_ID as PRODUCT15_53_, syncorder0_.ALLOW_PAY as
ALLOW16_53_, syncorder0_.AREACODE as AREACODE53_, syncorder0_.SP_IP
as SP18_53_, syncorder0_.CARRIER as CARRIER53_ from MTK.SYNC_ORDER
syncorder0_ where syncorder0_.LINK_ID=:1 order by
syncorder0_.ORDER_TIME desc
RATIONALE: SQL_ID 为 "7b481nzpdya3s" 的 SQL 语句执行了 450 次, 每次执行平均用时 4.2 秒。
RATIONALE: 每次执行使用 CPU 的平均时间为 0.69 秒。FINDING 6: 6.3% impact (125 seconds)
------------------------------------
SGA 大小不合适, 导致附加 I/O 或硬语法分析。 RECOMMENDATION 1: DB Configuration, 6% benefit (119 seconds)
ACTION: 通过将参数 "sga_target" 设置为 1022 M, 增加 SGA 的大小。 ADDITIONAL INFORMATION:
分析期间, 参数 "sga_target" 的值为 "584 M"。 SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: 等待类别 "用户 I/O" 消耗了大量数据库时间。 (89% impact [1763 seconds])
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ADDITIONAL INFORMATION
----------------------等待类别 "应用程序" 并未消耗大量数据库时间。
等待类别 "提交" 并未消耗大量数据库时间。
等待类别 "并发" 并未消耗大量数据库时间。
等待类别 "配置" 并未消耗大量数据库时间。
等待类别 "网络" 并未消耗大量数据库时间。
会话连接和断开连接的调用并未消耗大量数据库时间。
对 SQL 语句的硬语法分析并未消耗大量数据库时间。The analysis of I/O performance is based on the default assumption that the
average read time for one database block is 10000 micro-seconds.An explanation of the terminology used in this report is available when you
run the report with the 'ALL' level of detail.
-------------------------------------------------------------------- Analysis Period: 30-6月 -2011 from 13:00:22 to 14:01:02
Database ID/Instance: 2821911605/1
Database/Instance Names: MTK/mtk
Host Name: SL-1B1AFC8202AD
Database Version: 10.2.0.1.0
Snapshot Range: from 5928 to 5929
Database Time: 1990 seconds
Average Database Load: .5 active sessions
FINDING 1: 100% impact (1990 seconds)
-------------------------------------
在主机操作系统中检测到大量虚拟内存写入/写出。 RECOMMENDATION 1: Host Configuration, 100% benefit (1990 seconds)
ACTION: 主机操作系统出现大量内存写入/写出, 但未检测到根本原因。请研究不属于 (在消耗了大量虚拟内存的主机上运行的)
此实例的进程。还可以考虑在主机中添加更多物理内存。FINDING 2: 96% impact (1913 seconds)
------------------------------------
发现 SQL 语句消耗了大量数据库时间。 RECOMMENDATION 1: SQL Tuning, 100% benefit (2057 seconds)
ACTION: 对 SQL_ID 为 "7b481nzpdya3s" 的 SQL 语句运行 SQL Tuning Advisor。
RELEVANT OBJECT: SQL statement with SQL_ID 7b481nzpdya3s and
PLAN_HASH 777295479
select syncorder0_.ID as ID53_, syncorder0_.MOBILE as MOBILE53_,
syncorder0_.FEE as FEE53_, syncorder0_.ORDER_TIME as ORDER4_53_,
syncorder0_.ORDER_PORT as ORDER5_53_, syncorder0_.ORDER_CMD as
ORDER6_53_, syncorder0_.TERMINAL_ID as TERMINAL7_53_,
syncorder0_.PROJECT_ID as PROJECT8_53_, syncorder0_.SP_ID as SP9_53_,
syncorder0_.STATE_REPORT as STATE10_53_, syncorder0_.LINK_ID as
LINK11_53_, syncorder0_.MID as MID53_, syncorder0_.STATE_RESULT as
STATE13_53_, syncorder0_.COMMAND_ID as COMMAND14_53_,
syncorder0_.PRODUCT_ID as PRODUCT15_53_, syncorder0_.ALLOW_PAY as
ALLOW16_53_, syncorder0_.AREACODE as AREACODE53_, syncorder0_.SP_IP
as SP18_53_, syncorder0_.CARRIER as CARRIER53_ from MTK.SYNC_ORDER
syncorder0_ where syncorder0_.LINK_ID=:1 order by
syncorder0_.ORDER_TIME desc
RATIONALE: SQL_ID 为 "7b481nzpdya3s" 的 SQL 语句执行了 450 次, 每次执行平均用时 4.2 秒。FINDING 3: 88% impact (1752 seconds)
------------------------------------
发现个别数据库段造成了大量的用户 I/O 等待。 RECOMMENDATION 1: Segment Tuning, 88% benefit (1752 seconds)
ACTION: 在 TABLE "MTK.SYNC_ORDER" (对象 ID 为 51659) 上运行 "Segment Advisor"。
RELEVANT OBJECT: database object with id 51659
ACTION: 调查涉及 TABLE "MTK.SYNC_ORDER" (对象 ID 为 51659) 的 I/O 的应用程序逻辑。
RELEVANT OBJECT: database object with id 51659
RATIONALE: 对象的 I/O 使用统计信息为: 450 完整对象扫描, 9789443 物理读取, 35 物理写入和 0 直接读取。
RATIONALE: SQL_ID 为 "7b481nzpdya3s" 的 SQL 语句在等待热对象的用户 I/O 上消耗了大量时间。
RELEVANT OBJECT: SQL statement with SQL_ID 7b481nzpdya3s
select syncorder0_.ID as ID53_, syncorder0_.MOBILE as MOBILE53_,
syncorder0_.FEE as FEE53_, syncorder0_.ORDER_TIME as ORDER4_53_,
syncorder0_.ORDER_PORT as ORDER5_53_, syncorder0_.ORDER_CMD as
ORDER6_53_, syncorder0_.TERMINAL_ID as TERMINAL7_53_,
syncorder0_.PROJECT_ID as PROJECT8_53_, syncorder0_.SP_ID as SP9_53_,
syncorder0_.STATE_REPORT as STATE10_53_, syncorder0_.LINK_ID as
LINK11_53_, syncorder0_.MID as MID53_, syncorder0_.STATE_RESULT as
STATE13_53_, syncorder0_.COMMAND_ID as COMMAND14_53_,
syncorder0_.PRODUCT_ID as PRODUCT15_53_, syncorder0_.ALLOW_PAY as
ALLOW16_53_, syncorder0_.AREACODE as AREACODE53_, syncorder0_.SP_IP
as SP18_53_, syncorder0_.CARRIER as CARRIER53_ from MTK.SYNC_ORDER
syncorder0_ where syncorder0_.LINK_ID=:1 order by
syncorder0_.ORDER_TIME desc SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: 等待类别 "用户 I/O" 消耗了大量数据库时间。 (89% impact [1763 seconds])FINDING 4: 88% impact (1745 seconds)
------------------------------------
发现个别 SQL 语句造成了大量的用户 I/O 等待。 RECOMMENDATION 1: SQL Tuning, 100% benefit (2057 seconds)
ACTION: 对 SQL_ID 为 "7b481nzpdya3s" 的 SQL 语句运行 SQL Tuning Advisor。
RELEVANT OBJECT: SQL statement with SQL_ID 7b481nzpdya3s and
PLAN_HASH 777295479
select syncorder0_.ID as ID53_, syncorder0_.MOBILE as MOBILE53_,
syncorder0_.FEE as FEE53_, syncorder0_.ORDER_TIME as ORDER4_53_,
syncorder0_.ORDER_PORT as ORDER5_53_, syncorder0_.ORDER_CMD as
ORDER6_53_, syncorder0_.TERMINAL_ID as TERMINAL7_53_,
syncorder0_.PROJECT_ID as PROJECT8_53_, syncorder0_.SP_ID as SP9_53_,
syncorder0_.STATE_REPORT as STATE10_53_, syncorder0_.LINK_ID as
LINK11_53_, syncorder0_.MID as MID53_, syncorder0_.STATE_RESULT as
STATE13_53_, syncorder0_.COMMAND_ID as COMMAND14_53_,
syncorder0_.PRODUCT_ID as PRODUCT15_53_, syncorder0_.ALLOW_PAY as
ALLOW16_53_, syncorder0_.AREACODE as AREACODE53_, syncorder0_.SP_IP
as SP18_53_, syncorder0_.CARRIER as CARRIER53_ from MTK.SYNC_ORDER
syncorder0_ where syncorder0_.LINK_ID=:1 order by
syncorder0_.ORDER_TIME desc
RATIONALE: SQL_ID 为 "7b481nzpdya3s" 的 SQL 语句执行了 450 次, 每次执行平均用时 4.2 秒。
RATIONALE: 每次执行在用户 I/O 等待事件上花费的平均时间为 3.8 秒。 SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: 等待类别 "用户 I/O" 消耗了大量数据库时间。 (89% impact [1763 seconds])FINDING 5: 16% impact (312 seconds)
-----------------------------------
实例在 CPU 上花费的时间占据了数据库时间中的大部分。 RECOMMENDATION 1: SQL Tuning, 100% benefit (2057 seconds)
ACTION: 对 SQL_ID 为 "7b481nzpdya3s" 的 SQL 语句运行 SQL Tuning Advisor。
RELEVANT OBJECT: SQL statement with SQL_ID 7b481nzpdya3s and
PLAN_HASH 777295479
select syncorder0_.ID as ID53_, syncorder0_.MOBILE as MOBILE53_,
syncorder0_.FEE as FEE53_, syncorder0_.ORDER_TIME as ORDER4_53_,
syncorder0_.ORDER_PORT as ORDER5_53_, syncorder0_.ORDER_CMD as
ORDER6_53_, syncorder0_.TERMINAL_ID as TERMINAL7_53_,
syncorder0_.PROJECT_ID as PROJECT8_53_, syncorder0_.SP_ID as SP9_53_,
syncorder0_.STATE_REPORT as STATE10_53_, syncorder0_.LINK_ID as
LINK11_53_, syncorder0_.MID as MID53_, syncorder0_.STATE_RESULT as
STATE13_53_, syncorder0_.COMMAND_ID as COMMAND14_53_,
syncorder0_.PRODUCT_ID as PRODUCT15_53_, syncorder0_.ALLOW_PAY as
ALLOW16_53_, syncorder0_.AREACODE as AREACODE53_, syncorder0_.SP_IP
as SP18_53_, syncorder0_.CARRIER as CARRIER53_ from MTK.SYNC_ORDER
syncorder0_ where syncorder0_.LINK_ID=:1 order by
syncorder0_.ORDER_TIME desc
RATIONALE: SQL_ID 为 "7b481nzpdya3s" 的 SQL 语句执行了 450 次, 每次执行平均用时 4.2 秒。
RATIONALE: 每次执行使用 CPU 的平均时间为 0.69 秒。FINDING 6: 6.3% impact (125 seconds)
------------------------------------
SGA 大小不合适, 导致附加 I/O 或硬语法分析。 RECOMMENDATION 1: DB Configuration, 6% benefit (119 seconds)
ACTION: 通过将参数 "sga_target" 设置为 1022 M, 增加 SGA 的大小。 ADDITIONAL INFORMATION:
分析期间, 参数 "sga_target" 的值为 "584 M"。 SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: 等待类别 "用户 I/O" 消耗了大量数据库时间。 (89% impact [1763 seconds])
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ADDITIONAL INFORMATION
----------------------等待类别 "应用程序" 并未消耗大量数据库时间。
等待类别 "提交" 并未消耗大量数据库时间。
等待类别 "并发" 并未消耗大量数据库时间。
等待类别 "配置" 并未消耗大量数据库时间。
等待类别 "网络" 并未消耗大量数据库时间。
会话连接和断开连接的调用并未消耗大量数据库时间。
对 SQL 语句的硬语法分析并未消耗大量数据库时间。The analysis of I/O performance is based on the default assumption that the
average read time for one database block is 10000 micro-seconds.An explanation of the terminology used in this report is available when you
run the report with the 'ALL' level of detail.
解决方案 »
- group by 效率问题
- 关于date类型的使用
- 服务器无法处理请求。 ---> 值不能为空。 在线等
- 视图问题:1,对于多表联接的视图,DML操作时,是怎么达到增删改的目的的?2,什么情况下会对视图DML,一般不是直接增删改表么?
- 想给200分的,但是最多只可以给100分。跪求动态insert语句的思路。希望可以耐心看完我的问题。
- 关于锁定数据行的问题
- 关于自身连接的问题?这样为什么结果不一样?
- 十万火急,在线等待
- 菜问题:我怎么在dba stadio里找不到外键FK在哪里啊??请问在树状结构里有么,请告之
- 在线求助高人:在oracle 如何用like 查询带%的数据
- 怎么获取本机的oracle的服务器名
- oracle 存储过程一直报调用接口出错:ORA-01422: 实际返回的行数超出请求的行数,请大侠们帮助下看哪里出问题了
什么表,看看是不是有大量死锁?