oracle performance tuning 12c

oracle performance tuning 12c

SQL Tuning

The main objective of “Oracle performance tuning”, “SQL tuning” is to avoid performing unnecessary work to access rows that do not affect the result.

If a table has huge data, and some SQL queries are taking so much time to execute, what could be the reason?
How to tune that query?

1. Check there may not be the index on the columns used in the where condition of the select query.

2. If an index is there, as per the where condition the optimizer may skip(not using) the index.

3. As per the matched condition in an indexed column if less then 30% of the complete table data are there then the optimizer may skip the index use.

4. Check for the triggers and constraints presents with the driving tables if we find any of them are unused/unnecessary, better to drop or disable it.

5.  Check for any unintentional cartesian product.

6. Check for suspicious activity, such as a full table scan on tables with a large number of rows, which have predicates in the where clause. A full table scan does not mean inefficiency. It might be more efficient to perform a full table scan on a small table

7. Compose Predicates Using AND and = , to improve SQL efficiency.
Use equijoins whenever possible
Statements that perform equijoins on untransformed column values are the easiest to tune.

8. Avoid Transformed Columns in the WHERE Clause, Use untransformed column values as below:

WHERE a.order_no = b.order_no

rather than:

WHERE TO_NUMBER (SUBSTR(a.order_no, INSTR(b.order_no, ‘.’) – 1)) = TO_NUMBER
(SUBSTR(a.order_no, INSTR(b.order_no, ‘.’) – 1))

Do not use SQL functions in predicate clauses or WHERE clauses. Any expression using a column, such as a function having the column as its argument, causes the optimizer to ignore the possibility of using an index on that column, even a unique index unless there is a function-based index defined that the database can use.

9. Influence the optimizer’s choices by setting the optimizer approach and goal, and by gathering representative statistics for the query optimizer.

10. Join order can have a significant effect on performance.

  • Avoid a full-table scan if it is more efficient to get the required rows through an index.
  • Avoid using an index that fetches 10,000 rows from the driving table if you could instead use another index that fetches 100 rows.
  • Choose the join order so as to join fewer rows to tables later in the join order.
11. Restructuring the Indexes:

  • Remove nonselective indexes to speed the DML.
  • Index performance-critical access paths.
  • Consider reordering columns in existing concatenated indexes.
  • Add columns to the index to improve selectivity.

12. After restructuring the indexes and the statement, consider restructuring the data:

  • Introduce derived values. Avoid GROUP BY in response-critical code.
  • Review your data design. Change the design of your system if it can improve performance.
  • Consider partitioning, if appropriate.

13. Maintaining Execution Plans Over Time
You can maintain the existing execution plan of SQL statements over time either using stored statistics or SQL plan baselines. Storing optimizer statistics for tables will apply to all SQL statements that refer to those tables. Storing an execution plan as a SQL plan baseline maintains the plan for a set of SQL statements. If both statistics and a SQL plan baseline are available for a SQL statement, then the optimizer first uses a cost-based search method to build a best-cost plan and then tries to find a matching plan in the SQL plan baseline. If a match is found, then the optimizer proceeds using this plan. Otherwise, it evaluates the cost of each of the accepted plans in the SQL plan baseline and selects the plan with the lowest cost.

14. Use DML with RETURNING Clause
When appropriate, use INSERT, UPDATE, or DELETE… RETURNING to select and modify data with a single call. This technique improves performance by reducing the number of calls to the database.

Tune SQL Using: Autotrace, Explain plan, monitor session

Information to Gather on the SQL Identified During Tuning :
The tuning process begins by determining the structure of the underlying tables and indexes.
The information gathered includes the following:
  1. Complete SQL text from V$SQLTEXT.
  2. Structure of the tables referenced in the SQL statement, usually by describing the table in SQL*Plus.
  3. Definitions of any indexes (columns, column orders), and whether the indexes are unique or non-unique.
  4. Optimizer statistics for the segments (including the number of rows each table, the selectivity of the index columns), including the date when the segments were last analyzed.
  5. Definitions of any views referred to in the SQL statement.
  6. Optimizer plan for the SQL statement (either from EXPLAIN PLAN, V$SQL_PLAN, or the TKPROF output).
  7. Any previous optimizer plans for that SQL statement.


— Check the SQL_ID of the specific SELECT query from “V$SQLTEXT” as below:
SQL> select * from V$SQLTEXT
where upper(sql_text) like ‘SELECT%’;

—  Check the execution plan from “V$SQL_PLAN” for the query by using above SQL_ID.
SQL> select * from V$SQL_PLAN
where SQL_ID = ’78vj3gwmj003c’
and TIMESTAMP = to_date(’14/10/2015 12:29:39 AM’,’dd/mm/yyyy hh:mi:ss AM’);

Leave a Comment

Your email address will not be published. Required fields are marked *