Forms Tuning: Index Problem
Date: 29 Jan 1993 15:28:24 GMT
Message-ID: <1kbieoINNc96_at_nz12.rz.uni-karlsruhe.de>
Hi!
I'm looking for some help in a bit of performance tuning on a Forms 3.0 form. I think the problem lies in my indices.
Background: Oracle v. 6.0.34.2.1, SQL*Plus v. 3.0.11.1.1, PL/SQL v. 1.0.34.0.1
SQL*Forms v. 3.0.16.8.1, and operation system Unix on a HP/UX 9000 Series 800.
My form has as its base table a view:
create view person_view as
select u.user_id, u.user_pnr,p.lastname,p.firstname,p.title,p.telefon,
p.str,p.city,p.institut_name,p.room,i.institut_name_1
from institute i, person p, userid u
where u.user_pnr = p.pers_nr(+) and
u.inst_nr = i.inst_nr
(The reason for the outer join is that a person may have more than one userid.)
The tables have the following indices:
institute person userid --------- ------ ------ i_inst_nr (unique) i_pers_nr (unique) i_user_id (unique) i_city (non-unique) i_nachname (non-unique) i_u_inst_nr (non-unique) i_user_pnr (non-unique)
THE PROBLEM IS:
When I do a query on user_id, I get my response very quickly, rightly so. But when I do a query on lastname, response time is very slow. From TKPROF, I can see that my lastname index isn't even used! WHY NOT?
Could that outer join be causing problems?
WHAT I'VE TRIED:
I've already reordered the list of the tables in the FROM clause of my view (was: userid u, person p, instit i and is now: see above), which helped me quite a bit (about 3 seconds).
I then tried reversing the order of my predicates (i.e. p.pers_nr(+)= u.user_pnr and i.inst_nr=u.inst_nr). That didn't change anything.
I've studied the handbooks on TKPROF and EXPLAIN PLAN, index tuning, etc.
So, I created several new concatenated indices (testing, then dropping
one at a time) and traced them using TKPROF. I've tried the following
indices:
on person(lastname,pers_nr)
on person(pers_nr,lastname)
unique on person(lastname,pers_nr)
on userid(user_id,inst_nr,user_pnr)
The conclusion is that none of them were even used!
Does anyone have any suggestions as to what else I could try?
I hope this is detailed enough to make it understandable. If not, please let me know and I'll try to explain better. I can even provide some trace file output, if necessary.
Thanks in advance for your patience and valuable help!!
Karen Schillinger
Please send responses per Email to: rz34_at_hp850.rz.uni-karlsruhe.de
Received on Fri Jan 29 1993 - 16:28:24 CET