{"id":166,"date":"2017-05-03T20:39:58","date_gmt":"2017-05-03T20:39:58","guid":{"rendered":"https:\/\/www.spotonoracle.com\/?p=166"},"modified":"2017-05-13T15:54:22","modified_gmt":"2017-05-13T15:54:22","slug":"guid-or-how-to-get-ur-index-from-disk","status":"publish","type":"post","link":"https:\/\/www.spotonoracle.com\/?p=166","title":{"rendered":"GUID, or how to Get Ur Index from Disk"},"content":{"rendered":"

Who doesn’t want to write scalable applications these days? Then maybe you should think twice about using GUIDs as primary keys. It’s mostly not about storage overhead, or the fact that surrogate keys are not always the best solution. It is about performance!<\/p>\n

I’m going to talk about Oracle here in particular but most of the concepts are the same\/or similar in other database systems.
\nUsually, you’re using B-tree indexes and unique constraints to police primary keys. And if you read the excellent book SQL Performance Explained<\/a> by Markus Winand you might already know what pain you’ll be facing by using GUIDs. As long as your data volume is tiny and can fully live in the buffer cache you’re probably fine. To illustrate what happens once your dataset outgrows the buffer cache I setup a small test kit.<\/p>\n

The data model is simple. I have two tables, TESTS and RESULTS. Each Test has zero or more Results. To store GUIDs I will use data type RAW(16). The rest of the columns will hold random data to fill the space.<\/p>\n

\r\ncreate table tests (\r\n    test_id raw(16) not null\r\n  , vc2_long varchar2(4000 byte)\r\n) tablespace myapp_data;\r\n\r\ncreate unique index tests_idx_01 on tests (test_id) tablespace myapp_index;\r\nalter table tests add constraint tests_pk primary key (test_id) using index tests_idx_01;\r\n\r\n\r\ncreate table results (\r\n    result_id raw(16) not null\r\n  , test_id raw(16) not null\r\n  , dt date\r\n  , vc2_short varchar2(200 byte)\r\n) tablespace myapp_data;\r\n\r\ncreate unique index results_idx_01 on results(result_id) tablespace myapp_index;\r\nalter table results add constraint results_pk primary key (result_id) using index results_idx_01;\r\ncreate index results_idx_02 on results(test_id) tablespace myapp_index;\r\nalter table results add constraint results_fk_01 foreign key (test_id) references tests(test_id) on delete cascade;\r\n<\/pre>\n
For ease of grep’ing SQL traces I separated tables and indexes to different Tablespaces. MYAPP_INDEX has one datafile with file# 20.<\/p>\n
I wanted to use PL\/SQL to keep the test case short and concise. Unfortunately, Oracle’s SYS_GUID function produces “sequential” GUIDs in my setup (Oracle 12.2 CDB on Oracle Linux 7.2). To have random GUIDs in PL\/SQL I resorted to Java stored procedures (the implementation is not relevant to the case at hand):<\/p>\n
\r\ncreate or replace and compile java source named "RandomGUID"\r\nas\r\npublic class RandomGUID {\r\n  public static String create() {\r\n    return java.util.UUID.randomUUID().toString().replaceAll("-", "").toUpperCase();\r\n  }\r\n}\r\n\/\r\n\r\ncreate or replace function random_java_guid\r\nreturn varchar2\r\nis\r\nlanguage java name 'RandomGUID.create() return java.lang.String';\r\n\/\r\n\r\ncreate or replace function random_guid\r\nreturn raw\r\nis\r\nbegin\r\n  return hextoraw(random_java_guid());\r\nend random_guid;\r\n\/\r\n<\/pre>\n
Works like a charm \ud83d\ude42<\/p>\n
\r\nselect random_guid() guids from dual connect by level <= 5;\r\n\r\nGUIDS\r\n--------------------------------\r\n54746B0750374133BCF9FA85A6F2F532\r\nC92168647BEC4A93982F19498238757E\r\n3E4B858F41764126B177FCCB30CC73C5\r\n4B7CD39D222D4E339482CD25F9AD6EA2\r\nB8C5367F6B944EEA9BD71611CCB54E72\r\n<\/pre>\n
That’s it for the schema setup. To load data I use below code snippet (test1.sql) which in a loop inserts 1000 Tests. After each test it inserts 100 Results per Test in the inner loop and commits. The “RRT” package is used to collect session statistics (gv$session) and tracking wall clock time.<\/p>\n
\r\nset verify off feedback off\r\nset serveroutput on size unlimited\r\n\r\n<<myapp>>\r\ndeclare\r\n  test_id tests.test_id%type;\r\nbegin\r\n  rrt.stat_cfg_default;\r\n  rrt.stat_start('&1');\r\n  rrt.timer_start();\r\n\r\n  for tid in 1 .. 1000 loop\r\n    insert into tests t (test_id, vc2_long) values (random_guid(), dbms_random.string('A', trunc(dbms_random.value(200, 4000))))\r\n    returning t.test_id into myapp.test_id;\r\n    \r\n    for rid in 1 .. 100 loop\r\n      insert into results r (result_id, test_id, dt, vc2_short) values (random_guid(), myapp.test_id, sysdate, dbms_random.string('L', trunc(dbms_random.value(10, 200))));\r\n    end loop;\r\n    \r\n    commit;\r\n  end loop;\r\n\r\n  dbms_output.put_line('Elapsed:' || to_char(rrt.timer_elapsed));\r\n  rrt.stat_stop;\r\nend;\r\n\/\r\n<\/pre>\n
To run the script concurrently in multiple sessions I use following shell script. It just spawns N<\/i> SQL*Plus processes and runs the SQL script.<\/p>\n
\r\n#!\/usr\/bin\/bash\r\n\r\nSCRIPT=$1\r\nDEGREE=$2\r\nEZCONN=$3\r\n\r\ndeclare -i runid=$$\r\ndeclare -i cnt=1\r\n\r\necho "Coordinator process pid ${runid}"\r\nuntil [ $cnt -gt ${DEGREE} ]\r\ndo\r\n  sqlplus -S -L ${EZCONN} @${SCRIPT} ${SCRIPT}.${runid}.${cnt}.log > ${SCRIPT}.${runid}.${cnt}.log &\r\n  echo "Started sub-process ${cnt} with pid $!"\r\n  cnt=$(( cnt + 1 ))\r\ndone\r\n<\/pre>\n
I ran the test case multiple times letting the database grow bigger with every run. I kept running until the performance got unbearable. On my VirtualBox VM setup it took 9 runs with 10 concurrent processes for the performance to go way down south. I deliberately configured a small SGA of 800 MB which resulted in about 500 MB buffer cache to hit the issue soon. Obviously, your milage may vary as the larger the buffer cache the longer you can sustain.
\nLet’s look at resource usage profile of one of the sessions from run 9. I processed the raw extended SQL trace file using Chris Antognini’s TVD$XTAT<\/a>.<\/p>\n
\"\"<\/a><\/p>\n
Wow, this single session did 16,158<\/strong> random physical reads (db file sequential read). Let’s see which statements contributed to those reads:<\/p>\n
\"\"<\/a><\/p>\n
Almost all of the random reads (16,078<\/strong>) came from the INSERT statement with ID 2 which is:<\/p>\n
\"\"<\/a><\/p>\n
All this physical read I\/O to INSERT records, which is all this test case does. And as you can see above the I\/O is done on file 20 from the MYAPP_INDEX Tablespace. What’s the distribution between objects?<\/p>\n
\r\n$ grep 'db file.*file#=20' *ora_5592.trc | cut -d ' ' -f12 | sort | uniq -c\r\n     42 obj#=61805\r\n  14538 obj#=61807\r\n   1498 obj#=61808\r\n<\/pre>\n
\r\nselect object_id, object_name from cdb_objects where con_id = 4 and owner = 'MYAPP' and object_id in (61805, 61807, 61808) order by object_id;\r\n\r\n OBJECT_ID OBJECT_NAME\r\n---------- ---------------\r\n     61805 TESTS_IDX_01\r\n     61807 RESULTS_IDX_01\r\n     61808 RESULTS_IDX_02\r\n<\/pre>\n
Considering the data pattern it makes sense that RESULTS_IDX_01 is suffering the most. It is the unique index that gets the most index entries added. Index RESULTS_IDX_02 benefits from the repeating foreign key values per outer-loop that all go into the same leaf blocks which Oracle most likely caches.<\/p>\n
\r\nselect table_name, index_name, uniqueness, blevel, leaf_blocks, distinct_keys, avg_leaf_blocks_per_key, avg_data_blocks_per_key, clustering_factor, num_rows from dba_indexes where index_name in ('TESTS_IDX_01', 'RESULTS_IDX_01', 'RESULTS_IDX_02') order by table_name desc, index_name;\r\n\r\nTABLE_NAME  INDEX_NAME      UNIQUENESS  BLEVEL  LEAF_BLOCKS  DISTINCT_KEYS  AVG_LEAF_BLOCKS_PER_KEY  AVG_DATA_BLOCKS_PER_KEY  CLUSTERING_FACTOR  NUM_ROWS\r\n----------- --------------- ----------- ------- ------------ -------------- ------------------------ ------------------------ ------------------ --------\r\nTESTS       TESTS_IDX_01    UNIQUE      1       467          90000          1                        1                        89999              90000\r\nRESULTS     RESULTS_IDX_01  UNIQUE      2       44237        8941107        1                        1                        8941107            8941107\r\nRESULTS     RESULTS_IDX_02  NONUNIQUE   2       50757        92216          1                        4                        424304             9137794\r\n<\/pre>\n
Up to test run 8 Oracle could keep the most relevant parts of the indexes in the buffer cache. After that Oracle had to constantly “swap” index leaf blocks in and out of the buffer cache.<\/p>\n
\r\nselect segment_name, tablespace_name, blocks, extents, bytes\/1024\/1024 mb from dba_segments where (segment_name like 'TESTS%' or segment_name like 'RESULTS%') order by segment_name desc;\r\n\r\nSEGMENT_NAME    TABLESPACE_NAME  BLOCKS  EXTENTS  MB\r\n--------------- ---------------- ------- -------- ----\r\nTESTS_IDX_01    MYAPP_INDEX      512     4        4\r\nTESTS           MYAPP_DATA       31360   245      245\r\nRESULTS_IDX_02  MYAPP_INDEX      50560   395      395\r\nRESULTS_IDX_01  MYAPP_INDEX      44928   351      351\r\nRESULTS         MYAPP_DATA       191744  1498     1498\r\n<\/pre>\n
I also graphed the session statistics aggregated (average of the 10 concurrent sessions) by test runs. It clearly shows a correlation between physical reads and elapsed time. Who’d a thunk it? \ud83d\ude42<\/p>\n
\"\"<\/a><\/p>\n
Due to the random character of GUIDs every new index entry potentially goes into any of the index leaf blocks. Thus, performance is good as long as you can cache the entire index, meaning every index supporting primary and foreign keys in you entire application. That’s probably too much to ask.
\nNow, you might know about sequential GUIDs that Oracle and SQL Server implement and start using them. This might alleviate the problem somewhat, but could result in the next performance issue: right hand side index leaf block contention!<\/p>\n
As this post is getting long already I will talk about a solution that scales in my next installment.<\/p>\n","protected":false},"excerpt":{"rendered":"
Who doesn’t want to write scalable applications these days? Then maybe you should think twice about using GUIDs as primary keys. It’s mostly not about storage overhead, or the fact that surrogate keys are not always the best solution. It is about performance! I’m going to talk about Oracle here in particular but most of […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2,3,13],"tags":[],"class_list":["post-166","post","type-post","status-publish","format-standard","hentry","category-general","category-internals","category-things-that-happen"],"_links":{"self":[{"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/posts\/166","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=166"}],"version-history":[{"count":23,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/posts\/166\/revisions"}],"predecessor-version":[{"id":217,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/posts\/166\/revisions\/217"}],"wp:attachment":[{"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=166"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=166"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=166"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}