Friday Mar 20, 2015

Linux 'perf' script for Database In-Memory CPU cache miss

Last time I tested in virtual environment.

Database In-Memory vs Buffer Cache CPU cache miss comparison (INOUE Katsumi @ Tokyo)

SQL> select /*+inmemory full(t) */ count(1) from lineorder t;

I re-tested on bare-metal so it's far easier to write script. I studied Linux 'perf' and learned that there's a tip to use 'sleep' command.

Tutorial - Perf Wiki

Even though we are attaching to a process, we can still pass the name of a command. It is used to time the measurement.

And this is my very 'sleepy' script. 4 'sleep' in 15 lines script!
This script assumes 1 SQL runs within a few seconds.
And I apologize for anti-injection workarounds.

#!/bin/b-a-s-h
sudo echo "Caching password for sudo."
(
sleep 5
ora_fg=$(pgrep -P $(pgrep -P $$ sqlplus) oracle_)
 sudo perf stat $(perf list cache|awk '{print "-e " $1}') -p $ora_fg sleep 20
) &
(
stmt="select /*+ no_inmemory full(t) no_parallel(t)*/ count(c256) from t10 t;"
 e-c-h-o $stmt
 e-c-h-o '!sleep 5'
# Do real work 5 times.
for ((i=0;i<5;i++)); do echo $stmt; done
) | sqlplus scott/tiger
sleep 10

This is the diff of output. Left hand side is "In-Memory" and right side is "No in-memory".
I only ran the script once. I hope I can analyze this result a bit and write another entry in this blog.

<  Performance counter stats for process id '64924':
---
>  Performance counter stats for process id '69007':
3,10c3,10
<        548,073,868 L1-dcache-loads                                              [18.35%]
<          5,534,116 L1-dcache-load-misses     #    1.01% of all L1-dcache hits   [18.47%]
<        449,883,059 L1-dcache-stores                                             [18.61%]
<          1,696,349 L1-dcache-store-misses                                       [18.75%]
<          9,433,401 L1-dcache-prefetches                                         [18.59%]
<          1,254,053 L1-dcache-prefetch-misses                                    [18.56%]
<        415,250,454 L1-icache-loads                                              [18.53%]
<          4,300,010 L1-icache-load-misses     #    1.04% of all L1-icache hits   [18.50%]
---
>      8,715,337,112 L1-dcache-loads                                              [18.56%]
>        131,293,883 L1-dcache-load-misses     #    1.51% of all L1-dcache hits   [18.67%]
>      3,130,758,759 L1-dcache-stores                                             [18.16%]
>         11,259,260 L1-dcache-store-misses                                       [18.28%]
>         99,338,807 L1-dcache-prefetches                                         [18.14%]
>         83,794,833 L1-dcache-prefetch-misses                                    [18.11%]
>      8,853,466,123 L1-icache-loads                                              [18.26%]
>        235,017,451 L1-icache-load-misses     #    2.65% of all L1-icache hits   [17.96%]
13,22c13,22
<          1,653,747 LLC-loads                                                    [18.60%]
<          1,092,355 LLC-load-misses           #   66.05% of all LL-cache hits    [ 4.78%]
<          1,266,749 LLC-stores                                                   [ 4.61%]
<             20,119 LLC-store-misses                                             [ 4.61%]
---
>         88,399,685 LLC-loads                                                    [18.20%]
>         35,320,864 LLC-load-misses           #   39.96% of all LL-cache hits    [ 4.34%]
>         27,220,667 LLC-stores                                                   [ 4.46%]
>          2,857,378 LLC-store-misses                                             [ 4.43%]
[...]
30c30
<       20.000581301 seconds time elapsed
---
>       20.000570683 seconds time elapsed

Thursday Mar 19, 2015

DBpediaをOracle RDFストア/トリプル・ストアで

Oracle Database には色々な側面があります。
JSONストア、XML DBMS,ドキュメント・ストア....

Spatial & Graph オプションが RDFストア/トリプル・ストア 機能と(ほぼ)グラフDBMS/Graphデータベース機能を持たせます。
#脱線しますが (何ちゃら)"ストア" と(xx)"DBMS" はどちらも使える場合が多いようです。

ここにSpatial&GraphのデモVirtualBoxイメージがあり、

RDF Semantic Graph Licensed Software

This virtual machine includes all components of Oracle Spatial and Graph RDF Semantic Graph installed and configured with example code and data for ease of evaluation.

Oracle DB11g 11.2.0.3 に格納されたDBpedia データセットに対してSPARQLを投げることができます。

DBペディア - Wikipedia

DBペディア英語:DBpedia)は、ウィキペディアからの構造化コンテントの抽出を目的とするプロジェクトである

http://wiki.dbpedia.org/Applications にある
SNORQL Query Builder  をリンクした際の以下のデフォルトのSPARQLも

少し書き換えれば実行できます。お試しください。

PREFIX dc:    <http://purl.org/dc/elements/1.1/>
PREFIX rdf:   <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX rdfs:  <http://www.w3.org/2000/01/rdf-schema#>
PREFIX xsd:   <http://www.w3.org/2001/XMLSchema#>
PREFIX owl:   <http://www.w3.org/2002/07/owl#>
PREFIX fn:    <http://www.w3.org/2005/xpath-functions#>
PREFIX ouext: <http://oracle.com/semtech/jena-adaptor/ext/user-def-function#>
PREFIX oext:  <http://oracle.com/semtech/jena-adaptor/ext/function#>
PREFIX ORACLE_SEM_FS_NS: <http://oracle.com/semtech#timeout=100,qid=123>

PREFIX dbo: <http://dbpedia.org/ontology/>
PREFIX foaf: <http://xmlns.com/foaf/0.1/>

SELECT ?name ?birth ?death ?person WHERE {
     ?person dbo:birthPlace <http://dbpedia.org/resource/Berlin> .
     ?person dbo:birthDate ?birth .
     ?person foaf:name ?name .
     ?person dbo:deathDate ?death
     FILTER (?birth < "1900-01-01"^^xsd:date) .
} LIMIT 5

 

About

The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

Search

Archives
« March 2015
SunMonTueWedThuFriSat
1
2
3
5
6
7
8
9
14
15
21
22
24
25
26
28
29
30
31
    
       
Today