RE: Weird Oracle 12.2 issue ..

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Thu, 14 Sep 2017 22:58:02 -0400
Message-ID: <0d4a01d32dce$73dc59d0$5b950d70$_at_rsiz.com>



Tracing should tell you exactly the answer.  

As a blind shot in the dark I would suspect you are inadvertently running a tiny arraysize on the remote clients and you have proper glogin.sql or login.sql on the server.  

And it really doesn't matter if a very big file with OS optimization for transmission size is fast in aggregate if there is a moderately bad network latency and you are sending many, many tiny chunks of data.  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Upendra nerilla
Sent: Thursday, September 14, 2017 7:17 PM To: Oracle-L
Subject: Weird Oracle 12.2 issue ..  

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-l
Received on Fri Sep 15 2017 - 04:58:02 CEST

Original text of this message