Performance Tuning and AccessType



IBM's flagship relational database management system

Performance Tuning and AccessType

Postby 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.

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
maragatham
 
Posts: 9
Joined: Tue Nov 09, 2010 3:08 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Performance Tuning and AccessType

 

Re: Performance Tuning and AccessType

Postby GuyC » Tue Nov 09, 2010 4:17 pm

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

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
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Re: Performance Tuning and AccessType

Postby maragatham » Tue Nov 09, 2010 4:44 pm

Thanks for your query. The results are as below:
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?
maragatham
 
Posts: 9
Joined: Tue Nov 09, 2010 3:08 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Performance Tuning and AccessType

Postby 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
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Re: Performance Tuning and AccessType

Postby 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?
maragatham
 
Posts: 9
Joined: Tue Nov 09, 2010 3:08 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Performance Tuning and AccessType

Postby 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

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>)
cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort
enrico-sorichetti
Global moderator
 
Posts: 2643
Joined: Fri Apr 18, 2008 11:25 pm
Has thanked: 0 time
Been thanked: 130 times

Re: Performance Tuning and AccessType

Postby 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
     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
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Re: Performance Tuning and AccessType

Postby GuyC » Tue Nov 09, 2010 7:35 pm

actually it makes more sense to
order by l.version,l.queryno,l.bind_time,l.qblockno,l.planno,l.mixopseq,f.stage
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post