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