Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2004
    Posts
    60

    Unanswered: Query Performance Issue - ArcSDE

    Hi

    We are using ArcSDE views for some of our tables, and most of our queries are using views having joins more than 5 views and/or some normal tables. Each ArcSDE Multi-versioned uses 3 tables Base Table, A & D tables. (Native SQL Access)


    On Test server for which we have full access, the queries are running ok and sometimes the sames queries are taking ages (sometimes more that 30 mins) to execute. We usually check the top queries and update stats and run SQL Tunning Advisor and implement the new plan. After that the query runs ok (Most of the times).

    But on Production server, ever after updating stats the performance of the queries are too bad. We feel that oracle is not choosing the right execution plan for the queries in question.

    We planning to increase the hidden parameter optimizer_search_limit to 8 or 10.

    I refered the following URL.
    Oracle optimizer search limit & permutations tips

    Any suggestion from the Oracle gurus on this will be very helpfull.

    Regards

  2. #2
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    #1: I would say you should find out what the bottle neck is before changing anything.
    Enable SQL trace and use TKPROF to analyze the results.

    Also consult your AWR reports to check the waits and top SQL.

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    do the SQL statements utilize bind variables
    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.

  4. #4
    Join Date
    Oct 2004
    Posts
    60
    Yes we are using bind variables.

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    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.

  6. #6
    Join Date
    Oct 2004
    Posts
    60
    Thanks for the link.

    I am planning to set _optim_peek_user_binds to false and do a test run for a week or so on the test server to see the consistency in execution of queries.

Posting Permissions

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