Introduction to Sql Tuning
- Sql statements are written to retrieve /fetch data from the database. We want our sql statement to run fast(sql tuning) and provide the results in secs.
- A poorly designed sql could slow down the entire database operation and bring the entire operation to halt. It is much harder to write efficient SQL than it is to write functionally correct SQL. sql performance tuning can significantly improve a system’s health and performance.
- The key to tuning SQL is to minimize the data it accesses to provide the result. We can minimize the data it accesses to deliver the result through an optimal search path.
A simple example would be
select * from dept where emp=10
- Now this query will have to search the entire table dept to find out the data where emp=10. So it has to access the full table
- Now if we create the index to the emp column, then it can just access the index and get the result. So here it accesses the fewer data
12 Steps to perform Sql Query tuning in Oracle
Here are the general tips for sql performance tuning
(1) First you must have all the required tools for sql tuning. You must have good information on tracing, formatting the trace, explain plan, and reading the explain plan in oracle.
Good knowledge about the various join method available in Oracle and how to use them efficiently
(2) Read fewer data and be I/O efficient.
The more data you read for the sql statement, the more latches it needs to acquire and it slows down the performance. so it must be doing fewer logical reads always
Write a sensible sql statement with the proper filters. Do check about the number of rows in various tables involved and find out the best method to create the sql statement
(2) Use good Oracle indexes
B-Tree indexes and Bitmap indexes can be used to increase the performance of the queries if the data returned is less than 10%. But we must be careful while creating the index as it needs to be maintained for insert, update, and delete operations also. So creating an index creates overhead over many things. So we must carefully examine the effect of creating the index.
(3) Avoid sql which disables the usage of index
SQL that Disables Indexes
(a)Functions ( to_char(), to_date(), etc. )
Fix: move the function to the “constant/bind variable” side
(b) Type Casting
In SQL
where emp_no = 10 (emp_no is a varchar2)
In PL/SQL
where emp_no = v_emp_num (v_emp_num is a number)
Modifiers
and id + 0 = 111
and date + 1 = sysdate (try date = sysdate – 1)
Fix: Change it to avoid it
(4) Always use bind variables in the application. If you dont use bind variable in oracle , the sql will be parsed every time and will impact the database performance. If it contains the bind variable, sql would be cached and further execution will not require parsing thus overall performance of the system is improved
(5) UNION vs OR. Use UNION for queries with two clear execution paths; each returning a relatively small number of rows. Don’t use a union for queries that are likely to return many rows since all rows need to be sorted and most of them will be discarded. OR tends to disable the index
(6) Use the accurate optimizer statistics on the table to get the optimal plan.
(7) If you are using a function on the expression on the condition, check if there is a function-based index on that column. If it is not present the index will not be used
(8) Use exists vs in and Non exists vs not in for correlated subqueries
(9) Avoid Poor coding practices
Some Tips
(a) Avoid Cartesian join. Make sure all the tables required in the queries are needed and are linked to each other
(b) Use Decode to avoid multiple trips to the database
(c) Try to avoid outer join
(d) Sometimes decomposing the logic into small parts makes the work faster
(10) If trying to use the complex view, check if the base tables can be used instead as the view tends to make the performance bad
(11) Use UNION ALL Vs UNION if you know that the data retrieved will not have duplicate rows
( 12) Use hints to optimize the execution plan. Sometimes hints can be used to change the execution plan for the query so as to take the most optimal path.
Sometimes bind peeking create a bad plan, so in that case, putting necessary hint to fix the plan help in getting a good performance every time
The most common hints are
/*+ LEADING (table alias) */ specifies table by which to start join
/*+ FIRST_ROWS */ very good for online screens – favors NESTED LOOPS
/*+ INDEX ( table alias.index name) */ specifies index you want to use. Note: if index gets dropped and recreated and name changes then hint is no longer valid.
/*+ USE_NL (table alias1 table alias 2)*/ asks optimizer to use the Nested Loop Join for the two tables specified
Avoid unnecessary optimizer hint and use them with care
These are some of the tips to avoid issues and do the sql tuning. Sql tuning is a big Ocean and you can learn things by practicing only. Best Of Luck!!
Also Reads
https://docs.oracle.com/cd/B19306_01/server.102/b14211/sql_1016.htm
Automatic Workload Repository(AWR)
Hanganalyze and system state dump
Oracle waits events
How to find which sid is doing full table scan