Home » RDBMS Server » Performance Tuning » Performance Issue after upgrade to 11.2.0.4 (Oracle Database, 11.2.0.4, RHEL 6.7 x64)
Performance Issue after upgrade to 11.2.0.4 [message #650120] Fri, 15 April 2016 21:30 Go to next message
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 #650121 is a reply to message #650120] Fri, 15 April 2016 22:19 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>1. Some Queries/View are very slow .

http://www.orafaq.com/forum/mv/msg/84315/433888/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof

>2. Some Queries giving very good speed in first run and after that very slow.
Do these queries utilize bind variables?

>3. Some time CPU usage goes to 90%
when above occurs issue OS command below

vmstat 6 10

COPY the results from above then PASTE all back here
Re: Performance Issue after upgrade to 11.2.0.4 [message #650125 is a reply to message #650121] Sat, 16 April 2016 02:24 Go to previous messageGo to next message
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 Go to previous messageGo to next message
John Watson
Messages: 8922
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 #650127 is a reply to message #650126] Sat, 16 April 2016 05:39 Go to previous messageGo to next message
sadiq106
Messages: 22
Registered: February 2009
Junior Member
EXPLAIN PLAN
Please see attached file.

Re: Performance Issue after upgrade to 11.2.0.4 [message #650135 is a reply to message #650127] Sun, 17 April 2016 02:38 Go to previous messageGo to next message
John Watson
Messages: 8922
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 #650136 is a reply to message #650135] Sun, 17 April 2016 02:46 Go to previous messageGo to next message
sadiq106
Messages: 22
Registered: February 2009
Junior Member
Thanks for making me stupid.

Im trying to following your instructions.

You should observe your language.


Give me a favor to remove this discussion
Re: Performance Issue after upgrade to 11.2.0.4 [message #650138 is a reply to message #650136] Sun, 17 April 2016 04:13 Go to previous message
John Watson
Messages: 8922
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

Previous Topic: What Is The Correct Formula For Buffer Cache Hit Ratio
Next Topic: Cluster table access IO is unexpected
Goto Forum:
  


Current Time: Thu Mar 28 08:06:08 CDT 2024