Home » RDBMS Server » Performance Tuning » Performance degrade after migration from 11g exadata to 12c (12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production)
Performance degrade after migration from 11g exadata to 12c [message #652362] Tue, 07 June 2016 14:25 Go to next message
knw15pwr
Messages: 134
Registered: March 2010
Senior Member
We recently migrated our database from 11g Exadata platform to a new 12c data (non-exadata)
We have noticed a considerable degradation in performance for query retrieves. A sample query with the explain plan is given below.
We have more than 50 such queries running on different tables and filter combinations.

(Stats have been gathered before running queries)

SELECT count(*) as cnt FROM schema_h.table_H_con 
where REP_FR = 0 and ADJ_TP_CD <> 0 and CR_BK_BL <> 0;

Total row count in the table = approx. 6.6 million

Plan hash value: 2084274238
 
----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |     1 |    10 |   184K  (1)| 00:00:08 |
|   1 |  SORT AGGREGATE    |             |     1 |    10 |            |          |
|*  2 |   TABLE ACCESS FULL| table_H_con |   891K|  8705K|   184K  (1)| 00:00:08 |
----------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("REP_FR"=0 AND "CR_BK_BL"<>0 AND "ADJ_TP_CD"<>0)


Although the plan says 8 secs - the query runs for more than 3 minutes. DBA suggested to add indexes, it did perform better with an index.
But then we have more than 300 columns in the table and about 50 columns are being used as filter conditions in our queries.
So it does seem un-reasonable to be creating that many indexes.

----Plan with indexes

Plan hash value: 1920632177
 
-----------------------------------------------------------------------------------------------
| Id  | Operation                | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                    |     1 |    10 | 59646   (1)| 00:00:03 |
|   1 |  SORT AGGREGATE          |                    |     1 |    10 |            |          |
|*  2 |   VIEW                   | index$_join$_001   |   903K|  8826K| 59646   (1)| 00:00:03 |
|*  3 |    HASH JOIN             |                    |       |       |            |          |
|*  4 |     HASH JOIN            |                    |       |       |            |          |
|*  5 |      INDEX RANGE SCAN    | IDX_table_H_con_10 |   903K|  8826K|  4653   (1)| 00:00:01 |
|*  6 |      INDEX FAST FULL SCAN| IDX_table_H_con_11 |   903K|  8826K| 16073   (1)| 00:00:01 |
|*  7 |     INDEX FAST FULL SCAN | IDX_table_H_con_12 |   903K|  8826K| 17711   (1)| 00:00:01 |
-----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("REP_FR"=0)
   3 - access(ROWID=ROWID)
   4 - access(ROWID=ROWID)
   5 - access("REP_FR"=0)
   6 - filter("ADJ_TP_CD"<>0)
   7 - filter("CR_BK_BL"<>0)

With indexes it completes in about 1 sec.

In the previous environment, the same query completes in a few milliseconds with the below plan.

Plan hash value: 2084274238
 
------------------------------------------------------------------------------------------
| Id  | Operation                  | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |             |     1 |    10 |   672K  (1)| 02:14:36 |
|   1 |  SORT AGGREGATE            |             |     1 |    10 |            |          |
|*  2 |   TABLE ACCESS STORAGE FULL| table_H_con |   930K|  9091K|   672K  (1)| 02:14:36 |
------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - storage("REP_FR"=0 AND "CR_BK_BL"<>0 AND "ADJ_TP_CD"<>0)
       filter("REP_FR"=0 AND "CR_BK_BL"<>0 AND "ADJ_TP_CD"<>0)

I am trying to get the table creation scripts and some sample data for your reference, but in the meanwhile could you please suggest what i need to look at or any suggestions that i can start off with ? Any DB parameters that i can look into or anything else that could help us analyse this better?

Thanks !
Re: Performance degrade after migration from 11g exadata to 12c [message #652364 is a reply to message #652362] Tue, 07 June 2016 14:31 Go to previous messageGo to next message
John Watson
Messages: 7618
Registered: January 2010
Location: Global Village
Senior Member
Well, you have probably downgraded your hardware hugely, and added to that you no longer have Smart Scan processing. So it is perfectly normal that queries like that will run more slowly. If you want Exadata-like performance, your best option is probably to licence the In-Memory option. It requires a lot of tuning, though.
Re: Performance degrade after migration from 11g exadata to 12c [message #652429 is a reply to message #652364] Wed, 08 June 2016 14:42 Go to previous message
knw15pwr
Messages: 134
Registered: March 2010
Senior Member
Thanks John. I figured the hardware was going to be a major issue. Will have to find some other way to deal with it.
Previous Topic: LEFT OUTER JOIN of Large Tables - Performance issue
Next Topic: Is there a way to find out a killed session associated sql
Goto Forum:
  


Current Time: Wed Oct 17 11:46:15 CDT 2018