RE: Weird Oracle 12.2 issue ..

hehe after I sent my response I see that Sayan has sent a more complete version of what I was talking about. (Plus the lob bit).  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Sayan Malakshinov Sent: Thursday, September 14, 2017 7:40 PM To: nupendra_at_hotmail.com
Cc: Oracle-L
Subject: 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