Oracle queries sometimes are awfully slow, why??

Posted on 2003-11-06
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_
  • 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 43 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?
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...
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 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 41 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 41 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

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
Oracle regular expression 6 45
Oracle RAC 12c 8 55
Difference in number of minutes between 2 timestamps 16 39
Query Records that don't match 8 32
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 …
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
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.
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.

911 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

17 Experts available now in Live!

Get 1:1 Help Now