URI query issues

From: oafyuf <oafyuf_at_hotmail.com>
Date: 1 Jul 2004 03:40:55 -0700
Message-ID: <ea81924d.0407010240.3fa392b7@posting.google.com>

Hi all,

I am using URI XPath queries on 9i. I do not have permission on the database or database server to create any Stored Procedures, Servlets or anything else that I could possibly use to help me. I have firewall access on ports 80 and 8080 ONLY. I am connecting on a 100Mbit LAN with very little extra traffic. The Oracle server is on a Sun box which is a dev replica of a high-performance production server.

I have a couple issues to resolve:

The response speed is currently extremely slow. I am connecting and retrieving data via a C# .NET component using:

HttpWebRequest req = (HttpWebRequest)WebRequest.Create(strURIQuery); req.Method = "GET";
req.Credentials = new NetworkCredential(strUser, strPass); // Oracle log-in

// Get the stream from the response
StreamReader stream = new
StreamReader(req.GetResponse().GetResponseStream());

However, the timings are the same when I simply paste the query into a browser and get a response, even without applying a stylesheet transformation.

I have been fishing the internet/NGs for some optimization hints but it *seems* like almost no-one is using this Oracle feature. Does anyone on this NG have experience with speeding up URI queries?

Any query, regardless of complexity, seems to take between 1.5 and 3 seconds to return with XML. I am doing simple select statements on a single table with multiple "or" statements, but the classic test:

http://myservername:8080/oradb/SCOTT/EMP/ROW[EMPNO=7369]/ENAME

... is just as slow!

2. There is a limit to the number of characters I can use in a query (it appears to be 420 chars inside the [] predicate). Slightly over this limit and I get:

<ERROR>
ORA-00600: internal error code, arguments: [17114], [0x2703F690], [], [], [], [], [], []
</ERROR>

Way over this limit and Oracle can't even return an error message! I get a 500 back from Apache.

Does anyone know if this size limit is configurable? Alternatively, is there a way of using a more concise XPath syntax to perform a query with multiple "or" statements?. The queries I am doing are similar to the one below, only with even more "or" statements:

http://myservername:8080/oradb/SCOTT/RLR/ROW[NGC='SW37331083' or

NGC='SW37332471' or NGC='SW37332591' or NGC='SW37332965' or
NGC='SW37333078' or NGC='SW37333395' or NGC='SW37335398' or
NGC='SW37340233' or NGC='SW37340351' or NGC='SW37340811' or
NGC='SW37341521' or NGC='SW37341909' or NGC='SW37342828' or
NGC='SW37343418' or NGC='SW37343605' or NGC='SW37343728' or
NGC='SW37344528' or NGC='SW37344707']?rowsettag=Data



*****************************************************************

Please note that I do not have access to a newsserver, so I'm using Google Groups to post here. That means it will take around 24 hours for me to reply!

TIA,
Oafyuf Received on Thu Jul 01 2004 - 05:40:55 CDT