Hi Adarsh, Below are those 2 parameters
cursor_sharing=FORCE _b_tree_bitmap_plans=False Refer Page 39 of Performance tuning document. Regards/Vaibhav On Thu, Oct 25, 2012 at 8:58 PM, Adarsh <[email protected]> wrote: > ** 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"_ >> > > _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"
