Oracle SQL Tuning Tips and Tricks Tutorial -3

Hi,

I will continue to explain Oracle SQL Tuning Tips and Tricks in this article.

 

Read second article before this.

 

 

21- Use Partitioning for Large Tables

If Slow query contains large tables, then you can use table partitioning to make faster query.

 

You can read the following article to learn Partitioning detailed

 

22- Use CASE statements instead of DECODE

You can use CASE statements instead of DECODE for the readability and performance of the query

 

23– Use IN instead of OR

You should use IN instead of OR in the where clause. IN is used for selective predicate is in the sub query,and EXISTS is used for selective predicate is in the parent query.

 

 

 

24– Avoid Complex expressions in SQL

You should avoid using complex expression in SQL to prevent the optimizer from assigning valid cardinality or selectivity estimates.

 

--Do not use:
WHERE SUBSTR(a.id, INSTR(b.id, ',') - 1) = SUBSTR(b.id, INSTR(b.id, ',') - 1)

--Use this instead of above:
WHERE a.id = b.id



--Avoid:
WHERE id = NVL(:a1, id)

WHERE NVL(id,-1) = ( 1, etc...)

NVL (col1,-999) = ….


TO_DATE(), TO_NUMBER(), and so on




25– Prefer INNER JOIN instead of OUTER JOIN

Inner join is recommended instead of Outer Join if possible. Because Outer join limits the database optimization options

 

 

26– Avoid ORDER BY and GROUP BY If possible

it is recommended to avoid ORDER BY, GROUP BY clause , if using ORDER BY, GROUP BY clause is not mandatory for you. Because ORDER BY, GROUP BY causes sorting and result in one of the most expensive operations in SQL execution.

 

 

 

27– Avoid Hint usage

Hint usage is not recommended instead optimizing your code.

Hint usage is the last thing you should consider for Performance tuning or it is a temporary solution.

 

 

28– Drop Unused Index

You should drop unused index on tables, monitor all indexes and check them if they are unnecessary and unused, you should drop them.

Most common mistake is using Index, Index is not good everytime. You should monitor it, If it is good for performance, you can keep it, if not you should drop it.

Read the following article to learn Index detailed.

 

 

29– Avoid using wildcard (%)

Using wildcard (%) at the beginning of a predicate is a known performance problem in all databases.

Wildcard (%) usage LIKE ‘%abc’ causes full table scan, so avoid Using wildcard (%).

 

 

 

30–  Use Hash Join for Large tables Join

You should examine execution plan carefully, If 2 large tables are joined, If they don’t use Hash Join, use Hash Join hint. If a large table is joined with a small table,  If they don’t use Nested loop Join, use Nested loop Join hint.

 

 

 

 

Do you want to learn Oracle Database Performance Tuning detailed, then read the following articles.

Mehmet Salih Deveci

I am Founder of IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress mehmetsalih.deveci@outlook.com.-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  mehmetsalih.deveci@outlook.com a mail atabilirsiniz.

Leave a Reply

Do NOT follow this link or you will be banned from the site!