Apr 26, 2014

Common ORA - errors - related to DBA

Common 'ORA-' errors found in PROD envs:

ORA-12154: TNS:could not resolve the connect identifier specified

Cause : This error is, almost appropriately, often the very first one you get on trying to establish a connection to your database. Simply put, it means that you have named the database you wish to be connected to and Oracle doesn’t know who the heck you’re talking about. This error is often caused by typos, maybe in your connection string, but possibly in your tnsnames.ora file. It is also possible that the tnsnames.ora file is not accessible or does not even exist. If it does, ensure that it contains the service name you are using; also go over it with a fine toothcomb to make sure it doesn’t have any unpaired parentheses or such.

Action : Cross Check tns entry, Check firwall access to connecting server

ORA-00600: internal error code, arguments: [%s], [%s],[%s], [%s], [%s]

Cause : This is a generic error; it means something serious has gone wrong and you are going to need to roll up your sleeves, dig in, and find out what. But you are not without clues. Your alert.log file will contain the path to your trace file. You will want to look in it, as a record is written to the trace file every time an ORA-00600 error occurs.
Take the information you get from your trace file and the first argument in the square brackets (the internal message number), and head over to My Oracle Support (Metalink). There you will find a handy ORA-0600 lookup tool (Note 153788.1) that will direct you to additional information that will help you solve your problem.

Action : Check with Oracle support as per your version and arguments

Note: There are lots of reason for ORA-00600, Based of arguments and oracle document you can fix it. So may bugs are described in oracle document for ORA-00600.

ORA-03113: end-of-file on communication channel

This error pretty much means that your connection has clutched its chest and died. For some reason, your client machine and the database server are acting like an old married couple and have stopped talking to each other. That reason could be one of a rather long list: has your server crashed? has someone pulled out your network cable? was your process killed at the O/S level? is your Windows log full? or maybe there is an Oracle internal error?

Do not overlook the obvious. This error is sometimes caused by the simplest of things. If, however, it is caused by an Oracle internal error, look to your alert log for further information.

ORA-01555 Snapshot Too Old

The ORA-01555 is caused by Oracle read consistency mechanism.  If you have a long running SQL that starts at 10:30 AM, Oracle ensures that all rows are as they appeared at 10:30 AM, even if the query runs until noon! Oracles does this by reading the "before image" of changed rows from the online undo segments.  If you have lots of updates, long running SQL and too small UNDO, the ORA-01555 error will appear.

From the docs we see that the ORA-01555 error relates to insufficient undo storage or a too small value for the undo_retention parameter:

ORA-01555: snapshot too old: rollback segment number string with name "string" too small

Cause: Rollback records needed by a reader for consistent read are overwritten by other writers.

Action: If in Automatic Undo Management mode, increase the setting of UNDO_RETENTION. Otherwise, use larger rollback segments.

Click here to read : how to set optimal value for undo retention and undo tablespace size

If you are facing following error for the below query, then do the my suggested action plan. I rectified the issue in same way.

ERROR at line ..:
ORA-01555: snapshot too old: rollback segment number 0 with name "SYSTEM" too
small ...


Query to execute to find issued tablespace:

select * from(
SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
, greatest(e.block_id, c.block#) corr_start_block#
 , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
 , least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
 - greatest(e.block_id, c.block#) + 1 blocks_corrupted
 , null description
 FROM dba_extents e, v$database_block_corruption c
 WHERE e.file_id = c.file#
 AND e.block_id <= c.block# + c.blocks - 1
 AND e.block_id + e.blocks - 1 >= c.block#
 UNION
 SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
 , header_block corr_start_block#
 , header_block corr_end_block#
 , 1 blocks_corrupted
 , 'Segment Header' description
 FROM dba_segments s, v$database_block_corruption c
 WHERE s.header_file = c.file#
 AND s.segment_name is NOT NULL
 AND s.header_block between c.block# and c.block# + c.blocks - 1
 UNION
 SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
 , greatest(f.block_id, c.block#) corr_start_block#
 , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
 , least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
 - greatest(f.block_id, c.block#) + 1 blocks_corrupted
 , 'Free Block' description
 FROM dba_free_space f, v$database_block_corruption c
 WHERE f.file_id = c.file#
 AND f.block_id <= c.block# + c.blocks - 1
 AND f.block_id + f.blocks - 1 >= c.block#
 order by file#, corr_start_block#) t where segment_name IS NOT NULL; 


Solution with workaround:
Force an extension of the SYSTEM rollback segment to occur using the following :

connect / as sysdba
alter system set undo_management = MANUAL scope=spfile ;
shutdown immediate ;
startup ;

create table sample (c1 number, c2 varchar2(10));
 

begin
for i in 1.. 400000 loop
insert into sample values (i,'ABCDEFGH');
end loop;
end;
/

delete sample;
commit;

alter system set undo_management = AUTO scope=spfile;

shutdown immediate
startup 


Then monitor the database again.
ORA-07445 - exception encountered: core dump

Cause : An operating system exception occurred which should result in the creation of a core file. This is an internal error.

Action : Contact Oracle Customer Support.

Common precipitators of the ORA-07445 include:
-- High volume user transactions
-- Software bugs (i.e. Bug 4098853).  See note 342443.1 on MOSC.
-- Too-small RAM regions (shared_pool_size, java pool, large_pool_size), and a too-small application memory stack (e.g. PL/SQL array too small)
-- Too small undo and temp segments
-- Program errors (addressing outside of RAM region), e.g. S0C4. Improper NLS parameter settings
-- Hardware errors
-- Oracle block corruption and a host of other related issues.
-- When Oracle internal job failed with specific exception

Note: There are lots of reason for ORA-07445, Based of arguments and oracle document you can fix it. So may bugs are described in oracle document for ORA-07445.

ORA-01652: unable to extend temp segment by string in tablespace string 

Cause: Failed to allocate an extent of the required number of blocks for a temporary segment in the tablespace indicated.

Action: Use ALTER TABLESPACE ADD TEMPFILE statement to add one or more files to the tablespace indicated.

ORA-00257: archiver error. Connect internal only, until freed. 

Cause: The archiver process received an error while trying to archive a redo log. If the problem is not resolved soon, the database will stop executing transactions. The most likely cause of this message is the destination device is out of space to store the redo log file.

Action: Check archiver trace file for a detailed description of the problem. Also verify that the device specified in the initialization parameter ARCHIVE_LOG_DEST is set up properly for archiving.

ORA-01031: insufficient privileges 

Cause: An attempt was made to change the current username or password without the appropriate privilege. This error also occurs if attempting to install a database without the necessary operating system privileges. This error may occur if the user was granted the necessary privilege at a higher label than the current login.

Action: Ask the database administrator to perform the operation or grant the required privileges. For Trusted Oracle users getting this error although granted the the appropriate privilege at a higher label, ask the database administrator to regrant the privilege at the appropriate label.

For the DBA, the ORA-01031 can happen if the target OS executables do not have read and execute permissions (e.g. (770) in UNIX/Linux), and ensure that the oracle user is a member of the dba group (e.g. /etc/group).  There are similar permission in the Windows registry.

ORA-00918: column ambiguously defined

Cause :A column name used in a join exists in more than one table and is thus referenced ambiguously. In a join, any column name that occurs in more than one of the tables must be prefixed by its table name when referenced. The column should be referenced as TABLE.COLUMN or TABLE_ALIAS.COLUMN. For example, if tables EMP and DEPT are being joined and both contain the column DEPTNO, then all references to DEPTNO should be prefixed with the table name, as in EMP.DEPTNO or E.DEPTNO.

Action: Prefix references to column names that exist in multiple tables with either the table name or a table alias and a period (.), as in the examples above.

When ORA-00918 is thrown, you have a column which has been ambiguously defined.  If a column name in a join is referenced ambiguously, it exists in multiple tables.
-- Column names which occur in multiple tables should be prefixed when it is referenced by its table name.
-- Columns must be referenced as TABLE.COLUMN or TABLE_ALIAS.COLUM .  Oracle documentation which reference ORA-00918 give the following example:
-- If tables EMP and DEPT are being joined and both contain the column DEPTNO, then all references to DEPTNO should be prefixed with the table name, as in EMP.DEPTNO or E.DEPTNO.

To correct ORA-00918, references should be prefixed to column names existing in multiple tables (either with the table name or table alias and a period)

ORA-00054: resource busy and acquire with NOWAIT specified

Cause : This error usually comes when you are changing DDL but object is currently using by other user. It is not a critical error.

Action : Redefine your object using ONLINE key word where ever possible.

ORA-04031 : Unable to allocate x bytes of shared memory

Cause :
ORA-04031 - deals with shared memory issues. It's lack of allocation of contiguous memory as and when requested by the current executing sql i.e., when process attempts to allocate a large piece of contiguous memory in the shared pool fails. 

Action:
 If the shared pool is out of memory, either use the DBMS_SHARED_ POOL package to pin large packages, reduce your use of shared memory, or increase the amount of available shared memory by increasing the value of the initialization parameters SHARED_POOL_RESERVED_SIZE and SHARED_ POOL_SIZE. If the large pool is out of memory, increase the initialization parameter LARGE_POOL_SIZE.
More ORA errors...
Click here to read more for best practice.

ORA-01442
ORA-00910
ORA-17629
ORA-16191
ORA-27069
ORA-00333
ORA-00600
ORA-22992
ORA-12018
ORA-07445
ORA-02020 

Please feel free to post comment....





Apr 12, 2014

Fix High Water Mark in Oracle Database

High Water Mark management in Oracle Database

Definition:
Each table is made up of extents and each extent is made up of oracle blocks - a common block size is 8k. So you have a table with 10 extents (80K).

You populate your table with 2 million rows of data and you will have many hundreds of extents. Now lets assume that you delete over half of the records in the table. Oracle still has the same number of extents but many of the blocks are empty. When you run a query against the table Oracle will scan through all the blocks including empty ones looking for data. So you can think of the total number of extents / blocks used as the high water mark.

Fix-1:
To fix you export the table, drop it and import it back in.  But down time must be required. Some precautions to be taken like grants, constraint enable script etc. But this is best way.


So how can we release unused space from a table?
There are a number of possible options which are already available before Oracle 10g:
- What we always could do is export and import the segment. After an import the table will have only one extent. The rows will have new physical addresses and the high watermark will be adjusted.
- Another option would be to TRUNCATE the table.
With this we would loose all rows which are in the table. So we cannot use this if we want to keep existing records.


Fix-2: Move table to different tablespace:

SQL> ALTER TABLE emp MOVE TABLESPACE users;


This statement will also cause that
- the rows will have new physical addresses and
- the high watermark will be adjusted.
But for this:
- we need a full (exclusive) table lock
- the indexes will be left with the status unusable (because they contain the old rowids) and must be rebuilt.


Fix-3: Starting with Oracle 10g/11g

we can use a new feature for adjusting the high watermark, it is called segment shrinking and is only possible for segments which use ASSM, in other words, which are located in tablespaces which use Automatic Segement Space Management. In such a tablespace a table does not really have a High watermark!
It uses two watermarks instead:


- the High High Watermark referred to as HHWM, above which all blocks ar unformatted.
- the Low High Watermark referred to as LHWM below which all blocks are formatted.


We now can have unformatted blocks in the middle of a segment!

ASSM was introduced in Oracle 9iR2 and it was made the default for tablespaces in Oracle 10gR2.
With the table shrinking feature we can get Oracle to move rows which are located in the middle or at the end of a segment further more down to the beginning of the segment and by this make the segment more compact. For this we must first allow ORACLE to change the ROWIDs of these rows by issuing


SQL> ALTER TABLE emp ENABLE ROW MOVEMENT;

ROWIDs are normally assigned to a row for the life time of the row at insert time. After we have given Oracle the permission to change the ROWIDs we can now issue a shrink statement.


SQL> ALTER TABLE emp SHRINK SPACE;

This statement will procede in two steps:
- The first step makes the segment compact by moving rows further down to free blocks at the beginning of the segment.
- The second step adjusts the high watermark. For this Oracle needs an exclusive table lock,
but for a very short moment only.


Table shrinking…
- will adjust the high watermark
- can be done online
- will cause only rowlocks during the operation and just a very short full table lock at the end of the operation
- indexes will be maintained and remain usable
- can be made in one go
- can be made in two steps
(this can be usefull if you cannot get a full table lock during certain hours: you only make the first step and adjust the high watermark later when it is more convenient:


- ALTER TABLE emp SHRINK SPACE; – only for the emp table
- ALTER TABLE emp SHRINK SPACE CASCADE; – for all dependent objects as well

- ALTER TABLE emp SHRINK SPACE COMPACT; – only makes the first step (moves the rows)


How are the indexes maintained?

In the first phase Oracle scans the segment from the back to find the position of the last row.
Afterwards it scan the segment from the beginning to find the position of the first free slot in a block in this segment. In case the two positions are the same, there is nothing to shrink. In case the two positions are different Oracle deletes nth row from the back and inserts it into the free position at front of the segment. Now Oracle scan nth segment from the back and front again and again until it finds that the two positions are the same.


Since it is DML statements performed to move the rows, the indexes are maintained at the same time. Only row level locks are used for these operations in the first pase of SHRINK TABLE statement.


The following restrictions apply to table shrinking:
1.) It is only possible in tablespaces with ASSM.
2.) You cannot shrink:
- UNDO segments
- temporary segments
- clustered tables
- tables with a colmn of datatype LONG
- LOB indexes
- IOT mapping tables and IOT overflow segments
- tables with MVIEWS with ON COMMIT
- tables with MVIEWS which are based on ROWIDs


The Oracle 10g Oracle comes with a Segment Advisor utility. The Enterprise Manager, Database Control, even has a wizard which can search for shrink candidates. This advisor is run automatically by an autotask job on a regular basis in the default maintenance window. You can use the built in package DBMS_SPACE to run the advisor manually as well.

To run manually segment advisor in Oracle 11g/11g: Click here


Caution: Be careful when you are performing these activities. Take proper backups and then do. If you are using Oracle 10g/11g then check with segment advisor notice.

It will be better to reorganize the entire tablespace if you have more tables to manage water mark.

Thanks

Apr 5, 2014

Interpreting Explain Plan in Oracle – for beginners

Interpreting/ Understanding SQL query Explain Plan in Oracle

What's an explain plan?
An explain plan is a representation of the access path that is taken when a query is executed within Oracle.

The EXPLAIN PLAN statement displays execution plans chosen by the Oracle optimizer for SELECT, UPDATE, INSERT, and DELETE statements. A statement's execution plan is the sequence of operations Oracle performs to run the statement.

The row source tree is the core of the execution plan. It shows the following information:
·       An ordering of the tables referenced by the statement
·       An access method for each table mentioned in the statement
·       A join method for tables affected by join operations in the statement
·       Data operations like filter, sort, or aggregation
In addition to the row source tree, the plan table contains information about the following:
·       Optimization, such as the cost and cardinality of each operation
·       Partitioning, such as the set of accessed partitions
·       Parallel execution, such as the distribution method of join inputs
The EXPLAIN PLAN results let you determine whether the optimizer selects a particular execution plan, such as, nested loops join. It also helps you to understand the optimizer decisions, such as why the optimizer chose a nested loops join instead of a hash join, and lets you understand the performance of a query.

What is Cost ?

The Oracle Optimizer is a cost-based optimizer. The execution plan selected for a SQL statement is just one of the many alternative execution plans considered by the Optimizer. The Optimizer selects the execution plan with the lowest cost, where cost represents the estimated resource usage for that plan. The lower the cost the more efficient the plan is expected to be. The optimizer’s cost model accounts for the IO, CPU, and network resources that will be used by the query.

Query processing can be divided into 7 phases:

1) Syntactic                   : Checks the syntax of the query
2) Semantic                   : Checks that all objects exist and are accessible
3) View Merging : Rewrites 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) Optimization  : Determines 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 Generation          : QEP = Query Evaluation Plan
7) QEP Execution            : QEP = 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 re-parsed.

Terminology
Row Source
A 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
Predicate
where clause of a query
Tuples / Cardinality
rows
Driving Table
This 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 Table
This 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

Understanding the execution plan :
In order to determine if you are looking at a good execution plan or not, you need to understand how the Optimizer determined the plan in the first place. You should also be able to look at the execution lan and assess if the Optimizer has made any mistake in its estimations or calculations, leading to a suboptimal plan. The components to assess are:

  • Carnality – Estimate of the number of rows coming out of each of the operations.
  • Access method – The way in which the data is being accessed, via either a table scan or index access.
  • Join method – The method (e.g., hash, sort-merge, etc.) used to join tables with each other.
  • Join type – The type of join (e.g., outer, anti, semi, etc.).
  • Join order – The order in which the tables are joined to each other.
  • Partition pruning – Are only the necessary partitions being accessed to answer the query?
  • Parallel Execution – In case of parallel execution, is each operation in the plan being conducted in parallel? Is the right data redistribution method being used?


Explaining with sample example:

$ sqlplus /nolog
SQL> connect test@SIT
Enter password: xxxxxx
Connected.

See the plan:

                                                (figure: sample_1)

Explain plan Hierarchy : Simple explain plan:-

The rightmost uppermost operation of an explain plan is the first thing that the explain plan will execute. In this case TABLE ACCESS FULL EMP is the first operation. This statement means we are doing a full table scan of table EMP. 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.

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 Cost=69

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.

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 )

Cardinality :

The cardinality is the estimated number of rows that will be returned by each operation. The Optimizer determines the cardinality for each operation based on a complex set of formulas that use both table and column level statistics as input (or the statistics derived by dynamic sampling). One of the simplest formulas is used when there is a single equality predicate in a single table query (with no histogram). In this case the Optimizer assumes a uniform distribution and calculates the cardinality for the query by dividing the total number of rows in the table by the number of distinct values in the column used in the where clause predicate.

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;

(Figure : sample_2)

Index look-up :

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):


( figure : sample_3 )

Notice the 'TABLE ACCESS BY INDEX 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 PK_EMPID. 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 (empid) are in the index. Notice that no table access takes place:
SQL> explain plan for
select empid,fname,deptid from emp where empno=101010;

SQL> format column fname a20;
SQL> select empid,fname,deptno from emp where empid=101010;

Sample Query Output:
     EMPID          FNAME                    DEPTNO
    ----------        --------------------           ----------
    101010          GOURANGA                   4006

Indexes are presorted so sorting may be unnecessary if the sort order required is the same as the index.

By default Oracle automatically determines the columns that need histograms based on the column
usage statistics and the presence of a data skew. If you need (or want) to create a histogram manually
you can use the following command.

SQL > Exec DBMS_STATS.GATHER_TABLE_STATS('HR','EMP');

(Figure : sort_sample_1)

In this case the index is sorted so the rows will be returned in the order of the index hence a sort is unnecessary.

Methods of Index lookup:

There are 4 methods of index lookup:
·         Index unique scan
·         Index range scan
·         Index full scan
·         Index fast full scan

Method for looking up a single key value via a unique index scan. 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. 

In certain circumstances it is possible for the whole index to be scanned as opposed to a Index range scan (i.e. where no constraining predicates are provided for a table). Index full 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.

1) Index unique scan: A "unique" scan scans for a single value in a unique index.
( see figure sample_3 )

2) Index range scan : A "range" scan starts at some starting value, and reads index entries sequentially (i,.e. along the b-tree) until it encounters a value that runs past a second value (a search for a single value on a non-unique index is a range scan.
( see  figure sort_sample_1)

3) Index full scan : An index full scan is when we read the index a block at a time - from start to finish. We'll read the root block, navigate down the left hand side of the index (or right if we are doing a descending full scan) and then when we hit the leaf block - we'll read across the entire bottom of the index - a block at a time - in sorted order. We use single block IO, not multi-block IO for this operation.

See the example:

( Figure : Index_Fullscan)

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 PK_EMPID is a concatenated.

4) Index Fast full scan : An index fast full scan reads the ENTIRE index, unsorted, as it exists on disk. It is basically using the index as a "skinny" version of the table. The query in question would only be accessing attributes in the index (we are not using the index as a way to get to the table, we are using the index INSTEAD of the table) We use multi-block I/O and read all of the leaf, branch and the root block. We ignore the branch and root blocks and just process the (un-ordered) data on the leaf blocks.


See the example:

( Figure : index_fastscan )


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 CIDX_IDNAME is a concatenated index on emp (empid,fname).

Facts related to index scan : Use of Index may not perform the query well...

Even if the schemas are the same, the optimizer can choose different execution plans if the costs are different. Some factors that affect the costs include the following:
     - Data volume and statistics
     - Bind variable types and values
     - Initialization parameters - set globally or at session level

Looking Beyond Execution Plans

       The execution plan operation alone cannot differentiate between well-tuned statements and those that perform poorly. For example, an EXPLAIN PLAN output that shows that a statement uses an index does not necessarily mean that the statement runs efficiently. Sometimes indexes can be extremely inefficient. In this case, you should examine the following:
The columns of the index being used 

Their selectivity (fraction of table being accessed)

       It is best to use EXPLAIN PLAN to determine an access plan, and then later prove that it is the optimal plan through testing. When evaluating a plan, examine the statement's actual resource consumption.

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';

See the figure : access_by_rowid.





( Figure : access_by_rowid)

Table is accessed by rowid following index lookup: ( See figure  rowid_with_index_uniquescan)


( Figure : rowid_with_index_uniquescan )


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.

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; 

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.

See the plan for below example:
select b.fname,a.name,b.salary
from dept a
inner join emp b on a.deptno = b.deptno
where b.deptno=:b1;

( Figure : nested_loop_plan)

Hash Join :

This join is 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.

In cases where a very small table is being joined to a large table, the Oracle hash join will often dramatically speed-up the query. Hash joins are far faster than nested loop joins in certain cases, often in cases where your SQL is joining a large table to a small table.  

However, in a production database with very large tables, it is not always easy to get your database to invoke hash joins without increasing the RAM regions that control hash joins.  For large tables, hash joins requires lots of RAM.


Oracle places limits of the amount of RAM available for hash joins so that no single session will "hog" all of the RAM.  According to this research using Oracle 10g, the hash join maximum is only to only 200 megabytes (5% of pga_aggregate_target). 

The Oracle DBA controls the optimizers' propensity to invoke hash joins because the DBA must allocate the RAM resources to Oracle (using the hash_area_size and pga_aggregate_target parameters) for the optimizer to choose a hash join.  The CBO will only choose a hash join if you have allocated Oracle enough RAM area in which to perform the hash join.

In the above example, when we remove conditions from where condition, it is going for hash join.

See the below example to show hash join.

Explain plan for
select b.fname,a.name,b.salary
from dept a
inner join emp b on a.deptno = b.deptno;


( Figure : hash_join_plan)

Caution:
Unfortunately, the Oracle hash join is more memory intensive than a nested loop join. To be faster than a nested loop join, we must set the hash_area_size large enough to hold the entire hash table in memory (about 1.6 times the sum of the rows in the table).

If the Oracle hash join overflows the hash_area_size memory, the hash join will page into the TEMP tablespace, severely degrading the performance of the hash join. You can use the following script, to dynamically allocate the proper hash_area_size for your SQL query in terms of the size of your hash join driving table.

Fundamental difference between hash and nested loop joins :

Basically the most fundamental (or biggest or most important) difference between nested loop and hash joins is that "Hash joins can not look up rows from the inner (probed) row source based on values retrieved from the outer (driving) row source, nested loops can".

In other words, when joining table A and B (A is driving table, B is the probed table), then a nested loop join can take 1st row from A and perform a lookup to B using that value (of the column(s) you join by). Then nested loop takes the next row from A and performs another lookup to table B using the new value. And so on and so on and so on.

This opens up additional access paths to the table B, for example when joining ORDERS and ORDER_ITEMS by ORDER_ID (and ORDER_ID is leading column of PK in ORDER_ITEMS table), then for whatever orders are taken from ORDERS table, we can perform a focused, narrow index range scan on ORDER_ITEMS for every ORDER_ID retrieved from the driving ORDERS table. A hash join can’t do that.

Of course this doesn’t mean that hash joins can’t use any indexes for tables they read – index range scans and unique lookups can still be used under a hash join, but only if there are constant values in the query text (in form of literal or bind variables). If there are no such constant (filter) conditions under a hash join, then the other options to use that index would be to do an INDEX FULL SCAN (which is a range scan from end to end of the index) or INDEX FAST FULL SCAN (which is like a full table scan through the entire index segment). However none of these opportunities give the same benefits as nested loops looking up rows from row source B dynamically based on what was retrieved from A during runtime.

Note that this nested loops benefit isn’t limited to indexes only on table B, the difference is more fundamental than just a specific access path. For example, if table B happens to be a single table hash cluster or indexed X$ table, then the nested loop is also able to do “optimized” lookups from these row-sources, based on the values retrieved from table A.

Summery Hash Joins and Nested Loops joins:

Hash Joins - Hash joins are used for joining large data sets. The optimizer uses the smaller of the two tables or data sources to build a hash table, based on the join key, in memory. It then scans the larger table, and performs the same hashing algorithm on the join column(s). It then probes the previously built hash table for each value and if they match, it returns a row.

Nested Loops joins - Nested loops joins are useful when small subsets of data are being joined and if there is an efficient way of accessing the second table (for example an index look up). For every row in the first table (the outer table), Oracle accesses all the rows in the second table (the inner table). Consider it like two embedded FOR loops. In Oracle Database 11g the internal implementation for nested loop joins changed to reduce overall latency for physical I/O so it is possible you will see two NESTED LOOPS joins in the operations column of the plan, where you previously only saw one on
earlier versions of Oracle

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:


(Figure : cartesian_product_plan)

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


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 re-read 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:


(Figure : filter_plan)

View:

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:

example:
explain plan for
select firstname, tot
  from employee e,
       (select empid, sum(salary) tot from emp group by empid) tmp
 where e.employeeid = tmp.empid;


( Figure : view_plan)

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.

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

example:

explain plan for
select * from
hr.employee_insurance_details@remote_dblink;


In this case the whole query has been sent to the remote site. The other column shows nothing.


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_SERIAL
This means that source of the data is serial but it is passed to a parallel consumer
PARALLEL_TO_PARALLEL
Both the consumer and the producer are  parallel
PARALLEL_COMBINED_WITH_PARENT
This 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_SERIAL
The 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
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.name,avg(B.salary),max(B.salary)
from dept A,emp B
where A.deptno = B.deptno
group by A.name
order by max(B.salary),avg(B.salary) desc;


Sample Query #2 (Query #1 with parallel hints)

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



Sample Query #3 (Query #2 with fudged hints)

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

How to obtain explain plans

Explain plan for
Main advantage is that it does not actually run the query - just parses the sql. This means that it executes quickly. In the early stages of tuning explain plan gives you an idea of the potential performance of your query without actually running it. You can then make a judgement as to any modifications you may choose to make.

Autotrace
Autotrace can be configured to run the sql & gives a plan  and statistics afterwards or just give you an explain plan without executing the query.

Tkprof
Analyzes trace file

Click here to see white paper on The Oracle Optimizer Explain the Explain Plan.

If you are using plsqldev.exe tool, then open "Explain plan window" and write the query inside the window, then press F8 / execute to find "Execution plan" of a query.

This document will be helpful for beginner's  ( developers) . I will publish the advance concept of execution plan and performance tuning tips in next document.

Using V$SQL_PLAN Views

In addition to running the EXPLAIN PLAN command and displaying the plan, you can use the V$SQL_PLAN views to display the execution plan of a SQL statement:

After the statement has executed, you can display the plan by querying the V$SQL_PLAN view. V$SQL_PLAN contains the execution plan for every statement stored in the cursor cache. Its definition is similar to the PLAN_TABLE. See "PLAN_TABLE Columns".

The advantage of V$SQL_PLAN over EXPLAIN PLAN is that you do not need to know the compilation environment that was used to execute a particular statement. For EXPLAIN PLAN, you would need to set up an identical environment to get the same plan when executing the statement.

The V$SQL_PLAN_STATISTICS view provides the actual execution statistics for every operation in the plan, such as the number of output rows and elapsed time. All statistics, except the number of output rows, are cumulative. For example, the statistics for a join operation also includes the statistics for its two inputs.

The statistics in V$SQL_PLAN_STATISTICS are available for cursors that have been compiled with the STATISTICS_LEVEL initialization parameter set to ALL.

The V$SQL_PLAN_STATISTICS_ALL view enables side-by-side comparisons of the estimates that the optimizer provides for the number of rows and elapsed time. This view combines both V$SQL_PLAN and V$SQL_PLAN_STATISTICS information for every cursor.

make your execution plan's more tuned....
Feel free to post comments ...

Translate >>