PLSQL Performance Tuning with Hints in Oracle

/* Append */

The APPEND_VALUES hint in Oracle 11g Release 2 now allows us to take advantage of direct-path inserts when insert statements include a VALUES clause. Typically we would only want to do this when the insert statement is part of bulk operation using the FORALL statement. We will use the following table to demonstrate the effect of the hint.
This is because during a regular (conventional-path) insert, Oracle tries to use up any free space currently allocated to the table, including space left from previous delete operations. In contrast direct-path inserts ignore existing free space and append the data to the end of the table. After preparing the base table we time how long it takes to perform conventional-path insert as part of the FORALL statement. Next, we repeat the same test, but this time use a the APPEND_VALUES hint to give us direct-path inserts.

Ex:

INSERT /*+ APPEND */ INTO forall_test
    SELECT level, TO_CHAR(level), 'Description: ' || TO_CHAR(level)
    FROM   dual
    CONNECT BY level <= l_size;

/* PARALLEL(3) */

This query will execute in multi threads like 3 parallel operations of employee table. So its will be fast.

Ex:
SELECT /*+ PARALLEL(employees 3) */ e.last_name, d.department_name
FROM   employees e, departments d
WHERE  e.department_id=d.department_id;

Ex:
SELECT /*+ PARALLEL(4) */ hr_emp.last_name, d.department_name
FROM   employees hr_emp, departments d
WHERE  hr_emp.department_id=d.department_id;

/* FIRST_ROWS(10) */

This hint we use when we need to get only top 10 rows then will use this.

Ex: SELECT /*+ FIRST_ROWS(10) */ * FROM employees;

/* LEADING(e1) */
This hint we use when multiple table in the query and consider which table should take first preference while executing we can give leading table alias.


/* INDEX (t1 t1_idx1) */
This hint we use for forcefully consider the index while executing the query.

SELECT /*+ index(t1 t1_abc) index(t2 t2_abc) */ COUNT(*)
FROM t1, t2
WHERE t1.col1 = t2.col1;

/*+ DRIVING_SITE([@queryblock] ) */
Forces query execution to be done at a user selected  site rather than at a site selected by the database. This hint is useful if you are using distributed query optimization.

EX:
SELECT /*+ DRIVING_SITE(p1) AAA */ p1.first_name, p2.first_name, p2.last_name
FROM person p1, person@psoug_user p2
WHERE p1.person_id = p2.person_id
AND p1.first_name <> p2.first_name;

/* Ordered table1 table2*/ 
this hint is which order table should join in From clause.