Vengoal Chang's blog on IBM i (AS400): 4月 2011

星期三, 4月 27, 2011

Use Ops Navigator to determine if the system is advising new access paths

You can use Ops Navigator to determine if the system is advising new access paths, look for combinations of high costs and frequency. This little SQL can show those suggested since last IPL

Code:


SELECT DBNAME as Library,                                     
      SYS_TNAME as File,                                     
      decimal(timesadv,6) as Advised,                        
      date(lastadv) as Last_Adv,time(lastadv) as Time_Adv,   
      decimal(querycost,6) as Cost,                          
      decimal(queryest,6) as Avg_Cost,                       
      decimal(mticreated,6) as MTI_Create,                   
      date(lastmtiuse) as MTI_LastUsed,                      
      substr(KEYSADV,1,40) as Keys_Advised,                  
      substr(leadkeys,1,40) as Key_Order                     
FROM qsys2/sysixadv                                           
where mticreated > 0                                         
ORDER BY queryest DESC