| Improve query execution times [message #655547] |
Thu, 01 September 2016 16:35  |
 |
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 #655551 is a reply to message #655548] |
Thu, 01 September 2016 20:39  |
|
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 #655577 is a reply to message #655551] |
Sat, 03 September 2016 02:13 |
John Watson
Messages: 8964
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
