Category Archives: Hints

Ignoring hints

A hint is an instruction to the optimizer

This is what’s written in Oracle documentation. Instruction is defined as

a code that tells a computer to perform a particular operation

Which means Oracle CBO must obey the hints and must perform particular operation. The latter is hard to define correctly and explain precisely because it involves the logic of the block-box (what Cost Based Optimizer is). Some of the operations are mentioned in the standard Oracle documentation, some of them scattered across different places, and there are exceptions as usual. I think I’ll list here these cases which could lead to “ignoring hints” with the links to documentation/blogs.

Continue reading

CBO isn’t perfect

And you should remember that. Here is a nice example how Cost Based Optimizer can miss an obvious option (which is available to human eye and Oracle run-time with a hint) while searching for the best plan. CBO simply doesn’t consider Index Skip Scan with constant ‘in list’ predicates in the query, although it costs skip scan for a join. Such bits are always popping up here and there, so you just can’t say “The Cost Based Optimizer examines all of the possible plans for a SQL statement …”, even if Optimizer Team tells you CBO should do so. There will always be places where CBO will do less than possible to come to the best plan and will need a help from your side, such as re-written SQL or a hint.

Distinct placement

As a follow-up to a recent post on different names Oracle can use for the intermediate views, here is a quick example of the technique called distinct placement.

Continue reading

PQ_DISTRIBUTE enhancement – Part 1

Oracle 11g has introduced a hell lot of new features related to Parallel Execution – like statement queuing, in-memory PX and auto-DOP, to name a few. There are also small bits of enhancements here and there – like default value of the PARALLEL_EXECUTION_MESSAGE_SIZE and an introduction of an easy way to stick PX slaves to a current RAC node with PARALLEL_FORCE_LOCAL. As I’ve recently discovered, PQ_DISTRIBUTE hint used only for joins prior to 11gR2, can now be used for load operations.

Continue reading

DBMS_PARALLEL_EXECUTE

Here is a recent thread from OTN forums. The question raised is WTF? whether a hint in the examples section of DBMS_PARALLEL_EXECUTE documentation have to be used or not and why it is there. What do you think about it? Have a look in the documentation and mark your choice in a poll:

PS. In my view it’s a bug. I left a comment in the documentation about it, but didn’t receive any reply yet.

Native full outer join

Starting with version 11gR1 Oracle database uses native FULL JOIN implementation based on HASH JOIN whenever possible. It’s good to know that this functionality first appeared in Oracle 10.2.0.3 and could be used to overcome different issues, including performance and bugs. Here is an example appeared on the SQL.ru recently:

Continue reading

COALESCE_SQ

There are 19 new hints in Oracle 11.2.0.1:

SQL> select name from v$sql_hint where version='11.2.0.1';

NAME
------------------------------
COALESCE_SQ
NO_COALESCE_SQ
TRANSFORM_DISTINCT_AGG
NO_TRANSFORM_DISTINCT_AGG
CONNECT_BY_ELIM_DUPS
NO_CONNECT_BY_ELIM_DUPS
DST_UPGRADE_INSERT_CONV
NO_DST_UPGRADE_INSERT_CONV
STATEMENT_QUEUING
NO_STATEMENT_QUEUING
EXPAND_TABLE
NO_EXPAND_TABLE
XMLINDEX_SEL_IDX_TBL
FACTORIZE_JOIN
NO_FACTORIZE_JOIN
APPEND_VALUES
PLACE_DISTINCT
NO_PLACE_DISTINCT
NO_SUBSTRB_PAD

19 rows selected.

Some of them represents new CBO features (like FACTORIZE_JOIN/NO_FACTORIZE_JOIN). Here I’ll show purpose of COALESCE_SQ hint (NO_COALESCE is it’s inverse).

Continue reading

OPT_PARAM and OPTIMIZER_FEATURES_ENABLE

Here’s a question appeared on the oracle-l mailing list recently:

Does anyone know if opt_param hint works with optimizer_features_enable in 11.1.0.7?

Continue reading

Follow

Get every new post delivered to your Inbox.

Join 288 other followers