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

找回密码
注册
搜索
热搜: 活动 交友 discuz
Oracle数据库数据恢复、性能优化»论坛 › Oracle › Oracle数据库管理 › 语句如何优化
发新帖
Mille

51

积分

0

好友

20

主题

语句如何优化

1#
发表于 2012-3-9 11:04:51 | 查看: 6068| 回复: 8
select a.so_nbr,
       a.busi_code,
       a.serv_id,
       a.so_region_code,
       a.so_county_code,
       a.so_org_id,
       a.op_id,
       a.so_date,
       to_char(decode(a.isnormal, 2, a.rso_nbr, '')) rso_nbr,
       a.remark,
       b.phone_id,
       a.cust_id,
       (select cust_name
          from zk.cm_customer
         where cust_id = a.cust_id
           and rownum <= 1) cust_name
  from zk.cm_busi_201202 a, zk.cm_user b
where a.serv_id = b.serv_id(+)
   and (a.busi_code < 100 or a.busi_code in (2403, 2415))
   and a.so_date >= to_date('20120211000000', 'yyyymmddhh24miss')
   and a.so_date <= to_date('20120229235959', 'yyyymmddhh24miss')
   and a.so_region_code in (771)
   and a.so_county_code in
       (7101, 7102, 7103, 7104, 7105, 7107, 7108, 7114, 7116, 7117, 7118, 7119, 7120)
   and exists
(select 1 from zk.cm_busi_promo_201202 where so_nbr = a.so_nbr)
   
----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           |  Name                           | Rows  | Bytes | Cost (%CPU)| Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                                 |  6448 |  1397K|  4476K  (2)|       |       |
|*  1 |  COUNT STOPKEY                      |                                 |       |       |            |       |       |
|   2 |   TABLE ACCESS BY GLOBAL INDEX ROWID| CM_CUSTOMER                     |     1 |    17 |     4  (25)| ROWID | ROW L |
|*  3 |    INDEX UNIQUE SCAN                | PK_CM_CUSTOMER                  |     1 |       |     3  (34)|       |       |
|   4 |  NESTED LOOPS OUTER                 |                                 |  6448 |  1397K|  4476K  (2)|       |       |
|   5 |   NESTED LOOPS SEMI                 |                                 |  6448 |  1278K|  4463K  (2)|       |       |
|*  6 |    TABLE ACCESS BY INDEX ROWID      | CM_BUSI_201202                  |  6448 |  1215K|  4450K  (2)|       |       |
|*  7 |     INDEX RANGE SCAN                | IDX_BUSI_SODATE_201202          |    25M|       |   209K  (3)|       |       |
|   8 |    VIEW                             | CM_BUSI_PROMO_201202            |  6241K|    59M|            |       |       |
|   9 |     UNION-ALL PARTITION             |                                 |       |       |            |       |       |
|* 10 |      INDEX RANGE SCAN               | IDX_CM_PROMO_SO_NBR_0770201202  |     4 |    40 |     4  (25)|       |       |
|* 11 |      INDEX RANGE SCAN               | IDX_CM_PROMO_SO_NBR_0771201202  |     4 |    40 |     4  (25)|       |       |
|* 12 |      INDEX RANGE SCAN               | IDX_CM_PROMO_SO_NBR_0772201202  |     6 |    60 |     4  (25)|       |       |
|* 13 |      INDEX RANGE SCAN               | IDX_CM_PROMO_SO_NBR_0773201202  |    13 |   130 |     4  (25)|       |       |
|* 14 |      INDEX RANGE SCAN               | IDX_CM_PROMO_SO_NBR_0774201202  |     3 |    30 |     4  (25)|       |       |
|* 15 |      INDEX RANGE SCAN               | IDX_CM_PROMO_SO_NBR_0775201202  |     5 |    50 |     4  (25)|       |       |
|* 16 |      INDEX RANGE SCAN               | IDX_CM_PROMO_SO_NBR_0776201202  |     5 |    50 |     4  (25)|       |       |
|* 17 |      INDEX RANGE SCAN               | IDX_CM_PROMO_SO_NBR_0777201202  |    12 |   120 |     4  (25)|       |       |
|* 18 |      INDEX RANGE SCAN               | IDX_CM_PROMO_SO_NBR_0778201202  |     5 |    50 |     4  (25)|       |       |
|* 19 |      INDEX RANGE SCAN               | IDX_CM_PROMO_SO_NBR_0779201202  |     3 |    30 |     4  (25)|       |       |
|  20 |   TABLE ACCESS BY GLOBAL INDEX ROWID| CM_USER                         |     1 |    19 |     3  (34)| ROWID | ROW L |
|* 21 |    INDEX RANGE SCAN                 | PK_ZK_CM_USER                   |     1 |       |     2  (50)|       |       |
----------------------------------------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<=1)
   3 - access("CM_CUSTOMER"."CUST_ID"=:B1)
   6 - filter("SYS_ALIAS_1"."SO_REGION_CODE"=771 AND ("SYS_ALIAS_1"."BUSI_CODE"<100 OR "SYS_ALIAS_1"."BUSI_CODE"=2403 OR
              "SYS_ALIAS_1"."BUSI_CODE"=2415) AND ("SYS_ALIAS_1"."SO_COUNTY_CODE"=7101 OR "SYS_ALIAS_1"."SO_COUNTY_CODE"=7102 OR
              "SYS_ALIAS_1"."SO_COUNTY_CODE"=7103 OR "SYS_ALIAS_1"."SO_COUNTY_CODE"=7104 OR "SYS_ALIAS_1"."SO_COUNTY_CODE"=7105 OR
              "SYS_ALIAS_1"."SO_COUNTY_CODE"=7107 OR "SYS_ALIAS_1"."SO_COUNTY_CODE"=7108 OR "SYS_ALIAS_1"."SO_COUNTY_CODE"=7114 OR
              "SYS_ALIAS_1"."SO_COUNTY_CODE"=7116 OR "SYS_ALIAS_1"."SO_COUNTY_CODE"=7117 OR "SYS_ALIAS_1"."SO_COUNTY_CODE"=7118 OR
              "SYS_ALIAS_1"."SO_COUNTY_CODE"=7119 OR "SYS_ALIAS_1"."SO_COUNTY_CODE"=7120))
   7 - access("SYS_ALIAS_1"."SO_DATE">=TO_DATE('2012-02-11 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
              "SYS_ALIAS_1"."SO_DATE"<=TO_DATE('2012-02-29 23:59:59', 'yyyy-mm-dd hh24:mi:ss'))
  10 - access("CM_BUSI_PROMO_0770201202"."SO_NBR"="SYS_ALIAS_1"."SO_NBR")
  11 - access("CM_BUSI_PROMO_0771201202"."SO_NBR"="SYS_ALIAS_1"."SO_NBR")
  12 - access("CM_BUSI_PROMO_0772201202"."SO_NBR"="SYS_ALIAS_1"."SO_NBR")
  13 - access("CM_BUSI_PROMO_0773201202"."SO_NBR"="SYS_ALIAS_1"."SO_NBR")
  14 - access("CM_BUSI_PROMO_0774201202"."SO_NBR"="SYS_ALIAS_1"."SO_NBR")
  15 - access("CM_BUSI_PROMO_0775201202"."SO_NBR"="SYS_ALIAS_1"."SO_NBR")
  16 - access("CM_BUSI_PROMO_0776201202"."SO_NBR"="SYS_ALIAS_1"."SO_NBR")
  17 - access("CM_BUSI_PROMO_0777201202"."SO_NBR"="SYS_ALIAS_1"."SO_NBR")
  18 - access("CM_BUSI_PROMO_0778201202"."SO_NBR"="SYS_ALIAS_1"."SO_NBR")
  19 - access("CM_BUSI_PROMO_0779201202"."SO_NBR"="SYS_ALIAS_1"."SO_NBR")
  21 - access("SYS_ALIAS_1"."SERV_ID"="B"."SERV_ID"(+))

---Oracle9i Enterprise Edition Release 9.2.0.8.0
----zk.cm_busi_201202   4千万
---语句返回50几万
这个语句如何优化呢？？
2#
发表于 2012-3-9 15:56:40
NESTED LOOPS SEMI
NESTED LOOPS OUTER

  TABLE ACCESS BY INDEX ROWID      | CM_BUSI_201202                  |  6448
   INDEX RANGE SCAN                | IDX_BUSI_SODATE_201202          |    25M|  

   and (a.busi_code < 100 or a.busi_code in (2403, 2415))
   and a.so_date >= to_date('20120211000000', 'yyyymmddhh24miss')
   and a.so_date <= to_date('20120229235959', 'yyyymmddhh24miss')
   and a.so_region_code in (771)
   and a.so_county_code in
       (7101, 7102, 7103, 7104, 7105, 7107, 7108, 7114, 7116, 7117, 7118, 7119, 7120)

   and a.so_date >= to_date('20120211000000', 'yyyymmddhh24miss')
   and a.so_date <= to_date('20120229235959', 'yyyymmddhh24miss')   =》card 25M
==>  谓词过滤后  CM_BUSI_201202表的上的card为6448 造成 CBO 选择 NESTED LOOPS

最后CBO 获得的result card也为 6448  ，而你描述 语句返回50几万

那说明 CBO的 statistics 不准确或过时

action plan:

1.   利用一下脚本 收集信息：
http://www.oracledatabase12g.com ... h-check-script.html

2. 考虑收集 CM_BUSI_201202  一些列上的histogram

回复 只看该作者 道具 举报

3#
发表于 2012-3-9 16:06:22
select /*+hash_sj*/1 from zk.cm_busi_promo_201202 where so_nbr = a.so_nbr  加上这个hint看看

回复 只看该作者 道具 举报

4#
发表于 2012-3-9 16:10:43
insert into TEMP_TABLE values (7101);
insert into TEMP_TABLE  values (7102);
insert into TEMP_TABLE  values  (7103);
.....
insert into TEMP_TABLE  values  (7120);

select a.so_nbr,
        a.busi_code,
        a.serv_id,
        a.so_region_code,
        a.so_county_code,
        a.so_org_id,
        a.op_id,
        a.so_date,
        to_char(decode(a.isnormal, 2, a.rso_nbr, '')) rso_nbr,
        a.remark,
        b.phone_id,
        a.cust_id,
        (select cust_name
           from zk.cm_customer
          where cust_id = a.cust_id
            and rownum <= 1) cust_name
   from zk.cm_busi_201202 a, zk.cm_user b
  where a.serv_id = b.serv_id(+)
    and a.so_date >= to_date('20120211000000', 'yyyymmddhh24miss')
    and a.so_date <= to_date('20120229235959', 'yyyymmddhh24miss')
    and (a.busi_code < 100 or a.busi_code in (2403, 2415))
    and a.so_region_code = 771
    and exists ( select 1 from TEMP_TABLE dd where dd.so_county_code=a.so_county_code)
    and exists  (select 1 from zk.cm_busi_promo_201202 where so_nbr = a.so_nbr)

不知道可否， 我们公司的网优这样优化过。可以试试

回复 只看该作者 道具 举报

5#
发表于 2012-3-9 18:04:04

回复 3# 的帖子

-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            |  Name                           | Rows  | Bytes | Cost (%CPU)| Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                                 |  3938 |   853K|  2770K  (2)|       |       |
|*  1 |  COUNT STOPKEY                       |                                 |       |       |            |       |       |
|   2 |   TABLE ACCESS BY GLOBAL INDEX ROWID | CM_CUSTOMER                     |     1 |    17 |     4  (25)| ROWID | ROW L |
|*  3 |    INDEX UNIQUE SCAN                 | PK_CM_CUSTOMER                  |     1 |       |     3  (34)|       |       |
|*  4 |  HASH JOIN SEMI                      |                                 |  3938 |   853K|  2770K  (2)|       |       |
|   5 |   NESTED LOOPS OUTER                 |                                 |  3938 |   815K|  2726K  (2)|       |       |
|*  6 |    TABLE ACCESS BY INDEX ROWID       | CM_BUSI_201202                  |  3938 |   742K|  2718K  (2)|       |       |
|*  7 |     INDEX RANGE SCAN                 | IDX_BUSI_SODATE_201202          |    15M|       |   128K  (3)|       |       |
|   8 |    TABLE ACCESS BY GLOBAL INDEX ROWID| CM_USER                         |     1 |    19 |     3  (34)| ROWID | ROW L |
|*  9 |     INDEX RANGE SCAN                 | PK_ZK_CM_USER                   |     1 |       |     2  (50)|       |       |
|  10 |   VIEW                               | CM_BUSI_PROMO_201202            |  6241K|    59M|            |       |       |
|  11 |    UNION-ALL PARTITION               |                                 |       |       |            |       |       |
|  12 |     INDEX FULL SCAN                  | IDX_CM_PROMO_SO_NBR_0770201202  |   165K|  1620K|   912   (4)|       |       |
|  13 |     INDEX FULL SCAN                  | IDX_CM_PROMO_SO_NBR_0771201202  |  1929K|    18M| 16087   (3)|       |       |
|  14 |     INDEX FULL SCAN                  | IDX_CM_PROMO_SO_NBR_0772201202  |   979K|  9566K|  6753   (4)|       |       |
|  15 |     INDEX FULL SCAN                  | IDX_CM_PROMO_SO_NBR_0773201202  |   490K|  4793K|  3371   (3)|       |       |
|  16 |     INDEX FULL SCAN                  | IDX_CM_PROMO_SO_NBR_0774201202  |   309K|  3018K|  1888   (4)|       |       |
|  17 |     INDEX FULL SCAN                  | IDX_CM_PROMO_SO_NBR_0775201202  |   966K|  9440K|  6281   (4)|       |       |
|  18 |     INDEX FULL SCAN                  | IDX_CM_PROMO_SO_NBR_0776201202  |   375K|  3671K|  2396   (4)|       |       |
|  19 |     INDEX FULL SCAN                  | IDX_CM_PROMO_SO_NBR_0777201202  |   404K|  3952K|  2185   (4)|       |       |
|  20 |     INDEX FULL SCAN                  | IDX_CM_PROMO_SO_NBR_0778201202  |   412K|  4027K|  2466   (4)|       |       |
|  21 |     INDEX FULL SCAN                  | IDX_CM_PROMO_SO_NBR_0779201202  |   206K|  2019K|  1164   (4)|       |       |
-----------------------------------------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<=1)
   3 - access("CM_CUSTOMER"."CUST_ID"=:B1)
   4 - access("CM_BUSI_PROMO_201202"."SO_NBR"="SYS_ALIAS_1"."SO_NBR")
   6 - filter("SYS_ALIAS_1"."SO_REGION_CODE"=771 AND ("SYS_ALIAS_1"."BUSI_CODE"<100 OR "SYS_ALIAS_1"."BUSI_CODE"=2403 OR
              "SYS_ALIAS_1"."BUSI_CODE"=2415) AND ("SYS_ALIAS_1"."SO_COUNTY_CODE"=7101 OR "SYS_ALIAS_1"."SO_COUNTY_CODE"=7102 OR
              "SYS_ALIAS_1"."SO_COUNTY_CODE"=7103 OR "SYS_ALIAS_1"."SO_COUNTY_CODE"=7104 OR "SYS_ALIAS_1"."SO_COUNTY_CODE"=7105 OR
              "SYS_ALIAS_1"."SO_COUNTY_CODE"=7107 OR "SYS_ALIAS_1"."SO_COUNTY_CODE"=7108 OR "SYS_ALIAS_1"."SO_COUNTY_CODE"=7114 OR
              "SYS_ALIAS_1"."SO_COUNTY_CODE"=7116 OR "SYS_ALIAS_1"."SO_COUNTY_CODE"=7117 OR "SYS_ALIAS_1"."SO_COUNTY_CODE"=7118 OR
              "SYS_ALIAS_1"."SO_COUNTY_CODE"=7119 OR "SYS_ALIAS_1"."SO_COUNTY_CODE"=7120))
   7 - access("SYS_ALIAS_1"."SO_DATE">=TO_DATE('2012-02-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
              "SYS_ALIAS_1"."SO_DATE"<=TO_DATE('2012-02-10 23:59:59', 'yyyy-mm-dd hh24:mi:ss'))
   9 - access("SYS_ALIAS_1"."SERV_ID"="B"."SERV_ID"(+))

回复 只看该作者 道具 举报

6#
发表于 2012-3-9 18:04:53

回复 2# 的帖子

版主  已经收集过了 还是一样

回复 只看该作者 道具 举报

7#
发表于 2012-3-9 18:17:00
HASH JOIN SEMI      =》这是加了　hash_sj的 执行计划？

0 | SELECT STATEMENT                     |                                 |  3938

得到的 card仍不准确，  脚本运行一下， 没有更多信息的话 无法进一步诊断。

你是如何收集histogram的？

回复 只看该作者 道具 举报

8#
发表于 2012-3-9 20:11:35
execute dbms_stats.gather_table_stats(ownname =>  'ZK' ,
                                      tabname =>'CM_BUSI_201202',      
                                       method_opt => 'FOR ALL INDEXED COLUMNS',
                                       estimate_percent =>25 ,
                                        granularity =>'ALL',
                                         cascade => TRUE,
                                        degree =>16);



那个是回复上海select的，没错 是加hash_sj的计划

回复 只看该作者 道具 举报

9#
发表于 2012-3-9 20:37:54
method_opt => 'FOR ALL INDEXED COLUMNS',　　这样不等于收集了histogram啊

回复 只看该作者 道具 举报

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

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

GMT+8, 2024-11-15 10:40 , Processed in 0.085738 second(s), 21 queries .

Powered by Discuz! X2.5

© 2001-2012 Comsenz Inc.

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