特殊情况如下
- 客户的统计信息是固定的，没办法收集统计信息 。
- SQL profile 是最后考虑方案，因为同样写法sql 比较多，几十条。
- Parallle 并行客户一般不考虑接受，OLTP 系统。
- 最好使用hint .
无法收集统计信息，怎样优化SQL。
首先在dev 环境收集统计信息，通过SQLHC ，收取结果。
其次使用收集信息的outline ,来代入，调优
结果如下：
SELECT
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$7286615E")
MERGE(@"SEL$5")
OUTLINE_LEAF(@"SEL$335DD26A")
MERGE(@"SEL$3")
OUTLINE_LEAF(@"SEL$E9CE8D49")
MERGE(@"SEL$07C48A2D")
OUTLINE_LEAF(@"SEL$B584FDD1")
MERGE(@"SEL$29F99543")
OUTLINE_LEAF(@"SEL$201A8568")
MERGE(@"SEL$10")
MERGE(@"SEL$11")
MERGE(@"SEL$12")
MERGE(@"SEL$13")
MERGE(@"SEL$6")
MERGE(@"SEL$7")
MERGE(@"SEL$8")
MERGE(@"SEL$9")
OUTLINE(@"SEL$4")
OUTLINE(@"SEL$5")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$3")
OUTLINE(@"SEL$17")
OUTLINE(@"SEL$07C48A2D")
MERGE(@"SEL$19")
OUTLINE(@"SEL$14")
OUTLINE(@"SEL$29F99543")
MERGE(@"SEL$16")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$10")
OUTLINE(@"SEL$11")
OUTLINE(@"SEL$12")
OUTLINE(@"SEL$13")
OUTLINE(@"SEL$6")
OUTLINE(@"SEL$7")
OUTLINE(@"SEL$8")
OUTLINE(@"SEL$9")
OUTLINE(@"SEL$18")
OUTLINE(@"SEL$19")
OUTLINE(@"SEL$15")
OUTLINE(@"SEL$16")
FULL(@"SEL$201A8568" "CONTAINER"@"SEL$6")
INDEX_RS_ASC(@"SEL$201A8568" "LOCN_GP_ACCESS_CTRL_GP_MEMBER"@"SEL$10" ("LOCN_GP_ACCESS_CTRL_GP_MEMBER"."LS_LEVEL_1"
"LOCN_GP_ACCESS_CTRL_GP_MEMBER"."LS_LEVEL_2" "LOCN_GP_ACCESS_CTRL_GP_MEMBER"."LS_LEVEL_3" "LOCN_GP_ACCESS_CTRL_GP_MEMBER"."LS_LEVEL_4"))
FULL(@"SEL$201A8568" "SHIPMENT"@"SEL$7")
FULL(@"SEL$201A8568" "SHIPMENT_INSTRUCTION"@"SEL$8")
NO_ACCESS(@"SEL$201A8568" "RFR"@"SEL$1")
FULL(@"SEL$201A8568" "LOCATION_SPACE"@"SEL$13")
INDEX_FFS(@"SEL$201A8568" "EMPTY_POOL_CONTAINER"@"SEL$9" ("EMPTY_POOL_CONTAINER"."EMPTY_POOL_CODE" "EMPTY_POOL_CONTAINER"."CNTR_ID"))
FULL(@"SEL$201A8568" "CNTR_DAMAGE_DTL"@"SEL$12")
FULL(@"SEL$201A8568" "LOCN_GP_ACCESS_CTRL_GP_MEMBER"@"SEL$11")
LEADING(@"SEL$201A8568" "CONTAINER"@"SEL$6" "LOCN_GP_ACCESS_CTRL_GP_MEMBER"@"SEL$10" "SHIPMENT"@"SEL$7" "SHIPMENT_INSTRUCTION"@"SEL$8"
"RFR"@"SEL$1" "LOCATION_SPACE"@"SEL$13" "EMPTY_POOL_CONTAINER"@"SEL$9" "CNTR_DAMAGE_DTL"@"SEL$12" "LOCN_GP_ACCESS_CTRL_GP_MEMBER"@"SEL$11")
USE_HASH(@"SEL$201A8568" "LOCN_GP_ACCESS_CTRL_GP_MEMBER"@"SEL$10")
USE_HASH(@"SEL$201A8568" "SHIPMENT"@"SEL$7")
USE_HASH(@"SEL$201A8568" "SHIPMENT_INSTRUCTION"@"SEL$8")
USE_HASH(@"SEL$201A8568" "RFR"@"SEL$1")
USE_HASH(@"SEL$201A8568" "LOCATION_SPACE"@"SEL$13")
USE_HASH(@"SEL$201A8568" "EMPTY_POOL_CONTAINER"@"SEL$9")
USE_HASH(@"SEL$201A8568" "CNTR_DAMAGE_DTL"@"SEL$12")
USE_HASH(@"SEL$201A8568" "LOCN_GP_ACCESS_CTRL_GP_MEMBER"@"SEL$11")
PX_JOIN_FILTER(@"SEL$201A8568" "LOCN_GP_ACCESS_CTRL_GP_MEMBER"@"SEL$11")
SWAP_JOIN_INPUTS(@"SEL$201A8568" "LOCN_GP_ACCESS_CTRL_GP_MEMBER"@"SEL$10")
SWAP_JOIN_INPUTS(@"SEL$201A8568" "SHIPMENT"@"SEL$7")
SWAP_JOIN_INPUTS(@"SEL$201A8568" "SHIPMENT_INSTRUCTION"@"SEL$8")
SWAP_JOIN_INPUTS(@"SEL$201A8568" "RFR"@"SEL$1")
SWAP_JOIN_INPUTS(@"SEL$201A8568" "LOCATION_SPACE"@"SEL$13")
SWAP_JOIN_INPUTS(@"SEL$201A8568" "EMPTY_POOL_CONTAINER"@"SEL$9")
SWAP_JOIN_INPUTS(@"SEL$201A8568" "CNTR_DAMAGE_DTL"@"SEL$12")
SWAP_JOIN_INPUTS(@"SEL$201A8568" "LOCN_GP_ACCESS_CTRL_GP_MEMBER"@"SEL$11")
NO_ACCESS(@"SEL$B584FDD1" "C"@"SEL$14")
FULL(@"SEL$B584FDD1" "REEFER_MONITORING_LOG"@"SEL$16")
LEADING(@"SEL$B584FDD1" "C"@"SEL$14" "REEFER_MONITORING_LOG"@"SEL$16")
USE_HASH(@"SEL$B584FDD1" "REEFER_MONITORING_LOG"@"SEL$16")
INDEX_FFS(@"SEL$E9CE8D49" "REEFER_MONITORING_LOG"@"SEL$19" ("REEFER_MONITORING_LOG"."REEFER_HANDLING_CYCLE_MODE"
"REEFER_MONITORING_LOG"."CNTR_ID" "REEFER_MONITORING_LOG"."REEFER_MONITORING_DATETIME"))
USE_HASH_AGGREGATION(@"SEL$E9CE8D49")
INDEX_RS_ASC(@"SEL$335DD26A" "GATE_LOG"@"SEL$3" ("GATE_LOG"."CNTR_ID" "GATE_LOG"."CMS_NO"))
INDEX_RS_ASC(@"SEL$7286615E" "GATE_LOG"@"SEL$5" ("GATE_LOG"."CNTR_ID" "GATE_LOG"."CMS_NO"))
END_OUTLINE_DATA
*/
CNTR.CREATED_BY_OPR_UNIT CREATED_BY_OPR_UNIT,
*********
from
*****.