Kbase P61446: Why are there two select statements generated when a find st
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
06/01/2004 |
|
Status: Unverified
GOAL:
Why are there two select statements generated when a find statement is run against Oracle dataserver?
GOAL:
When are two select statements generated when running find against Oracle dataserver?
GOAL:
How to interpret dataserv.lg file for a find statement?
FACT(s) (Environment):
Oracle DataServer
FIX:
The two select's are generated only when running find against a new table
or after 20 find statements against the same table. If you execute find against
the same table multiple times (<20) during one session, there will be no extra
select's.
The first select printed in dataserv.lg is also used for schema check, the second select fetches 20 records into cache so the next find prev or find next will be able to walk through the cache and retrieve record from there.
You will also see 2 select's when you first querying one table with a non-find
statement. In this case, the first select is only used for schema check, it is
only parsed but not executed. The second select only fetches 1 row. This is why
find performs worse since it has extra overhead of fetching 20 rows instead of
none as in non-find statement.
See following example, the example is extract of dataserv.lg with 9.1D running against Oracle 8.1.7.
dataserv.lg for find
09:13:05 OCI call oparse <2> sqlcrc = 43624
09:13:05 SELECT /*+ INDEX(T0 CUSTOMER##PROGRESS_RECID) */ CUST_NUM,U##COUN
09:13:05 TRY,COUNTRY,U##NAME,NAME,ADDRESS,ADDRESS2,STATE,U##POSTAL_CODE,PO
09:13:05 STAL_CODE,CONTACT,PHONE,U##SALES_REP,SALES_REP,CREDIT_LIMIT,BALAN
09:13:05 CE,TERMS,DISCOUNT,COMMENTS,progress_recid,CITY FROM SPORTS.CUSTOM
09:13:05 ER T0 WHERE PROGRESS_RECID = :rid ----------> curor 2 schema check
and parse SQL
09:13:05 OCI call omru <2>
09:13:05 OCI call oopen <3> cc = 2
09:13:05 OCI call oparse <3> sqlcrc = 56282
09:13:05 SELECT /*+ INDEX_ASC(T0 CUSTOMER##CUST_NUM) */ PROGRESS_RECID FRO
09:13:05 M SPORTS.CUSTOMER T0 order by CUST_NUM
09:13:05 OCI call oexfet <3> num = 20 ---------------> cursor 3 fetching 20
rows
09:13:05 OCI call omru <2>
09:13:05 OCI call oexfet <2> num = 1 ----> one selected row plugged in back to
cursor 1's select
09:13:05 OCI call omru <3>
09:13:21
09:13:21 Cursor <3> Rows processed 20 (last execution) ---------> 20 rows
fetched, cursor 3
09:13:21 OCI call oclose <3> cc = 3
09:13:21
09:13:21 Cursor <2> Rows processed 1 (last execution)
09:13:21 OCI call oclose <2> cc = 2
dataserv.lg for "for first" (non-find)
10:14:47 OCI call oparse <2> sqlcrc = 43624
10:14:32 ALTER SESSION SET NLS_DATE_FORMAT = 'YYYYMMDD'
10:14:32 OCI call oexec <1>
10:14:32 -Dsrv qt_debug: 147 (0x93 Extended) (6489)
10:14:32 Cursor limit: 84 (-c setting) (6490)
10:14:47 OCI call oopen <2> cc = 1
10:14:47 OCI call oparse <2> sqlcrc = 43624
10:14:47 SELECT /*+ INDEX(T0 CUSTOMER##PROGRESS_RECID) */ CUST_NUM,U##COUN
10:14:47 TRY,COUNTRY,U##NAME,NAME,ADDRESS,ADDRESS2,STATE,U##POSTAL_CODE,PO
10:14:47 STAL_CODE,CONTACT,PHONE,U##SALES_REP,SALES_REP,CREDIT_LIMIT,BALAN
10:14:47 CE,TERMS,DISCOUNT,COMMENTS,progress_recid,CITY FROM SPORTS.CUSTOM
10:14:47 ER T0 WHERE PROGRESS_RECID = :rid -----------------> schema check
and parse only, cursor 2
10:14:47 OCI call omru <2>
10:14:47 OCI call oopen <3> cc = 2
10:14:47 OCI call oparse <3> sqlcrc = 33218
10:14:47 SELECT /*+ INDEX_ASC(T0 CUSTOMER##CUST_NUM) */ PROGRESS_RECID un
10:14:47 ique_id_0,CUST_NUM,U##COUNTRY,COUNTRY,U##NAME,NAME,ADDRESS,ADDRES
10:14:47 S2,STATE,U##POSTAL_CODE,POSTAL_CODE,CONTACT,PHONE,U##SALES_REP,SA
10:14:47 LES_REP,CREDIT_LIMIT,BALANCE,TERMS,DISCOUNT,COMMENTS,PROGRESS_REC
10:14:47 ID,CITY FROM SPORTS.CUSTOMER T0 ORDER BY CUST_NUM
10:14:47 OCI call oexfet <3> num = 1 ---------------------> only 1 row fetched
from cursor 3
10:14:47 OCI call omru <3>
10:14:55
10:14:55 Cursor <3> Rows processed 1 (last execution)
----------------------> only 1 row fetched
10:14:55 Number of array fetches 1
10:14:55 Number of rows fetched 1
10:14:55 Number of array rows 1
10:14:55 Number of array columns 22
10:14:55 . Number of tables 1
10:14:55 Space for one row 573
10:14:55 Requested cache size 573
10:14:55 Actual cache size used 573
10:14:55 OCI call oclose <3> cc = 3
10:14:55
10:14:55 Cursor <2> Rows processed 0 (last execution)
10:14:55 OCI call oclose <2> cc = 2.