| Performance Issue after upgrade to 11.2.0.4 [message #650120] |
Fri, 15 April 2016 21:30  |
sadiq106
Messages: 22
Registered: February 2009
|
Junior Member |
|
|
Hi,
I have recently upgraded my database from10.2.0.1 32 bit to 11.2.0.4 x64 with imp/exp method.
OS RHEL 5.9 32 bit to RHEL 6.7 x64
I'm facing some issues.
1. Some Queries/View are very slow .
2. Some Queries giving very good speed in first run and after that very slow.
3. Some time CPU usage goes to 90%
Need Suggestion to resolve these issues.
|
|
|
|
|
|
| Re: Performance Issue after upgrade to 11.2.0.4 [message #650125 is a reply to message #650121] |
Sat, 16 April 2016 02:24  |
sadiq106
Messages: 22
Registered: February 2009
|
Junior Member |
|
|
>1
DBMS_METADATA
SQL> EXPLAIN PLAN FOR select * from material_onhand_v;
Plan FOR succeeded.
SQL> SELECT DBMS_METADATA.GET_DDL (object_type, object_name, object_owner)
2 FROM plan_table
3 WHERE object_type IN ('TABLE','VIEW');
DBMS_METADATA.GET_DDL(OBJECT_TYPE,OBJECT_NAME,OBJECT_OWNER)
---------------------------------------------------------------------------
CREATE TABLE "IMAGE"."RCV_ROLL_TRANSACTIONS"
( "SHIPMENT_ID" NUMBER,
"I
CREATE OR REPLACE FORCE VIEW "IMAGE"."MATERIAL_SUBINVENTORY_V" ("SUBINVEN
CREATE TABLE "IMAGE"."FND_FLEX_VALUES"
( "FLEX_VALUE_SET_ID" NUMBER(10,0)
CREATE TABLE "IMAGE"."PACKING_CARTONS"
DBMS_METADATA.GET_DDL(OBJECT_TYPE,OBJECT_NAME,OBJECT_OWNER)
---------------------------------------------------------------------------
( "CARTON#" NUMBER,
"ORDER#" VAR
CREATE TABLE "IMAGE"."ORDER_PACKING_DEFINITION"
( "ORDER#" VARCHAR2(50),
CREATE TABLE "IMAGE"."ITEM#"
( "ORDER#" VARCHAR2(50),
"ITEM#" VARCHAR2(
CREATE TABLE "IMAGE"."ORDER_PACKING_DEFINITION"
( "ORDER#" VARCHAR2(50),
DBMS_METADATA.GET_DDL(OBJECT_TYPE,OBJECT_NAME,OBJECT_OWNER)
---------------------------------------------------------------------------
CREATE TABLE "IMAGE"."PACKING_COMBINATION_IDS_LOOSE"
( "ORDER#" VARCHAR2(
CREATE TABLE "IMAGE"."ITEMS"
( "ITEM_ID" NUMBER,
"ITEM_CODE" VARCHAR2(1
CREATE TABLE "IMAGE"."MOVE_ORDER_LINES"
( "LINE_ID" NUMBER,
"HEADER_ID"
DBMS_METADATA.GET_DDL(OBJECT_TYPE,OBJECT_NAME,OBJECT_OWNER)
---------------------------------------------------------------------------
CREATE TABLE "IMAGE"."ORDER_PEGGING"
( "PEGGING_NUM" NUMBER,
"ITEM_ID"
CREATE TABLE "IMAGE"."MATERIAL_ONHAND_QUANTITIES"
( "ITEM_ID" NUMBER NOT
CREATE TABLE "IMAGE"."DYED_INSPECTION_REPORT_D"
( "REPORT_NUMBER" NUMBER,
DBMS_METADATA.GET_DDL(OBJECT_TYPE,OBJECT_NAME,OBJECT_OWNER)
---------------------------------------------------------------------------
CREATE TABLE "IMAGE"."RCV_TRANSACTIONS"
( "RCV_TRANSACTION_ID" NUMBER,
CREATE TABLE "IMAGE"."ITEMS"
( "ITEM_ID" NUMBER,
"ITEM_CODE" VARCHAR2(1
CREATE TABLE "IMAGE"."RCV_TRANSACTIONS"
( "RCV_TRANSACTION_ID" NUMBER,
DBMS_METADATA.GET_DDL(OBJECT_TYPE,OBJECT_NAME,OBJECT_OWNER)
---------------------------------------------------------------------------
CREATE TABLE "IMAGE"."MOVE_ORDER_LINES"
( "LINE_ID" NUMBER,
"HEADER_ID"
17 rows selected
SQL>
> trace file is attached
>2. Yes, these queries utilize bind variables.
>3. vsstat
[oracle@wlserver trace]$ vmstat 6 10
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu-----
r b swpd free buff cache si so bi bo in cs us sy id wa st
6 0 1716 2330664 59380 23908836 0 0 341 488 132 152 64 3 32 1 0
7 0 1716 2332228 59420 23908988 0 0 4 151 6409 499 74 1 25 0 0
8 0 1716 2238780 59436 23909172 0 0 0 3517 9449 1481 95 5 0 0 0
6 0 1716 2354212 59444 23909328 0 0 0 2261 9608 3210 81 1 18 0 0
8 0 1716 2168480 59452 23909336 0 0 1 126 9459 2803 80 2 18 0 0
8 0 1716 2123220 59468 23909356 0 0 0 119 7347 355 87 1 11 0 0
7 0 1716 2157692 59484 23909360 0 0 0 141 7798 359 91 4 5 0 0
9 0 1716 2037760 59500 23910008 0 0 0 1546 7406 457 88 1 11 0 0
9 0 1716 2029592 59508 23910052 0 0 0 143 9329 1099 98 2 0 0 0
12 0 1716 2241916 59516 23910052 0 0 3 269 10208 2067 98 2 0 0 0
|
|
|
|
| Re: Performance Issue after upgrade to 11.2.0.4 [message #650126 is a reply to message #650125] |
Sat, 16 April 2016 04:31 |
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Come on, man! What you have posted is useless. It looks very like trolling: being deliberately stupid in order to make people angry.
However, I shall assume (this time) that it is not deliberate.
dbms_metadata.get_ddl returns a clob, so you need to SET LONG to something sensible to see anything.
EXPLAIN PLAN is no use by itself, you need to use dbms_xplan.display to show the result.
[Updated on: Sat, 16 April 2016 04:32] Report message to a moderator |
|
|
|
|
|
| Re: Performance Issue after upgrade to 11.2.0.4 [message #650135 is a reply to message #650127] |
Sun, 17 April 2016 02:38 |
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
It is beginning to look very much as though you are being deliberately stupid. No-one can tune invisible SQL. Do you not think that you should show the query behind the material_onhand_v view?
The plan has loads of what one might call "red flags". Non-mergeable sub-queries, aggregations that don't do anything, outer joins that may be unnecessary.
|
|
|
|
|
|
| Re: Performance Issue after upgrade to 11.2.0.4 [message #650138 is a reply to message #650136] |
Sun, 17 April 2016 04:13 |
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
I have already give you a few hints, but I'll try some more.
Look at operations 58 to 60 of the plan. The scan is costed at 10673, and the aggregation adds another 8014 cost units. Furthermore, the aggregation makes the subquery non-mergeable: Oracle has to stop whatever else it is doing to materialize it as an inline view. The same thing happens at operations 27 to 28, though the aggregation is cheaper because there are fewer rows. The end result is that you are hitting the same table twice, for a total cost of 29316. This is 45% of the total for the query.
You need to ask "are those aggregations really necessary?" Oracle does not expect them to remove any rows: the estimated cardinalities (619k and 958) are the same before and after. You need to check this, and if there are no duplicates remove the aggregations and you will find a substantial improvement in performance. If there are duplicates, and this actually matters, perhaps they could be handled better elsewhere. But no-one can tell you without seeing the query and the DDLs.
Something similar is happening at operations 16 - 17. My guess is a DISTINCT that isn't removing any rows, but does prevent view merging.
You need to understand your data, and understand your queries. No-one can help you do that if you do not show the data structures or the SQL.
[Updated on: Sun, 17 April 2016 04:15] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
