Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Failing to execute large queries --> 11.1.0.7.0

617912Oct 8 2009 — edited Oct 21 2009
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

Comments

alwu-Oracle
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
617912
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.
alwu-Oracle
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
1 - 3
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Nov 18 2009
Added on Oct 8 2009
3 comments
2,782 views