Oracle queries sometimes are awfully slow, why??

Posted on 2003-11-06
Medium Priority
Last Modified: 2008-01-09
Hi all,

I have a table with 3 million rows. The table has an id column and 16 fp columns. All columns are indexed. The types of the fp columns are INTEGER.

I perform the following query:

select count(*) from MYTABLE where BITAND(fp1,2144163094) = 2144163094 AND BITAND(fp2,1689182963) = 1689182963 AND BITAND(fp3,180991820) = 180991820 AND ... ... ...  AND BITAND(fp,-2023498656) = -2023498656;

So I use BITAND on all fp (INTEGER type) colums.

This query takes 17 second to perform. Is it possible? Is the Oracle's BITAND function too slow or the integer type of the oracle?  Should I use RAW type?

I have a P4 2.4G , 2 GB RAM, Win XP, Oracle .

Anyway the 'select count(*) from MYTABLE' takes 17 second as well. Maybe some Oracle tuning should help.(?)
Question by:fifty_
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
  • 3
  • 3
  • 2
  • +2

Expert Comment

ID: 9694989
Have you tried to run an explain plan on your query?  What was the outcome?
LVL 23

Accepted Solution

seazodiac earned 172 total points
ID: 9695115
ok, try these steps:

1. make sure your query is very selective (i.e., the rows in the results should be less than 5% of the total rows 3millions)
2. there is another caveat with BITAND function. BITAND returns a binary integer, so to use it in SQL you will need to convert it to a numeric.
That means when you are using BITAND function in where clause like this:
BITAND(fp2,1689182963) = 1689182963, oracle has to do some implicit data conversion(from binary integer to a numeric). to make it more efficient, in your where condition, explicity convert it by using this : BITAND(fp2,1689182963) + 0 = 1689182963,

So for you to speed up the query, you should Build a FUNCTION-BASED index on all the fp columns like this:

CREATE INDEX fp1_mytable_ix ON mytable (BITAND(fp1) +0);

of course, in case you don't know already, you need to make sure the following things are done before you use function-based index:

You must have the system privelege query rewrite to create function based indexes on tables in your own schema.
You must have the system privelege global query rewrite to create function based indexes on tables in other schemas
For the optimizer to use function based indexes, the following session or system variables must be set:

LVL 15

Expert Comment

ID: 9695399
Seazodiac, I don't understand what you just said: If BITAND returns a binary_integer which Oracle will implicitly convert, how does changing to BITAND(...)+0 make any difference?  Surely Oracle will still do the implicit conversion either before or after it adds the 0?  In any case, why would an explicit conversion (e.g. using TO_NUMBER) be faster than an implicit one?
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

LVL 23

Expert Comment

ID: 9695622
I think it does get better speed when you do things explicitly.Especially, this explicit conversion is  embedded in the function creation. that's why Oracle encourage you to do things explicitly.

one example:

oracle does recommend the use of " select * from emp where hiredate < to_date('10-OCT-03', 'DD-MON-YY')" , NOT " select * from emp where hiredate < '10-OCT-03'"
LVL 15

Expert Comment

ID: 9695816
Yes, but that is because you are more likely to get errors if you are not explicit.  to_date('10-OCT-03') would be just as bad.  Whether implicitly or explicitly that conversion IS going to be done and will take however long it takes.

OK, there is possibly a small PARSE penalty for implicit conversions: Oracle has to decide how to treat the values.  But parsing overhead is insignificant unless you are actually parsing 1000s of times.

I feel a benchmark test coming on.  Maybe tomorrow...
LVL 23

Expert Comment

ID: 9695871

Andrew, I can confirm that because I meant to explain exactly the same thing (penalty in the parse stage) last time, but I don't want to turn it into a long-winded story.


Expert Comment

ID: 9696452
I do not think the implied vs explicit conversion is the issue here but makes for a nice diversion.  As stated you need to look at the selectivity of the data, probably a FTS is more efficient.  Is this query going to be run often?  I would not be adding all the indices if not.  You also have to consider the DML implications on adding these indices on a heavy DML table.  Consider your requirements and implications thereof.


Assisted Solution

grim_toaster earned 164 total points
ID: 9699696
As a quick point on the parsing issue, wouldn't it be more efficient to only convert the passed in value to an Integer in the equals thing if you're worried about it?  As in:
BITAND(fp1, 1689182963) = CAST(1689182963 AS INTEGER)

Also, do you really need the bitand (I may be completely mistaken here)?  Couldn't you just use
fp1 = 1689182963
And then put a single index joining on all of the columns?  Therefore doing a single index scan instead of what would appear to be a FTS (calling a function on a column, without a function-based index, would force it to the full table scan).

Author Comment

ID: 9699721
Maybe the BITAND function is a little bit slow.

Is it possible to use more cache for this SELECT statement ? It would be much faster if the fp columns were in the cache.
LVL 15

Assisted Solution

andrewst earned 164 total points
ID: 9700144
I think this is the most significant point:

"Anyway the 'select count(*) from MYTABLE' takes 17 second as well"

So we know that the query with all the BITANDs is doing a full table scan, but at least it is not taking any longer due to the BITANDs.  So:

How many of the 3 million rows does the BITAND version return?  If it is a very small proportion, an index would probably help.  This could be on just one of the columns or many.  It would need to be a function-based index due to the BITANDs - unless, as grim_toaster suggest, you don't actually need the BITANDs.

On the other hand, if it returns a higher proportion of the rows there may be no benefit to using an index.  In that case you are left with the fact that you must use a full table scan, and that currently takes 17 seconds.  Can that be reduced?  Not by query tuning, for sure.  The first thing to check would be whether the data is stored efficiently.  For example, if at one time there were 100 million rows and 97 million had been deleted, the full table scan would read all those empty blocks as well as the blocks that contain the 3 million rows.  

Featured Post

Industry Leaders: 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 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…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

801 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