From: mfriedma@uucp (Michael Friedman)
Newsgroups: comp.databases.oracle
Subject: Re: Forms Tuning: Index Problem
Keywords: TKPROF, Explain Plan, Index, Forms
Message-ID: <1993Feb2.034136.26224@oracle.us.oracle.com>
Date: 2 Feb 93 03:41:36 GMT
References: <1kbieoINNc96@nz12.rz.uni-karlsruhe.de>
Sender: usenet@oracle.us.oracle.com (Oracle News Poster)
Organization: Oracle Corporation
Lines: 87
Nntp-Posting-Host: appseq
X-Disclaimer: This message was written by an unauthenticated user
              at Oracle Corporation.  The opinions expressed are those
              of the user and not necessarily those of Oracle.


In article <1kbieoINNc96@nz12.rz.uni-karlsruhe.de> rz34@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@hp850.rz.uni-karlsruhe.de


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