psalvini@maytag.waterloo.edu (Paul Salvini) (01/10/91)
Help! I (we) are trying to tune an application that uses hierarchy structures throughout the system. The "connect by/start" phrases take too long! I have used tkprof and explain and the info seems plausible (ie no full table scans - only index range scans at worst). Does anybody out there have hints/tricks/suggestions on how to tune this beast! I know what you're saying - "Its just Oracle!" but that won't help fix this problem/bottleneck. The results of the tkprof indicate excessive "Fetch" times with acceptable "Parse" and "Execute" times. This hierarchy structure gets called throughout forms/rpts/Pro*C and is not acceptable as is. Something must be done to change this. Any ideas greatly appreciated. P.S. A call to ORACLE is "in the queue". -- =============================================================================== Paul A. Salvini (519) 884-1973 x2913 Wilfrid Laurier University ** INTERNET: psalvini@maytag.UWaterloo.ca Department of Computing Services
psalvini@maytag.waterloo.edu (Paul Salvini) (01/11/91)
In article <1991Jan10.033617.16657@maytag.waterloo.edu> psalvini@maytag.waterloo.edu (Paul Salvini) writes: >Help! > I (we) are trying to tune an application that uses hierarchy structures >throughout the system. The "connect by/start" phrases take too long! >I have used tkprof and explain and the info seems plausible (ie no >full table scans - only index range scans at worst). > >Does anybody out there have hints/tricks/suggestions on how to >tune this beast! I know what you're saying - "Its just Oracle!" but >that won't help fix this problem/bottleneck. > >The results of the tkprof indicate excessive "Fetch" times with acceptable >"Parse" and "Execute" times. This hierarchy structure gets called throughout >forms/rpts/Pro*C and is not acceptable as is. Something must be done >to change this. Any ideas greatly appreciated. > Well Netters - I have stumbled onto the answer to MY immediate problem... A big thing that I discovered this morning, and should have realized, is that the key CAN/SHOULD NOT have functions placed on them. BIG BAD MISTAKE! eg: connect ... start with nvl(key2,0) = 999 and key1 = 'K'; (syntax may be wrong but you get the idea) First of all, I didn't write the code. But, it is obvious (to me) that key2 could not be null in this instance (was defined as 'NOT NULL') and removing this code brought the desired performance back to acceptable levels. The EXPLAIN indicated the index was being used -- but ONLY ON key1!!!! (It doesn't show that part! arg!) Other biggies are trying to stay clear of 'key is null', again reducing the effectivness of indexing. So, as you can see - the problems - for me anyways - was at the programming levels. But I will look into the other suggestions...It never hurts to do the best! Again...Thanks ( special thanks to Ernest Rubi @ Landmark Graphics for pointing out other more 'system' oriented tuning tricks) -- =============================================================================== Paul A. Salvini (519) 884-1973 x2913 Wilfrid Laurier University ** INTERNET: psalvini@maytag.UWaterloo.ca Department of Computing Services