Oracle SQL Query Tuning Training in Chennai for Database Developers, DBAs

Best Hadoop Training In Chennai

Best SQL Query Tuning Training in Chennai with Real-time Live Scenarios

Oracle SQL Tuning for Developers Workshop

Best Oracle Database SQL Query Tuning Training in Chennai

This Oracle SQL Tuning training in Chennai for Data Warehouse Developer, Developer, Database Designers, PL/SQL Developer will help you explore Oracle SQL statement tuning. , Learn how to write well-tuned SQL statements appropriate for the Oracle database.. Become A SQL Query Tuning Certified Professional & Fast-track Your Career.. We are also providing corporate training and online training all around world.

Awarded as the Best Oracle SQL Tuning Training Center in Chennai - We Guarantee Your SQL Query Tuning Training Success in Chennai

Expert SQL Query Tuning Training in Chennai topics taught by experienced certified professionals with extensive real-world experience. Expert instructors will also teach you how to rewrite queries for better performance. Furthermore, you'll learn how to utilize SQL Tuning Advisor using SQL Developer 3.0.


Best Software Apps DBA Training in ChennaiGreens Technology is a leading Training and Placement company in Chennai. We are known for our practical approach towards trainings that enable students to gain real-time exposure on competitive technologies. Trainings are offered by employees from MNCs to give a real corporate exposure.

This advanced Oracle SQL tuning is an intensive customized course designed to provide Oracle developers, programmers or analysts with an in-depth understanding of Oracle SQL tuning. Students will learn proven methods for optimizing their SQL and understand how to interpret execution plans for optimal performance. Training in Chennai Consulting instructors offer decades of real world DBA experience in Oracle features, and they will share their secrets in this intense Oracle SQL tuning training.

By the end of this course the student will be able to use advanced SQL techniques including query-requiting, using temporary tables, using hints and changing optimizer setting to achieve faster and more efficient SQL performance. correlated subqueries and outer joins. The student will also become familiar with all of the major SQL tuning techniques for Oracle, including the internals of the Oracle optimizers, materialized views, and techniques for tuning Oracle SQL statements for optimal performance. The overall goal for the class will be to leave the Oracle developer, programmer or analyst with the skills to expose a SQL execution plan, evaluate a plan for optimal execution and how to modify an execution plan for faster performance and throughput.


We undertake Classroom Training, Corporate Training and Video Based Training on latest Technologies on latest versions. We make sure that all our sessions are very much interactive and well structured. We encourage every participant to come up with his / her own queries during & after the training sessions. We prefer practical approach rather than theoretical information to master the technical depth of the technology. We provide excellent Lab Handouts for practice including Realtime Case Studies and Projects on ALL courses. Specific DAY to DAY Course Plan will also be shared prior to training registration to ensure transparency of our Training services.

Our Trainings are focused on perfect improvement of technical skills for Freshers and working professional. Our Training classes are sure to help the trainee with COMPLETE PRACTICAL TRAINING and Realtime methodologies.

Oracle Database Performance Tuning Courses Syllabus in Demand

Query optimization tips for Oracle


When we talk about query optimization, we basically wants to reduce response time for SQL Query processing and find more efficient way to process workload. One of the major way is make our queries to use indexes so that search time can be reduced.

We have compiled following basic but helpful tips for making a query to use indexes and optimizing a query.
1. Primary Key Search
2. Index on Foreign key
3. Simplified conditions
4. Use Outer Join avoid Not In
5. OR vs UNION
6. Function Based Index
7. Handling NULL Values
8. Index Hints and LIKE

Click on the above links for examples.

Scaling Hardware may hide bad code but a bigger hardware now will be insufficient tomorrow, when people are writing not optimized code. Code Optimization is must for survival of a system but we also need to understand that "Maruti 800" cannot be tuned into a "Ferrari".


Primary Key Search

Oracle Query Optimization - Single table

If we are using a query on single table with where clause on a column,
it can be simply optimized by creating an Index. 
If the query is fetching near 10% data, index would be used by query.

Example:

SQL> set autot trace
SQL> SELECT * FROM MYEMP WHERE EMPNO=7839;

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 2418123361

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

SQL> CREATE INDEX MYEMP_EMPNO_INDX ON MYEMP(EMPNO);

Index created.

Elapsed: 00:00:00.07
SQL> SELECT * FROM MYEMP WHERE EMPNO=7839;

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 254813117

------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |     1 |    87 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| MYEMP            |     1 |    87 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | MYEMP_EMPNO_INDX |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------



Get More Query Optimization Tips:
Oracle training in Chennai

Index on Foreign key

Oracle Query Optimization of Join Query.

Lets say we have following query to be optimized.
which currently is not using indexes.

SQL> SELECT ENAME, DNAME FROM MYEMP, MYDEPT WHERE MYEMP.DEPTNO=MYDEPT.DEPTNO AND EMPNO=7839;

Execution Plan
----------------------------------------------------------
Plan hash value: 3072374081

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |     1 |    55 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN         |        |     1 |    55 |     7  (15)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| MYEMP  |     1 |    33 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| MYDEPT |     4 |    88 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Here we can simply create index on MYEMP(EMPNO), 
as it is filtering the data most using where clause.

SQL> CREATE INDEX MYEMP_EMPNO_INDX ON MYEMP(EMPNO);

Index created.

SQL> SELECT ENAME, DNAME FROM MYEMP, MYDEPT WHERE MYEMP.DEPTNO=MYDEPT.DEPTNO AND EMPNO=7839;

Execution Plan
----------------------------------------------------------
Plan hash value: 3503326722

-------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                  |     1 |    55 |     6  (17)| 00:00:01 |
|*  1 |  HASH JOIN                   |                  |     1 |    55 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| MYEMP            |     1 |    33 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | MYEMP_EMPNO_INDX |     1 |       |     1   (0)| 00:00:01 |
|   4 |   TABLE ACCESS FULL          | MYDEPT           |     4 |    88 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

We can notice here that MYEMP table is being used by index,
but MYDEPT is not using indexes.
So we can create an index on MYDEPT(DEPTNO) which is used in JOINING both tables.

SQL> CREATE INDEX MYDEPT_DEPTNO_INDX ON MYDEPT(DEPTNO);

Index created.

SQL> SELECT ENAME, DNAME FROM MYEMP, MYDEPT WHERE MYEMP.DEPTNO=MYDEPT.DEPTNO AND EMPNO=7839;

Execution Plan
----------------------------------------------------------
Plan hash value: 2298462778

----------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                    |     1 |    55 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                 |                    |       |       |            |          |
|   2 |   NESTED LOOPS                |                    |     1 |    55 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| MYEMP              |     1 |    33 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | MYEMP_EMPNO_INDX   |     1 |       |     1   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN           | MYDEPT_DEPTNO_INDX |     1 |       |     0   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID | MYDEPT             |     1 |    22 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------



Get More Query Optimization Tips:
Oracle Training in Chennai
Oracle Query Optimization - Simplified Conditions

Lets say we have following query to optimize..

SQL> SELECT * FROM MYEMP WHERE HIREDATE + 60 > SYSDATE;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 2418123361

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

Even if we create an Index on MYEMP(HIREDATE) index will not be used, 
because the field used in where clause in computed field (HIREDATE + 60).

SQL> CREATE INDEX MYEMP_HIREDATE_INDX ON MYEMP(HIREDATE);

Index created.

SQL> SELECT * FROM MYEMP WHERE HIREDATE + 60 > SYSDATE;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 2418123361

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

So here we need to simplify the condition as below, the query will start using index.

SQL> SELECT * FROM MYEMP WHERE HIREDATE > SYSDATE - 60;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 334307922

---------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                     |     1 |    87 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| MYEMP               |     1 |    87 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | MYEMP_HIREDATE_INDX |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------



Get More Query Optimization Tips:
Best Oracle training center in Chennai
Query Optimization - Not In

NOT IN clause in Oracle is very expensive and let the optimizer FULL 
Scan your tables. Because NOT IN does not use a limiting condition.

A way to overcome this problem is to rewrite the query with OUTER JOIN.
Outer Joins adds a limiting condition to your queries that can reduce 
the number of full table scans necessary
 

Example of NOT IN
-----------------------------
SELECT * FROM MYEMP 
WHERE DEPTNO NOT IN 
(
 SELECT DEPTNO FROM MYDEPT
);


Above query with Outer Join
-----------------------------
SELECT MYEMP.* FROM MYEMP, MYDEPT 
WHERE MYEMP.DEPTNO = MYDEPT.DEPTNO(+) 
AND MYDEPT.DEPTNO IS NULL;



Get More Query Optimization Tips:
Best Oracle training in Chennai
When we execute query with OR clause where OR is filtering the records from 2 
different field. Oracle Database can not use index to filter the data.

For example:

SQL> CREATE INDEX MYEMP_EMPNO_INDX ON MYEMP(EMPNO);

Index created.

SQL> CREATE INDEX MYEMP_HIREDATE_INDX ON MYEMP(HIREDATE);

Index created.

SQL> SET AUTOT TRACE
SQL> SELECT * FROM MYEMP WHERE EMPNO=7839 OR HIREDATE > SYSDATE - 60;

Execution Plan
----------------------------------------------------------
Plan hash value: 2418123361

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

As here we can see that we have indexes on EMPNO and HIREDATE of EMP table,
But the query with EMPNO=7839 OR HIREDATE > SYSDATE - 60 condition was not able 
to use any index.

Workaround of this problem is to break this OR clause into UNION, 
so that index can be used by the Oracle Database

Solution:

SQL> SELECT * FROM MYEMP WHERE EMPNO=7839
  UNION
  SELECT * FROM MYEMP WHERE HIREDATE > SYSDATE - 60;

Execution Plan
----------------------------------------------------------
Plan hash value: 1491074982

-----------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                     |     2 |    76 |     6  (67)| 00:00:01 |
|   1 |  SORT UNIQUE                  |                     |     2 |    76 |     6  (67)| 00:00:01 |
|   2 |   UNION-ALL                   |                     |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID| MYEMP               |     1 |    38 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | MYEMP_EMPNO_INDX    |     1 |       |     1   (0)| 00:00:01 |
|   5 |    TABLE ACCESS BY INDEX ROWID| MYEMP               |     1 |    38 |     2   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN          | MYEMP_HIREDATE_INDX |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------


Get More Query Optimization Tips:
Best Oracle training institute in Chennai
Sometime the requirement is to search a table after transforming its values, 
as we may need to search employee names after converting them to lower case.
so that all employee names can be searched by ignoring their case.
But this requirement may lead Oracle to avoid the use of index.

For example:

SQL> CREATE INDEX MYEMP_ENAME_INDX ON MYEMP(ENAME);

Index created.

SQL> SET AUTOT TRACE
SQL>
SQL> SELECT * FROM MYEMP WHERE LOWER(ENAME) = 'ward';

Execution Plan
----------------------------------------------------------
Plan hash value: 2418123361

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

As we can see that MYEMP table is getting TABLE ACCESS FULL, and not using 
MYEMP_ENAME_INDX. For making Oracle to use the index we need to recreate 
the function based index.

For example:

SQL> DROP INDEX MYEMP_ENAME_INDX;

Index dropped.

SQL> CREATE INDEX MYEMP_ENAME_INDX ON MYEMP(LOWER(ENAME));

Index created.

SQL> SELECT * FROM MYEMP WHERE LOWER(ENAME) = 'ward';

Execution Plan
----------------------------------------------------------
Plan hash value: 2632457189

------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |     1 |    38 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| MYEMP            |     1 |    38 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | MYEMP_ENAME_INDX |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------


Oracle Documentation Says: 
The Oracle database does not include rows in an index if all indexed columns 
are NULL. NULL values are not stored in indexes. Therefore, the following query 
with "COMM IS NULL" will not use an index, even if that COMM is indexed.

Problem:

SQL> CREATE INDEX MYEMP_COMM_INDX ON MYEMP(COMM);
Index created.

SQL> set autot trace
SQL> SELECT * FROM MYEMP WHERE COMM IS NULL;
10 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2418123361
---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |    10 |   390 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| MYEMP |    10 |   390 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------


To use index, the query must be guaranteed not to need any NULL values from the 
indexed expression. So this can be achived by creating an index on NVL(COMM,-1) 
and change query condition accordingly.

Solution 1:

SQL> DROP INDEX MYEMP_COMM_INDX;
Index dropped.

SQL> CREATE INDEX MYEMP_COMM_INDX ON MYEMP(NVL(COMM,-1));
Index created.

SQL> SELECT * FROM MYEMP WHERE NVL(COMM,-1) = -1;
Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 2160314797
-----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |     1 |    44 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| MYEMP           |     1 |    44 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | MYEMP_COMM_INDX |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------


But if it is not possible to change the query as the module has been deployed which
is using the query. We can actually index NULL values by simply adding another not 
null column to the index.

Solution 2:

SQL> DROP INDEX MYEMP_COMM_INDX;
Index dropped.

SQL> CREATE INDEX MYEMP_COMM_INDX ON MYEMP(COMM,1);
Index created.

SQL> SELECT * FROM MYEMP WHERE COMM IS NULL;
10 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2160314797
-----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |    10 |   390 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| MYEMP           |    10 |   390 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | MYEMP_COMM_INDX |    10 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------


Sometime the requirement is to search a column of a table using like operator 
with both side %, as we may need to search employees whose names have 'AR'. 
so that table can be searched by query with ENAME LIKE '%AR%' condition 
or INSTR(ENAME,'AR')>0 condition. But this requirement may lead Oracle to avoid 
the use of index.

Problem:

SQL> CREATE INDEX MYEMP_ENAME_INDX ON MYEMP(ENAME);
Index created.

SQL> SELECT * FROM MYEMP WHERE ENAME LIKE '%AR%';
Execution Plan
----------------------------------------------------------
Plan hash value: 2208738075
---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |    14 |   546 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| MYEMP |    14 |   546 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------


Here we can provide an Index Hint to query to suggest Oracle Database Engine to use index,
Oracle may use index after providing index hint in query.

Solution:

SQL> SELECT /*+ INDEX (MYEMP MYEMP_ENAME_INDX) */ * FROM MYEMP WHERE ENAME LIKE '%AR%';
Execution Plan
----------------------------------------------------------
Plan hash value: 2208738075
------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |     1 |    39 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| MYEMP            |     1 |    39 |     2   (0)| 00:00:01 |
|*  2 |   INDEX FULL SCAN           | MYEMP_ENAME_INDX |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------


Same can be used when we write this requirement using INSTR.

Solution:

SQL> SELECT /*+ INDEX (MYEMP MYEMP_ENAME_INDX) */ * FROM MYEMP WHERE INSTR(ENAME,'AR')>0;
Execution Plan
----------------------------------------------------------
Plan hash value: 2418123361
---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     1 |    39 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| MYEMP |     1 |    39 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------- 


Oracle SQL Query Tuning Course Objective

  • Choose an appropriate SQL tuning approach
  • Gather and interpret session statistics using the SQL trace facility
  • Identify the SQL statements that perform poorly
  • Use tuning techniques to tune inefficient SQL statements
  • Interpret Execution Plans
  • Describe the Oracle optimizer fundamentals
  • Manage SQL performance through changes

Oracle SQL Query Tuning Training Syllabus

Introduction to SQL Tuning

  • Find a workaround to enhance performance
  • Analyze a poorly written SQL statement
  • Create a function based index
  • Redesign a simple table
  • Rewrite the SQL statement

Using SQL Trace Facility and TKPROF

  • Explore a trace file to understand the optimizer’s decisions

Understand Basic Tuning Techniques

  • Rewrite queries for better performance
  • Rewrite applications for better performance
  • Utilize SQL Tuning Advisor using SQL Developer 3.0

Optomizer Fundamentals

  • Explore a trace file to understand the optimizer’s decisions

Understanding Serial Execution Plans

  • Use different techniques to extract execution plans
  • Use SQL monitoring

Optimizer: Table and Index Access Paths

  • Learn about using different access paths for better optimization

Optimizer: Join Operations

  • Use different access paths for better optimization
  • Examine and use the result cache

Other Optimizer Methods

  • Use different access paths for better optimization
  • Examine and use the result cache

Optimizer Statistics

  • Analyze and use system statistics
  • Use automatic statistics gathering

Using Bind Variables

  • Use adaptive cursor sharing and bind peeking
  • Use the CURSOR_SHARING initialization parameter

SQL Plan Management

  • Use SQL Plan Management

Greens Technology Software Trainings Trainers:

Worked earlier in Oracle Corporation, IBM, Google, Verizon, CSC, Infosys etc.

PLSQL Tuning: Bulk Collect with Dynamic SQL

How to use Bulk Collect with Dynamic SQL or Can I use execute immediate with Bulk Collect? With this blog post I am trying to answer this question with very simple example. Yes we can use Bulk Collect with Dynamic SQLs and can improves performance by minimizing the number of context switches between the PL/SQL and SQL engines.

I have a "EMP" table with "11246872" records.
SQL> select count(*) from emp;
  COUNT(*)
----------
  11246872

Lets run a sample code for Dynamic SQL in EMP table without using bulk-collect.
SQL> declare
  2     l_sql varchar2(4000);
  3     l_row emp%rowtype;
  4     c sys_refcursor;
  5  begin
  6     l_sql := 'select * from emp';
  7     open c for l_sql;
  8
  9     loop
 10             fetch c into l_row;
 11             exit when c%notfound;
 12             -- some operation
 13     end loop;
 14     close c;
 15  end;
 16  /
PL/SQL procedure successfully completed.
Elapsed: 00:03:12.84

Above code is nice and simple, which we want to rewrite so that we can use performance benefits of bulk collect with dynamic sql. Lets modify above code of dynamic sql with bulk collect and execute it to check the performance.
SQL> declare
  2     l_sql varchar2(4000);
  3     type t_tab is table of emp%rowtype index by binary_integer;
  4     l_tab t_tab;
  5     c sys_refcursor;
  6  begin
  7     l_sql := 'select * from emp';
  8     open c for l_sql;
  9     loop
 10             fetch c bulk collect into l_tab limit 1000;
 11
 12             for i in 1..l_tab.count
 13             loop
 14                     null;
 15                     -- some operation
 16             end loop;
 17             exit when c%notfound;
 18     end loop;
 19     close c;
 20  end;
 21  /
PL/SQL procedure successfully completed.
Elapsed: 00:00:28.45

We have saved a lot of context switching between the PL/SQL and SQL engines. As we can see, this simple change has given us more than 85% performance benefit. Always use Bulk Collect in your code, wherever you are planning to have simple cursor loop fetch.

PLSQL Tuning: Bind Variables and execute immediate

With this blog post I am trying to highlight the Performance benefits of Bind Variables and How to use bind variables with execute immediate. I mostly do not like to have theory in my blog posts as it is already there perfectly in Oracle Documentation but here I am trying to give a little background.

First of all what is bind variable:
A bind variable is basically a place-holder SQL statement which is replaced by actual value when the SQL statement is executed. Bind Variables helps application to send exactly the same SQL to Oracle every time. Bind variable can highly improve performance by avoiding hard parsing. It also helps Oracle to lower Shared Pool Memory consumption and also helpful to avoid SQL injection.

Now about Hard Parsing and Soft Parsing
For each SQL submitted, Oracle picks the execution plan from shared pool if same exact SQL already exists there, which is called soft parsing. If the SQL submitted is not found in Shared Pool, Oracle has to do the hard parsing which is SQL statement needs to be checked for syntax and semantics errors, and generating various execution plans to find and select optimal one. Hard parsing is very CPU intensive.

Now my favourite part, lets code and check performance benefits of bind variables.

Prepare data for tesing the performance.
SQL> create table test_bind as
  2  select level col from dual
  3  connect by level <= 99999;
Table created.

SQL> commit;
Commit complete.

SQL> create index test_indx on test_bind(col);
Index created.

SQL> select count(*) from test_bind;
  COUNT(*)
----------
     99999

All good, we have created a table with 99999 records and we have an index on it. Lets run plsql block executing 99999 queries without bind variable.

SQL> set timing on
SQL> declare
  2     a number;
  3     i number;
  4     mysql varchar2(100);
  5  begin
  6    i := 1;
  7    while i<=99999
  8    loop
  9      mysql := 'select * from test_bind where col=' || i;
 10      execute immediate mysql
 11         into a;
 12      i := i+1;
 13    end loop;
 14  end;
 15  /
PL/SQL procedure successfully completed.
Elapsed: 00:27:15.55

99999 unique queries executed in more than 27 minutes, almost 60 queries per second.

Lets now try same code with bind variables on the same data

SQL> set timing on
SQL> declare
  2     a number;
  3     i number;
  4     mysql varchar2(100);
  5  begin
  6    i := 1;
  7    while i<=99999
  8    loop
  9       mysql := 'select * from test_bind where col=:1';
 10       execute immediate mysql
 11           into a
 12          using i;
 13       i := i+1;
 14     end loop;
 15  end;
 16  /
PL/SQL procedure successfully completed.
Elapsed: 00:00:05.03

WOW, It got executed in just 5 seconds, what a performance gain almost 20000 queries in a second. We just changed is just used bind variables and found 95% performance gain. Hard Parsing seems to be a really resource/CPU consuming task.

Difference Between Cursor And Ref Cursor


In this post I am trying to detail out the differences between Cursor and Ref Cursors. I am sure, this question has been asked in many interviews to many of us and it also has its own technical weightage. Lets first check the basic example of Cursor and Ref Cursors. In this post Cursor means PL/SQL Cursors only.

Example of Cursor:
declare    
    cursor c1 is select first_name, salary from hr.employees;
begin    
    for c in c1 
    loop
        dbms_output.put_line('Ename: ' || c.first_name || ', Salary: ' || c.salary);
    end loop;
end;
/

Example of Ref Cursor
declare    
    c1 SYS_REFCURSOR;
    ename varchar2(10);
    sal number;
begin
    open c1 for select first_name, salary from hr.employees;
    LOOP 
        FETCH c1 into ename, sal;
            EXIT WHEN c1%NOTFOUND;
        dbms_output.put_line('Ename: ' || first_name || ', Salary: ' || salary);
    END LOOP;
    close c1;    
end;
/

Technically, They are both cursors and can be processed in the same fashion and at the most basic level, they both are same. There are some important differences between regular cursors and ref cursors which are following:

1) A ref cursor can not be used in CURSOR FOR LOOP, it must be used in simple CURSOR LOOP statement as in example.

2) A ref cursor is defined at runtime and can be opened dynamically but a regular cursor is static and defined at compile time.

3) A ref cursor can be passed to another PL/SQL routine (function or procedure) or returned to a client. A regular cursor cannot be returned to a client application and must be consumed within same routine.

4) A ref cursor incurs a parsing penalty because it cannot cached but regular cursor will be cached by PL/SQL which can lead to a significant reduction in CPU utilization.

5) A regular cursor can be defined outside of a procedure or a function as a global package variable. A ref cursor cannot be; it must be local in scope to a block of PL/SQL code.

6) A regular cursor can more efficiently retrieve data than ref cursor. A regular cursor can implicitly fetch 100 rows at a time if used with CURSOR FOR LOOP. A ref cursor must use explicit array fetching.

My recommendation on ref cursors:
Use of ref cursors should be limited to only when you have a requirement of returning result sets to clients and when there is NO other efficient/effective means of achieving the goal.

Create XML file using PL/SQL


To create a xml file, we need to create a directory and grant the read write permission to the specified USER as

C:\>sqlplus sys/sys as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri Mar 29 15:29:12 2013
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Release 11.2.0.3.0 - Production

SQL> create or replace directory myxml as 'E:\myxml\';
Directory created.

SQL> grant read, write on directory myxml to hr;
Grant succeeded.

SQL> grant execute on utl_file to hr;
Grant succeeded.

NOTE: "E:\myxml\" has to be physical location on disk.

After creating the directory and granting the permissions to the HR user, following PL/SQL code needs to be executed by the HR user to create XML file.

C:\>sqlplus hr/admin
SQL*Plus: Release 11.2.0.3.0 Production on Tue Dec 29 15:32:06 2015
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Release 11.2.0.3.0 - Production

SQL> DECLARE
 2      F UTL_FILE.FILE_TYPE;
 3      MYCLOB CLOB;
 4  BEGIN
 5      SELECT
 6        DBMS_XMLGEN.GETXML('
 7          SELECT
 8            E.EMPLOYEE_ID, E.FIRST_NAME, E.SALARY, D.DEPARTMENT_NAME
 9          FROM
10            EMPLOYEES E,
11            DEPARTMENTS D
12          WHERE
13            D.DEPARTMENT_ID=E.DEPARTMENT_ID
14        ')
15      INTO MYCLOB
16      FROM DUAL;
17
18      F := UTL_FILE.FOPEN('MYXML','EMP_DEPT.XML','w',32767);
19      UTL_FILE.PUT(F,MYCLOB);
20      UTL_FILE.FCLOSE(F);
21  END;
22  /

PL/SQL procedure successfully completed.

After the execution of above procedure, a file (EMP_DEPT.XML) would have been created at "E:\myxml\" location.

Ref Cursor: Strongly Typed VS Weakly Typed

A Ref Cursor is a User Defined Type allows you to declare the cursor variable which is independent of select statement. A ref cursor is technically same as cursor and can be processed in the same fashion at the most basic level. A ref cursor is defined at runtime and acts as a pointer to the result set of the select query with with it is opened dynamically. It does not contain result of the query. Ref Cursor can be passed/returned to another PL/SQL routine (function or procedure) or even can be returned to client from the Oracle Database Server.

Ref Cursors can be further categorized in 2 parts
1) Strongly Typed Ref Cursor
2) Weakly Typed Ref Cursor

When a return type is included while defining Ref Cursor it called Strongly Typed Ref Cursor. Structure of Strongly Typed Ref Cursor is known at the compile time and can only be associated with queries which return result-set of same structure.

Example of Strongly Typed Ref Cursor

SQL> create or replace function f_get_emp_by_dept (p_deptno in number)
  2     return sys_refcursor
  3  is
  4     type my_row is record
  5     (
  6        empno   emp.empno%type,
  7        ename   emp.ename%type,
  8        mgr     emp.mgr%type,
  9        sal     emp.sal%type,
 10        dname   dept.dname%type
 11     );
 12
 13     type t_stronge_cursor is ref cursor return my_row;
 14
 15     v_cur t_stronge_cursor;
 16  begin
 17     open v_cur for
 18        select empno, ename, mgr, sal, dname
 19          from emp, dept
 20         where dept.deptno = emp.deptno and dept.deptno = p_deptno;
 21
 22     return v_cur;
 23  end;
 24  /

Function created.


Weakly Typed Ref Cursor do not have return type. Weakly Typed ref cursor gives us great flexibility and can be associated with any query. They can be directky created with predefined SYS_REFCURSOR type.

Example of Weakly Typed Ref Cursor

SQL> create or replace function f_get_emp_by_dept(p_deptno in number) return sys_refcursor is
  2    v_cur sys_refcursor;
  3  begin
  4    open v_cur for
  5     select empno, ename, mgr, sal, dname
  6     from    emp, dept
  7     where  dept.deptno = emp.deptno
  8     and    dept.deptno = p_deptno;
  9
 10    return v_cur;
 11  end;
 12  /

Function created.

Load XML File in Oracle Table

In my last post, I have created a XML file using PL/SQL. Here we are trying to load that XML data back in a normal table of Oracle Database. We assume that the directory is created and the permissions are already granted also the XML file has been exported. To read my last post on Exporting XML file using PL/SQL, creating directory and granting permission, please click Create XML file using PL/SQL
To Load "EMP_DEPT.XML" file in to Oracle Table we have created a table with same structure of XML file EMP_DEPT as

C:\>sqlplus hr/admin
SQL*Plus: Release 11.2.0.3.0 Production on Dec 29 15:52:06 2015
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Release 11.2.0.3.0 - Production

SQL> CREATE TABLE EMP_DEPT
  2  (
  3  EMPNO NUMBER(4),
  4  ENAME VARCHAR2(10),
  5  SAL NUMBER(7,2),
  6  DNAME VARCHAR2(14)
  7  );

Table created.

Once the table is created, we just need to execute following query to load the XML file into the Oracle table.


SQL> INSERT INTO EMP_DEPT (EMPNO, ENAME, SAL, DNAME)
  2  SELECT *
  3  FROM XMLTABLE('/ROWSET/ROW'
  4         PASSING XMLTYPE(BFILENAME('MYXML', 'EMP_DEPT.XML'),
  5         NLS_CHARSET_ID('CHAR_CS'))
  6         COLUMNS EMPNO  NUMBER(4)    PATH 'EMPLOYEE_ID',
  7                 ENAME  VARCHAR2(10) PATH 'FIRST_NAME',
  8                 SAL    NUMBER(7,2)  PATH 'SALARY',
  9                 DNAME  VARCHAR2(14) PATH 'DEPARTMENT_NAME'
10       )
11  ;
14 rows created.

Data of "EMP_DEPT" can be determind as following


SQL> SELECT * FROM EMP_DEPT;
     EMPNO ENAME             SAL DNAME
---------- ---------- ---------- --------------
      7782 CLARK            2450 ACCOUNTING
      7839 KING             5000 ACCOUNTING
      7934 MILLER           1300 ACCOUNTING
      7566 JONES            2975 RESEARCH
      7902 FORD             3000 RESEARCH
      7876 ADAMS            1100 RESEARCH
      7369 SMITH            1000 RESEARCH
      7788 SCOTT            3000 RESEARCH
      7521 WARD             4000 SALES
      7844 TURNER           1500 SALES
      7499 ALLEN            2000 SALES
      7900 JAMES             950 SALES
      7698 BLAKE            2850 SALES
      7654 MARTIN           1250 SALES

14 rows selected.

Bulk Collect with Limit Clause and %NOTFOUND

With is blog post I am trying to demonostrate the issue of using %NOTFOUND with BULK COLLECT and LIMIT clause. When we use %NOTFOUND with BULK COLLECT and LIMIT Clause we need to be cautious about the placement of %NOTFOUND.

Let me run the classic CURSOR LOOP on EMP Table first. Do not forget to set serveroutput on before running following examples.

SQL> declare
  2     cursor c1 is select * from emp;
  3     l_row c1%rowtype;
  4     cnt number := 0;
  5  begin
  6     open c1;
  7     loop
  8             fetch c1 into l_row;
  9             exit when c1%notfound;
 10             cnt := cnt+1;
 11             dbms_output.put_line(cnt || '. ' || l_row.ename || '(' || l_row.empno || ')' );
 12     end loop;
 13     close c1;
 14  end;
 15  /
1. KING(7839)
2. BLAKE(7698)
3. CLARK(7782)
4. JONES(7566)
5. SCOTT(7788)
6. FORD(7902)
7. SMITH(7369)
8. ALLEN(7499)
9. WARD(7521)
10. MARTIN(7654)
11. TURNER(7844)
12. ADAMS(7876)
13. JAMES(7900)
14. MILLER(7934)
PL/SQL procedure successfully completed.

As we can see we have 14 rows in EMP table. Lets modify the above code to run it using BULK COLLECT with limit of 5 rows.
SQL> declare
  2     cursor c1 is select * from emp;
  3     type tt is table of c1%rowtype index by binary_integer;
  4     t tt;
  5     cnt number := 0;
  6  begin
  7     open c1;
  8     loop
  9             fetch c1 bulk collect into t limit 5;
 10             exit when c1%notfound;
 11
 12             for i in 1..t.count
 13             loop
 14                     cnt := cnt+1;
 15                     dbms_output.put_line(cnt || '. ' || t(i).ename || '(' || t(i).empno || ')');
 16             end loop;
 17     end loop;
 18     close c1;
 19  end;
 20  /
1. KING(7839)
2. BLAKE(7698)
3. CLARK(7782)
4. JONES(7566)
5. SCOTT(7788)
6. FORD(7902)
7. SMITH(7369)
8. ALLEN(7499)
9. WARD(7521)
10. MARTIN(7654)
PL/SQL procedure successfully completed.

Ouch !!! It only displayed 10 records, where are my last 4 rows? The problem is we are using "exit when c1%notfound;" just after bulk collect. In the 3rd iteration when Oracle bulk collected the last 4 rows and cursor got exhausted, Oracle Set c1%notfound as true and we exited from our for loop.

So How to fix it? It is easy, simply put your "exit when c1%notfound;" just before the end loop of Cursor Loop. Lets try it.

SQL> declare
  2     cursor c1 is select * from emp;
  3     type tt is table of c1%rowtype index by binary_integer;
  4     t tt;
  5     cnt number := 0;
  6  begin
  7     open c1;
  8     loop
  9             fetch c1 bulk collect into t limit 5;
 10
 11             for i in 1..t.count
 12             loop
 13                     cnt := cnt+1;
 14                     dbms_output.put_line(cnt || '. ' || t(i).ename || '(' || t(i).empno || ')');
 15             end loop;
 16             exit when c1%notfound;
 17     end loop;
 18     close c1;
 19  end;
 20  /
1. KING(7839)
2. BLAKE(7698)
3. CLARK(7782)
4. JONES(7566)
5. SCOTT(7788)
6. FORD(7902)
7. SMITH(7369)
8. ALLEN(7499)
9. WARD(7521)
10. MARTIN(7654)
11. TURNER(7844)
12. ADAMS(7876)
13. JAMES(7900)
14. MILLER(7934)
PL/SQL procedure successfully completed.

Alternatively we can also use collection.count to check if collection is empty after bulk collect operation, instead of using "%NOTFOUND" as in the following example.

SQL> declare
  2     cursor c1 is select * from emp;
  3     type tt is table of c1%rowtype index by binary_integer;
  4     t tt;
  5     cnt number := 0;
  6  begin
  7     open c1;
  8     loop
  9             fetch c1 bulk collect into t limit 5;
 10             exit when t.count = 0;
 11             for i in 1..t.count
 12             loop
 13                     cnt := cnt+1;
 14                     dbms_output.put_line(cnt || '. ' || t(i).ename || '(' || t(i).empno || ')');
 15             end loop;
 16
 17     end loop;
 18     close c1;
 19  end;
 20  /
1. KING(7839)
2. BLAKE(7698)
3. CLARK(7782)
4. JONES(7566)
5. SCOTT(7788)
6. FORD(7902)
7. SMITH(7369)
8. ALLEN(7499)
9. WARD(7521)
10. MARTIN(7654)
11. TURNER(7844)
12. ADAMS(7876)
13. JAMES(7900)
14. MILLER(7934)
PL/SQL procedure successfully completed.

How to Get Execution Plan and Statistics of SQL Query

How do you check EXECUTION PLAN of a QUERY? I got this question on my facebook chat many times. "EXPLAIN PLANE" The answer was quite simple, Right? Actually it depends on what I am looking for. "AUTOT TRACE" is my personal favourite but I use one of the following depending on various situations.
1) EXPLAIN PLAN
2) AUTOT TRACE
3) DBMS_XPLAN.DISPLAY_CURSOR
4) SQL TRACE (10046)and TKPROF

Lets execute them to have an idea on how these methods are different and what information one provides and other doesn't.

1) EXPLAIN PLAN (basic and simple)
SQL> explain plan for select * from dual;
Explained.

SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
----------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost  |
----------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2 |
----------------------------------------------------------
Note
-----
   - 'PLAN_TABLE' is old version

11 rows selected.


2) AUTOT TRACE (PLAN + STATS)
SQL> set autot trace
SQL> select * from dual;

Execution Plan
----------------------------------------------------------

----------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost  |
----------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2 |
----------------------------------------------------------
Note
-----
   - 'PLAN_TABLE' is old version

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


3) DBMS_XPLAN.DISPLAY_CURSOR (PLAN + OTHER DETAILS)
SQL> SELECT * FROM DUAL;
D
-
X

SQL> SELECT SQL_ID FROM V$SQL WHERE SQL_TEXT LIKE 'SELECT * FROM DUAL%';
SQL_ID
-------------
9g6pyx7qz035v

SQL> select * from table(dbms_xplan.display_cursor('9g6pyx7qz035v',NULL,'ADVANCED'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
SQL_ID  9g6pyx7qz035v, child number 0
-------------------------------------
SELECT * FROM DUAL

Plan hash value: 3543395131
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / DUAL@SEL$1

Outline Data
-------------
  /*+
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "DUAL"@"SEL$1")
      END_OUTLINE_DATA
  */

Column Projection Information (identified by operation id):

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
   1 - "DUAL"."DUMMY"[VARCHAR2,1]

Note
-----
   - SQL plan baseline SQL_PLAN_c7fbanxudy9yvef6f73b0 used for this statement

41 rows selected.


4) SQL TRACE (10046)and TKPROF
Click here to know how to generate trace files and execute TKPROF in simple steps.
http://nimishgarg.blogspot.com/2010/10/oracle-tkprof-simple-steps.html

Oracle Database 12c New Features for Developers

Oracle Database 12c introduces a new multitenant architecture that makes it easy to deploy and manage database clouds. Oracle 12c is a pluggable database environment, where we can plug multiple databases into single database container. All these databases then share same background processes and memory. This helps in reducing the overhead of managing multiple databases.

I have tried to compile some of the important new features of Oracle Database 12c. Below are the top 15 new features of Oracle Database 12c for Oracle Developer & professional.

1. Sequence as Default Value
With Oracle Database 12c, we can directly assign sequence nextval as a default value for a column, So you no longer need to create a trigger to populate the column with the next value of sequence, you just need to declare it with table definition.

Example:
create sequence test_seq start with 1 increment by 1 nocycle;

create table test_tab
(
    id number default test_seq.nextval primary key
);


2. Invisible column:
Oracle Database 12c provides you the Invisible column feature. A Column defined as invisible, will not appear in generic queries (select * from). An Invisible Column need to be explicitly referred to in the SQL statement or condition. Also invisible column must be explicitly referred in INSERT statement to insert the database into invisible columns.

Example:
SQL> create table my_table
  2  (
  3  id number,
  4  name varchar2(100),
  5  email varchar2(100),
  6  password varchar2(100) INVISIBLE
  7  );
  
SQL> ALTER TABLE my_table MODIFY (password visible);  


3. Multiple indexes on the same column
Before Oracle Database 12c, we could not have multiple indexes on a single column. In Oracle Database 12c a column may have multiple indexes but all should be of different types. Like a column may have B-Tree and BitMap Index both. But, only one index will be used at a given time.


4. VARCHAR2 length up to 32767
Form Oracle Database 12c, a varchar2 column can be sized upto 32767, which was earlier 4000. The maximum size of the VARCHAR2, NVARCHAR2, and RAW data types has been increased from 4,000 to 32,767 bytes. Increasing the allotted size for these data types allows users to store more information in character data types before switching to large objects (LOBs).


5. Top-N feature
A Top-N query is used to retrieve the top or bottom N rows from an ordered set. Combining two Top-N queries gives you the ability to page through an ordered set
Example:
SQL> SELECT value
  2  FROM   mytable
  3  ORDER BY value DESC
  4  FETCH FIRST 10 ROWS ONLY;


6. IDENTITY Columns
In Oracle Database 12c, We can define Table columns with SQL keyword IDENTITY which is a American National Standards Institute (ANSI) SQL keyword. Which are auto-incremented at the time of insertion (like in MySQL).
Example:
SQL> create table my_table
  2  (
  3  id number generated as identity,
  4  name varchar2(100),
  5  email varchar2(100),
  6  password varchar2(100) INVISIBLE
  7  );


7. With Clause improvement
In Oracle 12c, we can declare PL/SQL functions in the WITH Clause of a select statement and use it as an ordinary function. Using this construct results in better performance as compared with schema-level functions
Example:
SQL> WITH
  2    FUNCTION f_test(n IN NUMBER) RETURN NUMBER IS
  3    BEGIN
  4      RETURN n+1;
  5    END;
  6  SELECT f_test(1)
  7  FROM   dual
  8  ;


8. Cascade for TRUNCATE and EXCHANGE partition.
With Oracle Database 12c, The TRUNCATE can be executed with CASCADE option which will also delete the child records.


9. Online RENAME/MOVE of Datafiles
Oracle Database 12c has provided a simple way to online renamed or moved data files by simply "ALTER DATABASE MOVE DATAFILE" command. Data files can also be migrated online from ASM to NON-ASM and NON-ASM to ASM easily now.

Examples:
Rename datafile:  
  SQL> ALTER DATABASE MOVE DATAFILE '/u01/oradata/indx.dbf' TO '/u01/oradata/indx_01.dbf';
Move Datafile:    
  SQL> ALTER DATABASE MOVE DATAFILE '/u01/oradata/indx.dbf' TO '/u01/oradata/orcl/indx.dbf';
NON-ASM to ASM:   
  SQL> ALTER DATABASE MOVE DATAFILE '/u01/oradata/indx.dbf' TO '+DISKGROUP_DATA01';


10. Move table partition to different Tablespace online
From Oracle 12c, it become very easy to move Table Partition to different tablespace and does not require complex steps
Example:
  SQL> ALTER TABLE MY_LARGE_TABLE MOVE PARTITION MY_LARGE_TABLE_PART1 TO TABLESPACE USERS_NEW;



11. Temporary Undo
Before Oracle Database 12c, undo records of temporary tables used to be stored in undo tablespace. With the temporary undo feature in Oracle Database 12c, the undo records of temporary tables can now be stored in a temporary table instead of stored in undo tablespace. The main benefits of temporary undo are 1) Low undo tablespace usages 2) less redo data generation. For using this feature Compatibility parameter must be set to 12.0.0 or higher and TEMP_UNDO_ENABLED initialization parameter must be Enabled.


12. DDL logging
By using the ENABLE_DDL_LOGGING initiation parameter in Oracle Database 12c, we can now log the DDL action into xml and log files to capture when the drop or create command was executed and by whom under the $ORACLE_BASE/diag/rdbms/DBNAME/log|ddl location. The parameter can be set at the database or session levels.
Example:
  SQL> ALTER SYSTEM SET ENABLE_DDL_LOGGING=TRUE;



13. PGA_AGGREGATE_LIMIT parameter
Oracle Database 12c has provided us a way to limit PGA by PGA_AGGREGATE_LIMIT parameter. Before Oracle Database 12c there was no option to limit and control the PGA size. Oracle will automatically abort the session that holds the most untenable PGA memory when PGA limits exceeds the defined value.


14. SQL statement in RMAN
From Oracle Database 12c, we can execute any SQL and PL/SQL commands in RMAN without SQL prefix
Example:
  RMAN> SELECT username,machine FROM v$session;


15. Turning off redo for Data Pump the import
The new TRANSFORM option, DISABLE_ARCHIVE_LOGGING, to the impdp command line causes Oracle Data Pump to disable redo logging when loading data into tables and when creating indexes. This feature provides a great relief when importing large tables, and reduces the excessive redo generation, which results in quicker imports. This attribute applies to tables and indexes.
Example:
  impdp directory=mydir dumpfile=mydmp.dmp logfile=mydmp.log TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y


New Features for Developers in Oracle 11g


Oracle 11g has introduced many new features in SQL & PL/SQL, which are very useful for a developer. I havecompiled some of the most important features, which a Oracle developer should know.

Reference: http://www.oracle.com/technetwork/articles/grid/index-099021.html

1. Compound Triggers: A compound trigger is actually four different triggers defined as one. it not only it saves lot of code writing, but also resolves the issue where same set of session variables share common data

2. Trigger Follows/Precedes Clause: The Oracle 11g trigger syntax now includes the Follows/Precedes clause to guarantee execution order for triggers defined with the same timing point. Follows clause in a trigger specifies that the current trigger would follow the execution of specified triggers.

3. Creating Trigger in DISABLED mode: Oracle 11g provides flexibility to create a Trigger in DISABLED mode also. They remain deactivated until they are enabled. Prior to Oracle 11g, a trigger can be created in ENABLED mode only.

4. Read Only Tables: In Oracle 11g, a table can be set READ ONLY mode to restrict write operations on the table. A table can be altered to toggle over READ ONLY and READ WRITE modes.

5. Invisible Indexes: An invisible index is ignored by the optimizer unless you explicitly set the OPTIMIZER_USE_INVISIBLE_INDEXES initialization parameter to TRUE or using INDEX hint. Making an index invisible is an alternative to making it unusable. It can be converted to VISIBLE mode for auto consideration by the optimizer

6. Virtual Columns: Oracle 11g allows you to create a "virtual column", an empty column that contains a function upon other table columns (the function itself is stored in the data dictionary). They show same behavior as other columns in the table in terms of indexing and statistics.
- Oracle 11g - Virtual Columns

7. Continue Statement: The continue statement passes control to the next iteration. The CONTINUE statement exits the current iteration of a loop, either conditionally or unconditionally, and transfer control to the next iteration.

8. LISTAGG Analytic Functions: Oracle 11g has introduced. LISTAGG analytic function to aggregate the result set in multiple rows into one single column.
- Oracle 11g New String Aggregation Techniques

9. REGEXP_COUNT: Oracle 11g introduced REGEXP_COUNT in regular expressions, used to count the occurrence of a character or string expression in another string.

10. Pivot and Unpivot: The PIVOT operator takes data in separate rows, aggregates it and converts it into columns. The UNPIVOT operator converts column-based data into separate rows.
- Oracle 11g Pivot data Rows to Columns

11. Result Cache: Oracle 11g has introduced a new component in SGA - Shared Pool as Result Cache to retain result-sets of SQL queries and PL/SQL functions results. By caching the results of queries, Oracle can avoid having to repeat the potentially time-consuming and intensive operations that generated the resultset.

12. Pragma Inline: A new pragma PRAGMA INLINE has been introduced to specify whether a subprogram call has to be inlines or not. Inlining replaces a subprogram call with a copy of the called subprogram

13. Direct Sequence Assignment: Prior to Oracle 11g, sequence assignment to a number variable could be done through a SELECT statement only, which requires context switching from PL/SQL engine to SQL engine. From oracle 11g we can directly assign a sequence value to a pl/sql variable.

14. SIMPLE_INTEGER Data Type: Oracle 11g has designed a new data type SIMPLE_INTEGER, SIMPLE_FLOAT, and SIMPLE_DOUBLE. They are compatible with the native compilation feature of Oracle 11g, which makes supports their faster implementation.

15. Recursive Subquery Factoring: Version 11g release 2 introduced recursive subquery factoring or the recursive with clause. This is an extension to the SQL syntax with which you can do recursive/hierarchical queries.
- Oracle 11g - Recursive subquery factoring

16. Calling Functions: In Oracle 11g, functions can now be called using Named, Positional and Mixed notation while calling from SQL SELECT statement.

17. DDL Wait Option: Oracle Database 11g, using ddl_lock_timeout parameter, when a DDL statement in the session does not get the exclusive lock, it will not error out. Instead, it will wait for sepcified seconds in ddl_lock_timeout parameter. In those seconds, Oracle continually re-tries the DDL operation until it's successful or the time expires.

18. Deprecation of SQL Plus: Oracle has deprecated the use of SQL Plus (isqlplus and sqlplusw) since Oracle 11g. Oracle 11g supports SQL plus through command line and recommends the use of SQL Developer.

19. SIMPLE_INTEGER: Oracle 11g has designed a new data type SIMPLE_INTEGER, SIMPLE_FLOAT, and SIMPLE_DOUBLE keeping in view the hardware requirements and expectations with an Integer value. They are compatible with the native compilation feature of Oracle 11g, which makes supports their faster implementation.

20. New PL/SQL Compiler Warning: Oracle Database 11g has a new warning called PLW-06009 to warn you "dangerous practice of leaving the WHEN OTHERS THEN NULL" during compile time. enable this warning you have to set this session parameter plsql_warnings = 'enable:all'

21. Behaviour: Oracle 11g no longer keeps PL/SQL as a pure procedural language. Now PL/SQL is efficient to implement Object Oriented Concepts, thus making it comparable to other programming languages like C++ and JAVA.

22. New Partitions: By using Oracle 11g new feature called Reference Partitioning we can create partitions identical to parent table in child table. Oracle 11g has also provided us a way to define partitions in an intelligent manner by System Partitioning.
- Oracle 11g - Reference Partitions
- Oracle 11g - System Partitions


ORA-00054: resource busy and acquire with NOWAIT specified

ORA-00054: resource busy and acquire with NOWAIT specified
Cause: The NOWAIT keyword forced a return to the command prompt
because a resource was unavailable for a LOCK TABLE or SELECT FOR
UPDATE command.
Action: Try the command after a few minutes or enter the command without
the NOWAIT keyword.

Reference: http://docs.oracle.com/cd/B19306_01/server.102/b14219/e0.htm

Example:
SQL> alter table emp add (mobile varchar2(15));
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified


How to avoid the ORA-00054:
    - Execute DDL at off-peak hours, when database is idle.
    - Execute DDL in maintenance window.
    - Find and Kill the session that is preventing the exclusive lock.


Other Solutions:

Solution 1:
In Oracle 11g you can set ddl_lock_timeout i.e. allow DDL to wait for the object to 
become available, simply specify how long you would like it to wait:
 
SQL> alter session set ddl_lock_timeout = 600;
Session altered.

SQL> alter table emp add (mobile varchar2(15));
Table altered. 


Solution 2:
Also In 11g, you can mark your table as read-only to prevent DML:
SQL> alter table emp read only;
Session altered.

SQL> alter table emp add (mobile varchar2(15));
Table altered.


Solution 3 (for 10g):
DECLARE
 MYSQL VARCHAR2(250) := 'alter table emp add (mobile varchar2(15))';
 IN_USE_EXCEPTION EXCEPTION;
 PRAGMA EXCEPTION_INIT(IN_USE_EXCEPTION, -54);
BEGIN
 WHILE TRUE LOOP
  BEGIN
   EXECUTE IMMEDIATE MYSQL;
   EXIT;
  EXCEPTION
   WHEN IN_USE_EXCEPTION THEN 
    NULL;
  END;
  DBMS_LOCK.SLEEP(1);
 END LOOP;
END;


Solution 4: 

Step 1: Identify the session which is locking the object
select a.sid, a.serial#
from v$session a, v$locked_object b, dba_objects c 
where b.object_id = c.object_id 
and a.sid = b.session_id
and OBJECT_NAME='EMP';

Step 2: kill that session using
alter system kill session 'sid,serial#'; 
 

Oracle DBA Daily Checklist

Oracle DBA Daily Checklist

1. Check that all instances are up.

2. Monitor alert log entries (using tail -f)

3. Check that dbsnmp(SNMP subagent for Oracle DB) is running.

4. Check all last night backups were successful.

5. Check all database archiving are done.

6. Check tablespaces should not be used more that 95%.

7. Check all crons and Oracle Jobs are completed without any error

8. Verify resources for acceptable performance.

9. Identify bad growth of Segments.

10. Identify atleast 1 top resource consuming query