Re: Weird Oracle 12.2 issue ..

Hi Upendra,

First of all, that is not real execution plan, that's just the plan produced by "autotrace". It uses explain plan really. You may noticed that statistics showed 1168 SQL*Net roundtrips, though you got just 583 rows. I suspect that your query contains LOB columns in select list - it could cause extra roundtrips. 11 seconds/1168 roundtrips = 8.5ms per roundtrip - it doesn't seem like something strange.
I think the easiest way to check it is to enable sql trace and give us raw trace.
Also I'd set the following parameters:
set termout off arraysize 1000
and execute your query as script.

On Fri, Sep 15, 2017 at 2:17 AM, 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 *
>
>
> 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
>

-- 
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate
http://orasql.org

--
http://www.freelists.org/webpage/oracle-l