Weird Oracle 12.2 issue ..
From: Upendra nerilla <nupendra_at_hotmail.com>
Date: Thu, 14 Sep 2017 23:17:28 +0000
Message-ID: <BN6PR1001MB216256F40F878704813F7097D86F0_at_BN6PR1001MB2162.namprd10.prod.outlook.com>
All,
Plan hash value: 2598178018
<snip>
Date: Thu, 14 Sep 2017 23:17:28 +0000
Message-ID: <BN6PR1001MB216256F40F878704813F7097D86F0_at_BN6PR1001MB2162.namprd10.prod.outlook.com>
All,
We are running into a unique issue.. We recently migrated a database from 11.2 to 12.2 for testing.
When we compared the performance, 12.2 performance was 3x-4x slow compared to 11g.
When we were peeling the layers to troubleshoot, we discovered:
Here is the output from SQL trace.. the output from running the query from the DB server and through the client produce identical output, except the elapsed time..
<snip>
583 rows selected.
Elapsed: 00:00:02.51 vs Elapsed: 00:00:11.44
Execution Plan
Plan hash value: 2598178018
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1220 | 56120 | 1244 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 47 | | | |* 2 | TABLE ACCESS BY INDEX ROWID BATCHED| AES_FLD_TYPE | 1 | 47 | 2 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | AES_IDX_FTYPE_RTYPE | 1 | | 1 (0)| 00:00:01 | |* 4 | TABLE ACCESS FULL | AES_REC_TYPE | 1220 | 56120 | 24 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
2 - filter("AES_FLD_TYPE"."DELETED_DATE" IS NULL)
3 - access("AES_FLD_TYPE"."REC_TYPE_ID"=:B1 AND "AES_FLD_TYPE"."CLIENT_ID"=:B2)
4 - filter("AES_REC_TYPE"."DELETED_DATE" IS NULL AND "AES_REC_TYPE"."CLIENT_ID"=12313400144)
Statistics
66503 recursive calls 0 db block gets 71151 consistent gets 0 physical reads 0 redo size 382206 bytes sent via SQL*Net to client 248383 bytes received via SQL*Net from client 1168 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 583 rows processed
<snip>
Any suggestions on what I could check to pin-point the issue might be helpful..
Thanks in advance
-Upendra
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Sep 15 2017 - 01:17:28 CEST