Kbase P78637: Find next statement performs slow against Oracle
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
9/19/2008 |
|
Status: Verified
SYMPTOM(s):
Find next statement performs slow against Oracle
Find next does not do a full table scan however it still reads almost whole table
The query is traced and tkprof-ed in Oracle to monitor execute plan
Typical execute plan from tkprof using account table
FIND FIRST travel.account WHERE RECID(travel.account) = 6597
NO-LOCK NO-ERROR.
FIND NEXT travel.account WHERE USE-INDEX label-i
NO-LOCK NO-ERROR.
TKPROF: Release 8.1.6.0.0 - Production on Fri Apr 23 10:44:37 2004
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Trace file: gtm82d_ora_2131.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
ALTER SESSION SET SQL_TRACE TRUE
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.04 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.04 0 0 0 0
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 23 (TM)
********************************************************************************
SELECT /*+ INDEX(T0 ACCOUNT##PROGRESS_RECID) */ U##ACCLABEL,ACCLABEL,ACC1,
ACC2,ACC3,ACC4,ACC5,ACC6,ACC7,ACC8,ACC9,ACC10,U##ORG,ORG,U##REF_VALUE,
REF_VALUE,progress_recid
FROM
TM.ACCOUNT T0 WHERE PROGRESS_RECID = :rid
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 8 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 8 0 2
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 23 (TM)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'ACCOUNT'
0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'ACCOUNT##PROGRESS_RECID' (UNIQUE)
********************************************************************************
SELECT /*+ INDEX_ASC(T0 ACCOUNT##LABEL_I) */ PROGRESS_RECID
FROM
TM.ACCOUNT T0 WHERE ((U##ORG = :p1 AND U##ACCLABEL > :p2) OR (U##ORG > :p1))
order by U##ORG, U##ACCLABEL
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.02 0.02 0 0 0 0
Fetch 1 16.48 101.46 6301 6379 407 20
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 16.50 101.48 6301 6379 407 20
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 23 (TM)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 SORT (ORDER BY)
0 CONCATENATION
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'ACCOUNT##LABEL_I'
(UNIQUE)
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'ACCOUNT##LABEL_I'
(UNIQUE)
********************************************************************************
As seen in the tkprof output, the find first is optimized by using a fake where clause, and neither find first or find next generates table scan. However, find next is scaning 6379 blocks of data
FACT(s) (Environment):
Oracle DataServer
Oracle 8.1.7
All Supported Operating Systems
Progress 9.x
OpenEdge 10.x
CAUSE:
The SQL generated for the FIND NEXT is:
SELECT /*+ INDEX_ASC(T0 ACCOUNT##LABEL_I) */ PROGRESS_RECID
FROM
TM.ACCOUNT T0 WHERE ((U##ORG = :p1 AND U##ACCLABEL > :p2) OR
(U##ORG > :p1))
order by U##ORG, U##ACCLABEL
The problem is the (U##ORG > :p1) this causes Oracle to read against almost the whole table.
FIX:
Use Open query to Limit the result set returned. Code example
DEFINE QUERY qry_account FOR account SCROLLING.
OPEN QUERY qry_account FOR EACH account
WHERE account.org = account.org and account.acclabel = account.acclabel /* Fake, depending on components of index */
USE-INDEX label-i NO-LOCK
INDEXED-REPOSITION MAX-ROWS 40. /* Allow to reposition and limit
the number of rows */
REPOSITION qry_account TO RECID 6151.
GET PREV qry_account.
DO WHILE AVAILABLE(account):
DISPLAY STRING(ROWID(account)) FORMAT "X(25)"
STRING(RECID(account)) account.org account.acclabel WITH 10 DOWN.
DOWN 1.
GET NEXT qry_account.
END.