SQL Tuning Tools   «Prev  Next»

Lesson 4 Working with SQL developers
Objective List techniques for working with SQL developers.

Working with SQL Developers

SQL Programmer Tips

SQL programmers have very different goals from DBA's. To a programmer, the main goal is to get a working SQL statement as quickly as possible without any regard for the performance of the SQL statement. This problem is compounded by the declarative nature of Oracle SQL, where any single requirement can be fulfilled by many SQL statements, each producing the same result set but with radically different execution times. The purpose of this module is to show you how to work with and educate your SQL programmers about the issues surrounding SQL tuning.
Databases have been in use long before the personal computer became readily available. IBM developed the Structured Query Language standard more than 50 years ago as a way to retrieve data from their new “relational” database. A decade later, Oracle released the first commercial relational database that used SQL, and SQL has become the de-facto query language for relational databases.

Database History

The history of database research over the past 30 years is one of exceptional productivity that has led to the database system becoming arguably the most important development in the field of software engineering. The database is now the underlying framework of the information system, and has fundamentally changed the way many organizations operate. In particular, the developments in this technology over the last few years have produced systems that are more powerful and more intuitive to use. This has resulted in database systems becoming increasingly available to a wider variety of users. Unfortunately, the apparent simplicity of these systems has led to users creating databases and applications without the necessary knowledge to produce an effective and efficient system.
It is a truism that the goals of the SQL programmer and the DBA are not the same. Many developers realize that their primary goal is to write and test SQL as quickly as possible without focusing solely on the performance. This is especially a problem because the same SQL statement may be written in a number of different ways, all return the same result, but with radically different performance. Consider a few of these examples in the following SlideShow.


Oracle SQL Efficiency

1) Consider a common DBA query against the data dictionary to show all users who do not have privileges that are granted via a role.
1) Consider a common DBA query against the data dictionary to show all users who do not have privileges that are granted by means of a role. This query runs in 18 seconds. 
SELECT
  USERNAME
FROM
  dba_users
WHERE
  USERNAME NOT IN
  (SELECT
     GRANTEE
   FROM
     dba_role_privs);


2) To tune this query, we could re-write the same query to utilize temporary tables. This query runs in less than three seconds.
2) To tune this query, we could re-write the same query to utilize temporary tables. This query runs in less than three seconds.

3) Another good example is between SQL that is executed with the rule-based optimizer versus the cost-based optimizer. This query uses the rule hint.
3) Another good example is between SQL that is executed with the rule-based optimizer versus the cost-based optimizer. This query uses the rule hint.

4) This query is identical to the previous query with the exception that it uses the cost-based "first-rows" hint.
4) This query is identical to the previous query with the exception that it uses the cost-based "first-rows" hint. This is a common type of DBA query that joins a table against itself to display tables that have extended.

5) The EXPLAIN PLAN for the rule-based query above uses two index range scans with AND EQUAL test, inside a nested loop followed by a full-table scan
5) The EXPLAIN PLAN for the rule-based query above uses two index range scans with AND EQUAL test, inside a nested loop followed by a full-table scan.

6) The EXPLAIN PLAN for second query (cost-based) does an index range scan, a table access by ROWID, and a full-table scan (TABLE ACCESS FULL)
6) The EXPLAIN PLAN for second query (cost-based) does an index range scan, a table access by ROWID, and a full-table scan (TABLE ACCESS FULL).

7) Because the cost-based query is invoking full-table scans "TABLE ACCESS FULL," we would expect the rule-based optimizer will out perform the cost-based optimizer for this query.
7) Because the cost-based query is invoking full-table scans "TABLE ACCESS FULL," we would expect the rule-based optimizer will out perform the cost-based optimizer for this query. By executing teh SQL IN SQL*Plus with the SET TIMING ON option, we see the dramatic difference in execution time.

In lieu of the fact the developers do not always create SQL that will perform perfectly, the DBA can employ a number of techniques to bridge this gap.

Facilitating SQL Management Techniques employed by DBAs

  1. Become an SQL tuning expert and offer to share your expertise with the developers
  2. Create SQL tuning tip sheets.
  3. Install Oracle SQL tuning tools such as (SQLab or SQL*Analyzer) and encourage the developers to use these products.
  4. Be a mentor. Let the developers know that they will not be criticized or ridiculed for sloppy SQL.
Now that we understand the issues involved with working with SQL developers, let us investigate compelling techniques for securing Oracle SQL with stored procedures.

SEMrush Software