0

We have a single flat table consisting of ~50 million records of data with ~100 columns on Oracle 11g DBMS. A single table was made primarily for performance reasons.

There are multiple client-applications which execute different select SQL-queries against this table, moreover they can listen for updates from this table asynchronously.

Issues:

The main problem - some of sql-queries work very slowly - up to 8 minutes, to be exact. The thing is that SQL-queries can be arbitrary, it's up to a client. Oracle-hints is not a solution. Furthermore, in some cases Oracle optimizator makes wrong estimations or db-indices degrade performance. Relational db sucks here.

Requirements:

  1. Queries must be based on standard SQL.
  2. Significantly improve the speed of almost any Select-query execution from this table.
  3. The solution must be scalable in terms of number of clients and data.
  4. The solution should be simple and cost-effective.

The question:

What solutions/architecture do you recommend to achieve the above requirements? We may consider different non-relational databases or in-memory caches, develop our own solution, whatever.

Ivan Voroshilin
  • 5,233
  • 3
  • 32
  • 61

1 Answers1

0

If you are using single table, it should be for reports only or else you are doing it wrong. Reports data should not be updated often, not every hour. If oracle hint fails, you can change it manually, happens time to time, but it's mostly because update/insert. And know your queries, don't blindly create indexes for every column (you do use indexes right), check where these slow queries spends time then you will know where to optimize.

Relational db doesn't sucks, if you know how to use them.

Dainius
  • 1,765
  • 1
  • 17
  • 36
  • No, it's not for reporting. What we really need is to persist incoming new data and send real-time updates to downstream clients. Changing a hint is not a solution here as queries are dynamic. Hints work best for concrete sql-queries. – Ivan Voroshilin Jul 19 '13 at 08:08
  • yes, hint would help when concrete execution plan went wrong, because oracle predicts wrong (mainly because of often updates). You can select few long queries and see where they waits - disk, locks etc. But having single table for exchanging huge amount of data between clients sounds a bit wrong. – Dainius Jul 19 '13 at 08:15
  • Thanks Dainius for your answers. Clients don't exchange data between themselves. They just compose sql-query and subscribe for updates incoming to this table. – Ivan Voroshilin Jul 19 '13 at 08:20
  • I want to point out as well that we don't use indices for this table at all due to slow updates when reading data. – Ivan Voroshilin Jul 19 '13 at 08:26
  • 1
    By exchanging I didn't mean that one send data to another trough database. Someone put data in database, another reads. But when you have many writes to (especially update) to single table read process will spend a lot of time in waiting, when it can read data. What I would probably try in this scenario is to have service that keeps data in memory (incoming change request invalidates data in memory) and to DB data are mostly only written (probably in separate thread) and read data only when need to start new read node (write process need to deliver data to every read node). – Dainius Jul 19 '13 at 08:26
  • The last answer is the way I would probably do it. Thanks. Any popular solutions for in-memory cache? – Ivan Voroshilin Jul 19 '13 at 08:37
  • Still outstanding question: Slow execution time of dynamic SQL-queries during the start and query-exection for a a new client. – Ivan Voroshilin Jul 19 '13 at 09:33