• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
Techgoeasy

Techgoeasy

Learn Oracle, PHP, HTML,CSS,Perl,UNIX shell scripts

  • Home
  • Oracle
    • Oracle database
    • Oracle Ebusiness Suite
    • Oracle weblogic
    • Oracle Performance Tuning
    • Oracle Hyperion
    • Oracle Cloud Tutorials
  • SQL
  • interview questions
  • Linux
  • PHP and HTML
  • Downloads
  • General
Home » Oracle » Oracle Database » How to do sql tuning in Oracle

How to do sql tuning in Oracle

October 12, 2020 by techgoeasy Leave a Comment

sql tuning

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

Filed Under: Oracle, Oracle Database Tagged With: sql performance tuning

Reader Interactions

Leave a Reply Cancel reply

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

Primary Sidebar



Subscribe to our mailing list

Enter your email address to subscribe to this blog and receive notifications of new posts by email

Recent Posts

  • Password Version in oracle
  • How to login as user without changing the password in Oracle database(alter user identified by values)
  • How to check encrypted tablespace in the Database
  • How To Export -Import TDE Master Encryption Key
  • How to Configure Auto Login TDE Wallet

Copyright © 2023 : TechGoEasy

  • Hire me
  • Privacy Policy
  • Contact Us
  • New? Start Here
  • About Us