Wednesday, October 31, 2007

Interpreting Explain plan

Interpreting Explain Plan
=========================

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.

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 unnesting, 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
[7] QEP Execution

(QEP = Query Evaluation Plan)

Steps [1]-[6] are sometimes grouped under the term 'Parsing'
Step [7] is the execution of the statement.

The explain plan a reprsentation of the access path produced in step 6.

Once the access path has been decided upon it is stored in the library cache
together with the statement itself. Queries are stored in the library cache
based upon a hashed representation of the query. When looking for a statement
in the library cache, we first apply a hashing algorithm to the current
statement and then look for this hash value in the library cache.
This access path will be used until the query is reparsed.

Terminology
~~~~~~~~~~~

Row Source - A row source is a software function that implements specific
operations (such as a table scan or a hash join)
and returns a set of rows.

Predicate - where clause of a query

Tuples - 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

Explain plan Hierarchy
~~~~~~~~~~~~~~~~~~~~~~

Simple explain plan:

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

When looking at a plan, the rightmost (ie most inndented) uppermost operation
is the first thing that is executed.
A more detailed explanation of this much simplified desctiption can be found below.

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.

More about Plans and the order of execution
===========================================

To understand plans and the order of execution, it is necessary to
understand the PARENT -- CHILD relationships involved:

PARENT
FIRST CHILD
SECOND CHILD

In this example, the FIRST CHILD is executed first followed by the
SECOND CHILD, then the PARENT collates the output in some way.

A more complex case is:

PARENT1
FIRST CHILD
FIRST GRANDCHILD
SECOND CHILD

Here the same principles apply, the FIRST GRANDCHILD is the initial operation
then the FIRST CHILD followed by the SECOND CHILD and finally the PARENT
collates the output.

These priniples can be applied to real operations as in the examples below.
Consider the following query:

EXAMPLE 1
=========

set autotrace traceonly explain

select ename,dname
from emp, dept
where emp.deptno=dept.deptno
and dept.dname in ('ACCOUNTING','RESEARCH','SALES','OPERATIONS');

15 rows selected.

This produces the following explain plan:

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=8 Bytes=248)
1 0 HASH JOIN (Cost=3 Card=8 Bytes=248)
2 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=1 Card=3 Bytes=36)
3 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=16 Bytes=304)

In the Execution Plan above, there are 2 columns of numbers that precede
the plan text of each line. The first number is the Statement ID
(usually known simply as "ID"), the second number is the Parent ID
of the line (The first line of the plan shows no parent because,
as the first line, it has no parent). The ID and PARETN ID are what
the plan generator uses to construct the explain plan.

The steps in the explain plan are indented to indicate the hierarchy of
operations and which steps are dependant on which other steps.

What follows is a walkthrough of the plan above:

Execution starts with: ID=0:

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=8 Bytes=248)

ID=0 has no parent but it has 1 child.
ID=0 is the parent of ID=1 and is dependant upon it for rows.
So ID=1 must be executed prior to ID=0

Moving on to ID=1:

1 0 HASH JOIN (Cost=3 Card=8 Bytes=248)

ID=1 is the child of ID=0
ID=1 is the parent of ID=2 and ID=3 and is dependant upon them for rows.
So ID=2 and ID=3 must be executed prior to ID=1

Moving on to ID=2:

2 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=1 Card=3 Bytes=36)

ID=2 is the first child of ID=1
ID=2 is not the parent of any other step.
This is the first step that is executed by the query.
Rows are provided to ID=1 from this step

ID=1 is also dependent on ID=3:

3 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=16 Bytes=304)

ID=3 is the second child of ID=1
ID=3 is not the parent of any other step.
This is the second step that is executed by the query.
Rows are provided to ID=1 from this step

ID=1 processes the rows it receives from its dependent steps (ID=2 & ID=3)
and returns them to its parent ID=0.
ID=0 returns the rows to the user.

A shortened summary of this is:

Execution starts with ID=0: SELECT STATEMENT but this is dependand on it's child objects
So it executes its first child step: ID=1 PID=0 HASH JOIN but this is dependand on it's child objects
So it executes its first child step: ID=2 PID=1 TABLE ACCESS (FULL) OF 'DEPT'
Then the second child step: ID=3 PID=2 TABLE ACCESS (FULL) OF 'EMP'
Rows are returned to the parent step(s) until finished


EXAMPLE 2
=========

select /*+ RULE */ ename,dname
from dept , emp
where emp.deptno=dept.deptno
and dept.dname in ('ACCOUNTING','RESEARCH','SALES','OPERATIONS');

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 NESTED LOOPS
2 1 TABLE ACCESS (FULL) OF 'EMP'
3 1 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'
4 3 INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)


Execution starts with ID=0: SELECT STATEMENT but this is dependand on it's child objects
So it executes it's first child step: ID=1 PID=0 NESTED LOOPS but this is dependand on it's child objects
So it executes it's first child step: ID=2 PID=1 TABLE ACCESS (FULL) OF 'EMP'
Rows are fetched in ID=2 These rows are returned to the parent
to drive the join in the parent: ID=1 PID=0 NESTED LOOPS
ID=1 uses the rows to execute ID=3: ID=3 PID=1 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' but this is dependand on it's child objects
So it executes it's first child step: ID=4 PID=3 INDEX (UNIQUE SCAN) OF 'PK_DEPT'
Rows are returned to the parent ID=1
ID=1 returns rows to it's parent ID=0
Once the rows fetched from ID=2 are exhausted another fetch is done to
get the next set of rows.
This process repeats until all the rows retched from ID=2 are exhausted

When starting to looks at explain plans, it is useful to use the ID/PARENT ID
but with practice the indentation, ordering and operations alone will become
enough to determine the access path used.

There are many ways of describing how to determine the operation in a plan,
once familiar with a particular method it becomes second nature. One such
description was to say that the rightmost-uppermost operation of an explain
plan is executed first, but although this proved an intuitive description
after some practice it is confusing confusing to some readers. If in doubt
consult the id and parent id hierarchy.


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 .

This defaults to:

db_block_buffers / ( (PROCESSES+3) / 4 )

The 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.

e.g.

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.

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 5 methods of index lookup:

index unique scan
index range scan
index full scan
index fast full scan
index skip 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.

example explain plan:

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
~~~~~~~~~~~~~~~~

Index range scan is a method for accessing a range values of a particular
column. AT LEAST the leading column of the index must be supplied to
access data via the index.
Can be used for range operations (e.g. > < <> >= <= between)
e.g.

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.

e.g.
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.

e.g.
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]

Index skip scan
---------------

Index skip scan finds rows even if the column is not the leading column of a
concatenated index. It skips the first column(s) during the search.
The next example checks ename='SMITH' for each index key
even though ename is not the leading column of the index. The leading column
(empno) is skipped.

create index i_emp on emp(empno, ename);
select /*+ index_ss(emp i_emp)*/ job from emp where ename='SMITH';

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 61 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 12 | 61 |
|* 2 | INDEX SKIP SCAN | I_EMP | 1 | | 11 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMP"."ENAME"='SMITH')
filter("EMP"."ENAME"='SMITH')


Rowid
~~~~~
This is the quickest access method available.
Oracle retrieves the specified block and extracts the rows it is
interested in.
Most frequently seen in explain plans as Table access by Rowid

Access by rowid :

SQL> explain plan for select * from dept where rowid = ':x';

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=18)
1 0 TABLE ACCESS (BY USER ROWID) OF 'DEPT' (Cost=1 Card=1 Bytes=18)

(BY USER ROWID) shows that the user provided the rowid.

Table is accessed by rowid following index lookup:

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

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=19)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=1 Card=1 Bytes=19)
2 1 INDEX (RANGE SCAN) OF 'EMP_PK' (UNIQUE) (Cost=1 Card=1)

(BY INDEX ROWID) shows that the rowid comes from the index access in the
step with ID = 2.

Joins
=====
A Join is a predicate that attempts to combine 2 row sources

We only ever join 2 row sources together:
At a given time only one Join Step is performed even though underlying
row sources may have been accessed in parallel. Once 2 row sources have been
the resultant row source can start to be joined it to other row sources.
Note that some join methods (such as nested loops) allow a batch of
fetched rows to be returned to the higher levels before fetching the next batch.

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.

e.g. 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 scehmatic:

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
~~~~~~~~~~~~
Fetches the first batch of 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
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.

Sometimes genreated VIEWs can be seeen in the execution plan:

VIEW in the FROM clause (INLINE VIEW)
-------------------------------------

If a view cannot be merged into the main query then a VIEW keyword will
be shown in the plan to represent this.

explain plan for
select ename,tot
from emp,
(select empno x, sum(empno) tot from big_emp group by empno)
where emp.empno = x;

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 16 | 720 | | 46 |
|* 1 | HASH JOIN | | 16 | 720 | | 46 |
| 2 | TABLE ACCESS FULL | EMP | 16 | 304 | | 1 |
| 3 | VIEW | | 10000 | 253K| | 44 |
| 4 | SORT GROUP BY | | 10000 | 30000 | 248K| 44 |
| 5 | TABLE ACCESS FULL| BIG_EMP | 10000 | 30000 | | 24 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EMP"."EMPNO"="from$_subquery$_002"."X")

"from$_subquery$_002" is the generated name of the inline view that is
produced in id=3 line. If the inline view was aliased in the from clause then
the system generated name ("from$_subquery$_002") would be replaced by this
alias.

Subquery isunnested but resultant view is not merged:
-----------------------------------------------------

In preoptimization there is a stage where subqueries can be unnested (combined
with base query). There are numerous and complex ruled governing this activity
which are beyond the scope of this article. However, if successful, a subquery
may be unnested to produce an inline view which represents the subquery. This
view is subject to view merging. If the view is non-mergeable then a VIEW keyword
will appear in the plan.

explain plan for
select ename
from emp
where emp.empno in (select empno x from big_emp group by empno);

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 16 | 512 | | 46 |
|* 1 | HASH JOIN SEMI | | 16 | 512 | | 46 |
| 2 | TABLE ACCESS FULL | EMP | 16 | 304 | | 1 |
| 3 | VIEW | VW_NSO_1 | 10000 | 126K| | 44 |
| 4 | SORT GROUP BY | | 10000 | 30000 | 248K| 44 |
| 5 | TABLE ACCESS FULL| BIG_EMP | 10000 | 30000 | | 24 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EMP"."EMPNO"="VW_NSO_1"."X")

In this example, the subquery is unnested but the resultant inline view
cannot be merged due to the 'group by'. This unmergeable view is given the
system geberated name of "VW_NSO_1".


Partition Views
===============

Partition views are a legacy technology that were superceded by
the partitioning option. This section of the article is provided as
reference for such legacy systems.

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 [:Q64000] [AN] Ct=11 Cd=2000 Bt=104000
TABLE ACCESS FULL KBWYT2 [:Q63000] [AN] Ct=11 Cd=2000 Bt=104000
TABLE ACCESS FULL KBWYT3 [:Q62000] [AN] Ct=11 Cd=2000 Bt=104000
FILTER [:Q61000]
TABLE ACCESS FULL KBWYT4 [:Q61000] [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.

Partition handling
==================

The examples are based on a simple range partitioned table:

create table range_part (col1 number(9))
partition by range (col1)
(partition p1 values less than (10) ,
partition p2 values less than (20) ,
partition p3 values less than(30) ,
partition p4 values less than (MAXVALUE)) ;

Eliminated partitions can be determined at parse time
=====================================================

As optimizer can check the partition ranges in the dictionary, in some
cases it knows which partitions should be used and the others are
"elliminated" when the select is optimized.

This activity is reflected in the following plan_table columns:

PARTITION_START
PARTITION_STOP

Example:

explain plan for select * from range_part where col1=15;
start $ORACLE_HOME/rdbms/admin/utlxpls.sql
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 1 | | |
|* 1 | TABLE ACCESS FULL | RANGE_PART | 1 | 13 | 1 | 2 | 2 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RANGE_PART"."COL1"=15)

The Pstart and Pstop columns show that the optimizer believes that
only partition 2 is required to satisfy this query.

Eliminated partitions can only be determined at run time
========================================================

In this example, since the partition key references a bind variable,
the actual partition numbers cannot be determined during optimization.
The optimizer can only determine that there is a possibility that
partitions can be eliminated at runtime. The
PARTITION_START and PARTITION_START columns in the plan table will contain
either KEY or ROW LOCATION depending on the nature of the query and the partition
itself.

Example:

explain plan for select * from range_part where col1= :x;
start $ORACLE_HOME/rdbms/admin/utlxpls.sql

--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 1 | | |
| 1 | PARTITION RANGE SINGLE| | | | | KEY | KEY |
|* 2 | TABLE ACCESS FULL | RANGE_PART | 1 | 13 | 1 | KEY | KEY |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("RANGE_PART"."COL1"=TO_NUMBER(:Z))

You can see more examples in
Note 179518.1 Partition Pruning and Joins


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]

OTHER (for REMOTE)
----------------------------------------------------------------
SELECT "DEPTNO","DNAME" FROM "DEPT" A

For more details on remote queries see Note 33838.1

Bind Variables
==============
Bind variables are recommended in most cases because they promote sharing of
sql code
During optimization, the optimizer 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.

To check the execution plan of a query using bind variables in sqlplus, the
following can be used:

variable x varchar2(18);

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:

o [:Q1000004] entries in the explain plan
o Checkout the other column for details of what the slaves are executing
o 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
~~~~~~~~~~~
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


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 files



The exact methods are explained in:

Note 235530.1 Recommended Method for Obtaining a Formatted Explain Plan
Note 31101.1 SCRIPT: DIAG - Obtaining Formatted Explain Plan Output
Note 41634.1 TKPROF and Problem Solving

REFERENCES:
==============
Note 33838.1 Determining the execution plan for a distributed query
Note 236026.1 Display Execution Plans with dbms_xplan.display
Note 235530.1 Recommended Method for Obtaining a Formatted Explain Plan
Note 31101.1 SCRIPT: DIAG - Obtaining Formatted Explain Plan Output
Note 41634.1 TKPROF and Problem Solving
Note 209197.1 Using Statspack to Record Explain Plan Details
Note 237287.1 How To Verify Parallel Execution is running
Note 179518.1 Partition Pruning and Joins

Wednesday, October 24, 2007

USING CRON WITH ORACLE

USING CRON WITH ORACLE

by Raghu Viswanathan
Oracle Support - Unix Group

The objective of this bulletin is to acquaint the reader with the use of cron
and to provide some examples on how it is used with ORACLE. We will use the
Unix att universe for the purpose of discussion. Differences between att and
bsd are documented at the end of this report.

For simplicity, let us assume that the database is owned
by the account 'oracle' and that the demo account is called 'scott'.
Let us also assume that $ORACLE_HOME is located at /usr/oracle and
the SID is S

What is cron?. Cron is a clock daemon utility provided by
unix. This utility, which is located in /etc/cron executes commands
specified in the file /usr/lib/crontab at times specified in crontab
by the user. cron checks this file EVERY MINUTE.

Not all users are allowed to use cron. In order to use it,
your userid must be listed in a file called /usr/lib/cron/cron.allow
which is owned by root. Contact your system administrator to get your
userid in this file. From now on, let us assume that the file
cron.allow only contains userids root and oracle.

Every user listed in cron.allow will have his/her personal
crontab file. Each user can only have one crontab file. This crontab
will list all the commands that the user wants to execute and what
times thereof.

How to set up your own crontab file:
------------------------------------

All crontab file contain lines in the following format:

* * * * *

where the *'s (often called the five time fields) reference digits
that collectively make up the time, and reference the
command to be executed. The five time fields in successive order
are:

Field Range
------------------------

minute 0-59
hour 0-23
day of the month 1-31
month of the year 1-12
day of the week 0-6 (0 is Sunday)

You may directly use * for any one of these values which means that
all possible values are accepted. For example, to execute an echo
command everyday at 7:29 in the evening you can set up the following
line:

29 19 * * * echo its dinner time.

You can enter more requests on consecutive lines. Once
you have entered all these into a file (say, mycron) using your
favorite editor, you can now submit this file as YOUR CRONTAB file
to the system. To do this type:

crontab mycron

You can always add more requests in your file mycron and
then resubmit it using the above command. The system will only retain
your most recent submission.

To view your current submitted file type:

crontab -l

To delete your current submitted file type:

crontab -r


Your Results:
-------------

Now that you have submitted your crontab file, the echo
command will execute everyday at 7:30 in the evening. The results are
sent to you via unix mail.


Uses of Cron:
-------------

As you can see, cron is very useful. System administrators
use cron often to perform administrative chores automatically.
Typically, such activities are performed at night when the system load
is low. From a database standpoint, we can use cron to perform backups
of the database every night or we can run SQL reports that will spool
output files every night to keep track of system usage.

Scenario:
---------

Let us set up a very simple example - let us say that we wish
to know the contents of the emp and dept tables in the scott account
everyday at 11:30 at night and save the results in a file called
scottout. In order to do this, we will need 3 files:

sql script containing the queries - call this scottcron.sql
a shell script to execute scottcron.sql - call this scottshell
a crontab file to run the shell script - call this mycron
(this last file has to be submitted to cron)

Let us also say that these files and the results will be
located in the directory /usr/oracle/personal.

Step 1 - Writing the SQL script:
--------------------------------

spool /usr/oracle/personal/scottout
select * from emp;
select * from dept;
exit;

Now we need to set up a shell script that will enter SQL*Plus
as scott/tiger and execute scottcron.sql. We also need to write the
crontab file. Let us approach this from two angles:

Approach A - Executing as ROOT:
-------------------------------

In this approach we will let the ROOT execute the commands
and save it in /usr/oracle/personal. We can set up the shell file
scottshell as follows:

sqlplus scott/tiger @/usr/oracle/personal/scottcron

and the crontab file as:

30 23 * * * su - oracle -c "/usr/oracle/personal/scottshell" >
"/usr/oracle/personall/scotterror"


Essentially, root is the super user and can thus impersonate oracle
without a password. su oracle will enable root to become oracle.
The -c means 'execute the following command as oracle'. The extra '-'
between su and oracle is crucial - because of this, the script is being
executed as if the user oracle had logged in himself. In other words
all environment variables like $ORACLE_HOME,$ORACLE_SID and $PATH
are set correctly.

This is the approach we use at Oracle Support when maintaining our own online
support system. Our crontab file looks like the following:

<5 time fields> su - oracle -c "shell1"
<5 time fields> su - oracle -c "shell2"
<5 time fields> su - oracle -c "shell3"
<5 time fields> su - oracle -c "shell4"
<5 time fields> su - oracle -c "shell5"


Approach B - Executing as oracle:
---------------------------------

In this approach, oracle will submit the crontab file
instead of root. The shell file scottshell will now look like:

ORACLE_HOME=/usr/oracle; export ORACLE_HOME;
ORACLE_SID=S; export ORACLE_SID;
/usr/oracle/bin/sqlplus scott/tiger @/usr/oracle/personal/scottcron
and the crontab file will look like:

30 23 * * * "/usr/oracle/personal/scottshell" >
"/usr/oracle/personall/scotterror"

A common mistake is to forget to set ORACLE_HOME and
ORACLE_SID - this usually yields to a ORA 2700 error. This is not a
bug - when cron runs the script it uses the environment similar to
the one used by root when it does an 'su' to oracle WITHOUT
executing the login scripts of oracle. Thus the ORACLE variables are
not set. As a matter of fact, you can also set the PATH explicitly to
avoid using full path names.

We can do an export or run a report in the same way.


Differences with BSD Environment:
---------------------------------

In the ATT environment, we could never edit the crontab
file directly - we had to keep resubmitting our personal cronfiles.
In BSD versions, there is only one configuration file called
/usr/lib/crontab. Version 4.3 also has a crontab.local. You may
either use one or the other, not both.

Also, on some systems, the crontab files are set up in a
seven-field format. The first 5 fields are the time fields, the sixth
is the userid and seventh is the command to be executed.

If you are not using cron, be sure to remove your crontab
file - otherwise the system will continually execute cron!

HOW TO RUN A SQL PLUS SCRIPT FROM A CRON JOB

PURPOSE:
========
HOW TO RUN A SQL PLUS SCRIPT FROM A CRON JOB.

SCOPE & APPLICATION:
=====================
This article is intended for an audience having knowledge about unix and sql.

EXPLANATION:
============
Cron is a mechanism for planning and scheduling batch jobs. The daemon "crond" is started automatically on system boot up. It runs one check per minute to see if there are any jobs to execute. The list of jobs to execute is created by the program "crontab".

Entering the command "crontab -l" will display a list of current entries. By default, only entries owned by the logged in user will be displayed.
Existing lists can be removed/deleted with command "crontab -r".

To create a new list, it is recommended to read the entries from a file using the command

"crontab filename".

To run a sql script, create a file (for ex: script1.ksh) containing the sql commands. Now use the following crontab entry.

#The following entry will execute the script 1.ksh every hour.
0 12 * * * $ORACLE_BASE/admin/scripts/snap.ksh

This entry consists of six parameters. The first five parameters define the time schedule, whereas the sixth parameter contains the command for executing the job.
This command consists of:

- the full path and file name of the script - an argument

Parameters defining the time schedule are:

minute(0-59)
hour(0-23)
day of month(1- 31)
month(1-12)
day of week(0-6) 0 = Sun

Hence, the above sample entry: 0 12 * * * can be translated as:

If Minute = 0 and Hour = 12, the script will be executed.

Because the last three scheduling parms are defined by the wildcard character
"*", the job will be executed every day.

REFERENCES:
===========
For further explanations under Unix enter the following commands:

man crontab
man 5 crontab
man cron

Contention on SYS.DUAL table-- - - From Vivek Sharma

Contention on sys.dual

SYS owned dual table was introduced by Oracle long back and since then the Application
Development team across the globe has been using this table for some internal processing.
Some of the common queries on DUAL table used by the application team are :

select sysdate from dual;
select sequence.nextval from dual;
select sys_context('USERENV','SESSION_USER') from dual;

DUAL being a magic table contains only one row and hence returns a single row for a given
sql statement. Any inputs passed to dual table returns the desired output and hence is widely
used by the applications.

Originally, this table was introduced by Oracle for its internal processing, for example, RMAN
uses this table to fetch the current date for a backup. Contention on this table can occur, if
application uses this table frequently and concurrently, with common waits like Buffer Busy Wait,
Latch Free (Cache Buffer Chain / Cache Buffer LRU chain). The behaviour of this table is changed in
Oracle database version 10g and hence, this note is only applicable for database versions 9i and below.


This article focusses on eliminating contention on sys owned dual table and an alternative to this table
without altering the original table. Oracle strongly recommends that sys owned dual table should not be
altered in any way.

In this Article, we will create our own dual table and will force the application to use our table rather
than using sys owned dual table. This will be done without making any change in the application which was a
big challenge when it comes to Customized applications developed by third party.

Any single call to a sys owned dual table does 3-4 logical i/o’s, thus concurrent and multiple calls on this
table will increase these I/O’s thus causing contention.
As an Example :

SQL> column user_info format a30
SQL> select sys_context('USERENV','SESSION_USER') user_info from dual;

USER_INFO
------------------------------
VIVEK


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'DUAL'




Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
410 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

Now, if I create my own table in my production schema, the same query used above
can be forced to use our table without making change in the code. The process would
be :

SQL> create table my_dual (x number primary key) organization index;

Table created.

SQL> insert into my_dual values (1);

1 row created.

SQL> exec dbms_stats.gather_table_stats(ownname=>'VIVEK',tabname=>'MY_DUAL',cascade=>TRUE);

PL/SQL procedure successfully completed.

SQL> create view dual as select * from my_dual;

View created.

SQL> set autot on
SQL> select sys_context('USERENV','SESSION_USER') user_info from dual;

USER_INFO
------------------------------
VIVEK


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1)
1 0 INDEX (FULL SCAN) OF 'SYS_IOT_TOP_32339' (UNIQUE) (Cost=1
Card=1)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
410 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

1 Logical I/O against 4 Logical I/O’s which is 75% performance improvement. The improvement is
clearly visible when this table is accessed concurrently and will eliminate the contention,
thus improving buffer cache efficiency.

Thus, if statspack shows high logical I/O’s or disk reads on SYS.Dual table, this solution can be
implemented to reduce these I/O’s. Less the number of I/O’s, less is the contention for latches.
Implementing this solution does not alter the original dual table and Oracle internally can use its
own table whenever required while our application (without any modification) uses our own table
efficiently.

As mentioned earlier, the behaviour of dual table is changed in Oracle 10g. Oracle 10g introduces
FAST DUAL concept and does not visits data blocks to get the desired output. Hence, the recommendations
suggested in this note is not required in all versions of 10g databases. For example,


Orcl10g> SQL> select sys_context('USERENV','SESSION_USER') user_info from dual;

USER_INFO
-------------
VIVEK


Execution Plan
----------------------------------------------------------
Plan hash value: 1388734953

-----------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
415 bytes sent via SQL*Net to client

Tuesday, October 23, 2007

Why isn't my index getting used?---Case Study

Why isn't my index getting used?

There are many possible causes of this. In this section, we'll take a look at some of the most common.
Case 1

We're using a B*Tree index, and our predicate does not use the leading edge of an index. In this case, we might have a table T with an index on T(x,y). We query SELECT * FROM T WHERE Y = 5. The optimizer will tend not to use the index since our predicate did not involve the column X -- it might have to inspect each and every index entry in this case (we'll discuss an index skip scan shortly where this is not true). It will typically opt for a full table scan of T instead. That does not preclude the index from being used. If the query was SELECT X,Y FROM T WHERE Y = 5, the optimizer would notice that it did not have to go to the table to get either X or Y (they are in the index) and may very well opt for a fast full scan of the index itself, as the index is typically much smaller than the underlying table. Note also that this access path is only available with the CBO.

There is another case whereby the index on T(x,y) could be used with the CBO is during an index skip scan. The skip scan works well if and only if the leading edge of the index (X in the previous example) has very few distinct values and the optimizer understands that. For example, consider an index on (GENDER, EMPNO) where GENDER has the values M and F, and EMPNO is unique. A query such as

select * from t where empno = 5;

might consider using that index on T to satisfy the query in a skip scan method, meaning the query will be processed conceptually like this:

select * from t where GENDER='M' and empno = 5
UNION ALL
select * from t where GENDER='F' and empno = 5;

It will skip throughout the index, pretending it is two indexes: one for Ms and one for Fs. We can see this in a query plan easily. We'll set up a table with a bivalued column and index it:

ops$tkyte@ORA10GR1> create table t
2 as
3 select decode(mod(rownum,2), 0, 'M', 'F' ) gender, all_objects.*
4 from all_objects
5 /
Table created.

ops$tkyte@ORA10GR1> create index t_idx on t(gender,object_id)
2 /
Index created.

ops$tkyte@ORA10GR1> begin
2 dbms_stats.gather_table_stats
3 ( user, 'T', cascade=>true );
4 end;
5 /
PL/SQL procedure successfully completed.

Now, when we query this, we should see the following:

ops$tkyte@ORA10GR1> set autotrace traceonly explain
ops$tkyte@ORA10GR1> select * from t t1 where object_id = 42;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=1 Bytes=95)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (TABLE) (Cost=4 Card=1 Bytes=95)
2 1 INDEX (SKIP SCAN) OF 'T_IDX' (INDEX) (Cost=3 Card=1)

The INDEX SKIP SCAN step tells us that Oracle is going to skip throughout the index, looking for points where GENDER changes values and read down the tree from there, looking for OBJECT_ID=42 in each virtual index being considered. If we increase the number of distinct values for GENDER measurably, as follows:

ops$tkyte@ORA10GR1> update t
2 set gender = chr(mod(rownum,256));
48215 rows updated.

ops$tkyte@ORA10GR1> begin
2 dbms_stats.gather_table_stats
3 ( user, 'T', cascade=>true );
4 end;
5 /
PL/SQL procedure successfully completed.

we'll see that Oracle stops seeing the skip scan as being a sensible plan. It would have 256 mini indexes to inspect, and it opts for a full table scan to find our row:

ops$tkyte@ORA10GR1> set autotrace traceonly explain
ops$tkyte@ORA10GR1> select * from t t1 where object_id = 42;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=158 Card=1 Bytes=95)
1 0 TABLE ACCESS (FULL) OF 'T' (TABLE) (Cost=158 Card=1 Bytes=95)

Case 2

We're using a SELECT COUNT(*) FROM T query (or something similar) and we have a B*Tree index on table T. However, the optimizer is full scanning the table, rather than counting the (much smaller) index entries. In this case, the index is probably on a set of columns that can contain nulls. Since a totally null index entry would never be made, the count of rows in the index will not be the count of rows in the table. Here the optimizer is doing the right thing -- it would get the wrong answer if it used the index to count rows.
Case 3

For an indexed column, we query using the following:

select * from t where f(indexed_column) = value

and find that the index on INDEX_COLUMN is not used. This is due to the use of the function on the column. We indexed the values of INDEX_COLUMN, not the value of F(INDEXED_COLUMN). The ability to use the index is curtailed here. We can index the function if we choose to do it.

Case 4

We have indexed a character column. This column contains only numeric data. We query using the following syntax:

select * from t where indexed_column = 5

Note that the number 5 in the query is the constant number 5 (not a character string). The index on INDEXED_COLUMN is not used. This is because the preceding query is the same as the following:

select * from t where to_number(indexed_column) = 5

We have implicitly applied a function to the column and, as noted in case 3, this will preclude the use of the index. This is very easy to see with a small example. In this example, we're going to use the built-in package DBMS_XPLAN. This package is available only with Oracle9i Release 2 and above (in Oracle9i Release 1, we will use AUTOTRACE instead to see the plan easily, but we will not see the predicate information—that is only available in Oracle9i Release 2 and above):

ops$tkyte@ORA10GR1> create table t ( x char(1) constraint t_pk primary key,
2 y date );
Table created.

ops$tkyte@ORA10GR1> insert into t values ( '5', sysdate );
1 row created.

ops$tkyte@ORA10GR1> delete from plan_table;
3 rows deleted.

ops$tkyte@ORA10GR1> explain plan for select * from t where x = 5;
Explained.

ops$tkyte@ORA10GR1> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------
Plan hash value: 749696591
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 1 | 12 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("X")=5)

As you can see, it full scanned the table, and even if we were to hint the query

ops$tkyte@ORA10GR1> explain plan for select /*+ INDEX(t t_pk) */ * from t
2 where x = 5;
Explained.

ops$tkyte@ORA10GR1> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------
Plan hash value: 3473040572
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 34 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 12 | 34 (0)| 00:00:01 |
|* 2 | INDEX FULL SCAN | T_PK | 1 | | 26 (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TO_NUMBER("X")=5)

it uses the index, but not for a UNIQUE SCAN as we might expect -- it is FULL SCANNING this index. The reason lies in the last line of output there: filter(TO_NUMBER("X")=5). There is an implicit function being applied to the database column. The character string stored in X must be converted to a number prior to comparing to the value 5. We cannot convert 5 to a string, since our NLS settings control what 5 might look like in a string (it is not deterministic), so we convert the string into a number, and that precludes the use of the index to rapidly find this row. If we simply compare strings to strings

ops$tkyte@ORA10GR1> delete from plan_table;
2 rows deleted.

ops$tkyte@ORA10GR1> explain plan for select * from t where x = '5';
Explained.

ops$tkyte@ORA10GR1> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------
Plan hash value: 1301177541
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 12 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | T_PK | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("X"='5')

we get the expected INDEX UNIQUE SCAN, and we can see the function is not being applied. You should always avoid implicit conversions anyway. Always compare apples to apples and oranges to oranges. Another case where this comes up frequently is with dates. We try to query:

-- find all records for today
select * from t where trunc(date_col) = trunc(sysdate);

and discover that the index on DATE_COL will not be used. We can either index the TRUNC(DATE_COL) or, perhaps more easily, query using range comparison operators. The following demonstrates the use of greater than and less than on a date. Once we realize that the condition

TRUNC(DATE_COL) = TRUNC(SYSDATE)

is the same as the condition

select *
from t
where date_col >= trunc(sysdate)
and date_col < trunc(sysdate+1)

this moves all of the functions to the right-hand side of the equation, allowing us to use the index on DATE_COL (and it has the same exact effect as WHERE TRUNC(DATE_COL) = ➥ TRUNC(SYSDATE)).

If possible, you should always remove the functions from database columns when they are in the predicate. Not only will doing so allow for more indexes to be considered for use, but also it will reduce the amount of processing the database needs to do. In the preceding case, when we used

where date_col >= trunc(sysdate)
and date_col < trunc(sysdate+1)

the TRUNC values are computed once for the query, and then an index could be used to find just the qualifying values. When we used TRUNC(DATE_COL) = TRUNC(SYSDATE), the TRUNC(DATE_COL) had to be evaluated once per row for every row in the entire table (no indexes).

Case 5

The index, if used, would actually be slower. I see this a lot -- people assume that, of course, an index will always make a query go faster. So, they set up a small table, analyze it, and find that the optimizer doesn't use the index. The optimizer is doing exactly the right thing in this case. Oracle (under the CBO) will use an index only when it makes sense to do so. Consider this example:

ops$tkyte@ORA10GR1> create table t
2 ( x, y , primary key (x) )
3 as
4 select rownum x, object_name
5 from all_objects
6 /
Table created.

ops$tkyte@ORA10GR1> begin
2 dbms_stats.gather_table_stats
3 ( user, 'T', cascade=>true );
4 end;
5 /
PL/SQL procedure successfully completed.

If we run a query that needs a relatively small percentage of the table, as follows:

ops$tkyte@ORA10GR1> set autotrace on explain
ops$tkyte@ORA10GR1> select count(y) from t where x < 50;

COUNT(Y)
----------
49

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=28)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'T' (TABLE) (Cost=3 Card=41 Bytes=1148)
3 2 INDEX (RANGE SCAN) OF 'SYS_C009167' (INDEX (UNIQUE)) (Cost=2 Card=41)

it will happily use the index; however, we'll find that when the estimated number of rows to be retrieved via the index crosses a threshold (which varies depending on various optimizer settings, physical statistics, and so on), we'll start to observe a full table scan:

ops$tkyte@ORA10GR1> select count(y) from t where x < 15000;

COUNT(Y)
----------
14999

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=57 Card=1 Bytes=28)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'T' (TABLE) (Cost=57 Card=14994 Bytes=419832)

This example shows the optimizer won't always use an index and, in fact, it makes the right choice in skipping indexes. While tuning your queries, if you discover that an index isn't used when you think it "ought to be," don't just force it to be used -- test and prove first that the index is indeed faster (via elapsed and I/O counts) before overruling the CBO. Reason it out.
Case 6

We haven't analyzed our tables in a while. They used to be small, but now when we look at them, they have grown quite large. An index will now make sense, whereas it didn't originally. If we analyze the table, it will use the index.

Without correct statistics, the CBO cannot make the correct decisions.
Index Case Summary

In my experience, these six cases are the main reasons I find that indexes are not being used. It usually boils down to a case of "They cannot be used -- using them would return incorrect results" or "They should not be used -- if they were used, performance would be terrible."

Why isn't my index getting used?-----1

by Jonathan Lewis

The question in the title of this piece is probably the single most frequently occurring question that appears in the Metalink forums and Usenet newsgroups. This article uses a test case that you can rebuild on your own systems to demonstrate the most fundamental issues with how cost-based optimisation works. And at the end of the article, you should be much better equipped to give an answer the next time you hear that dreaded question.

Because of the wide variety of options that are available when installing Oracle, it isn't usually safe to predict exactly what will happen when someone runs a script that you have dictated to them. But I'm going to risk it, in the hope that your database is a fairly vanilla installation, with the default values for the mostly commonly tweaked parameters. The example has been built and tested on an 8.1.7 database with the db_block_size set to the commonly used value of 8K and the db_file_multiblock_read_count set to the equally commonly used value 8. The results may be a little different under Oracle 9.2

Run the script from Figure 1, which creates a couple of tables, then indexes and analyses them.

create table t1 as
select
trunc((rownum-1)/15) n1,
trunc((rownum-1)/15) n2,
rpad('x', 215) v1
from all_objects<
where rownum <= 3000;

create table t2 as
select
mod(rownum,200) n1,
mod(rownum,200) n2,
rpad('x',215) v1
from all_objects
where rownum <= 3000;

create index t1_i1 on t1(N1);
create index t2_i1 on t2(n1);

analyze table t1 compute
statistics;
analyze table t2 compute
statistics;

Figure 1: The test data sets.

Once you have got this data in place, you might want to convince yourself that the two sets of data are identical — in particular, that the N1 columns in both data sets have values ranging from 0 to 199, with 15 occurrences of each value. You might try the following check:

select n1, count(*)
from t1
group by n1;

and the matching query against T2 to prove the point.

If you then execute the queries:

select * from t1 where n1 = 45;
select * from t2 where n1 = 45;

You will find that each query returns 15 rows. However if you

set autotrace traceonly explain

you will discover that the two queries have different execution paths.

The query against table T1 uses the index, but the query against table T2 does a full tablescan.

So you have two sets of identical data, with dramatically different access paths for the same query.
What Happened to the Index?

Note: if you've ever come across any of those "magic number" guidelines regarding the use of indexes, e.g., "Oracle will use an index for less than 23 percent, 10 percent, 2 percent (pick number at random) of the data," then you may at this stage begin to doubt their validity. In this example, Oracle has used a tablescan for 15 rows out of 3,000, i.e., for just one half of one percent of the data!

To investigate problems like this, there is one very simple ploy that I always try as the first step: Put in some hints to make Oracle do what I think it ought to be doing, and see if that gives me any clues.

In this case, a simple hint:

/*+ index(t2, t2_i1) */

is sufficient to switch Oracle from the full tablescan to the indexed access path. The three paths with costs (abbreviated to C=nnn) are shown in Figure 2:

select * from t1 where n1 = 45;

EXECUTION PLAN
--------------
TABLE ACCESS BY INDEX ROWID OF T1 (C=2)
INDEX(RANGE SCAN) OF T1_I1 (C=1)


select * from t2 where n1 = 45;

EXECUTION PLAN
--------------
TABLE ACCESS FULL OF T2 (C=15)


select /*+ index(t2 t2_i1) */
*
from t1
where n1 = 45;

EXECUTION PLAN
--------------
TABLE ACCESS BY INDEX ROWID OF T2 (C=16)
INDEX(RANGE SCAN) OF T2_I1 (C=1)

Figure 2: The different queries and their costs.

So why hasn't Oracle used the index by default in for the T2 query? Easy — as the execution plan shows, the cost of doing the tablescan is cheaper than the cost of using the index.
Why is the Tablescan Cheaper?

This, of course, is simply begging the question. Why is the cost of the tablescan cheaper than the cost of using the index?

By looking into this question, you uncover the key mechanisms (and critically erroneous assumptions) of the Cost Based Optimiser.

Let's start by examining the indexes by running the query:

select
table_name,
blevel,
avg_data_blocks_per_key,
avg_leaf_blocks_per_key,
clustering_factor
from user_indexes;

The results are given in the table below:
T1 T2
Blevel 1 1
Data block / key 1 15
Leaf block / key 1 1
Clustering factor 96 3000

Note particularly the value for "data blocks per key." This is the number of different blocks in the table that Oracle thinks it will have to visit if you execute a query that contains an equality test on a complete key value for this index.

So where do the costs for our queries come from? As far as Oracle is concerned, if we fire in the key value 45, we get the data from table T1 by hitting one index leaf block and one table block — two blocks, so a cost of two.

If we try the same with table T2, we have to hit one index leaf block and 15 table blocks — a total of 16 blocks, so a cost of 16.

Clearly, according to this viewpoint, the index on table T1 is much more desirable than the index on table T2. This leaves two questions outstanding, though:

Where does the tablescan cost come from, and why are the figures for the avg_data_blocks_per_key so different between the two tables?

The answer to the second question is simple. Look back at the definition of table T1 — it uses the trunc() function to generate the N1 values, dividing the "rownum - 1 "by 15 and truncating.

Trunc(675/15) = 45
Trunc(676/15) = 45

Trunc(689/15) = 45

All the rows with the value 45 do actually appear one after the other in a tight little clump (probably all fitting one data block) in the table.

Table T2 uses the mod() function to generate the N1 values, using modulus 200 on the rownum:

mod(45,200) = 45
mod(245,200) = 45

mod(2845,200) = 45

The rows with the value 45 appear every two hundredth position in the table (probably resulting in no more than one row in every relevant block).

By doing the analyze, Oracle was able to get a perfect description of the data scatter in our table. So the optimiser was able to work out exactly how many blocks Oracle would have to visit to answer our query — and, in simple cases, the number of block visits is the cost of the query.
But Why the Tablescan?

So we see that an indexed access into T2 is more expensive than the same path into T1, but why has Oracle switched to the tablescan?

This brings us to the two simple-minded, and rather inappropriate, assumptions that Oracle makes.

The first is that every block acquisition equates to a physical disk read, and the second is that a multiblock read is just as quick as a single block read.

So what impact do these assumptions have on our experiment?

If you query the user_tables view with the following SQL:

select
table_name,
blocks
from user_tables;

you will find that our two tables each cover 96 blocks.

At the start of the article, I pointed out that the test case was running a version 8 system with the value 8 for the db_file_multiblock_read_count.

Roughly speaking, Oracle has decided that it can read the entire 96 block table in 96/8 = 12 disk read requests.

Since it takes 16 block (= disk read) requests to access the table by index, it is clearer quicker (from Oracle's sadly deluded perspective) to scan the table — after all 12 is less than 16.

Voila! If the data you are targetting is suitably scattered across the table, you get tablescans even for a very small percentage of the data — a problem that can be exaggerated in the case of very big blocks and very small rows.
Correction

In fact, you will have noticed that my calculated number of scan reads was 12, whilst the cost reported in the execution plan was 15. It is a slight simplfication to say that the cost of a tablescan (or an index fast full scan for that matter) is

'number of blocks' /
db_file_multiblock_read_count.

Oracle uses an "adjusted" multi-block read value for the calculation (although it then tries to use the actual requested size when the scan starts to run).

For reference, the following table compares a few of the actual and adjusted values:
Actual Adjusted
4 4.175
8 6.589
16 10.398
32 16.409
64 25.895
128 40.865

As you can see, Oracle makes some attempt to protect you from the error of supplying an unfeasibly large value for this parameter.

There is a minor change in version 9, by the way, where the tablescan cost is further adjusted by adding one to result of the division — which means tablescans in V9 are generally just a little more expensive than in V8, so indexes are just a little more likely to be used.
Adjustments

We have seen that there are two assumptions built into the optimizer that are not very sensible.

* A single block read costs just as much as a multi-block read — (not really likely, particularly when running on file systems without direction)
* A block access will be a physical disk read — (so what is the buffer cache for?)

Since the early days of Oracle 8.1, there have been a couple of parameters that allow us to correct these assumption in a reasonably truthful way.

See Tim Gorman's article for a proper description of these parameters, but briefly:

Optimizer_index_cost_adj takes a value between 1 and 10000 with a default of 100. Effectively, this parameter describes how cheap a single block read is compared to a multiblock read. For example the value 30 (which is often a suitable first guess for an OLTP system) would tell Oracle that a single block read costs 30% of a multiblock read. Oracle would therefore incline towards using indexed access paths for low values of this parameter.

Optimizer_index_caching takes a value between 0 and 100 with a default of 0. This tells Oracle to assume that that percentage of index blocks will be found in the buffer cache. In this case, setting values close to 100 encourages the use of indexes over tablescans.

The really nice thing about both these parameters is that they can be set to "truthful" values.

Set the optimizer_index_caching to something in the region of the "buffer cache hit ratio." (You have to make your own choice about whether this should be the figure derived from the default pool, keep pool, or both).

The optimizer_index_cost_adj is a little more complicated. Check the typical wait times in v$system_event for the events "db file scattered read" (multi block reads) and "db file sequential reads" (single block reads). Divide the latter by the former and multiply by one hundred.
Improvements

Don't forget that the two parameters may need to be adjusted at different times of the day and week to reflect the end-user workload. You can't just derive one pair of figures, and use them for ever.

Happily, in Oracle 9, things have improved. You can now collect system statistics, which are originally included just the four:

+ Average single block read time
+ Average multi block read time
+ Average actual multiblock read
+ Notional usable CPU speed.

Suffice it to say that this feature is worth an article in its own right — but do note that the first three allow Oracle to discover the truth about the cost of multi block reads. And in fact, the CPU speed allows Oracle to work out the CPU cost of unsuitable access mechanisms like reading every single row in a block to find a specific data value and behave accordingly.

When you migrate to version 9, one of the first things you should investigate is the correct use of system statistics. This one feature alone may reduce the amount of time you spend trying to "tune" awkward SQL.

In passing, despite the wonderful effect of system statistics both of the optimizer adjusting parameters still apply — although the exact formula for their use seems to have changed between version 8 and version 9.
Variations on a Theme

Of course, I have picked one very special case — equality on a single column non-unique index, where thare are no nulls in the table — and treated it very simply. (I haven't even mentioned the relevance of the index blevel and clustering_factor yet.) There are numerous different strategies that Oracle uses to work out more general cases.

Consider some of the cases I have conveniently overlooked:

+ Multi-column indexes
+ Part-used multi-column indexes
+ Range scans
+ Unique indexes
+ Non-unique indexes representing unique constraints
+ Index skip scans
+ Index only queries
+ Bitmap indexes
+ Effects of nulls

The list goes on and on. There is no one simple formula that tells you how Oracle works out a cost — there is only a general guideline that gives you the flavour of the approach and a list of different formulae that apply in different cases.

However, the purpose of this article was to make you aware of the general approach and the two assumptions built into the optimiser's strategy. And I hope that this may be enough to take you a long way down the path of understanding the (apparently) strange things that the optimiser has been known to do.

Oracle Monitoring and Performance Tuning FAQ

Topics
Why and when should one tune performance?
What database aspects should be monitored?
Where should the tuning effort be directed?
What tuning indicators can one use?
What tools/utilities does Oracle provide to assist with performance tuning?
What is STATSPACK and how does one use it?
When is cost based optimization triggered?
How can one optimize %XYZ% queries?
Where can one find I/O statistics per table?
My query was fine last week and now it is slow. Why?
Why is Oracle not using the damn index?
When should one rebuild an index?
How does one tune Oracle Wait events?
What is the difference between DBFile Sequential and Scattered Reads?
Where can one get more info about Oracle Tuning?

--------------------------------------------------------------------------------
Back to top of file
--------------------------------------------------------------------------------

What database aspects should be monitored?
One should implement a monitoring system to constantly monitor the following aspects of a database. This can be achieved by writing custom scripts, implementing Oracle's Enterprise Manager, or buying a third-party monitoring product. If an alarm is triggered, the system should automatically notify the DBA (e-mail, page, etc.) to take appropriate action.
Infrastructure availability:

Is the database up and responding to requests
Are the listeners up and responding to requests
Are the Oracle Names and LDAP Servers up and responding to requests
Are the Web Listeners up and responding to requests
Etc.
Things that can cause service outages:

Is the archive log destination filling up?
Objects getting close to their max extents
Tablespaces running low on free space/ Objects what would not be able to extend
User and process limits reached
Etc.
Things that can cause bad performance:

See question "What tuning indicators can one use?".

Back to top of file
--------------------------------------------------------------------------------

Where should the tuning effort be directed?
Consider the following areas for tuning. The order in which steps are listed needs to be maintained to prevent tuning side effects. For example, it is no good increasing the buffer cache if you can reduce I/O by rewriting a SQL statement.
Database Design (if it's not too late):
Poor system performance usually results from a poor database design. One should generally normalize to the 3NF. Selective denormalization can provide valuable performance improvements. When designing, always keep the "data access path" in mind. Also look at proper data partitioning, data replication, aggregation tables for decision support systems, etc.

Application Tuning:
Experience showed that approximately 80% of all Oracle system performance problems are resolved by coding optimal SQL. Also consider proper scheduling of batch tasks after peak working hours.

Memory Tuning:
Properly size your database buffers (shared_pool, buffer cache, log buffer, etc) by looking at your buffer hit ratios. Pin large objects into memory to prevent frequent reloads.

Disk I/O Tuning:
Database files needs to be properly sized and placed to provide maximum disk subsystem throughput. Also look for frequent disk sorts, full table scans, missing indexes, row chaining, data fragmentation, etc.

Eliminate Database Contention:
Study database locks, latches and wait events carefully and eliminate where possible.

Tune the Operating System:
Monitor and tune operating system CPU, I/O and memory utilization. For more information, read the related Oracle FAQ dealing with your specific operating system.


Back to top of file
--------------------------------------------------------------------------------

What tuning indicators can one use?
The following high-level tuning indicators can be used to establish if a database is performing optimally or not:
Buffer Cache Hit Ratio
Formula: Hit Ratio = (Logical Reads - Physical Reads) / Logical Reads
Action: Increase DB_CACHE_SIZE (DB_BLOCK_BUFFERS prior to 9i) to increase hit ratio

Library Cache Hit Ratio
Action: Increase the SHARED_POOL_SIZE to increase hit ratio

Etc.

Back to top of file
--------------------------------------------------------------------------------

What tools/utilities does Oracle provide to assist with performance tuning?
Oracle provide the following tools/ utilities to assist with performance monitoring and tuning:
TKProf

UTLBSTAT.SQL and UTLESTAT.SQL - Begin and end stats monitoring

Statspack

Oracle Enterprise Manager - Tuning Pack


Back to top of file
--------------------------------------------------------------------------------

What is STATSPACK and how does one use it?
Statspack is a set of performance monitoring and reporting utilities provided by Oracle from Oracle8i and above. Statspack provides improved BSTAT/ESTAT functionality, though the old BSTAT/ESTAT scripts are still available. For more information about STATSPACK, read the documentation in file $ORACLE_HOME/rdbms/admin/spdoc.txt.
Install Statspack:

cd $ORACLE_HOME/rdbms/admin
sqlplus "/ as sysdba" @spdrop.sql -- Install Statspack -
sqlplus "/ as sysdba" @spcreate.sql -- Enter tablespace names when prompted

Use Statspack:
sqlplus perfstat/perfstat
exec statspack.snap; -- Take a performance snapshots
exec statspack.snap;

-- Get a list of snapshots
select SNAP_ID, SNAP_TIME from STATS$SNAPSHOT;

@spreport.sql -- Enter two snapshot id's for difference report

Other Statspack Scripts:
sppurge.sql - Purge a range of Snapshot Id's between the specified begin and end Snap Id's
spauto.sql - Schedule a dbms_job to automate the collection of STATPACK statistics
spcreate.sql - Installs the STATSPACK user, tables and package on a database (Run as SYS).
spdrop.sql - Deinstall STATSPACK from database (Run as SYS)
sppurge.sql - Delete a range of Snapshot Id's from the database
spreport.sql - Report on differences between values recorded in two snapshots
sptrunc.sql - Truncates all data in Statspack tables

Back to top of file
--------------------------------------------------------------------------------

When is cost based optimization triggered?
It's important to have statistics on all tables for the CBO (Cost Based Optimizer) to work correctly. If one table involved in a statement does not have statistics, Oracle has to revert to rule-based optimization for that statement. So you really want for all tables to have statistics right away; it won't help much to just have the larger tables analyzed.
Generally, the CBO can change the execution plan when you:

Change statistics of objects by doing an ANALYZE;
Change some initialization parameters (for example: hash_join_enabled, sort_area_size, db_file_multiblock_read_count).

Back to top of file
--------------------------------------------------------------------------------

How can one optimize %XYZ% queries?
It is possible to improve %XYZ% queries by forcing the optimizer to scan all the entries from the index instead of the table. This can be done by specifying hints.
If the index is physically smaller than the table (which is usually the case) it will take less time to scan the entire index than to scan the entire table.


Back to top of file
--------------------------------------------------------------------------------

Where can one find I/O statistics per table?
The UTLESTAT report shows I/O per tablespace but one cannot see what tables in the tablespace has the most I/O.
The $ORACLE_HOME/rdbms/admin/catio.sql script creates a sample_io procedure and table to gather the required information. After executing the procedure, one can do a simple SELECT * FROM io_per_object; to extract the required information.

For more details, look at the header comments in the $ORACLE_HOME/rdbms/admin/catio.sql script.


Back to top of file
--------------------------------------------------------------------------------

My query was fine last week and now it is slow. Why?
The likely cause of this is because the execution plan has changed. Generate a current explain plan of the offending query and compare it to a previous one that was taken when the query was performing well. Usually the previous plan is not available.
Some factors that can cause a plan to change are:

Which tables are currently analyzed? Were they previously analyzed? (ie. Was the query using RBO and now CBO?)
Has OPTIMIZER_MODE been changed in INIT.ORA?
Has the DEGREE of parallelism been defined/changed on any table?
Have the tables been re-analyzed? Were the tables analyzed using estimate or compute? If estimate, what percentage was used?
Have the statistics changed?
Has the INIT.ORA parameter DB_FILE_MULTIBLOCK_READ_COUNT been changed?
Has the INIT.ORA parameter SORT_AREA_SIZE been changed?
Have any other INIT.ORA parameters been changed?
What do you think the plan should be? Run the query with hints to see if this produces the required performance.

Back to top of file
--------------------------------------------------------------------------------

Why is Oracle not using the damn index?
This problem normally only arises when the query plan is being generated by the Cost Based Optimizer. The usual cause is because the CBO calculates that executing a Full Table Scan would be faster than accessing the table via the index. Fundamental things that can be checked are:
USER_TAB_COLUMNS.NUM_DISTINCT - This column defines the number of distinct values the column holds.
USER_TABLES.NUM_ROWS - If NUM_DISTINCT = NUM_ROWS then using an index would be preferable to doing a FULL TABLE SCAN. As the NUM_DISTINCT decreases, the cost of using an index increase thereby making the index less desirable.
USER_INDEXES.CLUSTERING_FACTOR - This defines how ordered the rows are in the index. If CLUSTERING_FACTOR approaches the number of blocks in the table, the rows are ordered. If it approaches the number of rows in the table, the rows are randomly ordered. In such a case, it is unlikely that index entries in the same leaf block will point to rows in the same data blocks.
Decrease the INIT.ORA parameter DB_FILE_MULTIBLOCK_READ_COUNT - A higher value will make the cost of a FULL TABLE SCAN cheaper.
Remember that you MUST supply the leading column of an index, for the index to be used (unless you use a FAST FULL SCAN or SKIP SCANNING).
There are many other factors that affect the cost, but sometimes the above can help to show why an index is not being used by the CBO. If from checking the above you still feel that the query should be using an index, try specifying an index hint. Obtain an explain plan of the query either using TKPROF with TIMED_STATISTICS, so that one can see the CPU utilization, or with AUTOTRACE to see the statistics. Compare this to the explain plan when not using an index.


Back to top of file
--------------------------------------------------------------------------------

When should one rebuild an index?
You can run the 'ANALYZE INDEX VALIDATE STRUCTURE' command on the affected indexes - each invocation of this command creates a single row in the INDEX_STATS view. This row is overwritten by the next ANALYZE INDEX command, so copy the contents of the view into a local table after each ANALYZE. The 'badness' of the index can then be judged by the ratio of 'DEL_LF_ROWS' to 'LF_ROWS'.

Back to top of file
--------------------------------------------------------------------------------

How does one tune Oracle Wait events?
Some wait events from V$SESSION_WAIT and V$SYSTEM_EVENT views:
Event Name: Tuning Recommendation:

db file sequential read Tune SQL to do less I/O. Make sure all objects are analyzed. Redistribute I/O across disks.
buffer busy waits Increase DB_CACHE_SIZE (DB_BLOCK_BUFFERS prior to 9i)/ Analyze contention from SYS.V$BH
log buffer space Increase LOG_BUFFER parameter or move log files to faster disks



Back to top of file
--------------------------------------------------------------------------------

What is the difference between DBFile Sequential and Scattered Reads?
Both "db file sequential read" and "db file scattered read" events signify time waited for I/O read requests to complete. Time is reported in 100's of a second for Oracle 8i releases and below, and 1000's of a second for Oracle 9i and above. Most people confuse these events with each other as they think of how data is read from disk. Instead they should think of how data is read into the SGA buffer cache.
db file sequential read:

A sequential read operation reads data into contiguous memory (usually a single-block read with p3=1, but can be multiple blocks). Single block I/Os are usually the result of using indexes. This event is also used for rebuilding the controlfile and reading datafile headers (P2=1). In general, this event is indicative of disk contention on index reads.

db file scattered read:

Similar to db file sequential reads, except that the session is reading multiple data blocks and scatters them into different discontinuous buffers in the SGA. This statistic is NORMALLY indicating disk contention on full table scans. Rarely, data from full table scans could be fitted into a contiguous buffer area, these waits would then show up as sequential reads instead of scattered reads.

The following query shows average wait time for sequential versus scattered reads:

prompt "AVERAGE WAIT TIME FOR READ REQUESTS"
select a.average_wait "SEQ READ", b.average_wait "SCAT READ"
from sys.v_$system_event a, sys.v_$system_event b
where a.event = 'db file sequential read'
and b.event = 'db file scattered read';

Back to top of file
--------------------------------------------------------------------------------

Where can one get more info about Oracle Tuning?
Tuning Information:

This FAQ: Tuning Scripts

This FAQ: Tuning Papers

Ixora = Unix + Oracle - scripts, tips and advanced performance tuning information

Kevin Loney's New DBA FAQs: Part 2 - Database Monitoring

On-Line Services:

Yet Another Performance Profiling (YAPP) Method - Upload your BSTAT/ ESTAT and StatsPack output for analysis

itrprof SQL Analyzer - Upload your SQL_TRACE/Event10046 trace files to find bottlenecks and tuning advice