Results 1 to 13 of 13
  1. #1
    Join Date
    Apr 2004
    Posts
    35

    Unanswered: query with "in" and 13000 bind variables

    the query is from a single table which is having around 30 million records.
    it has around 13000 values(bind variables) in the "in" clause.
    I cannot modify the query as it is being generated by middle layer in a GIS
    application.

    My problem is :
    It is working fine in oracle 8.1.5 but it is not working in 9.2.0 why?

    any reply with genuine solution is appreciated..

    skg

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Pity you can't change the application: this SQL sucks!

    But what do you mean by "not working"? Is there some kind of error message?

  3. #3
    Join Date
    Apr 2004
    Posts
    35

    Red face the middle layer is arcSDE 8.0.2 and client is a VC++ tool

    the error is given by the middle layer (error no 51 - this is a unknown error by
    the middle error).
    the VC++ tool reads/writes the data via middle layer(ML) from oracle database
    (which is maintained by the middle layer for sptial data scenario)

    I am getting the error while the VC is reading data from the database via ML.
    i was able to know that this query is not working i.e the middle layer is
    not able to pick or internally do what it wants through the debug in VC++.

    I want to know that how to find whether this query is executed by oracle or not ( because i was not able to find this query in views v$sqlarea,v$db_object_cache,v$sql_text)

    the error given by VC++ debug was DBMS underline error.

    the query is executed in the same scenario in 8i but not in 9i.

    please reply - thanks

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    1) Enable SQL_TRACE & see what shows up in the trace file.
    2) Enable AUDIT & see what shows up in SYS.AUD$ view
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Mar 2004
    Location
    Colorado
    Posts
    49

    Can you try the operation with a selected set

    Are the 13000 bind varables for objectid? Can you do the operation as a selected set This will have ArcSDE join to the SDE_Logfile_data table (be sure to load the table and have stat on the table to tell the optimizer it is a large table) If the stats say the logfile_data table is empty it seams to favor cartesian joins (very slow with 13000 and 30M)

  6. #6
    Join Date
    Apr 2004
    Posts
    35

    Thumbs down they are from single table

    thanks for reply


    all the 13000 bind variables are searching single table for primary key column.
    i have no info how it is working fine in oracle but not in 9i?

    skg

  7. #7
    Join Date
    Mar 2004
    Location
    Colorado
    Posts
    49
    Try setting
    _push_join_union_view=false

    I have seen many times where 9.2 choses to push the union which will cause the dedicated server process to crash in ArcSDE, where it did not in 8i

  8. #8
    Join Date
    Apr 2004
    Posts
    35

    Arrow problem not solved.

    The problem of -51 error in arcsde still persist.
    any other setting or parameters

    thanks
    skg

  9. #9
    Join Date
    Mar 2004
    Location
    Colorado
    Posts
    49
    can you send me a 10046 trace (level 12) of the process. Start up ArcMap and get to the step just before the error occurs. Start tracing (e-mail me if you need instructions on setting this up.) Then with trace on generate the error and once you get the error kill the ArcMap application from taskmanager. Zip and and send me the raw trace file (I may need to look at some of the bind varables and tkprof strips them out.)

  10. #10
    Join Date
    Apr 2004
    Posts
    35

    here i am attaching the trace file

    I have attached the raw trace files.

    please look into it and if any findning please let me know.
    this is not of 13000 bind variables but other file where i am getting -51 error
    (ARCsde error)
    thank you

    skg
    Attached Files Attached Files

  11. #11
    Join Date
    Mar 2004
    Location
    Colorado
    Posts
    49
    Is this really SDE 3.2? I am not sure ESRI has done compatablity testing between it and 9i. But it appears the problem sentance is
    select UNIQID, IMPORT, OWNSHP, GEOPRI, MINRGE, MAXRGE, INFORM, LANINF,
    NINFOM, LANNIF, ALTINF, LANALI, SOURID, CNRQID, DATEND, DATSTA,
    PEREND, PERSTA, LNKCNT
    from
    S57_DB_ADMIN.CTNARE where (( (UNIQID=:"SYS_B_0") OR (UNIQID=:"SYS_B_1") )
    AND (CTNARE.LASTOP <> :"SYS_B_2"))

    where the unqid 1 and 2 are 1351654 and 1390648 inspect these two objects and see if there is anything 'bad' about them. If I remember correctly in 3.2 when you get a corrupted object any time you hit them the system would report an error and we would have to delete the objects and recreate them. So the reason is works on 8i and not 9i could be an object got corrupted on the 9i database. Corruption was not uncommon in 3.2 but it could also be a problem with 9i that may keep comming back. Have you looked at upgrading from SDE 3.2 to ArcSDE 8.3?

    Oh forget about the push_join_union_view that is not applicable in 3.2.

    Mark

  12. #12
    Join Date
    Apr 2004
    Posts
    35

    Exclamation compatibility?

    we are using ArcSDE 8.0.2 and oracle 9i and cell are of 7CC.
    this is for your information and whether they are compatible.

    skg

  13. #13
    Join Date
    Mar 2004
    Location
    Colorado
    Posts
    49
    ArcSDE 8.0 was not the most stable platform. ArcSDE 8.1 was a marked improvement over 8.0 not only for performance but for stability. The upgrade from 8.0 to 8.1 is very painful we had many issues with it for many of our customers but from that point on (8.3 and 8.3) were very straight forward. (we have yet to finalize any customer on to ArcSDE 9.0 yet but it will start soon, so we will see how this upgrade goes)

    The problem you do have is 8.0 is definatly NOT certified for Oracle 9i. and there were no releases for 9i at that level for any OS. The ESRI site has the list of supported dbms by OS at their web site.

    http://support.esri.com/index.cfm?fa...PID=19&PVID=86

    Because ESRI builds a different Executable per database I have never tried to run the 8i exe against 9i so I do not know what the implications are.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •