Saturday, January 21, 2012

Cross-Platform Transportable Tablespaces in Oracle

Oracle transportable tablespaces are the fastest way for moving large volumes of data between two Oracle databases. Starting with Oracle Database 10g, we can transport  tablespaces across platforms . Furthermore, like import and export, transportable tablespaces provide a mechanism for transporting metadata in addition to transporting data. Below are the steps to perform the cross platform transport tablespace .

Prerequisites :
There are few points which has to be considered before performing the Transportable tablespaces . The followings are : 

1.) The source and target database must use the same character set and national character set .

2.) We cannot transport a tablespace to a target database in which a tablespace with the same name already exists. However, we can rename either the tablespace to be transported or the destination tablespace before the transport operation.

3.) Objects with underlying objects (such as materialized views) or contained objects (such as partitioned tables) are not transportable unless all of the underlying or contained objects are in the tablespace set.

4.) If the owners of tablespace objects does not exist on target database,  the usernames need to be created manually before starting the transportable tablespace import. If  we use spatial indexes, then:
  • be aware that TTS across different endian platforms are not supported  for spatial indexes in 10gR1 and 10gR2; such a limitation has been released in 11g .
  • Specific Spatial packages must be run before exporting and after transportation, please see Oracle Spatial documentation.
5.) We cannot transport the SYSTEM  tablespace or objects owned by the user SYS . 

6.) Opaque Types (such as RAW, BFILE, and the AnyTypes) can be transported, but they are not converted as part of the cross-platform transport operation. Their actual structure is known only to the application, so the application  must address any endianness issues after these types are moved to the new  platform .

7.) Floating-Point Numbers BINARY_FLOAT and BINARY_DOUBLE types are transportable using Data Pump but not the original export utility, EXP.

If we are migrating a database, then make sure there are no invalid objects in the source database before making the export. Take a full no rows export to recreate objects that won't be transported with TTS or remove all the invalid objects by running utlrp.sql scripts . Keep the source database viable until we have determined all objects are in the target database and there are no issues (i.e. the target database has been thoroughly checked out ).

Here we are performing the cross platform transport tablespace demo .The details about the sorce and target are as below : 

Source : 
OS                      = Redhat Linux (32 bit)
Oracle Version    = Oracle 10.2.0
Database Name  = comcast 
Tablespace         = TES_TBS (which is to be transported)

Target : 
OS                      = Microsoft Window (32 bit)
Oracle Version    = Oracle 11.2.0
Database Name  =  noida

Step 1 : Determine Platforms Support and  Endianness (on source) :
This step is only necessary if we are transporting the tablespace set to a platform different from the source platform. Determine whether cross-platform tablespace transport is supported for both the source and target platforms, and also determine the endianness of each platform . We can query the v$transportable_platform view to see the platforms that are supported, and to determine each platform's endian format (byte ordering). 
Now check the endians by below query:  
Source endians :

SQL> select  d.platform_name, endian_format  from   v$transportable_platform  tp ,   v$database d   where  tp.PLATFORM_NAME  =  d.PLATFORM_NAME ;
PLATFORM_NAME                   ENDIAN_FORMAT
-----------------------                 ----------------------
Linux IA (32-bit)                         Little

Target endians  :

SQL>  select d.platform_name,endian_format from v$transportable_platform tp , v$database d where 
tp.PLATFORM_NAME = d.PLATFORM_NAME;
PLATFORM_NAME                            ENDIAN_FORMAT
-----------------------------                   ---------------------
Microsoft Windows IA (32-bit)             Little

Here , we notice that the both source and target have same endians . If we have different endians then follow step 2 else skip it and move to step 3 .

Step 2 :  Different endian formats ( skip step 2 if  having same endians ) :
If  the endian formats are different  then a conversion is necessary for transporting the tablespace. For example, run the below command to convert the tablespace of Source(Linux 64 bit)  to Target(Solaris 64 bit) platform .

i.>  Using  CONVERT  Tablespace   FROM  PLATFORM  on  Source  host

RMAN> convert tablespace test_user_tbs 
2> to platform ‘Solaris[tm] OE (64-bit)'
3> format='/oradata/rman_backups/%N_%f' ;

The data file “test_user_tbs01.dbf” is not touched and a new file will be created for Solaris platform under “/oradata/rman_backups“and copy the file to Target platform. Use RMAN's CONVERT command to convert the datafiles to be transported to the destination host's format. The converted datafiles are stored in “/solaris/oradata”.

ii.>  Using CONVERT DATAFILE... FROM PLATFORM on Destination host

RMAN> convert datafile test_user_tbs01.dbf
2> from platform ‘Linux IA (64-bit)'
3> db_file_name_convert ‘/linux/oradata/’ ‘/solaris/oradata’

Let's have a demo of the transportable tablespace . 

Step 3  : Create a tablespace and user  : 
Here we will create the Tablespace and User on the source database and this tablespace will be transported further .

SQL> create tablespace tes_tbs datafile '/home/oracle/oradata/comcast/tes_tbs.dbf' size 100m ;
Tablespace created

SQL> create user tes identified by tes 
  2  default tablespace tes_tbs 
  3  quota unlimited on tes_tbs ; 
User created.

SQL> grant resource,connect to tes ;
Grant succeeded.

SQL> create table tes.t1 as select * from hr.employees ; 
Table created.

SQL> create index TES.IND_EMP_T1 on tes.t1(employee_id) tablespace sysaux ;
Index created.

SQL> create table tes.t2 as select * from dba_extents; 
Table created.

Step  4 :  Check Self-Contained Set of  Tablespaces  :
There may be logical or physical dependencies between objects in the transportable set and those outside of the set. We can only transport a set of  tablespaces that is self-contained . Some examples of self contained tablespace violations are:
  • An index inside the set of tablespaces is for a table outside of the set of tablespaces.
  • A partitioned table is partially contained in the set of tablespaces.
  • A referential integrity constraint points to a table across a set boundary.


The statement below can be used to determine whether Tablespace are self-contained or not : 
SQL> conn sys as sysdba
Enter password: 
Connected.

SQL> EXECUTE dbms_tts.transport_set_check('TES_TBS', TRUE, TRUE) ;
PL/SQL procedure successfully completed.

The  DBMS_TTS  package checks if the transportable set is self-contained. All violations are inserted into a temporary table that can be selected from the transport_set_violations view. 

SQL> select * from   transport_set_violations;
VIOLATIONS
----------------------------------------------------------------------------------------------------
Index TES.IND_EMP_T1 in tablespace SYSAUX points to table TES.T1 in tablespace TES_TBS

Since, there is violation ,so we manully  move the object to target tablespace .Here, we can rebuild the index and move it into tablespace 'TES_TBS' as 

SQL> alter  index  TES.IND_EMP_T1  rebuild  tablespace  tes_tbs ;
Index altered.

Again , run the dbms_tts package to check the violations .

SQL> EXECUTE dbms_tts.transport_set_check('TES_TBS', TRUE, TRUE);
PL/SQL procedure successfully completed.

SQL> select * from   transport_set_violations;
no rows selected

Now there is no violations. If there is any dependency object , then  we get  "ORA-29341: The transportable set is not self-contained"  error while exporting the tablespace .

Step 5 : Generate a Transportable Tablespace Set : 
After ensuring that we have a self-contained set of tablespaces that we want to transport, generate a transportable tablespace set by performing the following actions . Make the tablespaces read-only.

SQL> alter tablespace tes_tbs read only ;
Tablespace altered.

Now export the metadata of tablespace "tes_tbs"  as 

$ expdp system/xxxx  dumpfile=tes_tbs_exp.dmp  transport_tablespaces=tes_tbs  transport_full_check=y  logfile=tes_tbs_export.log

Export: Release 10.2.0.1.0 - Production on Friday, 20 January, 2012 14:03:53
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/******** dumpfile=tes_tbs_exp.dmp transport_tablespaces=tes_tbs transport_full_check=y logfile=tes_tbs_export.log 
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
  /home/oracle/product/10.2.0/db_1/rdbms/log/tes_tbs_exp.dmp
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 14:07:44

Step 6 : Copy the datafile and  dumpfile on target database 
Transport both the datafiles and the export file of the tablespaces to a place that is accessible to the target database.

Step 7 : Import the dumpfile in target database 
Before importing the dumpfile make sure that tablespace of same doesnot exist in the target database. Also check the user may exist which is having default tablespace i.e, "TES_TBS" . We can also use the remap_schema parameter to restore it into some other schemas.

C:\>impdp system/xxxx  dumpfile=tes_tbs_exp.dmp TRANSPORT_DATAFILES='C:\app\Neerajs\oradata\noida\tes_tbs.dbf' logfile=tes_imp.log

Import: Release 11.2.0.1.0 - Production on Fri Jan 20 14:38:14 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/******** dumpfile=tes_tbs_exp.dmp  TRANSPORT_DATAFILES='C:\app\Neerajs\oradata\noida\tes_tbs.dbf' logfile=tes_imp.log

Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 14:38:48

Step 8 : Connect to target Database  

C:\>sqlplus sys/xxxx@noida as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 20 14:40:04 2012
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter tablespace tes_tbs read write ;
Tablespace altered.

SQL> conn tes/tes
Connected.

SQL> select  *  from tab;

TNAME       TABTYPE     CLUSTERID
----------     -----------     --------------
T1                TABLE
T2                TABLE

Here, we find the tablespace is successfully transported with all the tables. The transportable tablespace feature is useful in a number of scenarios, including:
  • Exporting and importing partitions in data warehousing tables 
  • Copying multiple read-only versions of a tablespace on multiple databases
  • Performing tablespace point-in-time-recovery (TSPITR) 
  • Migrating databases among RDBMS versions and OS platforms



Enjoy       J J J


Friday, January 13, 2012

Interpreting Explain Plan



An explain plan is a representation of the access path that is taken when a query is executed within Oracle.
Query processing can be divided into 7 phases:
[1] SyntacticChecks the syntax of the query
[2] SemanticChecks that all objects exist and are accessible
[3] View MergingRewrites query as join on base tables as opposed to using views
[4] Statement
     Transformation
Rewrites query transforming some complex constructs into simpler ones where appropriate (e.g. subquery merging, in/or transformation)
[5] OptimizationDetermines the optimal access path for the query to take. With the Rule Based Optimizer (RBO) it uses a set of heuristics to determine access path. With the Cost Based Optimizer (CBO) we use statistics to analyze the relative costs of accessing objects.
[6] QEP GenerationQEP = Query Evaluation Plan
[7] QEP ExecutionQEP = Query Evaluation Plan
Steps [1]-[6] are handled by the parser. Step [7] is the execution of the statement.

The explain plan is produced by the parser. Once the access path has been decided upon it is stored in the library cache together with the statement itself. We store queries in the library cache based upon a hashed representation  of that query. When looking for a statement in the library cache, we first apply a hashing algorithm to the statement and then we look for this hash value in the library cache. This access path will be used until the query is reparsed.

Terminology 
Row SourceA set of rows used in a query may be a select from a base object or the result set returned by joining 2 earlier row sources
Predicatewhere clause of a query
Tuplesrows
Driving TableThis is the row source that we use to seed the query. If this returns a lot of rows then this can have a negative affect on all subsequent operations
Probed TableThis is the object we lookup data in after we have retrieved relevant key data from the driving table.
How does Oracle access data?
At the physical level Oracle reads blocks of data. The smallest amount of data read is a single Oracle block, the largest is constrained by operating system limits (and multiblock i/o). Logically Oracle finds the data to read by using the following methods:
  • Full Table Scan (FTS)
  • Index Lookup (unique & non-unique)
  • Rowid
Explain plan Hierarchy

Simple explain plan:
Query Plan
-----------------------------------------
SELECT STATEMENT     [CHOOSE] Cost=1234
  TABLE ACCESS FULL LARGE [:Q65001] [ANALYZED]

The rightmost uppermost operation of an explain plan is the first thing that the explain plan will execute. In this case TABLE ACCESS FULL LARGE is the first operation. This statement means we are doing a full table scan of table LARGE. When this operation completes then the resultant row source is passed up to the
next level of the query for processing. In this case it is the SELECT STATEMENT which is the top of the query.


[CHOOSE] is an indication of the optimizer_goal for the query. This DOES NOT necessarily indicate that plan has actually used this goal. The only way to confirm this is to check the
cost= part of the explain plan as well. For example the following query indicates that the CBO has been used because there is a cost in the cost field:


SELECT STATEMENT     [CHOOSE] Cost=1234
However the explain plan below indicates the use of the RBO because the cost field is blank:
SELECT STATEMENT     [CHOOSE] Cost=
The cost field is a comparative cost that is used internally to determine the best cost for particular plans. The costs of different statements are not really directly comparable.

[:Q65001] indicates that this particular part of the query is being executed in parallel. This number indicates that the operation will be processed by a parallel query slave as opposed to being executed serially.

[ANALYZED] indicates that the object in question has been analyzed and there are currently statistics available for the CBO to use. There is no indication of the 'level' of analysis done.

Access Methods in detail

Full Table Scan (FTS)
In a FTS operation, the whole table is read up to the high water mark (HWM). The HWM marks the last block in the table that has ever had data written to it. If you have deleted all the rows then you will still read up to the HWM. Truncate resets the HWM back to the start of the table. FTS uses multiblock i/o to read the blocks from disk. Multiblock i/o is controlled by the parameter <PARAM:db_block_multi_block_read_count>.
This defaults to:
db_block_buffers / ( (PROCESSES+3) / 4 )
Maximum values are OS dependant

Buffers from FTS operations are placed on the Least Recently Used (LRU) end of the buffer cache so will be quickly aged out. FTS is not recommended for large tables unless you are reading >5-10% of it (or so) or you intend to run in parallel.
Example FTS explain plan:
SQL> explain plan for select * from dual;

Query Plan
-----------------------------------------
SELECT STATEMENT     [CHOOSE] Cost=
  TABLE ACCESS FULL DUAL

Index lookup
Data is accessed by looking up key values in an index and returning rowids. A rowid uniquely identifies an individual row in a particular data block. This block is read via single block i/o.
In this example an index is used to find the relevant row(s) and then the table is accessed to lookup the ename column (which is not included in the index):
SQL> explain plan for
select empno,ename from emp where empno=10;

Query Plan

------------------------------------
SELECT STATEMENT [CHOOSE] Cost=1
TABLE ACCESS BY ROWID EMP [ANALYZED]
    INDEX UNIQUE SCAN EMP_I1

Notice the 'TABLE ACCESS BY ROWID' section. This indicates that the table data is not being accessed via a FTS operation but rather by a rowid lookup. In this case the rowid has been produced by looking up values in the index first. The index is being accessed by an 'INDEX UNIQUE SCAN' operation. This is explained below. The index name in this case is EMP_I1. If all the required data resides in the index then a table lookup may be unnecessary and all you will see is an index access with no table access.
In the following example all the columns (empno) are in the index. Notice that no table access takes place:
SQL> explain plan for
select empno from emp where empno=10;


Query Plan
------------------------------------
SELECT STATEMENT [CHOOSE] Cost=1
  INDEX UNIQUE SCAN EMP_I1

Indexes are presorted so sorting may be unecessary if the sort order required is the same as the index.
SQL> explain plan for select empno,ename from emp
where empno > 7876 order by empno;

Query Plan
-------------------------------------------------------------
SELECT STATEMENT   [CHOOSE] Cost=1
TABLE ACCESS BY ROWID EMP [ANALYZED]
  INDEX RANGE SCAN EMP_I1 [ANALYZED]


In this case the index is sorted so ther rows will be returned in the order of the index hence a sort is unecessary.
SQL> explain plan for
select /*+ Full(emp) */ empno,ename from emp
where empno> 7876 order by empno;

Query Plan
-------------------------------------------------------------
SELECT STATEMENT   [CHOOSE] Cost=9
  SORT ORDER BY
    TABLE ACCESS FULL EMP [ANALYZED]  Cost=1 Card=2 Bytes=66

Because we have forced a FTS the data is unsorted and so we must sort the data
after it has been retrieved.

There are 4 methods of index lookup:
  • index unique scan
  • index range scan
  • index full scan
  • index fast full scan
Index unique scan
Method for looking up a single key value via a unique index. Always returns a single value You must supply AT LEAST the leading column of the index to access data via the index, However this may return > 1 row as the uniqueness will not be guaranteed.
SQL> explain plan for
select empno,ename from emp where empno=10;

Query Plan
------------------------------------
SELECT STATEMENT [CHOOSE] Cost=1
TABLE ACCESS BY ROWID EMP [ANALYZED]
    INDEX UNIQUE SCAN EMP_I1

Index range scan
Method for accessing multiple column values You must supply AT LEAST the leading column of the index to access data via the index Can be used for range operations (e.g. > < <> >= <= between)
SQL> explain plan for select empno,ename from emp
where empno > 7876 order by empno;


Query Plan
-------------------------------------------------------
SELECT STATEMENT   [CHOOSE] Cost=1
TABLE ACCESS BY ROWID EMP [ANALYZED]
  INDEX RANGE SCAN EMP_I1 [ANALYZED]

A non-unique index may return multiple values for the predicate col1 = 5 and will use an index range scan
SQL> explain plan for select mgr from emp where mgr = 5

Query plan
--------------------
SELECT STATEMENT [CHOOSE] Cost=1
  INDEX RANGE SCAN EMP_I2 [ANALYZED]

Index Full Scan
In certain circumstances it is possible for the whole index to be scanned as opposed to a range scan (i.e. where no constraining predicates are provided for a table). Full index scans are  only available in the CBO as otherwise we are unable to determine whether a full scan would be a good idea or not. We choose an index Full Scan when we have statistics that indicate that it is going to be more efficient than a Full table scan and a sort.

For example we may do a Full index scan when we do an unbounded scan of an index and want the data to be ordered in the index order. The optimizer may decide that selecting all the information from the index and not sorting is more efficient than doing a FTS or a Fast Full Index Scan and then sorting.
An Index full scan will perform single block i/o's and so it may prove to be inefficient. Index BE_IX is a concatenated index on big_emp (empno,ename)
SQL> explain plan for select empno,ename
     from big_emp order by empno,ename;
Query Plan
------------------------------------------------------------
SELECT STATEMENT   [CHOOSE] Cost=26
  INDEX FULL SCAN BE_IX [ANALYZED]

Index Fast Full Scan
Scans all the block in the index Rows are not returned in sorted order Introduced in 7.3 and requires V733_PLANS_ENABLED=TRUE and CBO may be hinted using INDEX_FFS hint uses multiblock i/o can be executed in parallel can be used to access second column of concatenated indexes. This is because we are selecting all of the index.
Note that INDEX FAST FULL SCAN is the mechinism behind fast index create and recreate. Index BE_IX is a concatenated index on big_emp (empno,ename)
SQL> explain plan for select empno,ename from big_emp;

Query Plan
------------------------------------------
SELECT STATEMENT   [CHOOSE] Cost=1
  INDEX FAST FULL SCAN BE_IX [ANALYZED]

Selecting the 2nd column of concatenated index:
SQL> explain plan for select ename from big_emp;

Query Plan
------------------------------------------
SELECT STATEMENT   [CHOOSE] Cost=1
  INDEX FAST FULL SCAN BE_IX [ANALYZED]

Rowid
This is the quickest access method available Oracle simply retrieves the block specified and extracts the rows it is interested in. Most frequently seen in explain plans as Table access by Rowid
SQL> explain plan for select * from dept where rowid = ':x';

Query Plan
------------------------------------
SELECT STATEMENT [CHOOSE] Cost=1
TABLE ACCESS BY ROWID DEPT [ANALYZED]
Table is accessed by rowid following index lookup:

SQL> explain plan for
select empno,ename from emp where empno=10;


Query Plan
------------------------------------
SELECT STATEMENT [CHOOSE] Cost=1
TABLE ACCESS BY ROWID EMP [ANALYZED]
    INDEX UNIQUE SCAN EMP_I1

Joins
A Join is a predicate that attempts to combine 2 row sources We only ever join 2 row sources together Join steps are always performed serially even though underlying row sources may have been accessed in parallel. Join order - order in which joins are performed
The join order makes a significant difference to the way in which the query is executed. By accessing particular row sources first, certain predicates may be satisfied that are not satisfied by with other join orders. This may prevent certain access paths from being taken.

Suppose there is a concatenated index on A(a.col1,a.col2). Note that a.col1 is the leading column. Consider the following query:
select A.col4
from   A,B,C
where  B.col3 = 10
and    A.col1 = B.col1
and    A.col2 = C.col2
and    C.col3 = 5
We could represent the joins present in the query using the following schematic:
  B     <---> A <--->    C
col3=10                col3=5

There are really only 2 ways we can drive the query: via B.col3 or C.col3. We would have to do a Full scan of A to be able to drive off it. This is unlikely to be efficient with large tables;
If we drive off table B, using predicate B.col3=10 (as a filter or lookup key) then we will retrieve the value for B.col1 and join to A.col1. Because we have now filled the leading column of the concatenated index on table A we can use this index to give us values for A.col2 and join to A.

However if we drive of table c, then we only get a value for a.col2 and since this is a trailing column of a concatenated index and the leading column has not been supplied at this point, we cannot use the index on a to lookup the data.
So it is likely that the best join order will be B A C. The CBO will obviously use costs to establish whether the individual access paths are a good idea or not.
If the CBO does not choose this join order then we can hint it by changing the from
clause to read:

from B,A,C
and using the /*+ ordered */ hint. The resultant query would be:
select /*+ ordered */ A.col4
from   B,A,C
where  B.col3 = 10
and    A.col1 = B.col1
and    A.col2 = C.col2
and    C.col3 = 5

Join Types
  • Sort Merge Join (SMJ)
  • Nested Loops (NL)
  • Hash Join
Sort Merge Join
Rows are produced by Row Source 1 and are then sorted Rows from Row Source 2 are then produced and sorted by the same sort key as Row Source 1. Row Source 1 and 2 are NOT accessed concurrently Sorted rows from both sides are then merged together (joined)
                   MERGE
                 /      \
            SORT        SORT
             |             |
        Row Source 1  Row Source 2
If the row sources are already (known to be) sorted then the sort operation is unecessary as long as both 'sides' are sorted using the same key. Presorted row sources include indexed columns and row sources that have already been sorted in earlier steps. Although the merge of the 2 row sources is handled serially, the row sources could be accessed in parallel.
SQL> explain plan for
select /*+ ordered */ e.deptno,d.deptno
from emp e,dept d
where e.deptno = d.deptno
order by e.deptno,d.deptno;

Query Plan
-------------------------------------
SELECT STATEMENT [CHOOSE] Cost=17
  MERGE JOIN
    SORT JOIN
      TABLE ACCESS FULL EMP [ANALYZED]
    SORT JOIN
      TABLE ACCESS FULL DEPT [ANALYZED]

Sorting is an expensive operation, especially with large tables. Because of this, SMJ is often not a particularly efficient join method.

Nested Loops
First we return all the rows from row source 1 Then we probe row source 2 once for each row returned from row source 1
Row source 1
~~~~~~~~~~~~
Row 1 --------------       -- Probe ->       Row source 2
Row 2 --------------       -- Probe ->       Row source 2
Row 3 --------------       -- Probe ->       Row source 2
Row source 1 is known as the outer table
Row source 2 is known as the inner table

Accessing row source 2 is known a probing the inner table For nested loops to be efficient it is important that the first row source returns as few rows as possible as this directly controls the number of probes of the second row source. Also it helps if the access method for row source 2 is efficient as this operation is being repeated once for every row returned by row source 1.
SQL> explain plan for
select a.dname,b.sql
from dept a,emp b
where a.deptno = b.deptno;
Query Plan
-------------------------

SELECT STATEMENT [CHOOSE] Cost=5
  NESTED LOOPS
    TABLE ACCESS FULL DEPT [ANALYZED]
    TABLE ACCESS FULL EMP [ANALYZED]

Hash Join
New join type introduced in 7.3 More efficient in theory than NL & SMJ Only accessible via the CBO Smallest row source is chosen and used to build a hash table and a bitmap The second row source is hashed and checked against the hash table looking for joins. The bitmap is used as a quick lookup to check if rows are in the hash table and are especially useful when the hash table is too large to fit in memory.
SQL> explain plan for select /*+ use_hash(emp) */ empno
from emp,dept where emp.deptno = dept.deptno;

Query Plan
---------------------------------
SELECT STATEMENT  [CHOOSE] Cost=3
  HASH JOIN
    TABLE ACCESS FULL DEPT
    TABLE ACCESS FULL EMP

Hash joins are enabled by the parameter HASH_JOIN_ENABLED=TRUE in the init.ora or session. TRUE is the default in 7.3 

Cartesian Product
A Cartesian Product is done where they are no join conditions between 2 row sources and there is no alternative method of accessing the data Not really a join as such as there is no join! Typically this is caused by a coding mistake where a join has been left out. It can be useful in some circumstances - Star joins uses cartesian products.
Notice that there is no join between the 2 tables:
SQL> explain plan for
select emp.deptno,dept,deptno
from emp,dept

Query Plan
------------------------------
SLECT STATEMENT [CHOOSE] Cost=5
  MERGE JOIN CARTESIAN
    TABLE ACCESS FULL DEPT
    SORT JOIN
      TABLE ACCESS FULL EMP
The CARTESIAN keyword indicate that we are doing a cartesian product.

Operations
Operations that show up in explain plans
  • sort
  • filter
  • view
Sorts
There are a number of different operations that promote sorts
  • order by clauses
  • group by
  • sort merge join
Note that if the row source is already appropriately sorted then no sorting is required. This is now indicated in 7.3:
SORT GROUP BY NOSORT
     INDEX FULL SCAN .....

In this case the group by operation simply groups the rows it does not do the sort operation as this has already been completed.
Sorts are expensive operations especially on large tables where the rows do not fit in memory and spill to disk. By default sort blocks are placed into the buffer cache. This may result in aging out of other blocks that may be reread by other processes. To avoid this you can use the parameter <Parameter:SORT_DIRECT_WRITES> which does not place sort blocks into the buffer cache.

Filter
Has a number of different meanings used to indicate partition elimination may also indicate an actual filter step where one row source is filtering another functions such as min may introduce filter steps into query plans
In this example there are 2 filter steps. The first is effectively like a NL except that it stops when it gets something that it doesn't like (i.e. a bounded NL). This is there because of the not in. The second is filtering out the min value:
SQL> explain plan for select * from emp
     where empno not in (select min(empno)
     from big_emp group by empno);

Query Plan
------------------
SELECT STATEMENT [CHOOSE]  Cost=1
  FILTER     **** This is like a bounded nested loops
    TABLE ACCESS FULL EMP [ANALYZED]
     FILTER   **** This filter is introduced by the min
        SORT GROUP BY NOSORT
          INDEX FULL SCAN BE_IX

This example is also interesting in that it has a NOSORT function. The group by does not need to sort because the index row source is already pre sorted.

Views
When a view cannot be merged into the main query you will often see a projection view operation. This indicates that the 'view' will be selected from directly as opposed to being broken down into joins on the base tables. A number of constructs make a view non mergeable. Inline views are also non mergeable.
In the following example the select contains an inline view which cannot be merged:
SQL> explain plan for
select ename,tot
from emp,
    (select empno,sum(empno) tot from big_emp group by empno) tmp
where emp.empno = tmp.empno;

Query Plan
------------------------
SELECT STATEMENT [CHOOSE]
  HASH JOIN
    TABLE ACCESS FULL EMP [ANALYZED]
    VIEW
      SORT GROUP BY
        INDEX FULL SCAN BE_IX

In this case the inline view tmp which contains an aggregate function cannot be merged into the main query. The explain plan shows this as a view step.

Partition Views
Allows a large table to be broken up into a number of smaller partitions which can be queried much more quickly than the table as a whole a union all view is built over the top to provide the original functionality Check constraints or where clauses provide partition elimination capabilities
SQL> explain plan for
select /*+ use_nl(p1,kbwyv1) ordered */  sum(prc_pd)
from parent1 p1,  kbwyv1
where p1.class = 22
and   kbwyv1.bitm_numb = p1.bitm_numb
and   kbwyv1.year = 1997
and   kbwyv1.week between 32 and 33 ;

Query Plan
-----------------------------------------
SELECT STATEMENT   [FIRST_ROWS] Cost=1780
  SORT AGGREGATE
    NESTED LOOPS   [:Q65001] Ct=1780 Cd=40 Bt=3120
      TABLE ACCESS FULL PARENT1 [:Q65000] [AN] Ct=20 Cd=40 Bt=1040
      VIEW  KBWYV1 [:Q65001]
        UNION-ALL PARTITION  [:Q65001]
          FILTER   [:Q64000]
            TABLE ACCESS FULL KBWYT1 [AN] Ct=11 Cd=2000 Bt=104000
          TABLE ACCESS FULL KBWYT2 [AN] Ct=11 Cd=2000 Bt=104000
          TABLE ACCESS FULL KBWYT3 [AN] Ct=11 Cd=2000 Bt=104000
          FILTER   [:Q61000]
            TABLE ACCESS FULL KBWYT4 [AN] Ct=11 Cd=2000 Bt=104000

KBWYV1 is a view on 4 tables KBWYT1-4. KBWYT1-4 contain rows for week 31-34 respectively and are maintained by check constraints. This query should only return rows from partions 2 & 3. The filter operation indicates this. Partitions 1 & 4 are eliminated at execution time. The view line indicates that the view is not merged. The union-all partion information indicates that we have recognised this as a partition view. Note that the tables can be accessed in parallel.

Remote Queries
Only shows remote in the OPERATION column OTHER column shows query executed on remote node OTHER_NODE shows where it is executed Different operational characteristics for RBO & CBO
RBO - Drags everything across the link and joins locally
CBO - Uses cost estimates to determine whether to execute remotely or locally
SQL>  explain plan for
select *
from dept@loop_link;

Query Plan
-------------------------------------------------------
SELECT STATEMENT REMOTE  [CHOOSE] Cost=1
  TABLE ACCESS FULL DEPT [SJD.WORLD] [ANALYZED]

In this case the whole query has been sent to the remote site. The other column shows nothing.
SQL> explain plan for
select a.dname,avg(b.sal),max(b.sal)
from dept@loop_link a, emp b
where a.deptno=b.deptno
group by a.dname
order by max(b.sal),avg(b.sal) desc;

Query Plan
-----------------------------------------------------
SELECT STATEMENT   [CHOOSE] Cost=20
  SORT ORDER BY  [:Q137003] [PARALLEL_TO_SERIAL]
    SORT GROUP BY  [:Q137002] [PARALLEL_TO_PARALLEL]
      NESTED LOOPS   [:Q137001] [PARALLEL_TO_PARALLEL]
        REMOTE   [:Q137000] [PARALLEL_FROM_SERIAL]
        TABLE ACCESS FULL EMP [:Q137001] [ANALYZED]
        [PARALLEL_COMBINED_WITH_PARENT]

Bind Variables
Bind variables are recommended in most cases because they promote sharing of sql code
At parse time the parser has NO IDEA what the bind variable contains. With RBO this makes no difference but with CBO, which relies on accurate statistics to produce plans, this can be a problem.

Defining bind variables in sqlplus:
variable x varchar2(18);
assigning values:
begin
:x := 'hello';
end;
/
SQL> explain plan for
select *
from dept
where rowid = ':x';

Query Plan
------------------------------------
SELECT STATEMENT [CHOOSE] Cost=1
  TABLE ACCESS BY ROWID DEPT [ANALYZED]

Parallel Query

Main indicators that a query is using PQO:
  • [:Q1000004] entries in the explain plan
  • Checkout the other column for details of what the slaves are executing
  • v$pq_slave will show any parallel activity
Columns to look in for information
  • other - contains the query passed to the slaves
  • other_tag - describes the contents of other
  • object_node - indicates order of pqo slaves
Parallel Query operates on a producer/consumer basis. When you specify parallel degree 4 oracle tries to allocate 4 producer slaves and 4 consumer slaves. The producers can feed any of the consumers. If there are only 2 slaves available then we use these. If there is only 1 slave available then we go serial If there are none available then we use serial. If parallel_min_percent is set then we error ora 12827 instead of using a lower number of slaves or going serial.

Consumer processes typically perform a sorting function. If there is no requirement for the data to be sorted then the consumer slaves are not produced and we end up with the number of slaves used matching the degree of parallelism as opposed to being 2x the degree.

Parallel Terms
PARALLEL_FROM_SERIALThis means that source of the data is serial but it is passed to a parallel consumer
PARALLEL_TO_PARALLELBoth the consumer and the producer are  parallel
PARALLEL_COMBINED_WITH_PARENTThis operation has been combined with the parent operator. For example in a sort merge join the sort operations would be shown as PARALLEL_COMBINED_WITH_PARENT because the sort and the merge are handled as 1 operation.
PARALELL_TO_SERIALThe source of the data is parallel but it is passed to a serial consumer. This typically will happen at the top of the explain plan but could occur anywhere
Examples of parallel queries
Assumptions
OPTIMIZER_MODE = CHOOSE
DEPT is small compared to EMP
DEPT has an index (DEPT_INDX) on deptno column
Three examples are presented
Query #1:  Serial
Query #2:  Parallel
Query #3:  Parallel, with forced optimization to RULE and forced usage of DEPT_INDX

Sample Query #1 (Serial)
select A.dname, avg(B.sal), max(B.sal)
from  dept A, emp B
where A.deptno = B.deptno
group by A.dname
order by max(B.sal), avg(B.sal) desc;

Execution Plan #1 (Serial)
OBJECT_NAME                      OBJECT_NODE OTHER
-------------------------------  ----------- -------
SELECT STATEMENT
 SORT ORDER BY
   SORT GROUP BY
     MERGE JOIN
       SORT JOIN
         TABLE ACCESS FULL emp
       SORT JOIN
         TABLE ACCESS FULL dept

Notice that the object_node and other columns are empty
Sample Query #2 (Query #1 with parallel hints)

select /*+ parallel(B,4) parallel(A,4) */
A.dname, avg(B.sal), max(B.sal)
from  dept A, emp B
where A.deptno = B.deptno
group by A.dname
order by max(B.sal), avg(B.sal) desc;

Execution Plan #2  (Parallel)
OBJECT_NAME                      OBJECT_NODE OTHER
-------------------------------  ----------- -------
SELECT STATEMENT      Cost = ??
 SORT ORDER BY                   :Q55004     **[7]**
   SORT GROUP BY                 :Q55003     **[6]**
     MERGE JOIN                  :Q55002     **[5]**
       SORT JOIN                 :Q55002     **[4]**
         TABLE ACCESS FULL emp   :Q55001     **[2]**
       SORT JOIN                 :Q55002     **[3]**
         TABLE ACCESS FULL dept  :Q55000     **[1]**
Execution Plan #2  -- OTHER column
**[1]**  (:Q55000) "PARALLEL_FROM_SERIAL"
Serial execution of SELECT DEPTNO, DNAME FROM DEPT
**[2]**  (:Q55001) "PARALLEL_TO_PARALLEL"
        SELECT /*+ ROWID(A1)*/
        A1."DEPTNO" C0, A1."SAL" C1
        FROM "EMP" A1
        WHERE ROWID BETWEEN :1 AND :2
**[3]**  (:Q55002) "PARALLEL_COMBINED_WITH_PARENT"
**[4]**  (:Q55002) "PARALLEL_COMBINED_WITH_PARENT"
**[5]**  (:Q55002) "PARALLEL_TO_PARALLEL"
        SELECT /*+ ORDERED USE_MERGE(A2)*/
        A2.C1 C0, A1.C1 C1
        FROM :Q55001 A1,:Q55000 A2
        WHERE A1.C0=A2.C0
**[6]**  (:Q55003) "PARALLEL_TO_PARALLEL"
        SELECT MAX(A1.C1) C0, AVG(A1.C1) C1, A1.C0 C2
        FROM :Q55002 A1
        GROUP BY A1.C0
**[7]**  (:Q55004) "PARALLEL_FROM_SERIAL"
        SELECT A1.C0 C0, A1.C1 C1, A1.C2 C2
        FROM :Q55003 A1
        ORDER BY A1.CO, A1.C1 DESC

Sample Query #3 (Query #2 with fudged hints)
select /*+ index(A dept_indx) parallel(B,4) parallel(A,4) */
      A.dname, avg(B.sal), max(B.sal)
from  dept A, emp B
where A.deptno = B.deptno
group by A.dname
order by max(B.sal), avg(B.sal) desc;

Execution Plan #3  (Parallel)
OBJECT_NAME                         OBJECT_NODE OTHER
----------------------------------- ----------- -------
SELECT STATEMENT          Cost = ??
 SORT ORDER BY                      :Q58002     **[6]**
   SORT GROUP BY                    :Q58001     **[5]**
     NESTED LOOPS JOIN              :Q58000     **[4]**
       TABLE ACCESS FULL emp        :Q58000     **[3]**
       TABLE ACCESS BY ROWID dept   :Q58000     **[2]**
         INDEX RANGE SCAN dept_indx :Q58000     **[1]**
Execution Plan #3  -- OTHER column
**[1]**  (:Q58000) "PARALLEL_COMBINED_WITH_PARENT"
**[2]**  (:Q58000) "PARALLEL_COMBINED_WITH_PARENT"
**[3]**  (:Q58000) "PARALLEL_COMBINED_WITH_PARENT"
**[4]**  (:Q58000) "PARALLEL_TO_PARALLEL"
        SELECT /*+ ORDERED USE_NL(A2) INDEX(A2) */
        A2."DNAME" C0, A1.C0 C1
        FROM
          (SELECT /*+ ROWID(A3) */
           A3."SAL" CO, A3."DEPTNO" C1
           FROM "EMP" A3
           WHERE ROWID BETWEEN :1 AND :2) A1,
          "DEPT" A2
        WHERE A2."DEPTNO" = A1.C1
**[5]**  (:Q58001) "PARALLEL_TO_PARALLEL"
        SELECT MAX(A1.C1) C0, AVG(A1.C1) C1, A1.C0 C2
        FROM :Q58000 A1
        GROUP BY A1.C0
**[6]**  (:Q58002) "PARALLEL_TO_SERIAL"
        SELECT A1.C0 C0, A1.C1 C1, A1.C2 C2
        FROM :Q58001 A1
        ORDER BY A1.C0, A1.C1 DESC


The following is a sample execution plan.

SQL> explain plan for
  2  select e.empno, e.ename, d.dname
  3  from emp e, dept d
  4  where e.deptno = d.deptno
  5  and e.deptno = 10;
 Explained.

SQL> SELECT * FROM table(dbms_xplan.display(null,null,'basic'));
 PLAN_TABLE_OUTPUT
------------------------------------------------------------
Plan hash value: 568005898
------------------------------------------------------------
| Id  | Operation                    | Name    |
------------------------------------------------------------
|   0 | SELECT STATEMENT                         |         |
|   1 |  NESTED LOOPS                                  |         |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |
|   3 |    INDEX UNIQUE SCAN                     | PK_DEPT |
|   4 |   TABLE ACCESS FULL                       | EMP     |
------------------------------------------------
Using the rules above, you could say;

Operation 0 is the root of the tree; it has one child, Operation 1
Operation 1 has two children, which is Operation 2 and 4
Operation 2 has one child, which is Operation 3

Below is the graphical representation of the execution plan. If you read the tree; In order to perform Operation 1, you need to perform Operation 2 and 4. Operation 2 comes first; In order to perform 2, you need to perform its Child Operation 3. In order to perform Operation 4, you need to perform Operation 2

         Operation 0
      (SELECT STATEMENT)
             |
             |             |
         Operation 1
        (NESTED LOOPS)
                   /  \
                /         \
             /               \
           /                    \
        /                          \
      /                               \
 Operation 2                    Operation 4
(TABLE ACCESS          (TABLE ACCESS FULL)
BY INDEX ROWID)
     |
     |
     |
 Operation 3
(INDEX UNIQUE SCAN)
Operation 3 accesses DEPT table using INDEX UNIQUE SCAN and passes the ROWID to Operation 2
Operation 2 returns all the rows from DEPT table to Operation 1
Operation 1 performs Operation 4 for each row returned by Operation 2
Operation 4 performs a full table scan (TABLE ACCESS FULL) scan and applies the filter E.DEPTNO=10 and returns the rows to Operation 1
Operation 1 returns the final results to Operation 0


Reference :: click here 

Enjoy      :)