SQL Tuning Tips and Tricks Tutorial in Oracle -2

Hi,

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

 

Read first article before this.

https://ittutorial.org/oracle-sql-tuning-tips-and-tricks-tutorial-1/

 

 

11- Avoid Function usage like Substr

Try to avoid using too many functions, especially don’t  use SUBSTRING, instead use LIKE clause.

Difference between substr and like is Such as “where name like ‘DEV%”;” will use an index whereas a substr such as “where substr(name,1,2) = ‘Dev’; will require a function-based index (and extended optimizer statistics) for optimal performance.

Using the substr clause is best for large production queries, when you provide a function-based index and deploy extended optimizer statistics on the target column.

 

12- Use Truncate instead of Delete

You should prefer truncate table instead of delete table if you delete too many rows on a table. Too many delete operations cause fragmentation and performance problems.

 

blank

 

 

 

 

13- Avoid Using Triggers

You should avoid using of TRIGGERS. Just use it as a last resort. It is better to use CONSTRAINT and STORED PROCEDURE to maintain the integrity of your databases.

 

blank

 

 

14- Use Bulk DML instead of Single DML

You should use Bulk Insert,Delete and Update instead of Single Insert,Delete and Update if you are executing Bulk operation.

Bulk insert,Delete and Update will give your system a huge performance boost.

 

blank

 

 

 

15- Avoid unnecessary columns

Unnecessary columns in Select clause incur more I/O on the database and increase network traffic.

You should analyze all columns in select or DML clause and if it is unnecessary, you should remove that columns in queries. Mostly Using SELECT * is not true for database and related query performance.

blank

 

 

 

16- Avoid Distinct clause

DISTINCT operator causes sorting which slows down the SQL execution time.

If it is possible, it is better that remove Distinct clause in Select.

 

blank

 

 

17- Use MINUS instead of EXISTS

Minus operator is mostly running in a faster execution plan than NOT IN and NOT Exists operator.

blank

 

 

18- Use Alias for table column

If you use more than one table in a query, you should use table aliases for column to avoid confusion.

 

 

 

19- Use Parallel hint for large data access

If you execute any SQL which is accessing large data sets, then you may use Parallel hint to perform it in parallel.

When you use parallel hint, you should check operating system CPU counts and cores to specify how many parallel will you use it.

If lots of people use parallel hint everytime, operating system and database can  slow down or power off because of CPU bootleneck.

blank

 

20- Use COUNT(1) instead of COUNT(*)

Using COUNT(1) instead of COUNT(*) is recommended  for SQL query performance optimization

 

 

 

I will continue to explain tips and tricks of SQL Tuning in the next article.

 

 

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

 

https://ittutorial.org/oracle-database-performance-tuning-tutorial-12-what-is-the-automatic-sql-tuning-and-how-to-automated-sql-tuning/

 

 1,089 views last month,  2 views today

About Mehmet Salih Deveci

blank
I am Founder of SysDBASoft IT and 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.

Check Also

blank

Alter System Flush Shared pool in Oracle

Hi, I will explain Alter System Flush Shared pool in Oracle in this post.  1,662 views …

Leave a Reply