0

im trying TPC-H benchmark on my Oracle database, testing takes place on a 10GB dataset. Currently I have target_memory set to 7GB, but the current test time is 18 minutes. The biggest problem is with the lineitem table(7,5GB), because its size does not fit in the cache and with each new query, all data must be reloaded from disk. Do you have any ideas how to speed up the test?

I've already tried the parallelization offered by the optimizer, but it ran even slower with that test due to the HDD and index doesnt work here, because for example in Q1 Oracle need to proccess almost 97% of table lineitem, which is the largest table with a size of 7.5 GB.

SItypack
  • 41
  • 5
  • 1
    There's not much we can tell you. Like, how fast are your disk(s)? You haven't told us anything about your hardware, version of Oracle, what indexes you have have added/dropped from the TPC-H spec...find the slowest bit, and try tuning that specifically, as you would any other query. – thatjeffsmith Apr 01 '22 at 13:09
  • at the moment, I only have indexes on the primary keys, as all others allowed have slowed down the query. The data is stored on the 1TB HDD. The system I currently use is database-enterprise: 12.2.0.1 – SItypack Apr 01 '22 at 13:21
  • 2
    well you're on a version of the db engine that's several years old, and only have 1 disk for all of your data isn't going to help things much...partitioning the tables might be helpful. Lots of people have done this already, here's one example https://maksimkrupenin.medium.com/oracle-in-memory-option-performance-testing-using-tpc-h-benchmark-1a2701069316 – thatjeffsmith Apr 01 '22 at 14:00

0 Answers0