Re: Forms Tuning: Index Problem

In article <1kbieoINNc96_at_nz12.rz.uni-karlsruhe.de> rz34_at_uni-karlsruhe.de writes:

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

It's always a bit hard to tell from this kind of information, but won't you get the multiple userids even if you don't have the outer join? As far as I can tell, the outer join will get you any user-institute pairs that are not linked with people. This doesn't seem to be what you want.

>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)
 

>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?

Yes, but I see another possibility as well.

Try adding an index on u.user_pnr. That way, the database can get the p.pers_nr from the last name, the u.user_pnr from the p.pers_nr using the new index, and then the insitute. The way you have things, no matter what it can't use an index everywhere and the optimizer is deciding to punt using the last name index instead of another index.

>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

-- 
-------------------------------------------------------------------------------
I am not an official Oracle spokesman.  I speak for myself and no one else.