Page 1 of 1

Performance Tuning and AccessType

PostPosted: Tue Nov 09, 2010 3:55 pm
by maragatham
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

Re: Performance Tuning and AccessType

PostPosted: Tue Nov 09, 2010 4:17 pm
by GuyC
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

Re: Performance Tuning and AccessType

PostPosted: Tue Nov 09, 2010 4:44 pm
by maragatham
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?

Re: Performance Tuning and AccessType

PostPosted: Tue Nov 09, 2010 5:42 pm
by GuyC
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

Re: Performance Tuning and AccessType

PostPosted: Tue Nov 09, 2010 6:19 pm
by maragatham
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?

Re: Performance Tuning and AccessType

PostPosted: Tue Nov 09, 2010 6:25 pm
by enrico-sorichetti
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>)

Re: Performance Tuning and AccessType

PostPosted: Tue Nov 09, 2010 7:31 pm
by GuyC
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

Re: Performance Tuning and AccessType

PostPosted: Tue Nov 09, 2010 7:35 pm
by GuyC
actually it makes more sense to
order by l.version,l.queryno,l.bind_time,l.qblockno,l.planno,l.mixopseq,f.stage