0 积分	1 好友	4 主题

发消息

[RAC性能调优] EXISTS主表全表扫描优化

1^#

发表于 2015-3-5 14:09:57 | 查看: 4824| 回复: 6

db version 11.2.0.3
os：aix

SQL_ID 00pspjfdy328d
--------------------
DELETE FROM DDS.CUP_ACCOUNT T1 WHERE EXISTS( SELECT 1    FROM
ODS.CUP_ACCOUNT T2 WHERE T2.BDP_ETL_DATE = '20150201'    AND
T1.ACCT_NMBR = SUBSTR(T2.CARD_NMBR,1,13)    AND T1.CURR_CODE =
TRIM(T2.CURR_CODE) )

Plan hash value: 2786671412
----------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name                         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time    |
----------------------------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT    |                            |    |    |    | 199K(100)|       |
| 1 |  DELETE             | CUP_ACCOUNT                |    |    |    |          |       |
| 2 | HASH JOIN RIGHT SEMI |                            |  2172K| 93M| 90M| 199K (11)| 00:13:44 |
| 3 | INDEX FAST FULL SCAN| IDX_CUP_ACCOUNT_EDT_CNMBR_CCD |  2445K| 62M|    |  1174  (51)| 00:00:05 |
| 4 | TABLE ACCESS FULL | CUP_ACCOUNT                | 13M| 238M|    | 193K (11)| 00:13:19 |
----------------------------------------------------------------------------------------------------------------

像上述sql，外表有可能走索引吗？全表扫消耗很大

分享0

收藏0 回复只看该作者道具举报

江南的诗

2^#

发表于 2015-3-5 14:13:14

系统内大量的类似语句，全是全表扫，io压力很大

回复只看该作者道具举报

Liu Maclean(刘相兵

3^#

发表于 2015-3-5 14:15:03

SELECT count(*) FROM
ODS.CUP_ACCOUNT T2 WHERE T2.BDP_ETL_DATE = '20150201' ;

执行
set linesize 200 pagesize 1400
@?/rdbms/admin/sqltrpt

输入 00pspjfdy328d

给出输出的结果

回复只看该作者道具举报

江南的诗

4^#

发表于 2015-3-5 14:17:12

收集了sqlhc

sqlhc_20150304_114844_00pspjfdy328d.zip

395.58 KB, 下载次数: 1226

回复只看该作者道具举报

Liu Maclean(刘相兵

5^#

发表于 2015-3-5 14:24:48

10 TABLE ODS.CUP_ACCOUNT Table lacks CBO Statistics. Consider gathering table statistics using DBMS_STATS.GATHER_TABLE_STATS. See 465787.1.
11 TABLE ODS.CUP_ACCOUNT Table has 2 CBO statistics extension(s). Review table statistics extensions. Extensions can be used for expressions or column groups.

回复只看该作者道具举报

江南的诗

6^#

发表于 2015-3-5 14:40:00

现在我没法拿到BDP_ETL_DATE 的distinct值，假如BDP_ETL_DATE分布倾斜或者不倾斜，这样的sql有优化的余地吗

回复只看该作者道具举报

Liu Maclean(刘相兵

7^#

发表于 2015-3-5 14:59:25

ODS.CUP_ACCOUNT 这个没有统计信息，不先收集下？

回复只看该作者道具举报

返回列表

		自动登录	找回密码
密码			注册