Identifying DBA Roles in SQL Tuning

Historically, the Oracle DBA assumes overall responsibility for the performance and tuning of the Oracle database. With this responsibility comes the challenge of monitoring all of the SQL that is written and executed against the Oracle database. SQL statements can originate from a variety of sources, including PC-based SQL code generators. It can be a formidable challenge for the DBA to keep track of the origin of all of these statements. The following Slide show describes the potential origins of SQL statements.

While the method varies from shop to shop, the goal of the DBA is to manage and control all of the SQL and ensure that all SQL statements are properly tuned. While a formal SQL management plan would be very desirable, any plan can be very difficult to enforce. WithOracle, SQL can be written and executed from any Oracle client without the knowledge or consent of the DBA. The only known way to enforce an SQL management plan is to deny access to the Oracle tables, except through GRANT EXECUTE on stored procedures. However, many shops find this cumbersome because the DBA acquires responsibility for stored procedures and packages for every SQL statement. Now let us look at methods that the DBA can use to enforce SQL standards.