SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Query running long when running with application

Anand YadavNov 12 2017 — edited Jan 4 2018

DB Details:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

PL/SQL Release 12.1.0.2.0 - Production

CORE 12.1.0.2.0 Production

TNS for IBM/AIX RISC System/6000: Version 12.1.0.2.0 - Production

NLSRTL Version 12.1.0.2.0 - Production

Hi ,

I have a update query that run's for 1 hour when executed by java process but finishes in 6 seconds if executed standalone .

Below is the query details and the plan.

UPDATE /* LevelSKU_itemHint_3 */

ITEM i

SET i.PlanLevel =

NVL (

(SELECT MAX (s.PlanLevel + 1)

FROM SKU s

WHERE s.item IN (SELECT /*

LevelSKU_skuwhuitemHint */

/*LevelSKU_skuwhuItemHint*/

ParentItem

FROM SKUWHEREUSED skuwhu

WHERE skuwhu.SubordItem = i.item

AND skuwhu.ParentItem <> i.item)),

-1)

WHERE i.PlanLevel = -1;

Plan hash value: 3213331517

------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------

| 0 | UPDATE STATEMENT | | | | 47M(100)| |

|* 2 | TABLE ACCESS FULL | ITEM | 15461 | 1932K| 112 (0)| 00:00:01 |

| 3 | SORT AGGREGATE | | 1 | 27 | | |

|* 4 | HASH JOIN RIGHT SEMI | | 2604K| 67M| 3095 (1)| 00:00:01 |

|* 5 | INDEX RANGE SCAN | SKUWHEREUSED_PARSUB | 6892 | 114K| 23 (0)| 00:00:01 |

|* 6 | INDEX FAST FULL SCAN| SKU_IDX3677 | 5983K| 57M| 3061 (1)| 00:00:01 |

------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

1 - UPD$1

2 - UPD$1 / I@UPD$1

3 - SEL$5DA710D3

5 - SEL$5DA710D3 / SKUWHU@SEL$2

6 - SEL$5DA710D3 / S@SEL$1

Outline Data

-------------

/*+

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE('12.1.0.2')

DB_VERSION('12.1.0.2')

ALL_ROWS

OUTLINE_LEAF(@"SEL$5DA710D3")

UNNEST(@"SEL$2")

OUTLINE_LEAF(@"UPD$1")

OUTLINE(@"SEL$1")

OUTLINE(@"SEL$2")

FULL(@"UPD$1" "I"@"UPD$1")

INDEX_FFS(@"SEL$5DA710D3" "S"@"SEL$1" ("SKU"."ITEM" "SKU"."PLANLEVEL"))

INDEX(@"SEL$5DA710D3" "SKUWHU"@"SEL$2" ("SKUWHEREUSED"."SUBORDITEM"

"SKUWHEREUSED"."PARENTITEM"))

LEADING(@"SEL$5DA710D3" "S"@"SEL$1" "SKUWHU"@"SEL$2")

USE_HASH(@"SEL$5DA710D3" "SKUWHU"@"SEL$2")

SWAP_JOIN_INPUTS(@"SEL$5DA710D3" "SKUWHU"@"SEL$2")

PARTIAL_JOIN(@"SEL$5DA710D3" "SKUWHU"@"SEL$2")

END_OUTLINE_DATA

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter("I"."PLANLEVEL"=(-1))

4 - access("S"."ITEM"="PARENTITEM")

5 - access("SKUWHU"."SUBORDITEM"=:B1)

filter("SKUWHU"."PARENTITEM"<>:B1)

6 - filter("S"."ITEM"<>:B1)

Column Projection Information (identified by operation id):

-----------------------------------------------------------

2 - (upd=5,8; cmp=2,3,4,5,6,7,8,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27

,28; cpy=2,5,9) "I".ROWID[ROWID,10], "I"."ITEM"[VARCHAR2,200],

"I"."DESCR"[VARCHAR2,200], "I"."UOM"[VARCHAR2,200], "I"."PLANLEVEL"[NUMBER,22],

"I"."PERISHABLESW"[NUMBER,22], "I"."RESTRICTPLANMODE"[NUMBER,22],

"FF_TRIGGER_CONTROL"[NUMBER,22], "I"."SUPSNGROUPNUM"[NUMBER,22],

"I"."U_PROMOSW"[NUMBER,22], "I"."U_ABC"[VARCHAR2,200], "I"."U_FAMILY1"[VARCHAR2,200],

"I"."U_FAMILY2"[VARCHAR2,200], "I"."U_BULKSW"[NUMBER,22],

"I"."U_FAMILYTYPE"[VARCHAR2,12], "I"."U_ACTIVE"[NUMBER,22], "I"."U_MRIN"[VARCHAR2,120],

"I"."U_PRODUCTTYPE"[VARCHAR2,12], "I"."U_ITEMTYPE"[VARCHAR2,12],

"I"."U_YIELDSW"[NUMBER,22], "I"."U_UNITSQTY"[NUMBER,22], "I"."U_GROUPE1"[VARCHAR2,200],

"I"."U_GROUPE2"[VARCHAR2,200], "I"."U_SHELFLIFEDUR"[NUMBER,22],

"I"."U_ORDERTYPE"[VARCHAR2,200], "I"."U_MARKET"[VARCHAR2,200],

"I"."U_ERP_ID"[VARCHAR2,200], "I"."U_DEL_SCH_TYP"[VARCHAR2,200]

3 - (#keys=0) MAX("S"."PLANLEVEL"+1)[22]

4 - (#keys=1) "S"."ITEM"[VARCHAR2,200], "S"."PLANLEVEL"[NUMBER,22]

5 - "SKUWHU".ROWID[ROWID,10], "SKUWHU"."PARENTITEM"[VARCHAR2,200],

"SKUWHU"."SUBORDITEM"[VARCHAR2,200]

6 - "S"."ITEM"[VARCHAR2,200], "S"."PLANLEVEL"[NUMBER,22]

Note

-----

- Warning: basic plan statistics not available. These are only collected when:

* hint 'gather_plan_statistics' is used for the statement or

* parameter 'statistics_level' is set to 'ALL', at session or system level

Below is plan when executed standalone.

Plan hash value: 3213331517

-----------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------

| 0 | UPDATE STATEMENT | | | | 50M(100)| |

|* 2 | TABLE ACCESS FULL | ITEM | 15461 | 1857K| 112 (0)| 00:00:01 |

| 3 | SORT AGGREGATE | | 1 | 27 | | |

|* 4 | HASH JOIN RIGHT SEMI | | 2604K| 67M| 3258 (1)| 00:00:01 |

|* 5 | INDEX RANGE SCAN | SKUWHEREUSED_PARSUB | 6892 | 114K| 23 (0)| 00:00:01 |

|* 6 | INDEX FAST FULL SCAN| SKU_IDX3677 | 5983K| 57M| 3225 (1)| 00:00:01 |

-----------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

1 - UPD$1

2 - UPD$1 / I@UPD$1

3 - SEL$5DA710D3

5 - SEL$5DA710D3 / SKUWHU@SEL$2

6 - SEL$5DA710D3 / S@SEL$1

Outline Data

-------------

/*+

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE('12.1.0.2')

DB_VERSION('12.1.0.2')

ALL_ROWS

OUTLINE_LEAF(@"SEL$5DA710D3")

UNNEST(@"SEL$2")

OUTLINE_LEAF(@"UPD$1")

OUTLINE(@"SEL$1")

OUTLINE(@"SEL$2")

FULL(@"UPD$1" "I"@"UPD$1")

INDEX_FFS(@"SEL$5DA710D3" "S"@"SEL$1" ("SKU"."ITEM" "SKU"."PLANLEVEL"))

INDEX(@"SEL$5DA710D3" "SKUWHU"@"SEL$2" ("SKUWHEREUSED"."SUBORDITEM"

"SKUWHEREUSED"."PARENTITEM"))

LEADING(@"SEL$5DA710D3" "S"@"SEL$1" "SKUWHU"@"SEL$2")

USE_HASH(@"SEL$5DA710D3" "SKUWHU"@"SEL$2")

SWAP_JOIN_INPUTS(@"SEL$5DA710D3" "SKUWHU"@"SEL$2")

PARTIAL_JOIN(@"SEL$5DA710D3" "SKUWHU"@"SEL$2")

END_OUTLINE_DATA

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter("I"."PLANLEVEL"=(-1))

4 - access("S"."ITEM"="PARENTITEM")

5 - access("SKUWHU"."SUBORDITEM"=:B1)

filter("SKUWHU"."PARENTITEM"<>:B1)

6 - filter("S"."ITEM"<>:B1)

Column Projection Information (identified by operation id):

-----------------------------------------------------------

2 - (upd=5; cmp=2,3,4,5,6,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26;

cpy=2,5,7) "I".ROWID[ROWID,10], "I"."ITEM"[VARCHAR2,200], "I"."DESCR"[VARCHAR2,200],

"I"."UOM"[VARCHAR2,200], "I"."PLANLEVEL"[NUMBER,22], "I"."PERISHABLESW"[NUMBER,22],

"I"."SUPSNGROUPNUM"[NUMBER,22], "I"."U_PROMOSW"[NUMBER,22], "I"."U_ABC"[VARCHAR2,200],

"I"."U_FAMILY1"[VARCHAR2,200], "I"."U_FAMILY2"[VARCHAR2,200],

"I"."U_BULKSW"[NUMBER,22], "I"."U_FAMILYTYPE"[VARCHAR2,12], "I"."U_ACTIVE"[NUMBER,22],

"I"."U_MRIN"[VARCHAR2,120], "I"."U_PRODUCTTYPE"[VARCHAR2,12],

"I"."U_ITEMTYPE"[VARCHAR2,12], "I"."U_YIELDSW"[NUMBER,22],

"I"."U_UNITSQTY"[NUMBER,22], "I"."U_GROUPE1"[VARCHAR2,200],

"I"."U_GROUPE2"[VARCHAR2,200], "I"."U_SHELFLIFEDUR"[NUMBER,22],

"I"."U_ORDERTYPE"[VARCHAR2,200], "I"."U_MARKET"[VARCHAR2,200],

"I"."U_ERP_ID"[VARCHAR2,200], "I"."U_DEL_SCH_TYP"[VARCHAR2,200]

3 - (#keys=0) MAX("S"."PLANLEVEL"+1)[22]

4 - (#keys=1) "S"."ITEM"[VARCHAR2,200], "S"."PLANLEVEL"[NUMBER,22]

5 - "SKUWHU".ROWID[ROWID,10], "SKUWHU"."PARENTITEM"[VARCHAR2,200],

"SKUWHU"."SUBORDITEM"[VARCHAR2,200]

6 - "S"."ITEM"[VARCHAR2,200], "S"."PLANLEVEL"[NUMBER,22]

Any quick inputs that you may suggest based on above information?

Below are the details from v$sql that might give more inputs.

0y3ah10c2p1fv is good running but 6zg6y5r6vbvw3 is very slow.

SQL_ID	SHARABLE_MEM	PERSISTENT_MEM	RUNTIME_MEM	EXECUTIONS	END_OF_FETCH_COUNT	USERS_EXECUTING	LOADS	FIRST_LOAD_TIME	INVALIDATIONS	PARSE_CALLS	DISK_READS	DIRECT_WRITES	BUFFER_GETS	CONCURRENCY_WAIT_TIME	USER_IO_WAIT_TIME	PLSQL_EXEC_TIME	ROWS_PROCESSED	COMMAND_TYPE	OPTIMIZER_MODE	OPTIMIZER_COST	MODULE	CPU_TIME	ELAPSED_TIME	PROGRAM_ID	PROGRAM_LINE#	EXACT_MATCHING_SIGNATURE	FORCE_MATCHING_SIGNATURE	LAST_ACTIVE_TIME	IO_INTERCONNECT_BYTES	PHYSICAL_READ_REQUESTS	PHYSICAL_READ_BYTES	PHYSICAL_WRITE_REQUESTS	PHYSICAL_WRITE_BYTES	LOCKED_TOTAL	PINNED_TOTAL
0y3ah10c2p1fv	89,021	32,920	31,352	2	2	0	1	2017-11-12/08:15:40	1	2	0	0	113,704	0	95	2,904,093	30,922	6	ALL_ROWS	50,387,511	TOAD 12.0.0.61	2,735,196	10,134,489	0	0	11,238,678,480,914,200,000	779,944,531,786,442,000	11/12/2017 08:19:02	0	0	0	0	0	2	3
6zg6y5r6vbvw3	84,611	31,512	29,944	1	1	0	5	2017-11-08/04:45:31	4	1	300	300	13,858,748	208	176,598	1,946,340	16,424	6	ALL_ROWS	50,387,511	JDBC Thin Client	1,060,134,794	3,617,840,506	230,869	698	15,888,157,573,517,600,000	8,265,604,637,537,560,000	11/12/2017 09:35:35	9,830,400	68	4,915,200	68	4,915,200	8	13

This post has been answered by AndrewSayer on Nov 14 2017

Jump to Answer

Locked Post

New comments cannot be posted to this locked post.

Locked on Dec 12 2017

Added on Nov 12 2017

#sql-pl-sql

29 comments

1,524 views