Oracle Database In-Memory FAQ
Via Lindsey in IT, zdroj http://www.bluefinsolutions.com/Blogs/John-Appleby/October-2014/Oracle-Database-In-Memory-FAQ/
Velmi zajímavý blog post od Johna Applebyho (Global Head of SAP HANA Bluefin Solutions) o nové Oracle 12c In-memory option, která je od červencového releasu přímým konkurentem SAP HANA in-memory DB.
Postřehy:
...Database In-Memory option is very much geared towards effective, efficient simple queries. This is demonstrated because when you increase query complexity, performance decreases so that by the time you get to a typical star schema OLAP query, the performance can be worse than before the in-Memory option was enabled.
...The In-Memory cache is not sorted and therefore for aggregations that access a random or large volume of data, response time is good. For queries where data needs to be sorted prior to aggregation, the sort happens in the SGA and performance decreases. It is not possible to add additional indexes with specific sort orders.
Doporučení ohledně velikosti paměti:
...In any case, an in-memory cache of around 20% your database size is practical.
...In addition, temporary RAM is required to persist interim results. For very simple range queries we found we needed a SGA of 5x the in-memory cache and for more complex queries we found we needed the SGA to be 10x the in-memory cache, for single-user operations.
Informace ohledně ceny (nákladů):
This will cost you $25k per CPU at list price. In most cases, Oracle use a 2:1 core:CPU ratio so a typical 4 socket, 15 core system with 60 total cores would require 30 licenses, or $500k. That's in addition to the Oracle Enterprise, Partition, RAC and other licenses you will require like Dev, Test, HA, DR.
Ohledně HW:
...And since you pay per CPU, it makes sense to put the maximum RAM possible into the system.
Ohledně škálovatelnosti:
...Yes, it appears to scale in much the same way as Oracle RAC.
Musí Oracle změnit chování SGA?
...We suspect that the SGA will have to be refactored in later versions of the database, because analytic queries can cause very poor overall system performance.
Postřehy ohledně výkonu a stability:
...For instance, we found that AVERAGE is usually one order of magnitude slower than SUM and COUNT combined. And in one case, COUNT was two orders of magnitude slower than AVERAGE. More investigation is required to understand why this should be.
Ohledně paralelního zpracování:
By default, queries do not execute in parallel, as they do with the regular Oracle RDBMS. It is necessary either to manually enable parallel processing, or to use a database hint to run the query in parallel.
...As a result a busy database may have unpredictable response times and may not share the number of parallel threads with the amount of processes and queries running at that time.
...If a thread isn't available, queries may hang for seconds or minutes before they can start processing. This requires very careful tuning to get the Degree of Parallelism (DOP) correct for the data available.
...One thing we found very interesting was that a query running with 80 parallel threads on a 40-core system ran faster than a query with 40 parallel threads. This can only be because it was taking advantage of Hyperthreading, which is only of benefit when the database is CPU-bound. But since an optimal in-memory database design is IO-bound between the CPU and RAM with current Intel hardware designs, Oracle has some optimization to do.
Ohledně column storu - resp. column cache:
...The column store is only a cache and so any inserts, updates, or deletes are persisted to the row store first, like in the regular Oracle RDBMS. These changes are also then inserted into a Transaction Journal, which is a secondary store. If there is an update or delete, then the relevant column values in the main IMCUs are then invalidated.
...This Transaction Journal store is much slower and larger than the main IMCUs because it is uncompressed, and query performance will degrade as it rises in size. So a periodic merge of the Transaction Journal and IMCUs occurs, to recompress the data.
In theory there is a delay between row and column store data, but in practice the delay seems minimal, even with concurrent inserts and queries.
Limitace Column Storu:
Index Organized Tables, Clustered Tables, LONGS and Out of Line LOBS are not supported. The most serious of these is clustered tables, because applications like SAP ERP use clustered tables in Oracle for tables like BSEG (Accounting Document Segment) and this severely limits the usability of the In-Memory option for SAP ERP.
