Re: Query optimizer logic (was: About BPCS V6.1.01 MM) -- BPCS-L

Dean,

>>Hmmm.  On the one hand, I beg to disagree.  We were always told that the
>>query optimizer considered only the ten most recently created access paths on
>>the AS/400 prior to starting its own IDX function.  On the other hand, newer
>>versions of OS/400 have improved SQL performance, so the optimization rules
>>may have changed.  Guess I'll be visiting Mr's. Soltice and Sears at the next
>>COMMON conference...

Double hmmm on my side now(!) -  I cannot be sure optimizer did work this way in
the past (i.e. consired only 10 most recent LFs), but for the past several years
while dealing with SQL performance problems in IBM and now in SSA, I have never
noticed in the debug joblog that any of the logicals based on physical file have
not been evaluated other than for reasons I have mentioned last time (shared or
damaged access path). The example bellow is the extract from the debug joblog
with CPI432C message and for one query optimizer access path evaluation I just
run against simple interactive (SELECT) SQL statement. It shows that in total 31
access paths were evaluated which is actually the total number of logicals
dependent on ECL file in question here (as per DSPDBR command info bellow), so
at least now it works as it should, if I may say :-) In addition, first level
text for message CPI432C says as per bellow: 'ALL access paths were considered
for file.....', therefore I would assume this is really what optimizer will do
(i.e. consider really all of them). The only guess I could have with regards to
such comment about 10 most recent logicals, is that although optimizer does
evaluate all access paths available, it does assign to them different weights
based on their creation date, for whatever reason its developers have found
necessary to put in the decision logic algorithm, but this is a pure
speculation. I would like to be with you when discussing this with your friends
at next Common :-)

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CPI432C Information  00    09/28/00   09:57:47   QQQIMPLE       QSYS        3A1F
     QSQOPEN      QSYS
                       Message . . . . :   All access paths were considered for
file ECL.
                       Cause . . . . . :   The OS/400 Query optimizer considered
 all access paths
                        built over member ECL of file ECL in library SGPRDRF.
The list below shows
                        the access paths considered. If file ECL in library
SGPRDRF is a logical
                        file then the access paths specified are actually built
over member ECL of
                        physical file ECL in library SGPRDRF. Following each
access path name in the
                        list is a reason code which explains why the access path
 was not used.  A
                        reason code of 0 indicates that the access path was used
 to implement the
                        query. SGPRDRF/ECLL99 17, SGPRDRF/ECLL48 11,
SGPRDRF/ECLL47 11,
                        SGPRDRF/ECLL44 11, SGPRDRF/ECLL43 11, SGPRDRF/ECLL42 11,
 SGPRDRF/ECLL39 11,
                        SGPRDRF/ECLL38 11, SGPRDRF/ECLL37 11, SGPRDRF/ECLL33 11,
 SGPRDRF/ECLL29 11,
                        SGPRDRF/ECLL28 11, SGPRDRF/ECLL27 11, SGPRDRF/ECLL26 11,
 SGPRDRF/ECLL24 17,
                        SGPRDRF/ECLL23 11, SGPRDRF/ECLL20 11, SGPRDRF/ECLL19 17,
 SGPRDRF/ECLL18 17,
                        SGPRDRF/ECLL17 17, SGPRDRF/ECLL16 17, SGPRDRF/ECLL12 17,
 SGPRDRF/ECLL10 11,
                        SGPRDRF/ECLL09 11, SGPRDRF/ECLL08 11, SGPRDRF/ECLL07 11,
 SGPRDRF/ECLL06 17,
                        SGPRDRF/ECLL04 11, SGPRDRF/ECLL03 11, SGPRDRF/ECLL02 17,
 SGPRDRF/ECLL01 11.
                        The reason codes and their meanings follow: 1 - Access
path was not in a
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   9/28/00              Display Data Base Relations
DSPDBR Command Input
  File  . . . . . . . . . . . . . . . . . . . : FILE       ECL
    Library . . . . . . . . . . . . . . . . . :            *LIBL
  Member  . . . . . . . . . . . . . . . . . . : MBR        *NONE
  Record format . . . . . . . . . . . . . . . : RCDFMT     *NONE
  Output  . . . . . . . . . . . . . . . . . . : OUTPUT     *
Specifications
  Type of file  . . . . . . . . . . . . . . . :            Physical
  File  . . . . . . . . . . . . . . . . . . . :            ECL
    Library . . . . . . . . . . . . . . . . . :            SGPRDRF
    Member  . . . . . . . . . . . . . . . . . :            *NONE
    Record format . . . . . . . . . . . . . . :            *NONE
    Number of dependent files . . . . . . . . :               31
<<<<<-------------!!!!
Files Dependent On Specified File
  Dependent File         Library       Dependency   JREF    Constraint
      ECLL99             SGPRDRF       Data
      ECLL07             SGPRDRF       Data
      ECLL18             SGPRDRF       Data
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

>>You never really addressed the lack of intuitive logical files story on work
>>files.  I understand the _THEORY_ -- work files should have few records and
>>thus should not require optimization.  However, at the few full-client
>>accounts at which I've worked, the records in ECHW have tended to remain
>>above the 10K level.  No programs to clear these out, no logicals to optimize
>>performance.  What's up with _THAT_?

I was only saying 'be cautious' because things are not always that simple, I
hope I don't sound like I am some kind of sworn defender of BPCS who always says
it is a perfect software and there is nothing to improve or change anymore, if
any logical files you create result in improving performance, it is definitely
ok thing to do :-)
Also, I wouldn't be that concerned about files of size 10K or so, this should be
nothing for today's as/400s and should not create a significant performance
overhead. The easiest way to test that is by running DBMON and check for the
time system spent to execute specific SQL statement. Also, another simple way of
checking how useful some logical file would be (assuming that it is the one
picked by optimizer in the actual query implementation), is to delete it and
then see how much time will take to optimizer to build temporary one (message
CPI4321 in the debug joblog) instead.

Regards,
Goran





+---
| This is the BPCS Users Mailing List!
| To submit a new message, send your mail to BPCS-L@midrange.com.
| To subscribe to this list send email to BPCS-L-SUB@midrange.com.
| To unsubscribe from this list send email to BPCS-L-UNSUB@midrange.com.
| Questions should be directed to the list owner: dasmussen@aol.com
+---