query run slow -bad query

[stmontgo]

I created an index on a table as a result turned out to be hero just destroying the run time of some processes to next to nothing.

Now that index is whacking another query. Where previously the query ran in seconds it takes 10 mins as it uses the new index as opposed to a FFS. All stats are current.

Funnt thing is with the inded the cost is lower for the problem query yet it still runs like a dog.

Gory details attached.

Any ideas?

[jmodic]

Originally posted by stmontgo
All stats are current.

Are you sure? What about the histograms of the columns included in the new index? Is their distribution squewed?

And btw, that Webfocus things appears to be oe of those incredible products where their developers never heard of the concept called "bind variables"....

[tamilselvan]

I worked with PeopleSoft (HR/PAYROLL) application. PS_JOB is very important table. If you add new index or drop existing index, then you need to test the whole application. Be careful.

SELECT T1."EMPLID", T1."COMPANY", T1."EMPL_STATUS",T1."NAME",   
       T1."ETHNIC_GROUP",T1."GRADE",T2."ROWSECCLASS"
FROM   SYSADM.PS_EMPLOYEES  T1,
       SYSADM.PS_PERS_SRCH_QRY T2
WHERE (T2."EMPLID" = T1."EMPLID")
  AND (T1."EMPL_STATUS" IN('A', 'L', 'P', 'S'))
  AND (T1."COMPANY" = '096')
  AND (T2."ROWSECCLASS" = 'DPALL') 

If I am not mistaken, PS_PERS_SRCH_QRY is a view. Can you post the view definition.

Tamil

[stmontgo]

Originally posted by tamilselvan
I worked with PeopleSoft (HR/PAYROLL) application. PS_JOB is very important table. If you add new index or drop existing index, then you need to test the whole application. Be careful.

SELECT T1."EMPLID", T1."COMPANY", T1."EMPL_STATUS",T1."NAME",   
       T1."ETHNIC_GROUP",T1."GRADE",T2."ROWSECCLASS"
FROM   SYSADM.PS_EMPLOYEES  T1,
       SYSADM.PS_PERS_SRCH_QRY T2
WHERE (T2."EMPLID" = T1."EMPLID")
  AND (T1."EMPL_STATUS" IN('A', 'L', 'P', 'S'))
  AND (T1."COMPANY" = '096')
  AND (T2."ROWSECCLASS" = 'DPALL') 

If I am not mistaken, PS_PERS_SRCH_QRY is a view. Can you post the view definition.

Tamil

far be it from me to make a mistake but...

We are using gather stale stats and having looked at a smallish table from the PS_PERS_SRCH_QRY view I could see that stats were last genereated two months ago. Once I generated stats on that table the query is back in business.

Yeah I know PS_JOB is a big deal, hence this index has been in test for 3 weeks before we even considered a production migration.

Thanks to both of you for your feedback