Silent index hint: hint is manually allocated to the hash area. No result is returned in 5 hours. The optimization takes 20 minutes.

My colleague said that there was a statement that could not be returned within five hours. I asked my colleague to send it for help. 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. Developers always think that taking index is fast, so using index hint finally leads to a slow process. 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_v Alue, 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. cuz T_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 aaa a, bbb B, CCC t where. subs_id = T. subs_id and B. serv_id = T. serv_id -- the colleague said that the overhead is greater than that of the slave. There are 450 W .. The following is a running plan:

The size of the involved table: Owner segment_name segment_type size (MB) summary_sjz_gz CCC table 40 summary_sjz_gz BBB table partition 9016.1875 summary_sjz_gz AAA table partition 67330.25 The following is the optimization idea: forcible Indexing, as a result, 9 GB tables go through index full scan and then return 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 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. db_file_multiblock_read_count the maximum number of multi-block read partitions is set to 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 near maximum 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,. s Peed_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 summary_sjz_gz.aaa A, summary_sjz_gz.bbb B, summary_sjz_gz.ccc t where. subs_id = T. subs_id and B. serv_id = T. serv_id; plan_table_output -------------------------------------------------------------------------------- ----------------------------------------------------------------- Plan hash value: 382646192 bytes ------------------- | ID | operation | Name | rows | bytes | cost (% CPU) | time | pstart | pstop | TQ | in-out | PQ distrib | -------------------------------------------------------- California | 0 | SELECT statement | 781k | 453m | 260 K (1) | 01:18:03 | 1 | Px Coordinator | 2 | Px send QC (random) |: tq10002 | 781k | 453m | 260 K (1) | 01:18:03 | Q1, 02 | p-> S | QC (RAND) | * 3 | hash join | 781k | 453m | 260 K (1) | 01:18:03 | Q1, 02 | PCWP | 4 | Px receive | | 781k | micron M | 26771 (2) | 00:08:02 | Q1, 02 | PCWP | 5 | Px send broadcast |: tq10001 | 781k | micron M | 26771 (2) | 00:08:02 | Q1, 01 | p-> p | broadcast | * 6 | hash join | 781k | micron M | 26771 (2) | 00:08:02 | Q1, 01 | PCWP | 7 | Px receive | 781k | 73m | 327 (2) | 00:00:06 | Q1, 01 | PCWP | 8 | Px send broadcast |: tq10000 | 781k | 73m | 327 (2) | 00:00:06 | Q1, 00 | p-> p | broa Dcast | 9 | Px block iterator | 781k | 73m | 327 (2) | 00:00:06 | Q1, 00 | pcwc | 10 | table access full | CCC | 781k | 73m | 327 (2) | 00:00:06 | Q1, 00 | PCWP | 11 | Px block iterator | 23m | 9096m | 26427 (2) | 00:07:56 | 1 | 17 | Q1, 01 | pcwc | 12 | table access full | BBB | 23m | 9096m | 26427 (2) | 00:07:56 | 1 | 17 | Q1, 01 | PCWP | 13 | Px block iterator | 204m | 18g | 233 K (1) | 01:09:58 | 1 | 20 | Q1, 02 | pcwc | 14 | table access full | AAA | 204m | 18g | 233 K (1) | 01:09:58 | 1 | 20 | Q1, 02 | PCWP | certificate ------------------- predicate information (identified by Operation ID): ------------------------------------------------- 3-access ("". "s Ubs_id "=" T ". "subs_id") 6-access ("B ". "serv_id" = "T ". "serv_id") -- no result is returned in 5 hours. After optimization, the result is returned in less than 20 minutes.

Silent index hint: hint is manually allocated to the hash area. No result is returned in 5 hours. The optimization takes 20 minutes.