Why is this query so slow?

Question

I have tables FOO and BAR. FOO has a foreign key to BAR's PK.

When I execute the following query it takes several seconds.

select foo.name, foo.description, bar.quadrant from FOO, BAR
where FOO.BAR_ID = BAR.BAR_ID

Here is my explain plan:

OPERATION        OBJECT_NAME     OPTIONS     COST 
SELECT STATEMENT                                 39 
 HASH JOIN                                       39 
  TABLE ACCESS   BAR             FULL            2 
  TABLE ACCESS   FOO             FULL            36 

FOO has 6000 records in it and BAR only has 5. The BAR_ID column is a NUMBER.

This is running on Oracle 10g and it is taking ~3 seconds to complete. That seems extreme given how quickly it performs other queries.

EDIT table defs:

CREATE TABLE BAR
 (
    "BAR_ID" NUMBER NOT NULL,
    "QUADRANT" VARCHAR2(100 BYTE) NOT NULL,
    CONSTRAINT "BAR_PK" PRIMARY KEY ("BAR_ID")
 )

 CREATE TABLE FOO
 (  "FOO_ID" NUMBER NOT NULL, 
    "BAR_ID" NUMBER NOT NULL, 
    "NAME" VARCHAR2(250 BYTE) NOT NULL, 
    "DESCRIPTION" VARCHAR2(250 BYTE),
    CONSTRAINT "FOO_PK" PRIMARY KEY ("FOO_ID"), 
    CONSTRAINT "FOO__FK1" FOREIGN KEY ("BAR_ID") REFERENCES BAR ("BAR_ID") ENABLE
 );

Answer 1

Does the table get frequent updates?

Is foo.description a huge CLOB?

Is network latency making it seem like the query is taking a long time?

Are these tables really complex views?

Were the tables once very large and have since had lots of data deleted?

Answer 2

There's a bucket load of instrumentation built into Oracle for investigating this sort of issue.

Start with this paper:

http://method-r.com/downloads/doc_download/10-for-developers-making-friends-with-the-oracle-database-cary-millsap

Answer 3

Get a TKPROF trace for your query to see what really happens - explain plan is just an estimate.

Basically, execute ALTER SESSION SET SQL_TRACE = TRUE command before your query, execute the query, and then ALTER SESSION SET SQL_TRACE = FALSE. Then find the trace file produced from location determined by USER_DUMP_DEST parameter (look into v$parameter view). Use TKPROF utility to process the raw trace file into more readable format, and examine the results (and post them here, too).

(See Using SQL Trace and TKPROF from Oracle.com for more information.)

Answer 4

From what I can remember, Oracle will see this as a simple join that will ignore the indexes. The basic idea is that because you are not limiting the data in either table and just joining them together, it thinks that a full table scan will work better. If the foo table has null in the bar_id column for several rows, then you may want to use the index hint.

As an example, if you run the query based on a single bar_id, the explain plan will likely use the indexes as expected. Without the index it will do a full scan on the bar table, because it is very small, and a full scan on the foo table because you are not filtering out any values for bar_id.

One last note is to make sure you update statistics on the tables and indexes. This would be important for a sparse index as Oracle may realize the index can significantly change the cost of the query.

Answer 5

Are you sure you have good statistics? I created a test case from your DDL and saw this plan before statistics:

--------------------------------------------------------------------------- 
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     | 
--------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT   |      |  4996 |  1619K|    10  (10)| 00:00:01 | 
|*  1 |  HASH JOIN         |      |  4996 |  1619K|    10  (10)| 00:00:01 | 
|   2 |   TABLE ACCESS FULL| BAR  |     5 |   325 |     3   (0)| 00:00:01 | 
|   3 |   TABLE ACCESS FULL| FOO  |  4996 |  1302K|     6   (0)| 00:00:01 | 
--------------------------------------------------------------------------- 

(If you get the dbms_xplan output you'll also see "dynamic sampling used for this statement").

After doing this:

SQL> begin dbms_stats.gather_table_stats(user,'FOO'); end;
  2  /

PL/SQL procedure successfully completed.

SQL> c/FOO/BAR/
  1* begin dbms_stats.gather_table_stats(user,'BAR'); end;
SQL> /

PL/SQL procedure successfully completed.

I see:

--------------------------------------------------------------------------------------- 
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     | 
--------------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT             |        |  4996 |   131K|     9  (12)| 00:00:01 | 
|   1 |  MERGE JOIN                  |        |  4996 |   131K|     9  (12)| 00:00:01 | 
|   2 |   TABLE ACCESS BY INDEX ROWID| BAR    |     5 |    40 |     2   (0)| 00:00:01 | 
|   3 |    INDEX FULL SCAN           | BAR_PK |     5 |       |     1   (0)| 00:00:01 | 
|*  4 |   SORT JOIN                  |        |  4996 | 94924 |     7  (15)| 00:00:01 | 
|   5 |    TABLE ACCESS FULL         | FOO    |  4996 | 94924 |     6   (0)| 00:00:01 | 
--------------------------------------------------------------------------------------- 

Answer 6

It is very reasonable to make a full table scan to FOO table, the table has 4996 row and you right a query that you ask oracle to "Send all the Foo records along with their bar.quadrant"