Conference orarep::nomahs::dbintegrator_public_public

    So I tested DBI and Rdb performace a join over a 1Mill record table and
    a 65Mill record table (With group by etc.)

    Rdb alone took:
  Accounting information:
  Buffered I/O count:            2423         Peak working set size: 419440
  Direct I/O count:           2000583         Peak page file size:   454208
  Page faults:                  32041         Mounted volumes:            0
  Charged CPU time:           0 00:25:02.22   Elapsed time:     0 15:55:22.89

    (Do not look at Elapsed time as the machne was VERY busy)


    DBI 3.1 and the latest 3.2FT kit keep stoping with an SORT error.
    Engineering suplied a fixed kit and DBI took (with 2 seperate  links!!):

  Accounting information:
  Buffered I/O count:           29180         Peak working set size: 524288
  Direct I/O count:           1571076         Peak page file size:   675552
  Page faults:                 258018         Mounted volumes:            0
  Charged CPU time:           0 00:33:46.31   Elapsed time:     0 07:19:37.41
 

    !!!! 25% less direct IO!!!

    Slightly more CPU and pagefaults. (more virtual memory.)

    Thanks again to the DBI engineers who fixed the problem VERY fast.

    Regards, and all the best in the new company.

    Andy

    RE .-1
    
    Hello, can I get information on this sort-error patch. We are having
    serious performance problems on an Alpha 2100 running RDB V6.1-2 and
    DBI V3.1-02A (dbi share images dateed Jun-Jul 1995)
    
    I am very interested in obtaining the latest DBI for Alpha OpenVMS kit
    and patches.
    
    Thanks,
    	John

    
    Hi John,
    
    The fixes are available in the FT software only - there isn't a V3.1
    kit available with the wort errors fixed.
    
    Do you have more information as to what your performance problems look
    like - perhaps we can help.  The problems that were fixed for Andy were
    show stoppers so unless you are seeing errors on the queries they won't
    do you much good.
    
    Thanks for any information (queries, dbi db setup, etc).
    
    Dan

    Hi Dan,
    
    I have been a field test site since V1. Only now, I have no way of 
    obtaining any kits/upgrades/patches since the buyout. There are many
    people and managers in Digital right now flaming over the no support,
    no upgrades, no anything without now buying a support contract from
    Oracle for grandfathered software. If the only answer from Oracle at
    this point is "Sorry, buy support", then I will predict an absolute
    mass exodus from using DBI to other compatible products, like EDA/SQL.
    I hope this is not the case, because I was the major pusher of DBI
    use internally within Digital IM&T, and I really like DBI. I just
    don't understand why the use of DBI in Digital wasn't rendered the
    same as RDB in grandfathered software installations. I know this is
    politics and in no way do I hold anyone in engineering responsible
    for any decisions made during contract negotiations between Digital
    and Oracle. I guess this is something I need to call Don Plummer about.
    
    
    I will gladly provide the information you need to help with solving our
    problems. I will get this posted shortly.
    
    Thanks,
    	John

    
    Hi John,
    
    I'm sorry about the bad stuff that is happening.  We do need field test
    sites though and we'd be happy to get your feedback on the updated
    product.  I'll bet that now there will have to be some formal FT
    paperwork to be done.  Please don't let that dissuade you from field
    testing DBI!  We still think we have the best product in the industry,
    and we value all of the info we get back from our users.
    
    Looking forward to hearing from you.
    
    dan
    
    

$	if "BATCH" .eqs. "BATCH" then goto b
$ b:
$!
$! DRC SPECIFICS
$!

$define rdms$debug_flags "SOE"
$define dbi_trace_flags "sdi_brief,explain,dcm_ap"
$define dbi_trace_output dbi_trace.out
$r disk$ercdev_prd01:[cr_prototype.cobol]aussie
Solutions tried 1
Solutions blocks created 1
Created solutions pruned 0
Cost of the chosen solution      5.3333335E+00
Cardinality of chosen solution   1.0000000E+00
~S#0001
Firstn  Get     Retrieval sequentially of relation RDB$DATABASE 
Solutions tried 1
Solutions blocks created 1
Created solutions pruned 0
Cost of the chosen solution      3.0000000E+00
Cardinality of chosen solution   1.0000000E+00
~S#0002
Firstn  Get     Retrieval sequentially of relation DBI_DATABASE 
Solutions tried 1
Solutions blocks created 1
Created solutions pruned 0
Cost of the chosen solution      3.0000000E+00
Cardinality of chosen solution   1.0000000E+00
~S#0003
Get     Retrieval sequentially of relation DBI_DATABASE 
Solutions tried 2
Solutions blocks created 1
Created solutions pruned 0
Cost of the chosen solution      6.9499884E+00
Cardinality of chosen solution   5.0000000E+00
~S#0004
Get     Retrieval by index of relation DBI_DATABASE_LINKS
  Index name  DBI_LINK_NAME_NDX [0:0]
Solutions tried 3
Solutions blocks created 2
Created solutions pruned 0
Cost of the chosen solution      6.5340080E+00
Cardinality of chosen solution   3.1250000E-01
~S#0005
Conjunct        Get     Retrieval by index of relation DBI_LINK_PROXIES
  Index name  DBI_USER_NAME_NDX [0:0] Bool
Solutions tried 2
Solutions blocks created 1
Created solutions pruned 0
Cost of the chosen solution      7.3028999E+01
Cardinality of chosen solution   1.6300000E+02
~S#0006
Get     Retrieval by index of relation DBI_FIELDS
  Index name  DBI_FIELDS_NAME_NDX [0:0]
Solutions tried 3
Solutions blocks created 2
Created solutions pruned 1
Cost of the chosen solution      3.4716690E+01
Cardinality of chosen solution   8.4000000E+01
~S#0007
Get     Retrieval by index of relation DBI_RELATIONS
  Index name  DBI_REL_REL_NAME_NDX [0:0]
Solutions tried 3
Solutions blocks created 2
Created solutions pruned 1
Cost of the chosen solution      2.2115061E+00
Cardinality of chosen solution   1.0000000E+01
~S#0008
Get     Retrieval by index of relation DBI_MODULES
  Index name  DBI_MOD_ID_NDX [0:0]
Solutions tried 3
Solutions blocks created 2
Created solutions pruned 1
Cost of the chosen solution      2.4159481E+01
Cardinality of chosen solution   0.0000000E+00
~S#0009
Sort    Conjunct        Get     Retrieval by index of relation DBI_ROUTINES
  Index name  DBI_RTN_ID_NDX [0:0]
Solutions tried 1
Solutions blocks created 1
Created solutions pruned 0
Cost of the chosen solution      3.0000000E+00
Cardinality of chosen solution   1.0000000E+00
~S#0010
Firstn  Retrieval sequentially of relation DBI_DATABASE 
Solutions tried 3
Solutions blocks created 1
Created solutions pruned 0
Cost of the chosen solution      2.1269038E+00
Cardinality of chosen solution   6.0000000E+00
~S#0011
Get     Retrieval by index of relation DBI_USERS
  Index name  DBI_USERS_ID_NDX [0:0]
Solutions tried 2
Solutions blocks created 1
Created solutions pruned 0
Cost of the chosen solution      2.4286592E+00
Cardinality of chosen solution   3.1250000E-02
~S#0012
Aggregate       Conjunct        Get 
Retrieval by index of relation DBI_RELATIONS
  Index name  DBI_REL_REL_NAME_NDX [1:1]         Direct lookup 
Solutions tried 3
Solutions blocks created 2
Created solutions pruned 0
Cost of the chosen solution      1.4286592E+00
Cardinality of chosen solution   1.0000000E+00
~S#0013
Firstn  Aggregate       Index only retrieval of relation DBI_RELATIONS
  Index name  DBI_REL_REL_NAME_NDX [1:1]         Direct lookup 
Solutions tried 2
Solutions blocks created 1
Created solutions pruned 0
Cost of the chosen solution      4.5338977E+02
Cardinality of chosen solution   0.0000000E+00
~S#0014
Sort 
Leaf#01 BgrOnly DBI_RELATION_FIELDS Card=846
  BgrNdx1 DBI_RFR_REL_NAME_FLD_ID_NDX [0:0] Bool Fan=8
~E#0014.01(1) BgrNdx1 EofData  DBKeys=9  Fetches=2+6  RecsOut=0 #Bufs=2
~E#0014.01(1) Fin     Buf      DBKeys=9  Fetches=0+2  RecsOut=9
Solutions tried 3
Solutions blocks created 1
Created solutions pruned 0
Cost of the chosen solution      2.5076149E+00
Cardinality of chosen solution   0.0000000E+00
~S#0015
Conjunct        Get     Retrieval by index of relation DBI_VIEW_RELATIONS
  Index name  DBI_VIEW_VIEW_NAME_NDX [0:0] Bool
Solutions tried 3
Solutions blocks created 3
Created solutions pruned 2
Cost of the chosen solution      7.4243904E+01
Cardinality of chosen solution   0.0000000E+00
~S#0016
Sort    Conjunct        Get 
Retrieval sequentially of relation DBI_INTERRELATIONS 
Solutions tried 2
Solutions blocks created 2
Created solutions pruned 1
Cost of the chosen solution      3.8769650E+01
Cardinality of chosen solution   0.0000000E+00
~S#0017
Sort    Conjunct        Get     Retrieval sequentially of relation DBI_INDICES 
Solutions tried 2
Solutions blocks created 1
Created solutions pruned 0
Cost of the chosen solution      4.2821705E+01
Cardinality of chosen solution   0.0000000E+00
~S#0018
Conjunct        Get     Retrieval by index of relation DBI_INDEX_SEGMENTS
  Index name  DBI_NDX_SEG_NAM_FLD_POS_NDX [0:0] Bool
~E#0014.01(2) BgrNdx1 EofData  DBKeys=10  Fetches=2+6  RecsOut=0 #Bufs=2
~E#0014.01(2) Fin     Buf      DBKeys=10  Fetches=0+2  RecsOut=10
~E#0014.01(3) BgrNdx1 EofData  DBKeys=5  Fetches=2+6  RecsOut=0 #Bufs=1
~E#0014.01(3) Fin     Buf      DBKeys=5  Fetches=0+1  RecsOut=5
~E#0014.01(4) BgrNdx1 EofData  DBKeys=7  Fetches=2+6  RecsOut=0 #Bufs=1
~E#0014.01(4) Fin     Buf      DBKeys=7  Fetches=0+1  RecsOut=7
~E#0014.01(5) BgrNdx1 EofData  DBKeys=76  Fetches=2+6  RecsOut=0 #Bufs=6
~E#0014.01(5) Fin     Buf      DBKeys=76  Fetches=0+5  RecsOut=76
Solutions tried 1
Solutions blocks created 1
Created solutions pruned 0
Cost of the chosen solution      2.1319008E+05
Cardinality of chosen solution   1.0000000E+00
~S#0019
Firstn  Get     Retrieval sequentially of relation DTL_XPLOD_OR 
Solutions tried 2
Solutions blocks created 1
Created solutions pruned 0
Cost of the chosen solution      1.3453175E+03
Cardinality of chosen solution   1.0000000E+00
~S#0020
Firstn  Index only retrieval of relation BU_ACCT_RT
  Index name  BU_ACCT_RT_IDX_5 [0:0]
Solutions tried 3
Solutions blocks created 1
Created solutions pruned 0
Cost of the chosen solution      3.1078274E+00
Cardinality of chosen solution   1.0705882E+01
~S#0021
Leaf#01 FFirst BU_SEGMT_RT Card=182
  BgrNdx1 BU_SEGMT_RT_IDX_2 [1:1] Fan=18
Solutions tried 2
Solutions blocks created 1
Created solutions pruned 0
Cost of the chosen solution      2.1710646E+00
Cardinality of chosen solution   9.9999994E-01
~S#0022
Get     Retrieval by index of relation ISO_CNTRY_RT
  Index name  ISO_CNTRY_RT_IDX_1 [1:1]   Direct lookup 
Solutions tried 7
Solutions blocks created 2
Created solutions pruned 1
Cost of the chosen solution      6.9358594E+02
Cardinality of chosen solution   1.4768514E+03
~S#0023
Leaf#01 FFirst DTL_XPLOD_OR Card=4246409
  BgrNdx1 DTL_XPLOD_OR_IDX_13 [1:1] Fan=31
  BgrNdx2 DTL_XPLOD_OR_IDX_17 [1:1...]3 Fan=32
  BgrNdx3 DTL_XPLOD_OR_IDX_1 [1:1] Fan=29
Solutions tried 39
Solutions blocks created 8
Created solutions pruned 3
Cost of the chosen solution      3.2289494E+04
Cardinality of chosen solution   7.0178100E+05
~S#0024
Conjunct 
Match 
  Outer loop 
    Get     Retrieval by index of relation BU_ACCT_RT
      Index name  BU_ACCT_RT_IDX_1 [0:0]
  Inner loop      (zig-zag) 
    Get     Retrieval by index of relation CUSTMR_ADDR_RT
      Index name  CUSTMR_ADDR_RT_IDX_3 [0:0]
~E#0023.01(1) Estim   Ndx:Lev/Seps/DBKeys 2:_2077 1:4/1\30785 3:4/9\233409
~E#0023.01(1) FgrNdx  FFirst   DBKeys=1024  Fetches=0+14543  RecsOut=1024`ABA
~E#0023.01(1) BgrNdx2 EofData  DBKeys=1489168* Fetches=6+228  RecsOut=1024 #Bufs=27088
~E#0023.01(1) BgrNdx1 EofData  DBKeys=132373* Fetches=4+7  RecsOut=1024 #Bufs=3968
~E#0023.01(1) BgrNdx3 EofData  DBKeys=110064* Fetches=0+4  RecsOut=1024 #Bufs=6240
~E#0023.01(1) Fin     TTbl     DBKeys=109040  Fetches=0+2196  RecsOut=69889
~E#0021.01(1) BgrNdx1 EofData  DBKeys=12  Fetches=1+1  RecsOut=12 #Bufs=1
~E#0021.01(1) FgrNdx  FFirst   DBKeys=12  Fetches=0+2  RecsOut=12`ABA
~E#0021.01(1) Fin     Buf      DBKeys=12  Fetches=0+0  RecsOut=12
an unexpected error was encountered
        -1
%RDB-F-SYS_REQUEST, error from system services request
-DBI-E-SORTEXECFAIL, Sort execution failed
-SORT-W-SYSERROR, system service error
%TRACE-F-TRACEBACK, symbolic stack dump follows
 Image Name   Module Name     Routine Name    Line Number  rel PC      abs PC 
 AUSSIE       SQL$GETERR      SQL$SIGNAL_STOP        4961 000011C8    00043878
 AUSSIE       SQL$GETERR      SQL$SIGNAL             4659 00000404    00042AB4
 AUSSIE       AUSSIE          AUSSIE                  147 00000350    00030350
 AUSSIE                                                 0 00025394    00035394
                                                        0 ADDFE170    ADDFE170
  CR_PROTOTYPE job terminated at 29-MAY-1996 11:14:44.80

  Accounting information:
  Buffered I/O count:            4007         Peak working set size: 159344
  Direct I/O count:            162710         Peak page file size:   227872
  Page faults:                   9487         Mounted volumes:            0
  Charged CPU time:           0 00:15:20.98   Elapsed time:     0 00:36:59.15

    In the previous note, the query runs with almost no working set
    (10,000) or virtual pages (50,000) until the direct i/o count gets
    up to about 120,000. At that point the memory usage goes crazy until
    the point it fails with the sort error.
    
    
    Thanks,
    	John