Hi Joe, We did not find anything in the Remedy logs .. but below are findings from DB end
Please find the TOP I/O consuming SQL's in the database. ################################################################## SQL_ID 6zqfj98g199pp, child number 1 ------------------------------------- SELECT * FROM ( SELECT T2118.C1,C1000000161,C1000000000,C303497300,C100 0000164,C7,C1000000218,C1000005261,C1000000217,C1000003009,C1000000018,C 1000000019,C1000000150,C179,C1000000422,C1000000715,C4,C1000000079,C1000 000162,C1000002613,C1000005785,C1000005791,C1000000560,C1000000151,C1000 000099,C1000000056,C1000000001,C1000000080,C1000000020,C1000000163,C3,C3 03497400,C923051203 FROM T2118 WHERE ((T2118.C7 < :"SYS_B_0") AND (:"SYS_B_1" = :"SYS_B_2") AND (:"SYS_B_3" = :"SYS_B_4")) ORDER BY C1000000161 DESC, :"SYS_B_5" ASC ) WHERE ROWNUM <= :"SYS_B_6" Plan hash value: 4273177787 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | 172K(100)| | |* 1 | COUNT STOPKEY | | | | | | | | 2 | VIEW | | 334K| 968M| | 172K (1)| 00:34:30 | |* 3 | SORT ORDER BY STOPKEY| | 334K| 332M| 373M| 172K (1)| 00:34:30 | |* 4 | FILTER | | | | | | | |* 5 | TABLE ACCESS FULL | T2118 | 334K| 332M| | 99321 (1)| 00:19:52 | ----------------------------------------------------------------------------------------- ################################################################## SQL_ID 6ttp45bbmxgr5, child number 1 ------------------------------------- SELECT T2723.C1 FROM T2723 WHERE (((:"SYS_B_0" = T2723.C179) OR (:"SYS_B_1" = T2723.C490009000)) AND (:"SYS_B_2" = T2723.C490008000) AND (T2723.C300364200 = :"SYS_B_3")) ORDER BY C300364900 ASC,C1 DESC Plan hash value: 3787592043 ---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 52221 (100)| | | 1 | SORT ORDER BY | | 1 | 118 | 52221 (2)| 00:10:27 | |* 2 | TABLE ACCESS FULL| T2723 | 1 | 118 | 52220 (2)| 00:10:27 | ---------------------------------------------------------------------------- ################################################################## SQL_ID 6vq7xsws6ga2q, child number 1 ------------------------------------- SELECT T1581.C1 FROM T1581 WHERE ((T1581.C179 IS NULL) AND ((T1581.C302943300 = :"SYS_B_0") OR (T1581.C301721000 = :"SYS_B_1"))) ORDER BY C3 DESC, :"SYS_B_2" ASC Plan hash value: 736766182 ---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 57186 (100)| | | 1 | SORT ORDER BY | | 1 | 58 | 57186 (1)| 00:11:27 | |* 2 | TABLE ACCESS FULL| T1581 | 1 | 58 | 57185 (1)| 00:11:27 | ---------------------------------------------------------------------------- ################################################################## SQL_ID gbzhnmm1db1tw, child number 1 ------------------------------------- SELECT * FROM ( SELECT T2118.C1,C1000000161,C1000000000,C303497300,C100 0000164,C7,C1000000218,C1000005261,C1000000217,C1000003009,C1000000018,C 1000000019,C1000000150,C179,C1000000422,C1000000715,C4,C1000000079,C1000 000162,C1000002613,C1000005785,C1000005791,C1000000560,C1000000151,C1000 000099,C1000000056,C1000000001,C1000000080,C1000000020,C1000000163,C3,C3 03497400,C923051203 FROM T2118 WHERE ((T2118.C7 < :"SYS_B_00") AND ((T2118.C1000000079 = :"SYS_B_01") OR (T2118.C1000000427 = :"SYS_B_02") OR (T2118.C1000000079 = :"SYS_B_03") OR (T2118.C1000000427 = :"SYS_B_04") OR (T2118.C1000000079 = :"SYS_B_05") OR (T2118.C1000000427 = :"SYS_B_06") OR (T2118.C1000000079 = :"SYS_B_07") OR (T2118.C1000000427 = :"SYS_B_08") OR (T2118.C1000000079 = :"SYS_B_09") OR (T2118.C1000000427 = :"SYS_B_10") OR (T2118.C1000000079 = :"SYS_B_11") OR (T2118.C1000000427 = :"SYS_B_12") OR (T2118.C1000000079 = :"SYS_B_13") OR (T2118.C1000000427 = :"SYS_B_14") OR (T2118.C1000000079 = :"SYS_B_15") OR (T2118.C100000042 Plan hash value: 2646718021 -------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | 92257 (100)| | |* 1 | COUNT STOPKEY | | | | | | | | 2 | VIEW | | 75850 | 219M| | 92257 (1)| 00:18:28 | |* 3 | SORT ORDER BY STOPKEY | | 75850 | 67M| 74M| 92257 (1)| 00:18:28 | |* 4 | FILTER | | | | | | | | 5 | TABLE ACCESS BY INDEX ROWID| T2118 | 75850 | 67M| | 77445 (1)| 00:15:30 | |* 6 | INDEX SKIP SCAN | I2118_1000000427_1 | 75850 | | | 3505 (1)| 00:00:43 | -------------------------------------------------------------------------------------------------------------- ################################################################## On Wed, Oct 24, 2012 at 11:15 PM, Joe Martin D'Souza <[email protected]>wrote: > I'd also suggest to run the same SQL that is executed (capture it from the > SQL logs) directly against the DB and measure the response time. If there > is a significant difference in timing where the DB search is returned much > quicker, it would immediately tell you if it’s a problem at the DB level or > something within Remedy.. if within Remedy it could be factors like (or a > combination of factors like) number of records returned, whether or not > chunking is enabled on the AR side, etc. etc.. > > You could find out the same thing by taking a combined SQL and API log, > and analyzing that log.. > > Joe > > -----Original Message----- From: Longwing, LJ CTR MDA/IC > Sent: Wednesday, October 24, 2012 11:34 AM Newsgroups: > public.remedy.arsystem.general > > To: [email protected] > Subject: Re: Sarch on HPD:Help Desk from taking a very long time. > > And if you do a Show Plan, or, in whatever way is supported by your DB, > what does it say it's doing for those 25 seconds? > > -----Original Message----- > From: Action Request System discussion list(ARSList) [mailto: > [email protected]] On Behalf Of Adarsh > Sent: Wednesday, October 24, 2012 9:30 AM > To: [email protected] > Subject: Re: Sarch on HPD:Help Desk from taking a very long time. > > ** I have following indexes on the HPD form > > > On Wed, Oct 24, 2012 at 8:45 PM, Adarsh <[email protected]> > wrote: > > > Thanks Tauf, > > I am trying to search all open tickets (status < "Resolved") assigned to a > particular group > > 'Status*' < "Resolved" AND 'Assigned Group*+' = "Service Desk" > > > > > > > On Wed, Oct 24, 2012 at 8:14 PM, Tauf Chowdhury <[email protected]> > wrote: > > > ** There are various factors such as how much data you are searching on, > indexing, etc... > What kind of searches are you doing? Are you searching for words in the > summary or Notes field? > What if you left everything blank and only select Status = "In Progress" ? > Does the data come back quicker? The status field is indexed by default (I > think) so just check that and report back. We would need more info to help > you though. > > > On Wed, Oct 24, 2012 at 10:40 AM, Adarsh <[email protected]> > wrote: > > > ** Hi , > > > We are using Remedy 7.6.04 SP2 , currently we are facing a weird issue, > The application is working perfectly fine except for searches on HPD:Help > Desk form, > > everything from database end has been checked, we did a re-org of the > tables and also rebuilt the indexes but still the issue is not resolved. > > its takes around 25 seconds to refresh the data.. > > Has anyone faced such an issue.. ??? :-( > > > Regards, > Adarsh > > > > _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_ > > > > > -- > Tauf Chowdhury > > > _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_ > > > > _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_ > > ______________________________**______________________________** > ___________________ > UNSUBSCRIBE or access ARSlist Archives at www.arslist.org > attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are" > ______________________________**______________________________** > ___________________ > UNSUBSCRIBE or access ARSlist Archives at www.arslist.org > attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are" > _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are"
