SQL Optimization in Oracle 18C :

In my previous articles i have given the multiple features of Oracle 18 c with its advantages as well as disadvantages.In this article I would like to give you brief introduction about the SQL Optimization in Oracle 18C databases.I will explain different techniques for  SQL Optimization in Oracle 18C.My style is to give the SQL Optimization in Oracle 18C information with different kind of real world examples.

There are following New Features of  SQL Optimization in Oracle 18C :

I have explained in other article that oracle 18c is first autonomous database which will use their automated features to tune the SQL Query.I would like to explain you some SQL tuning features in oracle 18c.I will explain one by one feature with example.

I would first like to introduce the features for SQL Optimization in Oracle 18C for Database administrators. As you all know that the SQL Optimization is not a simple activity.It has lot of tasks to do. So I would like to split the SQL Optimization features in to two categories :

Category 1 : SQL Optimization in Oracle 18C DBA Features

Category 2 : SQL Optimization in Oracle 18C Developers Features

Category 1 : SQL Optimization in Oracle 18C DBA Features :

1.Setting Up The PGA Limit Before Processing the Query :

User can now set the absolute limit for setting up the PGA so that PGA memory can be used by the each session in particular consumer group in Oracle.

2.MEMOPTIMIZE FOR READ Clause for Internet of Things(IOT) Applications :

In Oracle 18c the new clause is implemented named ‘MEMOPTIMIZE FOR READ’ clause which is used for high performance reads from the table. This clause is most useful in IOT like applications or IOT robots to perform high performance read.

3.Automatic AWR Reports for Different Environments :

The AWR reports will show you why the query is running with slow manner.In Oracle 18c user can capture the AWR data for multi-tenant environment as well as Stand by databases.

4.Data Dependent Caching :

The data dependent caching techniques are implemented in oracle 18c.The Oracle 18c supports the service specific data cache which will improve the data access time for the services.

Category 2 : SQL Optimization in 18C Developers Features

1.Use of Private Temporary tables :

In previous versions of oracle we have implemented the use of global temporary tables. But in this new version of oracle has implemented a new concept of temporary tables. The temporary tables are tables which are dropped after the session is closed. The temporary tables improves the performance of PL SQL codes as it deleted after the session is closed. Means those tables are not created physically in the database.Private temporary tables are temporary database objects that are automatically dropped at the end of a transaction or a session.

Syntax for Private temporary tables :

CREATE PRIVATE TEMPORARY TABLE Tablename

(Column_name1 datatype(size),…

Column_name’n’ datatype(size)
)
ON COMMIT DROP DEFINITION
ON COMMIT DELETE ROWS;

Real life example :

If user want to create temporary table named Employee1 after deleting rows of Employee table then following statement is used. These kind of scenarios are used

CREATE PRIVATE TEMPORARY TABLE Employee1

(EMP_NO Number(10),

EMP_NAME Varchar2(30)
)
ON COMMIT PRESERVE DEFINITION
ON COMMIT DELETE ROWS AS
SELECT * FROM Employee;

With using the Private temporary tables user can improve the performance of the SQL Query.

2.Approximate Query Processing for Top’N’ Function :

In previous version the approximate query feature is implemented. Instead of using the TopN function if user uses the APPROX_SUM and APPROX_COUNT function with use of APPROX_RANK function then it will improves the query performance.I would like to give you reason behind the same. If user is using TopN function then the data is been sorted and that sorting is implemented differently.

3.Brand New In-Built Package for Maintaining the SQL Tuning Sets :

Previously we are using the DBMS_SQLTUNE package to perform all the operations on SQL tuning set operations. Now In Oracle 18c new package named DBMS_SQLSET has been implemented so that all the create,modify and drop actions can be performed fast.DBMS_SQLSET package has implemented the SQL tuning set operations performance.

4.Sequence index implementation :

In oracle 18c new scalable sequence is implemented.With using that scalable sequence user can load the data into tables as per the sequence values which are further used as keys in table.

5.Enhancements in SQL Tuning Advisory:

Previous version oracle not uses the Exadata for SQL Tuning Advisory.In this new version of oracle it is directly giving the best result and it also recommends an Exadata-aware SQL profile.

6.About OPTIMIZER_ADAPTIVE_STATISTICS parameter :

In previous versions the most important parameter to consider is OPTIMIZER_ADAPTIVE_STATISTICS. In new version of Oracle 18c this parameter has no effect on performance tuning of query.

Hope you get the different features of Oracle 18c. If you like this article on SQL optimization in 18c. If you like this article or if you have any issues or concerns with the same kindly comment in comments section.