3 Replies Latest reply on Oct 21, 2009 10:24 PM by alwu-Oracle

    Failing to execute large queries --> 11.1.0.7.0

    617912
    617912 Oct 8, 2009 11:18 AM
      Below is the query:

      SELECT DISTINCT
      product1 tag , product3 suppliername , product6 peerreview_score , product9 externalreview_score ,
      product10 shoppingcategory , product11 leadtime , product14 manufacturerrating_score , product17 supplierrating_score ,
      product20 corporatecompliance_score , product22 price , product23 description , product24 name ,
      product27 buyerpercentage_score , product29 manufacturername , product30 imageurl , product31 internalitemnumber
      from TABLE
      ( sdo_rdf_match (
      '(?x rdf:type v1:Supplier) (?x v1:has_CompanyProfile ?x01) (?x01 v1:has_Region ?x02) (?x v1:has_CompanyProfile ?x11)
      (?x11 v1:has_ExportPercentage ?x12) (?x v1:has_CompanyProfile ?x21) (?x21 v1:has_Contact ?x22) (?x22 v1:has_Email ?x23)
      (?x v1:has_YearEstablished ?x31) (?x v1:has_AnnualRevenue ?x41) (?x v1:has_EmployeeCount ?x51) (?x v1:has_CompanyProfile ?x61)
      (?x61 v1:has_Site ?x62) (?x62 v2:has_Address ?x63) (?x63 v2:has_Country ?x64) (?x v1:has_Id ?x71) (?x v1:has_CompanyProfile ?x81)
      (?x81 v1:has_Contact ?x82) (?x82 v1:has_InstantMessengerId ?x83) (?x v1:has_CompanyProfile ?x91) (?x91 v1:has_Contact ?x92)
      (?x92 v1:has_Name ?x93) (?x v1:has_DUNSNumber ?x101) (?x v1:has_CEOName ?x111) (?x v2:has_Rating ?x121)
      (?x121 v2:has_EnterpriseRatingComponent ?x122) (?x122 v2:has_CompositeRatingScore ?x123) (?x v1:has_ImageURL ?x131)
      (?x v1:has_Name ?x141) (?product rdf:type v1:Product) (?product ?some_property ?x) (?some_property rdf:type owl:ObjectProperty)
      (?product v1:has_Tags ?product1) (?product v1:has_Supplier ?product2) (?product2 v1:has_Name ?product3)
      (?product v2:has_Rating ?product4) (?product4 v2:has_EnterpriseRatingComponent ?product5) (?product5 v2:has_PeerReviewScore ?product6)
      (?product v2:has_Rating ?product7) (?product7 v2:has_SocialRatingComponent ?product8) (?product8 v2:has_CompositeRatingValue ?product9)
      (?product v1:has_ShoppingCategory ?product10) (?product v1:has_LeadTime ?product11) (?product v2:has_Rating ?product12)
      (?product12 v2:has_EnterpriseRatingComponent ?product13) (?product13 v2:has_ManufacturerRatingScore ?product14)
      (?product v2:has_Rating ?product15) (?product15 v2:has_EnterpriseRatingComponent ?product16)
      (?product16 v2:has_SupplierRatingScore ?product17) (?product v2:has_Rating ?product18)
      (?product18 v2:has_EnterpriseRatingComponent ?product19) (?product19 v2:has_CorporateComplianceScore ?product20)
      (?product v1:has_Price ?product21) (?product21 v1:has_Value ?product22) (?product v1:has_Description ?product23)
      (?product v1:has_Name ?product24) (?product v2:has_Rating ?product25) (?product25 v2:has_EnterpriseRatingComponent ?product26)
      (?product26 v2:has_BuyerPercentageScore ?product27) (?product v1:has_OEM ?product28) (?product28 v1:has_Name ?product29)
      (?product v1:has_ImageURL ?product30) (?product v1:has_InternalItemId ?product31) ' ,

      sdo_rdf_models('Proc3OntologyForAdityaM'),
      null,
      sdo_rdf_aliases( sdo_rdf_alias('owl', 'http://www.w3.org/2002/07/owl#') ,
      sdo_rdf_alias('v4', 'http://xmlns.com/foaf/0.1#') ,
      sdo_rdf_alias('v3', 'http://rdfs.org/sioc/ns#') ,
      sdo_rdf_alias('v2', 'http://oracle/apps/qp/scm/enterprise_sioc#') ,
      sdo_rdf_alias('v1', 'http://oracle/apps/scm/proc3/ontology#') ) ,
      null ) )
      WHERE REGEXP_like(x141 , 'dhl', 'i')
      And the error message:

      java.sql.SQLException: No more data to read from socket
      at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:74)
      at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:110)
      at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:171)
      at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:227)
      at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:439)
      at oracle.jdbc.driver.T4CMAREngine.unmarshalUB1(T4CMAREngine.java:1042)
      at oracle.jdbc.driver.T4CMAREngine.unmarshalSB1(T4CMAREngine.java:999)
      at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:584)
      at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:183)
      at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:774)
      at oracle.jdbc.driver.T4CStatement.executeMaybeDescribe(T4CStatement.java:849)
      at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1186)
      at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1377)
      at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:386)

      Most queries of this size are not working. When i downsize the pattern OR selected columns, some times they are working and other times, they are not.
      Is there any limit on the query size in 11g? Also, i found that these queries are working on 10g.

      Please suggest me a solution.
      DB Version : 11.1.0.7.0
      • 2681 Views
      • Tags:
        • 1. Re: Failing to execute large queries --> 11.1.0.7.0
          alwu-Oracle
          alwu-Oracle Oct 12, 2009 1:24 PM (in response to 617912)
          Hi,

          Have you tried the same query on 11.2 database?

          One thing I'd like to point out is your query has a bit too many patterns. Behind the scene, we use table joins to implement the sparql query you send in. The number of joins is linear to the number of patterns you have. Keep in mind that such a big query is tricky to optimize.

          Is it possible to simplify your query?

          Thanks,

          Zhe Wu
          • 2. Re: Failing to execute large queries --> 11.1.0.7.0
            617912
            617912 Oct 13, 2009 8:55 AM (in response to alwu-Oracle)
            Hi alwu,

            Thanks for your reply. Our ontology design and our query requirements demand queries with large patterns. Generally, we want to retrieve 10 to 20 properties of any given class. I am assuming it is a common requirement, to retrieve that many number of properties, is it not? In any case, is there any way we can model our queries to perform better? Any performance guidelines etc..?

            Thanks,
            Rajesh.
            • 3. Re: Failing to execute large queries --> 11.1.0.7.0
              alwu-Oracle
              alwu-Oracle Oct 21, 2009 10:24 PM (in response to 617912)
              Hi,

              It is not that common to have queries with so many patterns :) However, without knowing details of your applications, I would not say you did something wrong. When a query has so many patterns, join orders and types of joins are critical for performance.

              It is a good idea to 1) gather statistics, 2) set a realistic performance target, 3) check execution plan of slow running queries, understand why it is slow, and 4) tune the query (by either changing pattern order, or by adding hints. Please refer to http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28397/sdo_rdf_newfeat.htm for adding hints to SEM_MATCH).

              Cheers,

              Zhe Wu