I started to play with in-memory to see what performance improvements I can get. I set up four tables, zip, customers, products and sales and executed a simple queries against them.
Sales has 3M rows, and customers has 110K rows, zip 4271 rows and products 6 rows.
|
SELECT p.name, sum(s.price),count(*) |
Run 1) using a default install of Oracle 12.1.0.2. Execution time 17.21 seconds
|
So we turn on In-Memory by setting inmemory_size to a value > 100M. (100M is the smallest value you can use) We then add the sales and customers table to the In-Memory column store using the alter table <tablename> inmemory statement. Once I made the change I did a stop / start of the instance and executed the query again. Oh, I did one more thing to load the column store, select * from sales; and select * from customers; If you want to load the column store when the database instance starts up then use the alter statement alter table <tablename> inmemory priority critical; (there are five levels of priority critical, high, medium, low and none. (none is the default)
Run 2) Execution time 10.73 seconds. Yea, that’s all I did and it’s that simple. Now imagine the performance improvement if I actually tried!
1 SQL> alter system set INMEMORY_SIZE=500M scope=spfile; 2 3 System altered. 4 5 SQL> alter table sales inmemory; 6 7 Table altered. 8 9 SQL> alter table customers inmemory; 10 11 Table altered. 12 13 SQL> 14
1 SQL> sho parameter inmemory 2 3 NAME TYPE VALUE 4 ------------------------------------ ----------- ------------------------------ 5 inmemory_clause_default string 6 inmemory_force string DEFAULT 7 inmemory_max_populate_servers integer 1 8 inmemory_query string ENABLE 9 inmemory_size big integer 500M 10 inmemory_trickle_repopulate_servers_ integer 1 11 percent 12 optimizer_inmemory_aware boolean TRUE 13 SQL> 14 15 16 SQL> set timing on 17 SQL> SELECT p.name, sum(s.price),count(*) 18 FROM customers c, 19 products p, 20 sales s, 21 zip z 22 WHERE c.ID = s.cust_id 23 AND p.ID = s.product_id 24 AND c.zip = z.zip 25 GROUP BY p.NAME 26 / 27 2 3 4 5 6 7 8 9 10 28 29 Elapsed: 00:00:10.73 30 31 Execution Plan 32 ---------------------------------------------------------- 33 Plan hash value: 1408061610 34 35 ------------------------------------------------------------------------------------------- 36 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 37 ------------------------------------------------------------------------------------------- 38 | 0 | SELECT STATEMENT | | 1 | 47 | 215 (17)| 00:00:01 | 39 | 1 | HASH GROUP BY | | 1 | 47 | 215 (17)| 00:00:01 | 40 |* 2 | HASH JOIN | | 1 | 47 | 214 (17)| 00:00:01 | 41 |* 3 | HASH JOIN | | 1 | 34 | 76 (2)| 00:00:01 | 42 | 4 | MERGE JOIN CARTESIAN | | 25626 | 575K| 62 (0)| 00:00:01 | 43 | 5 | TABLE ACCESS FULL | PRODUCTS | 6 | 108 | 3 (0)| 00:00:01 | 44 | 6 | BUFFER SORT | | 4271 | 21355 | 59 (0)| 00:00:01 | 45 | 7 | TABLE ACCESS FULL | ZIP | 4271 | 21355 | 10 (0)| 00:00:01 | 46 | 8 | TABLE ACCESS INMEMORY FULL| CUSTOMERS | 110K| 1181K| 14 (8)| 00:00:01 | 47 | 9 | TABLE ACCESS INMEMORY FULL | SALES | 3142K| 38M| 130 (20)| 00:00:01 | 48 ------------------------------------------------------------------------------------------- 49 50 Predicate Information (identified by operation id): 51 --------------------------------------------------- 52 53 2 - access("C"."ID"="S"."CUST_ID" AND "P"."ID"="S"."PRODUCT_ID") 54 3 - access("C"."ZIP"="Z"."ZIP") 55 56 Note 57 ----- 58 - dynamic statistics used: dynamic sampling (level=2) 59 - 1 Sql Plan Directive used for this statement 60 61 62 Statistics 63 ---------------------------------------------------------- 64 3894 recursive calls 65 0 db block gets 66 9231 consistent gets 67 14236 physical reads 68 0 redo size 69 882 bytes sent via SQL*Net to client 70 551 bytes received via SQL*Net from client 71 2 SQL*Net roundtrips to/from client 72 526 sorts (memory) 73 0 sorts (disk) 74 5 rows processed 75 76 SQL>
There will be more to come, but if you want to read more about Oracle in-memory there is an excellent white paper at http://www.oracle.com/technetwork/database/in-memory/overview/twp-oracle-database-in-memory-2245633.html
Okay, I looked at the memory usage and the size of my tables I loaded into the column store and made a change. The customers table is over 1G, therefore I changed the inmemory_size to 2G. Let’s see what that does for my performance.
1 SQL> alter system set inmemory_size=2G scope=spfile; 2 3 System altered. 4 5 SQL> shutdown immediate 6 Database closed. 7 Database dismounted. 8 ORACLE instance shut down. 9 SQL> startup 10 ORACLE instance started. 11 12 Total System Global Area 2449473536 bytes 13 Fixed Size 2948320 bytes 14 Variable Size 243270432 bytes 15 Database Buffers 50331648 bytes 16 Redo Buffers 5439488 bytes 17 In-Memory Area 2147483648 bytes 18 Database mounted. 19 Database opened. 20 SQL> 21
Okay, 6.02 seconds. Nice try but I think can do better. Physical reads went from 16,351 without in-memory to 14,236 with 500M used for in-memory and down to 1,084 when I sized in-memory appropriately.
1 SQL> @qry1 2 3 NAME SUM(S.PRICE) COUNT(*) 4 ----------------------------------- ------------ ---------- 5 Posidon Odin 527301055 627150 6 Scuba Pro G500 Regulator 453549388 628716 7 Stop Stink 1874278.96 627888 8 Scuba Fins 75210608.1 629888 9 Scuba Pro Mask 46894525.5 628395 10 11 Elapsed: 00:00:06.02 12 13 Execution Plan 14 ---------------------------------------------------------- 15 Plan hash value: 1408061610 16 17 ------------------------------------------------------------------------------------------- 18 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 19 ------------------------------------------------------------------------------------------- 20 | 0 | SELECT STATEMENT | | 1 | 47 | 215 (17)| 00:00:01 | 21 | 1 | HASH GROUP BY | | 1 | 47 | 215 (17)| 00:00:01 | 22 |* 2 | HASH JOIN | | 1 | 47 | 214 (17)| 00:00:01 | 23 |* 3 | HASH JOIN | | 1 | 34 | 76 (2)| 00:00:01 | 24 | 4 | MERGE JOIN CARTESIAN | | 25626 | 575K| 62 (0)| 00:00:01 | 25 | 5 | TABLE ACCESS FULL | PRODUCTS | 6 | 108 | 3 (0)| 00:00:01 | 26 | 6 | BUFFER SORT | | 4271 | 21355 | 59 (0)| 00:00:01 | 27 | 7 | TABLE ACCESS FULL | ZIP | 4271 | 21355 | 10 (0)| 00:00:01 | 28 | 8 | TABLE ACCESS INMEMORY FULL| CUSTOMERS | 110K| 1181K| 14 (8)| 00:00:01 | 29 | 9 | TABLE ACCESS INMEMORY FULL | SALES | 3142K| 38M| 130 (20)| 00:00:01 | 30 ------------------------------------------------------------------------------------------- 31 32 Predicate Information (identified by operation id): 33 --------------------------------------------------- 34 35 2 - access("C"."ID"="S"."CUST_ID" AND "P"."ID"="S"."PRODUCT_ID") 36 3 - access("C"."ZIP"="Z"."ZIP") 37 38 Note 39 ----- 40 - dynamic statistics used: dynamic sampling (level=2) 41 - 1 Sql Plan Directive used for this statement 42 43 44 Statistics 45 ---------------------------------------------------------- 46 3793 recursive calls 47 0 db block gets 48 9239 consistent gets 49 1084 physical reads 50 0 redo size 51 882 bytes sent via SQL*Net to client 52 551 bytes received via SQL*Net from client 53 2 SQL*Net roundtrips to/from client 54 491 sorts (memory) 55 0 sorts (disk) 56 5 rows processed 57 58 SQL> 59
Okay, I decided to do a little better. I added three indexes to support the foreign key relationships and see what that does. 3.79 seconds, 44 physical reads. My work here is done.
1 SQL> create index sales_cust_id on sales(cust_id); 2 3 Index created. 4 5 Elapsed: 00:00:17.77 6 SQL> create index product_id on sales(product_id); 7 8 Index created. 9 10 1* create index customers_zip on customers(zip) 11 SQL> / 12 13 Index created. 14 15 Elapsed: 00:00:00.58 16 17 Elapsed: 00:00:24.23 18 SQL> exec dbms_stats.gather_schema_stats(user); 19 20 PL/SQL procedure successfully completed. 21 22 Elapsed: 00:00:22.57 23 24 SQL> @qry1 25 26 NAME SUM(S.PRICE) COUNT(*) 27 ----------------------------------- ------------ ---------- 28 Posidon Odin 527301055 627150 29 Scuba Pro G500 Regulator 453549388 628716 30 Scuba Fins 75210608.1 629888 31 Scuba Pro Mask 46894525.5 628395 32 Stop Stink 1874278.96 627888 33 34 Elapsed: 00:00:03.79 35 36 Execution Plan 37 ---------------------------------------------------------- 38 Plan hash value: 463445694 39 40 ---------------------------------------------------------------------------------------------------------- 41 | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | 42 ---------------------------------------------------------------------------------------------------------- 43 | 0 | SELECT STATEMENT | | 5 | 235 | | 4081 (4)| 00:00:01 | 44 | 1 | HASH GROUP BY | | 5 | 235 | | 4081 (4)| 00:00:01 | 45 | 2 | MERGE JOIN | | 5 | 235 | | 4080 (4)| 00:00:01 | 46 | 3 | TABLE ACCESS BY INDEX ROWID | PRODUCTS | 6 | 108 | | 2 (0)| 00:00:01 | 47 | 4 | INDEX FULL SCAN | SYS_C0011426 | 6 | | | 1 (0)| 00:00:01 | 48 |* 5 | SORT JOIN | | 5 | 145 | | 4078 (4)| 00:00:01 | 49 | 6 | VIEW | VW_GBC_13 | 5 | 145 | | 4077 (4)| 00:00:01 | 50 | 7 | HASH GROUP BY | | 5 | 140 | | 4077 (4)| 00:00:01 | 51 |* 8 | HASH JOIN | | 3102K| 82M| | 4000 (2)| 00:00:01 | 52 | 9 | INDEX FAST FULL SCAN | ZIP_ZIP | 4271 | 21355 | | 4 (0)| 00:00:01 | 53 |* 10 | HASH JOIN | | 3102K| 68M| 2368K| 3988 (2)| 00:00:01 | 54 | 11 | TABLE ACCESS INMEMORY FULL| CUSTOMERS | 110K| 1074K| | 14 (8)| 00:00:01 | 55 | 12 | TABLE ACCESS INMEMORY FULL| SALES | 3142K| 38M| | 130 (20)| 00:00:01 | 56 ---------------------------------------------------------------------------------------------------------- 57 58 Predicate Information (identified by operation id): 59 --------------------------------------------------- 60 61 5 - access("P"."ID"="ITEM_1") 62 filter("P"."ID"="ITEM_1") 63 8 - access("C"."ZIP"="Z"."ZIP") 64 10 - access("C"."ID"="S"."CUST_ID") 65 66 Note 67 ----- 68 - dynamic statistics used: dynamic sampling (level=2) 69 - 1 Sql Plan Directive used for this statement 70 71 72 Statistics 73 ---------------------------------------------------------- 74 139 recursive calls 75 0 db block gets 76 189 consistent gets 77 44 physical reads 78 0 redo size 79 882 bytes sent via SQL*Net to client 80 551 bytes received via SQL*Net from client 81 2 SQL*Net roundtrips to/from client 82 13 sorts (memory) 83 0 sorts (disk) 84 5 rows processed 85 86 SQL> 87