Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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?

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal


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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

704 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