Home » RDBMS Server » Performance Tuning » At specific table size i'm seeing a 10x increase in query time
At specific table size i'm seeing a 10x increase in query time [message #212475] Fri, 05 January 2007 08:49 Go to next message
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 #212491 is a reply to message #212475] Fri, 05 January 2007 09:42 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
4 or 5 million records really does not matter, especially with a JOIN. It depends on the selectivity and join cardinality and available statistics.
Did you generate the statement PLAN or traced the sql?
Did the plan differ significantly?

Re: At specific table size i'm seeing a 10x increase in query time [message #212492 is a reply to message #212475] Fri, 05 January 2007 09:43 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
post EXPLAIN_PLAN for both cases.
What OS, OS version & Oracle version?
Are object's statistics kept current in both cases?
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
rleishman
Messages: 3724
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 Go to previous messageGo to next message
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
Re: At specific table size i'm seeing a 10x increase in query time [message #212932 is a reply to message #212810] Mon, 08 January 2007 19:46 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Perhaps. Before I start guessing even more, trace both sessions and post the TK*Prof output here. In the slow one, we should see a non-proportional increase in Query and Disk.

Ross Leishman
Previous Topic: Hints index combine
Next Topic: Cognos tuning
Goto Forum:
  


Current Time: Wed Dec 07 06:33:09 CST 2016

Total time taken to generate the page: 0.11451 seconds