CODEWITHSUNDEEP
sqloracle-databaseperformancesql-execution-plan
Lara
Lara

Reputation: 31

Oracle: Do queries starting with comments have impact on performance?

I am adding comments to the beginning of my queries (in Oracle) to provide metadata information about the query itself.

Every information that I found out regarding this topic is only about hints besides this one: https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:93449400346099694.

Will this query be interpreted the same way by the execution plan with and without the comments? Will my comment have any impact regarding performance or caching?

Upvotes: 3

Views: 1055

Answers (2)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59652

When you run a query the first time then Oracle creates the execution plan and caches this plan for further use.

When you add an arbitrary comment then in fact you run a different query and thus Oracle evaluates a new execution plan. In general this execution plan could be different to the first one - although rather unlikely.

I remember in a training the trainer showed us the "secret" Oracle hint /*+ RUN_FASTER */ - in deed the query was executing much faster! The trick was, for the original query he stored a prepared execution plan (see Using Plan Stability) forcing a Full Table Scan. With the "hint" /*+ RUN_FASTER */ you have a new query and the optimizer evaluated a new better plan.

In fact hints like /*+ RUN_SLOWER */ or /*+ drink a cup of tea */ do the same but without astonishment from the students. :-)

Upvotes: 3

Martin Preiss
Martin Preiss

Reputation: 396

adding comments to queries is a sensible strategy in my opinion - in Oracle as in other RDBMS. But in Oracle there are indeed some situations in which a comment may have an impact on the execution plan: for example there are some plan management strategies (sql baselines for example) that match a query to a plan (or rather a set of hints), and the matching is based on the sql_id - and this sql_id changes, if a different comment is added.

Another problem is, that mixing optimizer hints (/*+ ... */) with leading comments may lead to an invalidation of the hints - as shown in https://hoopercharles.wordpress.com/2011/01/15/adding-comments-to-sql-statements-improves-performance/.

So I would always encourage the use of comments, but separate them from hints and try to find out, if a given query is subject of plan management (taking a look at v$sql and its columns SQL_PLAN_BASELINE - and maybe also SQL_PATCH, which is another option to combine queries with additional execution information).

Upvotes: 1

Related Questions