4 Replies Latest reply: Sep 17, 2007 5:14 PM by 157315 RSS

    10g vs 11g xquery performance with XBRL

    587246
      Finally,I set up 11g on small notebook with 1G memory.

      The result was impresive compared to 10g ,but I need more than that.

      I used this query generating 761 rows for testing

      SELECT c.seqno,xt.ns,xt.name,nvl(xt.lang,'na') as lang,xt.unit,xt.decimals,
      xt.value
      FROM FINES_CTX c,FINES_XBRL_CLOB r,
      XMLTABLE(
      XMLNAMESPACES(
      'http://www.xbrl.org/2003/linkbase' AS "link",
      'http://www.w3.org/1999/xlink' AS "xlink",
      'http://www.w3.org/2001/XMLSchema' AS "xsd",
      'http://www.xbrl.org/2003/instance' AS "xbrli",
      'http://fss.xbrl.or.kr/kr/br/f/aa/2007-06-30' AS
      "fines-f-aa",
      'http://fss.xbrl.or.kr/kr/br/b/aa/2007-06-30' AS
      "fines-b-aa",
      'http://fss.xbrl.or.kr/kr/br/f/ad/2007-06-30' AS
      "fines-f-ad",
      'http://fss.xbrl.or.kr/kr/br/b/ad/2007-06-30' AS
      "fines-b-ad",
      'http://fss.xbrl.or.kr/kr/br/f/af/2007-06-30' AS
      "fines-f-af",
      'http://fss.xbrl.or.kr/kr/br/b/af/2007-06-30' AS
      "fines-b-af",
      'http://fss.xbrl.or.kr/kr/br/f/ai/2007-06-30' AS
      "fines-f-ai",
      'http://fss.xbrl.or.kr/kr/br/b/ai/2007-06-30' AS
      "fines-b-ai",
      'http://fss.xbrl.or.kr/kr/br/f/ak/2007-06-30' AS
      "fines-f-ak",
      'http://fss.xbrl.or.kr/kr/br/b/ak/2007-06-30' AS
      "fines-b-ak",
      'http://fss.xbrl.or.kr/kr/br/f/bs/2007-06-30' AS
      "fines-f-bs",
      'http://fss.xbrl.or.kr/kr/br/b/bs/2007-06-30' AS
      "fines-b-bs",
      'http://xbrl.org/2005/xbrldt' AS "xbrldt",
      'http://www.xbrl.org/2004/ref' AS "ref",
      'http://www.xbrl.org/2003/XLink' AS "xl"),

      '
      for $item in $doc/xbrli:xbrl/*[not(starts-with(name(),"xbrli:")) and not(starts-with(name(),"link:"))]
      where $item/@contextRef
      return <item decimals="{$item/@decimals}" contextRef="{$item/@contextRef}" xml:lang="{$item/@xml:lang}" unitRef="{$item/@unitRef}" name="{local-name($item)}" ns="{namespace-uri($item)}">{$item/text()}</item>'
      PASSING r.xbrl as "doc"
      COLUMNS context_id varchar2(128) PATH '@contextRef',
      ns varchar2(128) PATH '@ns',
      name varchar2(128) PATH '@name',
      lang varchar2(2) PATH '@xml:lang',
      unit varchar2(16) PATH '@unitRef',
      decimals varchar2(64) PATH '@decimals',
      value varchar(256) PATH '.'
      ) xt
      WHERE c.report_cd = r.report_cd and c.finance_cd = r.finance_cd and
      c.base_month = r.base_month and c.gubn_cd = r.gubn_cd
      and c.seqno = 109299 and c.context_id = xt.context_id

      all the tables have 500 rows and non-schema-based xmltype clolumn.
      FINES_XBRL_CLOB - xmltype stored as clob
      FINES_XBRL_BINARY - xmltype stored as binary with xml index
      FINES_XBRL_BINARY_NI - xmltype stored as binary without xml index.

      case 1 : run on 10g with XMLType stored as CLOB
      time: took 1270 secs.- quite disappointed.
      plan: 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=26 Card=82 Bytes=173K)
      1 0 NESTED LOOPS (Cost=26 Card=82 Bytes=173K)
      2 1 NESTED LOOPS (Cost=2 Card=1 Bytes=2K)
      3 2 TABLE ACCESS (BY INDEX ROWID) OF 'FINES_CTXB' (TABLE) (Cost=1 Card=1 Bytes=119)
      4 3 INDEX (UNIQUE SCAN) OF 'PK_FINES_CTXB' (INDEX (UNIQUE)) (Cost=1 Card=1)
      5 2 TABLE ACCESS (BY INDEX ROWID) OF 'FINES_XBRLB' (TABLE) (Cost=1 Card=82 Bytes=164K)
      6 5 INDEX (UNIQUE SCAN) OF 'PK_FINES_XBRLB' (INDEX (UNIQUE)) (Cost=0 Card=1)
      7 1 COLLECTION ITERATOR (PICKLER FETCH) OF 'SYS.XQSEQUENCEFROMXMLTYPE' (PROCEDURE)


      case 2: run on 11g with XMLType stored as CLOB
      time: took 27 secs. - almost 50 times faster
      plan:
      0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=114 Card=1 Bytes=2K)
      1 0 FILTER
      2 1 NESTED LOOPS (Cost=32 Card=82 Bytes=173K)
      3 2 NESTED LOOPS (Cost=3 Card=1 Bytes=2K)
      4 3 TABLE ACCESS (BY INDEX ROWID) OF 'FINES_CTX' (TABLE) (Cost=2 Card=1 Bytes=119)
      5 4 INDEX (UNIQUE SCAN) OF 'PK_FINES_CTX' (INDEX (UNIQUE)) (Cost=1 Card=1)
      6 3 TABLE ACCESS (BY INDEX ROWID) OF 'FINES_XBRL_CLOB' (TABLE) (Cost=1 Card=5K Bytes=10M)
      7 6 INDEX (UNIQUE SCAN) OF 'PK_FINES_XBRL_CLOB' (INDEX (UNIQUE)) (Cost=0 Card=1)
      8 2 COLLECTION ITERATOR (PICKLER FETCH) OF 'SYS.XMLSEQUENCEFROMXMLTYPE' (PROCEDURE)
      9 1 COLLECTION ITERATOR (PICKLER FETCH) OF 'SYS.XQSEQUENCEFROMXMLTYPE' (PROCEDURE)


      case 3: run on 11g with XMLType stored as BINARY no XMLIndex
      time: 10 secs (9.6 sec exactly) , 120 times faster..
      0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=113 Card=1 Bytes=2K)
      1 0 FILTER
      2 1 NESTED LOOPS (Cost=33 Card=80 Bytes=169K)
      3 2 NESTED LOOPS (Cost=3 Card=1 Bytes=2K)
      4 3 TABLE ACCESS (BY INDEX ROWID) OF 'FINES_CTX' (TABLE) (Cost=2 Card=1 Bytes=119)
      5 4 INDEX (UNIQUE SCAN) OF 'PK_FINES_CTX' (INDEX (UNIQUE)) (Cost=1 Card=1)
      6 3 TABLE ACCESS (BY INDEX ROWID) OF 'FINES_XBRL_BINARY_NI' (TABLE) (Cost=1 Card=82 Bytes=164K)
      7 6 INDEX (UNIQUE SCAN) OF 'PK_FINES_BINARY_XBRL_NI' (INDEX (UNIQUE)) (Cost=0 Card=1)
      8 2 XPATH EVALUATION
      9 1 XPATH EVALUATION


      CREATE INDEX fines_xbrl_binary_ix ON fines_xbrl_binary (xbrl) INDEXTYPE IS XDB.XMLIndex
      case 4: run on 11g with XMLType stored as BINARY and XMLIndex
      time: 574 secs. - oops...not good.
      plan: quite long..
      0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=16 Card=1 Bytes=5K)
      1 0 FILTER
      2 1 NESTED LOOPS
      3 2 NESTED LOOPS (Cost=4 Card=1 Bytes=4K)
      4 3 TABLE ACCESS (BY INDEX ROWID) OF 'XDB.X$PT1MP1MWL3978FCE0G24J0CM85AM' (TABLE) (Cost=0 Card=1 Bytes=1008)
      5 4 INDEX (RANGE SCAN) OF 'XDB.X$PR1MP1MWL3978FCE0G24J0CM85AM' (INDEX (UNIQUE)) (Cost=0 Card=1)
      6 3 INDEX (RANGE SCAN) OF 'SYS69876_FINES_XBRL_PATHID_IX' (INDEX) (Cost=2 Card=3)
      7 2 TABLE ACCESS (BY INDEX ROWID) OF 'SYS69876_FINES_XBRL_PATH_TABLE' (TABLE) (Cost=4 Card=1 Bytes=3K)
      8 0 FILTER
      9 8 NESTED LOOPS
      10 9 NESTED LOOPS (Cost=4 Card=1 Bytes=4K)
      11 10 TABLE ACCESS (BY INDEX ROWID) OF 'XDB.X$PT1MP1MWL3978FCE0G24J0CM85AM' (TABLE) (Cost=0 Card=1 Bytes=1008)
      12 11 INDEX (RANGE SCAN) OF 'XDB.X$PR1MP1MWL3978FCE0G24J0CM85AM' (INDEX (UNIQUE)) (Cost=0 Card=1)
      13 10 INDEX (RANGE SCAN) OF 'SYS69876_FINES_XBRL_PATHID_IX' (INDEX) (Cost=2 Card=3)
      14 9 TABLE ACCESS (BY INDEX ROWID) OF 'SYS69876_FINES_XBRL_PATH_TABLE' (TABLE) (Cost=4 Card=1 Bytes=3K)
      15 0 FILTER
      16 15 NESTED LOOPS
      17 16 NESTED LOOPS (Cost=4 Card=1 Bytes=4K)
      18 17 TABLE ACCESS (BY INDEX ROWID) OF 'XDB.X$PT1MP1MWL3978FCE0G24J0CM85AM' (TABLE) (Cost=0 Card=1 Bytes=1008)
      19 18 INDEX (RANGE SCAN) OF 'XDB.X$PR1MP1MWL3978FCE0G24J0CM85AM' (INDEX (UNIQUE)) (Cost=0 Card=1)
      20 17 INDEX (RANGE SCAN) OF 'SYS69876_FINES_XBRL_PATHID_IX' (INDEX) (Cost=2 Card=3)
      21 16 TABLE ACCESS (BY INDEX ROWID) OF 'SYS69876_FINES_XBRL_PATH_TABLE' (TABLE) (Cost=4 Card=1 Bytes=3K)
      22 0 FILTER
      23 22 NESTED LOOPS
      24 23 NESTED LOOPS (Cost=4 Card=1 Bytes=4K)
      25 24 TABLE ACCESS (BY INDEX ROWID) OF 'XDB.X$PT1MP1MWL3978FCE0G24J0CM85AM' (TABLE) (Cost=0 Card=1 Bytes=1008)
      26 25 INDEX (RANGE SCAN) OF 'XDB.X$PR1MP1MWL3978FCE0G24J0CM85AM' (INDEX (UNIQUE)) (Cost=0 Card=1)
      27 24 INDEX (RANGE SCAN) OF 'SYS69876_FINES_XBRL_PATHID_IX' (INDEX) (Cost=2 Card=3)
      28 23 TABLE ACCESS (BY INDEX ROWID) OF 'SYS69876_FINES_XBRL_PATH_TABLE' (TABLE) (Cost=4 Card=1 Bytes=3K)
      29 0 SORT (AGGREGATE) (Card=1 Bytes=3K)
      30 29 FILTER
      31 30 TABLE ACCESS (BY INDEX ROWID) OF 'SYS69876_FINES_XBRL_PATH_TABLE' (TABLE) (Cost=5 Card=32 Bytes=110K)
      32 31 INDEX (RANGE SCAN) OF 'SYS69876_FINES_XBRL_ORDKEY_IX' (INDEX) (Cost=3 Card=92)
      33 0 FILTER
      34 33 NESTED LOOPS
      35 34 NESTED LOOPS (Cost=4 Card=1 Bytes=4K)
      36 35 TABLE ACCESS (BY INDEX ROWID) OF 'XDB.X$PT1MP1MWL3978FCE0G24J0CM85AM' (TABLE) (Cost=0 Card=1 Bytes=1008)
      37 36 INDEX (RANGE SCAN) OF 'XDB.X$PR1MP1MWL3978FCE0G24J0CM85AM' (INDEX (UNIQUE)) (Cost=0 Card=1)
      38 35 INDEX (RANGE SCAN) OF 'SYS69876_FINES_XBRL_PATHID_IX' (INDEX) (Cost=2 Card=3)
      39 34 TABLE ACCESS (BY INDEX ROWID) OF 'SYS69876_FINES_XBRL_PATH_TABLE' (TABLE) (Cost=4 Card=1 Bytes=3K)
      40 0 FILTER
      41 40 NESTED LOOPS
      42 41 NESTED LOOPS (Cost=4 Card=1 Bytes=4K)
      43 42 TABLE ACCESS (BY INDEX ROWID) OF 'XDB.X$PT1MP1MWL3978FCE0G24J0CM85AM' (TABLE) (Cost=0 Card=1 Bytes=1008)
      44 43 INDEX (RANGE SCAN) OF 'XDB.X$PR1MP1MWL3978FCE0G24J0CM85AM' (INDEX (UNIQUE)) (Cost=0 Card=1)
      45 42 INDEX (RANGE SCAN) OF 'SYS69876_FINES_XBRL_PATHID_IX' (INDEX) (Cost=2 Card=3)
      46 41 TABLE ACCESS (BY INDEX ROWID) OF 'SYS69876_FINES_XBRL_PATH_TABLE' (TABLE) (Cost=4 Card=1 Bytes=3K)
      -- continue....


      With very limited test case, I personally concluded that oracle 11g's engine related XML is much better than 10g, especially when using Binary type ,getting additional performance boost.

      xbrl document is basically flat ,not heirarchical structured, that makes XMLIndex inefficient ,I guess.

      Is there any good way to use XMLIndex more efficient just with this kind of case ?
      Please point out anything I can do more.

      thanks.