oracle database expert performance tuning buffer pools keep recycle sqlplus query TOAD system trigger plsql programming
Edward Stoever is your
DATABASE-EXPERT
Computer solutions for businesses of all sizes!
(818) 528-4111
 Home  Résumé  Articles  Portfolio  Contact  YouTube 
ORACLE - PERFORMANCE TUNING
IMPROVE THE PERFORMANCE OF A VIEW
By Edward Stoever
I ran into the problem of an Oracle view returning results too slowly, well... so slowly that people gave up on it entirely. To make matters worse, the query was taking place from within a form. To solve this, I traced a session which opened the form and ran the query. The interesting part of the trace output is shown here:
SELECT SPVPERS_CHANGE_IND,SPVPERS_ID,SPVPERS_SSN,SPVPERS_SEX,
  SPVPERS_BIRTH_MON,SPVPERS_BIRTH_DAY,SPVPERS_BIRTH_YEAR,SPVPERS_ATYP_CODE,
  SPVPERS_STREET_LINE1,SPVPERS_STREET_LINE2,SPVPERS_CITY,SPVPERS_STAT_CODE,
  SPVPERS_ZIP,SPVPERS_LAST_NAME,SPVPERS_FIRST_NAME,SPVPERS_MI,
  SPVPERS_PHONE_AREA,SPVPERS_PHONE_NUMBER,SPVPERS_PIDM 
FROM
 SPVPERS WHERE (SPVPERS_SSN=:1) order by spvpers_last_name


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      3.20     123.30       7975      25363          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      3.20     123.30       7975      25363          0           0

Misses in library cache during parse: 0
Optimizer goal: RULE
Parsing user id: 423  

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  NESTED LOOPS OUTER 
      0   NESTED LOOPS OUTER 
      0    FILTER  
   9776     NESTED LOOPS OUTER 
   9776      TABLE ACCESS BY INDEX ROWID OBJ#(26764) 
   9776       INDEX FULL SCAN OBJ#(41433) (object id 41433)
   6066      TABLE ACCESS BY INDEX ROWID OBJ#(26753) 
   6066       INDEX UNIQUE SCAN OBJ#(26754) (object id 26754)
      0    TABLE ACCESS BY INDEX ROWID OBJ#(26755) 
      0     INDEX RANGE SCAN OBJ#(41427) (object id 41427)
      0   TABLE ACCESS BY INDEX ROWID OBJ#(26769) 
      0    INDEX RANGE SCAN OBJ#(41438) (object id 41438)
123 seconds is actually the time it took before I killed the session.... this could have taken much longer. People were waiting for more than 15 minutes before giving up! (Why don't more people call me for this stuff? Is the database working that well?)

To solve it, I did a little investigating. The query is looking at the SPVPERS view. That view is a join of 4 tables. The where clause should key in on an index. I get the definition of the view and copy the query and run it. Ok, not too bad. Now I add in the where clause that was slowing it down (in this case it is WHERE SPBPERS_SSN = '012345678'). Looking at the explain plan, I can see that the query does NOT use an index even though there is one available on that column. So, I change the query by adding a hint that it looks at that index, and then I run it again. Wow! Much faster! So, I simply add that hint into the view definition and call the people who are frustrated and ask them to try it again.

Here is the new view definition:
CREATE OR REPLACE FORCE VIEW BANINST1.SPVPERS
(SPVPERS_PIDM, SPVPERS_ID, SPVPERS_LAST_NAME, 
 SPVPERS_FIRST_NAME, SPVPERS_MI, 
 SPVPERS_CHANGE_IND, SPVPERS_ATYP_CODE, SPVPERS_STREET_LINE1, 
 SPVPERS_STREET_LINE2, SPVPERS_STREET_LINE3, 
 SPVPERS_CITY, SPVPERS_STAT_CODE, SPVPERS_ZIP, 
 SPVPERS_NATN_CODE, SPVPERS_PHONE_AREA, 
 SPVPERS_PHONE_NUMBER, SPVPERS_PHONE_EXT, 
 SPVPERS_SSN, SPVPERS_BIRTH_MON, SPVPERS_BIRTH_DAY, 
 SPVPERS_BIRTH_YEAR, SPVPERS_SEX)
AS 
 /* HINT ADDED BY EDWARD STOEVER on AUG-31-2004 */
SELECT
  /*+ INDEX (SPBPERS_SSN_INDEX) */
 SPRIDEN_PIDM,
 SPRIDEN_ID,
 SPRIDEN_LAST_NAME,
 SPRIDEN_FIRST_NAME,
 SPRIDEN_MI,
 SPRIDEN_CHANGE_IND,
 SPRADDR_ATYP_CODE,
 SPRADDR_STREET_LINE1,
 SPRADDR_STREET_LINE2,
 SPRADDR_STREET_LINE3,
 SPRADDR_CITY,
 SPRADDR_STAT_CODE,
 SPRADDR_ZIP,
 SPRADDR_NATN_CODE,
 SPRTELE_PHONE_AREA,
 SPRTELE_PHONE_NUMBER,
 SPRTELE_PHONE_EXT,
 SPBPERS_SSN,
 TO_CHAR(SPBPERS_BIRTH_DATE,'MM'),
 TO_CHAR(SPBPERS_BIRTH_DATE,'DD'),
 TO_CHAR(SPBPERS_BIRTH_DATE,'YYYY'),
 SPBPERS_SEX
FROM SPRTELE,
     SPRADDR,
     SPBPERS,
     SPRIDEN
WHERE SPRADDR_PIDM(+) = SPRIDEN_PIDM
  AND SPBPERS_PIDM(+) = SPRIDEN_PIDM
  AND SPRTELE_PIDM(+) = SPRADDR_PIDM
  AND SPRTELE_ATYP_CODE(+) = SPRADDR_ATYP_CODE
  AND SPRTELE_SEQNO(+) = SPRADDR_SEQNO;



©2017 Edward Stoever