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.
SELECT sql_text FROM v$sql WHERE users_executing > 0;
SELECT disk_reads, sql_text FROM v$sqlarea WHERE disk_reads > 5000 ORDER BY disk_reads DESC;
SELECT buffer_gets, sql_text FROM v$sqlarea WHERE buffer_gets > 10000 ORDER BY buffer_gets DESC;
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 );
SET AUTOTRACE ON; 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;
COUNT(ENAME)
------------
14
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'EMP'
Statistics
----------------------------------------------------------
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
SQL>