| At specific table size i'm seeing a 10x increase in query time [message #212475] |
Fri, 05 January 2007 08:49  |
tolas
Messages: 5
Registered: January 2007
|
Junior Member |
|
|
I'm benchmarking an Oracle database for my company. We are dealing with EXTREMELY large and highly indexed databases. I am benchmarking a slightly smaller version of the database on some dev hardware.
The problem comes in with a specific JOIN query which does a LIKE lookup on one of the fields. I was testing this on database sizes up to 4.5 million records and getting query times of under 1 second. As soon as I went up to 5 million records however, I saw the query time jump up to 10 seconds. I attempted to repeat these results, and got the database size up to 4.75 million records still with query times under 1 second. Again right at 5 million records the query time jumped to 10+ seconds.
Seeking just one more confirmation of these results, I ran the test again. 4 Million records gave me the sub 1 second query times, but this time when I went to 4.5 million, I saw the 10+ second query.
Can any of you guide me on what is potentially causing this, and why on earth it happened at 5 million records twice, but the last time happened at 4.5 million.
Thanks
|
|
|
|
|
|
|
|
| Re: At specific table size i'm seeing a 10x increase in query time [message #212500 is a reply to message #212475] |
Fri, 05 January 2007 10:06  |
tolas
Messages: 5
Registered: January 2007
|
Junior Member |
|
|
It is definitely the LIKE lookup that is causing the slowdown, as I have other test queries that are not affected like this by the db size.
I am just running the tests through a JDBC connection, and do not have much info (or knowledge) on this particular database, or Oracle DB's in general.
The query in question looks like this:
SELECT TABLE1.*, TABLE2.*, TABLE3.*
FROM TABLE1, TABLE2, TABLE3
WHERE TABLE2.field1 LIKE 'aus%'
AND TABLE2.seq_id = TABLE1.seq_id
AND TABLE3.seq_id = TABLE1.seq_id
the TABLE2.field1 fields are filled with random sequences of 4 chars. And I limit the particular test case "aus%" to return roughly 66 results regardless of the table size.
Is it unheard of to have such a drastic increase in query time at a certain DB size?
|
|
|
|
| Re: At specific table size i'm seeing a 10x increase in query time [message #212566 is a reply to message #212500] |
Fri, 05 January 2007 18:04 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
It is probably performing a hash join. When both tables in a hash join exceed a certain size (where neither can be hashed into memory), then Oracle must partition BOTH of them into temp space. Since we already know there is insufficient memory to hash just one of them, the partitioned copies of both will certainly swap to disk.
Upshot is that you are effectively re-writing both tables before you join. There is no way around this unless you can keep one side of the join below that magic threshold.
Ross Leishman
|
|
|
|
| Re: At specific table size i'm seeing a 10x increase in query time [message #212810 is a reply to message #212475] |
Mon, 08 January 2007 07:51 |
tolas
Messages: 5
Registered: January 2007
|
Junior Member |
|
|
rleishman: thanks a lot, this is the type of thing I'm looking for.
One question though... I made sure that that LIKE lookup would only return ~60 results at all table sizes. Thus the ONLY thing changing between trials is the size of the tables, not the size of the ResultSet.
Would the problem you outlined still hold even if the result set is kept the same?
thanks again
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
