Toad World® Forums

SQL Rewrite Messages


#1

Hello Everyone,

I just started working with SQL Optimizer and I’m starting to have questions.

When looking at the alternative sql I see things like…

/*+ FIRST_ROWS(30) / or /+ PARALLEL(CI, 2) PARALLEL(MSI, 2) PARALLEL(MP1, 2) PARALLEL(P, 2) PARALLEL(CA, 2) PARALLEL(CIX, 2) */

What do these mean and where can I look to see a full list and descriptions?

Thanks!,

Wayne


#2

Hi Wayne,

Those are Oracle optimizer hints that are being applied to the SQL statement. They influence how the optimizer chooses the physical path for gathering the data. You can think of them as an “override” for the optimizer’s default behavior. In certain cases, this can result in significant performance improvements.

If you want to learn more about SQL Optimizer’s use of hints (and the other methods it uses for optimization) - this Tech Brief is a good place to start.

quest.com/…/secrets-of-the-quest-sql-optimizer820017

Oracle also has documentation of the different hints that are available. Here is a section on the hints for 11.2.

docs.oracle.com/…/hintsref.htm

Please let us know if you have any other questions!

Thanks!

Kevin


#3

Hey Kevin!,

Thank you very much for the info! =)

Have a Great Weekend,

Wayne

From: Kevin Stern [mailto:bounce-KevinStern@toadworld.com]

Sent: Thursday, January 29, 2015 12:49 PM

To: sqloptoracle@toadworld.com

Subject: RE: [SQL Optimizer for Oracle - Discussion Forum] SQL Rewrite Messages

RE: SQL Rewrite Messages

Reply by Kevin Stern

Hi Wayne,

Those are Oracle optimizer hints that are being applied to the SQL statement. They influence how the optimizer chooses the physical path for gathering the data. You can think of them as an “override”
for the optimizer’s default behavior. In certain cases, this can result in significant performance improvements.

If you want to learn more about SQL Optimizer’s use of hints (and the other methods it uses for optimization) - this Tech Brief is a good place to start.

quest.com/…/secrets-of-the-quest-sql-optimizer820017

Oracle also has documentation of the different hints that are available. Here is a section on the hints for 11.2.

docs.oracle.com/…/hintsref.htm

Please let us know if you have any other questions!

Thanks!

Kevin

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from SQL Optimizer for Oracle - General
notifications altogether.

SQL Optimizer for Oracle - Discussion Forum

Flag
this post as spam/abuse.