Hi,
WE have an issue in production as below:
Environment details:
ARS 7.1 patch 004
ITSM ver 7 patch 007
On a change form, while realting a change to another change, when we click on
the search button on the Infrastructure change relationship search form and
enter the change number, it takes more than 1 minute to return the result.
We have checked the logs and found the below active link and the query which is
taking time :
AL: CHG:CSA:Search_100
DB query:
SELECT * FROM ( SELECT
T1084.C1,C260007003,C1000000182,C7,C1000000000,C1000000403,C1000000598,C1000000349,C1000000363,C1000000350,C1000000362,C1000000001,C1000000348,C1000003228,C1000003229,C1000003227,C1000003230,C1000000181,C1000000163,C1000002270,C1000002269,C1000001270,C1000001271,C1000001272,C1000002268,C200000012,C1000000082,C1000000019,C1000000018,C1000000180,C1000000065,C1000000064,C1000000063,C260000001,C200000007,C1000000251,C1000000015,C1000000014,C1000000568,C1000000162,C1000000364,C1000000150
FROM T1084 WHERE (((T1084.C1000000598 = ' ') OR (' ' = ' ')) AND
((T1084.C1000000408 = ' ') OR (' ' = ' ')) AND ((T1084.C1000000251 = ' ') OR ('
' = ' ')) AND ((T1084.C1000000014 = ' ') OR (' ' = ' ')) AND
((T1084.C1000000079 = ' ') OR (' ' = ' ')) AND ((T1084.C1000003228 = ' ') OR ('
' = ' ')) AND ((T1084.C1000003227 = ' ') OR (' ' = ' ')) AND
((T1084.C1000003234 = ' ') OR (' ' = ' ')) AND ((T1084.C1000003231 = ' ') OR ('
' = ' ')) AND ((T1084.C1000000063 = ' ') OR (' ' = ' ')) AND
((T1084.C1000000064 = ' ') OR (' ' = ' ')) AND ((T1084.C1000000065 = ' ') OR ('
' = ' ')) AND ((T1084.C1000001270 = ' ') OR (' ' = ' ')) AND
((T1084.C1000001271 = ' ') OR (' ' = ' ')) AND ((T1084.C1000001272 = ' ') OR ('
' = ' ')) AND ((T1084.C1000002268 = ' ') OR (' ' = ' ')) AND
((T1084.C1000002270 = ' ') OR (' ' = ' ')) AND ((T1084.C1000002269 = ' ') OR ('
' = ' ')) AND ((T1084.C1000000182 = 'CRQ000000520052') OR ('CRQ000000520052' =
' ')) AND ((T1084.C1000000001 = ' ') OR (' ' = ' ')) AND ((T1084.C200000012 = '
') OR (' ' = ' ')) AND ((T1084.C200000007 = ' ') OR (' ' = ' ')) AND
((T1084.C1000000074 = ' ') OR (' ' = ' ')) AND ((T1084.C1000003254 = ' ') OR ('
' = ' ')) AND ((T1084.C1000003255 = ' ') OR (' ' = ' ')) AND
((T1084.C1000003259 = ' ') OR (' ' = ' ')) AND ((T1084.C1000003258 = ' ') OR ('
' = ' '))) ORDER BY 3 DESC, 1 ASC ) WHERE ROWNUM <= 51
When we check the cost of the query, it shows as 1 and shows use of index on
field C1000000182 but still while running this query it takes more than 1
minute to return the result in the table field.
Steps to replicate :
1. open any existing change
2. goto relationship tab
3. select request type as infrastructire change and click on search
4. Infrasturctire change search form opens, enter the change id to be related
and click on search again
5. The table below gets populated with the change id that you entered but it
takes more than 1 minute to return the result.
Has anyone come across this issue?
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
"Where the Answers Are, and have been for 20 years"
