Search

OakieTags

Who's online

There are currently 0 users and 33 guests online.

Recent comments

hint

[Oracle] Insights into SQL hints - Embedded global and local hints and how to use them

Introduction

The idea for this blog post started a few weeks ago when i had to troubleshoot some Oracle database / SQL performance issues at client site. The SQL itself included several views and so placing hints (for testing purpose) into the views was not possible, especially as the views were used widely and not only by the SQL with the performance issue. In consequence this blog post is about the difference between embedded global and local hints and how to use them.

 

OPT_PARAM Hint saves the day…

One of the developers was performing a delete using a nested subquery, which was throwing out ORA-00600 errors. The ORA-00600 Lookup Tool on MOS suggested it was caused by this bug:

The workaround in the bug suggested running this ALTER SESSION command.

alter session set "_optimizer_unnest_disjunctive_subq"= FALSE;

Rather than alter the whole session, we used the OPT_PARAM hint, making the subquery look something like this.

APPEND_VALUES Hint…

The APPEND_VALUES hint is new to 11gR2 and allows you to use direct-path inserts from “INSERT INTO … VALUES” type statements. Pretty neat if you are doing inserts in a FORALL statement and need the extra punch.

Cheers

Tim…