Problem solve Get help with specific problems with your technologies, process and projects.

Query fast on SQL Server, slow on Oracle

We have the following Oracle query:
select col1,col2,col3,...,coln 
  from item 
 where collection_id=9999 
   and (contains(title,'word1 or word2 or word3')>0 
    or contains(summary,'word1 or word2 or word3')>0 
    or contains (body, 'word1 or word2 or word3')>0 ) 
   and creation>8888 
 order by creation desc;
When we run this query in on a powerful SUN server, we find it very solw. When we run the same query on a poor Windows 2000 server with MS SQL Server, we find it fast. It is very slow in Oracle especially when we retrieve large amounts of data. It takes few seconds to start listing the SQL query ouput, but a long time to finish listing all output data. In SQL Server, it finishes listing all output data in a very short time. This is general for all queries (SQL Server finished listing output query data before Oracle on SUN). Note: relevant fields are indexed on a tablespace on different hard disk (LUN) than tables TS. We use Oracle 9i installed on a powerful SUN server with RAID 5 Any help?

Did you look at the execution plans? That's where you are going to be able to determine what is truly going on....

You can get the query's execution plan by using the EXPLAIN PLAN command or by using AUTOTRACE in SQL*Plus as a couple of quick options. Make sure you have a PLAN_TABLE table created (use the script $ORACLE_HOME/rdbms/admin/utlxplan to create it). For a quick/easy way to start, simply start SQL*Plus, enter the command SET AUTOTRACE TRACEONLY. This will then cause any query you subsequently issue to show it's execution plan without attempting to run the query.

Here's an example using the SCOTT.EMP table:

SQL> set autotrace traceonly
SQL> select * from emp where empno = 7934 ;

Execution Plan
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=32)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=1 Card=1 Bytes=32)

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

For More Information

You can see in this example how the query used the index. Run your query and look at its execution plan and see what's really going on. Feel free to re-post your question after you've gathered this information if you need help determining why the query is behaving the way it is.

Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.