Our DBAs have a set of jobs they run against the database to do things like; Move any indexes to the correct tablespace, If it is a "Primary" index make it an actual primary index (on Oracle these are the IT##, IB##, and IH## indexes), ...
This way Remedy knows about the indexes and the DBA only does an ALTER INDEX statement on them. Fred -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Kevin Murray Sent: Friday, August 17, 2007 5:18 PM To: [email protected] Subject: Re: ITSM 7 indexes Rick, I agree and am guilty of this all the time...especially when working on an indexing strategy for an remedy app having some performance problems. It's a lot quicker creating/dropping indexes while investigating than going through the admin tool...especially when using debug tools that come with the respective database tool (e.g. AUTOTRACE with SQLPLUS) There's plenty of reasons to do it (create indexes) directly at the db level..the ardb.conf is a method really to close the gap between what the arsystem does generically across all databases (simple create index statement with no elaborate storage clauses etc.) and the bells and whistles that one can get with the modern day relational database today..the dba you mentioned could have used the ardb.conf file (if it was a version of Remedy that supported it..think it came in around 4.x time)...the reason a tweak to the statement was required in your Sybase scenario was to setup some of the replication modes (transactional, snapshot) your database table MUST have a primary key constraint on it (Remedy Request ID columns (C1) by default are simply unique not null constraints - similar to a primary key but not the full deal) hence your (or the) DBA had to intervene to add the primary key constraint. Remedy use of relational databases is quite basic, I remember the days when studying RDBMSs in college and the first thing that you were taught was to have a a primary key constraint on a table...but here we are 37+ years since Mr.Codd I think invented the RDBMSs that some applications kinda fudge it. It's one area I'd wish Remedy's install script were improved...rather than to have to mess with a ardb.conf (in advance of creating something)...I've sincerely hope the re-vamped admin tool has the capability for advanced developers to interface better with the database as well...more often then not in my experience, customers pay some bucks for support with say Oracle or SQL Server, we install Remedy one day and it's nearly also left on that DB flavour , but yet we have to work *harder* to get it (Remedy) to work with some of the more impressive features of the underlying database. Later, Kevin Quoting Rick Cook <[EMAIL PROTECTED]>: > Thanks, Kevin. I remember a discussion with a Sybase DBA once who > said that the way he could create the indexes at the DB level was more > efficient than those created via Remedy, so he recreated the Remedy > defined ones directly in the DB. It was also necessary for his DB > replication process. I'm sure that some Oracle DBAs might have the same claim. > > Rick > > On 8/17/07, Kevin Murray <[EMAIL PROTECTED]> wrote: > > > > Guys, > > > > -The ARSystem does not have a built-in Query Optimizer, but one can > > change server configuration settings that could influence the > > respective database query optimizer that interprets the SQL (passed > > to it from Remedy, many of these config seetings can be manually set > > through the ar.conf file directly such as removing the order by > > clause from most of remedy generated sql...most statements by > > default are order by 1 (which is the request id column), most times > > there is no need to mess with with them (Direct SQL statements are > > passed as is) > > > > -Each supported database does have a query optimizer (e.g. Oracle - > > Rule-Based (<=9i supported), Cost-Based Optimizer) > > > > -The database optimizes the statement (or thinks it optimizes a > > statement > > - the > > optimiser isn't always right...lots of variables at play) > > > > -If an index is created through the admin tool, the arsystem > > application server issues the respective create index statement, > > whilst also updating a number of Remedy data dictionary tables (e.g. > > schema_index) so that Remedy knows about the index when you view > > form properties in the admin tool or for when you export a form and > > import (in place) or to another remedy server - this ensures the > > index travels with the form > > > > -If you create an index at the database level independent of the > > admin tool (through for example - sqlplus), the index exists and is > > noted in the relevant database dictionary table (such as > > dba_indexes, user_indexes in Oracle). > > Obviously Remedy knows nothing about it, so if one was to view the > > indexes in the admin tool for the form, it wouldn't show up, nor > > would the index be created on another server it the form was > > exported/imported..BUT > > > > -Yes, absolutely the database optimizer will evaluate all > > appropriate query plans, which may or may not involve the index > > created at the database layer by the DBA/Developer (which Remedy may > > or may not know about) etc..so an index created at the database > > level unknown to remedy (arsystem) can be used by the respective > > database optimizer(either negatively or positively - optimizer not > > always right, but one expects it is more often than not!) > > > > I've seen the odd occassionally where people are in twist having > > completed a remedy migration from one server to another, and it has > > turned out that the dba created an index at the db level at some > > point that wasn't brought across..this situation happens for a > > variety of reasons...dba knows nothing about remedy, dba wants to > > improve the parameters passed to a standard remedy generated create > > index statement (and isn't aware of the ardb.conf feature or > > couldn't be bother with it) > > > > -Remedy (BMC's) recommendation is always through the admin tool > > (supported method), it you need to leverage the ardb.conf do so re: > > leafs...but it's your call...if you db it at the db level just make > > sure you've documentated it for future reference (by you or your > > peers) > > > > HTH, > > Kevin > > > > Quoting Rick Cook <[EMAIL PROTECTED]>: > > > > > True - sort of. ARS does not have its own query optimizer - it > > > uses the > > one > > > in the attached DB. So while it's technically accurate to say > > > that > > Remedy > > > doesn't optimize queries, in practice that's not as true, because > > > the DB does it for Remedy, so the queries are optimized. > > > > > > If you meant to say that ARS is oblivious to indexes created > > > directly in > > the > > > DB, that's correct. Would the DB use indexes that only it knew > > > about to optimize queries sent it by ARS? That's the 64-bit question. > > > > > > Rick > > > > > > On 8/17/07, Robert Molenda <[EMAIL PROTECTED]> wrote: > > > > > > > > ** > > > > > > > > From what I have noticed, ARServer is oblivious to indexes and > > > > does > > not > > > > optimization any SQL statements for usage of indexes. > > > > > > > > > > > > > > > > That is why it is up to the developer(s) to use queries > > > > appropriately... > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > *Thanks-n-advance*; > > > > > > > > *HDT Platform Incident / Problem Manager & Architect* Robert > > > > Molenda *IT OS PA* > > > > Tel: +1 408 503 2701 > > > > Fax: +1 408 503 2912 > > > > Mobile: +1 408 472 8097 > > > > [EMAIL PROTECTED] > > > > > > > > Quality begins with your actions. > > > > > > > > > > > > ------------------------------ > > > > > > > > *From:* Action Request System discussion list(ARSList) [mailto: > > > > [EMAIL PROTECTED] *On Behalf Of *Joe D'Souza > > > > *Sent:* Friday, August 17, 2007 10:55 AM > > > > *To:* [email protected] > > > > *Subject:* Re: ITSM 7 indexes > > > > > > > > > > > > > > > > Hi John, > > > > > > > > > > > > > > > > The ARS has no way of knowing what fields are indexed if you do > > > > not do > > it > > > > from the Admin Tool and hence the indexes created from the > > > > database > > level > > > > will not get utilized from the application except if you run a > > > > Direct > > SQL > > > > from ARS.. Direct SQL's are not really evaluated from the ARS > > > > and the > > ARS > > > > throws whatever SQL statement you type directly to the database > > > > and > > fetches > > > > results if any. So the database would use those indexes. > > > > > > > > > > > > > > > > Joe > > > > > > > > -----Original Message----- > > > > *From:* Action Request System discussion list(ARSList) [mailto: > > > > [EMAIL PROTECTED] Behalf Of *john rosquist > > > > *Sent:* Friday, August 17, 2007 10:27 AM > > > > *To:* [email protected] > > > > *Subject:* ITSM 7 indexes > > > > > > > > ** > > > > > > > > I am looking at adding indexes to my ITSM 7 application > > > > (ARS7.02) on > > SQL > > > > 2005. Does anyone know if it is still recommended to added the > > indexes via > > > > the admin tool, or can you use the underlying database to add > > indexes. I > > > > remember from the tuning class that you should use the admin > > > > tool, but > > I > > > > want to see if this is still the case. > > > > > > > > > > > > > > > > Based on the SQL profiler result, adding more information to the > > > > leaf, should improve performance. > > > > > > > > > > > > > > > > Note( T688 is CTM:People, 1,18,19,56 are company, ln, fn, Phone). > > > > > > > > > > > > > > > > CREATE NONCLUSTERED INDEX > > > > > > > > > > > > [_dta_index_T688_5_19947593__K46_K52_K53_K88_1_4_7_15_16_18_28_50_51_54_ 56_59_69_87_106_111] > > > > ON [dbo].[T688] > > > > ( [C1000000001] ASC, [C1000000018] ASC, [C1000000019] ASC, > > [C1000000056] > > > > ASC) > > > > INCLUDE ( > > > > > > > > > > > > [C1],[C4],[C7],[C179],[C200000006],[C200000012],[C260000001],[C100000001 0],[C1000000017], > > > > > > > > > > > > > > > > [C1000000020],[C1000000022],[C1000000025],[C1000000035],[C1000000054],[C 1000000346], > > > > [C1000000846]) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, > > > > IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] > > > > > > > > > > > > > > > > Any ideas? > > > > > > > > > > > > > > > > Thanks, > > > > > > > > John Rosquist > > > > > > > > Senior Consultant > > > > > > > > Windward Consulting Group > > > > ________________________________________________________________________ _______ > UNSUBSCRIBE or access ARSlist Archives at www.arslist.org > ARSlist:"Where the Answers Are" > R ---------------------------------------------------------------- This message was sent using IMP, the Internet Messaging Program. ________________________________________________________________________ _______ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the Answers Are" _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the Answers Are"
