Oracle数据库数据恢复、性能优化

找回密码
注册
搜索
热搜: 活动 交友 discuz
Oracle数据库数据恢复、性能优化»论坛 › Oracle › Oracle数据库管理 › 超高的consistent gets，10分钟才运行完，求大神们帮忙 ...
发新帖
clot09

0

积分

1

好友

6

主题

[RAC性能调优] 超高的consistent gets，10分钟才运行完，求大神们帮忙

1#
发表于 2013-9-3 17:39:21 | 查看: 4107| 回复: 6
本帖最后由 clot09 于 2013-9-3 17:39 编辑

linux Release:        2.6.32.17
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
  1. SQL> set autot traceonly
  2. SQL> select d.id, d.publish_object_id, to_char(phc.assetid)
  3.   2     from content_iptv_physicalchannel phc,
  4.   3          content_iptv_schedule        sc,
  5.   4          vod_cms_publish_seq_detail_bak   d
  6.   5    where exists (SELECT 1 FROM content_base cb where cb.activeid=phc.id and cb.assetid = phc.assetid)
  7.   6      and phc.channelassetid = sc.channelassetid
  8.   7      and phc.assetstatus =3
  9.   8      and sc.id = d.publish_object_id
  10.   9      and d.data_type = 30
  11. 10      and d.delete_asset_type = 40
  12. 11      and d.parent_id = 126187112
  13. 12     order by d.publish_object_id;

  15. 910710 rows selected.


  18. Execution Plan
  19. ----------------------------------------------------------
  20. Plan hash value: 1777070399

  22. ------------------------------------------------------------------------------------------------------------------
  23. | Id  | Operation                       | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
  24. ------------------------------------------------------------------------------------------------------------------
  25. |   0 | SELECT STATEMENT                |                                |     1 |    71 |    28   (4)| 00:00:01 |
  26. |   1 |  SORT ORDER BY                  |                                |     1 |    71 |    28   (4)| 00:00:01 |
  27. |   2 |   NESTED LOOPS SEMI             |                                |     1 |    71 |    27   (0)| 00:00:01 |
  28. |   3 |    NESTED LOOPS                 |                                |     4 |   236 |    26   (0)| 00:00:01 |
  29. |   4 |     NESTED LOOPS                |                                |     1 |    40 |     2   (0)| 00:00:01 |
  30. |*  5 |      TABLE ACCESS BY INDEX ROWID| VOD_CMS_PUBLISH_SEQ_DETAIL_BAK |     1 |    28 |     1   (0)| 00:00:01 |
  31. |*  6 |       INDEX RANGE SCAN          | SEQ_DETAILB_PARENTDATATYPE_IDX |     1 |       |     1   (0)| 00:00:01 |
  32. |   7 |      TABLE ACCESS BY INDEX ROWID| CONTENT_IPTV_SCHEDULE          |     1 |    12 |     1   (0)| 00:00:01 |
  33. |*  8 |       INDEX UNIQUE SCAN         | PK_CONTENT_IPTV_SCHEDULE       |     1 |       |     1   (0)| 00:00:01 |
  34. |   9 |     TABLE ACCESS BY INDEX ROWID | CONTENT_IPTV_PHYSICALCHANNEL   |   300 |  5700 |    24   (0)| 00:00:01 |
  35. |* 10 |      INDEX RANGE SCAN           | IDX_PHYSICALCHANNEL_NEW        |   300 |       |     1   (0)| 00:00:01 |
  36. |* 11 |    INDEX RANGE SCAN             | IDX_CONTENT_BASE_ASSETACTIVEID |     1 |    12 |     1   (0)| 00:00:01 |
  37. ------------------------------------------------------------------------------------------------------------------

  39. Predicate Information (identified by operation id):
  40. ---------------------------------------------------

  42.    5 - filter("D"."DELETE_ASSET_TYPE"=40)
  43.    6 - access("D"."PARENT_ID"=126187112 AND "D"."DATA_TYPE"=30)
  44.    8 - access("SC"."ID"="D"."PUBLISH_OBJECT_ID")
  45.   10 - access("PHC"."CHANNELASSETID"="SC"."CHANNELASSETID" AND "PHC"."ASSETSTATUS"=3)
  46.   11 - access("CB"."ASSETID"="PHC"."ASSETID" AND "CB"."ACTIVEID"="PHC"."ID")


  49. Statistics
  50. ----------------------------------------------------------
  51.           0  recursive calls
  52.           0  db block gets
  53.   146210796  consistent gets
  54.          89  physical reads
  55.           0  redo size
  56.    30733018  bytes sent via SQL*Net to client
  57.      668335  bytes received via SQL*Net from client
  58.       60715  SQL*Net roundtrips to/from client
  59.           1  sorts (memory)
  60.           0  sorts (disk)
  61.      910710  rows processed
复制代码
content_iptv_physicalchannel   ---90073行
content_base    ---1429183行
content_iptv_schedule--    188095
vod_cms_publish_seq_detail_bak  ---  2035297


表的ddl在附件中


---做了个level 1 的10046
（后来做的，consistent gets稍微有点区别）

PARSE #3:c=0,e=62,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1345890593930718
EXEC #3:c=0,e=304,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,tim=1345890593931209
STAT #2 id=1 cnt=910710 pid=0 pos=1 obj=0 op='SORT ORDER BY (cr=1,4621,0797 pr=29 pw=0 time=621015820 us)'
STAT #2 id=2 cnt=910710 pid=1 pos=1 obj=0 op='NESTED LOOPS SEMI (cr=1,4621,0797 pr=29 pw=0 time=767827195 us)'
STAT #2 id=3 cnt=68213663 pid=2 pos=1 obj=0 op='NESTED LOOPS  (cr=1070,6085 pr=29 pw=0 time=204695611 us)'
STAT #2 id=4 cnt=71339 pid=3 pos=1 obj=0 op='NESTED LOOPS  (cr=14,7580 pr=29 pw=0 time=1766561 us)'
STAT #2 id=5 cnt=71339 pid=4 pos=1 obj=988075 op='TABLE ACCESS BY INDEX ROWID VOD_CMS_PUBLISH_SEQ_DETAIL_BAK (cr=4900 pr=29 pw=0 time=553727 us)'
STAT #2 id=6 cnt=534198 pid=5 pos=1 obj=988078 op='INDEX RANGE SCAN SEQ_DETAILB_PARENTDATATYPE_IDX (cr=1409 pr=29 pw=0 time=168 us)'
STAT #2 id=7 cnt=71339 pid=4 pos=2 obj=986390 op='TABLE ACCESS BY INDEX ROWID CONTENT_IPTV_SCHEDULE (cr=14,2680 pr=0 pw=0 time=1232653 us)'
STAT #2 id=8 cnt=71339 pid=7 pos=1 obj=986391 op='INDEX UNIQUE SCAN PK_CONTENT_IPTV_SCHEDULE (cr=7,1341 pr=0 pw=0 time=625421 us)'
STAT #2 id=9 cnt=68213663 pid=3 pos=2 obj=986374 op='TABLE ACCESS BY INDEX ROWID CONTENT_IPTV_PHYSICALCHANNEL (cr=1055,8505 pr=0 pw=0 time=205925057 us)'
STAT #2 id=10 cnt=68213663 pid=9 pos=1 obj=986378 op='INDEX RANGE SCAN IDX_PHYSICALCHANNEL_NEW (cr=26,1148 pr=0 pw=0 time=804810 us)'
STAT #2 id=11 cnt=889822 pid=2 pos=2 obj=986334 op='INDEX RANGE SCAN IDX_CONTENT_BASE_ASSETACTIVEID (cr=1,3550,4712 pr=0 pw=0 time=382974329 us)'

table_ddl.txt

12 KB, 下载次数: 1700

table ddl
2#
发表于 2013-9-3 22:50:45
1、从这个执行计划的成本看 明显不对， 你确认统计信息是最新的吗？

2、

STAT #2 id=6 cnt=534198 pid=5 pos=1 obj=988078 op='INDEX RANGE SCAN SEQ_DETAILB_PARENTDATATYPE_IDX (cr=1409 pr=29 pw=0 time=168 us)'
STAT #2 id=7 cnt=71339 pid=4 pos=2 obj=986390 op='TABLE ACCESS BY INDEX ROWID CONTENT_IPTV_SCHEDULE (cr=14,2680 pr=0 pw=0 time=1232653 us)'
STAT #2 id=8 cnt=71339 pid=7 pos=1 obj=986391 op='INDEX UNIQUE SCAN PK_CONTENT_IPTV_SCHEDULE (cr=7,1341 pr=0 pw=0 time=625421 us)'
STAT #2 id=9 cnt=68213663 pid=3 pos=2 obj=986374 op='TABLE ACCESS BY INDEX ROWID CONTENT_IPTV_PHYSICALCHANNEL (cr=1055,8505 pr=0 pw=0 time=205925057 us)'
STAT #2 id=10 cnt=68213663 pid=9 pos=1 obj=986378 op='INDEX RANGE SCAN IDX_PHYSICALCHANNEL_NEW (cr=26,1148 pr=0 pw=0 time=804810 us)'
STAT #2 id=11 cnt=889822 pid=2 pos=2 obj=986334 op='INDEX RANGE SCAN IDX_CONTENT_BASE_ASSETACTIVEID (cr=1,3550,4712 pr=0 pw=0 time=382974329 us)'


cnt=889822 op='INDEX RANGE SCAN IDX_CONTENT_BASE_ASSETACTIVEID (cr=1,3550,4712 pr=0 pw=0 time=382974329 us)'

这个信息反映 从cnt=889822 该数据源范围 889822  行 而不是CBO预估的1行

这说明统计信息很不准确

回复 只看该作者 道具 举报

3#
发表于 2013-9-4 00:24:50
结果集91w行，还有orderby，不好调整吧。

回复 只看该作者 道具 举报

4#
发表于 2013-9-4 09:01:47
Maclean Liu(刘相兵 发表于 2013-9-3 22:50
1、从这个执行计划的成本看 明显不对， 你确认统计信息是最新的吗？

2、

表的统计信息固化了已经，无任何ddl操作，研发说要求把统计信息固化，因为现场的环境会怎么样无法预料

回复 只看该作者 道具 举报

5#
发表于 2013-9-4 09:53:52
这理由真奇葩，如果要固定统计信息，前提是SQL的执行计划是合理的。如果是合理的，那它就应该是这个速度。如果不合理，为啥要固定统计信息。

回复 只看该作者 道具 举报

6#
发表于 2013-9-4 13:20:31
dla001 发表于 2013-9-4 09:53
这理由真奇葩，如果要固定统计信息，前提是SQL的执行计划是合理的。如果是合理的，那它就应该是这个速度。 ...

主要是现场库的数据量不稳定，造成执行计划不同，所以我们在应用里写了分析统计信息和固化的语句，防止一会儿快一会儿慢，我也觉得不是很可取

回复 只看该作者 道具 举报

7#
发表于 2013-9-4 13:53:22
本帖最后由 clot09 于 2013-9-4 14:29 编辑

在VOD_CMS_PUBLISH_SEQ_DETAIL表上重新建了组合索引
drop index DELETE_ASSET_TYPE_INDEX
/
drop index IDX_CMS_PUBLISH_SEQ_NEW
/
drop index IDX_DETAIL_PARENTDATATYPE
/
drop index IDX_SEQ_DETAIL_MULT
/
drop index SEQ_DETAIL_ASSETID_INDEX
/
drop index SEQ_DETAIL_DATATYPE_INDEX
/
drop index SEQ_DETAIL_FORLDERID_INDEX
/
drop index SEQ_DETAIL_OBJECT_INDEX
/
drop index SEQ_DETAIL_PARENTDATATYPE_IDX
/
drop index VOD_CMS_SEQ_DT_PARIENTID_INDEX
/
create index IDX_DETAIL_PARENTDATATYPE on VOD_CMS_PUBLISH_SEQ_DETAIL (PUBLISH_OBJECT_ID, DATA_TYPE, DELETE_ASSET_TYPE, PARENT_ID)
/
create index IDX_SEQ_DETAIL_MULT on VOD_CMS_PUBLISH_SEQ_DETAIL (ASSETID, DATA_TYPE, DELETE_ASSET_TYPE,PARENT_ID)
/
create index VOD_CMS_SEQ_DT_PARIENTID_INDEX on VOD_CMS_PUBLISH_SEQ_DETAIL (PARENT_ID,DELETE_ASSET_TYPE,DATA_TYPE)
/

语句更改如下所示，可以说没动
select d.id, d.publish_object_id, to_char(phc.assetid)
  2     from content_iptv_physicalchannel phc,
  3          content_iptv_schedule        sc,
  4          vod_cms_publish_seq_detail   d
  5    where exists (SELECT 1 FROM content_base cb where cb.activeid=phc.id and cb.assetid = phc.assetid)
  6      and phc.channelassetid = sc.channelassetid
  7      and phc.assetstatus =3
  8      and sc.id = d.publish_object_id
  9      and d.data_type = 30
10      and to_char(d.delete_asset_type) = 40
11      and to_char(d.parent_id) = 130279363   -------parent_id变了，但是返回的行数没变
12     order by d.publish_object_id;

一下是sql trace
SQL>  select d.id, d.publish_object_id, to_char(phc.assetid)
  2     from content_iptv_physicalchannel phc,
  3          content_iptv_schedule        sc,
  4          vod_cms_publish_seq_detail   d
  5    where exists (SELECT 1 FROM content_base cb where cb.activeid=phc.id and cb.assetid = phc.assetid)
  6      and phc.channelassetid = sc.channelassetid
  7      and phc.assetstatus =3
  8      and sc.id = d.publish_object_id
  9      and d.data_type = 30
10      and to_char(d.delete_asset_type) = 40
11      and to_char(d.parent_id) = 130279363
12     order by d.publish_object_id;

910710 rows selected.

Elapsed: 00:00:24.25

Execution Plan
----------------------------------------------------------
Plan hash value: 2834412430

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                                |     2 |   134 |   329   (3)| 00:00:04 |
|   1 |  SORT ORDER BY                  |                                |     2 |   134 |   329   (3)| 00:00:04 |
|   2 |   TABLE ACCESS BY INDEX ROWID   | VOD_CMS_PUBLISH_SEQ_DETAIL     |     1 |    24 |     1   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                 |                                |     2 |   134 |   328   (3)| 00:00:04 |
|   4 |     NESTED LOOPS                |                                |    47 |  2021 |   300   (3)| 00:00:04 |
|   5 |      NESTED LOOPS SEMI          |                                |     1 |    31 |   272   (3)| 00:00:04 |
|*  6 |       TABLE ACCESS FULL         | CONTENT_IPTV_PHYSICALCHANNEL   | 90073 |  1671K|   267   (2)| 00:00:04 |
|*  7 |       INDEX RANGE SCAN          | IDX_CONTENT_BASE_ASSETACTIVEID |     1 |    12 |     1   (0)| 00:00:01 |
|   8 |      TABLE ACCESS BY INDEX ROWID| CONTENT_IPTV_SCHEDULE          |   741 |  8892 |    29   (0)| 00:00:01 |
|*  9 |       INDEX RANGE SCAN          | IDX_SCHEDULE_CHANNELASSETID    |   741 |       |     1   (0)| 00:00:01 |
|* 10 |     INDEX RANGE SCAN            | IDX_DETAIL_PARENTDATATYPE      |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - filter("PHC"."ASSETSTATUS"=3)
   7 - access("CB"."ASSETID"="PHC"."ASSETID" AND "CB"."ACTIVEID"="PHC"."ID")
   9 - access("PHC"."CHANNELASSETID"="SC"."CHANNELASSETID")
  10 - access("SC"."ID"="D"."PUBLISH_OBJECT_ID" AND "D"."DATA_TYPE"=30)
       filter(TO_NUMBER(TO_CHAR("D"."DELETE_ASSET_TYPE"))=40 AND
              TO_NUMBER(TO_CHAR("D"."PARENT_ID"))=130279363)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
    2603171  consistent gets
         24  physical reads
          0  redo size
   30698271  bytes sent via SQL*Net to client
     668335  bytes received via SQL*Net from client
      60715  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
     910710  rows processed

回复 只看该作者 道具 举报

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

QQ|手机版|Archiver|Oracle数据库数据恢复、性能优化

GMT+8, 2025-1-4 07:28 , Processed in 0.054572 second(s), 23 queries .

Powered by Discuz! X2.5

© 2001-2012 Comsenz Inc.

回顶部
TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569