IBM Mainframe Forum

by **maragatham** » Tue Nov 09, 2010 3:55 pm

Hi,

My query takes a longtime and I tried to view the plan_table to check ffor the accessstype. And please find the result of my query below.

Select all

ACCESSTYPE MATCHCOLS INDEXONLY ACCESSNAME
+--------- --------+ -------+- -+--------
   0 N
I       1 N B
M       0 N
MX    1 Y C3
MX    1 Y C3
I       0 N E1
I       1 N F1
I       2 N F5
MI    0 N
MU    0 N
MX    1 Y C3
MX    1 Y C3
MU    0 N

"Code'd"

My query is

1) There are few tables where MATCHCOLS = 0. Can this decrease performance?
2) If not please let me know if there is any other area to improve this by looking at the plan_table results.
3) Is it possible to find the exact column of the table wich goes for scanning from the plan_table ?

Thanks,
M

by **GuyC** » Tue Nov 09, 2010 4:17 pm

your extract of the plan table doesn't say much.

Select all

select
l.PROGNAME
, l.version
, l.bind_time
, l."QUERYNO"
, strip(char(l.qblockno)) || ',' || strip(char( l.planno)) || ',' || strip(char(l.mixopseq)) as StepNo

,case when l.creator > '' then strip(l.creator) !! '.' !! strip(l.tname) else '' end as "Table"
,case l.method when 0 then ' '
            when 1 then ' Nested Loop '
         when 2 then ' Merge Scan '
         when 3 then ' Sort '
         when 4 then ' Hybrid Join '
         else ' ' end !!
case l.accesstype when 'I' then strip(l.accessname) !!
            case l.matchcols when 0 then 'Non-matching'
               else '(MatchCols=' !! strip(char(l.matchcols)) !! ')' end
            when 'I1' then 'One-fetch IX ' !! strip(l.accessname)
            when 'R' then 'Tablespace scan ' !! strip(l.tname)
            when 'N' then 'IN-list ' !! strip(l.accessname) !! '(MatchCols=' !! strip(char(l.matchcols)) !! ')'
            when 'M' then 'Multiple Ix (+ by MU,MI,MX) ' !! strip(l.accessname)
         when 'MU' then 'Union of RIDs due to OR ' !! strip(l.accessname)
         when 'MI' then 'Intersection RIDs due to AND ' !! strip(l.accessname)
         when 'MX' then 'RIDs collection ' !! strip(l.accessname)
         when 'P' then 'Dynamic Index anding ' !! strip(l.accessname)
         when 'T' then 'Sparse Index ' !! strip(l.accessname)
         else ' ' end !!
case l.prefetch when 'L' then ' List prefetch'
         when 'S' then ' Seq. prefetch'
else ' ' end
as step
from plan_table l
where collid = &collid
and progname = &progname
-- and queryno = &queryno
order by version,queryno,qblockno,planno,mixopseq,bind_time

by **maragatham** » Tue Nov 09, 2010 4:44 pm

Thanks for your query. The results are as below:

Select all

STEPNO STEP
+------- ---------+---------+---------+---------+---------
1,1,0 MULTIPLE IX (+ BY MU,MI,MX) LIST PREFETCH
1,1,1    RIDS COLLECTION CHREMPL3
1,1,2    RIDS COLLECTION CHREMPL3 SEQ. PREFETCH
1,1,3    UNION OF RIDS DUE TO OR
1,1,4    RIDS COLLECTION CHREMPL3
1,1,5    RIDS COLLECTION CHREMPL3 SEQ. PREFETCH
1,1,6    UNION OF RIDS DUE TO OR
1,1,7    INTERSECTION RIDS DUE TO AND
1,2,0    NESTED LOOP FC5(MATCHCOLS=2)
1,3,0    NESTED LOOP FCSTATU1(MATCHCOLS=1)
1,4,0    NESTED LOOP EMPLOYE1NON-MATCHING SEQ. PREFETCH
1,5,0    NESTED LOOP BRINDV3(MATCHCOLS=1)
1,6,0    SORT

But please can you guide me further on how to infer information with the above results?

by **GuyC** » Tue Nov 09, 2010 5:42 pm

it's hard to say without where-clause ,index definitions, cardinalities
probably :
- your criteria on table with index chrempl3 are not optimal.
Multiple-IX paths are usually bad performance

- your join condition with employe1 is not good, and/or not supported by an index
Nested loop non matching i also bad performance

wether the other nested loops are good depends if matchcols is close to keycols

by **maragatham** » Tue Nov 09, 2010 6:19 pm

Thanks again.
Please can you let me know, if it is possible to find the exact column of the table which goes for scanning from the plan_table?

i,e when the plan_table returns the below:

TNAME ACCESSTYPE
+---------+---------+---------

HR_EMPL M
HR_EMPL MX
HR_EMPL MX
EMPLOYEE I
FC_STATUS I
FC I
HR_EMPL MI
HR_EMPL MU
HR_EMPL MX
HR_EMPL MX
HR_EMPL MU

how to identify the column of HR_EMPL which goes for M \MI \MU?

by **enrico-sorichetti** » Tue Nov 09, 2010 6:25 pm

please learn to use the bbcode tags, it will make easier for everybody to understand the data You post, thanks
like

Select all

text tagged by clicking the Code box after having selected it

in Your edit window it should look like
[ c o d e ]text tagged by clicking the Code box after having selected it[ / c o d e ]
( blanks added inside the [] to show the <tags>)

by **GuyC** » Tue Nov 09, 2010 7:31 pm

let 's see :
if something is using index CHREMPL3, matchcols=1 and index CHREMPL3 has columns(ColA,ColB,ColC) which columns do you think are used ?

So that's one way to find out.

The best one is to use some tool like OSC to visual explain your query.

The geeky way is to have a full set of explain tables (not just plan_table) and start looking in dsn_filter_table and dsn_predicat_table.
next query will give you more lines, listing all predicats used in that "step', and how (MATCHING,SCREENING,STAGE1,STAGE2)

Select all

select
l.PROGNAME
, l.version
, l.bind_time
, l."QUERYNO"
, strip(char(l.qblockno)) || ',' || strip(char( l.planno)) || ',' || strip(char(l.mixopseq)) as StepNo

,case when l.creator > '' then strip(l.creator) !! '.' !! strip(l.tname) else '' end as "Table"
,case l.method when 0 then ' '
            when 1 then ' Nested Loop '
         when 2 then ' Merge Scan '
         when 3 then ' Sort '
         when 4 then ' Hybrid Join '
         else ' ' end !!
case l.accesstype when 'I' then strip(l.accessname) !!
            case l.matchcols when 0 then ' Non-matching'
               else '(MatchCols=' !! strip(char(l.matchcols)) !! ')' end
            when 'I1' then 'One-fetch IX ' !! strip(l.accessname)
            when 'R' then 'Tablespace scan ' !! strip(l.tname)
            when 'N' then 'IN-list ' !! strip(l.accessname) !! '(MatchCols=' !! strip(char(l.matchcols)) !! ')'
            when 'M' then 'Multiple Ix (+ by MU,MI,MX) '
         when 'MU' then 'Union of RIDs due to OR '
         when 'MI' then 'Intersection RIDs due to AND '
         when 'MX' then 'RIDs collection ' !! strip(l.accessname) !! '(MatchCols=' !! strip(char(l.matchcols)) !! ')'
         when 'P' then 'Dynamic Index anding ' !! strip(l.accessname)
         when 'T' then 'Sparse Index ' !! strip(l.accessname)
         else ' ' end !!
case l.prefetch when 'L' then ' List prefetch'
         when 'S' then ' Seq. prefetch'
else ' ' end
as step
, f.stage
, p.text
, char(decimal (p.filter_factor * 100,9,6)) !! '%'
from plan_table l
left join DSN_FILTER_TABLE F
on F.collid = l.collid
and f.progname =l.progname
and f.queryno = l.queryno
and f.qblockno = l.qblockno
and f.planno = l.planno
and f.mixopseqno = l.mixopseq
and f.explain_time = l.bind_time
left join DSN_PREDICAT_TABLE P
on P.progname = F.progname
and p.explain_time = f.explain_time
and p.queryno = f.queryno
and p.qblockno = f.qblockno
and p.predno = f.predno
where l.collid = &collid
and l.progname = &progname
and l.queryno = &queryno

order by l.version,l.queryno,l.qblockno,l.planno,l.mixopseq,l.bind_time,f.stage

by **GuyC** » Tue Nov 09, 2010 7:35 pm

actually it makes more sense to

Select all

order by l.version,l.queryno,l.bind_time,l.qblockno,l.planno,l.mixopseq,f.stage

IBM Mainframe Forum

Performance Tuning and AccessType

Performance Tuning and AccessType

Re: Performance Tuning and AccessType

Re: Performance Tuning and AccessType

Re: Performance Tuning and AccessType

Re: Performance Tuning and AccessType

Re: Performance Tuning and AccessType

Re: Performance Tuning and AccessType

Re: Performance Tuning and AccessType