Performance Tuning Basics

Performance Tuning Basics

1) Selectivity and Cardinality
2) Parent and Child Cursors
3) Trace and TKPROF
        It's tool provided by oracle database to do the performance tuning of SQL Queries. This trace file have all the information's related to SQL and which helps to do the performance tuning in oracle apps. and it helps to generates the complete trace file about SQL queries
Performance Tuning in Oracle Apps R12
1) The trace Check box enable in the concurrent Program

    a) Set Profile "Concurrent: Allow Debugging" to yes

    b) The SQL trace under Debug Options button enable in Submit Concurrent Request Window

2) Performance Tuning in Oracle Apps R12

    Which is taking long Time and Enable the Trace for this Concurrent Program. Go to the Concurrent            Program.

Step 1:- 

    Navigation : -System Administrator ==>Profiles > System

    Search the  Profile option "Concurrent: Allow Debugging" and set to yes.

Step 2:- 

Navigation :-  Go to Responsibility of (System Administrator  ==>Concurrent > Program > Define

                Query the Concurrent Program which is taking long time.

                Select the Enable Trace Check Box and Turn On Tracing

Step 3:- 

 Now run the Concurrent program. which is taking log run and to check the performance tuning.

Step 4: The concurrent program trace file in to the TK PROF Trace File

$tkprof xx_raw_trace_file.trc output_file explain=apps/ sort=(exeela,fchela) sys=no

 xx_raw_trace_file.trc: Name of trace file

 output_file: tkprof out file

explain: This option provides the explain plan for the sql statements

Oracle uses two types of Optimizer, but now a days it uses only Cost base optimize

1. Rule-Based Optimizer (RBO) - It uses a list of rules Oracle should follow to generate an execution plan. Even after the cost-based optimizer was introduced

2. Cost-Based Optimizer (CBO) - This uses database statistics to generate several execution plans. select the lowest cost. where the system resources required to complete the operation.

statement with explain plan for:

EXPLAIN PLAN FOR select * from dual

select * from table(dbms_xplan.display)

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY (FORMAT=>'ALL +OUTLINE'))

SQL hint to instruct the Oracle optimizer in choosing an execution plan

How to use hints in Oracle SQL for performance

With the hints one can influence the optimizer. 

The usage of hints causes Oracle to use the Cost Based optimizer.

SQL> SELECT /*+ INDEX (parcels shape_idx) */

  2  FROM SRI_TEMP_TAB

  3  WHERE owner = 'SRI';

Hint Types in Oracle

There are 4 types of hints in the Oracle database as follows.

1) Single-table hints ( one table or view ) such as INDEX and USE_NL hints

Real Example :

Select Emp_Num,Employee_Name,Emp_Salary from Employee where Emp_Salary <15000;

We can create index on emp_salary.

Create Index IDX_Emp_Salary on Employee(emp_salary);

Select Emp_Num,Employee_Name,

/*+ INDEX (Employee IDX_Employee_Salary)*/ Salary

from Employee where Emp_Salary < 15000;

Use_NL Hint :

2) Multitable hints ( Lots of tables or views ) such as LEADING hint.

3) Query block hints ( a single query block ) such as STAR_TRANSFORMATION and UNNEST hints

4) Statement hints ( entire SQL statement ) such as ALL_ROWS hint.

Hints Categories are as follows.

Optimizer Approaches ( ALL_ROWS and FIRST_ROWS )

Parallel execution

Join orders

Join operation

Access paths ( Index Access, Full access and etc.. )

Query transformations 

1) Parallel Hint

        Use the parallel hint on SQL Query. We need to explicitly defines the Parallel hints on the SQL Query for forcefully used the parallel operations.

1. Parallel Hint

2. Parallel_index Hint

1. Parallel Hint

    Parallel hint works on tables

    SELECT /*+ parallel(employees,5) */ emp_id, name FROM hr.employees;

   Above query, you are specify the parallel 5 means 5 process is used to fetch data from the employees table

    SELECT/*+ parallel(emp) */ emp_id, name FROM hr.employees emp;

    Above query, you specifies nothing, But Oracle detect the parallel operation based on database initialization parameters:

2. Parallel_index hints

    Parallel_index hint used for the parallel access to indexes.

    SELECT /*+ parallel_index(emp, emp_i5 ,5) */ emp_id, name FROM employees WHERE deptno =15;

Above Two arguments used in parallel_index hint: Table name and Index name. If you do not specify the parallel number then oracle choose automatic on DB settings. 

2) APPEND

        The append mode is the default during a parallel insert operation. The data is always inserted into a new block, which is allocated to the table. Therefore, the APPEND hint is optional. You should use append mode to increase the speed of INSERT operations, but not when space utilization must be optimized.

Serialized inserts:

Simply it's specify the APPEND hint in each of INSERT statements, either it use immediately after the INSERT keyword 

or it use immediately after the SELECT keyword in the subquery of the INSERT statement.

INSERT /*+ APPEND */ INTO sales 

       SELECT employee_name, employee_id, TRUNC(date), Salary

       FROM Employee;

Parallelized inserts:

Step1 - Eenable the parallel in DML of your session:

ALTER SESSION ENABLE PARALLEL DML;

Step 2- specify the APPEND hint with the PARALLEL hint:

INSERT /*+ APPEND PARALLEL */ INTO sales 

       SELECT employee_name, employee_id, TRUNC(date), Salary

       FROM Employee;