Translate

Showing posts sorted by relevance for query optimizer. Sort by date Show all posts
Showing posts sorted by relevance for query optimizer. Sort by date Show all posts

Optimizer Modes(CHOOSE,ALL_ROWS,FIRST_ROWS,FIRST_ROWS(n),RULE)

What are Optimizer Modes available in ORACLE?

Within two Optimizers RBO and CBO, Oracle provides several optimizer modes. An optimizer mode may be set for the entire database, or it may be set for a specific Oracle session.

ORACLE OPTIMIZER- RBO/CBO

How OPTIMIZER works in ORACLE? What is RULE BASED and COST BASED OPTIMIZATION in ORACLE.

The optimizer determines the most efficient way to execute a SQL statement after considering many factors related to the objects referenced and the conditions specified in the query. This determination is an important step in the processing of any SQL statement and can greatly affect execution time.

IMG Source- https://docs.oracle.com
RBO- Rule Based Optimizer – Whenever we execute any SQL, the Optimizer will use execution plan based on some predefined rules. So, Query should be matched with the rules specified by RBO if not matched then it will result in Full Table scan. It works just like a Machine works on some rules and instructions.

The functionality is still present but no new functionality has been included in it and it is no longer supported by Oracle. It is only present to provide backwards compatibility during the migration to the query optimizer (Cost Based Optimizer).

CBO- Cost Based Optimizer – CBO uses Artificial Intelligence to decide the Execution Plan for SQL Query based on the Query Statistics. The cost-based method means the database must decide which query execution plan to choose using best guess approach that takes into account what data is stored in db. The Oracle cost-based optimizer is designed to determine the most efficient way to carry out a SQL statement, but it can’t reach do this without good, up-to-date statistical information on the data being accessed.

With the cost-based approach, the optimizer factors in statistical information about the contents of the particular schema objects (tables, clusters, or indexes) being accessed. 

Earlier, the only optimizer in the Oracle database was the Rule-Based Optimizer (RBO). Basically, the RBO used a set of rules to determine how to execute a query. If an index was available on a table, the RBO rules said to always use the index. There are some cases where the use of an index slowed down a query.

For Example: 
There is an index on the GENDER column which holds one of two values MALE and FEMALE. Then someone issues the following query:  
SELECT * FROM Employee WHERE gender 'FEMALE';

If the above query returned approximately 50 of the rows, then using an index would actually slow things down. It would be faster to read the entire table and throw away all rows that have MALE values. Rule of thumb says if the number of rows returned is more than 5-10 of the total table volume using an index would slow things down. The RBO would always use an index if present because its rules said to. 

The biggest problem with the RBO was that it did not take the data distribution into account. So, the Cost-Based Optimizer (CBO) was came into picture. The CBO uses statistics about the table its indexes and the data distribution to make better informed decisions. 

Now, assume that the company has employees that are 95 females and 5 males. If you query for females, then you do not want to use the index. If you query for males, then you would like to use the index. The CBO has information at hand to help make these kinds of determinations that were not available in the Rule Based Optimization. 
SQL processing uses the following main components to execute a SQL query:
  • The Parser checks both syntax and semantic analysis.
  • The Optimizer uses costing methods, cost-based optimizer (CBO), or internal rules, rule-based optimizer (RBO), to determine the most efficient way of producing the result of the query.
  • The Row Source Generator receives the optimal plan from the optimizer and outputs the execution plan for the SQL statement.
  • The SQL Execution Engine operates on the execution plan associated with a SQL statement and then produces the results of the query.
A SQL statement can be executed in many different ways, including the following:
  • Full table scans
  • Index scans
  • Nested loops
  • Hash joins

Full Table Scans

This type of scan reads all rows from a table and filters out those that do not meet the selection criteria. During a full table scan, all blocks in the table that are under the high-water mark are scanned. Each row is examined to determine whether it satisfies the statement's WHERE clause.
When Oracle performs a full table scan, the blocks are read sequentially. Because the blocks are adjacent, I/O calls larger than a single block can be used to speed up the process.

Why a Full Table Scan Is Faster for Accessing Large Amounts of Data?

Full table scans are cheaper than index range scans when accessing a large fraction of the blocks in a table. This is because full table scans can use larger I/O calls, and making fewer large I/O calls is cheaper than making many smaller calls.

When the Optimizer Uses, Full Table Scans?
The optimizer uses a full table scan in any of the following cases:
·       Lack of Index or Indexed column used with Functions: If the query is unable to use any existing indexes, then it uses a full table scan. For example, if there is a function used on the indexed column in the query, the optimizer is unable to use the index and instead uses a full table scan
SELECT FIRST_NAME, LAST_NAME FROM Employees
WHERE UPPER(LAST_NAME) LIKE :B1; /*Even if there is an Index on LAST_NAME it will not be used by ORACLE*/ 
·       Access Large Amount of Data: If the optimizer thinks that the query will access most of the blocks in the table, then it uses a full table scan, even though indexes might be available. 
·       High Degree of Parallelism: A high degree of parallelism for a table skews the optimizer toward full table scans over range scans. Examine the DEGREE column in ALL_TABLES for the table to determine the degree of parallelism.

Full Table Scan Hints

FULL hint in ORACLE is used if you want to force the use of a full table scan. 

Without Using the FULL Hint
SQL> SELECT EMP_NO, ENAME
  2  From EMPLOYEES
  3  Where DEPTNO IN(10,20);

    EMP_NO ENAME
---------- ----------
      1001 Ravi
      1002 Surya
      1006 Rajan
      1007 Manu
      1008 Karan


Execution Plan
----------------------------------------------------------
Plan hash value: 522316573

------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |     3 |    42 |     2   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR             |           |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES |     3 |    42 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IND_DEP   |     3 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   3 - access("DEPTNO"=10 OR "DEPTNO"=20)


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

After Using the FULL Hint
SQL> SELECT /*+ FULL(EMP) */  EMP_NO, ENAME
  2  From EMPLOYEES EMP
  3  Where DEPTNO IN(10,20);

    EMP_NO ENAME
---------- ----------
      1001 Ravi
      1002 Surya
      1006 Rajan
      1007 Manu
      1008 Karan


Execution Plan
----------------------------------------------------------
Plan hash value: 1445457117

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     3 |    42 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMPLOYEES |     3 |    42 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

   1 - filter("DEPTNO"=10 OR "DEPTNO"=20)


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


Index Scans

In Index Scans rows are accessed and retrieved using an Index on column values specified by the statement. An index scan retrieves data from an index based on the value of one or more columns in the index. To perform an index scan, Oracle searches the index for the indexed column values accessed by the statement.
Index Scan Hints
A hint might be required if the optimizer chooses some other index or uses a full table scan. The hint INDEX(table_alias index_name) specifies the index to use.

SQL> SELECT /*+ INDEX(EMP IND_DEP) */  EMP_NO, ENAME, SALARY, DEPTNO, DNAME
  2  From EMPLOYEES EMP
  3  Where DEPTNO IN(10,20,30);

    EMP_NO ENAME          SALARY     DEPTNO DNAME
---------- ---------- ---------- ---------- ----------
      1001 Ravi            48000         10 ACCOUNTS
      1002 Surya           89000         20 SALES
      1006 Rajan           77770         20 SALES
      1007 Manu            98888         20 BANKING
      1008 Karan           48000         20 BANKING
      1003 Ankit           89000         30 ACCOUNTS

6 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 522316573

------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |     5 |   135 |     2   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR             |           |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES |     5 |   135 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IND_DEP   |     5 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   3 - access("DEPTNO"=10 OR "DEPTNO"=20 OR "DEPTNO"=30)


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

Check another example of INDEX Range Scan Descending:


An index range scan descending is identical to an index range scan, except that the data is returned in descending order. Indexes, by default, are stored in ascending order. Usually, this scan is used when ordering data in a descending order to return the most recent data first, or when seeking a value less than a specified value.


SQL> SELECT /*+ INDEX_DESC(EMP IND_EMPNO) */  EMP_NO, ENAME, SALARY, DEPTNO, DNAME
  2  From EMPLOYEES EMP
  3  Where EMP_NO<1006 Order By EMP_NO DESC;

    EMP_NO ENAME          SALARY     DEPTNO DNAME
---------- ---------- ---------- ---------- ----------
      1005 Pritesh         48000         50 ACCOUNTS
      1004 Nikhil          77770         40 SALES
      1003 Ankit           89000         30 ACCOUNTS
      1002 Surya           89000         20 SALES
      1001 Ravi            48000         10 ACCOUNTS


Execution Plan
----------------------------------------------------------
Plan hash value: 507161224

------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |     6 |   162 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID | EMPLOYEES |     6 |   162 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN DESCENDING| IND_EMPNO |     6 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   2 - access("EMP_NO"<1006)


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


When the Optimizer Uses, Nested Loop Joins

The optimizer uses nested loop joins when joining small number of rows, with a good driving condition between the two tables. You drive from the outer loop to the inner loop, so the order of tables in the execution plan is important.

The outer loop is the driving row source. It produces a set of rows for driving the join condition. The row source can be a table accessed using an index scan or a full table scan. Also, the rows can be produced from any other operation. For example, the output from a nested loop join can be used as a row source for another nested loop join.

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

Nested Loop Join Hints

If the optimizer is choosing to use some other join method, you can use the USE_NL(table1 table2) hint, where table1 and table2 are the aliases of the tables being joined.
The USE_NL hint causes Oracle to join each specified table to another row source with a nested loop join, using the specified table as the inner table.

 SELECT /*+ USE_NL(E D) */ E.EMP_NO, E.ENAME, D.DEPTNAME

    From EMP_TEST E, DEPT_TEST D

    Where E.DEPTNO=D.DEPTNO;


Hash Joins

Hash joins are used for joining large data sets. The optimizer uses the smaller of two tables or data sources to build a hash table on the join key in memory. It then scans the larger table, probing the hash table to find the joined rows.

This method is best used when the smaller table fits in available memory. The cost is then limited to a single read pass over the data for the two tables.

Essentially, a hash join is a technique whereby Oracle loads the rows from the driving table (the smallest table, first after the where clause). Oracle then uses a hashing technique to locate the rows in the larger second table. A hash join is often combined with parallel query in cases where both tables are very large.

When the Optimizer Uses, Hash Joins

The optimizer uses a hash join to join two tables if they are joined using an equijoin and if either of the following conditions are true:
  • A large amount of data needs to be joined.
  • A large fraction of the table needs to be joined.

Hash Join Hints


Apply the USE_HASH hint to advise the optimizer to use a hash join when joining two tables together. If you are having trouble getting the optimizer to use hash joins, investigate the values for the HASH_AREA_SIZE and HASH_JOIN_ENABLED parameters.

SQL> SELECT /*+ Use_Hash(E,D) */ E.EMP_NO, E.ENAME, D.DEPTNAME
  2  From EMP_TEST E, DEPT_TEST D
  3  Where E.DEPTNO=D.DEPTNO;

    EMP_NO ENAME      DEPTNAME
---------- ---------- --------------------
      1001 Ravi       Accounts
      1002 Surya      Retail
      1003 Ankit      Insurance
      1004 Nikhil     Banking
      1005 Pritesh    Cloud
      1006 Rajan      Retail
      1007 Manu       Retail
      1008 Karan      Retail

8 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2815500728

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     8 |   200 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN         |           |     8 |   200 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT_TEST |     5 |    55 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP_TEST  |     8 |   112 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------

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

   1 - access("E"."DEPTNO"="D"."DEPTNO")


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

Hash joins are often faster than nested loop joins, especially in cases where the driving table is filtered into a small number of rows in the query's where clause.

In general, nested loop joins work best when there are useful indexes and the percentage of data returned is small. Hash joins work best when there are no useful indexes or when the join will return a large percentage of rows. It is usually better to avoid hints and let Oracle decide how to build an execution plan.




Get involved and leave your Comments in the Box Below. The more people get involved, the more we all benefit. So, leave your thoughts before you leave the page.

Dynamic sampling and its impact on Optimizer

What is Dynamic sampling and its impact on the Optimizer and how it helps generating better Execution Plan?

The Oracle optimizer makes decisions based on the statistics stored in the data dictionary for the objects referenced by a given query. Without valid statistics, the optimizer often makes poor choices when picking an execution plan. 
The main objective of dynamic sampling is to create more accurate selectivity and cardinality estimates, which, in turn, helps the CBO generate faster execution plans. 
Oracle optimizer use the default statistic values when table does not have any statistics and dynamic sampling query feature is disabled. Optimizer Dynamic Sampling has 11 levels between 0 to 10. The default setting is 2. This setting ensures that the optimizer will have a good estimate whenever it optimizes a query that accesses a table with no statistics. Database will perform dynamic sampling when existing statistics are missing, unusable or insufficient (unless dynamic sampling has been turned off entirely).


NOTE: Optimizer will not perform dynamic sampling on Remote tables or External Tables.

How Dynamic Sampling Works
There are two ways to use dynamic sampling:
  • The OPTIMIZER_DYNAMIC_SAMPLING parameter can be set at the database instance level and can also be overridden at the session level with the ALTER SESSION command.
  • The DYNAMIC_SAMPLING query hint can be added to specific queries. DYNAMIC_SAMPLING hint can also be used to invoke dynamic sampling for a single statement. 
Here are the criteria levels for OPTIMIZER_DYNAMIC_SAMPLING:
Level
When Dynamic Sampling will be used
Sample size (blocks)
0
Do not use dynamic sampling.
N/A
1
Use dynamic sampling with a 32-block sample size for all tables that have not been analysed, but only if the unanalysed table has more than 32 blocks, no indexes and is not partitioned.
32
2 (default)
Use dynamic sampling with a 64-block sample size if at least one table in the statement has no statistics. This is the default.
64
3
Use dynamic sampling with a 64-block sample size if the statement meets level 2 criteria or has one or more expressions used in the WHERE clause predicates. Example: Where SUBSTR(CUSTLASTNAME,1,3) or Where a + b =5
64
4
Use dynamic sampling with a 64-block sample size if the statement meets level 3 criteria or uses complex predicates (an OR or AND operator between multiple predicates on the same table).
64
5
Any statement that meets level 4 criteria
128
6
Any statement that meets level 4 criteria
256
7
Any statement that meets level 4 criteria
512
8
Any statement that meets level 4 criteria
1024
9
Any statement that meets level 4 criteria
4086
10
Use dynamic sampling of all blocks for all statements.
All Blocks


Consider below Table:
SQL> CREATE Table MyTemp AS
SELECT OWNER, OBJECT_TYPE
FROM ALL_OBJECTS;
Table created.

SQL> SELECT Count(*) From MyTemp;
  COUNT(*)
----------
     71879



Let’s disable the Dynamic Sampling by DYNAMIC_SAMPLING hint, with a level of zero and see the Execution Plan.

Disabling dynamic sampling to see default cardinalities
SQL> SET AUTOTRACE TRACEONLY EXP
SQL> SELECT /*+ Dynamic_Sampling(MyTemp,0) */ *
  2  From MyTemp;
Execution Plan
----------------------------------------------------------
Plan hash value: 2234051319
----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        | 16908 |   462K|    58   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| MYTEMP | 16908 |   462K|    58   (0)| 00:00:01 |


As you can see, the estimated cardinality is 16,906, which is very far off from the real cardinality, about 71,879. If I permit dynamic sampling, I get a much more realistic cardinality estimate.

More-realistic cardinalities with dynamic sampling enabled
SQL> SELECT * From MyTemp;
Execution Plan
----------------------------------------------------------
Plan hash value: 2234051319

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        | 82504 |  2255K|    59   (2)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| MYTEMP | 82504 |  2255K|    59   (2)| 00:00:01 |
----------------------------------------------------------------------------
Note
-----
 - dynamic sampling used for this statement (level=2)


Now, 82504 is much closer to 71,879. An inaccurate cardinality estimate can swing either way. We have seen the optimizer radically underestimating the cardinality, but it can overestimate as well. 


Overestimating cardinalities
SQL> DELETE From MyTemp;
71879 rows deleted.
SQL> COMMIT;
Commit complete.

SQL> SET AUTOTRACE TRACEONLY EXP
SQL> SELECT /*+ Dynamic_Sampling(MyTemp,0) */ *
  2  From MyTemp;
Execution Plan
----------------------------------------------------------
Plan hash value: 2234051319
----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        | 16908 |   462K|    58   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| MYTEMP | 16908 |   462K|    58   (0)| 00:00:01 |
----------------------------------------------------------------------------
SQL> SELECT * From MyTemp;

Execution Plan
----------------------------------------------------------
Plan hash value: 2234051319
----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |     1 |    28 |    58   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| MYTEMP |     1 |    28 |    58   (0)| 00:00:01 |
----------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)




Think about what might happen if the optimizer guessed 16,908 rows instead of 1 row in this case. For queries accessing table MyTemp, the optimizer would grossly overestimate the rows that will be returned from MyTemp and generate incorrect plans as a result.


Poor Cardinality Estimates 


Creating the “FLAG” table and gathering statistics

SQL> CREATE Table MyTable
  2  AS SELECT
  3  DECODE(MOD(ROWNUM,2),0,'N','Y') Flag1,
  4  DECODE(MOD(ROWNUM,2),0,'Y','N') Flag2, AO.*
  5  FROM ALL_OBJECTS AO;
Table created.

SQL> SELECT Count(*) From MyTable;
  COUNT(*)
----------
     71880

SQL> CREATE INDEX T_IND ON MyTable(Flag1,Flag2);
Index created.

SQL> BEGIN
  2  DBMS_STATS.GATHER_TABLE_STATS(USER,'MYTABLE',
  3  METHOD_OPT=>'For All Indexed Columns Size 254');
  4  END;
  5  /
PL/SQL procedure successfully completed.

SQL> SET AUTOTRACE TRACEONLY EXP
SQL> SELECT * From MyTable Where Flag1='N' And Flag2='N';

Execution Plan
----------------------------------------------------------
Plan hash value: 1015944200
-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         | 17969 |  1772K|   299   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| MYTABLE | 17969 |  1772K|   299   (1)| 00:00:04 |
-----------------------------------------------------------------------------

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

   1 - filter("FLAG1"='N' AND "FLAG2"='N')



Let’s use Dynamic Sampling
SQL> SELECT /*+ Dynamic_Sampling(MyTable 3) */  * From MyTable Where Flag1='N' And Flag2='N';
Execution Plan
----------------------------------------------------------
Plan hash value: 270124127
---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     7 |   707 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| MYTABLE |     7 |   707 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_IND   |     7 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("FLAG1"='N' AND "FLAG2"='N')
Note
-----
- dynamic sampling used for this statement (level=2)




Note the much better row estimate (7 instead of more than 17,959) , also note the very different (and now optimal) plan. Instead of a full table scan, the optimizer has decided to use the index, and this execution plan will definitely be optimal compared to the original full table scan, because in reality, no data will be returned. The index will discover that instantly.
So, not all queries can benefit from Oracle dynamic sampling. Queries with very fast execution times or ad-hoc queries that will not be repeated are not good candidates. Here are the situations that can benefit most from dynamic sampling:
·       A query with complex predicates that is generating a poor execution plan.
·       Queries with execution times that are significantly longer than the time required to perform dynamic sampling.
·       Queries that will be repeatedly executed on the database.
 ·       If the available statistics are not enough to generate a good execution plan, dynamic sampling will be used.





Get involved and leave your Comments in the Box Below. The more people get involved, the more we all benefit. So, leave your thoughts before you leave the page.
Source- AskTom 

NOT NULL and INDEXED Column

How INDEXED Columns behaves when not defined as NOT NULL?

Sometimes, while Declaring Table columns we do not declare them as NOT NULL even if we know that column will never be NULL. In such cases Optimizer has to assume that column can accept NULL values while generating Execution Plan which results in Full Table Scan.

Let’s check this with the help of below Example:
I have a Table below without any Constraint and Index.
SQL> DESC EMPLOYEE_DATA;
 Name                                                                    Null?    Type
 ----------------------------------------------------------------------- -------- ------------------------------------------------
 EMP_NO                                                                           NUMBER
 ENAME                                                                            VARCHAR2(10)
 DEPTNO                                                                           NUMBER
 HIREDATE                                                                         DATE
 DNAME                                                                            VARCHAR2(10)
 DLOC                                                                             VARCHAR2(10)
 SALARY                                                                           NUMBER(8,2)
 JOB_ID                                                                           VARCHAR2(20)

SQL> SET AUTOTRACE TRACENLY EXP
SQL> SELECT * From EMPLOYEE_DATA Where HIREDATE IS NULL;
Execution Plan
----------------------------------------------------------
Plan hash value: 4276744253
-----------------------------------------------------------------------------------
| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |     1 |    81 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMPLOYEE_DATA |     1 |    81 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("HIREDATE" IS NULL)

Note
-----
   - dynamic sampling used for this statement (level=2)
In above statement, Full Access path was followed which was obvious and expected that Optimizer will use Full Table Scan as there were no Index no Constraint been used.


Let’s create an Index on Above Table assuming that now Optimizer will use Index Scan Path.

SQL> CREATE INDEX INDX_HD_NAME ON EMPLOYEE_DATA(HIREDATE,ENAME);
Index created.

SQL> SELECT * From EMPLOYEE_DATA Where HIREDATE IS NULL;
Execution Plan
----------------------------------------------------------
Plan hash value: 4276744253
-----------------------------------------------------------------------------------
| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |     1 |    81 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMPLOYEE_DATA |     1 |    81 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("HIREDATE" IS NULL)
Note
-----
   - dynamic sampling used for this statement (level=2)


So, even after creating Index on Column it has followed Full Scan access path. Why?
The above index supports the query only if the column ENAME has a NOT NULL constraint. 

Removing NOT NULL Constraint renders the Index Unusable state for the above Query.

Let’s try declaring ENAME column as NOT NULL and see how it helps in generating better execution plan.
SQL> Alter table EMPLOYEE_DATA modify ENAME NOT NULL;
Table altered.

SQL> DESC EMPLOYEE_DATA;
 Name                                                                    Null?    Type
 ----------------------------------------------------------------------- -------- ------------------------------------------------
 EMP_NO                                                                           NUMBER
 ENAME                                                                   NOT NULL VARCHAR2(10)
 DEPTNO                                                                           NUMBER
 HIREDATE                                                                         DATE
 DNAME                                                                            VARCHAR2(10)
 DLOC                                                                             VARCHAR2(10)
 SALARY                                                                           NUMBER(8,2)
 JOB_ID                                                                           VARCHAR2(20)

SQL> SELECT * From EMPLOYEE_DATA Where HIREDATE IS NULL;
Execution Plan
----------------------------------------------------------
Plan hash value: 1371098230
---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 |    81 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEE_DATA |     1 |    81 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | INDX_HD_NAME  |     2 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("HIREDATE" IS NULL)
Note
-----
   - dynamic sampling used for this statement (level=2)

As you can see Optimizer has followed Index Scan path as Optimizer doesn’t need to Assume about NULLs.


Let’s do the same Exercise with Function returning your Table column instead of directly using them in your Query.
SQL> Create Index INDX_EMPNO ON EMPLOYEE_DATA(EMP_NO);
Index created.

SQL> CREATE OR REPLACE FUNCTION RETURN_EMPNO(EID IN NUMBER) RETURN NUMBER
DETERMINISTIC
IS BEGIN
RETURN EID;
END;
  /
Function created.

You will need to create DETERMINISITC Function when you know that function will always return Unique value. If we skip DETERMINISTIC in above function, then you will receive below error while referring it creating Index.

ORA-30553: The function is not deterministic


If the PL/SQL function is truly deterministic  (it always returns a unique value for all input values) then you can just add the "deterministic" keyword to the function definition. If the function is deterministic, mark it DETERMINISTIC. If it is not deterministic (it depends on package state, database state, current time, or anything other than the function inputs) then do not create the index. The values returned by a deterministic function should not change even when the function is rewritten or recompiled.
SQL> Drop Index INDX_HD_NAME;
Index dropped.
SQL> CREATE INDEX INDX_HD_NAME ON EMPLOYEE_DATA(HIREDATE,RETURN_EMPNO(EMP_NO));
Index created.

SQL> SET AUTOTRACE TRACEONLY EXP
SQL> SELECT * From EMPLOYEE_DATA Where HIREDATE IS NULL;
Execution Plan
----------------------------------------------------------
Plan hash value: 4276744253
-----------------------------------------------------------------------------------
| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |     1 |    81 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMPLOYEE_DATA |     1 |    81 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("HIREDATE" IS NULL)
Note
-----
   - dynamic sampling used for this statement (level=2)

We know that Function name RETURN_EMPNO will always return Unique and NOT NULL Values but Optimizer doesn’t have any idea what this function does. For Database, this is just a function which returns a Number. Although, we have Index created on EMP_NO but still Database can not use the Index for the Query.

So, if you know that Function never returns NULL, then you can change the Query to make use of Created Index by Optimizer.
SQL> SELECT * From EMPLOYEE_DATA Where HIREDATE IS NULL AND RETURN_EMPNO(EMP_NO) IS NOT NULL;
Execution Plan
----------------------------------------------------------
Plan hash value: 1371098230
---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 |    94 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEE_DATA |     1 |    94 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | INDX_HD_NAME  |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("HIREDATE" IS NULL)
       filter("MYUSER"."RETURN_EMPNO"("EMP_NO") IS NOT NULL)
Note
-----
   - dynamic sampling used for this statement (level=2)



The extra condition in the where clause is always true and therefore does not change the result. Nevertheless, the Oracle database recognizes that you only query rows that must be in the index per definition.




ORACLE Driving Tables

How ORACLE decides Driving Tables and how it traverse rest of the things after From clause?
The driving table is the table that is joined to other tables in an SQL query and the first table accessed when a query will run. This understanding will help you tune your Query in a better way.

It’s not like that your driving table will always be the first table in your From clause. In case of Rule Based Optimizer the From Clause would be traversed from Right to Left however this would not be the case with Cost Based Optimizer.
Let’s see how From clause will work in case we will force RBO to work on Execution Plan.

SQL> SELECT /*+ RULE */ * From EMPLOYEE_DETAILS E, DEPARTMENT D
Where E.DEPTNO=D.DEPTNO;

Execution Plan
----------------------------------------------------------
Plan hash value: 1994740439
------------------------------------------------
| Id  | Operation           | Name             |
------------------------------------------------
|   0 | SELECT STATEMENT    |                  |
|   1 |  MERGE JOIN         |                  |
|   2 |   SORT JOIN         |                  |
|   3 |    TABLE ACCESS FULL|DEPARTMENT        |
|*  4 |   SORT JOIN         |                  |
|   5 |    TABLE ACCESS FULL|EMPLOYEE_DETAILS|
------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("E"."DEPTNO"="D"."DEPTNO")
       filter("E"."DEPTNO"="D"."DEPTNO")
Note
-----
   - rule based optimizer used (consider using cbo)


Always put smallest tables at the end in your From clause.


Let’s create Index on DEPARTMENT(DEPTNO) and check the Execution Plan with Changed order.
SQL> Create Index INDX_DEPT on DEPARTMENT(DEPTNO);
Index created.
SQL> SELECT /*+ RULE */ * From DEPARTMENT D,EMPLOYEE_DETAILS E
 Where E.DEPTNO=D.DEPTNO;
Execution Plan
----------------------------------------------------------
Plan hash value: 1430033380
---------------------------------------------------------
| Id  | Operation                    | Name             |
---------------------------------------------------------
|   0 | SELECT STATEMENT             |                  |
|   1 |  NESTED LOOPS                |                  |
|   2 |   NESTED LOOPS               |                  |
|   3 |    TABLE ACCESS FULL         | EMPLOYEE_DETAILS |
|*  4 |    INDEX RANGE SCAN          | INDX_DEPT        |
|   5 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENT       |
---------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("E"."DEPTNO"="D"."DEPTNO")
Note
-----
   - rule based optimizer used (consider using cbo)


So, in this case EMPLOYEE_DETAILS table was taken as Driving Table as we created Index on DEPARTMENT(DEPTNO) Table. So, ORACLE will fetch all the ROWS from EMPLOYEE_DETAILS Table (Full Scan) and will compare it with DEPARTMENT Table and display the result.


Let’s check it with CBO with the same Order.
SQL> SELECT * From DEPARTMENT D, EMPLOYEE_DETAILS E
Where  E.DEPTNO=D.DEPTNO;
Execution Plan
----------------------------------------------------------
Plan hash value: 1365186588
-------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                  |     8 |   536 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN                  |                  |     8 |   536 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENT       |     5 |   125 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | INDX_DEPT        |     5 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |                  |     8 |   336 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | EMPLOYEE_DETAILS |     8 |   336 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("E"."DEPTNO"="D"."DEPTNO")
       filter("E"."DEPTNO"="D"."DEPTNO")
Note
-----
   - dynamic sampling used for this statement (level=2)


In this case, ORACLE takes DEPARTMENT as Driving Table and then search for matching records in EMPLOYEE_DETAILS Table.


Now, let’s explicitly force Optimizer to follow the Order.
SQL> SELECT /*+ ordered */ * From EMPLOYEE_DETAILS E, DEPARTMENT D
 Where  E.DEPTNO=D.DEPTNO;
Execution Plan
----------------------------------------------------------
Plan hash value: 124983262
---------------------------------------------------------------------------------------
| Id  | Operation          | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                  |     8 |   536 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN         |                  |     8 |   536 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMPLOYEE_DETAILS |     8 |   336 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| DEPARTMENT       |     5 |   125 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("E"."DEPTNO"="D"."DEPTNO")
Note
-----
   - dynamic sampling used for this statement (level=2)



If you change the Table ordering, then it will use the same order and first table will become your Driving Table.

SQL> SELECT /*+ ordered */ * From DEPARTMENT D, EMPLOYEE_DETAILS E
 Where  E.DEPTNO=D.DEPTNO;
Execution Plan
----------------------------------------------------------
Plan hash value: 1365186588
-------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                  |     8 |   536 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN                  |                  |     8 |   536 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENT       |     5 |   125 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | INDX_DEPT        |     5 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |                  |     8 |   336 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | EMPLOYEE_DETAILS |     8 |   336 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("E"."DEPTNO"="D"."DEPTNO")
       filter("E"."DEPTNO"="D"."DEPTNO")
Note
-----
   - dynamic sampling used for this statement (level=2)




So, ORACLE will use the Driving table which is based on the Cost and Cardinality and you can force the Optimizer to use the Driving Table as per your choice to make the Better Execution Plan as per your requirement.