Re: Weird Oracle 12.2 issue ..

Hi

Try setting tcp.nodelay=yes in the sqlnet.ora on the client just to make surer you are not bite by that.

Gram/

Med Venlig Hilsen

Peter Gram

Kultur-kustode for Miracle

*Miracle Expert Services*
Borupvang 2c, 2750 Ballerup, Denmark
Cell: (+45) 53747107
Office Phone: (+45) 44668855
Office Fax: (+45) 44668856
Home : (+45) 38745696
Home : Sæbyholmsvej 18 2500 Valby
Mail: peter.gram_at_miracle.dk
linkedin: dk.linkedin.com/in/petergram/
OakTable : oaktable.net/members <http://oaktable.net/members>

On 15 September 2017 at 01:17, Upendra nerilla <nupendra_at_hotmail.com> wrote:

> 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:
>
> - A query which runs in 3 seconds at the database directly (in 12.2)
> takes between 11-14 seconds when it is run through a client like pl/sql
> developer.
> - To eliminate the variables, we ran sqlplus from another host (which
> is in same network), and the execution time was still over 13 seconds.
> - sqlplus on the DB server (using tns/sqlnet) produces the result in
> about 3 seconds as well
> - Sysadmin says, Network throughput between the servers have no issue,
> he was able to scp a 500MB file in less than 2 seconds.
>
>
> 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