Oracle Lite query is too slow


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 ?

Who is Participating?
neo9414Connect With a Mentor Commented:
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.

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)
Ivo StoykovCommented:
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...


Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

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?

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.

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 :-)

actonwangConnect With a Mentor Commented:
>>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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.