Home » RDBMS Server » Performance Tuning » Improve query execution times (oracle 12c)
Improve query execution times [message #655547] Thu, 01 September 2016 16:35 Go to next message
gorants
Messages: 85
Registered: May 2014
Location: ATL
Member
The execution of sql query time is varying based on the fetches , I need couple of clarifications and understanding to analyze the issue further


1. Explain plan will not depend on data passed in sql query -- is my understanding is correct ?
2. When I run same query from oracle client (Sql developer) it showing execution took 0.02 sec , but from application logs showing ( when multiple fetches occurring) 0.4 sec execution time.
4. Do you think multiple fetches on same table causing slowness ? if so why? How can I improve the performance , in my case I would except 1000 fetches per minute using same query. Please note that each fetch has different data passed in sql.

Appreciate your inputs
Re: Improve query execution times [message #655548 is a reply to message #655547] Thu, 01 September 2016 17:26 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
ORAFAQ tuning below -
#1 no
#2 unsubstantiated observation. Why do you care about sub-second SQL? Do you suffer from Compulsive Tuning Disorder?
#3 you count funny

#4 no

Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/mv/msg/84315/433888/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) ddl for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Re: Improve query execution times [message #655551 is a reply to message #655548] Thu, 01 September 2016 20:39 Go to previous messageGo to next message
gorants
Messages: 85
Registered: May 2014
Location: ATL
Member
Thank you for the response. Sorry i completely understand that with out query and ddl it is difficult to tell what the problem is. The query what i was saying has multiple table joins and posting all ddl here is difficult. So based on problem symptoms i explained, i am looking for some direction so i can analyze the issue further.


I have to look/correct performance issue from application code , DB side. So wondering what is causing the query execution to increase how to correct. I am sure there should be some reason for increasing time of query execution like during multiple records fetch any other application thread doing updates which causing any lock at row or table level so fetch is taking some time.

Need some tips to narrow down and understand issue.


Just to be clear on #1

#1. Explain plan will vary based on data passed in sql query ? in Below query i just changed NBR 100 to 200. So this change has any impact in explain. The reason i am asking as said in earlier post same query with just data change from application logs i see it is taking 0.02 sec, 0.4 sec and 0.5 sec ..so not sure what causing performance degrade.



Example:

select MSG_ID  from MSG
where ACTUAL_END_DATE>=to_timestamp('08/29/2016 13:42:34.000', 'mm/dd/yyyy hh24:mi:ss.ff3')
and ACTUAL_END_DATE<=to_timestamp('08/30/2016 13:42:34.000', 'mm/dd/yyyy hh24:mi:ss.ff3')
and (STATUS in (10)) and (CODE in (40 , 50)) and
and (NBR not in (100)) order by ACTUAL_END_DATE desc, MSG_ID desc 


select MSG_ID  from MSG
where ACTUAL_END_DATE>=to_timestamp('08/29/2016 13:42:34.000', 'mm/dd/yyyy hh24:mi:ss.ff3')
and ACTUAL_END_DATE<=to_timestamp('08/30/2016 13:42:34.000', 'mm/dd/yyyy hh24:mi:ss.ff3')
and (STATUS in (10)) and (CODE in (40 , 50)) and
and (NBR not in (200)) order by ACTUAL_END_DATE desc, MSG_ID desc 


#2.

[Updated on: Thu, 01 September 2016 20:42]

Report message to a moderator

Re: Improve query execution times [message #655552 is a reply to message #655551] Thu, 01 September 2016 21:02 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
https://www.google.com/webhp?hl=en&tab=ww#hl=en&q=bind+variable+peeking+oracle

skewed data without histograms vs. normal data distribution

either above can result in measurably different elapsed run time.

While the SQL remains the same the underlying data may produce majorly different results
Re: Improve query execution times [message #655577 is a reply to message #655551] Sat, 03 September 2016 02:13 Go to previous message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
You may well have different execution plans (which may not be the same as the EXPLAIN PLAN) for those two queries, and for different executions of the same query. Is it a problem?
Some possible reasons for plan instability:
The cardinality estimate may be different for the different predicate.
Statistics feedback.
SQL directives and dynamic sampling.
Adaptive execution plans.

If you are executing those statements zillions of times with minor changes in the predicate, you might consider using bind variables rather than literals.
Previous Topic: Commit interval
Next Topic: MR (Metadata Repository) SQL caused poor performance
Goto Forum:
  


Current Time: Thu Mar 28 07:39:35 CDT 2024