Browse DevX
Sign up for e-mail newsletters from DevX


Some "Hints" for Mastering SQL Tuning

Help your database optimize SQL queries by including hints in your SQL statements. Used correctly, hints can reduce the running time of your queries significantly.

y wife and I recently had a baby. In the labor and delivery room, the medical staff connected several sensors to my wife's tummy to monitor the baby's heartbeat, contractions, blood pressure etc. Every few hours, the obstetrician pulled all the data from different times. While I was eagerly looking for normal values, I was surprised to see how fast the system retrieved and displayed data. The data-retrieval queries it used must have been highly optimized. This experience inspired me to write an article on SQL tuning.

SQL Optimization
When an application submits a SQL query to the database server, the server first parses the SQL. It checks the SQL syntax, for security access and prepares the query for execution. Query optimization involves determining the optimal path for executing the query. Each database comes with built-in intelligent algorithms to figure out the best possible way to execute the query. For complex queries involving joins between eight different tables, the optimizer could spend as much as 30 minutes to find an effective execution path before the server actually executes the query. The server uses either a cost-based optimizer or a rule-based optimizer to figure out the best execution path for the query. This article details more about the cost-based optimizer, as rule-based optimizers are becoming deprecated.

A cost-based optimizer tries to estimate the cost of satisfying each execution plan. The cost is based on the estimated number of database reads required as well as on the requirements for sorting. The lower the cost means the better use of system resources. However, lower-cost queries do not guarantee faster execution.

Once the server parses a query, it keeps it in the shared pool. That way, when the same query is submitted again, the server need not parse it. The best use of this feature is using the bind variables for frequently run queries wherever applicable.

Hints for Your SQL Statement
Hints are instructions that you include in your SQL statement for the optimizer. Using hints, you can specify join orders, types of access path, indexes to be used, and the intended optimization goals. You must place the hints within /*+ <hint> */, and you should place them after the SELECT key word.

Note: If a user doesn't follow the syntax, the optimizer will not prompt the user with a syntax error. Instead, it will treat it as no hint.

Thanks for your registration, follow us on our social networks to keep up-to-date