Oracle Lite query is too slow

Posted on 2006-07-05
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?

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


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 200 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 50 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

726 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