Bad sql
SELECT T3.CONFLICT_ID,
?????? T3.LAST_UPD,
?????? T3.CREATED,
?????? T3.LAST_UPD_BY,
?????? T3.CREATED_BY,
?????? T3.MODIFICATION_NUM,
?????? T3.ROW_ID,
?????? T3.ATTR_04,
?????? T3.ATTR_03,
?????? T2.VAL,
?????? T3.ATTR_01,
?????? T3.ATTR_02,
?????? T1.NAME,
?????? T2.VAL1
? FROM user.S_ORG_EXT T1, user.CX_LOOKUP T2, user.CX_POS_LOG_INFOT3
?WHERE T3.ATTR_02 = T1.PAR_ROW_ID
?? AND T3.ATTR_01 = T2.NAME1
?? AND T2.TYPE = 'PROVIDER'
?? AND (T3.TYPE = 'INFO')
?? AND (T3.ATTR_04 = :1 AND T3.ATTR_02 = :2)
?
原因分析：对CX_POS_LOG_INFO表全表扫描，其实统计信息也不准确，表的大小应该500M左右了
?
?
--------------------------------------------
| Operation????????????????????? | PHV/Object Name???? |?Rows | Bytes|?? Cost |
--------------------------------------------
|SELECT STATEMENT??????????????? |----- 1762745057 ----|?????? |?????|?? 3655 |
|NESTED LOOPS??????????????????? |???????????????????? |???? 1 |???1K|?? 3655 |
| NESTED LOOPS?????????????????? |???????????????????? |???? 1 |?109 |????? 2 |
|? TABLE ACCESS BY INDEX ROWID?? |S_ORG_EXT??????????? |???? 1 |??25 |????? 1 |
|?? INDEX UNIQUE SCAN??????????? |S_ORG_EXT_U3???????? |????1 |????? |????? 2 |
|? TABLE ACCESS BY INDEX ROWID?? |CX_LOOKUP??????????? |???? 1 |??84 |????? 1 |
|?? INDEX RANGE SCAN???????????? |CX_LOOKUP_U1???????? |????1 |????? |????? 2 |
| TABLE ACCESS FULL????????????? |CX_POS_LOG_INFO????? |????1 |??? 1K|?? 3653 |
--------------------------------------------
Statistics
----------------------
????????? 1?recursive calls
????????? 0?db block gets
????? 71207?consistent gets
????? 71187?physical reads
????????? 0?redo size
?????? 1131?bytes sent via SQL*Net to client
??????? 460?bytes received via SQL*Net from client
????????? 1?SQL*Net roundtrips to/from client
????????? 0?sorts (memory)
????????? 0?sorts (disk)
????????? 0?rows processed
?
?
解决思路：
手工添加组合索引，使查询走组合索引，查询从65秒下降到1秒，逻辑读从71207（556M）下降到21
create indexSIEBEL.IDX_CX_POS_LOG_INFO_U1 on SIEBEL.CX_POS_LOG_INFO (ATTR_02, ATTR_04)
? tablespace SIEB_IDX
?
?
?
Execution Plan
----------------------
?? 0?????SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=1278)
?? 1???0?? TABLE ACCESS (BY INDEX ROWID)OF 'CX_POS_LOG_INFO' (Cost=1 Card=1 Bytes=1169)
?? 2???1???? NESTED LOOPS (Cost=3 Card=1Bytes=1278)
?? 3???2?????? NESTED LOOPS (Cost=2Card=1 Bytes=109)
?? 4???3???????? TABLE ACCESS (BY INDEXROWID) OF 'S_ORG_EXT' (Cost=1? Card=1Bytes=25)
?? 5???4?????????? INDEX (UNIQUE SCAN) OF'S_ORG_EXT_U3' (UNIQUE) (Cost=2 Card=1)
?? 6???3???????? TABLE ACCESS (BY INDEXROWID) OF 'CX_LOOKUP' (Cost=1? Card=1Bytes=84)
?? 7???6?????????? INDEX (RANGE SCAN) OF'CX_LOOKUP_U1' (UNIQUE) (Cost=2 Card=1)
?? 8???2?????? INDEX (RANGE SCAN) OF'IDX_CX_POS_LOG_INFO_U1'? (NON-UNIQUE)(Cost=1 Card=93)
Statistics
----------------------
????????? 0?recursive calls
????????? 0?db block gets
???????? 18?consistent gets
????????? 0?physical reads
????????? 0?redo size
?????? 1448?bytes sent via SQL*Net to client
??????? 655?bytes received via SQL*Net from client
????????? 2?SQL*Net roundtrips to/from client
????????? 0?sorts (memory)
????????? 0?sorts (disk)
????????? 1?rows processed