Solved

Oracle Lite query is too slow

Posted on 2006-07-05
7
1,154 Views
Last Modified: 2011-10-03
Hi,

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 10.2.0.2. (This problem also exists for the version 10.2.0.1)

Can I improve the performance of this query ?

0
Comment
Question by:GregBo
7 Comments
 
LVL 35

Expert Comment

by:Raynard7
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)
0
 
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...

HTH

I
0
 
LVL 12

Expert Comment

by:jwahl
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.CANV_EDITION --> NUMBER?
pi.SECTION_CODE = '01' --> VARCHAR2?


0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 9

Expert Comment

by:neo9414
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.

0
 
LVL 4

Expert Comment

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

Thanks,
-Sudhi.
0
 
LVL 9

Accepted Solution

by:
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.

0
 
LVL 19

Assisted Solution

by:actonwang
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.

Acton
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
one-way data "masking" MD5 sql 26 146
sql query 5 70
Oracle - Create Procedure with Paramater 16 57
Oracle 12c database link between pdb not working 20 48
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

867 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now