RE: Weird Oracle 12.2 issue ..

I entirely agree with Mark and Sayan. You will also need a trace from the 11g system. If you run them both through a profiler, tkprof will do to start* then you'll be able to see where the extra 10s is.

I'd also take a second look at the plans in your output before saying the output is the same, since the BY INDEX ROWID BATCHED operation is a 12.1+ feature.

*given the issue is likely to do with network traffic you *may *find tkprof omitting some relevant waits and will need a full featured profiler.

On 15 Sep 2017 04:01, "Mark W. Farnham" <mwf_at_rsiz.com> wrote:

> 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_freeli
> sts.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