Home » RDBMS Server » Performance Tuning » query optimization phase (Oracle DB 11.2.0)
query optimization phase [message #612295] Tue, 15 April 2014 13:18 Go to next message
Johnny_aig
Messages: 4
Registered: April 2014
Junior Member
I faced with a question these days and I was in doubt about the correct answers. In my opinion, not just two answers are possible, even if the question was like "which two tasks are performed during the optimization stage of a sql statement"
And, as I remember, this was the list of possible answers:

a - evaluating the expressions and conditions in the query (in my opinion this is correct, as I can see here http://docs.oracle.com/cd/B10500_01/server.920/a96533/optimops.htm#40574 )
b - checking the syntax and analyzing the semantics of the statement (this is false, as this is done in the parse stage, and the result of the parse stage which consists of a set of query blocks is then sent to the optimizer)
c - separating the clauses of the sql statement into structures that can be processed (not sure about what "structures that can be processed" really are)
d - inspecting integrity constraints and optimizing the query based on this metadata (correct? as per http://docs.oracle.com/cd/E16655_01/server.121/e17749/schemas.htm#DWHSG8151 , this option should be correct)
e - gathering the statistics before creating the execution plan for the statement (not really clear...they can be gathered automatically or not, but not implicitly gathered automatically).

I was in doubt here. However, B is definitely false.
Any suggestions?
Thank you!

[Updated on: Tue, 15 April 2014 13:37]

Report message to a moderator

Re: query optimization phase [message #612320 is a reply to message #612295] Wed, 16 April 2014 01:06 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Johnny_aig wrote on Tue, 15 April 2014 23:48
c - separating the clauses of the sql statement into structures that can be processed (not sure about what "structures that can be processed" really are)


It means the sql statement transformation stage where a complex query with lots of clauses is resolved into a compound query to improve the efficiency of the query execution, such that the goal remains the same.
Re: query optimization phase [message #612337 is a reply to message #612320] Wed, 16 April 2014 03:35 Go to previous messageGo to next message
Johnny_aig
Messages: 4
Registered: April 2014
Junior Member
Ok, I understand. So C should be correct in this case.
What about the others?
Re: query optimization phase [message #612338 is a reply to message #612337] Wed, 16 April 2014 03:58 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Johnny_aig wrote on Wed, 16 April 2014 14:05
What about the others?


What about them? Be specific. Where do you find issue in understanding the explanation in the documentation.

[Updated on: Wed, 16 April 2014 03:59]

Report message to a moderator

Re: query optimization phase [message #612350 is a reply to message #612338] Wed, 16 April 2014 07:54 Go to previous messageGo to next message
Johnny_aig
Messages: 4
Registered: April 2014
Junior Member
d choice is unclear for me. as per the doc:

Query optimization

The Oracle Database utilizes constraints when optimizing SQL queries. Although constraints can be useful in many aspects of query optimization, constraints are particularly important for query rewrite of materialized views.


constraints.. but which constraints? d choice is about "integrity constraints", and "optimizing the query based on this metadata" (does constraints have metadata?)
Re: query optimization phase [message #612351 is a reply to message #612350] Wed, 16 April 2014 08:08 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
constraints are metadata
Re: query optimization phase [message #612352 is a reply to message #612350] Wed, 16 April 2014 08:21 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
In your original post you stated that option d is correct per the docs. So why are you unclear now?

Anyway, constraints have an important role in optimizing the execution plan. Yes, it is METADATA. The optimizer needs this metadata to make it's choice for an optimized execution plan. It all depends on as much information you provide to the optimizer. Read the documentation to understand the use of constraints, they take care of the integrity of the data in your DB. There are numerous explanations about the importance of integrity constraints. Good luck!
Re: query optimization phase [message #612353 is a reply to message #612352] Wed, 16 April 2014 08:39 Go to previous messageGo to next message
Johnny_aig
Messages: 4
Registered: April 2014
Junior Member
Thank you!
What about the last one, e? They are not implicitly gathered automatically and, moreover, not "before creating the execution plan for the statement". In my opinion this is false.
Re: query optimization phase [message #612354 is a reply to message #612353] Wed, 16 April 2014 08:49 Go to previous message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Then you did not read the documentation properly. By default Oracle enables automatic statistics gathering using a scheduled job GATHER_STATS_JOB.
Previous Topic: Can I instruct Oracle 11g to deliberately delay query completion?
Next Topic: Would making indexes "invisible" be the right approach for this?
Goto Forum:
  


Current Time: Thu Mar 28 15:13:47 CDT 2024