Oracle SQL and PL/SQL Tuning Basics for Developers
I am not a DBA but do work closely with the DBA when we are looking at performance improving slow or high cost queries which are slowing the system down or causing issues for users. These are a collection of tips I have learnt over the years developing and supporting Oracle Databases. Many of these can be solved by modern tools like the Tuning Advisor in the Enterprise Manager, however some problems are more ellusive and need some out-side-the-box thinking.
Database and Application performance is influenced heavily by the SQL running on your database. SQL Queries are used to select, insert, update and delete data from your database. The query is typically built by your design tool such as discoverer or jDeveloper. It could be formed from your underlying database views or PL/SQL packages you call in a query, report or form. A number of design decisions will influence the performance of your query, such as effective use of selection parameters, tables, join order and index utilization.
All Oracle databases provide statistics on the queries currently running on your database. These stats should be maintained by your DBA, often they will form part of their schedule of database maintenance tasks. All queries are allocated an execution plan and query cost. The query cost is a measurable number assigned by the dbms which represents the work involved in running each of your queries. Cost has no units of measure. The database reviews the resulting query when it hits the database server, and will run through different execution plans and assigns the cost value to running it via the different possible execution paths. The cost will be influenced by the status of current database statistics.
Identifying Poor Running Queries
There are several ways you can identify poor running queries.
- Your users will tell you when things run slow, do they have to wait more than a few seconds for a form or report to return results.
- Your DBA will be able to help find top-sql queries running on the database - they will be keen to help you reduce the cost these queries have on the system.
- You can find top-sql yourself using the built in reports in SQL Developer or Enterprise Manager, if you have access.
- You can easily predict the cost during development and testing, by using a production-like system.
* Query V$SQL - this table stores query statistics such as how long they took to execute and the cost.
DBA Pre-Checks
Make sure the tables involved have been analysed for appropriate use of Gather Stats at the appropriate time intervals and at the optimum percentages. This is more of a DBA than developer task, but it does good to ask the question.
Quantify "Poor" running Queries
You need to decide when queries can be considered poor in different circumstances. Consider when they are run, how long they take, how much load on the system and so forth.
- Queries may take much longer complete than can be reasonably expected.
- An Apps report which 5 minutes or more to complete could be considered OK, but in my experience can usually be improved, even on very large systems.
- An Apps form which takes more than a five seconds to complete, would be inadequate when dealing with a customer call.
- Take up lots of system resources when running - i.e. may not in it self seem slow to the user running the query, but it may take up so much system resource, that it slows everyone else's queries down.
- Query cost is higher than 5000 cost units
- Query has a high number of waits, fetches or reads
- Query has high cost and high number of executions, in other words it is ran many times.
- Query locks rows or tables for long periord.
You need to decide on it's use case and the urgency in terms of how badly it may be affecting the users or the wider system.
Tuning - Costs