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

#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.

do the SQL statements utilize bind variables

Yes we are using bind variables.

Let me google that for you

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.