无语的indexhint：手工分配哈希区，5小时不出结果，优化后20分钟
同事发来一个语句，说5个小时不出结果，我滴个神呀，想看看到底是什么垃圾语句造成的。于是叫同事发过来。不看不知道，一看吓一跳，3个表关联，强制使用了2个index hint，其中一个表9g，一个表67g，还有一个小表40Mb。无知的开发人员，以为走index就是快的，
同事发来一个语句，说5个小时不出结果，我滴个神呀，想看看到底是什么垃圾语句造成的。于是叫同事发过来。不看不知道，一看吓一跳，3个表关联，强制使用了2个index hint，其中一个表9g，一个表67g，还有一个小表40Mb。无知的开发人员，以为走index就是快的，哎。。。
下面是同事发来的语句：
select /*+ parallel(t,4) index(a,IDX_COMMBASUBSHIST_1) index(b,IDX_COMMCMSERVHIST_1)*/
1,
t.DISC_ID,
t.DISC_LEV,
to_date(20140117082042, 'yyyymmddhh24miss'),
t.MSINFO_ID,
t.ORG_ID,
t.SERV_ID,
t.SUBS_ID,
t.OBJ_GRP_ID,
a.SUBS_CODE,
a.SUBS_STAT,
a.SUBS_STAT_REASON,
a.SUBS_STAT_DATE,
a.ACTION_ID,
a.ACTION_TYPE,
a.ACTION_EX_TYPE,
a.ACT_DATE,
a.REQ_ID,
a.STAFF_ID,
a.CMMS_CUST_CODE,
a.SPEED_VALUE,
b.ACC_NBR,
b.CUST_ID,
b.SERV_NBR,
b.CONSUME_GRADE,
b.SERV_LEV,
b.ACCOUNT_NBR,
b.CITY_VILLAGE_ID,
b.SERV_CHANNEL_ID,
b.SERV_STAT_ID,
b.CUST_CLASS_DL,
b.CUST_TYPE_ID,
b.USER_TYPE,
b.USER_CHAR,
b.PAYMENT_TYPE,
b.BILLING_TYPE,
b.PROD_ID,
b.PROD_CAT_ID,
b.EXCHANGE_ID,
b.SERV_COL1,
b.SERV_COL2,
b.AREA_ID,
b.SUBST_ID,
b.BRANCH_ID,
b.STOP_TYPE,
b.CUST_MANAGER_ID,
b.CREATE_DATE,
b.ADDRESS_ID,
b.SUBS_DATE,
b.OPEN_DATE,
b.MODI_STAFF_ID,
b.CMMS_CUST_ID,
b.CUST_NAME,
b.SALES_ID,
b.SALES_TYPE_ID,
b.SERV_ADDR_ID,
t.HIST_CREATE_DATE,
b.ARREAR_MONTH,
b.ARREAR_MONTH_LAST,
t.SALESTAFF_ID,
t.EHOME_TYPE,
t.EHOME_CLASS,
b.strat_grp_dl,
b.sale_org1,
b.sale_org2,
b.sale_org3,
b.location_type,
b.region_flag,
b.terminal_id,
b.pstn_id,
b.fee_id,
b.payment_id,
b.billing_id,
b.strat_grp_xl,
b.fld1,
b.fld3,
b.cust_level,
b.group_cust_type,
b.cust_region,
b.group_cust_grade,
b.control_level,
b.net_connect_type,
b.trade_type_id,
b.acc_nbr2,
b.cdma_class_id,
b.phone_number_id,
b.develop_channel,
b.online_time,
t.wireless_type,
b.new_serv_stat_id,
b.is_phs_tk,
b.serv_grp_type,
b.state,
t.cdma_disc_type,
b.mix_disc,
b.is_3g,
t.add_disc_type,
to_number(nvl(b.business_type, '-1')),
nvl(t.label_num, -1),
b.is_mix_prod,
t.price_id,
t.disc_item_id,
b.STD_SUBST_ID,
b.STD_BRANCH_ID,
t.DISC_ITEM_ID_OP,
t.PRICE_ID_OP,
t.business_type,
b.new_prod_id,
b.BOARD_SUBST_ID,
b.BOARD_BRANCH_ID
from RPT_COMM_BA_SUBS_HIST a,
RPT_COMM_CM_SERV_HIST b,
TB_COMM_BA_MSDISC_TEMP t
where a.subs_id = t.subs_id
and b.serv_id = t.serv_id
--同事说开销比较大。有450W。。下面是执行计划：
<img src="/static/imghw/default1.png" data-src="http://img.blog.csdn.net/20141025102001913?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvZ2RtemxoajE=/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center" class="lazy" alt="" />
/*
涉及的表大小：
OWNER SEGMENT_NAME SEGMENT_TYPE Size(Mb)
SUMMARY_SJZ_GZ TB_COMM_BA_MSDISC_TEMP TABLE 40
SUMMARY_SJZ_GZ RPT_COMM_CM_SERV_HIST TABLE PARTITION 9016.1875
SUMMARY_SJZ_GZ RPT_COMM_BA_SUBS_HIST TABLE PARTITION 67330.25
以下是优化思路：
强制使用索引，导致其中9g的表走了index full scan，然后回表。因为除了index fast scan以外，其他索引扫描都是单块读，回表又是单块读。导致速度非常慢。优化时考虑使用哈希连接，40Mb的小表作为驱动表，连接9g的表，最后连接超大的67G的表。
优化时使用的技术：
1. use_hash（a，b），使用哈希表关联方式
2. /*+parallel(a 5)*/;并行处理
3. db_file_multiblock_read_count多块读参数设置为最大
4. workarea_size_policy设置为手工管理
5. sort_area_size设为接近最大
6. hash_area_size设为接近最大
<p>5小时不出结果，优化后20分钟不到出结果，就是这么神奇。</p><p>alter session enable parallel dml;
alter session set workarea_size_policy=manual;
alter session set sort_area_size=2100000000;
alter session set hash_area_size=2100000000;
alter session set db_file_multiblock_read_count=128;
select  /*+parallel(a,5) parallel(b,5) parallel(t,5) leading(t) use_hash(t,b) user_hash(b,a)*/
     1,
    t.DISC_ID,
    t.DISC_LEV,
    to_date(20140117082042, 'yyyymmddhh24miss'),
    t.MSINFO_ID,
    t.ORG_ID,
    t.SERV_ID,
    t.SUBS_ID,
    t.OBJ_GRP_ID,
    a.SUBS_CODE,
    a.SUBS_STAT,
    a.SUBS_STAT_REASON,
    a.SUBS_STAT_DATE,
    a.ACTION_ID,
    a.ACTION_TYPE,
    a.ACTION_EX_TYPE,
    a.ACT_DATE,
    a.REQ_ID,
    a.STAFF_ID,
    a.CMMS_CUST_CODE,
    a.SPEED_VALUE,
    b.ACC_NBR,
    b.CUST_ID,
    b.SERV_NBR,
    b.CONSUME_GRADE,
    b.SERV_LEV,
    b.ACCOUNT_NBR,
    b.CITY_VILLAGE_ID,
    b.SERV_CHANNEL_ID,
    b.SERV_STAT_ID,
    b.CUST_CLASS_DL,
    b.CUST_TYPE_ID,
    b.USER_TYPE,
    b.USER_CHAR,
    b.PAYMENT_TYPE,
    b.BILLING_TYPE,
    b.PROD_ID,
    b.PROD_CAT_ID,
    b.EXCHANGE_ID,
    b.SERV_COL1,
    b.SERV_COL2,
    b.AREA_ID,
    b.SUBST_ID,
    b.BRANCH_ID,
    b.STOP_TYPE,
    b.CUST_MANAGER_ID,
    b.CREATE_DATE,
    b.ADDRESS_ID,
    b.SUBS_DATE,
    b.OPEN_DATE,
    b.MODI_STAFF_ID,
    b.CMMS_CUST_ID,
    b.CUST_NAME,
    b.SALES_ID,
    b.SALES_TYPE_ID,
    b.SERV_ADDR_ID,
    t.HIST_CREATE_DATE,
    b.ARREAR_MONTH,
    b.ARREAR_MONTH_LAST,
    t.SALESTAFF_ID,
    t.EHOME_TYPE,
    t.EHOME_CLASS,
    b.strat_grp_dl,
    b.sale_org1,
    b.sale_org2,
    b.sale_org3,
    b.location_type,
    b.region_flag,
    b.terminal_id,
    b.pstn_id,
    b.fee_id,
    b.payment_id,
    b.billing_id,
    b.strat_grp_xl,
    b.fld1,
    b.fld3,
    b.cust_level,
    b.group_cust_type,
    b.cust_region,
    b.group_cust_grade,
    b.control_level,
    b.net_connect_type,
    b.trade_type_id,
    b.acc_nbr2,
    b.cdma_class_id,
    b.phone_number_id,
    b.develop_channel,
    b.online_time,
    t.wireless_type,
    b.new_serv_stat_id,
    b.is_phs_tk,
    b.serv_grp_type,
    b.state,
    t.cdma_disc_type,
    b.mix_disc,
    b.is_3g,
    t.add_disc_type,
    to_number(nvl(b.business_type, '-1')),
    nvl(t.label_num, -1),
    b.is_mix_prod,
    t.price_id,
    t.disc_item_id,
    b.STD_SUBST_ID,
    b.STD_BRANCH_ID,
    t.DISC_ITEM_ID_OP,
    t.PRICE_ID_OP,
    t.business_type,
    b.new_prod_id,
    b.BOARD_SUBST_ID,
    b.BOARD_BRANCH_ID
     from SUMMARY_SJZ_GZ.RPT_COMM_BA_SUBS_HIST  a,
          SUMMARY_SJZ_GZ.RPT_COMM_CM_SERV_HIST  b,
          SUMMARY_SJZ_GZ.TB_COMM_BA_MSDISC_TEMP t
    where a.subs_id = t.subs_id
      and b.serv_id = t.serv_id
</p>
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
1387
52
When might a full table scan be faster than using an index in MySQL?
Apr 09, 2025 am 12:05 AM
Full table scanning may be faster in MySQL than using indexes. Specific cases include: 1) the data volume is small; 2) when the query returns a large amount of data; 3) when the index column is not highly selective; 4) when the complex query. By analyzing query plans, optimizing indexes, avoiding over-index and regularly maintaining tables, you can make the best choices in practical applications.
Explain InnoDB Full-Text Search capabilities.
Apr 02, 2025 pm 06:09 PM
InnoDB's full-text search capabilities are very powerful, which can significantly improve database query efficiency and ability to process large amounts of text data. 1) InnoDB implements full-text search through inverted indexing, supporting basic and advanced search queries. 2) Use MATCH and AGAINST keywords to search, support Boolean mode and phrase search. 3) Optimization methods include using word segmentation technology, periodic rebuilding of indexes and adjusting cache size to improve performance and accuracy.
Can I install mysql on Windows 7
Apr 08, 2025 pm 03:21 PM
Yes, MySQL can be installed on Windows 7, and although Microsoft has stopped supporting Windows 7, MySQL is still compatible with it. However, the following points should be noted during the installation process: Download the MySQL installer for Windows. Select the appropriate version of MySQL (community or enterprise). Select the appropriate installation directory and character set during the installation process. Set the root user password and keep it properly. Connect to the database for testing. Note the compatibility and security issues on Windows 7, and it is recommended to upgrade to a supported operating system.
Difference between clustered index and non-clustered index (secondary index) in InnoDB.
Apr 02, 2025 pm 06:25 PM
The difference between clustered index and non-clustered index is: 1. Clustered index stores data rows in the index structure, which is suitable for querying by primary key and range. 2. The non-clustered index stores index key values and pointers to data rows, and is suitable for non-primary key column queries.
What are some popular MySQL GUI tools (e.g., MySQL Workbench, phpMyAdmin)?
Mar 21, 2025 pm 06:28 PM
Article discusses popular MySQL GUI tools like MySQL Workbench and phpMyAdmin, comparing their features and suitability for beginners and advanced users.[159 characters]
How do you handle large datasets in MySQL?
Mar 21, 2025 pm 12:15 PM
Article discusses strategies for handling large datasets in MySQL, including partitioning, sharding, indexing, and query optimization.
MySQL: Simple Concepts for Easy Learning
Apr 10, 2025 am 09:29 AM
MySQL is an open source relational database management system. 1) Create database and tables: Use the CREATEDATABASE and CREATETABLE commands. 2) Basic operations: INSERT, UPDATE, DELETE and SELECT. 3) Advanced operations: JOIN, subquery and transaction processing. 4) Debugging skills: Check syntax, data type and permissions. 5) Optimization suggestions: Use indexes, avoid SELECT* and use transactions.
The relationship between mysql user and database
Apr 08, 2025 pm 07:15 PM
In MySQL database, the relationship between the user and the database is defined by permissions and tables. The user has a username and password to access the database. Permissions are granted through the GRANT command, while the table is created by the CREATE TABLE command. To establish a relationship between a user and a database, you need to create a database, create a user, and then grant permissions.
