设为首页收藏本站
开启辅助访问 切换到窄版
搜索
第一问答网»论坛 › 中问网 › 问答 › 请问各位大神内文中的SQL有调优的空间吗 ...

请问各位大神内文中的SQL有调优的空间吗

[复制链接]
查看11 | 回复3 | 2016-2-18 10:08:14 | 显示全部楼层 |阅读模式
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id| Operation
| Name
| Rows| Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |

-------------------------------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT
|
| | | | 104K(100)|
| | |

| 1 |NESTED LOOPS OUTER
|
| 1 | 170 | | 104K(1)| 00:20:57 | | |

| 2 | NESTED LOOPS OUTER
|
| 1 | 153 | | 110 (1)| 00:00:02 | | |

| 3 |NESTED LOOPS OUTER
|
| 1 | 138 | | 8 (0)| 00:00:01 | | |

| 4 | NESTED LOOPS
|
| 1 | 112 | | 6 (0)| 00:00:01 | | |

| 5 |NESTED LOOPS
|
| 1 |85 | | 4 (0)| 00:00:01 | | |

|*6 | TABLE ACCESS FULL
| DSA_POLICY_SCORE| 1 |23 | | 2 (0)| 00:00:01 | | |

|*7 | TABLE ACCESS BY INDEX ROWID | DSA_CARD_ACCOUNT_DIM| 1 |62 | | 2 (0)| 00:00:01 | | |

|*8 |INDEX UNIQUE SCAN
| PK_DSA_CARD_ACCOUNT_DIM | 1 | | | 1 (0)| 00:00:01 | | |

| 9 |TABLE ACCESS BY INDEX ROWID| DSA_ACNO_DAILY_STATUS | 1 |27 | | 2 (0)| 00:00:01 | | |

|* 10 | INDEX UNIQUE SCAN
| PK_DSA_ACNO_DAILY | 1 | | | 1 (0)| 00:00:01 | | |

|11 | TABLE ACCESS BY INDEX ROWID | DSA_CARD_HOLDER_DIM | 1 |26 | | 2 (0)| 00:00:01 | | |

|* 12 |INDEX UNIQUE SCAN
| PK_DSA_CARD_HOLDER_DIM| 1 | | | 1 (0)| 00:00:01 | | |

|13 |VIEW PUSHED PREDICATE
|
| 1 |15 | | 102 (1)| 00:00:02 | | |

|14 | SORT GROUP BY
|
| 1 |32 | | 102 (1)| 00:00:02 | | |

|* 15 |HASH JOIN
|
|13 | 416 | | 102 (1)| 00:00:02 | | |

|16 | PARTITION RANGE ITERATOR
|
|13 | 325 | |95 (0)| 00:00:02 | KEY |33 |

|* 17 |TABLE ACCESS BY LOCAL INDEX ROWID| AUTH_TXLOG_HIST |13 | 325 | |95 (0)| 00:00:02 | KEY |33 |

|18 | BITMAP CONVERSION TO ROWIDS |
| | | |
|
| | |

PLAN_TABLE_OUTPUT


------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 19 |
BITMAP INDEX SINGLE VALUE| IDX_TXLOGH_02 | | | |
|
| KEY |33 |

|* 20 | MAT_VIEW ACCESS FULL
| MCC_RISK_BAK
|50 | 350 | | 6 (0)| 00:00:01 | | |

|21 | VIEW PUSHED PREDICATE
|
| 1 |17 | | 104K(1)| 00:20:56 | | |

|* 22 |HASH JOIN
|
| 1 |71 | | 104K(1)| 00:20:56 | | |

|23 | NESTED LOOPS
|
| 1 |63 | | 7 (0)| 00:00:01 | | |

|* 24 |TABLE ACCESS BY INDEX ROWID| DSA_CARD_ACCOUNT_DIM| 1 |19 | | 3 (0)| 00:00:01 | | |

|* 25 | INDEX UNIQUE SCAN
| PK_DSA_CARD_ACCOUNT_DIM | 1 | | | 2 (0)| 00:00:01 | | |

|26 |TABLE ACCESS BY INDEX ROWID| DATA_APPMAIN_C2 | 1 |44 | | 4 (0)| 00:00:01 | | |

|* 27 | INDEX RANGE SCAN
| IDX_DAMC2_ID
| 1 | | | 2 (0)| 00:00:01 | | |

|28 | VIEW
|
|2465K|18M| | 104K(1)| 00:20:55 | | |

|29 |SORT GROUP BY
|
|2465K|56M| 106M| 104K(1)| 00:20:55 | | |

|30 | TABLE ACCESS FULL
| DATA_APPMAIN_C2 |3463K|79M| | 84461 (1)| 00:16:54 | | |

-------------------------------------------------------------------------------------------------------------------------------------------
回复

使用道具 举报

千问 | 2016-2-18 10:08:14 | 显示全部楼层
SQL语句是什么？
回复

使用道具 举报

千问 | 2016-2-18 10:08:14 | 显示全部楼层
最基本的就是看一下 TABLE ACCESS FULL 是否正确，如可以建index解决就先建吧
回复

使用道具 举报

千问 | 2016-2-18 10:08:14 | 显示全部楼层
建议你从view入手，view是比较容易出现问题的
另外如果统计信息比较旧的话先收集一下
回复

使用道具 举报

返回列表 发新帖
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

千问

主题

0

回帖

4882万

积分

论坛元老

Rank: 8Rank: 8

积分
48824836
回复楼主 返回列表
热门排行