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

找回密码
注册
搜索
热搜: 活动 交友 discuz
Oracle数据库数据恢复、性能优化»论坛 › Oracle › Oracle数据库管理 › SQL优化，请帮忙看看
发新帖
btxgua

0

积分

1

好友

3

主题

[RAC性能调优] SQL优化，请帮忙看看

1#
发表于 2013-4-3 15:36:28 | 查看: 4208| 回复: 8
select * from ocs.ocs_voice_ticket t where t.mm=3 and t.serv_id=9110121015498084
------------------------------------------------------------------------+-----------------------------------+---------------+
| Id  | Operation                           | Name                      | Rows  | Bytes | Cost  | Time      | Pstart| Pstop |
------------------------------------------------------------------------+-----------------------------------+---------------+
| 0   | SELECT STATEMENT                    |                           |       |       |   169 |           |       |       |
| 1   |  PARTITION RANGE SINGLE             |                           |   168 |   67K |   169 |  00:00:03 | 3     | 3     |
| 2   |   TABLE ACCESS BY LOCAL INDEX ROWID | OCS_VOICE_TICKET          |   168 |   67K |   169 |  00:00:03 | 3     | 3     |
| 3   |    INDEX RANGE SCAN                 | IDX_VOICE_TICKET_SERV_ID_N|    88 |       |     4 |  00:00:01 | 3     | 3     |
------------------------------------------------------------------------+-----------------------------------+---------------+
上面是选择3月份（mm=3）分区的执行计划，索引选择正常。

由于4月份是月头，数据不完整，在1号数据量比较大，数据库也很忙，月初的统计信息是不准确的。
所以尝试解决的办法，是将其中的一个分区的统计信息复制到剩余的11个月中，然后lock统计信息，保持稳定。
但实际中，碰到了点问题。

exec dbms_stats.unlock_table_stats('OCS','OCS_VOICE_TICKET');
exec dbms_stats.copy_table_stats('OCS','OCS_VOICE_TICKET','P03','P04') ;
exec dbms_stats.lock_table_stats('OCS','OCS_VOICE_TICKET');
将3月份统计信息复制到了4月份，然后执行4月份相同的查询，执行计划不对，选择了错误的索引。
select * from ocs.ocs_voice_ticket t where t.mm=4 and t.serv_id=9110121015498084
----------------------------------------------------------------------+-----------------------------------+---------------+
| Id  | Operation                           | Name                    | Rows  | Bytes | Cost  | Time      | Pstart| Pstop |
----------------------------------------------------------------------+-----------------------------------+---------------+
| 0   | SELECT STATEMENT                    |                         |       |       |     5 |           |       |       |
| 1   |  PARTITION RANGE SINGLE             |                         |     1 |   408 |     5 |  00:00:01 | 4     | 4     |
| 2   |   TABLE ACCESS BY LOCAL INDEX ROWID | OCS_VOICE_TICKET        |     1 |   408 |     5 |  00:00:01 | 4     | 4     |
| 3   |    INDEX RANGE SCAN                 | IDX_VOICE_TICKET_SDATE_N|     1 |       |     4 |  00:00:01 | 4     | 4     |
----------------------------------------------------------------------+-----------------------------------+---------------+


3月份和4月份的10053文件见附件。

10053-ocs.TXT

158.18 KB, 下载次数: 937
2#
发表于 2013-4-3 15:38:22
补充一下我的数据库版本信息：
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 IBM/AIX RISC System/6000: Version 10.2.0.5.0 - Productio
NLSRTL Version 10.2.0.5.0 - Production

回复 只看该作者 道具 举报

3#
发表于 2013-4-3 15:42:01
  Access Path: index (RangeScan)
    Index: IDX_VOICE_TICKET_SDATE_N
    resc_io: 120605623.00  resc_cpu: 778276735352
   ix_sel: 0.99992  ix_sel_with_filters: 0.99992
    Cost: 120637372.12  Resp: 120637372.12  Degree: 1
       
       
       
          Access Path: index (RangeScan)
    Index: IDX_VOICE_TICKET_SDATE_N
    resc_io: 5.00  resc_cpu: 34866
   ix_sel: 7.9870e-09  ix_sel_with_filters: 7.9870e-09
    Cost: 5.00  Resp: 5.00  Degree: 1


2次的选择率并不一样啊

回复 只看该作者 道具 举报

4#
发表于 2013-4-3 15:43:17
我是copy的呀，为什么会不同呢？

回复 只看该作者 道具 举报

5#
发表于 2013-4-3 16:06:39

  CREATE TABLE "OCS"."OCS_VOICE_TICKET"
   (        "EVENT_ID" NUMBER(16,0) NOT NULL ENABLE,
        "FILE_ID" NUMBER(16,0),
        "PROCESS_ID" NUMBER(9,0),
        "EVENT_TYPE_ID" NUMBER(9,0),
        "START_DATE" DATE NOT NULL ENABLE,
        "CREATED_DATE" DATE,
        "SESSIONTERMINATEDTIME" DATE,
        "SERVICESCENARIOUS" NUMBER(9,0),
        "SESSION_ID" VARCHAR2(100),
        "SERV_ID" NUMBER(16,0),
        "BILLING_CYCLE_ID" NUMBER(9,0),
        "BILLING_AREA_CODE" VARCHAR2(10),
        "BILLING_NBR" VARCHAR2(32),
        "BILLING_VISIT_AREA_CODE" VARCHAR2(10),
        "CALLTYPEID" NUMBER(1,0),
        "ORG_CALLING_NBR" VARCHAR2(32),
        "CALLING_AREA_CODE" VARCHAR2(10),
        "CALLING_NBR" VARCHAR2(32),
        "CALLING_LAI" VARCHAR2(20),
        "CALLING_SP_TYPE_ID" NUMBER(9,0),
        "CALLINGPARTYCELLID" VARCHAR2(20),
        "CALLINGPARTYVLR" VARCHAR2(20),
        "CALLINGPARTYHLR" VARCHAR2(20),
        "CALLINGPARTYLOCATIONPRVOCINCE" VARCHAR2(6),
        "CALLINGPARTYVISITEDCITY" VARCHAR2(10),
        "CALLINGGROUPID" VARCHAR2(20),
        "ORG_CALLED_NBR" VARCHAR2(32),
        "CALLED_AREA_CODE" VARCHAR2(10),
        "CALLED_NBR" VARCHAR2(32),
        "CALLED_SP_TYPE_ID" NUMBER(9,0),
        "CALLEDPARTYCELLID" VARCHAR2(20),
        "CALLEDPARTYVLR" VARCHAR2(20),
        "CALLEDPARTYHLR" VARCHAR2(20),
        "CALLEDPARTYLOCATIONPRVOCINCE" VARCHAR2(6),
        "CALLEDPARTYVISITEDCITY" VARCHAR2(10),
        "CALLEDGROUPID" VARCHAR2(20),
        "FORWARDINGFLAG" VARCHAR2(2),
        "FOWARDINGNUMBER" VARCHAR2(20),
        "THIRD_PARTY_AREA_CODE" VARCHAR2(10),
        "THIRD_PARTY_NBR" VARCHAR2(32),
        "TERMINATEDCAUSE" NUMBER(9,0),
        "ORIGNIALHOST" VARCHAR2(64),
        "BALANCEINFO" VARCHAR2(512),
        "ACCUMLATORINFO" VARCHAR2(512),
        "TARIFFINFO" VARCHAR2(512),
        "ACCT_ITEM_TYPE_ID1" NUMBER(9,0),
        "ACCT_ITEM_TYPE_ID2" NUMBER(9,0),
        "ACCT_ITEM_TYPE_ID3" NUMBER(9,0),
        "CCR_NUM" NUMBER(9,0),
        "CHARGE1" NUMBER(9,0),
        "CHARGE2" NUMBER(9,0),
        "CHARGE3" NUMBER(9,0),
        "PAYFLAG" NUMBER(1,0),
        "CHARGE_PRESENT_MONEY" NUMBER(9,0) DEFAULT 0,
        "LAC" VARCHAR2(20),
        "MSC_ADDRESS" VARCHAR2(20),
        "CELLID" VARCHAR2(20),
        "IMSI" VARCHAR2(15),
        "DURATION" NUMBER(9,0),
        "BEARERCAPABILITY" VARCHAR2(8),
        "LONGDISTANCETYPE" VARCHAR2(2),
        "ROAMINGTYPE" VARCHAR2(2),
        "CARRIERTYPEID" NUMBER(9,0),
        "VIOCECHARGINGMODE" NUMBER(1,0),
        "NETWORKID" NUMBER(9,0),
        "CUST_ID" NUMBER(16,0),
        "BRAND" NUMBER(9,0),
        "OFFER_ID1" NUMBER(9,0),
        "OFFER_ID2" NUMBER(9,0),
        "HOSTID" VARCHAR2(64),
        "SWITCH_ID" NUMBER(9,0),
        "MASTERPRODUCTID" VARCHAR2(20),
        "REDO_FLAG" VARCHAR2(2) DEFAULT 0,
        "EVENTATTR" VARCHAR2(20) DEFAULT '000',
        "MM" NUMBER(2,0) NOT NULL ENABLE,
         CONSTRAINT "PK_OCS_VOICE_TICKET_N" PRIMARY KEY ("EVENT_ID", "MM")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT) LOCAL
(PARTITION "P01"
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TICKET_DATA01_INDEX" ,
PARTITION "P02"
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TICKET_DATA02_INDEX" ,
PARTITION "P03"
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TICKET_DATA03_INDEX" ,
PARTITION "P04"
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TICKET_DATA04_INDEX" ,
PARTITION "P05"
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TICKET_DATA05_INDEX" ,
PARTITION "P06"
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TICKET_DATA06_INDEX" ,
PARTITION "P07"
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TICKET_DATA01_INDEX" ,
PARTITION "P08"
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TICKET_DATA02_INDEX" ,
PARTITION "P09"
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TICKET_DATA03_INDEX" ,
PARTITION "P10"
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TICKET_DATA04_INDEX" ,
PARTITION "P11"
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TICKET_DATA05_INDEX" ,
PARTITION "P12"
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TICKET_DATA06_INDEX" )  ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT)
  TABLESPACE "OCS_TBS"
  PARTITION BY RANGE ("MM")
(PARTITION "P01"  VALUES LESS THAN (2)
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TICKET_DATA01_TBS" NOCOMPRESS ,
PARTITION "P02"  VALUES LESS THAN (3)
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TICKET_DATA02_TBS" NOCOMPRESS ,
PARTITION "P03"  VALUES LESS THAN (4)
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TICKET_DATA03_TBS" NOCOMPRESS ,
PARTITION "P04"  VALUES LESS THAN (5)
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TICKET_DATA04_TBS" NOCOMPRESS ,
PARTITION "P05"  VALUES LESS THAN (6)
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TICKET_DATA05_TBS" NOCOMPRESS ,
PARTITION "P06"  VALUES LESS THAN (7)
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TICKET_DATA06_TBS" NOCOMPRESS ,
PARTITION "P07"  VALUES LESS THAN (8)
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TICKET_DATA01_TBS" NOCOMPRESS ,
PARTITION "P08"  VALUES LESS THAN (9)
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TICKET_DATA02_TBS" NOCOMPRESS ,
PARTITION "P09"  VALUES LESS THAN (10)
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TICKET_DATA03_TBS" NOCOMPRESS ,
PARTITION "P10"  VALUES LESS THAN (11)
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TICKET_DATA04_TBS" NOCOMPRESS ,
PARTITION "P11"  VALUES LESS THAN (12)
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TICKET_DATA05_TBS" NOCOMPRESS ,
PARTITION "P12"  VALUES LESS THAN (MAXVALUE)
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TICKET_DATA06_TBS" NOCOMPRESS )

回复 只看该作者 道具 举报

6#
发表于 2013-4-3 16:07:53
两个分区的统计信息

统计信息.rar

304.55 KB, 下载次数: 1022

回复 只看该作者 道具 举报

7#
发表于 2013-4-3 16:12:18
dbms_stats.copy_table_stats does not alter low/high value

FYI

http://askdba.org/weblog/2009/06 ... lter-lowhigh-value/

回复 只看该作者 道具 举报

8#
发表于 2013-4-3 16:28:12
顶一个。很牛逼

回复 只看该作者 道具 举报

9#
发表于 2013-4-3 17:07:02
大拿，强...

回复 只看该作者 道具 举报

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

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

GMT+8, 2024-12-27 15:24 , Processed in 0.091089 second(s), 24 queries .

Powered by Discuz! X2.5

© 2001-2012 Comsenz Inc.

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