Sql statement are written to retrive /fetch data from the database. We want our sql statement to running fast 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 signficantly improve a system’s health and performance.
The key to tuning SQL is to minimize the data it accesess 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
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,formating the trace, explain plan,reading th explain plan.
Good knowlegde about the various join method availabe in Oracle and how to use them efficently
1) 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 indexes
BTree 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 carefull examine the effect of creating the index.
3) Avoid sql which disable the usage of index
SQL that Disables Indexes
Functions ( to_char(), to_date(), etc. )
move the function to the “constant/bind variable” side
where emp_no = 10 (emp_no is a varchar2)
where emp_no = v_emp_num (v_emp_num is a number)
and id + 0 = 111
and date + 1 = sysdate (try date = sysdate – 1)
4) Always use bind variable in the application. If you dont use bind variable, 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 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
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 decomoposing 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 everytime
The most common hints are
/*+ LEADING (table alias) */ specifies table by which to start join
/*+ FIRST_ROWS */ very good for on-line screens – favours 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 unneccsary hint and use them with care