Finding and Tuning Problem Queries in Oracle

Problem queries are queries accessing or hoarding too many resources. Problem queries are common in ah-hoc user SQL environments and from inexperienced applications developers. Many years ago databases contained small amounts of information and thus ad-hoc environments were common. MS-Access is a clear example of an ad-hoc environment, hwoever, MS-Access is a single user environment. In general, a multi-user database environment is not susceptible to ad-hoc SQL user queries. As far as inexperienced applications developers submitting inappropriately structured queries it is a partial DBA responsibility that these queries are located, killed and that the application developers concerned are instructed as to how to build efficient queries.

The V$SQLAREA and V$SQLTEXT performance views are used to seek out problem queries. Note that these queries find based on high disk I/O and buffer reads. These views will display high impact queries but not the reasons for high impact. Typical problem queries involve incorrect ordering in WHERE clauses, particularly difficult to determine in mult-table/view joins. Other culprits involve over-usage of SQL Functions in WHERE clauses such as a SUBSTR function. A SUBSTR of an index column in a WHERE clause is not a match for the index column since the function is applied before the WHERE clause condition is checked. Therefore since the SUBSTR result does not match the index column therefore the index is completely ignored and a full table-scan is performed, in the case of a multi-table join a cartesian product results.

Currently Executing SQL Statements

SELECT sql_text FROM v$sql WHERE users_executing > 0;

High Physical Reads

SELECT disk_reads, sql_text FROM v$sqlarea WHERE disk_reads > 5000 ORDER BY disk_reads DESC;

High Logical Reads

SELECT buffer_gets, sql_text FROM v$sqlarea WHERE buffer_gets > 10000 ORDER BY buffer_gets DESC;

The AUTOTRACE Facility

Using the AUTOTRACE facility requires the creation of the PLAN_TABLE before-hand.

CREATE TABLE plan_table 
	 statement_id VARCHAR2(30)
	,timestamp DATE
	,remarks VARCHAR2(80)
	,operation VARCHAR2(30)
	,options VARCHAR2(30)
	,object_node VARCHAR2(128)
	,object_owner VARCHAR2(30)
	,object_name VARCHAR2(30)
	,object_instance FLOAT
	,object_type VARCHAR2(30)
	,optimizer VARCHAR2(255)
	,search_columns FLOAT
	,id FLOAT
	,parent_id FLOAT
	,position FLOAT
	,cost FLOAT
	,cardinality FLOAT
	,bytes FLOAT
	,other_tag VARCHAR2(255)
	,other LONG
SELECT COUNT(name) FROM <tablename> WHERE name = '<whatever>';

Shown immediately below is the data content of the default installation user SCOTT table called EMP. Below that is an analysis of a selection from the SCOTT.EMP table.

SQL> select * from scott.emp;

    EMPNO ENAME      JOB             MGR HIREDATE        SAL      COMM    DEPTNO
--------- ---------- --------- --------- --------- --------- --------- ---------
     7369 SMITH      CLERK          7902 17-DEC-80       800                  20
     7499 ALLEN      SALESMAN       7698 20-FEB-81      1600       300        30
     7521 WARD       SALESMAN       7698 22-FEB-81      1250       500        30
     7566 JONES      MANAGER        7839 02-APR-81      2975                  20
     7654 MARTIN     SALESMAN       7698 28-SEP-81      1250      1400        30
     7698 BLAKE      MANAGER        7839 01-MAY-81      2850                  30
     7782 CLARK      MANAGER        7839 09-JUN-81      2450                  10
     7788 SCOTT      ANALYST        7566 19-APR-87      3000                  20
     7839 KING       PRESIDENT           17-NOV-81      5000                  10
     7844 TURNER     SALESMAN       7698 08-SEP-81      1500         0        30
     7876 ADAMS      CLERK          7788 23-MAY-87      1100                  20
     7900 JAMES      CLERK          7698 03-DEC-81       950                  30
     7902 FORD       ANALYST        7566 03-DEC-81      3000                  20
     7934 MILLER     CLERK          7782 23-JAN-82      1300                  10

14 rows selected.
SQL> set autotrace on;
SQL> select count(ename) from scott.emp;


Execution Plan
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'EMP'

          0  recursive calls
          4  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
       1098  bytes sent via SQL*Net to client
        670  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed