Find the worst-performing SQL statements

There are many question about SQL interview which are very common for a freshers as below:

  • How to delete a duplicate record in SQL or in oracle database?
  • How to create table in SQL or DBMS?
  • How to convert date format in SQL or MySql?
  • How to auto increment in SQL ?
  • How to do self Join in SQL or DBMS?
  • How to connect to PHP admin by localhost/phpmyadmin ?

and many more.. which if you search over net you then you will get all the short or long answers.

But if as a developer you have been asked to find the worst-performing SQL statements in Oracle Database and tune it then what should be your approach?

Many would land in a big trouble as many have not ever come across, if you are among those then we can help you. There are some hidden views which are also said as dynamic views in Oracle Database starting as v$* / dba_*. This will help us in getting the result. See below:

V$SQLAREA and V$SQL are great views that you can query to find the worst-performing SQL statements that need to be optimised.

To find the worst queries:

select b.username username, a.disk_reads reads,
a.executions exec, a.disk_reads /decode
(a.executions, 0, 1,a.executions) rds_exec_ratio,
a.sql_text Statement
from V$sqlarea a, dba_users b
where a.parsing_user_id = b.user_id
and a.disk_reads > 100000
order by a.disk_reads desc;
USERNAME READS EXEC RDS_EXEC_RATIO STATEMENT

The value in DISK_READS column signifies volume of disk reads being performed on system. if you combined with executions then (DISK_READS/EXECUTIONS), return SQL statements that have most disk hits per statement execution. If you find any statement at the top of the list then it is most likely a problem query needs to be tuned.

There are many such Quarries which a developer need to know. Just getting the answers over the net will not satisfy the interviewer. you should also know the inner aspect of the query written in SQL or MySql. We have been discussion all these on our courses and also in our workshop which we have conduct as Job Oriented Computer Courses.

CONCLUSION: Its very mandatory that you should have the required industry skill along with your semester studies, both goes parallel, and you should always go for a bridge to up-skill yourself

Authors: Plentynum’s Content Development Team. This article is only the basics, and we will cover more the same in near future. We have only expressed our views on the topic which we have experienced with the companies and the students. We do not contradict any other sentiments or view. Your valuable comments in the Comment box are welcome.

About Plentynum:

We Develop, Support, Mentor and Empower to a new level of Excellence. We have designed Workshop and Training programme for developers, whereby all the above knowledge can be collected or enhanced. You can also Contact Us for further suggestion and placement support activities. Guidance for more opportunities and options is available. We got some valuable Comments to mention from our past students. You can also Contact us for any further knowledge or organisation skills, professional skills, which mostly of our team members possess. Drop your comments if you have any specific requirements.

Related Links:

Leave a Reply

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