I find the applying slowly sql which execute by full scan
I believe the sql execute in the primary database by index
how to change the sql execute plan
and I used oracle tuning advise ,but I can't get the better advise
the sql ：
update /*+ streams or_expand(p "PRODUCT_SN"
)restrict_all_ref_cons */ "SMP"."WIP_RUNCARD_BACKUP" p set
"AK_CHECK"=decode(:1,'N',"AK_CHECK",:2),
"ATTRIBUTE01"=decode(:3,'N',"ATTRIBUTE01",:4),
"ATTRIBUTE02"=decode(:5,'N',"ATTRIBUTE02",:6),
"ATTRIBUTE03"=decode(:7,'N',"ATTRIBUTE03",:8),
"ATTRIBUTE04"=decode(:9,'N',"ATTRIBUTE04",:10),
"ATTRIBUTE05"=decode(:11,'N',"ATTRIBUTE05",:12),
"ATTRIBUTE06"=decode(:13,'N',"ATTRIBUTE06",:14),
"ATTRIBUTE07"=decode(:15,'N',"ATTRIBUTE07",:16),
"ATTRIBUTE08"=decode(:17,'N',"ATTRIBUTE08",:18),
"ATTRIBUTE09"=decode(:19,'N',"ATTRIBUTE09",:20),
"ATTRIBUTE10"=decode(:21,'N',"ATTRIBUTE10",:22),
"BATCH_KEY"=decode(:23,'N',"BATCH_KEY",:24),
"BIOS"=decode(:25,'N',"BIOS",:26),
"BOARD_NAME"=decode(:27,'N',"BOARD_NAME",:28),
"BOX_NO"=decode(:29,'N',"BOX_NO",:30),
"BOX_TYPE"=decode(:31,'N',"BOX_TYPE",:32),
"COA"=decode(:33,'N',"COA",:34),
"CONFIGURATION"=decode(:35,'N',"CONFIGURATION",:36),
"COUNTRY_CODE"=decode(:37,'N',"COUNTRY_CODE",:38),
"CREATE_TIME"=decode(:39,'N',"CREATE_TIME",:40),
"CRITICALCOMP"=decode(:41,'N',"CRITICALCOMP",:42),
"CURRENT_SITE"=decode(:43,'N',"CURRENT_SITE",:44),
"DATE01"=decode(:45,'N',"DATE01",:46),
"DATE02"=decode(:47,'N',"DATE02",:48),
"DATE03"=decode(:49,'N',"DATE03",:50),
"DATE04"=decode(:51,'N',"DATE04",:52),
"DATE05"=decode(:53,'N',"DATE05",:54),
"DATE06"=decode(:55,'N',"DATE06",:56),
"DATE07"=decode(:57,'N',"DATE07",:58),
"DATE08"=decode(:59,'N',"DATE08",:60),
"DATE09"=decode(:61,'N',"DATE09",:62),
"DATE10"=decode(:63,'N',"DATE10",:64),
"DATE_CODE"=decode(:65,'N',"DATE_CODE",:66),
"DIP"=decode(:67,'N',"DIP",:68),
"DN_STREAM"=decode(:69,'N',"DN_STREAM",:70),
"DOUBLE_CHECK"=decode(:71,'N',"DOUBLE_CHECK",:72),
"ECO"=decode(:73,'N',"ECO",:74),
"ENABLED"=decode(:75,'N',"ENABLED",:76),
"EXPIRE_DATE"=decode(:77,'N',"EXPIRE_DATE",:78),
"FACTORY_SN"=decode(:79,'N',"FACTORY_SN",:80),
"FAILCOUNT"=decode(:81,'N',"FAILCOUNT",:82),
"FEATURE_BITS"=decode(:83,'N',"FEATURE_BITS",:84),
"FW_VERSION"=decode(:85,'N',"FW_VERSION",:86),
"HW_VERSION"=decode(:87,'N',"HW_VERSION",:88),
"INVOICE"=decode(:89,'N',"INVOICE",:90),
"INVOICE_PRINT_TIME"=decode(:91,'N',"INVOICE_PRINT_TIME",:92),
"LANCODE"=decode(:93,'N',"LANCODE",:94),
"LAST_ROUTE"=decode(:95,'N',"LAST_ROUTE",:96),
"LAST_ROUTE_COUNTER"=decode(:97,'N',"LAST_ROUTE_COUNTER",:98),
"MASTER_KEY"=decode(:99,'N',"MASTER_KEY",:100),
"MEMO"=decode(:101,'N',"MEMO",:102),
"MODEL1"=decode(:103,'N',"MODEL1",:104),
"MODEL2"=decode(:105,'N',"MODEL2",:106),
"MODEL3"=decode(:107,'N',"MODEL3",:108),
"MODEL4"=decode(:109,'N',"MODEL4",:110),
"MODEL5"=decode(:111,'N',"MODEL5",:112),
"NR_FLOW"=decode(:113,'N',"NR_FLOW",:114),
"NR_NOISE1"=decode(:115,'N',"NR_NOISE1",:116),
"NR_NOISE2"=decode(:117,'N',"NR_NOISE2",:118),
"NR_NOISE3"=decode(:119,'N',"NR_NOISE3",:120),
"NR_NOISE4"=decode(:121,'N',"NR_NOISE4",:122),
"NR_PRESSURE"=decode(:123,'N',"NR_PRESSURE",:124),
"NR_VOL"=decode(:125,'N',"NR_VOL",:126),
"OLD_SN"=decode(:127,'N',"OLD_SN",:128),
"OP_FLAG"=decode(:129,'N',"OP_FLAG",:130),
"OQA_LABEL_SN_KEY"=decode(:131,'N',"OQA_LABEL_SN_KEY",:132),
"ORG_ID"=decode(:133,'N',"ORG_ID",:134),
"PACKING_DATE"=decode(:135,'N',"PACKING_DATE",:136),
"PALLET_NO"=decode(:137,'N',"PALLET_NO",:138),
"PARENT_SN"=decode(:139,'N',"PARENT_SN",:140),
"PCB"=decode(:141,'N',"PCB",:142),
"PENDING_KIND"=decode(:143,'N',"PENDING_KIND",:144),
"PENDING_REASON"=decode(:145,'N',"PENDING_REASON",:146),
"PRODUCT_SN"=decode(:147,'N',"PRODUCT_SN",:148),
"PRODUCT_SN2"=decode(:149,'N',"PRODUCT_SN2",:150),
"PRODUCT_SN3"=decode(:151,'N',"PRODUCT_SN3",:152),
"PROGRAM_ID"=decode(:153,'N',"PROGRAM_ID",:154),
"PROGRAM_UPDATE_TIME"=decode(:155,'N',"PROGRAM_UPDATE_TIME",:156)
, "QTY"=decode(:157,'N',"QTY",:158),
"RELATION_FLAG"=decode(:159,'N',"RELATION_FLAG",:160),
"REPAIRED_COUNTER"=decode(:161,'N',"REPAIRED_COUNTER",:162),
"REPAIRED_TIME"=decode(:163,'N',"REPAIRED_TIME",:164),
"REPLACED"=decode(:165,'N',"REPLACED",:166),
"REVISION_NO"=decode(:167,'N',"REVISION_NO",:168),
"REVISION_OLD"=decode(:169,'N',"REVISION_OLD",:170),
"ROUTE_KEY"=decode(:171,'N',"ROUTE_KEY",:172),
"SCRAP_QTY"=decode(:173,'N',"SCRAP_QTY",:174),
"SEGMENT01"=decode(:175,'N',"SEGMENT01",:176),
"SEGMENT02"=decode(:177,'N',"SEGMENT02",:178),
"SEGMENT03"=decode(:179,'N',"SEGMENT03",:180),
"SEGMENT04"=decode(:181,'N',"SEGMENT04",:182),
"SEGMENT05"=decode(:183,'N',"SEGMENT05",:184),
"SEGMENT06"=decode(:185,'N',"SEGMENT06",:186),
"SEGMENT07"=decode(:187,'N',"SEGMENT07",:188),
"SEGMENT08"=decode(:189,'N',"SEGMENT08",:190),
"SEGMENT09"=decode(:191,'N',"SEGMENT09",:192),
"SEGMENT10"=decode(:193,'N',"SEGMENT10",:194),
"SHIPPING_LIST"=decode(:195,'N',"SHIPPING_LIST",:196),
"SHIP_BY"=decode(:197,'N',"SHIP_BY",:198),
"SHIP_DATE"=decode(:199,'N',"SHIP_DATE",:200),
"SHIP_LINES_KEY"=decode(:201,'N',"SHIP_LINES_KEY",:202),
"SHIP_TIME"=decode(:203,'N',"SHIP_TIME",:204),
"SN"=decode(:205,'N',"SN",:206),
"SN_KEY"=decode(:207,'N',"SN_KEY",:208),
"SN_UNIQUE"=decode(:209,'N',"SN_UNIQUE",:210),
"SO_DETAIL_KEY"=decode(:211,'N',"SO_DETAIL_KEY",:212),
"STATUS"=decode(:213,'N',"STATUS",:214),
"SUBINVENTORY"=decode(:215,'N',"SUBINVENTORY",:216),
"SW_VERSION"=decode(:217,'N',"SW_VERSION",:218),
"TEST_FILE"=decode(:219,'N',"TEST_FILE",:220),
"TIME"=decode(:221,'N',"TIME",:222),
"TLEVEL"=decode(:223,'N',"TLEVEL",:224),
"TRACKING_NO"=decode(:225,'N',"TRACKING_NO",:226),
"TURNIN_PRINT_TIME"=decode(:227,'N',"TURNIN_PRINT_TIME",:228),
"TURNIN_TIME"=decode(:229,'N',"TURNIN_TIME",:230),
"TURN_IN_ORD_NO"=decode(:231,'N',"TURN_IN_ORD_NO",:232),
"UPDATE_BY"=decode(:233,'N',"UPDATE_BY",:234),
"UP_STREAM"=decode(:235,'N',"UP_STREAM",:236),
"USED_STATUS"=decode(:237,'N',"USED_STATUS",:238),
"WARRANTY"=decode(:239,'N',"WARRANTY",:240),
"WIP_ROUTE"=decode(:241,'N',"WIP_ROUTE",:242),
"WIP_ROUTE_BACKUP"=decode(:243,'N',"WIP_ROUTE_BACKUP",:244),
"WO_KEY"=decode(:245,'N',"WO_KEY",:246) where (:247="AK_CHECK"
or(:247 is null and "AK_CHECK" is null)) and(:248="ATTRIBUTE01"
or(:248 is null and "ATTRIBUTE01" is null))
and(:249="ATTRIBUTE02" or(:249 is null and "ATTRIBUTE02" is
null)) and(:250="ATTRIBUTE03" or(:250 is null and "ATTRIBUTE03"
is null)) and(:251="ATTRIBUTE04" or(:251 is null and
"ATTRIBUTE04" is null)) and(:252="ATTRIBUTE05" or(:252 is null
and "ATTRIBUTE05" is null)) and(:253="ATTRIBUTE06" or(:253 is
null and "ATTRIBUTE06" is null)) and(:254="ATTRIBUTE07" or(:254
is null and "ATTRIBUTE07" is null)) and(:255="ATTRIBUTE08"
or(:255 is null and "ATTRIBUTE08" is null))
and(:256="ATTRIBUTE09" or(:256 is null and "ATTRIBUTE09" is
null)) and(:257="ATTRIBUTE10" or(:257 is null and "ATTRIBUTE10"
is null)) and(:258="BATCH_KEY" or(:258 is null and "BATCH_KEY"
is null)) and(:259="BIOS" or(:259 is null and "BIOS" is null))
and(:260="BOARD_NAME" or(:260 is null and "BOARD_NAME" is null))
and(:261="BOX_NO" or(:261 is null and "BOX_NO" is null))
and(:262="BOX_TYPE" or(:262 is null and "BOX_TYPE" is null))
and(:263="COA" or(:263 is null and "COA" is null))
and(:264="CONFIGURATION" or(:264 is null and "CONFIGURATION" is
null)) and(:265="COUNTRY_CODE" or(:265 is null and
"COUNTRY_CODE" is null)) and(:266="CREATE_TIME" or(:266 is null
and "CREATE_TIME" is null)) and(:267="CRITICALCOMP" or(:267 is
null and "CRITICALCOMP" is null)) and(:268="CURRENT_SITE"
or(:268 is null and "CURRENT_SITE" is null)) and(:269="DATE01"
or(:269 is null and "DATE01" is null)) and(:270="DATE02" or(:270
is null and "DATE02" is null)) and(:271="DATE03" or(:271 is null
and "DATE03" is null)) and(:272="DATE04" or(:272 is null and
"DATE04" is null)) and(:273="DATE05" or(:273 is null and
"DATE05" is null)) and(:274="DATE06" or(:274 is null and
"DATE06" is null)) and(:275="DATE07" or(:275 is null and
"DATE07" is null)) and(:276="DATE08" or(:276 is null and
"DATE08" is null)) and(:277="DATE09" or(:277 is null and
"DATE09" is null)) and(:278="DATE10" or(:278 is null and
"DATE10" is null)) and(:279="DATE_CODE" or(:279 is null and
"DATE_CODE" is null)) and(:280="DIP" or(:280 is null and "DIP"
is null)) and(:281="DN_STREAM" or(:281 is null and "DN_STREAM"
is null)) and(:282="DOUBLE_CHECK" or(:282 is null and
"DOUBLE_CHECK" is null)) and(:283="ECO" or(:283 is null and
"ECO" is null)) and(:284="ENABLED" or(:284 is null and "ENABLED"
is null)) and(:285="EXPIRE_DATE" or(:285 is null and
"EXPIRE_DATE" is null)) and(:286="FACTORY_SN" or(:286 is null
and "FACTORY_SN" is null)) and(:287="FAILCOUNT" or(:287 is null
and "FAILCOUNT" is null)) and(:288="FEATURE_BITS" or(:288 is
null and "FEATURE_BITS" is null)) and(:289="FW_VERSION" or(:289
is null and "FW_VERSION" is null)) and(:290="HW_VERSION" or(:290
is null and "HW_VERSION" is null)) and(:291="INVOICE" or(:291 is
null and "INVOICE" is null)) and(:292="INVOICE_PRINT_TIME"
or(:292 is null and "INVOICE_PRINT_TIME" is null))
and(:293="LANCODE" or(:293 is null and "LANCODE" is null))
and(:294="LAST_ROUTE" or(:294 is null and "LAST_ROUTE" is null))
and(:295="LAST_ROUTE_COUNTER" or(:295 is null and
"LAST_ROUTE_COUNTER" is null)) and(:296="MASTER_KEY" or(:296 is
null and "MASTER_KEY" is null)) and(:297="MEMO" or(:297 is null
and "MEMO" is null)) and(:298="MODEL1" or(:298 is null and
"MODEL1" is null)) and(:299="MODEL2" or(:299 is null and
"MODEL2" is null)) and(:300="MODEL3" or(:300 is null and
"MODEL3" is null)) and(:301="MODEL4" or(:301 is null and
"MODEL4" is null)) and(:302="MODEL5" or(:302 is null and
"MODEL5" is null)) and(:303="NR_FLOW" or(:303 is null and
"NR_FLOW" is null)) and(:304="NR_NOISE1" or(:304 is null and
"NR_NOISE1" is null)) and(:305="NR_NOISE2" or(:305 is null and
"NR_NOISE2" is null)) and(:306="NR_NOISE3" or(:306 is null and
"NR_NOISE3" is null)) and(:307="NR_NOISE4" or(:307 is null and
"NR_NOISE4" is null)) and(:308="NR_PRESSURE" or(:308 is null and
"NR_PRESSURE" is null)) and(:309="NR_VOL" or(:309 is null and
"NR_VOL" is null)) and(:310="OLD_SN" or(:310 is null and
"OLD_SN" is null)) and(:311="OP_FLAG" or(:311 is null and
"OP_FLAG" is null)) and(:312="OQA_LABEL_SN_KEY" or(:312 is null
and "OQA_LABEL_SN_KEY" is null)) and(:313="ORG_ID" or(:313 is
null and "ORG_ID" is null)) and(:314="PACKING_DATE" or(:314 is
null and "PACKING_DATE" is null)) and(:315="PALLET_NO" or(:315
is null and "PALLET_NO" is null)) and(:316="PARENT_SN" or(:316
is null and "PARENT_SN" is null)) and(:317="PCB" or(:317 is null
and "PCB" is null)) and(:318="PENDING_KIND" or(:318 is null and
"PENDING_KIND" is null)) and(:319="PENDING_REASON" or(:319 is
null and "PENDING_REASON" is null)) and(:320="PRODUCT_SN"
or(:320 is null and "PRODUCT_SN" is null))
and(:321="PRODUCT_SN2" or(:321 is null and "PRODUCT_SN2" is
null)) and(:322="PRODUCT_SN3" or(:322 is null and "PRODUCT_SN3"
is null)) and(:323="PROGRAM_ID" or(:323 is null and "PROGRAM_ID"
is null)) and(:324="PROGRAM_UPDATE_TIME" or(:324 is null and
"PROGRAM_UPDATE_TIME" is null)) and(:325="QTY" or(:325 is null
and "QTY" is null)) and(:326="RELATION_FLAG" or(:326 is null and
"RELATION_FLAG" is null)) and(:327="REPAIRED_COUNTER" or(:327 is
null and "REPAIRED_COUNTER" is null)) and(:328="REPAIRED_TIME"
or(:328 is null and "REPAIRED_TIME" is null))
and(:329="REPLACED" or(:329 is null and "REPLACED" is null))
and(:330="REVISION_NO" or(:330 is null and "REVISION_NO" is
null)) and(:331="REVISION_OLD" or(:331 is null and
"REVISION_OLD" is null)) and(:332="ROUTE_KEY" or(:332 is null
and "ROUTE_KEY" is null)) and(:333="SCRAP_QTY" or(:333 is null
and "SCRAP_QTY" is null)) and(:334="SEGMENT01" or(:334 is null
and "SEGMENT01" is null)) and(:335="SEGMENT02" or(:335 is null
and "SEGMENT02" is null)) and(:336="SEGMENT03" or(:336 is null
and "SEGMENT03" is null)) and(:337="SEGMENT04" or(:337 is null
and "SEGMENT04" is null)) and(:338="SEGMENT05" or(:338 is null
and "SEGMENT05" is null)) and(:339="SEGMENT06" or(:339 is null
and "SEGMENT06" is null)) and(:340="SEGMENT07" or(:340 is null
and "SEGMENT07" is null)) and(:341="SEGMENT08" or(:341 is null
and "SEGMENT08" is null)) and(:342="SEGMENT09" or(:342 is null
and "SEGMENT09" is null)) and(:343="SEGMENT10" or(:343 is null
and "SEGMENT10" is null)) and(:344="SHIPPING_LIST" or(:344 is
null and "SHIPPING_LIST" is null)) and(:345="SHIP_BY" or(:345 is
null and "SHIP_BY" is null)) and(:346="SHIP_DATE" or(:346 is
null and "SHIP_DATE" is null)) and(:347="SHIP_LINES_KEY" or(:347
is null and "SHIP_LINES_KEY" is null)) and(:348="SHIP_TIME"
or(:348 is null and "SHIP_TIME" is null)) and(:349="SN" or(:349
is null and "SN" is null)) and(:350="SN_KEY" or(:350 is null and
"SN_KEY" is null)) and(:351="SN_UNIQUE" or(:351 is null and
"SN_UNIQUE" is null)) and(:352="SO_DETAIL_KEY" or(:352 is null
and "SO_DETAIL_KEY" is null)) and(:353="STATUS" or(:353 is null
and "STATUS" is null)) and(:354="SUBINVENTORY" or(:354 is null
and "SUBINVENTORY" is null)) and(:355="SW_VERSION" or(:355 is
null and "SW_VERSION" is null)) and(:356="TEST_FILE" or(:356 is
null and "TEST_FILE" is null)) and(:357="TIME" or(:357 is null
and "TIME" is null)) and(:358="TLEVEL" or(:358 is null and
"TLEVEL" is null)) and(:359="TRACKING_NO" or(:359 is null and
"TRACKING_NO" is null)) and(:360="TURNIN_PRINT_TIME" or(:360 is
null and "TURNIN_PRINT_TIME" is null)) and(:361="TURNIN_TIME"
or(:361 is null and "TURNIN_TIME" is null))
and(:362="TURN_IN_ORD_NO" or(:362 is null and "TURN_IN_ORD_NO"
is null)) and(:363="UPDATE_BY" or(:363 is null and "UPDATE_BY"
is null)) and(:364="UP_STREAM" or(:364 is null and "UP_STREAM"
is null)) and(:365="USED_STATUS" or(:365 is null and
"USED_STATUS" is null)) and(:366="WARRANTY" or(:366 is null and
"WARRANTY" is null)) and(:367="WIP_ROUTE" or(:367 is null and
"WIP_ROUTE" is null)) and(:368="WIP_ROUTE_BACKUP" or(:368 is
null and "WIP_ROUTE_BACKUP" is null)) and(:369="WO_KEY" or(:369
is null and "WO_KEY" is null)) and rownum < 2

John WatsonMessages: 6581Registered: January 2010 Location: Global Village

Senior Member

This is a common problem in Streams too, perhaps you need to launch more preparer and applier processes. You can do this with dbms_logstdby.apply_set to create a bigger pool of apply servers.
---------------------------
edit: corrected typo, dbms_logstby for dbms_logstdby