Hi Vaibhav I am not able to view the Parameters which you have suggested . could you please send it again..
Regards, Adarsh On Fri, Oct 26, 2012 at 7:17 AM, vaibhav wadekar <[email protected]>wrote: > ** Adarsh, > > Based on this,it appears that it is using a bitmap index and I woud > recommend you to try below two parameters > > > > > So setting above two parameter will help you resolve the problem. > > Hope this helps. > > Regards/Vaibhav > > > Do you know the Cursor-Sharing Parameter set to what value? Did you try > setting that to FORCE and setting the > > On Thu, Oct 25, 2012 at 2:45 AM, Adarsh <[email protected]>wrote: > >> ** 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" >>> >> >> _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"
