This discussion is archived
11 Replies Latest reply: Sep 11, 2013 7:40 AM by ChrisJenkins RSS

Any performance benchmark

user2545677 Newbie
Currently Being Moderated

One months ago,  I found this performance benchmark of timesten on oracle site:  https://blogs.oracle.com/BestPerf/entry/20130326_sparc_t5_timesten 

I think timesten perform excellent.

Then I spent a lot of time to read 10 timesten documents, and recently successfully migrate data from oracle database 11g2.

I test the sql search performance in timesten:  only less than twice improvement, this really depress me.  

  • 1. Re: Any performance benchmark
    ChrisJenkins Guru
    Currently Being Moderated

    There could be many reasons for this. Can you share the details? Without details how can we make any sensible comment? it may be easy to improve this depending on why it is slow.

     

    Chris

  • 2. Re: Any performance benchmark
    user2545677 Newbie
    Currently Being Moderated

    I kind of figure out the evil source :  parameterized sql

    I create two function to test the sql search performance, one is plain sql, the other is parameterized sql.

    They both do the same things with same parameters.

    But the parameterized sql is  20 times slower than  the concatenated sql string.

    what a shock,does that means we should use concatenated sql string over parameterized sql string.

    I'm really puzzled!

     

    PS: By the way I use oracle sql developer's run function to test user defined function performance.

     

    function1:(took 0.636799000  seconds  for n:=10)

    create or replace
    FUNCTION BENCHMARK1
    (
    n in number
    )
    RETURN nvarchar2 IS
    c number;
    temp number:=0;
    begt timestamp;
    endt timestamp;
    BEGIN
    execute immediate 'select tt_sysdate from dual' into begt;
    while temp<n loop
    select count(rowindex) into c from (
        select rownum as rowindex, x.* from (
         select wi.buwi_serviceuserid AS UserID,
          ws.bsus_totalavgscore as ItemScore, ws.bsus_finishordercount as FinishOrderCount,
          1 as usertype, w.bsui_username as ShowName, w.bsui_beginworkyear as WorkYear, w.bsui_onlinestatus as OnlineStatus      
          from mh_base_userwithitem WI
          inner join mh_base_serviceuserinfo W
            on wi.buwi_serviceitemid=110 and wi.buwi_serviceuserid=w.bsui_userid
              and w.bsui_activestatus=2 and (w.bsui_onlinestatus = 2)
              and ( w.bsui_educational>=2 )  
          inner join mh_base_serviceuserstatistics WS
            on w.bsui_userid=ws.bsui_userid and ( ws.bsus_totalavgscore>= 2 )
              order by itemscore desc ) x )
       where Rowindex >= 1  and Rowindex < 21;
       temp:=temp+1;
    end loop;
    execute immediate 'select tt_sysdate from dual' into endt;
      return to_char(endt-begt);
    END BENCHMARK1;
    
    
    

     

     

    function2:(took 12.722116000 seconds for n:=10)

    create or replace
    FUNCTION BENCHMARK2
    (
    n in number
    )
    RETURN nvarchar2 IS
    c number;
    temp number:=0;
    begt timestamp;
    endt timestamp;
    IN_ITEMID number:= 110;
    IN_ONLINESTATUS number:= 1;
    IN_ACTIVESTATUS number:= 2;
    IN_SCORE number:= 2;
    IN_MININDEX number:= 1;
    IN_MAXINDEX number:= 21;
    IN_EDU number:= 2;
    BEGIN
    execute immediate 'select tt_sysdate from dual' into begt;
    while temp<n loop
    select count(rowindex) into c from (
        select rownum as rowindex, x.* from (
         select wi.buwi_serviceuserid AS UserID,
          ws.bsus_totalavgscore as ItemScore, ws.bsus_finishordercount as FinishOrderCount,
          1 as usertype, w.bsui_username as ShowName, w.bsui_beginworkyear as WorkYear, w.bsui_onlinestatus as OnlineStatus        
          from mh_base_userwithitem WI
          inner join mh_base_serviceuserinfo W
            on wi.buwi_serviceitemid=In_itemid and wi.buwi_serviceuserid=w.bsui_userid
              and w.bsui_activestatus=In_ActiveStatus and (  w.bsui_onlinestatus = In_OnlineStatus)
              and (w.bsui_educational>=In_edu )   
          inner join mh_base_serviceuserstatistics WS
            on w.bsui_userid=ws.bsui_userid and (  ws.bsus_totalavgscore>= In_score )
              order by itemscore desc ) x )
       where Rowindex >= In_MinIndex  and Rowindex < In_MaxIndex;
       temp:=temp+1;
    end loop;
    execute immediate 'select tt_sysdate from dual' into endt;
      return to_char(endt-begt);
    END BENCHMARK2;
    
    
    
  • 3. Re: Any performance benchmark
    ChrisJenkins Guru
    Currently Being Moderated

    Actually, this is the exact opposite of what we would expect. It is usually always a good idea to use parameterised SQL as it is normally much faster than non-parameterised SQL. So, something unusual is going on here., My suspicion is that you are getting a sub-optimal query plan for the parameterised SQL.

     

    Can you please generate and post the TimesTen query plans for both the non-parameterised and parameterised SQL. To do this in a ttIsql session connected to to the database execute the commands:

     

    spool planfile.txt

    explain sqlsqtmt1;

    explain sqlstsmt2;

    spool off

     

    Where you replace 'sqlstmt1' and 'sqlstmt2' with the actual SQL statements. For the parameterised statement please replace the parameters with either a ? character or a parameter name of the form paramname (e.g. :minindex and :maxindex).

     

    Thanks,

     

    Chris

  • 4. Re: Any performance benchmark
    ChrisJenkins Guru
    Currently Being Moderated

    Also, could you please write a simple anonymous block to test this function and run that for both cases from ttIsql with 'timing 1' in effect. Just to eliminate any issue relating to SQL Developer.

     

    Thanks,

     

    Chris

  • 5. Re: Any performance benchmark
    user2545677 Newbie
    Currently Being Moderated

    Thanks for your answer.

    I really done the anonymous block thing first,  then for  resuablility ease I wrap them in the function.

    The time is really the same.

     

    For the query plan, i'll try ,but usally I'm pleased to use query plan explanation in oracle sql developer.

  • 6. Re: Any performance benchmark
    ChrisJenkins Guru
    Currently Being Moderated

    I think that you missed my point. I wanted you to:

     

    1.    Write an anonymous block which calls the functions that you have written (just as a wrapper).

     

    2.    Test that, with timing, using ttIsql instead of SQL Developer to eliminate any possible issue related to SQL developer here.

     

    Chris

  • 7. Re: Any performance benchmark
    ChrisJenkins Guru
    Currently Being Moderated

    I just did some experiments to see if parameterised SQL is generally a problem in this scenario and they show that it is not. hence I think the issue is very likely to be query plan related. Can you please provide the plans for both SQL statements as I requested above.

     

    Thanks,

     

    Chris

  • 8. Re: Any performance benchmark
    user2545677 Newbie
    Currently Being Moderated

    Command>        >        >        >        >        >        >        >        >        >        >        >        >        >

    Query Optimizer Plan:

     

     

      STEP:                1

      LEVEL:               4

      OPERATION:           TblLkRangeScan

      TBLNAME:             DEV.MH_BASE_SERVICEUSERINFO

      IXNAME:              MH_BASE_SERVICEUSERINFO

      INDEXED CONDITION:   <NULL>

      NOT INDEXED:         W.BSUI_ONLINESTATUS = 2 AND W.BSUI_ACTIVESTATUS = 2 AND W.BSUI_EDUCATIONAL >= 2

     

     

     

     

      STEP:                2

      LEVEL:               4

      OPERATION:           RowLkRangeScan

      TBLNAME:             DEV.MH_BASE_USERWITHITEM

      IXNAME:              MH_BASE_USERWITHITEM

      INDEXED CONDITION:   WI.BUWI_SERVICEUSERID = W.BSUI_USERID AND WI.BUWI_SERVICEITEMID = 110

      NOT INDEXED:         <NULL>

     

     

     

     

      STEP:                3

      LEVEL:               3

      OPERATION:           NestedLoop

      TBLNAME:             <NULL>

      IXNAME:              <NULL>

      INDEXED CONDITION:   <NULL>

      NOT INDEXED:         <NULL>

     

     

     

     

      STEP:                4

      LEVEL:               3

      OPERATION:           RowLkRangeScan

      TBLNAME:             MH_BASE_SERVICEUSERSTATISTICS

      IXNAME:              MH_BASE_SERVICEUSERSTATISTICS

      INDEXED CONDITION:   WS.BSUI_USERID = WI.BUWI_SERVICEUSERID

      NOT INDEXED:         WS.BSUS_TOTALAVGSCORE >= 2

     

     

     

     

      STEP:                5

      LEVEL:               2

      OPERATION:           NestedLoop

      TBLNAME:             <NULL>

      IXNAME:              <NULL>

      INDEXED CONDITION:   <NULL>

      NOT INDEXED:         <NULL>

     

     

     

     

      STEP:                6

      LEVEL:               2

      OPERATION:           OrderBy

      TBLNAME:             <NULL>

      IXNAME:              <NULL>

      INDEXED CONDITION:   <NULL>

      NOT INDEXED:         _DT2517841464.ROWINDEX < 22 AND _DT2517841464.ROWINDEX >= 2

     

     

     

     

      STEP:                7

      LEVEL:               1

      OPERATION:           OneGroupGroupBy

      TBLNAME:             <NULL>

      IXNAME:              <NULL>

      INDEXED CONDITION:   <NULL>

      NOT INDEXED:         <NULL>

     

     

    Command> Command>        >        >        >        >        >        >        >        >        >        >        >        >        >

    Query Optimizer Plan:

     

     

      STEP:                1

      LEVEL:               4

      OPERATION:           TblLkRangeScan

      TBLNAME:             DEV.MH_BASE_SERVICEUSERINFO

      IXNAME:              MH_BASE_SERVICEUSERINFO

      INDEXED CONDITION:   <NULL>

      NOT INDEXED:         W.BSUI_ONLINESTATUS = _QMARK_3 AND W.BSUI_ACTIVESTATUS = _QMARK_2 AND W.BSUI_EDUCATIONAL >= _QMARK_4

     

     

     

     

      STEP:                2

      LEVEL:               4

      OPERATION:           RowLkRangeScan

      TBLNAME:             DEV.MH_BASE_USERWITHITEM

      IXNAME:              MH_BASE_USERWITHITEM

      INDEXED CONDITION:   WI.BUWI_SERVICEUSERID = W.BSUI_USERID AND WI.BUWI_SERVICEITEMID = _QMARK_1

      NOT INDEXED:         <NULL>

     

     

     

     

      STEP:                3

      LEVEL:               3

      OPERATION:           NestedLoop

      TBLNAME:             <NULL>

      IXNAME:              <NULL>

      INDEXED CONDITION:   <NULL>

      NOT INDEXED:         <NULL>

     

     

     

     

      STEP:                4

      LEVEL:               3

      OPERATION:           RowLkRangeScan

      TBLNAME:             MH_BASE_SERVICEUSERSTATISTICS

      IXNAME:              MH_BASE_SERVICEUSERSTATISTICS

      INDEXED CONDITION:   WS.BSUI_USERID = WI.BUWI_SERVICEUSERID

      NOT INDEXED:         WS.BSUS_TOTALAVGSCORE >= _QMARK_5

     

     

     

     

      STEP:                5

      LEVEL:               2

      OPERATION:           NestedLoop

      TBLNAME:             <NULL>

      IXNAME:              <NULL>

      INDEXED CONDITION:   <NULL>

      NOT INDEXED:         <NULL>

     

     

     

     

      STEP:                6

      LEVEL:               2

      OPERATION:           OrderBy

      TBLNAME:             <NULL>

      IXNAME:              <NULL>

      INDEXED CONDITION:   <NULL>

      NOT INDEXED:         _DT2517841904.ROWINDEX < _QMARK_7 AND _DT2517841904.ROWINDEX >= _QMARK_6

     

     

     

     

      STEP:                7

      LEVEL:               1

      OPERATION:           OneGroupGroupBy

      TBLNAME:             <NULL>

      IXNAME:              <NULL>

      INDEXED CONDITION:   <NULL>

      NOT INDEXED:         <NULL>

     

     

    Command>

  • 9. Re: Any performance benchmark
    ChrisJenkins Guru
    Currently Being Moderated

    Thanks for the plans. They are identical for both queries so it is not a plan issue. I created an equivalent test using the same tables names and the same query though obviously I do not have the full table definitions or data. In my tests I see the version using bind variables being a little slower than the non-parameterised query but we are talking from maybe 20% slower up to maybe 2x slower (over 10,000 iterations) which is not unreasonable - certainly not 20x!

     

    So, I'd like to check a few other things please... Can you please tell me the exact column definitions in TimesTen for the following columns:

     

    mh_base_userwithitem.buwi_serviceuserid

    mh_base_userwithitem.buwi_serviceitemid

     

    mh_base_serviceuserinfo.bsui_userid

    mh_base_serviceuserinfo.bsui_username

    mh_base_serviceuserinfo.bsui_beginworkyear

    mh_base_serviceuserinfo.bsui_onlinestatus

    mh_base_serviceuserinfo.bsui_activestatus

    mh_base_serviceuserinfo.bsui_educational

     

    mh_base_serviceuserstatistics.bsui_userid

    mh_base_serviceuserstatistics.bsus_totalavgscore

    mh_base_serviceuserstatistics.bsus_finishordercount

     

    Thanks,

     

    Chris

  • 10. Re: Any performance benchmark
    user2545677 Newbie
    Currently Being Moderated

    Table MH_BASE_SERVICEUSERINFO:

     

    COLUMN NAMEDATA TYPENULLABLEDATA DEFAULTCOLUMN IDPRIMARY KEY
    BSUI_USERIDNUMBER(38)No(null)1Yes
    BSUI_USERNAMENVARCHAR2(64) inlineYes(null)2No
    BSUI_MOBILEPHONENVARCHAR2(32) inlineYes(null)3No
    BSUI_IDCARDNVARCHAR2(64) inlineYes(null)4No
    BSUI_PHOTONVARCHAR2(256)Yes(null)5No
    BSUI_SEXNUMBER(38)Yes(null)6No
    BSUI_BIRTHDAYDATEYes(null)7No
    BSUI_HOMETOWNNVARCHAR2(128)Yes(null)8No
    BSUI_HOMEADDRESSNVARCHAR2(256)Yes(null)9No
    BSUI_BEGINWORKYEARDATEYes(null)10No
    BSUI_USERPASSWORDNVARCHAR2(64) inlineYes(null)11No
    BSUI_EDUCATIONALNUMBER(38)Yes(null)12No
    BSUI_LANGUAGENVARCHAR2(64) inlineYes(null)13No
    BSUI_ONLINESTATUSNUMBER(38)Yes(null)14No
    BSUI_ACTIVESTATUSNUMBER(38)Yes(null)15No
    BSUI_USERSIGNALINGNVARCHAR2(64) inlineYes(null)16No
    BSUI_PUSHUSERIDNVARCHAR2(64) inlineYes(null)17No
    BSUI_PUSHCHANNELIDNVARCHAR2(64) inlineYes(null)18No
    BSUI_REGISTERTIMEDATEYes(null)19No

     

    Table MH_BASE_USERWITHITEM:

     

    COLUMN NAMEDATA TYPENULLABLEDATA DEFAULTCOLUMN IDPRIMARY KEY
    BUWI_SERVICEUSERIDNUMBER(38)No(null)1Yes
    BUWI_SERVICEITEMIDNUMBER(38)No(null)2Yes
    BUWI_STARTPRICEFLOAT(126)Yes(null)3No

     

     

    Table MH_BASE_SERVICEUSERSTATISTICS:

    COLUMN NAMEDATA TYPE NULLABLEDATA DEFAULTCOLUMN IDPRIMARY KEY
    BSUI_USERIDNUMBER(38)No(null)1Yes
    BSUS_TOTALAVGSCOREFLOAT(126)Yes(null)2No
    BSUS_FINISHORDERCOUNTNUMBER(38)Yes(null)3No
    BSUS_ORDERCOUNTNUMBER(38)Yes(null)4No
    BSUS_CANCELCOUNTNUMBER(38)Yes(null)5No
    BSUS_TODOORAVGTIMENUMBER(38)Yes(null)6No
    BSUS_RESERVEPUNCTUALRATIOFLOAT(126)Yes(null)7No
    BSUS_RESERVELATEAVGTIMENUMBER(38)Yes(null)8No
    BSUS_ACCEPTEDCOUNTNUMBER(38)Yes(null)9No
    BSUS_ACCEPTEDAVGTIMENUMBER(38)Yes(null)10No
    BSUS_ACCEPTEDRATIOFLOAT(126)Yes(null)11No
    BSUS_SCRAMBLECOUNTNUMBER(38)Yes(null)12No
    BSUS_SCRAMBLEAVGTIMENUMBER(38)Yes(null)13No
    BSUS_SCRAMBLERATIOFLOAT(126)Yes(null)14No
    BSUS_TODOORAVGDISTANCENUMBER(38)Yes(null)15No
  • 11. Re: Any performance benchmark
    ChrisJenkins Guru
    Currently Being Moderated

    Thanks for that. I have now created an exact reproduction but I still see only a 2x difference between the version that uses bind variables and the one that does not. That is quite reasonable given the significant extra work involved when using bind variables. And of course, in a real test where the query would use different values you would see a big cost overhead on the non-bind variable one due to it having to be re-prepared on every execution (so this test is not really 'real world').

     

    So, if you are still seeing a >>2x difference then that suggests that the query execution itself is taking much longer when binbd variables are used. I do nto observe that in my tests but maybe there is something about your data.

     

    Are you able to time a single iteration of this query (both forms) outside of  PL/SQL, ideally from ttIsql? You'll need to do this quiet a few times to get a feel for the average time.

     

    Either way, I think this will need you to log a service request with Oracle Support since clearly something quite strange is going on here.

     

    Chris

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points