(Novice warning). We have a table which has grown to about 1M rows and some of our queries are running very slowly. We have a number of indexes on this table. I am focusing on one particular query which is run often. This query will usually return zero, or only a few rows. It is taking 4-6 seconds. So I stripped it down and tried some variations, and I don't understand the results:
select PRIMKEY from MYTABLE where STATUS=6; // takes about 0.01 seconds
select JOBID from MYTABLE where STATUS=6; // takes about 0.01 seconds
select PRIMKEY,JOBID from MYTABLE where STATUS=6; // takes 4-6 seconds
The only difference is the selected columns. The explain plan shows that the 3rd query is not using any indexes:
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 512K| 22M| 11053 (1)| 00:02:13 |
|* 1 | TABLE ACCESS FULL| WORKITEMBEAN | 512K| 22M| 11053 (1)| 00:02:13 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("STATUS"=6)
Both of the first two queries do use indexes (this is the select on JOBID):
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 512K| 11M| 1365 (1)| 00:00:17 |
|* 1 | INDEX FAST FULL SCAN| WIB_INDEX1 | 512K| 11M| 1365 (1)| 00:00:17 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("STATUS"=6)
WIB_INDEX1 is on JOBID,STATUS.
At the most fundamental level I don't understand why the selected columns would affect the indexes used to locate the rows that match "status=6". Creating a new index with both PRIMKEY and JOBID does not help - the 3rd query still does a full scan and takes 4-6 seconds.
Here is the TKPROF output for the 3rd query, if it provides any clues:
TKPROF: Release 11.1.0.6.0 - Production on Thu May 20 11:16:59 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Trace file: aptos45_ora_3708.trc
Sort options: prsela exeela fchela
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 1 0.00 4.66 40697 40702 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 4.66 40697 40702 0 0
Misses in library cache during parse: 0
Misses in library cache during execute: 1
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 0 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
2 user SQL statements in session.
0 internal SQL statements in session.
2 SQL statements in session.
********************************************************************************
Trace file: aptos45_ora_3708.trc
Trace file compatibility: 11.01.00
Sort options: prsela exeela fchela
1 session in tracefile.
2 user SQL statements in trace file.
0 internal SQL statements in trace file.
2 SQL statements in trace file.
2 unique SQL statements in trace file.
40 lines in trace file.
33 elapsed seconds in trace file.
SQL Advisor does not give many any suggestions to help this statement run faster. We are using Oracle 11g.
Any ideas? Thanks...
Edited by: user540812 on May 20, 2010 11:37 AM