Oracle Tuning Question - Using SQL Developer to benchmark performance - fair or foul?

[KenMac]

Hi,

I have an issue with an Oracle query that seems to run slowly.  By slowly, I mean that if I take the exact same query that takes 30-50 seconds to run in EG and run it in Oracle SQL Developer, it consistently completes in less than one second. Notes and code are below.  Also ran the query using SQL*PLUS on the same compute server that EG is running on and it also ran with sub-second response.

Question: Should I expect the SQL to show similar performance on EG vs SQL Developer/SQL*PLUS?  Is it fair to compare using those two tools?  If not, what is he right tool?  All other suggestions are welcome and appreciated!

Thanks!

Ken

Notes: SAS server and the database sever reside in the same data center and connect via a 10GB fiber backbone.

           The EG query is using SQL Pass-through

           The final query result is one row

           I have "options sastrace = ',,,d' sastraceloc = saslog" enabled

           The query used in SQL Developer is taken directly from the log

           SQL Developer is running locally on my desktop in a remote location connect via WAN 

           The outline of the query is below - I cannot post the full original query due to confidentially reasons

           Other queries from EG to the same database perform well returning 1000's of rows in far less time

Code:

/*  Pseudo query masked for confidential content  */

proc sql threads noprint;
  connect to oracle as alias1 (path="xxxxxxx" AuthDomain="xxxxxxxx" buffsize=10000);

  create table final_result as 

  select strip(put( fld1,30.)) as uid, 
         strip(fld2)           as node_type 

    from connection to alias1 ( select distinct fld1, 
                                              fld2 
                                         from table1 
                                        where to_node_uid in 
                               ( select fld3
                                   from table1 
                                  where group=123456789 and fld3_type in 
                                  ( select distinct fld_type 
                                           from table2 
                                          where class in ('A1','B2','C3','D4') ) ) );
  quit;
run;

[TomKari]

You have put your question very well, and you have a good understanding of the underlying technologies.

If your select statement that begins with "select distinct fld1," only returns one row, then I can't see any reason why it should run slower in your SAS environment than in SQL Developer.

Note that you say "on the same compute server that EG is running on", which is a slight misinterpretation. EG is purely a client tool that runs on your PC, it uses a SAS server to execute the SAS code. I'm assuming you mean "on the same compute server that EG is submitting the SAS code to run on".

Frequently the reason for this kind of timing anomaly is that the SAS environment is pulling a bunch of records out of the Oracle server, over the network to the SAS server, and then the SAS server summarizes them, as opposed to them being summarized in the Oracle server. But that doesn't sound like your situation.

All that I can speculate on are things like differences in priority and resources provided when you submit via SAS versus SQL Developer, but that seems unlikely to me.

Hopefully someone else will have some good ideas!

Tom

[KenMac]

Tom,

Your assumption is correct - the code submitted via EG is executing on the same server as SQL*PLUS.

With the trace options on, I can see the exact SQL that SAS is passing to Oracle which is the query I posted.  Since it is using pass-through, SAS does not modify the query in any way.

Thanks for responding.

Ken

[LinusH]

options sastrace = ',,,d' sastraceloc = saslog" shouldn't give you any information when using explicit pass through.
Is the result set "large"?
To rule out any differences in data transport change the query to create a temporary table in the Oracle instance.
You can fiddle with the READBUFF option to get better query performance.

[KenMac]

Hi LinusH,

Agree about 'sastrace'.  It just allows me to confirm what was sent to Oracle.

The result set is a single row and so network/transport latency should not be an issue.  Given that, is there any reason that READBUFF would have any effect?

Per your suggestion, I ran a modified version of the query that created a temp table in Oracle for the result followed by a second query to copy the result table to saswork.  The combined execution time for the two queries is 0.33 seconds.  This is a potential workaround although our DBA's would not likely approve something like this for our production environment.

Any other suggestions?

Thanks again for the prompt response! 

[LinusH]

READBUFF=1 sounds like a performance killer. You want to fill the buffer on bulks, not row by row. Try setting to at least 1000.

[KenMac]

Updated connect to and still 30 seconds:

connect to oracle as alias1 (path="xxxxxxx" AuthDomain="xxxxxxxx" readbuff=1000);

Log  

[SASKiwi · Accepted solution]

It might be worth changing your query to select count(*) so see if that has any impact. Also could SQL*Plus be parallelising your query by default? SAS won't, you have to add an Oracle hint to do that.

[KenMac]

SASKiwi,

Adding the +PARALLEL hint to the query made a big improvement.  Query response is consistently under 5 seconds now.  Still much slower than SQL*PLUS/Developer but at least in the ballgame now.  If possible, I'll get my DBAs to take a look at the query to see what else can be done.

Lastly, how did you know that SAS would not parallelise the query?   If I am using pas-thru, why didn't Oracle do it?

Many thanks again for the super tip!

Ken

[SASKiwi]

@KenMac - your experience with parallelising mirrors my own. SAS SQL passthru merely passes the query through to Oracle without analysing it or doing any optimisation. Hence you need to add your own tuning options via hints. Also I suspect vendor-supplied tools do fancy caching of data while queries continue to run, something that would be hard for SAS to do because they don't have access to the database internals.

Glad my tip was helpful!

[KenMac]

Still a bit puzzling.  Every DBMS has an optimizer that analyzes the query to find the most efficient execution plan.  Given that, why does the same query, submitted by different tools, have such large differences in performance?  Could it be the vendr is playing favorites with its own tools?

Inconceivable!   😄 

[LinusH]

Oh sorry now I saw that your result set is one row. Then increasing READBUFF of course have no effect as you stated....
So I don't really have any more to offer, open a track to SAS Tech support might be a way forward.