Click here to see additional details, references etc.
Performance tuning SQL queries in Oracle
Video tutorial showing how to optimise your SQL queries for maximum performance. Talks about indexes, statistics, hints and optimiser plans.
Video tutorial showing how to optimise your SQL queries for maximum performance. Talks about indexes, statistics, hints and optimiser plans.
Nice talk. DB Performance is still a bit of a mystery to me. I usually log the times that each query takes and if they are long, then I’ll point them to an expert like yourself. At least I will know who to ask for advice next time I write a horribly slow DB query :-)))
Heinz
@Kabutz, yes please feel free. Rather than manually logging the times, it would be more convienient – and helpful for the ‘expert’ – to generate a database level trace. Here is how to do that:
http://repettas.wordpress.com/2008/09/19/oracle-10046-trace-for-new-users/
With P6Spy, you have a JDBC driver that logs the times automatically. The beauty is that it is database independent, so I can apply the same technique whether the customer is using Sybase, DB2, Oracle, MySQL, etc. Once we have discovered that the database is indeed a bottleneck, we would pass that information to someone like you who can then run a detailed trace. Thanks for the link for Oracle though 🙂
Hi Hardeep,
I found your session quite informative, i want to learn how explain plan works and how can I understand explain plan and the terminology used in it, to tune my code. is this something you can help? any article ? book ? examples ?
From this blog I can suggest http://blog.2cent.me/computer/programming/20090224/sql-tuning-ref/ and http://blog.2cent.me/wp-content/uploads/2009/07/performance_tuning_guide.pdf
If you are interested further, I suggest this book: http://books.google.com/books?id=Ifdbua6o4agC&lpg=PP1&dq=book%20oracle%20sql%20performance%20tuning&pg=PP1#v=onepage&q=book%20oracle%20sql%20performance%20tuning&f=false
Hi Hardeep,
Nice to see your blog and impressive articles. I am looking for PLSQL perfromance quide for Oracle from basic to Advance which will help in Live Scenarios.
Regards,
Hello Vivek, Thanks for the appreciation. We do not have anything on PLSQL performance though, and you have given us an idea for future.
The video is open for all, and does not have any access restrictions. Please try to access it using the link below:
http://www.youtube.com/watch?v=GVZzI-4gze0
Hi,
I would like to know how to find out why the query running slow? What are the steps to look for finding out the reason for slow running query?
To see why the query is running slow, the first step is to review the plan Oracle is using to execute it. Look for the ‘explain plan’ command. Once you have the plan review it to see bottlenecks – is it using appropriate indexes etc. Needs some bit of experience, gets easier when you get the hang of it.
There was a problem with the video but I have fixed it, please watch it – will give you some starting ideas. Also review the PDF document available at http://blog.2cent.me/computer/20130410/oracle-performance-tuning/.