-
Hanna,
We run ESRI and it produces some nasty code specifically adding hints to use unique key indexes and does not let the Oracle optimizer do its thing. I hope with its next release ESRI wakes up and stops adding hints to its code.
About the only thing you can do to make ESRI perform any better is to cache, monitor the main objects and make sure your statistics are up todate as often as possible. Also, you can tweak the giomgr.defs file and the axle file. You'd be suprised at how making an layer use attribute first helps.
-
Thanks Jeff and everyone who participate to help me. Per Jeff's advises, I was able to trace the SQL which misused the index and do a FTS (I guess Oracle pick whatever lowest cost). I added Optimizer_index_cost_adj = 10 and Oracle pick up the index adn resolve my problem.
the next question I have now is "so lower cost doesn't mean your query optimize" specially in this case, could someone please tell me why???
-
I n t e r e s t i n g . . . .
I wouldn't have expected excessive FTS to produce this
Code:
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
CPU time 97 75.94
db file sequential read 1,532,454 28 22.23
db file scattered read 23,868 2 1.61
-
Hannah
I think you are inviting more problem by doing this
I added Optimizer_index_cost_adj = 10 and Oracle pick up the index adn resolve my problem.
Look at your wait events ..they are mostly due to CPU and making use of the indexes .Fix the correct problem :-)
regards
Hrishy
-
I would guess, some of your queries going for index full scan due to hints added by the third party application..
Abhay.
-
thanks all again,
I thought my problem is resolved but I guess not :O(
could you please tell me why adding Optimizer_index_cost_adj = 10 weould create more problem???? does it mean it will affect other queries that already used indexes ????
I thought Jeff has mentioend earlier "high CPU and db file sequential read is the result of misused indexes"
I think you guys were right (as usual), I did run another statspack and the CPU and db file sequential read still very high, the question is how come my map was running much faster, even at the zoom in/out level.
Dapi,
if it's not FTS would produce high CPU, what causing this issue??
please advise
-
db file sequential read means index scans
cpu usage most of times are buffer reads and parse calls
db file sequential read + high cpu usage most of times means Nested Loop Joins when a sort or hash join is preferred
-
I'm not sure I can make up a totally consistant story and remember that we are talking about averages when we look at statspack:
A) If there were a lot of FTS and the Buffer Cache Hit Ratio were 30% then you would be waiting mainly on "db file scattered read" - i.e. multi-block reads from disk.
B) High CPU usage suggests that the data you need is already in buffers. Waits on "db file sequential read" are mainly single-block reads - i.e. NOT by FTS.
I could imagine that there are two types of activity going on:
A) queries using FTS like the one you isolated and "tuned"
B) queries that are thrashing CPU & Buffers using Nested Loops that might be more efficient NOT using the indexes (I think that's what Jeff meant by "misusing" an index). Optimizer_index_cost_adj = 10 could make this worse?
I find it hard to reconcile some mix of A & B that would produce the statspack report you see. :confused:
-
thanks Pando and Dapi,
according to you guys, Optimizer_index_cost_adj = 10 could make thing worse, but how come my map is run much faster now, PLEASE, I am not trying to be ignorant, I just want to understand what's going on and learn.
look into a few queries that were trace, you are right, the executions plan is NESTED LOOP JOINS. ESRI (third party tool) is issued these statements not from our app.
how can we force the app to use sort or hash join???
-
You need to think in terms of TWO questions here:
1) Are my critical business activities (queries & changes) executed with a appropriate speed? If not, why not?
2) How is my server standing up to this?
The answer to (1) is trace & tkprof. The answer to (2) is statspack.
It's not necessarily bad that the CPU is at 50% - if it were at 0.5% you'd kick yourself that you didn't buy a cheaper, slower machine!
You haven't told us under what conditions the ststapack was run: how long? how many users? (I do notice you have one transaction per 500 seconds . . . .)
BTW I'm NOT saying "Optimizer_index_cost_adj = 10" will make things worse, just that it could.
(I remember seeing someone saying that setting this to 10-30 was the only genuine "silver bullet" he'd seen - I'll post a link if ever I find it.***)
P.S.
*** OOOPS - I think it might have been from a DKB article . . . . I did set it to 30 and have had no probs.
