Silent index hint: hint is manually allocated to the hash area, which does not result in 5 hours. After optimization, the hint is 20 minutes.

Silent index hint: hint is manually allocated to the hash area, which does not result in 5 hours. After optimization, the hint is 20 minutes.

My colleague sent a statement saying that the result could not be returned within five hours. I am so distracted that I want to see what the spam statement is. So I asked my colleagues to send it. I don't know what to do. I was shocked at the first glance. Three tables are associated and two index hints are used. One table is 9 GB, one table is 67 GB, and the other small table is 40 Mb. Ignorant developers think that taking index is fast... The following statements are sent from colleagues: select/* + parallel (t, 4) index (a, IDX_COMMBASUBSHIST_1) index (B, IDX_COMMCMSERVHIST_1) */1, t. DISC_ID, t. disc_lev, to_date (20140117082042, 'yyyymmddhh24mis'), t. MSINFO_ID, t. ORG_ID, t. SERV_ID, t. SUBS_ID, t. OBJ_GRP_ID,. SUBS_CODE,. SUBS_STAT,. SUBS_STAT_REASON,. SUBS_STAT_DATE,. ACTION_ID,. ACTION_TYPE,. ACTION_EX_TYPE,. ACT_DATE,. REQ_ID,. STAFF_ID,. CMMS_CUST_CODE,. SPEED_VA LUE, B. ACC_NBR, B. CUST_ID, B. SERV_NBR, B. CONSUME_GRADE, B. serv_lev, B. ACCOUNT_NBR, B. city_language_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_D ATE, 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. subs_id = t. subs_id and B. serv_id = t. serv_id -- the colleague said that the overhead is relatively large. There are 450 W .. The following is the execution plan: /* involved table size: OWNERSEGMENT_NAMESEGMENT_TYPESize (Mb) Partition PARTITION9016.1875SUMMARY _ partition PARTITION67330.25 The following is the optimization idea: mandatory use of indexes, resulting in 9 GB of tables taking the index full scan, and then back to the table. In addition to index fast scan, other index scans are single-read, and the return table is single-read. This results in a very slow speed. During optimization, hash connections are used. A 40 MB small table is used as the driving table, a 9g table is connected, and a large 67G table is connected. Technologies used during optimization: 1. use_hash (a, B), using hash table join method 2. /* + parallel (a 5) */; parallel Processing 3. set db_file_multiblock_read_count to the maximum value of 4. workarea_size_policy is set to manual management 5. sort_area_size is set to close to the maximum of 6. hash_area_size is set to close to the maximum <p> 5 hours without results. The optimization results are less than 20 minutes, which is amazing. </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, 'yyyymmddhh24mis'), t. MSINFO_ID, t. ORG_ID, t. SERV_ID, t. SUBS_ID, t. OBJ_GRP_ID,. SUBS_CODE,. SUBS_STAT,. SUBS_STAT_REASON,. SUBS_STAT_DATE,. ACTION_ID,. ACTION_TYPE,. ACTION_EX_TYPE,. ACT_DATE,. REQ_ID,. STAFF_ID,. CMMS_CUST_CODE,. SPEED_VALUE, B. ACC_NBR, B. CUST_ID, B. SERV_NBR, B. CONSUME_GRADE, B. serv_lev, B. ACCOUNT_NBR, B. city_language_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 consumer a, SUMMARY_SJZ_GZ.RPT_COMM_CM_SERV_HIST B, SUMMARY_SJZ_GZ.TB_COMM_BA_MSDISC_TEMP t where. subs_id = t. subs_id and B. serv_id = t. serv_id </p>

</pre><pre>

Oracle HINT

Hint is often used in SQL statement optimization,
The following are 30 common "HINT" statements in Oracle during SQL optimization: 1./* + ALL_ROWS */
It indicates that the overhead-based optimization method is selected for the statement block, and the optimal throughput is obtained to minimize the resource consumption.
For example:
SELECT/* + ALL + _ ROWS */EMP_NO, EMP_NAM, DAT_IN from bsempms where EMP_NO = 'Scott ';

2./* + FIRST_ROWS */
It indicates that the overhead-based optimization method is selected for the statement block and the optimal response time is obtained to minimize the resource consumption.
For example:
SELECT/* + FIRST_ROWS */EMP_NO, EMP_NAM, DAT_IN from bsempms where EMP_NO = 'Scott ';

3./* + CHOOSE */
It indicates that if the data dictionary contains statistics about the access table, the overhead-based optimization method is used to obtain the optimal throughput;
It indicates that if no statistical information of the Access Table is found in the data dictionary, the rule overhead-based optimization method is used;
For example:
SELECT/* + CHOOSE */EMP_NO, EMP_NAM, DAT_IN from bsempms where EMP_NO = 'Scott ';

4./* + RULE */
Indicates the rule-based Optimization Method for the statement block.
For example:
SELECT/* + RULE */EMP_NO, EMP_NAM, DAT_IN from bsempms where EMP_NO = 'Scott ';

5./* + FULL (TABLE )*/
Indicates the method used to globally scan the table.
For example:
SELECT/* + FULL (A) */EMP_NO, EMP_NAM from bsempms a where EMP_NO = 'Scott ';

6./* + ROWID (TABLE )*/
The prompt explicitly indicates that the specified table is accessed Based on ROWID.
For example:
SELECT/* + ROWID (BSEMPMS) */* from bsempms where rowid> = 'aaaaaaaaaaaaaaa'
AND EMP_NO = 'Scott ';

7./* + CLUSTER (TABLE )*/
The prompt clearly indicates the access method for the selected cluster scan for the specified table, which is only valid for the cluster object.
For example:
SELECT/* + CLUSTER */BSEMPMS. EMP_NO, DPT_NO from bsempms, BSDPTMS
WHERE DPT_NO = 'tec304 'and bsempms. DPT_NO = BSDPTMS. DPT_NO;

8./* + INDEX (TABLE INDEX_NAME )*/
Indicates the method used to scan the selected index on the table.
For example:
SELECT/* + INDEX (BSEMPMS SEX_INDEX) USE SEX_INDEX because there are fewmale bsempms */from bsempms where sex = 'M ';

9./* + INDEX_ASC (TABLE INDEX_NAME )*/
Indicates the method used to scan the index in ascending order.
For example:
... The remaining full text>

Oracle Hint usage

1./* + ALL_ROWS */
It indicates that the overhead-based optimization method is selected for the statement block, and the optimal throughput is obtained to minimize the resource consumption.
For example:
SELECT/* + ALL + _ ROWS */EMP_NO, EMP_NAM, DAT_IN from bsempms where EMP_NO = 'Scott ';
2./* + FIRST_ROWS */
It indicates that the overhead-based optimization method is selected for the statement block and the optimal response time is obtained to minimize the resource consumption.
For example:
SELECT/* + FIRST_ROWS */EMP_NO, EMP_NAM, DAT_IN from bsempms where EMP_NO = 'Scott ';

3./* + CHOOSE */
It indicates that if the data dictionary contains statistics about the access table, the overhead-based optimization method is used to obtain the optimal throughput;
It indicates that if no statistical information of the Access Table is found in the data dictionary, the rule overhead-based optimization method is used;
For example:
SELECT/* + CHOOSE */EMP_NO, EMP_NAM, DAT_IN from bsempms where EMP_NO = 'Scott ';

4./* + RULE */
Indicates the rule-based Optimization Method for the statement block.
For example:
SELECT/* + RULE */EMP_NO, EMP_NAM, DAT_IN from bsempms where EMP_NO = 'Scott ';

5./* + FULL (TABLE )*/
Indicates the method used to globally scan the table.
For example:
SELECT/* + FULL (A) */EMP_NO, EMP_NAM from bsempms a where EMP_NO = 'Scott ';

6./* + ROWID (TABLE )*/
The prompt explicitly indicates that the specified table is accessed Based on ROWID.
For example:
SELECT/* + ROWID (BSEMPMS) */* from bsempms where rowid> = 'aaaaaaaaaaaaaaa'
AND EMP_NO = 'Scott ';

7./* + CLUSTER (TABLE )*/
The prompt clearly indicates the access method for the selected cluster scan for the specified table, which is only valid for the cluster object.
For example:
SELECT/* + CLUSTER */BSEMPMS. EMP_NO, DPT_NO from bsempms, BSDPTMS
WHERE DPT_NO = 'tec304 'and bsempms. DPT_NO = BSDPTMS. DPT_NO;

8./* + INDEX (TABLE INDEX_NAME )*/
Indicates the method used to scan the selected index on the table.
For example:
SELECT/* + INDEX (BSEMPMS SEX_INDEX) USE SEX_INDEX because there are fewmale bsempms */from bsempms where sex = 'M ';

9./* + INDEX_ASC (TABLE INDEX_NAME )*/
Indicates the method used to scan the index in ascending order.
For example:
SELECT/* + INDEX_ASC (BSEMPMS PK_BSEMPMS) */from bsempms where DPT_NO = 'Scott ';

10./* + INDEX_COMBINE */
Select a bitmap access path for the specified table. If the INDEX is... the remaining full text>