Search

Top 60 Oracle Blogs

Recent comments

Oakies Blog Aggregator

Common XQuery mistakes…applied in XML DB

Do you read FAQ…?

Somehow I keep people reminding there is a FAQ URL on the XMLDB forum and even then people refuse to read those good examples… Anyway found two great posts I want to share and remember on this, my, web “notepad”. Besides the treewalker example, I tested the examples of those mentioned in the XQuery post on a Oracle 11.2 database.

As far as I could find the treewalker example is part of DOM V2 and not mandatory to implement but I wonder how I can get around the local() stuff, anyway, I will have to investigate a bit further if its just me being a novice in XQuery or that I am missing out on details/info. The XQuery post only demonstrates to me how powerful this extra query language is in an Oracle database and that it is time for me to learn this properly…

The posts that I was referring to:

…be aware of the use of the (double quote instead single quote), namespaces (indeed apparently always an issue) and using (::) in SQL*Plus… The (::) is needed in SQL*Plus to mark that the “;”  is not seen as direct processing instruction for SQL*Plus, but in this case, is for the XQuery engine.

The headlines follow the ones in the XQuery post…

You always need to do something else

SQL> select xmlquery
  2         ('xquery version "1.0"; (: :)
  3           let $n := 1
  4           return
  5             if ($n = 1) then
  6              "one"
  7          ' returning content)
  8          as "XMLQuery Output"
  9  from  dual;
from  dual
      *
ERROR at line 9:
ORA-19114: XPST0003 - error during parsing the
XQuery expression:
LPX-00801: XQuery syntax error at 'EOF'
5               "one"
-                    ^
 
SQL> ! oerr ORA 19114
 
19114, "XPST0003 - error during parsing the XQuery expression: %s"
 
// *Cause:  An error occurred during the parsing of the XQuery
            expression.
// *Action: Check the detailed error message for the possible causes.
 
SQL> !  oerr LPX 00801
 
00801, 00000, "XQuery syntax error at"
 
// *Cause:  Invalid XQuery query.
// *Action: Correct the query.
 
SQL> select xmlquery
  2         ('xquery version "1.0"; (: :)
  3           let $n := 1
  4           return
  5             if ($n = 1) then
  6              "one"
  7             else
  8              ()
  9          ' returning content)
 10          as "XMLQuery Output"
 11  from  dual;
 
XMLQuery Output
--------------------------------------------------
one

Dynamic evaluation is desired

SQL> select xmlquery
  2         ('xquery version "1.0"; (: :)
  3           let $xml := #009900;">#000000; font-weight: bold;">#000000; font-weight: bold;">>#000000; font-weight: bold;">#000000; font-weight: bold;">>text#009900;">#000000; font-weight: bold;">>#000000; font-weight: bold;">#000000; font-weight: bold;">>text#009900;">#000000; font-weight: bold;">>#000000; font-weight: bold;">>
  4           for $el in ("bar", "baz")
  5           return
  6             $xml/$el/text()
  7          ' returning content)
  8          as "XMLQuery Output"
  9 from  dual;
from  dual
      *
ERROR at line 9:
ORA-19224: XPTY0004 - XQuery static type
mismatch: expected - node()* got - xs:string
 
SQL> ! oerr ORA 19224
 
19224, 00000, "XPTY0004 - XQuery static type mismatch: 
                          expected - %s got - %s "
 
// *Cause:  The expression could not be used because it's static type 
            is not appropriate for the context in which it was used.
// *Action: Fix the expression to be of the required type or add
            appropriate cast functions around the expression.

Curly, curly, curly braces

SQL> select xmlquery
  2         ('xquery version "1.0"; (: :)
  3           let $a := "test"
  4           return
  5             #009900;">#000000; font-weight: bold;">#000000; font-weight: bold;">>{$a}#009900;">#000000; font-weight: bold;">>
  6          ' returning content)
  7          as "XMLQuery Output"
  8  from  dual;
 
XMLQuery Output
--------------------------------------------------
#009900;">#000000; font-weight: bold;">#000000; font-weight: bold;">>test#009900;">#000000; font-weight: bold;">>

The desire to return multiple elements is strong

SQL> select xmlquery
  2         ('xquery version "1.0"; (: :)
  3           let $a := "test"
  4           return
  5             #009900;">#000000; font-weight: bold;">#000000; font-weight: bold;">>{$a}#009900;">#000000; font-weight: bold;">>#000000; font-weight: bold;">#000000; font-weight: bold;">>{$a}#009900;">#000000; font-weight: bold;">>
  6          ' returning content)
  7          as "XMLQuery Output"
  8  from  dual;
from  dual
      *
ERROR at line 8:
ORA-19114: XPST0003 - error during parsing the
XQuery expression:
LPX-00801: XQuery syntax error at '>'
4              #009900;">#000000; font-weight: bold;">#000000; font-weight: bold;">>{$a}#009900;">#000000; font-weight: bold;">>#000000; font-weight: bold;">#000000; font-weight: bold;">>{$a}#009900;">#000000; font-weight: bold;">>
-                                 ^
 
SQL> select xmlquery
  2         ('xquery version "1.0"; (: :)
  3           let $a := "test"
  4           return
  5             (#009900;">#000000; font-weight: bold;">#000000; font-weight: bold;">>{$a}#009900;">#000000; font-weight: bold;">>,#009900;">#000000; font-weight: bold;">#000000; font-weight: bold;">>{$a}#009900;">#000000; font-weight: bold;">>)
  6          ' returning content)
  7          as "XMLQuery Output"
  8  from  dual;
 
XMLQuery Output
--------------------------------------------------
#009900;">#000000; font-weight: bold;">#000000; font-weight: bold;">>test#009900;">#000000; font-weight: bold;">>#000000; font-weight: bold;">#000000; font-weight: bold;">>test#009900;">#000000; font-weight: bold;">>

attributes when you mean string

SQL> select xmlquery
  2         ('xquery version "1.0"; (: :)
  3           let $node := #009900;">#000000; font-weight: bold;"> #000066;">name=#ff0000;">"author" #000066;">content=#ff0000;">"James Fuller"#000000; font-weight: bold;">/>
  4           return
  5             element {$node/@name}
  6                     {$node/@content}
  7          ' returning content)
  8          as "XMLQuery Output"
  9  from  dual;
 
XMLQuery Output
--------------------------------------------------
#009900;">#000000; font-weight: bold;"> #000066;">content=#ff0000;">"James Fuller"#000000; font-weight: bold;">>#000000; font-weight: bold;">>
 
SQL> select xmlquery
  2         ('xquery version "1.0"; (: :)
  3           let $node := #009900;">#000000; font-weight: bold;"> #000066;">name=#ff0000;">"author" #000066;">content=#ff0000;">"James Fuller"#000000; font-weight: bold;">/>
  4           return
  5             element {$node/@name}
  6                     {fn:string($node/@content)}
  7          ' returning content)
  8          as "XMLQuery Output"
  9  from  dual;
 
XMLQuery Output
--------------------------------------------------
#009900;">#000000; font-weight: bold;">#000000; font-weight: bold;">>James Fuller#009900;">#000000; font-weight: bold;">>

Comparing things properly

SQL> select xmlquery
  2         ('xquery version "1.0"; (: :)
  3           let $a := "test"
  4           let $b := "test"
  5           return
  6           fn:compare($a,$b)
  7          ' returning content)
  8          as "XMLQuery Output"
  9  from  dual;
 
XMLQuery Output
--------------------------------------------------
0
 
SQL> select xmlquery
  2         ('xquery version "1.0"; (: :)
  3           let $a := "abc"
  4           let $b := "ab"
  5           return
  6           fn:compare($a,$b)
  7          ' returning content)
  8          as "XMLQuery Output"
  9  from  dual;
 
XMLQuery Output
--------------------------------------------------
1
 
SQL> select xmlquery
  2         ('xquery version "1.0"; (: :)
  3           let $a := "ab"
  4           let $b := "abc"
  5           return
  6           fn:compare($a,$b)
  7          ' returning content)
  8          as "XMLQuery Output"
  9  from  dual;
 
XMLQuery Output
--------------------------------------------------
-1
 
SQL> select xmlquery
  2         ('xquery version "1.0"; (: :)
  3           let $a := (1,2,3)
  4           let $b := (3)
  5           return
  6           $a = $b
  7          ' returning content)
  8          as "XMLQuery Output"
  9  from  dual;
 
XMLQuery Output
--------------------------------------------------
true
 
SQL> select xmlquery
  2         ('xquery version "1.0"; (: :)
  3           let $a := "a"
  4           let $b := "a"
  5           return
  6           if( $a eq $b) then
  7             "string values matched"
  8           else
  9             "string values do not match"
 10          ' returning content)
 11          as "XMLQuery Output"
 12  from  dual;
 
XMLQuery Output
--------------------------------------------------
string values matched

Empty namespaces conundrum

SQL> set lines 100
SQL> set long 10000
SQL> set pages 5000
SQL> set feed on
 
SQL> select xmlquery
  2         ('xquery version "1.0"; (: :)
  3           declare default element namespace "http://www.w3.org/1999/xhtml"; (: :)
  4           declare namespace no-namespace = " "; (: :)
  5           let $xml := #009900;">#000000; font-weight: bold;">#000000; font-weight: bold;">>#000000; font-weight: bold;">#000000; font-weight: bold;">>
  6                       [no-namespace:element]This element has no namespace[/no]
  7                       #009900;">#000000; font-weight: bold;">>#000000; font-weight: bold;">>
  8           return
  9           $xml//*[namespace-uri() eq " "]
 10          ' returning content)
 11          as "XMLQuery Output"
 12  from  dual;
 
XMLQuery Output
----------------------------------------------------------------------------------------------------
[no-namespace:element xmlns:no-namespace=" "]This element has no namespace[/no-namespace:element]
 
1 row selected.

Had to use [ ] brackets here because I still don’t have found a plug-in that doesn’t mess with namespaces on WordPress sites and scrambles the output the moment it encounters namespace notations…

Anyway good to see that Oracle produces the same errors and results.
Next one to read for me ‘An Introduction to XQuery FLWOR expression‘…

8-)

Running Oracle Exadata V2 on Dell Hardware

Well we had to give it a shot.

So we created an Oracle Exadata Storage Server Software CELLBOOT USB flash drive. I’m not kidding, that’s what the Oracle/Sun guys decided to call it. They didn’t even use an acronym in the manual (I guess “ESSSCB USB FD” doesn’t roll off the tongue much better than the whole thing anyway). We used the make_cellboot_usb utility to create the thing off one of our storage servers, which by the way was not that easy to do, since the USB ports are in the back of the 4275’s and they are not easy to get to with all the cabling that’s back there. Anyway, once we had the little bugger created we pulled it out of the back of the rack and booted a Dell Latitude D630 off of it. Here’s a picture:

Notice the thumb drive is all lit up like a Christmas tree.

Here is a close up of the screen (in case your eyes are going bad like mine):

So we tried a couple of different options but eventually got to this screen:

Notice the ERROR line in the middle of the screen. Somebody wisely put a check in the boot procedure to verify the machine type, presumably if it’s not a Sun 4170 it will throw an error. We thought about hacking the system but decided not to at this point as we had real work to do. (maybe later when we’ve got nothing else to do)

Index rebuilds

A couple of years ago I wrote about a poster on the OTN db forum who was puzzled by the fact that when he started rebuilding tables they got bigger.  (Bad luck, sometimes that’s what happens !)

A few days ago a related question appeared: I rebuilt some indexes and my query got slower. (Bad luck, sometimes that’s what happens – again!)

If you rebuild an index it’s physically different and its statistics are different. Plans can change and go slower because the index stats look sufficiently different; plans can stay the same and go slower because the index is physically different. 

I’ve added a couple of comments to the thread – there may still be some further mileage in it.

TPC-Hの結果

In-Memory Non Parallel Queryのテストなんて、あまり見かけない。
でも「Parallel Queryが使えないOracle SEでmini DWHができるか?」という意味でテストをした。
それに今回の試作機は16GBのメモリを積んだんだから、メモリを使い倒すテストはやりたくて仕方がなかった。

当然、次回からはIn-Memory Parallel Queryのテスト結果をBlogするのだけど、今回は、これまで結果を整理することにした。

今までの結果をグラフにしてみた

T1 Parallel Query no_compress + no_partion table
T2 compress + no_partion table
T3 no_compress + partion table
T4 compress + partion table
T5 In-Memory Non PQ compress
T6 no_compress
T7 compress + result_cache
T8 no_compress + result_cache

               [X軸=同時セッション数、Y軸=qph(Query Per Hour)]

  • qphは同時8セッションでIn-Memory Non PQ(Parallel Query)が優れているのが分かる(T5-T8)
  • そして、Compressをしていない(no_compress)がダントツ1位に見える(T8)
  • でも、これをセッション数で割ると:

  • Compress Tableに対する1セッションのParallel Queryが1位になる(T2)
  • でもParallel Queryは同時2セッションが限界で、それ以上は安定した計測値が出ないし、時間も掛かり過ぎた(T1-T3)
  • その場合でもPartitioning+Compressを行えば6セッションまで動いた(T4)
  • 対して、In-Memory Non PQは8セッションまで安定して動いた(T5-T8)
  • そしてIn-Memory non Parallel Queryじゃダメなんだよ!の中で、

    In-Memory nonParallelはTPC-Hの総合点には大きく貢献したが、実際のデータウェアハウスではParallel Queryの方が優れていることが確認できた。
    そして、Parallel Queryは初めから限界ディスク転送量を出してしまうので「同時実行制御」の工夫が必要だということが、今回のテストを通じて理解できた。

    と書いた。

    ディスク転送量はCrystalDiskMarkの限界量522MB/sを常に超える場合もあった(T2)
    でもReadされているデータは殆んどが必要のないもの、Readしてメモリ上でフィルタリングされる。
    1分間のRead量は:

    500MB/s x 60(秒) = 30000MB = 30GB  ....たった1分で実際のデータ量を簡単に超えている。

    Exadata Storage ServerがインテリジェントにWhere条件をフィルタリングして結果を返したり、Join条件をハッシュ値として返すことが重要な機能だということが実感できた。
    以前Oracle Closed Worldの中で、

    でもStorage Server内での「Secondary Oracle」はExadataだけのClosedなものでしょ?
    →そんなのこれからも「どんどん変わる」。既に細かな話になり始めている。

    と書いた。
    「SSDの普及で転送速度が上がればフィルタリングなんてそれほど重要じゃなくなる」というのは認識不足でした。

    でも、
    TPC-Hベンチマークのように同時8セッションがThink Time無しにHeavy SQLを発行し続けるようなことは、現実世界ではあまりないので、2セッションぐらいで限界になるParallel Queryが活躍できるんだね。
    そうそう、T4はCompress+Partitioningで実質Read量を減らしたので、6セッションまで行けた。

    それから、
    In-Memoryはスケーラビリティでは大勝です。
    でも、メモリに収まるサイズだからです。。。

    An investigation into exadata

    This is an investigation into an half rack database machine (the half rack database machine at VX Company). It’s an exadata/database V2, which means SUN hardware and database and cell (storage) software version 11.2.

    I build a table (called ‘CG_VAR’), which consists of:
    - bytes: 50787188736 (47.30 GB)
    - extents: 6194
    - blocks: 6199608

    The table doesn’t have a primary key, nor any other constraints, nor any indexes. (of course this is not a real life situation)

    No exadata optimisation

    At first I disabled the Oracle storage optimisation using the session parameter ‘CELL_OFFLOAD_PROCESSING’:
    alter session set cell_offload_processing=false;

    Then executed: select count(*) from cg_var where sample_id=1;
    The value ’1′ in the table ‘CG_VAR’ accounts for roughly 25%.

    Execution plan:

    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2301354116
    -------------------------------------------------------------------------------------
    | Id  | Operation                  | Name   | Rows  | Bytes | Cost (%CPU)| Time    |
    -------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT           |        |     1 |     3 |  1692K  (1)| 05:38:34 |
    |   1 |  SORT AGGREGATE            |        |     1 |     3 |            |    |
    |*  2 |   TABLE ACCESS STORAGE FULL| CG_VAR |   395M|  1131M|  1692K  (1)| 05:38:34 |
    -------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       2 - filter("SAMPLE_ID"=1)

    Statistics:

    592,STAT opened cursors cumulative                                                     1
    592,STAT user calls                                                                    3
    592,STAT session logical reads                                                   6178215
    592,STAT CPU used when call started                                                15398
    592,STAT CPU used by this session                                                  15398
    592,STAT DB time                                                                   30667
    592,STAT user I/O wait time                                                        15397
    592,STAT non-idle wait time                                                        15424
    592,STAT non-idle wait count                                                       96193
    592,STAT session uga memory                                                      2105504
    592,STAT session uga memory max                                                   982320
    592,STAT session pga memory                                                      1572864
    592,STAT session pga memory max                                                  1572864
    592,STAT enqueue waits                                                                 3
    592,STAT enqueue requests                                                              2
    592,STAT enqueue conversions                                                           3
    592,STAT enqueue releases                                                              2
    592,STAT global enqueue gets sync                                                      5
    592,STAT global enqueue releases                                                       2
    592,STAT physical read total IO requests                                           48448
    592,STAT physical read total multi block requests                                  48446
    592,STAT physical read requests optimized                                              2
    592,STAT physical read total bytes                                           50610774016
    592,STAT cell physical IO interconnect bytes                                 50610774016
    592,STAT ges messages sent                                                             4
    592,STAT consistent gets                                                         6178215
    592,STAT consistent gets from cache                                                  142
    592,STAT consistent gets from cache (fastpath)                                       142
    592,STAT consistent gets direct                                                  6178073
    592,STAT physical reads                                                          6178073
    592,STAT physical reads direct                                                   6178073
    592,STAT physical read IO requests                                                 48448
    592,STAT physical read bytes                                                 50610774016
    592,STAT calls to kcmgcs                                                             142
    592,STAT calls to get snapshot scn: kcmgss                                             1
    592,STAT file io wait time                                                         17477
    592,STAT Number of read IOs issued                                                 48448
    592,STAT no work - consistent read gets                                          6178073
    592,STAT table scans (long tables)                                                     1
    592,STAT table scans (direct read)                                                     1
    592,STAT table scan rows gotten                                               1596587000
    592,STAT table scan blocks gotten                                                6178073
    592,STAT session cursor cache hits                                                     1
    592,STAT session cursor cache count                                                    1
    592,STAT parse count (total)                                                           1
    592,STAT execute count                                                                 1
    592,STAT bytes sent via SQL*Net to client                                            528
    592,STAT bytes received via SQL*Net from client                                       11
    592,STAT SQL*Net roundtrips to/from client                                             2
    592,STAT cell flash cache read hits                                                    2
    592,WAIT enq: KO - fast object checkpoint                                            621
    592,WAIT direct path read                                                      153970472
    592,WAIT SQL*Net message to client                                                     3
    592,WAIT SQL*Net message from client                                          1782253728
    592,WAIT kfk: async disk IO                                                       264299
    592,WAIT events in waitclass Other                                                  2236
    592,TIME parse time elapsed                                                           20
    592,TIME DB CPU                                                                153981591
    592,TIME sql execute elapsed time                                              306726075
    592,TIME DB time                                                               306726219

    The executionplan is different with the operation ‘TABLE ACCESS STORAGE FULL’. This means the database is aware the segment is on cell/exadata storage.

    We see here the SQL took 306,726,095 microseconds (306.726 seconds; time: parse time+sql execute elapsed time) for scanning 47.30 GB (!!), of which roughly half the time (153.970 seconds) was taken by the wait ‘direct path read’. The I/O wait-time is also in the statistic ‘user I/O wait time’: 15397.

    I’ve executed the SQL before; there is no hard parse in the ‘time section’, there’s also no ‘parse count (hard)’ in the statistics section.

    I’ve done 48448 IO requests (‘physical read total IO requests’), of which almost all IO’s (48446) where multiblock requests (‘physical read total multi block requests’). This means the average IO wait time is: 153,970,472/48,448 = 3,178 which is 3ms!

    Using exadata optimisation, without storage indexes

    In order to enable exadata optimisation, I reverted the session parameter ‘CELL_OFFLOAD_PROCESSING’ to ‘TRUE’.

    One of the optimisation techniques of exadata is called ‘storage index’. A storage index is a list kept in the memory of the cellservers which keeps track of minimum and maximum values of fields per chunk of 1 MB (called ‘storage region’). Storage indexes are build automatically during usage of the ‘storage region’. A way to flush the storage index is to restart the cell servers. (Warning! Do not do this in a production environment!)

    Execution plan:

    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2301354116
    -------------------------------------------------------------------------------------
    | Id  | Operation                  | Name   | Rows  | Bytes | Cost (%CPU)| Time    |
    -------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT           |        |     1 |     3 |  1692K  (1)| 05:38:34 |
    |   1 |  SORT AGGREGATE            |        |     1 |     3 |            |    |
    |*  2 |   TABLE ACCESS STORAGE FULL| CG_VAR |   395M|  1131M|  1692K  (1)| 05:38:34 |
    -------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       2 - storage("SAMPLE_ID"=1)
           filter("SAMPLE_ID"=1)

    Statistics:

    592,STAT opened cursors cumulative                                                     1
    592,STAT user calls                                                                    3
    592,STAT session logical reads                                                   6178215
    592,STAT CPU used when call started                                                 3707
    592,STAT CPU used by this session                                                   3707
    592,STAT DB time                                                                    3856
    592,STAT user I/O wait time                                                          156
    592,STAT non-idle wait time                                                          156
    592,STAT non-idle wait count                                                        2437
    592,STAT session uga memory                                                     24054408
    592,STAT session uga memory max                                                 21948904
    592,STAT session pga memory                                                     25755648
    592,STAT session pga memory max                                                 23527424
    592,STAT enqueue waits                                                                 3
    592,STAT enqueue requests                                                              2
    592,STAT enqueue conversions                                                           3
    592,STAT enqueue releases                                                              2
    592,STAT global enqueue gets sync                                                      5
    592,STAT global enqueue releases                                                       2
    592,STAT physical read total IO requests                                           53714
    592,STAT physical read total multi block requests                                  52661
    592,STAT physical read total bytes                                           50610774016
    592,STAT cell physical IO interconnect bytes                                  5575531904
    592,STAT ges messages sent                                                             4
    592,STAT consistent gets                                                         6178215
    592,STAT consistent gets from cache                                                  142
    592,STAT consistent gets from cache (fastpath)                                       142
    592,STAT consistent gets direct                                                  6178073
    592,STAT physical reads                                                          6178073
    592,STAT physical reads direct                                                   6178073
    592,STAT physical read IO requests                                                 53714
    592,STAT physical read bytes                                                 50610774016
    592,STAT calls to kcmgcs                                                             142
    592,STAT calls to get snapshot scn: kcmgss                                             1
    592,STAT cell physical IO bytes eligible for predicate offload               50610774016
    592,STAT cell physical IO interconnect bytes returned by smart scan           5575531904
    592,STAT cell session smart scan efficiency                                            9
    592,STAT table scans (long tables)                                                     1
    592,STAT table scans (direct read)                                                     1
    592,STAT table scan rows gotten                                                387130468
    592,STAT table scan blocks gotten                                                1503621
    592,STAT cell scans                                                                    1
    592,STAT cell blocks processed by cache layer                                    6549118
    592,STAT cell blocks processed by txn layer                                      6549118
    592,STAT cell blocks processed by data layer                                     6183339
    592,STAT cell blocks helped by minscn optimization                               6549118
    592,STAT cell simulated session smart scan efficiency                        50653913088
    592,STAT cell IO uncompressed bytes                                          50653913088
    592,STAT session cursor cache count                                                    1
    592,STAT parse count (total)                                                           1
    592,STAT execute count                                                                 1
    592,STAT bytes sent via SQL*Net to client                                            528
    592,STAT bytes received via SQL*Net from client                                      327
    592,STAT SQL*Net roundtrips to/from client                                             2
    592,WAIT enq: KO - fast object checkpoint                                            492
    592,WAIT cell smart table scan                                                   1560351
    592,WAIT SQL*Net message to client                                                     3
    592,WAIT SQL*Net message from client                                             5605106
    592,WAIT events in waitclass Other                                                  1590
    592,TIME parse time elapsed                                                           46
    592,TIME DB CPU                                                                 37064365
    592,TIME sql execute elapsed time                                               38571803
    592,TIME DB time                                                                38571968

    The execution plan shows the ‘TABLE ACCESS STORAGE FULL’ again, to indicate cell storage, but now it was able to offload, which is visible in the predicate information section with the predicate ‘storage(“SAMPLE_ID”=1)’. Let’s see the differences:

    NR ,TYP Statistic                                                                  1st           2nd          Diff
    592,STAT opened cursors cumulative                                                     1              1              0
    592,STAT user calls                                                                    3              3              0
    592,STAT session logical reads                                                   6178215        6178215              0
    592,STAT CPU used when call started                                                15398           3707         -11691
    592,STAT CPU used by this session                                                  15398           3707         -11691
    592,STAT DB time                                                                   30667           3856         -26811
    592,STAT user I/O wait time                                                        15397            156         -15241
    592,STAT non-idle wait time                                                        15424            156         -15268
    592,STAT non-idle wait count                                                       96193           2437         -93756
    592,STAT session uga memory                                                      2105504       24054408       21948904
    592,STAT session uga memory max                                                   982320       21948904       20966584
    592,STAT session pga memory                                                      1572864       25755648       24182784
    592,STAT session pga memory max                                                  1572864       23527424       21954560
    592,STAT enqueue waits                                                                 3              3              0
    592,STAT enqueue requests                                                              2              2              0
    592,STAT enqueue conversions                                                           3              3              0
    592,STAT enqueue releases                                                              2              2              0
    592,STAT global enqueue gets sync                                                      5              5              0
    592,STAT global enqueue releases                                                       2              2              0
    592,STAT physical read total IO requests                                           48448          53714           5266
    592,STAT physical read total multi block requests                                  48446          52661           4215
    592,STAT physical read requests optimized                                              2              0             -2
    592,STAT physical read total bytes                                           50610774016    50610774016              0
    592,STAT cell physical IO interconnect bytes                                 50610774016     5575531904   -45035242112
    592,STAT ges messages sent                                                             4              4              0
    592,STAT consistent gets                                                         6178215        6178215              0
    592,STAT consistent gets from cache                                                  142            142              0
    592,STAT consistent gets from cache (fastpath)                                       142            142              0
    592,STAT consistent gets direct                                                  6178073        6178073              0
    592,STAT physical reads                                                          6178073        6178073              0
    592,STAT physical reads direct                                                   6178073        6178073              0
    592,STAT physical read IO requests                                                 48448          53714           5266
    592,STAT physical read bytes                                                 50610774016    50610774016              0
    592,STAT calls to kcmgcs                                                             142            142              0
    592,STAT calls to get snapshot scn: kcmgss                                             1              1              0
    592,STAT file io wait time                                                         17477              0         -17477
    592,STAT Number of read IOs issued                                                 48448              0         -48448
    592,STAT cell physical IO bytes eligible for predicate offload                         0    50610774016    50610774016
    592,STAT cell physical IO interconnect bytes returned by smart scan                    0     5575531904     5575531904
    592,STAT cell session smart scan efficiency                                            0              9              9
    592,STAT no work - consistent read gets                                          6178073              0       -6178073
    592,STAT table scans (long tables)                                                     1              1              0
    592,STAT table scans (direct read)                                                     1              1              0
    592,STAT table scan rows gotten                                               1596587000      387130468    -1209456532
    592,STAT table scan blocks gotten                                                6178073        1503621       -4674452
    592,STAT cell scans                                                                    0              1              1
    592,STAT cell blocks processed by cache layer                                          0        6549118        6549118
    592,STAT cell blocks processed by txn layer                                            0        6549118        6549118
    592,STAT cell blocks processed by data layer                                           0        6183339        6183339
    592,STAT cell blocks helped by minscn optimization                                     0        6549118        6549118
    592,STAT cell simulated session smart scan efficiency                                  0    50653913088    50653913088
    592,STAT cell IO uncompressed bytes                                                    0    50653913088    50653913088
    592,STAT session cursor cache hits                                                     1              0             -1
    592,STAT session cursor cache count                                                    1              1              0
    592,STAT parse count (total)                                                           1              1              0
    592,STAT execute count                                                                 1              1              0
    592,STAT bytes sent via SQL*Net to client                                            528            528              0
    592,STAT bytes received via SQL*Net from client                                       11            327            316
    592,STAT SQL*Net roundtrips to/from client                                             2              2              0
    592,STAT cell flash cache read hits                                                    2              0             -2
    592,WAIT enq: KO - fast object checkpoint                                            621            492           -129
    592,WAIT direct path read                                                      153970472              0     -153970472
    592,WAIT cell smart table scan                                                         0        1560351        1560351
    592,WAIT SQL*Net message to client                                                     3              3              0
    592,WAIT SQL*Net message from client                                          1782253728        5605106    -1776648622
    592,WAIT kfk: async disk IO                                                       264299              0        -264299
    592,WAIT events in waitclass Other                                                  2236           1590           -646
    592,TIME parse time elapsed                                                           20             46             26
    592,TIME DB CPU                                                                153981591       37064365     -116917226
    592,TIME sql execute elapsed time                                              306726075       38571803     -268154272
    592,TIME DB time                                                               306726219       38571968     -268154251

    Let start at the time spend: the first run, this was: 306726075+20= 307 seconds, the second run took: 38571803+46= 39 seconds (!!). That is a reduction of 87%. That _is_ spectacular!

    The waits have changed: the ‘direct path read’ is swapped for ‘cell smart table scan’.

    In the (session) statistics also have some things to notice:

    • physical read total bytes: Is the same(!) It’s not strange: the size of the segment to read is known by the database, and must be read entirely from the database’s perspective.
    • cell physical IO bytes eligible for predicate offload: this statistic shows the amount of data which the cell server is able to process on behalf of the database, instead of the database processing and the cell server just delivering blocks. In this case, all bytes are processed on the cellserver (cell physical IO bytes eligible for predicate offload=physical read total bytes)
    • cell physical IO interconnect bytes: Here we see that the amount of data traffic between the database and the cell servers. Because of the offload, the amount is reduced immensely (50,610,774,016 versus 5,575,531,904) which is a reduction of 88.9% (!!)
    • cell physical IO interconnect bytes returned by smart scan: This is the amount of data which is caused by the cell smart scan (the statistic name is quite self explaining). If we compare this statistic with ‘cell physical IO interconnect bytes’, we see all data on the interconnect was caused by the smart scan.
    • cell scans: the number of scans offloaded to cellservers. one in this case.
    Using exadata optimisation, with storage indexes

    Upon the next execution of this statement, the execution plan stays the same, but the cellservers built the storage index.

    Let’s see a comparison of the statistics of the previous run (without a storage index in place) with a second run, which will use the storage index, which is built up during the first run:

    NR ,TYP Statistic                                                                  1st           2nd          Diff
    592,STAT opened cursors cumulative                                                     1              1              0
    592,STAT user calls                                                                    3              3              0
    592,STAT session logical reads                                                   6178215        6178215              0
    592,STAT CPU used when call started                                                 3707           3713              6
    592,STAT CPU used by this session                                                   3707           3713              6
    592,STAT DB time                                                                    3856           3745           -111
    592,STAT user I/O wait time                                                          156             37           -119
    592,STAT non-idle wait time                                                          156             37           -119
    592,STAT non-idle wait count                                                        2437           1825           -612
    592,STAT session uga memory                                                     24054408              0      -24054408
    592,STAT session uga memory max                                                 21948904              0      -21948904
    592,STAT session pga memory                                                     25755648              0      -25755648
    592,STAT session pga memory max                                                 23527424              0      -23527424
    592,STAT enqueue waits                                                                 3              3              0
    592,STAT enqueue requests                                                              2              2              0
    592,STAT enqueue conversions                                                           3              3              0
    592,STAT enqueue releases                                                              2              2              0
    592,STAT global enqueue gets sync                                                      5              5              0
    592,STAT global enqueue releases                                                       2              2              0
    592,STAT physical read total IO requests                                           53714          53765             51
    592,STAT physical read total multi block requests                                  52661          52688             27
    592,STAT physical read requests optimized                                              0          35423          35423
    592,STAT physical read total bytes                                           50610774016    50610774016              0
    592,STAT cell physical IO interconnect bytes                                  5575531904     5570438336       -5093568
    592,STAT ges messages sent                                                             4              4              0
    592,STAT consistent gets                                                         6178215        6178215              0
    592,STAT consistent gets from cache                                                  142            142              0
    592,STAT consistent gets from cache (fastpath)                                       142            142              0
    592,STAT consistent gets direct                                                  6178073        6178073              0
    592,STAT physical reads                                                          6178073        6178073              0
    592,STAT physical reads direct                                                   6178073        6178073              0
    592,STAT physical read IO requests                                                 53714          53765             51
    592,STAT physical read bytes                                                 50610774016    50610774016              0
    592,STAT calls to kcmgcs                                                             142            142              0
    592,STAT calls to get snapshot scn: kcmgss                                             1              1              0
    592,STAT cell physical IO bytes eligible for predicate offload               50610774016    50610774016              0
    592,STAT cell physical IO bytes saved by storage index                                 0    37006434304    37006434304
    592,STAT cell physical IO interconnect bytes returned by smart scan           5575531904     5570438336       -5093568
    592,STAT cell session smart scan efficiency                                            9              0             -9
    592,STAT table scans (long tables)                                                     1              1              0
    592,STAT table scans (direct read)                                                     1              1              0
    592,STAT table scan rows gotten                                                387130468      387130468              0
    592,STAT table scan blocks gotten                                                1503621        1503621              0
    592,STAT cell scans                                                                    1              1              0
    592,STAT cell blocks processed by cache layer                                    6549118        2025039       -4524079
    592,STAT cell blocks processed by txn layer                                      6549118        2025039       -4524079
    592,STAT cell blocks processed by data layer                                     6183339        1666003       -4517336
    592,STAT cell blocks helped by minscn optimization                               6549118        2025039       -4524079
    592,STAT cell simulated session smart scan efficiency                        50653913088    13647896576   -37006016512
    592,STAT cell IO uncompressed bytes                                          50653913088    13647896576   -37006016512
    592,STAT session cursor cache hits                                                     0              1              1
    592,STAT session cursor cache count                                                    1             1            0
    592,STAT parse count (total)                                                           1              1              0
    592,STAT execute count                                                                 1              1              0
    592,STAT bytes sent via SQL*Net to client                                            528            528              0
    592,STAT bytes received via SQL*Net from client                                      327            327              0
    592,STAT SQL*Net roundtrips to/from client                                             2              2              0
    592,WAIT enq: KO - fast object checkpoint                                            492            790            298
    592,WAIT cell smart table scan                                                   1560351         366466       -1193885
    592,WAIT SQL*Net message to client                                                     3              2             -1
    592,WAIT SQL*Net message from client                                             5605106        8163815        2558709
    592,WAIT events in waitclass Other                                                  1590           1723            133
    592,TIME parse time elapsed                                                           46             23            -23
    592,TIME DB CPU                                                                 37064365       37131355          66990
    592,TIME sql execute elapsed time                                               38571803       37456254       -1115549
    592,TIME DB time                                                                38571968       37457846       -1114122
    

    We see here the storage index reduced an enormous amount of I/O on the cells (cell physical IO bytes saved by storage index: 37006434304), but didn’t result in a enormous reduction of execution time (38.5 seconds versus 37.4 seconds).

    This isn’t too unexpected:
    1/without exadata optimisation: total time: 307 s, IO wait (direct path read): 154 s.
    2/with exadata optimisation: total time: 39 s, IO wait (cell smart table scan): 2 s.

    I think you get the point now: after IO waits are brought down to 2 seconds, any further optimisation on IO (which is what the storage indexes are) only will reduce the 2 seconds further. And it did! The optimisation of the storage indexes is again spectacular, only has little impact on overall response time, because it influences a too little part of it (in fact, that is Amdahl’s law):

    3/with exadata optimisation and storage indexes: total time: 37 s, IO wait (cell smart table scan): 0.3 s.

    Conclusion

    This is only an investigation in some very specific things of Exadata. There is much more to explore.

    Using the hardware in the database machine, a single process can read 47.30 GB in 154 seconds. That is a very decent number, and sustainable with multiple processes, alias Oracle Parallel Query. That on it self is something of which many shops can benefit from. But, it gets really interesting when using the exadata optimisations. In this case, the total response time dropped from 307 to 37 seconds, with barely a wait on I/O (0.3 seconds for 47.30 GB!!)

    Tagged: oracle exadata database machine performance

    So what’s the “Michigan OakTable Symposium” all about…

    Bit sad that I don’t have the time to post some of the cool internal tracing stuff that I have done lately and that gave me a far better insight in the inner workings of Oracle XMLDB, but there is a reason for it…

    :-)

    As you have maybe have noticed, there is besides the day-to-day database administration and consulting work, a lot of presenting going on in my “free” time. Sharing is fun, but also needs a lot of preparing. So besides attending and presenting on Oracle Open World and OPP2000 in Brussels, I also sneaky arranged – LOL – some slots in that great Michigan OakTable Symposium line-up, in which I feel myself very humbled…so if you can’t read it maybe you should get hold of me somewhere on one of those conferences and just ask…

    ;-)

    Anyway, need to know why you should attend…? Have a sneak peek…

    MOTS from Joel Schneider on Vimeo.

    SQL Developer and MS SQL Server…

    This afternoon I’ve been cleaning up some data in an SQL Server database. I decided to use SQL*Developer to connect to SQL Server by following this post.

    I made liberal use of the following tip when dealing with TEXT and NTEXT types.

    The joys of dealing with multiple engines…

    Cheers

    Tim…

    SaneSAN2010: Serial to Serial – When One Bottleneck Isn’t Enough

    I was recently looking into a storage-related performance problem at a customer site. The system was an Oracle 10.2.0.4/SLES 9 Linux system, Fibre Channel attached to an EMC DMX storage array. The DMX was replicated to a DR site using SRDF/S. The problem was only really visible during the overnight batch runs, so AWR reports [...]

    Sane SAN 2010 – Introduction

    This year at the UKOUG Conference in Birmingham, acceptance permitting, I will present the successor to my original Sane SAN whitepaper first penned in 2000. The initial paper was spectacularly well received, relatively speaking, mostly because disk storage at that time was very much a black box to DBAs and a great deal of mystique [...]

    Broken(ish) Links…

    A couple of days ago Sten Vesterli tweeted about the URL changes on OTN. The previous base URL of “http://www.oracle.com/technology” has been replaced by “http://www.oracle.com/technetwork“. There are redirects in place, so for many of the top level pages this isn’t a problem, but some of the deeper links result in “page not found” errors or redirect to rather generic pages.

    Fortunately, most of the links from my site are to the Oracle docs, whose URLs haven’t changed, but there are also plenty of OTN links. I’m trying to clean up the problem links, but it’s going to take a little time. If you spot any broken links, or links that don’t look like they point to the intended information, feel free to contact me and I’ll do my best to sort them.

    Cheers

    Tim…