Kbase P78607: Find first statement or any query for Oracle dataserver performs slow
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
9/19/2008 |
|
Status: Verified
SYMPTOM(s):
Find statement for Oracle DataServer performs slow
Any query statement (find, for first, for each) performs slow
The SQL generated by DataServer 99% percent of the time will cause Oracle do a full table scan
The index hints are correctly generated and sent over to Oracle
The Oracle's query plans shows a full table scan
The Oracle's query plan is seen via tracing and tkprof
Example SQL and query plan (against account table):
4GL
FIND FIRST account USE-INDEX label_i NO-LOCK NO-ERROR.
SELECT /*+ INDEX_ASC(T0 ACCOUNT##LABEL_I) */ PROGRESS_RECID
FROM
TM.ACCOUNT T0 order by U##ORG, U##ACCLABEL
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0
Execute 2 0.17 0.18 0 0 7 0
Fetch 1 53.31 287.12 36481 13212 2509 20
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 53.49 287.31 36481 13212 2516 20
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 23 (TM)
Rows Row Source Operation
------- ---------------------------------------------------
20 SORT ORDER BY
1327211 TABLE ACCESS FULL ACCOUNT
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
20 SORT (ORDER BY)
1327211 TABLE ACCESS (FULL) OF 'ACCOUNT'
********************************************************************************
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 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 4 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 4 0 1
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 (BY INDEX ROWID) OF 'ACCOUNT'
0 INDEX (UNIQUE SCAN) OF 'ACCOUNT##PROGRESS_RECID' (UNIQUE)
********************************************************************************
FACT(s) (Environment):
Oracle DataServer
Oracle 8.x
All Supported Operating Systems
Progress 9.x
OpenEdge 10.x
CAUSE:
This is more an Oracle issue than dataserver issue. As hints are correctly generated and passed to Oracle, Oracle is not picking up the hint and use index to look up the record but instead does a full table scan
FIX:
To accomodate Oracle's behavior against Index hint, use a fake where clause in find statement so the SQL generated has a where clause, Oracle then will use index.
Sample 4GL code and SQL code
FIND FIRST account WHERE account.org = account.org
USE-INDEX label-i NO-LOCK NO-ERROR.
TKPROF: Release 8.1.6.0.0 - Production on Wed Apr 28 08:54:24 2004
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Trace file: gtm82d_ora_5548.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.03 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.03 0 0 0 0
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 23 (TM)
********************************************************************************
SELECT /*+ INDEX_ASC(T0 ACCOUNT##LABEL_I) */ PROGRESS_RECID
FROM
TM.ACCOUNT T0 WHERE ((U##ORG = U##ORG)) order by U##ORG, U##ACCLABEL
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 3 0 20
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.01 0.01 0 3 0 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 INDEX GOAL: ANALYZED (FULL SCAN) OF 'ACCOUNT##LABEL_I' (UNIQUE)
********************************************************************************
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 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 4 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 4 0 1
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)