标签云
asm 恢复 asm恢复 bbed bootstrap$ dul In Memory kcbzib_kcrsds_1 kccpb_sanity_check_2 kfed MySQL恢复 ORA-00312 ORA-00607 ORA-00704 ORA-01110 ORA-01555 ORA-01578 ORA-08103 ORA-600 2662 ORA-600 2663 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 ORACLE恢复 Oracle 恢复 ORACLE数据库恢复 oracle 比特币 OSD-04016 YOUR FILES ARE ENCRYPTED 勒索恢复 比特币加密文章分类
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (100)
- 数据库 (1,598)
- DB2 (22)
- MySQL (70)
- Oracle (1,463)
- Data Guard (49)
- EXADATA (7)
- GoldenGate (21)
- ORA-xxxxx (158)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (13)
- ORACLE 21C (3)
- Oracle ASM (65)
- Oracle Bug (7)
- Oracle RAC (47)
- Oracle 安全 (6)
- Oracle 开发 (27)
- Oracle 监听 (27)
- Oracle备份恢复 (530)
- Oracle安装升级 (84)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (75)
- PostgreSQL (18)
- PostgreSQL恢复 (6)
- SQL Server (27)
- SQL Server恢复 (8)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (36)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (19)
-
最近发表
- PostgreSQL解析wal日志之—walminer
- Oracle 19c/21c最新patch信息-202404
- PostgreSQL恢复系列:pg_filedump批量处理
- PostgreSQL部分主要字典信息
- PostgreSQL恢复系列:pg_filedump恢复字典构造
- PostgreSQL 16 源码安装
- ORA-00742 ORA-00312 恢复
- 数据库open成功后报ORA-00353 ORA-00354错误引起的一系列问题(本质ntfs文件系统异常)
- ORA-600 ktsiseginfo1故障
- ORA-00600: internal error code, arguments: [16703], [1403], [4] 原因
- 最近遇到几起ORA-600 16703故障(tab$被清空),请引起重视
- ORA-600 2662快速恢复之Patch scn工具
- TNS-12518: TNS:listener could not hand off client connection
- ora.storage无法启动报ORA-12514故障处理
- 断电引起文件scn异常数据库恢复
- ORA-16188: LOG_ARCHIVE_CONFIG settings inconsistent with previously started instance
- .[hudsonL@cock.li].mkp勒索加密数据库完美恢复
- 模拟带库实现rman远程备份
- 又一例:ORA-600 kclchkblk_4和2662故障
- Oracle误删除数据文件恢复
月归档:十二月 2011
恢复被rm意外删除数据文件
一.模拟数据文件删除
[oracle@node1 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Sat Dec 31 22:00:52 2011 Copyright (c) 1982, 2011, Oracle. All rights reserved. --数据库版本 Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options --所有数据文件 SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /opt/oracle/oradata/ora11g/system01.dbf /opt/oracle/oradata/ora11g/sysaux01.dbf /opt/oracle/oradata/ora11g/undotbs01.dbf /opt/oracle/oradata/ora11g/users01.dbf /opt/oracle/oradata/ora11g/example01.dbf --删除example01.dbf数据文件 SQL> !rm /opt/oracle/oradata/ora11g/example01.dbf SQL> !ls -l /opt/oracle/oradata/ora11g/example01.dbf ls: /opt/oracle/oradata/ora11g/example01.dbf: 没有那个文件或目录 --因为数据文件被删除,创建表失败 SQL> create table t_xifenfei tablespace example 2 as select * from dba_tables; as select * from dba_tables * ERROR at line 2: ORA-01116: error in opening database file 5 ORA-01110: data file 5: '/opt/oracle/oradata/ora11g/example01.dbf' ORA-27041: unable to open file Linux-x86_64 Error: 2: No such file or directory Additional information: 3
二.找回数据文件
--查找dbw进程spid [oracle@node1 ~]$ ps -ef|grep dbw|grep -v grep oracle 18387 1 0 Dec22 ? 00:00:12 ora_dbw0_ora11g --查看该进程所有文件句柄 [oracle@node1 ~]$ ll /proc/18387/fd 总计 0 lr-x------ 1 oracle oinstall 64 12-31 22:03 0 -> /dev/null l-wx------ 1 oracle oinstall 64 12-31 22:03 1 -> /dev/null lr-x------ 1 oracle oinstall 64 12-31 22:03 10 -> /dev/zero lr-x------ 1 oracle oinstall 64 12-31 22:03 11 -> /dev/zero lr-x------ 1 oracle oinstall 64 12-31 22:03 12 -> /opt/oracle/product/11.2.0/db_1/rdbms/mesg/orazhs.msb lrwx------ 1 oracle oinstall 64 12-31 22:03 13 -> /opt/oracle/product/11.2.0/db_1/dbs/hc_ora11g.dat lr-x------ 1 oracle oinstall 64 12-31 22:03 14 -> /proc/18387/fd lr-x------ 1 oracle oinstall 64 12-31 22:03 15 -> /dev/zero lr-x------ 1 oracle oinstall 64 12-31 22:03 16 -> /opt/oracle/product/11.2.0/db_1/rdbms/mesg/oraus.msb lrwx------ 1 oracle oinstall 64 12-31 22:03 17 -> /opt/oracle/product/11.2.0/db_1/dbs/hc_ora11g.dat lrwx------ 1 oracle oinstall 64 12-31 22:03 18 -> /opt/oracle/product/11.2.0/db_1/dbs/lkORA11G lr-x------ 1 oracle oinstall 64 12-31 22:03 19 -> /opt/oracle/product/11.2.0/db_1/rdbms/mesg/orazhs.msb l-wx------ 1 oracle oinstall 64 12-31 22:03 2 -> /dev/null lr-x------ 1 oracle oinstall 64 12-31 22:03 20 -> /opt/oracle/product/11.2.0/db_1/rdbms/mesg/oraus.msb lrwx------ 1 oracle oinstall 64 12-31 22:03 21 -> socket:[441562] lrwx------ 1 oracle oinstall 64 12-31 22:03 256 -> /opt/oracle/oradata/ora11g/control01.ctl lrwx------ 1 oracle oinstall 64 12-31 22:03 257 -> /opt/oracle/oradata/ora11g/system01.dbf lrwx------ 1 oracle oinstall 64 12-31 22:03 258 -> /opt/oracle/oradata/ora11g/sysaux01.dbf lrwx------ 1 oracle oinstall 64 12-31 22:03 259 -> /opt/oracle/oradata/ora11g/undotbs01.dbf lrwx------ 1 oracle oinstall 64 12-31 22:03 260 -> /opt/oracle/oradata/ora11g/users01.dbf lrwx------ 1 oracle oinstall 64 12-31 22:03 261 -> /opt/oracle/oradata/ora11g/example01.dbf (deleted) lrwx------ 1 oracle oinstall 64 12-31 22:03 262 -> /opt/oracle/oradata/ora11g/temp01.dbf lr-x------ 1 oracle oinstall 64 12-31 22:03 3 -> /dev/null lr-x------ 1 oracle oinstall 64 12-31 22:03 4 -> /dev/null lrwx------ 1 oracle oinstall 64 12-31 22:03 5 -> /opt/oracle/product/11.2.0/db_1/dbs/hc_ora11g.dat lr-x------ 1 oracle oinstall 64 12-31 22:03 6 -> /dev/null lr-x------ 1 oracle oinstall 64 12-31 22:03 7 -> /dev/null lr-x------ 1 oracle oinstall 64 12-31 22:03 8 -> /dev/null lr-x------ 1 oracle oinstall 64 12-31 22:03 9 -> /dev/null --通过句柄恢复数据文件[被删除数据文件会被标示(deleted)] [oracle@node1 ~]$ cp /proc/18387/fd/261 /opt/oracle/oradata/ora11g/example01.dbf --确认该数据文件已经恢复成功 [oracle@node1 ~]$ ll /opt/oracle/oradata/ora11g/example01.dbf -rw-r----- 1 oracle oinstall 362422272 12-31 22:05 /opt/oracle/oradata/ora11g/example01.dbf
三.数据文件online
SQL> alter database datafile 5 offline; Database altered. SQL> recover datafile 5; Media recovery complete. SQL> alter database datafile 5 online; Database altered. SQL> create table t_xifenfei tablespace example 2 as select * from dba_tables; Table created.
四.补充说明
在意外使用os命令删除掉数据文件时,千万不要慌张重启数据库或者操作系统,可以通过dbwn进程相关句柄找回数据文件
发表在 Linux, Oracle备份恢复
7 条评论
在UltraEdit中定位数据文件内容
一、定位数据块
1.bbed查看数据块
--第一个数据块 BBED> set block 1 BLOCK# 1 BBED> dump count 16 File: /opt/oracle/oradata/chf/example01.dbf (0) Block: 1 Offsets: 0 to 15 Dba:0x00000000 ------------------------------------------------------------------------ 0ba20000 01004001 00000000 00000104 <32 bytes per line> BBED> dump count 128 File: /opt/oracle/oradata/chf/example01.dbf (0) Block: 1 Offsets: 0 to 127 Dba:0x00000000 ------------------------------------------------------------------------ 0ba20000 01004001 00000000 00000104 387a0000 00000000 0003200b 74684acd 43484600 00000000 7d4a0000 00320000 00200000 05000300 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 d28a0900 00000000 1ab5d72c f7b4d72c c5320900 00000000 00000000 <32 bytes per line> --第二个数据块 BBED> set block 2 BLOCK# 2 BBED> dump count 128 File: /opt/oracle/oradata/chf/example01.dbf (0) Block: 2 Offsets: 0 to 127 Dba:0x00000000 ------------------------------------------------------------------------ 1da20000 02004001 08dee400 00000204 f6b80000 05000000 08000000 00320000 09000000 50000000 feff3f00 07000000 00320000 7f000000 fa010000 07dee400 00000000 00000000 00000000 00000000 b1220000 08000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 <32 bytes per line> --第100个数据块 BBED> set block 100 BLOCK# 100 BBED> dump count 128 File: /opt/oracle/oradata/chf/example01.dbf (0) Block: 100 Offsets: 0 to 127 Dba:0x00000000 ------------------------------------------------------------------------ 06a20000 64004001 343f0900 00000104 a38d0000 02002000 e1c90000 343f0900 0000e81f 021f3200 61004001 00000000 00000000 00000000 00000000 00000000 00000000 05000900 2c010000 52008000 c8005900 00800000 a3350900 00000000 00000000 00008001 00000000 6b00fa00 5e1a6419 00000000 00000000 00000000 <32 bytes per line>
2.UltraEdit查看数据块
--第0个数据块 --(为了便于和bbed的block一致,称为0比较合适,因为这个块在bbed中看不到) 00000000h: 00 A2 00 00 00 00 C0 FF 00 00 00 00 00 00 00 00 ; .?...?........ 00000010h: 66 C8 00 00 00 20 00 00 00 32 00 00 7D 7C 7B 7A ; f?.. ...2..}|{z 00000020h: A0 81 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ; 爜.............. 00000030h: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ; ................ …………………………………………………… 00001fa0h: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ; ................ 00001fb0h: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ; ................ 00001fc0h: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ; ................ 00001fd0h: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ; ................ 00001fe0h: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ; ................ 00001ff0h: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ; ................ --第一个数据块(bbed中的block 1) 00002000h: 0B A2 00 00 01 00 40 01 00 00 00 00 00 00 01 04 ; .?...@......... 00002010h: 38 7A 00 00 00 00 00 00 00 03 20 0B 74 68 4A CD ; 8z........ .thJ? …………………………………………………………… 000021c0h: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ; ................ 000021d0h: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ; ................ --第二个数据块(bbed中的block 2) 00004000h: 1D A2 00 00 02 00 40 01 08 DE E4 00 00 00 02 04 ; .?...@..掬..... 00004010h: F6 B8 00 00 05 00 00 00 08 00 00 00 00 32 00 00 ; 龈...........2.. 00004020h: 09 00 00 00 50 00 00 00 FE FF 3F 00 07 00 00 00 ; ....P...??..... 00004030h: 00 32 00 00 7F 00 00 00 FA 01 00 00 07 DE E4 00 ; .2.....?...掬. 00004040h: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ; ................ --第100个数据块(bbed中的block 100) 000c8000h: 06 A2 00 00 64 00 40 01 34 3F 09 00 00 00 01 04 ; .?.d.@.4?...... 000c8010h: A3 8D 00 00 02 00 20 00 E1 C9 00 00 34 3F 09 00 ; .... .嵘..4?.. 000c8020h: 00 00 E8 1F 02 1F 32 00 61 00 40 01 00 00 00 00 ; ..?..2.a.@..... 000c8030h: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ; ................ 000c8040h: 00 00 00 00 05 00 09 00 2C 01 00 00 52 00 80 00 ; ........,...R.€. 000c8050h: C8 00 59 00 00 80 00 00 A3 35 09 00 00 00 00 00 ; ?Y..€..?...... 000c8060h: 00 00 00 00 00 00 80 01 00 00 00 00 6B 00 FA 00 ; ......€.....k.? 000c8070h: 5E 1A 64 19 00 00 00 00 00 00 00 00 00 00 00 00 ; ^.d.............
3.结论:因为2000(16进制)=8192(10进制),而每行又是16,所以UltraEdit定位块的规则是
SELECT to_char(’2*块数’,’xxxxxxx’) xff FROM dual;(xff*1000)
SQL> SELECT TO_number('2000','xxxxxx') FROM dual; TO_NUMBER('2000','XXXXXX') -------------------------- 8192
二、UltraEdit定位块内内容
1.bbed查看内容
--第一块偏移量为484数据 BBED> set block 1 BLOCK# 1 BBED> set offset 484 OFFSET 484 BBED> dump count 128 File: /opt/oracle/oradata/chf/example01.dbf (0) Block: 1 Offsets: 484 to 611 Dba:0x00000000 ------------------------------------------------------------------------ c052e700 00000000 ec82ba2d 01000000 2e060000 39020000 10008984 02000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 <32 bytes per line> --第一块偏移量为8188数据 BBED> dump offset 8188 count 128 File: /opt/oracle/oradata/chf/example01.dbf (0) Block: 1 Offsets: 8188 to 8191 Dba:0x00000000 ------------------------------------------------------------------------ 010b0000 <32 bytes per line>
2.UltraEdit中内容
--offset 484(kscnbas) 000021e0h: 00 00 00 00 C0 52 E7 00 00 00 00 00 EC 82 BA 2D ; ....繰?....靷? 000021f0h: 01 00 00 00 2E 06 00 00 39 02 00 00 10 00 89 84 ; ........9.....墑 00002200h: 02 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ; ................ --offset 8188(tailchk) 00003ff0h: 00 00 00 00 00 00 00 00 00 00 00 00 01 0B 00 00 ; ................
3.结论:10进制的便宜量转换为16进制,然后对照你块开始行号+偏移量(16进制),得到对应偏移量开始位置
SQL> SELECT to_char('484','xxxxxxx') FROM dual; TO_CHAR('484','X ---------------- 1e4 SQL> SELECT to_char('8188','xxxxxxx') FROM dual; TO_CHAR('8188',' ---------------- 1ffc
三.补充说明
1.本实验是拷贝linux下11g数据文件到win上使用UltraEdit操作得出
2.使用UltraEdit,需要熟悉对一些关键数据的偏移量比较清楚
发表在 Oracle
评论关闭
记一次含AND-EQUAL执行计划调优
1.数据库版本
SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production PL/SQL Release 9.2.0.4.0 - Production CORE 9.2.0.3.0 Production TNS for Linux: Version 9.2.0.4.0 - Production NLSRTL Version 9.2.0.4.0 - Production
2.发现含有AND-EQUAL执行计划
SQL> SELECT 2 COUNT(TABXNPRESM1_.DETAIL_ID) AS X0_0_ 3 FROM QXTDEV1.TAB_XN_PRESMS TABXNPRESM0_, QXTDEV1.TAB_XN_PRESMS_DETAIL TABXNPRESM1_ 4 WHERE (TABXNPRESM0_.COMPANY_ID = 346240) 5 AND (TABXNPRESM0_.EMPLOYEE_ID = 0) 6 AND (TABXNPRESM0_.PRE_TIME >= TO_DATE('2011/12/25', 'yyyy/mm/dd')) 7 AND (TABXNPRESM0_.PRE_TIME < TO_DATE('2011/12/26', 'yyyy/mm/dd') + 1) 8 AND ((TABXNPRESM0_.SEND_TYPE = 1) OR (TABXNPRESM0_.SEND_TYPE = 0)) 9 AND ((TABXNPRESM1_.RESULT = -1) OR (TABXNPRESM1_.RESULT = 0)) 10 AND (TABXNPRESM0_.PRE_ID = TABXNPRESM1_.PRE_ID); Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=11 Card=1 Bytes=40) 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'TAB_XN_PRESMS_DETAIL'(Cost=3 Card=2 Bytes=30) 3 2 NESTED LOOPS (Cost=11 Card=1 Bytes=40) 4 3 TABLE ACCESS (BY INDEX ROWID) OF 'TAB_XN_PRESMS' (Cost=8 Card=1 Bytes=25) 5 4 AND-EQUAL 6 5 INDEX (RANGE SCAN) OF 'IDX_XN_PRESMS_2' (NON-UNIQUE) 7 5 INDEX (RANGE SCAN) OF 'IDX_XN_PRESMS_1' (NON-UNIQUE) (Cost=4 Card=638) 8 3 INDEX (RANGE SCAN) OF 'IDX_XN_PRESMS_DETAIL' (NON-UNIQUE) (Cost=2 Card=2) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 272188 consistent gets 0 physical reads 0 redo size 375 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
3.关于AND-EQUAL解释
If the WHERE clauses uses columns of many single-column indexes, then Oracle executes the statement by performing a range scan on each index to retrieve the rowids of the rows that satisfy each condition. Oracle then merges the sets of rowids to obtain a set of rowids of rows that satisfy all conditions. Oracle then accesses the table using these rowids.
Oracle can merge up to five indexes. If the WHERE clause uses columns of more than five single-column indexes, then Oracle merges five of them, accesses the table by rowid, and then tests the resulting rows to determine whether they satisfy the remaining conditions before returning them.
大概的意思是当where条件后面含有多个列的单列索引时(不超过5个),会先得到每个条件的rowid,然后这些rowid进行merges,得到一个rowid的结果集,最后根据这些rowid取表中记录。
4.表/列/索引相关信息
--index和列信息 SQL> SELECT INDEX_NAME, TABLE_NAME, COLUMN_NAME 2 FROM USER_IND_COLUMNS 3 WHERE TABLE_NAME IN ('TAB_XN_PRESMS', 'TAB_XN_PRESMS_DETAIL'); INDEX_NAME TABLE_NAME COLUMN_NAME ------------------------------ ------------------------------ -------------------- IDX_XN_PRESMS_1 TAB_XN_PRESMS COMPANY_ID IDX_XN_PRESMS_2 TAB_XN_PRESMS EMPLOYEE_ID IDX_XN_PRESMS_3 TAB_XN_PRESMS PRE_TIME PK_TAB_XN_PRESMS TAB_XN_PRESMS PRE_ID IDX_XN_PRESMS_4 TAB_XN_PRESMS SEND_TYPE IDX_XN_PRESMS_DETAIL TAB_XN_PRESMS_DETAIL PRE_ID IDX_XN_PRESMS_DETAIL_2 TAB_XN_PRESMS_DETAIL SEND_TIME PK_TAB_XN_PRESMS_DETAIL TAB_XN_PRESMS_DETAIL DETAIL_ID 8 rows selected --index的统计信息 SQL> SELECT TABLE_NAME, 2 INDEX_NAME, 3 TO_CHAR(LAST_ANALYZED, 'yyyy-mm-dd hh24:mi:ss') 4 FROM USER_INDEXES 5 WHERE TABLE_NAME IN ('TAB_XN_PRESMS', 'TAB_XN_PRESMS_DETAIL'); TABLE_NAME INDEX_NAME TO_CHAR(LAST_ANALYZED,'YYYY-MM ------------------------------ ------------------------------ ------------------------------ TAB_XN_PRESMS IDX_XN_PRESMS_1 2011-12-29 09:25:32 TAB_XN_PRESMS IDX_XN_PRESMS_2 2011-12-29 09:25:35 TAB_XN_PRESMS IDX_XN_PRESMS_3 2011-12-29 09:25:39 TAB_XN_PRESMS IDX_XN_PRESMS_4 2011-12-29 09:25:21 TAB_XN_PRESMS_DETAIL IDX_XN_PRESMS_DETAIL 2011-12-29 09:20:03 TAB_XN_PRESMS_DETAIL IDX_XN_PRESMS_DETAIL_2 2011-12-29 09:20:01 TAB_XN_PRESMS PK_TAB_XN_PRESMS 2011-12-29 09:25:46 TAB_XN_PRESMS_DETAIL PK_TAB_XN_PRESMS_DETAIL 2011-12-29 09:20:02 8 rows selected --列的唯一度情况 SQL> SELECT TABLE_NAME, COLUMN_NAME, NUM_DISTINCT 2 FROM USER_TAB_COLS 3 WHERE (TABLE_NAME, COLUMN_NAME) IN 4 (SELECT TABLE_NAME, COLUMN_NAME 5 FROM USER_IND_COLUMNS 6 WHERE TABLE_NAME IN ('TAB_XN_PRESMS', 'TAB_XN_PRESMS_DETAIL')) 7 ORDER BY table_name,NUM_DISTINCT DESC; TABLE_NAME COLUMN_NAME NUM_DISTINCT ------------------------------ ------------------------------ ------------ TAB_XN_PRESMS PRE_ID 1999270 TAB_XN_PRESMS PRE_TIME 1342594 TAB_XN_PRESMS EMPLOYEE_ID 10676 TAB_XN_PRESMS COMPANY_ID 3136 TAB_XN_PRESMS SEND_TYPE 10 TAB_XN_PRESMS_DETAIL DETAIL_ID 3863184 TAB_XN_PRESMS_DETAIL PRE_ID 1996872 TAB_XN_PRESMS_DETAIL SEND_TIME 437526 8 rows selected
通过这些信息可以得出:
1)统计信息是最新收集过的
2)因为有多个单列index,数据库为了使得cost最小,可能选择了不合适的index(IDX_XN_PRESMS_2[EMPLOYEE_ID]/IDX_XN_PRESMS_1[COMPANY_ID]),使得出现AND-EQUAL,从而逻辑读偏高。对于这个sql,应该使用唯一度比较高的IDX_XN_PRESMS_3[PRE_TIME]
3)也可以通过修改index,实现程序高效,但是考虑到会影响启动程序,在没有十足的把握之前遵守hint优先原则
5.增加hint提示
SQL> SELECT /*+ index(TABXNPRESM0_ IDX_XN_PRESMS_3) */ 2 COUNT(TABXNPRESM1_.DETAIL_ID) AS X0_0_ 3 FROM QXTDEV1.TAB_XN_PRESMS TABXNPRESM0_, QXTDEV1.TAB_XN_PRESMS_DETAIL TABXNPRESM1_ 4 WHERE (TABXNPRESM0_.COMPANY_ID = 346240) 5 AND (TABXNPRESM0_.EMPLOYEE_ID = 0) 6 AND (TABXNPRESM0_.PRE_TIME >= TO_DATE('2011/12/25', 'yyyy/mm/dd')) 7 AND (TABXNPRESM0_.PRE_TIME < TO_DATE('2011/12/26', 'yyyy/mm/dd') + 1) 8 AND ((TABXNPRESM0_.SEND_TYPE = 1) OR (TABXNPRESM0_.SEND_TYPE = 0)) 9 AND ((TABXNPRESM1_.RESULT = -1) OR (TABXNPRESM1_.RESULT = 0)) 10 AND (TABXNPRESM0_.PRE_ID = TABXNPRESM1_.PRE_ID); Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=641 Card=1 Bytes=40) 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'TAB_XN_PRESMS_DETAIL'(Cost=3 Card=2 Bytes=30) 3 2 NESTED LOOPS (Cost=641 Card=1 Bytes=40) 4 3 TABLE ACCESS (BY INDEX ROWID) OF 'TAB_XN_PRESMS' (Cost=638 Card=1 Bytes=25) 5 4 INDEX (RANGE SCAN) OF 'IDX_XN_PRESMS_3' (NON-UNIQUE) (Cost=63 Card=22286) 6 3 INDEX (RANGE SCAN) OF 'IDX_XN_PRESMS_DETAIL' (NON-UNIQUE) (Cost=2 Card=2) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 1422 consistent gets 0 physical reads 0 redo size 375 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
从这里可以看出,程序的逻辑读下降了很多(从272188下降到1422),得到了优化效果,提高了程序执行效率
结合上篇:BITMAP CONVERSION FROM ROWIDS,总结一个经验,如果同时使用到了一个表的多个index,效率一般情况下不会太高。同时也给各位提个醒,index并非越多越好,有时候会适得其反(建立index时需要考虑情况)
发表在 Oracle 开发
评论关闭