Monday, June 23, 2014

How to specify SQL Hints in Oracle correctly


As we know, Oracle honors the hints as long as they are properly specified. The catch phrase here is "properly specified." If you don't specify the hints properly, Oracle simply ignores the hints without any errors or warnings which can be quite frustrating. We all have our favorite stories about hints not being honored by Oracle. We all have tried and tried so many permutations and combinations to make the hints work, but Oracle somehow stubbornly ignores our hints. But that's not Oracle's fault! You need to specify the hints properly. Otherwise, Oracle will simply ignore them. The following are few useful tips that would help you specify hints properly.

Useful Tips
  • Syntax - You must specify hints within /*+   <hint>   */
  • Case-insensitive - Hints are not case sensitive so you don't need to worry about upper or lower case.
  • Incorrect names - If names of the tables or indexes are not correct in the hints or have typos in them, Oracle will simply ignore the hints.
  • Availability of access path - If you specify a hint for an access path which is not available, then Oracle simply ignores the hint. For example, if you specify an index hint, but there are no indexes on the table, then Oracle will simply ignore the hint.
  • Join hints - Inner table and outer table must be specified properly. Otherwise hints may not work.

How to correctly specify Nested Loop Join Hint

If you want Oracle to use nested loop join method, then you need to specify the hint as /*+ leading(outer_table)  use_nl (inner_table) */. You may be fine with only use_nl  (inner_table) hint, but to leave nothing to doubt, you should specify leading (outer_table) hint as well. Let's take an example.

As shown below, the un-hinted execution plan uses hash join.

SELECT *
FROM T1, T2
WHERE T1.ID = T2.T1_ID                                                   

-----------------------------------                                                           
| Id  | Operation          | Name |                                                           
-----------------------------------                                                           
|   0 | SELECT STATEMENT   |      |                                                           
|   1 |  HASH JOIN         |      |                                                           
|   2 |   TABLE ACCESS FULL| T1   |                                                           
|   3 |   TABLE ACCESS FULL| T2   |                                                           
-----------------------------------                                                           

If you need nested loop join method on tables t1 and t2, then specify the hint as follows. Note that T1 is the inner table or row source and T2 is the outer table or row source.

SELECT /*+ leading(t2) use_nl(t1) */ *
FROM T1, T2
WHERE T1.ID = T2.T1_ID                                                                                      

-----------------------------------                                                           
| Id  | Operation          | Name |                                                           
-----------------------------------                                                           
|   0 | SELECT STATEMENT   |      |                                                           
|   1 |  NESTED LOOPS      |      |                                                           
|   2 |   TABLE ACCESS FULL| T2   |                                                           
|   3 |   TABLE ACCESS FULL| T1   |                                                           
-----------------------------------                                                           


If you need a nested loop join method with T2 as inner table and T1 as outer table, then specify hint as follows:

SELECT /*+ leading(t1) use_nl(t2) */ *
FROM T1, T2
WHERE T1.ID = T2.T1_ID                                                                                      

-----------------------------------                                                           
| Id  | Operation          | Name |                                                           
-----------------------------------                                                           
|   0 | SELECT STATEMENT   |      |                                                           
|   1 |  NESTED LOOPS      |      |                                                           
|   2 |   TABLE ACCESS FULL| T1   |                                                           
|   3 |   TABLE ACCESS FULL| T2   |                                                           
-----------------------------------                                                           


How to correctly specify Nested Loop (Semi) Join Hint - EXISTS Query

If you want to force nested loop (semi) join method, then you need to specify /*+ use_nl (inner_table) */ in the sub-query. Let's take an example. The below is un-hinted execution plan.

SELECT *
FROM T1
WHERE EXISTS ( SELECT 'x'                
               FROM T2                            
               WHERE T1.ID = T2.T1_ID )                                                           

-----------------------------------                                                           
| Id  | Operation          | Name |                                                           
-----------------------------------                                                           
|   0 | SELECT STATEMENT   |      |                                                           
|   1 |  HASH JOIN SEMI    |      |                                                           
|   2 |   TABLE ACCESS FULL| T1   |                                                           
|   3 |   TABLE ACCESS FULL| T2   |                                                           
-----------------------------------                                                           


To force nested loop (semi) join, please note how to specify leading and use_nl hints along with appropriate inner_table and outer_table.

SELECT /*+ leading(t1) */ *
FROM T1
WHERE EXISTS ( SELECT /*+ use_nl(t2) */ 'x'                
               FROM T2                
               WHERE T1.ID = T2.T1_ID )                                                                                    

-----------------------------------                                                           
| Id  | Operation          | Name |                                                           
-----------------------------------                                                           
|   0 | SELECT STATEMENT   |      |                                                           
|   1 |  NESTED LOOPS SEMI |      |                                                           
|   2 |   TABLE ACCESS FULL| T1   |                                                           
|   3 |   TABLE ACCESS FULL| T2   |                                                           
-----------------------------------                                                           


How to correctly specify Nested Loop (Semi) Join Hint - IN Queries

If you have a SQL with IN clause, as shown below, then use_nl hint must be specified within the inner query as shown below. Leading hint is specified in the outer query. The first example shows un-hinted statement while the second example shows hinted statement.

SELECT *
FROM t1
WHERE id IN ( SELECT t1_id FROM t2 )                           

-----------------------------------                                                           
| Id  | Operation          | Name |                                                           
-----------------------------------                                                           
|   0 | SELECT STATEMENT   |      |                                                           
|   1 |  HASH JOIN SEMI    |      |                                                           
|   2 |   TABLE ACCESS FULL| T1   |                                                           
|   3 |   TABLE ACCESS FULL| T2   |                                                           
-----------------------------------                                                           


SELECT /*+ leading(t1) */ * 
FROM t1
WHERE id IN ( SELECT /*+ use_nl(t2) */ t1_id              
              FROM t2 )                                                   

-----------------------------------                                                           
| Id  | Operation          | Name |                                                           
-----------------------------------                                                           
|   0 | SELECT STATEMENT   |      |                                                           
|   1 |  NESTED LOOPS SEMI |      |                                                           
|   2 |   TABLE ACCESS FULL| T1   |                                                           
|   3 |   TABLE ACCESS FULL| T2   |                                                           
-----------------------------------                                                           


How to correctly specify Nested Loop (anti) Join Hint  - NOT EXISTS Queries

The specifications for nested loop (anti) join hint are similar to nested loop (semi) join hint specifications. You must specify use_nl hint in the sub-query with inner table. The leading hint should be specified in the outer query. The first example shows un-hinted statement while the second example shows hinted statement.

SELECT  *
FROM T1
WHERE NOT EXISTS ( SELECT 'x'
                   FROM T2                    
                   WHERE T1.ID = T2.T1_ID )                                          

-----------------------------------                                                           
| Id  | Operation          | Name |                                                           
-----------------------------------                                                           
|   0 | SELECT STATEMENT   |      |                                                           
|   1 |  HASH JOIN ANTI    |      |                                                           
|   2 |   TABLE ACCESS FULL| T1   |                                                           
|   3 |   TABLE ACCESS FULL| T2   |                                                           
-----------------------------------                                                           


SELECT  /*+ leading(t1) */ *
FROM T1
WHERE NOT EXISTS ( SELECT /*+ use_nl(t2) */ 'x'                     
                   FROM T2                    
                   WHERE T1.ID = T2.T1_ID )                                                                            


-----------------------------------                                                           
| Id  | Operation          | Name |                                                           
-----------------------------------                                                           
|   0 | SELECT STATEMENT   |      |                                                           
|   1 |  NESTED LOOPS ANTI |      |                                                           
|   2 |   TABLE ACCESS FULL| T1   |                                                           
|   3 |   TABLE ACCESS FULL| T2   |                                                           
-----------------------------------                                                           













Friday, June 20, 2014

Enable Supplemental Logging

We recently encountered an issue in the production database due to a bug in the application batch processing code that resulted in corrupted data in one of the key tables. Developers suspected that the code, which performed extensive processing before persisting data to the database, might have caused this data corruption issue. Since we couldn't reproduce the same issue in our test environment, developers requested the DBA team that a complete history of update and delete statements that were fired against this production table be generated to facilitate troubleshooting. DBA team decided to deploy Oracle Log Miner to dig out the history of all update and delete statements that were fired against this table. DBA team had used LogMiner successfully in the past several times without any issue.

As you know, Oracle LogMiner presents a complete history of changes to the database by reading and processing redo vectors in the online and archived redo logs. DBAs started the LogMiner, added required archive log files and queried V$LOGMINER_CONTENTS to get the history of changes that had occurred against the table in question. The following is the LogMiner session log:

REM Step 1: Build LogMiner Dictionary

SQL> EXECUTE dbms_logmnr_d.build('dictionary.ora','e:\oradba\temp', options => dbms_logmnr_d.store_in_flat_file);

PL/SQL procedure successfully completed.

REM Step 2: Generate a script to add archived log files to LogMiner

SQL> set pagesi 0
SQL> set linesi 300
SQL> SELECT 'exec  dbms_logmnr.add_logfile(' || '''' || name || ''''|| ',DBMS_LOGMNR.ADDFILE );'
  FROM V$ARCHIVED_LOG
  WHERE FIRST_TIME >= TO_DATE('06/16/2014 00:00','mm/dd/yyyy hh24:mi')
  AND FIRST_TIME <= TO_DATE('06/16/2014 16:59','mm/dd/yyyy hh24:mi')
  AND dest_ID = 1 ;

exec  dbms_logmnr.add_logfile('E:\ORARCH\CTEST\CTEST8468602021012.1',DBMS_LOGMNR.ADDFILE );
exec  dbms_logmnr.add_logfile('E:\ORARCH\CTEST\CTEST8468602021039.1',DBMS_LOGMNR.ADDFILE );

REM Step 3: Add Redo Archived Log Files

SQL> exec  dbms_logmnr.add_logfile('E:\ORARCH\CTEST\CTEST8468602021012.1',DBMS_LOGMNR.ADDFILE );

PL/SQL procedure successfully completed.

SQL > exec  dbms_logmnr.add_logfile('E:\ORARCH\CTEST\CTEST8468602021039.1',DBMS_LOGMNR.ADDFILE );

PL/SQL procedure successfully completed.

REM Step 4: Start LogMiner

SQL > exec   DBMS_LOGMNR.START_LOGMNR(DICTFILENAME =>'e:\Oradba\temp\dictionary.ora');

PL/SQL procedure successfully completed.

REM Step 5: Create a table to store LogMiner contents

SQL > SELECT MIN(TIMESTAMP), MAX(TIMESTAMP) FROM V$LOGMNR_CONTENTS ;

06/16/2014:00:04:57 06/16/2014:16:45:07

SQL > CREATE TABLE TAB1_DML
  2  AS
  3  select *
  4  from v$logmnr_contents
  5  where table_name='DOC ;

Table created.

REM Step 6: End LogMiner Sesssion

SQL > exec DBMS_LOGMNR.END_LOGMNR;

PL/SQL procedure successfully completed.

The LogMiner contents that DBAs sent to the developers had few insert statements, but there were no update statements. This was surprising. We knew that the table was updated, but Oracle LogMiner contents didn't show any update statements. DBAs asserted that LogMiner wouldn't miss a single update or delete statement. A long meeting ensued. Lots of discussion and debate. Who updated the data? How was the data updated? Malicious activity was now a suspect. Security team was involved. Long story short, this became a big issue.

DBA team re-ran the LogMiner to double-check and triple-check everything. That's when DBAs realized that Supplemental Logging wasn't enabled as shown below:

SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE ;

SUPPLEME
--------------
NO

As Oracle® Database Utilities 11g Release 2 (11.2) mentions, "Supplemental logging, or minimal supplemental logging, logs the minimal amount of information needed for LogMiner to identify, group, and merge the redo operations associated with DML changes. It ensures that LogMiner (and any product building on LogMiner technology) has sufficient information to support chained rows and various storage arrangements, such as cluster tables and index-organized tables."

It is not a common knowledge that Supplemental Logging is a pre-requisite to Oracle LogMiner. I don't think supplemental logging has been on many best practices check lists. It hasn't gotten the same press as archive logs or flashback logs. Many DBAs use LogMiner without realizing that they may not get the complete history of changes to the database if supplemental logging is not enabled. Unfortunately, it would work without supplemental logging, but it should not be used without it.

Indeed, we had not enabled minimal supplemental logging in this database. Due to the update activities and smaller value of PCTFREE for this table, there were chained and migrated rows in the tables. We realized that LogMiner wouldn't be able to produce a complete list of changes to the table as we were expecting that it would. This was not a good news. Due to missing minimal supplemental logging information, it was not possible to produce a history of changes that had occurred through LogMiner.

That brings me to the purpose of this blog which is a recommendation to enable minimal supplemental logging. Minimal Supplemental logging is not default in Oracle9i or higher.  Quite often DBAs are not aware that it may be crucial in detecting/repairing logical database corruption issues through LogMiner. If you haven’t enable supplemental logging, then you should enable it now. Enabling supplemental logging is quite simple, as shown below:

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Database altered.

SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE ;

SUPPLEME
--------------
YES

In addition, you may want to enable primary keys (PK) supplemental logging as so that Oracle logs values of primary keys in the redo logs for UPDATE and DELETE statements. Without PK supplemental logging, Oracle only logs ROWIDs in the redo log vectors and hence it is not easy to identify updated or deleted rows just by reading SQL_UNDO and SQL_REDO columns of V$LOGMNR_CONTENTS. I would suggest enabling  PK supplemental logging as well. Please note that there is an overhead of PK supplemental logging so you need to weigh pros and cons before enabling PK supplemental logging. It's worth mentioning that replication tools such as Oracle Golden Gate, Oracle Logical Standby Database, Oracle Streams do require PK supplemental logging. Enabling PK supplemental logging is quite simple as shown below:

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS ;

Database altered.



DBMS_XPLAN.DISPLAY_CURSOR fails with error

In my day-to-day performance tuning activities, I use DBMS_XPLAN.DISPLAY_CURSOR quite often to display the execution plan of the last SQL executed in SQL*Plus, but it recently failed with the following message:

JMEHTA@ORA11203> select * from table(dbms_xplan.display_cursor) ;

PLAN_TABLE_OUTPUT
------------------------------------------------------------

SQL_ID  9babjv8yq8ru3, child number 0

BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;

NOTE: cannot fetch plan for SQL_ID: 9babjv8yq8ru3, CHILD_NUMBER: 0
      Please verify value of SQL_ID and CHILD_NUMBER;
      It could also be that the plan is no longer in cursor cache (check v$sql_plan)


DBMS_XPLAN.DISPLAU_CURSOR was showing the text of a SQL statement that I didn't execute. The fix to this issue is to disable SERVEROUTPUT via SET SERVEROUTPUT OFF so that SQL*Plus won’t execute the statement to fetch the output of DBMS_OUTPUT.PUT_LINE, if any.

JMEHTA@ORA11203> set serverout off

JMEHTA@ORA11203> select * from table(dbms_xplan.display_cursor) ;

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

SQL_ID  gc5ac0ywrn17r, child number 0
-------------------------------------

SELECT /*+ FULL(TP) */ * FROM TP WHERE ID = 1

Plan hash value: 1244819442

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |       |       |     1 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| TP        |   126 |  8190 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TP_ID_IDX |    50 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

It works!


A simple fix, but it took me few minutes to figure it out. A good idea, but not for DBMS_XPLAN.DISPLAY_CURSOR. Hopefully, if you encounter this issue, you will have to spend less time figuring it out.