现象描述1, 具体什么业务受到影响不清楚,但从系统测看,主机IO资源比较紧张(HPUX 11.31 +oracle 9i)
HP-UX crmdb3 B.11.23 U ia64 09/19/12
11:09:42 %usr %sys %wio %idle
11:09:45 28 5 64 3
11:09:48 28 2 61 9
11:09:51 28 2 67 3
11:09:54 33 2 57 7
11:09:57 31 2 59 7
glance看IO已接近100%
2,数据库侧看,大量db file scattered read IO相关等待事件 SID SERIAL# OSUSER USERNAME SVRPROC SQL_HASH_VALUE EVENT P1 P2 P3
------ ---------- -------- -------- ------------------------------------ -------------- ------------------------------ --------------------- ---------- ----------
89 28200 airsm ai 10261 664153718 db file scattered read 37 192750 8
159 43064 airsm ai 26996 3295997871 db file scattered read 36 60587 8
173 8048 airsm ai 3250 1002585284 db file scattered read 36 75123 8
458 18261 airsm ai 2505 2812298138 db file scattered read 36 365179 8
……..
3,等待的sql具体如下,主要原因是对ai.RM_A_x全表扫描,该表72GB大小。
SQL> @get_sql_by_hv
Enter value for hv: 1775869170
old 3: where hash_value = '&HV'
new 3: where hash_value = '1775869170'
SQL Text
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
select a.serial_num, x.c_attr_1 crypto, to_char(a.expirt_dt, 'yy
yy-mm-dd') expirt_dt, k.key_info from RM_A_x x, RM_A
a, RM_A_key_info k
where a.row_id = x.row_id (+)
and k.row_id(+) = x.n_attr_1
a
nd serial_num in ( '12475014246302465', '12475014246302485', '1
2475014246302572', '12475014246302595', '12475014246302599', '12
475014246302620', '12475014246302636', '12475014246302765')
9 rows selected.
SQL> /
Enter value for hv: 2144161010
old 3: where hash_value = '&HV'
new 3: where hash_value = '2144161010'
SQL Text
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
select a.serial_num, x.c_attr_1 crypto, to_char(a.expirt_dt, 'yy
yy-mm-dd') expirt_dt, k.key_info from RM_A_x x, RM_A
a, RM_A_key_info k
where a.row_id = x.row_id (+)
and k.row_id(+) = x.n_attr_1
a
nd serial_num in ( '12475014246306603', '12475014246306726', '1
2475014246306804')
8 rows selected.
SQL> select bytes/1024/1024 M,owner from dba_segments where segment_name ='RM_A';
M OWNER
---------- ------------------------------------------------------------------------------------------
71206 ai
SQL> @showplan_9i
Enter value for hash: 125827763
old 29: hash_value='&hash'
new 29: hash_value='125827763'
Optimizer Plan:
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Operation | PHV/Object Name | Rows | Bytes| Cost |
|SELECT STATEMENT |----- 125827763 [0]
-| | | |
|NESTED LOOPS OUTER | | | | |
| NESTED LOOPS OUTER | | | | |
| TABLE ACCESS FULL |RM_A | | | |
| TABLE ACCESS BY INDEX ROWID |RM_A_X | | | |
| INDEX UNIQUE SCAN |RM_A_X_P1 | | | |
| TABLE ACCESS BY INDEX ROWID |RM_A_KEY_INFO | | | |
| INDEX UNIQUE SCAN |RM_A_KEY_INFO_P1 | | | |
原因分析1,该表有相关索引,RM_A_M1对应SERIAL_NUM列的索引,字段类型也匹配Index Column Col Column
Name Name Pos Details
--------------- ------------------------- ---- ------------------------
RM_A_F1 BUCKET_ID 1 NUMBER(15,0)
BUCKET_ID 1 NUMBER(15,0)
RM_A_F2 INV_ID 1 NUMBER(15,0)
INV_ID 1 NUMBER(15,0)
RM_A_M1 SERIAL_NUM 1 VARCHAR2(80)
SERIAL_NUM 1 VARCHAR2(80)
2,为什么不走索引呢.原来是使用基于rule类型的优化器,RULE优化器根据FROM列表中的位置来选择驱动表,FROM列表中最后一个表被作为驱动表。SQL> select OPTIMIZER_MODE from v$sqlarea where HASH_VALUE='125827763';
OPTIMIZER_MODE
---------------------------------------------------------------------------
RULE
Table Number Empty Average Chain Average Global User Sample Date
Name of Rows Blocks Blocks Space Count Row Len Stats Stats Size MM-DD-YYYY
--------------- -------------- -------- ------------ ------- -------- ------- ------ ------ -------------- ----------
RM_A_X NO NO
SQL> select CREATED,OBJECT_NAME from dba_objects where object_name ='RM_A_X' and owner='ai';
CREATED OBJECT_NAME
---------------------------------------------------------------------------------------------------- --------------------
2012-06-26 09:52:48 RM_A_X
SQL> show parameter optimizer_mode
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
optimizer_mode string RULE
3,rbo的执行顺序如下,安装如下文章所述,IN应该属于rank 10,如果把in改成单条件=则直接走相关索引
sing the RBO, the optimizer chooses an execution plan based on the access paths available and the ranks of these access paths. Oracle's ranking of the access paths is heuristic. If there is more than one way to execute a SQL statement, then the RBO always
uses the operation with the lower rank. Usually, operations of lower rank execute faster than those associated with constructs of higher rank.
The list shows access paths and their ranking:
RBO Path 1: Single Row by Rowid
RBO Path 2: Single Row by Cluster Join
RBO Path 3: Single Row by Hash Cluster Key with Unique or Primary Key
RBO Path 4: Single Row by Unique or Primary Key
RBO Path 5: Clustered Join
RBO Path 6: Hash Cluster Key
RBO Path 7: Indexed Cluster Key
RBO Path 8: Composite Index
RBO Path 9: Single-Column Indexes
RBO Path 10: Bounded Range Search on Indexed Columns
RBO Path 11: Unbounded Range Search on Indexed Columns
RBO Path 12: Sort Merge Join
RBO Path 13: MAX or MIN of Indexed Column
RBO Path 14: ORDER BY on Indexed Column
RBO Path 15: Full Table Scan
解决方案:
由于问题比较紧急,需要尽快解决,因此使用了最简单有效的解决办法用hint,使用此方法修改sql后问题很快解决。
SQL> explain plan for
2 select /*+index(a RM_A_M1) */ a.serial_num, x.c_attr_1 crypto, to_char(a.expirt_dt, 'yyyy-mm-dd') expirt_dt, k.key_info from ai.RM_A_key_info k ,ai.RM_A_x x ,ai.RM_A
3 a where a.row_id = x.row_id (+)
4 and k.row_id(+) = x.n_attr_1 and serial_num in ( '12475014246300079', '12475014246300099');
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 6204 | 9 |
| 1 | NESTED LOOPS OUTER | | 2 | 6204 | 9 |
| 2 | NESTED LOOPS OUTER | | 2 | 4126 | 7 |
| 3 | INLIST ITERATOR | | | | |
| 4 | TABLE ACCESS BY INDEX ROWID| RM_A | 2 | 70 | 5 |
|* 5 | INDEX RANGE SCAN | RM_A_M1 | 2 | | 4 |
| 6 | TABLE ACCESS BY INDEX ROWID | RM_A_X | 1 | 2028 | 1 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 7 | INDEX UNIQUE SCAN | RM_A_X_P1 | 1 | | |
| 8 | TABLE ACCESS BY INDEX ROWID | RM_A_KEY_INFO | 1 | 1039 | 1 |
|* 9 | INDEX UNIQUE SCAN | RM_A_KEY_INFO_P1 | 1 | | |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("A"."SERIAL_NUM"='12475014246300079' OR
"A"."SERIAL_NUM"='12475014246300099')
7 - access("A"."ROW_ID"="X"."ROW_ID"(+))
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
9 - access("K"."ROW_ID"(+)="X"."N_ATTR_1")
Note: cpu costing is off
25 rows selected.
参考资料
http://docs.oracle.com/cd/B10501_01/server.920/a96533/rbo.htm#39232
分享到:
相关推荐
Io流上传图片到数据库
而问题定位分析通常情况下,最优先排查的是监控服务器资源利用率,例如先用TOP 或者nmon等查看CPU、内存使用情况,然后在排查IO问题,例如网络IO、磁盘IO的问题。 如果是磁盘IO问题,一般问题是SQL语法问题、MYSQL...
Oracle数据库服务器IO高的分析方案.docx
Oracle数据库优化之数据库磁盘IO! 值得下载看看!资源免费,大家分享!!
oracle基本知识+内存分析+数据库IO深入分析3个pdf文件,受益无穷
用oracle自带的集中工具来判断数据库IO是否慢,进而为下一步优化工作提供依据
Oracle数据库IO深入分析 Oracle数据库IO深入分析 Oracle数据库IO深入分析 Oracle数据库IO深入分析
Java初学者IO模拟数据库实现图书管理项目: 1.图书,管理员,读者数据怎删改查; 2.实现自动借书,还书,已经添加当前时间; 3.借书证办理; 3.不同权限实现不同功能;
对于初学者来说,学习完UI组件,就要学习安卓一些基本组件与机制了。。
主要为大家分享监测windows主机IO利用率的脚本代码,需要的朋友可以参考一下
rubywork ruby编程例子 逻辑 IO 数据库rubywork ruby编程例子 逻辑 IO 数据库 rubywork ruby编程例子 逻辑 IO 数据库rubywork ruby编程例子 逻辑 IO 数据库rubywork ruby编程例子 逻辑 IO 数据库
Oracle数据库服务器IO高的分析方案和案例探讨.pdf
很给力的IO按键扫描电路,老外的5个IO可以驱动25个按键,很给力的IO按键扫描电路,老外的5个IO可以驱动25个按键,很给力的IO按键扫描电路,老外的5个IO可以驱动25个按键,很给力的IO按键扫描电路,老外的5个IO可以...
IO地址分配表
次为章力民Visual C# 2005 文件IO和数据存取秘诀中的数据库部分,下载后解压即刻使用,但要下载数据库二,把四个部分放在一块解压为北风贸易数据库
在做项目(工程)的时候,我们经常要用到比较多的按键,而且IO资源紧张,于是我们就想方设法地在别的模块中节省IO口,好不容易挤出一两个IO口,却发现仍然不够用,实在没办法了就添加一个IC来扫键。一个IC虽然价格不...
一直以来,数据库架构实践、数据库优化应用等,是备受大家关注的传统话题,本届大会仍将继续邀请一批国内顶尖的技术专家来进行分享,包括Oracle应用实践、MySQL应用实践、SQL Server应用实践等。
这种数据的特点是:只读不写,数据量相当大,一旦查询大结果集的数据时,对数据库的IO,内存缓存占用相当大,会严重影响同一个数据库的其他会话的操作,表现为整个数据库反应迟缓,业务功能不可用。采用复制技术后,...