Go Premium for a chance to win a PS4. Enter to Win


Oracle Lite query is too slow

Posted on 2006-07-05
Medium Priority
Last Modified: 2011-10-03

I have the following problem. I am developing an application using Oracle Lite. One of queries have a strange behavior.
If my computer is just switched on, the query runs 10 seconds (which is unacceptably slow). Next time I run the query it runs 1 second. All other queries run normally.

The query is very simple. something like this:

select pi.item_code
  from gg_price_items pi
  where pi.CANV_CODE = 'ROTT'
    and pi.CANV_EDITION = 2006
    and pi.SECTION_CODE = '01';

The table has approximately 2000000 records. All necessary indexes exist. Query returns approximately 5000 records.

I use Oracle Lite (This problem also exists for the version

Can I improve the performance of this query ?

Question by:GregBo
LVL 35

Expert Comment

ID: 17040816
The reason the second query runs quickly is because the first time you ran it - the results and the execution plan were cached.

I would suggest that you look at the explain plan - and make sure it is not doing a full table scan - if it is you should use a query hint to pic the index that fits best for the data that you are using (preferably something with canv_code, canv_edition and section_code - in that order)
LVL 22

Expert Comment

by:Ivo Stoykov
ID: 17040825
Hello GregBo,

> Next time I run the query it runs 1 second
This is normal because after the 1st run the query is in the cache and that's why it runs faster.

The speed dependa on meny factors... but for this query itself the only thing you could do is to use bind variables instead of literals. This will asure less parse time and probably will speed the run time.

Additionally you have to analyse the table tructure, indexes and so on there might be some possible improvments...


LVL 12

Expert Comment

ID: 17040862
also make sure that the datatypes in the table correspond with the datatypes in the query.
otherwise the indexes can not be used.

pi.SECTION_CODE = '01' --> VARCHAR2?

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.


Expert Comment

ID: 17040889
I am sure that the indexes are being used else the full table scan would have taken much more time for a table with 2 million records.

Can you post the execution plan for the above query.

here is what you can do in the mean time.

1. gather statisics for the above table and the indexes build on it using dbms_stats package.

2. See if first_rows hint helps you...  
   select /*+ first_rows */ pi.item_code
   from gg_price_items pi
   where pi.CANV_CODE = 'ROTT'
    and pi.CANV_EDITION = 2006
    and pi.SECTION_CODE = '01';

3.. Try building a composite index (if you don't have one already) on CANV_CODE,CANV_EDITION and pi.SECTION_CODE in that order and run your query. A composite index wil lbe faster than indexes on individual column.


Expert Comment

ID: 17040969
As explained by others, things take time to *warm-up* which is pretty clear in your case. Here is what I would do in your case:

1. Switch on the computer
2. Open a sqlplus session and:
- alter session set tracefile_identifier='badQueryPerf';
- dbms_monitor.session_trace_enable(waits=>true, binds=>true)
- run the query.
3. Disconnect.

After sometime, open another session and:
- alter session set tracefile_identifier='GoodQueryPerf'
- dbms_monitor.session_trace_enable(waits=>true, binds=>true)
- run the query.
- disconnect.

The above would generated two trace files in the directory pointed by USER_DUMP_DESTINATION parameter with the filenames containg the strings 'badQueryPerf' and 'GoodQueryPerf'. Now you can do a tkprof on these two files and compare where exactly the time is spent in the badly performing query.

I would guess that most of the time spent would likely be on i/o. There might also be a good chance that it cold be something else too. But looking at the trace output would tell you 'why' its taking time :-)


Accepted Solution

neo9414 earned 600 total points
ID: 17041005
Wen you fire a query, Oracle will see if the blocks containing the rows are already present in the buffer cache. If not, then it will read those blocks from the data files and bring it to the buffer cache. This is the case when you run the query for the first time. When you run the query next time, the data is already present in the buffer cache and there is no need of physical i/o. as mentioned in above post, the physical i/o seems to be the problem. The trace file will definitely give you all the info.

LVL 19

Assisted Solution

actonwang earned 150 total points
ID: 17042914
>>select pi.item_code
  from gg_price_items pi
  where pi.CANV_CODE = 'ROTT'
    and pi.CANV_EDITION = 2006
    and pi.SECTION_CODE = '01';

   This is a very straightforward query. If you have indexes on them properly, you have done enough for this query. You don't need to dig up more to do it.

   For specific query, the reason why it runs faster the second time, as lots of people pointed out, mainly because:
   The query will go through parse-->execution-->return 3 phases:
   (1) parse time will be avoided  second time.
   (2) execution time will be a lot of less especially in your case because you can find blocks inside db cache instead of reading from disk. other words, the second time only incur logic I/O vesus physical I/O.
    As a side note, for your query, I suggest you use "bind variables" in case you will reuse it next time using different parameters.


Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses

782 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question