Thanks for your response Connor. I've given the profiler a try and compared results on 12c with various other 11g versions all of which perform better. I've also resorted to trying it on the default database created using DBCA to eliminate anything I did and the results are the same. It seems switching between plsql and sql (and back again) is where the delay is. Upto 4 times longer on 12c. Here are snippets from the output
12c
P#V PLSHPROF Internal Version 1.0
P#! PL/SQL Timer Started
P#C PLSQL."".""."__plsql_vm"
P#X 8
P#C PLSQL."".""."__anonymous_block"
P#X 63
P#C SQL."".""."__static_sql_exec_line5" #5."d8jhv8fcm27kd"
P#! SELECT :B1 FROM DUAL
P#X 192
P#R
P#X 29
P#C SQL."".""."__static_sql_exec_line5" #5."d8jhv8fcm27kd"
P#! SELECT :B1 FROM DUAL
P#X 98
P#R
P#X 15
P#C SQL."".""."__static_sql_exec_line5" #5."d8jhv8fcm27kd"
P#! SELECT :B1 FROM DUAL
P#X 90
P#R
P#X 14
...
and 11g
P#V PLSHPROF Internal Version 1.0
P#! PL/SQL Timer Started
P#C PLSQL."".""."__plsql_vm"
P#X 2
P#C PLSQL."".""."__anonymous_block"
P#X 32
P#C PLSQL."SYS"."DBMS_OUTPUT"::11."GET_LINES"#660bd56a1b1640db #180
P#X 11
P#R
P#X 28
P#R
P#X 1
P#R
P#C PLSQL."".""."__plsql_vm"
P#X 2
P#C PLSQL."".""."__anonymous_block"
P#X 29
P#C SQL."".""."__static_sql_exec_line5" #5
P#X 74
P#R
P#X 16
P#C SQL."".""."__static_sql_exec_line5" #5
P#X 28
P#R
P#X 4
P#C SQL."".""."__static_sql_exec_line5" #5
P#X 23
P#R
...
and in some of the 12c output there were spikes where values for P#X > 300!
Any suggestions for DB or OS parameters that may be behind this would be much appreciated.
Thanks,
John
April 13, 2017 - 1:24 am UTC
Hi John,
I've asked around some of the PM's, and we cant see any evidence of this anywhere.
All I can suggest is logging an SR - they might be able to do some detailed tracing for you.