RDA HVCE Option

The purpose of this blog is not discuss RDA but instead focus on an important option of RDA.  Never shakeout an environment  after  OS upgrade or Oracle install without this option.

The option is hvce (health validation check engine). HCVE performs health check of Oracle environment to find incorrect OS settings,  Incorrect important  OS parameters, missing OS packages.

..

See below for output of rda.sh -hcve

ID	NAME	RESULT	VALUE
A00110	ulimits OK?	FAILED	StackTooSmall
A00140	LD_LIBRARY_PATH Unse	FAILED	IsSet
A00160	LIBPATH Unset?	FAILED	IsSet
A00200	JDK Version	FAILED	JDK home is missing
A00210	Other O_Hs in PATH?	FAILED	OratabEntryInPath
A00310	DNS Lookup	FAILED	Host not correctly registered in DNS
A00320	/etc/hosts Format	FAILED	no entry found
A00335	aio_maxreqs OK?	FAILED	ioo and iostat error
A00010	OS Certified?	PASSED	Certified
A00015	HARDWARE_BITMODE 64?	PASSED	is 64-bit
A00016	System mode 64-bit?	PASSED	64-bit kernel enabled
A00020	User in /etc/passwd?	PASSED	userOK
A00040	Group in /etc/group?	PASSED	GroupOK
A00060	ORACLE_HOME Valid?	PASSED	OHexists
A00070	O_H Permissions OK?	PASSED	CorrectPerms
A00080	oraInventory Permiss	PASSED	oraInventoryNotFound
A00090	Got Software Tools?	PASSED	ld_nm_ar_make_found
A00100	Umask Set to 022?	PASSED	UmaskOK
A00220	Other OUI Up?	PASSED	NoOtherOUI
A00230	Temp Adequate?	PASSED	TempSpaceOK
A00240	Disk Space OK?	PASSED	DiskSpaceOK
A00260	RAM (in MB)	PASSED	40960
A00270	SwapToRAM OK?	PASSED	SwapToRAMOK
A00330	Kernel Parameters OK	PASSED	Parameters OK
A00350	LINK_CNTRL is Unset?	PASSED	LINK_CNTRLunset
A00410	Got OS Patches?	PASSED	PatchesFound
A00430	Got OS Packages?	PASSED	All required OS packages are installed
A00445	TCP/UDP Ephemeral OK	PASSED	Ephemeral OK
A00050	Enter ORACLE_HOME	RECORD	/app/home/oracle/product/11.2.0.2.0/db
A00190	Enter JDK Home	RECORD
A00250	Swap (in MB)	RECORD	20480
A00290	IP Address	RECORD	10.XXX.XX.XX
A00300	Domain Name	RECORD	NotFound
A00340	AIXTHREAD_SCOPE=S?	SKIPPED	NA

I am speaking at IOUG 2013

http://collaborate13.ioug.org/p/cm/ld/fid=197

 

I am speaking at prestigious Hotsos 2013 symposium

https://portal.hotsos.com/company/news/hotsos-symposium-2013-speakers-announced

I am speaking at Sangam 2012

Got Selected to Speak at DOAG 2012

I got selected to speak at DOAG 2012. Unfortunately I will not be able to make two international trips in one single month.  I feel very sad in declining this opportunity. Hopefully I will get another opportunity next year or so

Tracing specific SQL’s (Tracing Series-2)

Until 11g ,tracing was synonymous  with sessions.  You could not trace anything other than sessions (Don’t take this literally)–

–

With 11g  you have the option of  tracing individual  SQL’s by means a event/trigger.   This is very useful  if  you are executing a long running process  and interested in tracing only a  particular SQL.  You can trace the SQL either at session level or system level.
…

Syntax

• alter system set events ‘sql_trace [sql:sql_id] level 12′
• alter session set events ‘sql_trace [sql:sql_id] level 12′

Note:  The SQL trace command can be executed  for  more than 1 SQL_ID too by separating the  sql_id’s with pipe symbol

alter system set events ‘sql_trace [sql:sql_id|sql_id] level 12′;

 

=-

Steps to Trace the SQL

–

Step-1: Execute the following Query  to get the SQL_ID. 

.

SQL>select object_name from dba_objects where object_name=’DBA_TABLES’;

–

Step-2: Find the SQL_ID from v$sql

..
 SQL> column sql_text format a80
 SQL> set lines 120 pages 2000
 SQL> set long 3000
 SQL>
 SQL> select sql_id , sql_text from v$sql where sql_text like 
'%select object_name from dba_objects where object_name=%';
SQL_ID SQL_TEXT
 ------------- --------------------------------------------------------------------------------
 gynhgrz60jayw    select object_name from dba_objects where object_name='DBA_TABLES'
3n6v2xv2s5wm4     select sql_id , sql_text from v$sql where sql_text like '%select object_name fro
                  m dba_objects  where object_name=%'
--

.

Step-3: Enable the trace for the SQL_ID

–

.

SQL> alter system set events ‘sql_trace [sql:gynhgrz60jayw] level 12′;

System altered

.

Step-4: Execute the SQL statement to generate trace

–

.

SQL> select object_name from dba_objects where object_name=’DBA_TABLES’;

OBJECT_NAME
————————————————————————————————————————
DBA_TABLES
DBA_TABLES

–

Step-5: Analyze the trace file. This is the fun part. Identifying problem SQL is often easy but fixing it …

–

Trace file /app/oracle/diag/rdbms/mydb/mydn/trace/mydb_ora_36897294.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
With the Partitioning option
ORACLE_HOME = /app/oracle/product/11.2.0.2/db
System name: AIX
Node name: mydb1
Release: 1
Version: 6
Machine: 00CF7$B54C00
Instance name: mydb
Redo thread mounted by this instance: 1
Oracle process number: 28
Unix process pid: 36897294, image: oracle@mydb1 (TNS V1-V3)
*** 2012-08-07 16:55:40.894
*** SESSION ID:(1012.117) 2012-08-07 16:55:40.894
*** CLIENT ID:() 2012-08-07 16:55:40.894
*** SERVICE NAME:(SYS$USERS) 2012-08-07 16:55:40.894
*** MODULE NAME:(SQL*Plus) 2012-08-07 16:55:40.894
*** ACTION NAME:() 2012-08-07 16:55:40.894

=====================
PARSING IN CURSOR #4575285296 len=37 dep=1 uid=0 oct=3 lid=0 tim=49002870757326

hv=1398610540 ad=’700000127d65258′ sqlid=’grwydz59pu6mc’
select text from view$ where rowid=:1
END OF STMT
PARSE #4575285296:c=0,e=683,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=49002870757324
BINDS #4575285296:
Bind#0
oacdty=11 mxl=16(16) mxlc=00 mal=00 scl=00 pre=00
oacflg=18 fl2=0001 frm=00 csi=00 siz=16 off=0
kxsbbbfp=110b54bf0 bln=16 avl=16 flg=05
value=000004D6.0000.0001
EXEC #4575285296:c=0,e=1314,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=1028580536,tim=49002870772280
FETCH #4575285296:c=0,e=89,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,plh=1028580536,tim=49002870772402
STAT #4575285296 id=1 cnt=1 pid=0 pos=1 obj=59 op=’TABLE ACCESS BY USER ROWID VIEW$ (cr=2 pr=0 pw=0 time=69 us cost=1 size=15 card=1)’
CLOSE #4575285296:c=0,e=52,dep=1,type=0,tim=49002870772481
=====================
PARSING IN CURSOR #4575285296 len=37 dep=1 uid=0 oct=3 lid=0 tim=49002870773099 hv=1398610540 ad=’700000127d65258′ sqlid=’grwydz59pu6mc’
select text from view$ where rowid=:1
END OF STMT
PARSE #4575285296:c=0,e=23,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=1028580536,tim=49002870773098
BINDS #4575285296:
Bind#0
oacdty=11 mxl=16(16) mxlc=00 mal=00 scl=00 pre=00
oacflg=18 fl2=0001 frm=00 csi=00 siz=16 off=0
kxsbbbfp=110b54bf0 bln=16 avl=16 flg=05
value=0000C0C4.0000.0001
EXEC #4575285296:c=0,e=77,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=1028580536,tim=49002870773248
FETCH #4575285296:c=0,e=15,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=1028580536,tim=49002870773283
STAT #4575285296 id=1 cnt=1 pid=0 pos=1 obj=59 op=’TABLE ACCESS BY USER ROWID VIEW$ (cr=1 pr=0 pw=0 time=8 us cost=1 size=15 card=1)’
CLOSE #4575285296:c=0,e=29,dep=1,type=0,tim=49002870773333
=====================
PARSING IN CURSOR #4575286160 len=66 dep=0 uid=21 oct=3 lid=21 tim=49002870784095 hv=3423120348 ad=’7000001208cd1f8′ sqlid=’gynhgrz60jayw’
select object_name from dba_objects where object_name=’DBA_TABLES’
END OF STMT
EXEC #4575286160:c=0,e=38,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=2,plh=2998908629,tim=49002870784093
WAIT #4575286160: nam=’SQL*Net message to client’ ela= 4 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=49002870784259
FETCH #4575286160:c=0,e=336,p=0,cr=17,cu=0,mis=0,r=1,dep=0,og=2,plh=2998908629,tim=49002870784629
WAIT #4575286160: nam=’SQL*Net message from client’ ela= 280 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=49002870784977
WAIT #4575286160: nam=’SQL*Net message to client’ ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=49002870785044
FETCH #4575286160:c=0,e=2993,p=0,cr=304,cu=0,mis=0,r=1,dep=0,og=2,plh=2998908629,tim=49002870787996
STAT #4575286160 id=1 cnt=2 pid=0 pos=1 obj=1195 op=’VIEW DBA_OBJECTS (cr=321 pr=0 pw=0 time=332 us cost=79 size=132 card=2)’
STAT #4575286160 id=2 cnt=2 pid=1 pos=1 obj=0 op=’UNION-ALL (cr=321 pr=0 pw=0 time=328 us)’
STAT #4575286160 id=3 cnt=2 pid=2 pos=1 obj=0 op=’FILTER (cr=317 pr=0 pw=0 time=321 us)’
STAT #4575286160 id=4 cnt=2 pid=3 pos=1 obj=0 op=’NESTED LOOPS (cr=317 pr=0 pw=0 time=315 us cost=78 size=65 card=1)’
STAT #4575286160 id=5 cnt=2 pid=4 pos=1 obj=0 op=’NESTED LOOPS (cr=314 pr=0 pw=0 time=323 us cost=77 size=61 card=1)’
STAT #4575286160 id=6 cnt=2 pid=5 pos=1 obj=507636 op=’TABLE ACCESS FULL OBJ$ (cr=311 pr=0 pw=0 time=263 us cost=76 size=39 card=1)’
STAT #4575286160 id=7 cnt=2 pid=5 pos=2 obj=507644 op=’INDEX RANGE SCAN I_USER2 (cr=3 pr=0 pw=0 time=57 us cost=1 size=22 card=1)’
STAT #4575286160 id=8 cnt=2 pid=4 pos=2 obj=507644 op=’INDEX RANGE SCAN I_USER2 (cr=3 pr=0 pw=0 time=10 us cost=1 size=4 card=1)’
STAT #4575286160 id=9 cnt=0 pid=3 pos=2 obj=19 op=’TABLE ACCESS BY INDEX ROWID IND$ (cr=0 pr=0 pw=0 time=0 us cost=2 size=8 card=1)’
STAT #4575286160 id=10 cnt=0 pid=9 pos=1 obj=36 op=’INDEX UNIQUE SCAN I_IND1 (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)’
STAT #4575286160 id=11 cnt=0 pid=3 pos=3 obj=0 op=’NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=2 size=30 card=1)’
STAT #4575286160 id=12 cnt=0 pid=11 pos=1 obj=507644 op=’INDEX SKIP SCAN I_USER2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=20 card=1)’
STAT #4575286160 id=13 cnt=0 pid=11 pos=2 obj=507640 op=’INDEX RANGE SCAN I_OBJ4 (cr=0 pr=0 pw=0 time=0 us cost=1 size=10 card=1)’
STAT #4575286160 id=14 cnt=0 pid=2 pos=2 obj=0 op=’NESTED LOOPS (cr=4 pr=0 pw=0 time=199 us cost=1 size=29 card=1)’
STAT #4575286160 id=15 cnt=89 pid=14 pos=1 obj=507644 op=’INDEX FULL SCAN I_USER2 (cr=1 pr=0 pw=0 time=98 us cost=1 size=352 card=88)’
STAT #4575286160 id=16 cnt=0 pid=14 pos=2 obj=105 op=’INDEX RANGE SCAN I_LINK1 (cr=3 pr=0 pw=0 time=133 us cost=0 size=25 card=1)’

*** 2012-08-07 16:55:44.200
CLOSE #4575286160:c=0,e=38,dep=0,type=0,tim=49002874076143

Like Operator and Indexes

Trying to  predict Oracle’s behavior with respect to using indexes with LIKE operator is not simple.  However you may be able to get close enough with the following algorithm.  Let me  list  the number of ways you can use the single  LIKE operator along with the algorithm.

1. Beginning of search expression (%ABC) :  Less likely to use an Index (Most likely to perform full table scan)
2. End of search expression (ABC%): More likely to use an Index
3. In  between search expression (A%BC) : More likely to used an Index

However one key thing I want to mention is that if possible avoid using LIKE operator even if index is used and look for ways to use replace LIKE with  SUBSTR/INSTR/ASCII and so on.  This is to reduce the number of consistent gets even if index is being used.

See example below  for all the 3 case scenarios. In the 1st few commands, we are creating tables , indexes and gathering stats.

 

SQL> CREATE TABLE MY_DBAOBJECTS AS SELECT * FROM DBA_OBJECTS UNION ALL 
 2 SELECT * FROM DBA_OBJECTS UNION ALL SELECT * FROM DBA_OBJECTS;
-
TABLE CREATED.
SQL> 
SQL> CREATE INDEX IDX_OBJ_NAME ON MY_DBAOBJECTS (OBJECT_NAME);
-
INDEX CREATED.

SQL> EXECUTE DBMS_STATS.GATHER_INDEX_STATS(OWNNAME=>'DATA_MASTER', INDNAME=>'IDX_OBJ_NAME');
-
PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.
-
SQL> SET AUTOTRACE TRACEONLY EXPLAIN

-
Case:1
#####################################################################################
Beginning of search expression (%ABC): Less likely to use Index
#####################################################################################

SQL> SELECT OBJECT_ID FROM MY_DBAOBJECTS WHERE OBJECT_NAME LIKE '%DBA';
-
EXECUTION PLAN
------------------------------------------------------------------------
| ID | OPERATION | NAME | ROWS | BYTES | COST (%CPU)|
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 869 | 276 (1)|
|* 1 | TABLE ACCESS FULL| MY_DBAOBJECTS | 11 | 869 | 276 (1)|
------------------------------------------------------------------------
PREDICATE INFORMATION (IDENTIFIED BY OPERATION ID):
---------------------------------------------------
1 - FILTER("OBJECT_NAME" IS NOT NULL AND "OBJECT_NAME" LIKE '%DBA')
NOTE
-----
 - 'PLAN_TABLE' IS OLD VERSION

-
Case-2
#####################################################################################
End of search expression (ABC%): More likely to use an Index
#####################################################################################

SQL> SELECT OBJECT_ID FROM MY_DBAOBJECTS WHERE OBJECT_NAME LIKE 'DBA%';
-
EXECUTION PLAN
----------------------------------------------------------------------------------
| ID | OPERATION | NAME | ROWS | BYTES | COST (%CPU)|
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6176 | 476K| 10 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID| MY_DBAOBJECTS | 6176 | 476K| 10 (0)|
|* 2 | INDEX RANGE SCAN | IDX_OBJ_NAME | | | 2 (0)|
----------------------------------------------------------------------------------
PREDICATE INFORMATION (IDENTIFIED BY OPERATION ID):
---------------------------------------------------
2 - ACCESS("OBJECT_NAME" LIKE 'DBA%')
 FILTER("OBJECT_NAME" LIKE 'DBA%')
NOTE
-----
 - 'PLAN_TABLE' IS OLD VERSION

--
Case-3
#####################################################################################
In between search expression (A%BC) : More likely to used an Index
#####################################################################################

SQL> SELECT OBJECT_ID FROM MY_DBAOBJECTS WHERE OBJECT_NAME LIKE 'DBA%T';
-
EXECUTION PLAN
----------------------------------------------------------------------------------
| ID | OPERATION | NAME | ROWS | BYTES | COST (%CPU)|
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 228 | 18012 | 10 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID| MY_DBAOBJECTS | 228 | 18012 | 10 (0)|
|* 2 | INDEX RANGE SCAN | IDX_OBJ_NAME | 228 | | 2 (0)|
----------------------------------------------------------------------------------
PREDICATE INFORMATION (IDENTIFIED BY OPERATION ID):
---------------------------------------------------
2 - ACCESS("OBJECT_NAME" LIKE 'DBA%T')
 FILTER("OBJECT_NAME" LIKE 'DBA%T')
NOTE
-----
 - 'PLAN_TABLE' IS OLD VERSION
SQL>

I am Speaking at InSync12 (Melbourne)

-

 

I will be presenting once again at this year InSync conference at  Melbourne (Aug 21/22),  Australia.  My session is about Oracle GoldenGate Best Practices and performance optimization techniques.  Last years conference was in Sydney convention center and had impressive list of speakers including Tom Kyte, Richard Foote, Andrew Holdsworth & Graham Wood.  

..

..

About InSync12

..

The InSync12 Conference and Exhibitor Showcase is a joint event of the Quest ANZ user group – representing JDE and PeopleSoft customers, and the Australian Oracle User Group (AUSOUG) representing the Technology, Development and Applications users.  This is the fourth time the independent user groups in Australia will come together for one national conference.  See link for more details http://www.insync12.com.au/

Tracing SPM’s and Datapump (Tracing Series-I)

Some of  the complex issues that we face in our day-day operations can be resolved by tracing with or without Oracle support.  It depends on how much you want to grind yourself as understanding some of the trace files are not for faint hearts.  I always end up spending time googling for appropriate  syntax or trace number. Sometimes you  are lucky enough to get  to the intended document but sometimes you end up at wrong place.   So here I am compiling a one stop shop for all tracing needs as series.  In  Part-I , I will be discussing SPM tracing and Datapump tracing.  SPM and datapump have nothing in common , just  picked them randomly

–
SPM Tracing: There may be many  reasons why one would trace SPM.  Few of the reasons I can think of is to diagnose SPM’s not being created or to see more information of plan evolution.  SPM Tracing can be enabled either of following ways

•     exec dbms_spm.configure(‘spm_tracing’,1);

I could not find documentation about spm_tracing option  in 11gR2. Two documented parameters supported by dbms_spm.configure procedure are  space_budget_percent and plan_retention_weeks.  Only references for parameter spm_tracing was found in metalink with values 0 (disable tracing) and 1(enable tracing). The actual command however takes any values you provide.  You can query sys.smb$config to verify tracing is enabled or disabled

SQL> exec dbms_spm.configure('spm_tracing',1); 

PL/SQL procedure successfully completed.

SQL> 
SQL>  select parameter_name, parameter_value from sys.smb$config;

PARAMETER_NAME                 PARAMETER_VALUE
------------------------------ ---------------
SPACE_BUDGET_PERCENT                        25
PLAN_RETENTION_WEEKS                       523
SPM_TRACING                                  1

•     alter session set events ‘trace [sql_planmanagement.*]‘;

Noticed the 2nd command; If does not look familiar then you are not  alone. This is the new syntax introduced with 11g also referred to as universal tracing.  Unfortunately I was not able to get this working after trying different options. I will follow-up with Oracle on this command.
SQL>
SQL> alter session set events ‘trace [sql_planmanagement.*]‘;
ERROR:
ORA-49100: Failed to process event statement [trace [sql_planmanagement.*]]
ORA-48904: String [SQL_PLANMANAGEMENT] is not a valid component name

SQL> alter session set events ‘trace {sql_plan_management.*}’;
ERROR:
ORA-49100: Failed to process event statement [trace {sql_plan_management.*}]
ORA-49109: Library Name [SQL_PLAN_MANAGEMENT] not found

SQL>

–

DataPump Tracing:  Datapump can be traced in multiple ways either at database level or at command level or with 10046 trace.  With datapump , tracing is as complex as analyzing the trace files.  Never understood the reasoning behind  exposing hexadecimals to users unless this was intended for Oracle support. Datapump tracing  is enabled with  TRACE parameter using 7 digit hexadecimal. The first three digits enable the trace  while last four digits are usually “0300″
At the database level, datapump tracing can be enabled with initialization parameter EVENT 39089.  Again you have to find the correct  hexadecimal value and  bounce the database.

–

Example:(Init.ora setting)

EVENT=”39089 trace name context forever,level 0×300″ .

–

Example (alter system ,10046 trace)
alter system set events ‘sql_trace {process : pname = dw | pname = dm} level=12′; Just replace 12 with off to disable tracing. Be aware of this command , it will take any argument for process name.

–
SQL> alter system set events ‘sql_trace {process : pname = dw } level=12′;

System altered.

–

SQL> alter system set events ‘sql_trace {process : pname =djsljl} level=12′;  ==> Incorrect value

System altered.

SQL>
—-

–

Example (Command level)
expdp system/manager DIRECTORY=mydata_dir DUMPFILE=full_db.dmp LOGFILE=full_db.log full=y TRACE=480300

–

–

Here is information about  the hexadecimals supported trace levels supported.  Oracle also supports combination of these values

================================================================================
Hex     | Purpose
================================================================================
10300   |  SHDW: To trace the Shadow process (API) (expdp/impdp)
20300   |  KUPV: To trace Fixed table
40300   |  'div' To trace Process services
80300   |  KUPM: To trace Master Control Process (MCP,DM)
100300  |  KUPF: To trace File Manager
200300  |  KUPC: To trace Queue services
400300  |  KUPW: To trace Worker process(DW)
800300  |  KUPD: To trace Data Package
1000300 |  META: To trace Metadata Package
1FF0300 |  'all' To trace all components(full tracing)
================================================================================

Unnesting Nested Loops

Lately I have been trying to help my non-DBA colleague to understand optimizer operations. Now that I am putting this effort , why not add it to my blog. I will be writing a series of blogs discussing optimizer operations.  Let me start the honors with NESTED LOOPS.

NESTED LOOPS:  As the name implies, this operation works similar to nested loops in programming language aka a loop within a loop.   For example, let’s use PL/SQL language that most of us are familiar

SQL> BEGIN

2       FOR  OuterLoop IN 1..2 LOOP

3            FOR InnerLoop  IN 1..3 LOOP

4                 DBMS_OUTPUT.PUT_LINE(‘OuterLoop=> ‘|| OuterLoop  || ‘   ‘||  ‘ InnerLoop=> ‘ || InnerLoop);

5            END LOOP;

6       END LOOP;

7  END;

8  /

OuterLoop=> 1    InnerLoop=> 1

OuterLoop=> 1    InnerLoop=> 2

OuterLoop=> 1    InnerLoop=> 3

OuterLoop=> 2    InnerLoop=> 1

OuterLoop=> 2    InnerLoop=> 2

OuterLoop=> 2    InnerLoop=> 3

 PL/SQL procedure successfully completed.

In this example, the outer loop executes 2 times and for each execution of the outer loop, the inner loop executes 3 times. Switching gears to nested loop between let’s say tables A and B; Assume table A has 50 rows and table B has 1000 rows.  If table A is used as the outer table, then for every row accessed in table a, inner table B will be accessed 1000 times.

So who makes the choice of deciding the outer and inner table? Well the Oracle optimizer does. Generally the rule of thumb is the table with less number of rows is used in the outer loop and table will more number of rows is used in the inner table.   This is where table statistics plays an important role, actually up-to-date statistics.  Number of IO operations and blocks retrieved also influences the decision in choosing the appropriate outer and inner tables. None of what is mentioned in this article is always true  as most of us don’t have access to actual Oracle logic .

A nested loops join is  very effective if the outer input is small and the inner input is indexed and large.  Generally nested loops perform better in OLTP environment where the result set tends to be small.

Changes in 11g

Execution plans with 11G might look different because of  changes in nested loop implementation. The change was introduced  to reduce overall latency for physical I/O  when the required data for nested loop join is not in the buffer cache.  Oracle reduces the overall physical I/O request latency by batching multiple physical I/O requests  improving the  performance (most likely). You can see the new batching operation with a  10053 trace or  DBMS_XPLAN.DISPLAY_CURSOR.

You will notice  two NESTED LOOPS  operations  in the execution plan instead of one because of this implementation change.   Two nested loop are required for

1.   1st nested loop to  join data from outer loop table  and index on the inner loop table.
2.   2nd nested loop to join the result set of  1st nested loop and table  data on the inner side of the join.

The above implementation change can be controlled with init.ora parameter optimizer_features_enable or  with a new hint “no_nlj_batching”

For example, Let us run the a query with optimizer_features_enable set to Oracle version 11 and then again rerun the same query with Oracle version 10.

Case-1 : optimizer_features_enable=11.2.0.2

In this case you will see 2  nested loops in the execution plan and new operation NLJ_BATCHING in the outline_data.

SQL> SELECT *   FROM   TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ADVANCED'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  apzagcxrnwka8, child number 0
-------------------------------------
SELECT   A.OBJECT_NAME ,  A. LAST_DDL_TIME , B.CREATED FROM A, B WHERE
b.OBJECT_ID=a.OBJECT_ID  AND  a.OBJECT_ID BETWEEN 1 AND 30

Plan hash value: 1521750179

----------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |       |       |     5 (100)|          |
|   1 |  NESTED LOOPS                 |        |       |       |            |          |
|   2 |   NESTED LOOPS                |        |     3 |   135 |     5   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| A      |    16 |   512 |     3   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | IDX_A  |    16 |       |     2   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN          | INDX_B |     1 |       |     0   (0)|          |
|   6 |   TABLE ACCESS BY INDEX ROWID | B      |     1 |    13 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   3 - SEL$1 / A@SEL$1
   4 - SEL$1 / A@SEL$1
   5 - SEL$1 / B@SEL$1
   6 - SEL$1 / B@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
      DB_VERSION('11.2.0.2')
      OPT_PARAM('query_rewrite_enabled' 'false')
      FIRST_ROWS(10)
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "A"@"SEL$1" ("A"."OBJECT_ID"))
      INDEX(@"SEL$1" "B"@"SEL$1" ("B"."OBJECT_ID"))
      LEADING(@"SEL$1" "A"@"SEL$1" "B"@"SEL$1")
      USE_NL(@"SEL$1" "B"@"SEL$1")
      NLJ_BATCHING(@"SEL$1" "B"@"SEL$1")      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("A"."OBJECT_ID">=1 AND "A"."OBJECT_ID"<=30)
   5 - access("B"."OBJECT_ID"="A"."OBJECT_ID")
       filter(("B"."OBJECT_ID"<=30 AND "B"."OBJECT_ID">=1))

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "A"."OBJECT_NAME"[VARCHAR2,128], "A"."LAST_DDL_TIME"[DATE,7],
       "B"."CREATED"[DATE,7]
   2 - "A"."OBJECT_NAME"[VARCHAR2,128], "A"."LAST_DDL_TIME"[DATE,7],
       "B".ROWID[ROWID,10]
   3 - "A"."OBJECT_NAME"[VARCHAR2,128], "A"."OBJECT_ID"[NUMBER,22],
       "A"."LAST_DDL_TIME"[DATE,7]
   4 - "A".ROWID[ROWID,10], "A"."OBJECT_ID"[NUMBER,22]
   5 - "B".ROWID[ROWID,10]
   6 - "B"."CREATED"[DATE,7]

Case-2 : optimizer_features_enable=10.2.0.3

In this case you will see 1  nested loops in the execution plan and new operation NLJ_BATCHING in missing in outline_data.

SQL> SELECT *   FROM   TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ADVANCED'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  apzagcxrnwka8, child number 1
-------------------------------------
SELECT   A.OBJECT_NAME ,  A. LAST_DDL_TIME , B.CREATED FROM A, B WHERE
b.OBJECT_ID=a.OBJECT_ID  AND  a.OBJECT_ID BETWEEN 1 AND 30

Plan hash value: 1900124691

---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |       |       |     5 (100)|          |
|   1 |  NESTED LOOPS                |        |     3 |   135 |     5   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| A      |    16 |   512 |     3   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX_A  |    16 |       |     2   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| B      |     1 |    13 |     1   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN         | INDX_B |     1 |       |     0   (0)|          |
---------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   2 - SEL$1 / A@SEL$1
   3 - SEL$1 / A@SEL$1
   4 - SEL$1 / B@SEL$1
   5 - SEL$1 / B@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      DB_VERSION('11.2.0.2')
      OPT_PARAM('query_rewrite_enabled' 'false')
      FIRST_ROWS(10)
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "A"@"SEL$1" ("A"."OBJECT_ID"))
      INDEX_RS_ASC(@"SEL$1" "B"@"SEL$1" ("B"."OBJECT_ID"))
      LEADING(@"SEL$1" "A"@"SEL$1" "B"@"SEL$1")
      USE_NL(@"SEL$1" "B"@"SEL$1")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("A"."OBJECT_ID">=1 AND "A"."OBJECT_ID"<=30)
   5 - access("B"."OBJECT_ID"="A"."OBJECT_ID")
       filter(("B"."OBJECT_ID"<=30 AND "B"."OBJECT_ID">=1))

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "A"."OBJECT_NAME"[VARCHAR2,128], "A"."LAST_DDL_TIME"[DATE,7],
       "B"."CREATED"[DATE,7]
   2 - "A"."OBJECT_NAME"[VARCHAR2,128], "A"."OBJECT_ID"[NUMBER,22],
       "A"."LAST_DDL_TIME"[DATE,7]
   3 - "A".ROWID[ROWID,10], "A"."OBJECT_ID"[NUMBER,22]
   4 - "B"."CREATED"[DATE,7]
   5 - "B".ROWID[ROWID,10]

63 rows selected.