Introduction to Sql Tuning
- Sql statement are written to retrieve /fetch data from the database. We want our sql statement to running fast(sql tuning) and providing the results in secs.
- A poorly design 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 provide the result through 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 emp column, then it can just access the index and get the result. So here it access the less data
12 Steps to perform Sql tuning in Oracle
Here 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,reading the explain plan in oracle.
Good knowledge about the various join method available in Oracle and how to use them efficiently
(2) Read less data and be I/O efficient.
The more data you read for the sql statement, the more latches it need to acquire and it slows down the performance. so it must be doing fewer logical reads always
Write sensible sql statement where 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 increased the performance of the queries if the data returned is less than 10%. But we must be careful while creating the index as it need to maintained for insert,update and delete operation also. So creating an index create overhead over many things. So we must careful examine the effect of creating the index.
(3) Avoid sql which disable 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 variable 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 and thus overall performance of the system is improved
(5) UNION vs OR. Use UNION for queries with two clear execution paths; each returning a fairly small number of rows. Don’t use union for queries that are likely to return a large number of 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 function on 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 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 knows that data retrieved will not have duplicate rows
( 12) Use hints to optimize the execution plan. Sometimes hint can be used to change the execution plan for the query so as to take the most optimal path.
Some times bind peeking create a bad plan ,so in that case putting necessary hint to fix the plan help in getting the good performance every time
The most common hints are
/*+ LEADING (table alias) */ specifies table by which to start join
/*+ FIRST_ROWS */ very good for on-line 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
Leave a Reply