9 Replies Latest reply on Sep 21, 2009 4:39 PM by 630782

    Performance much worse on Windows 64bit than on Windows 32bit

    630782
      Hi,

      I installed Oracle 11.1.0.7 for Windows 64 bit with the Oracle Database 11g Release 1 - Interim Patch for Base Bugs: 7600122 on a Windows 64bit server with 12G memory. Some of my SPARQL queries through the Oracle Jena adapter finished pretty fast, but some of the SPARQL queries won't finish after a long time waiting so I have to stop it. The same queries run fine on the 11.1.0.6 version on a Windows 32bit server with 3G memory though they do take some time to run. So I uninstalled 11.1.0.7 and installed 11.1.0.6 for Windows 64 bit with Oracle Database 11g Release 1 - Patch Set for 11.1.0.6 on the server. Those queries still won't finish. That's really unexpected.

      Are there any special configurations needed for Oracle 11.1.0.6/11.1.0.7 Windows 64 bit regarding RDF/SPARQL support?

      Thanks,

      Weihua
        • 1. Re: Performance much worse on Windows 64bit than on Windows 32bit
          alwu-Oracle
          Hmm. What kind of queries run slow on 11.1.0.7? I assume you've gather statistics.

          Thanks,

          Zhe Wu
          • 2. Re: Performance much worse on Windows 64bit than on Windows 32bit
            630782
            One example query is
            select count(ta) FROM table(SEM_MATCH('
            (?ta <PREDICATE1> VALUE1)
            (?ta <PREDICATE2> VALUE2)
            (?ta <PREDICATE3> ?affyId)
            (?affyId <PREDICATE4> VALUE3)
            ', SEM_MODELS(MODEL_NAME),
            null, null, null ))

            Yes, I've gathered statistics for the application table holding the triples. Thanks,

            Weihua
            • 3. Re: Performance much worse on Windows 64bit than on Windows 32bit
              alwu-Oracle
              Hi,

              Have you compared the query execution plan? It is likely that the plan has been changed.

              Zhe Wu
              • 4. Re: Performance much worse on Windows 64bit than on Windows 32bit
                630782
                I compared the execution plan between the 32bit server and 64bit server and they are the same.

                When I created the table space for the network I used big table file option and specify the initial data file size to be 4G. Could that cause problem? Thanks,

                Weihua
                • 5. Re: Performance much worse on Windows 64bit than on Windows 32bit
                  alwu-Oracle
                  Did you run either sem_perf.gather_stats or sem_apis.analyze_model?

                  Could you please cut & paste the two plans? It is a bit unlikely that the bigfile option is causing the difference.

                  Thanks,

                  Zhe Wu
                  • 6. Re: Performance much worse on Windows 64bit than on Windows 32bit
                    630782
                    Yes, I ran the sem_apis.analyze_model on the 64bit server.

                    I re-generated the query plan on both the 32bit and 64bit server. It looks that the query plan generated on the 32bit server is much longer than the one generated on 64bit server. I don't know why I thought they are the same when I generated them the first time. Maybe I didn't pay close attention. Sorry about that. Here are the plans.

                    For 64bit server:
                    "OPERATION","OPTIONS","OBJECT_NAME","ID","PARENT_ID","POSITION","COST","CARDINALITY","OTHER_TAG","OPTIMIZER"
                    "SELECT STATEMENT","","","0","","29","29","8168","","ALL_ROWS"
                    "SELECT STATEMENT","","","0","","29","29","8168","","ALL_ROWS"
                    "COLLECTION ITERATOR","SUBQUERY FETCH","","1","0","1","","","",""
                    "COLLECTION ITERATOR","SUBQUERY FETCH","","1","0","1","","","",""
                    "NESTED LOOPS","","","2","1","1","","","",""
                    "NESTED LOOPS","","","2","1","1","","","",""
                    "NESTED LOOPS","","","3","2","1","11","1","",""
                    "NESTED LOOPS","","","3","2","1","11","1","",""
                    "VIEW","","","4","3","1","9","1","",""
                    "VIEW","","","4","3","1","9","1","",""
                    "NESTED LOOPS","","","5","4","1","9","1","",""
                    "NESTED LOOPS","","","5","4","1","9","1","",""
                    "NESTED LOOPS","","","6","5","1","7","1","",""
                    "NESTED LOOPS","","","6","5","1","7","1","",""
                    "MERGE JOIN","CARTESIAN","","7","6","1","5","1","",""
                    "MERGE JOIN","CARTESIAN","","7","6","1","5","1","",""
                    "PARTITION LIST","SINGLE","","8","7","1","3","1","",""
                    "PARTITION LIST","SINGLE","","8","7","1","3","1","",""
                    "INDEX","RANGE SCAN","RDF_LNK_PVIDCENSNMID_IDX","9","8","1","3","1","","ANALYZED"
                    "INDEX","RANGE SCAN","RDF_LNK_PVIDCENSNMID_IDX","9","8","1","3","1","","ANALYZED"
                    "BUFFER","SORT","","10","7","2","2","1","",""
                    "BUFFER","SORT","","10","7","2","2","1","",""
                    "PARTITION LIST","SINGLE","","11","10","1","2","1","",""
                    "PARTITION LIST","SINGLE","","11","10","1","2","1","",""
                    "INDEX","RANGE SCAN","RDF_LNK_PVIDCENSNMID_IDX","12","11","1","2","1","","ANALYZED"
                    "INDEX","RANGE SCAN","RDF_LNK_PVIDCENSNMID_IDX","12","11","1","2","1","","ANALYZED"
                    "PARTITION LIST","SINGLE","","13","6","2","2","1","",""
                    "PARTITION LIST","SINGLE","","13","6","2","2","1","",""
                    "INDEX","RANGE SCAN","RDF_LNK_PVIDCENSNMID_IDX","14","13","1","2","1","","ANALYZED"
                    "INDEX","RANGE SCAN","RDF_LNK_PVIDCENSNMID_IDX","14","13","1","2","1","","ANALYZED"
                    "PARTITION LIST","SINGLE","","15","5","2","2","1","",""
                    "PARTITION LIST","SINGLE","","15","5","2","2","1","",""
                    "INDEX","RANGE SCAN","RDF_LNK_PVIDCENSNMID_IDX","16","15","1","2","1","","ANALYZED"
                    "INDEX","RANGE SCAN","RDF_LNK_PVIDCENSNMID_IDX","16","15","1","2","1","","ANALYZED"
                    "INDEX","UNIQUE SCAN","C_PK_VID","17","3","2","1","1","","ANALYZED"
                    "INDEX","UNIQUE SCAN","C_PK_VID","17","3","2","1","1","","ANALYZED"
                    "TABLE ACCESS","BY INDEX ROWID","RDF_VALUE$","18","2","2","2","1","","ANALYZED"
                    "TABLE ACCESS","BY INDEX ROWID","RDF_VALUE$","18","2","2","2","1","","ANALYZED"

                    For 32bit server:
                    "OPERATION","OPTIONS","OBJECT_NAME","ID","PARENT_ID","POSITION","COST","CARDINALITY","OTHER_TAG","OPTIMIZER"
                    "SELECT STATEMENT","","","0","","29","29","8168","","ALL_ROWS"
                    "SELECT STATEMENT","","","0","","29","29","8168","","ALL_ROWS"
                    "SELECT STATEMENT","","","0","","29","29","8168","","ALL_ROWS"
                    "SELECT STATEMENT","","","0","","29","29","8168","","ALL_ROWS"
                    "SELECT STATEMENT","","","0","","29","29","8168","","ALL_ROWS"
                    "COLLECTION ITERATOR","SUBQUERY FETCH","","1","0","1","","","",""
                    "COLLECTION ITERATOR","SUBQUERY FETCH","","1","0","1","","","",""
                    "COLLECTION ITERATOR","SUBQUERY FETCH","","1","0","1","","","",""
                    "COLLECTION ITERATOR","SUBQUERY FETCH","","1","0","1","","","",""
                    "COLLECTION ITERATOR","SUBQUERY FETCH","","1","0","1","","","",""
                    "NESTED LOOPS","","","2","1","1","","","",""
                    "NESTED LOOPS","","","2","1","1","","","",""
                    "NESTED LOOPS","","","2","1","1","","","",""
                    "NESTED LOOPS","","","2","1","1","","","",""
                    "NESTED LOOPS","","","2","1","1","","","",""
                    "NESTED LOOPS","","","3","2","1","11","1","",""
                    "NESTED LOOPS","","","3","2","1","11","1","",""
                    "NESTED LOOPS","","","3","2","1","11","1","",""
                    "NESTED LOOPS","","","3","2","1","11","1","",""
                    "NESTED LOOPS","","","3","2","1","11","1","",""
                    "VIEW","","","4","3","1","9","1","",""
                    "VIEW","","","4","3","1","9","1","",""
                    "VIEW","","","4","3","1","9","1","",""
                    "VIEW","","","4","3","1","9","1","",""
                    "VIEW","","","4","3","1","9","1","",""
                    "NESTED LOOPS","","","5","4","1","9","1","",""
                    "NESTED LOOPS","","","5","4","1","9","1","",""
                    "NESTED LOOPS","","","5","4","1","9","1","",""
                    "NESTED LOOPS","","","5","4","1","9","1","",""
                    "NESTED LOOPS","","","5","4","1","9","1","",""
                    "NESTED LOOPS","","","6","5","1","7","1","",""
                    "NESTED LOOPS","","","6","5","1","7","1","",""
                    "NESTED LOOPS","","","6","5","1","7","1","",""
                    "NESTED LOOPS","","","6","5","1","7","1","",""
                    "NESTED LOOPS","","","6","5","1","7","1","",""
                    "MERGE JOIN","CARTESIAN","","7","6","1","5","1","",""
                    "MERGE JOIN","CARTESIAN","","7","6","1","5","1","",""
                    "MERGE JOIN","CARTESIAN","","7","6","1","5","1","",""
                    "MERGE JOIN","CARTESIAN","","7","6","1","5","1","",""
                    "MERGE JOIN","CARTESIAN","","7","6","1","5","1","",""
                    "PARTITION LIST","SINGLE","","8","7","1","3","1","",""
                    "PARTITION LIST","SINGLE","","8","7","1","3","1","",""
                    "PARTITION LIST","SINGLE","","8","7","1","3","1","",""
                    "PARTITION LIST","SINGLE","","8","7","1","3","1","",""
                    "PARTITION LIST","SINGLE","","8","7","1","3","1","",""
                    "INDEX","RANGE SCAN","RDF_LNK_PVIDCENSNMID_IDX","9","8","1","3","1","","ANALYZED"
                    "INDEX","RANGE SCAN","RDF_LNK_PVIDCENSNMID_IDX","9","8","1","3","1","","ANALYZED"
                    "INDEX","RANGE SCAN","RDF_LNK_PVIDCENSNMID_IDX","9","8","1","3","1","","ANALYZED"
                    "INDEX","RANGE SCAN","RDF_LNK_PVIDCENSNMID_IDX","9","8","1","3","1","","ANALYZED"
                    "INDEX","RANGE SCAN","RDF_LNK_PVIDCENSNMID_IDX","9","8","1","3","1","","ANALYZED"
                    "BUFFER","SORT","","10","7","2","2","1","",""
                    "BUFFER","SORT","","10","7","2","2","1","",""
                    "BUFFER","SORT","","10","7","2","2","1","",""
                    "BUFFER","SORT","","10","7","2","2","1","",""
                    "BUFFER","SORT","","10","7","2","2","1","",""
                    "PARTITION LIST","SINGLE","","11","10","1","2","1","",""
                    "PARTITION LIST","SINGLE","","11","10","1","2","1","",""
                    "PARTITION LIST","SINGLE","","11","10","1","2","1","",""
                    "PARTITION LIST","SINGLE","","11","10","1","2","1","",""
                    "PARTITION LIST","SINGLE","","11","10","1","2","1","",""
                    "INDEX","RANGE SCAN","RDF_LNK_PVIDCENSNMID_IDX","12","11","1","2","1","","ANALYZED"
                    "INDEX","RANGE SCAN","RDF_LNK_PVIDCENSNMID_IDX","12","11","1","2","1","","ANALYZED"
                    "INDEX","RANGE SCAN","RDF_LNK_PVIDCENSNMID_IDX","12","11","1","2","1","","ANALYZED"
                    "INDEX","RANGE SCAN","RDF_LNK_PVIDCENSNMID_IDX","12","11","1","2","1","","ANALYZED"
                    "INDEX","RANGE SCAN","RDF_LNK_PVIDCENSNMID_IDX","12","11","1","2","1","","ANALYZED"
                    "PARTITION LIST","SINGLE","","13","6","2","2","1","",""
                    "PARTITION LIST","SINGLE","","13","6","2","2","1","",""
                    "PARTITION LIST","SINGLE","","13","6","2","2","1","",""
                    "PARTITION LIST","SINGLE","","13","6","2","2","1","",""
                    "PARTITION LIST","SINGLE","","13","6","2","2","1","",""
                    "INDEX","RANGE SCAN","RDF_LNK_PVIDCENSNMID_IDX","14","13","1","2","1","","ANALYZED"
                    "INDEX","RANGE SCAN","RDF_LNK_PVIDCENSNMID_IDX","14","13","1","2","1","","ANALYZED"
                    "INDEX","RANGE SCAN","RDF_LNK_PVIDCENSNMID_IDX","14","13","1","2","1","","ANALYZED"
                    "INDEX","RANGE SCAN","RDF_LNK_PVIDCENSNMID_IDX","14","13","1","2","1","","ANALYZED"
                    "INDEX","RANGE SCAN","RDF_LNK_PVIDCENSNMID_IDX","14","13","1","2","1","","ANALYZED"
                    "PARTITION LIST","SINGLE","","15","5","2","2","1","",""
                    "PARTITION LIST","SINGLE","","15","5","2","2","1","",""
                    "PARTITION LIST","SINGLE","","15","5","2","2","1","",""
                    "PARTITION LIST","SINGLE","","15","5","2","2","1","",""
                    "PARTITION LIST","SINGLE","","15","5","2","2","1","",""
                    "INDEX","RANGE SCAN","RDF_LNK_PVIDCENSNMID_IDX","16","15","1","2","1","","ANALYZED"
                    "INDEX","RANGE SCAN","RDF_LNK_PVIDCENSNMID_IDX","16","15","1","2","1","","ANALYZED"
                    "INDEX","RANGE SCAN","RDF_LNK_PVIDCENSNMID_IDX","16","15","1","2","1","","ANALYZED"
                    "INDEX","RANGE SCAN","RDF_LNK_PVIDCENSNMID_IDX","16","15","1","2","1","","ANALYZED"
                    "INDEX","RANGE SCAN","RDF_LNK_PVIDCENSNMID_IDX","16","15","1","2","1","","ANALYZED"
                    "INDEX","UNIQUE SCAN","C_PK_VID","17","3","2","1","1","","ANALYZED"
                    "INDEX","UNIQUE SCAN","C_PK_VID","17","3","2","1","1","","ANALYZED"
                    "INDEX","UNIQUE SCAN","C_PK_VID","17","3","2","1","1","","ANALYZED"
                    "INDEX","UNIQUE SCAN","C_PK_VID","17","3","2","1","1","","ANALYZED"
                    "INDEX","UNIQUE SCAN","C_PK_VID","17","3","2","1","1","","ANALYZED"
                    "TABLE ACCESS","BY INDEX ROWID","RDF_VALUE$","18","2","2","2","1","","ANALYZED"
                    "TABLE ACCESS","BY INDEX ROWID","RDF_VALUE$","18","2","2","2","1","","ANALYZED"
                    "TABLE ACCESS","BY INDEX ROWID","RDF_VALUE$","18","2","2","2","1","","ANALYZED"
                    "TABLE ACCESS","BY INDEX ROWID","RDF_VALUE$","18","2","2","2","1","","ANALYZED"
                    "TABLE ACCESS","BY INDEX ROWID","RDF_VALUE$","18","2","2","2","1","","ANALYZED"

                    What could be the reasons that the plans are generated differently and what can be done to change that? Thanks for the help,

                    Weihua
                    • 7. Re: Performance much worse on Windows 64bit than on Windows 32bit
                      alwu-Oracle
                      Hi,

                      Are you sure you are getting the execution plan for the same query? The 32 bit plan seems to project out more variables than the 64 bit version.
                      Could you do "set autotrace on" followed by "set linesize 160" and then run the same query in two databases?

                      Thanks,

                      Zhe Wu
                      • 8. Re: Performance much worse on Windows 64bit than on Windows 32bit
                        630782
                        You are right. I didn't change the query id when I re-generated the query plan. So the plan I pasted is actually the combination of the query plans for the same query for several times. That also explains why I saw they are the same the first time.

                        It's really interesting to see that the same query runs very fast on the 64bit server. I tried several other queries and they all run pretty fast. One thing I noticed is that the oracle process now is now taking 2.3G memory while it was 900M before. I don't know what made that memory increase. The 2.3G memory usage is fine for the server as it has 12G memory as long as it performs well. I'll try is to restart the Oracle process to see if it will again start with 900M memory size and the query becomes slow again. Thanks,

                        Weihua
                        • 9. Re: Performance much worse on Windows 64bit than on Windows 32bit
                          630782
                          I restarted the Oracle process and it's memory usage is about 900M. I tried the queries and they still run pretty fast now. Can't explain what caused this mysterious behavior. Thanks,

                          Weihua