Clarity Service Management

Expand all | Collapse all

How to avoid Issue's activity load preload on Service Desk startup

Jump to Best Answer
  • 1.  How to avoid Issue's activity load preload on Service Desk startup

    Posted 07-08-2014 11:16 AM

    Hallo.

    I need to avoid that Service Desk preloads, on service startup, issue's Activity log because the table contains too many records and preload takes,in total, about half an hour (80% of the total time to preload issalg table).

    Anybody knows how to modify NX.ENV or other configurations to do that?

     

    Unfortunately I'm not able to archive&purge inactive or older issues, because my colleagues needs the whole historical archive online for reporting.

     

    Thank you!

    Emanuele



  • 2.  Re: How to avoid Issue's activity load preload on Service Desk startup

    Posted 07-08-2014 01:15 PM

    Emanuele,

     

    Would you happen to have some cache entries in the web.cfg or  <hostname>-web*.cfg  files in  NX_ROOT\bopcfg\www  directory?

     

    SelListCacheExclude

     

    SelListCachePreload

     

    issalg is in Exclude option normally. Maybe you have it in the PreLoad section?

     

    _R



  • 3.  Re: How to avoid Issue's activity load preload on Service Desk startup

    Posted 07-08-2014 04:26 PM

    Thank you Raghu for your reply.

     

    I've just checked and issalg in, as normally, in SetListCacheExclude.

     

    SelListCacheExclude acc_lvls act_type_assoc ADMIN_TREE agt alg all_lr am_asset_map ANI api arcpur_rul arcpur_hist asset atev attmnt attmnt_lrel attr_alias audlog bhvtpl bhvtpl_wftpl bmhier bmrep bmlrel bns BU_TRANS ca_application_registration ca_asset ca_asset_source ca_asset_source_unrestricted ca_asset_type ca_cmpny ca_logical_asset ca_logical_asset_property chg chg_tpl chgalg chgcat_grp chgcat_loc chgcat_workshift CI_ACTIONS CI_BOOKMARKS CI_DOC_LINKS cnt country cpex cr cr_prp cr_prptpl crsq cst ctab ctp dlgsrvr dmc dcon dcon_typ EBR_ACRONYMS EBR_DICTIONARY EBR_DICTIONARY_ADM EBR_FULLTEXT EBR_FULLTEXT_ADM EBR_FULLTEXT_SD EBR_FULLTEXT_SD_ADM EBR_INDEX EBR_INDEX_ADM EBR_INDEXING_QUEUE EBR_INDEXING_QUEUE_ADM EBR_KEYWORDS EBR_LOG EBR_METRICS EBR_NOISE_WORDS EBR_PATTERNS EBR_PREFIXES EBR_PROPERTIES EBR_SUBSTITS EBR_SUFFIXES EBR_SYNONYMS EBR_SYNONYMS_ADM ES_CONSTANTS ES_MODES ES_SESSIONS ES_RESPONSES event_log event_type evt_dly evtdlytp fmgrp grc grp grp_loc grpmem help_content help_item help_lookup hier INDEX_DOC_LINKS in intfc ir iss issalg isscat_grp isscat_loc isscat_workshift iss_prp iss_wf KCAT KD KD_ALL KD_ATTMNT KD_FILE KD_QA kdlinks kmlrel KT_REPORT_CARD kwrd ldap ldap_group LONG_TEXTS lr lrel1 macro macro_type menu_tree menu_tree_res mfrmod mgs mgsstat nr nr_com ntfl ntfm ntfr O_COMMENTS O_EVENTS options P_GROUPS pcat_grp pcat_loc pcat_workshift pr prod_list prp prptpl response role_go_form rptm sapolicy saprobtyp session_log session_type SHOW_OBJ sqchg sqcr survey svy_ans svy_atpl svy_qtpl svy_ques svystat svy_tpl svytrk swex text_api tkt tspan url USP_PREFERENCES USP_PROPERTIES usq wf wftpl wftpl_chgcat wftpl_grp wspcol wsptbl kcd ktd

     

    It must be something else...

     

    Emanuele



  • 4.  Re: How to avoid Issue's activity load preload on Service Desk startup

    Posted 07-08-2014 09:23 PM

    What type of entries do you see in the SDM logs during startup?  Do you see something like this? (just a sample for actlog  in the SetListCachePreLoad)

     

    07/08 21:19:20.74 Server sqlagt:select3   4932 SIGNIFICANT  sqlclass.c        1049 The following statement took 4462 milliseconds: Clause (SELECT act_log.id, act_log.persid, act_log.description FROM act_log ORDER BY act_log.time_stamp DESC, act_log.id DESC) Input (<None>)
    07/08 21:19:22.35 Server sqlagt:select3   4932 SIGNIFICANT  prov_base.c       1010 SQL Query has returned (5000) rows for (domsrvr/superuser). Clause (SELECT act_log.id, act_log.persid, act_log.description FROM act_log ORDER BY act_log.time_stamp DESC, act_log.id DESC) Input (<None>)
    07/08 21:19:24.22 Server sqlagt:select3   4932 SIGNIFICANT  prov_base.c       1010 SQL Query has returned (10000) rows for (domsrvr/superuser). Clause (SELECT act_log.id, act_log.persid, act_log.description FROM act_log ORDER BY act_log.time_stamp DESC, act_log.id DESC) Input (<None>)
    07/08 21:19:25.80 Server sqlagt:select3   4932 SIGNIFICANT  prov_base.c       1010 SQL Query has returned (15000) rows for (domsrvr/superuser). Clause (SELECT act_log.id, act_log.persid, act_log.description FROM act_log ORDER BY act_log.time_stamp DESC, act_log.id DESC) Input (<None>)


  • 5.  Re: How to avoid Issue's activity load preload on Service Desk startup

    Posted 07-09-2014 04:03 AM

    The following are the statements in stdlog during start-up:

     

    07/09 09:57:59.53 ILSD-PRIMARY   sqlagt:select1046   16852 SIGNIFICANT  prov_base.c            965 SQL Query has returned (4490000) rows for (domsrvr/). Clause (SELECT issalg.id FROM issalg) Input (<None>)

    07/09 09:58:00.22 ILSD-PRIMARY   sqlagt:select1046   16852 SIGNIFICANT  prov_base.c            965 SQL Query has returned (4495000) rows for (domsrvr/). Clause (SELECT issalg.id FROM issalg) Input (<None>)

    07/09 09:58:00.89 ILSD-PRIMARY   sqlagt:select1046   16852 SIGNIFICANT  prov_base.c            965 SQL Query has returned (4500000) rows for (domsrvr/). Clause (SELECT issalg.id FROM issalg) Input (<None>)

    07/09 09:58:01.47 ILSD-PRIMARY   sqlagt:select1046   16852 SIGNIFICANT  prov_base.c            965 SQL Query has returned (4505000) rows for (domsrvr/). Clause (SELECT issalg.id FROM issalg) Input (<None>)

     

    Until whole table is loaded.

     

    07/09 10:00:43.85 ILSD-PRIMARY   sqlagt:select1046   16852 SIGNIFICANT  prov_base.c            965 SQL Query has returned (5715000) rows for (domsrvr/). Clause (SELECT issalg.id FROM issalg) Input (<None>)

    07/09 10:00:44.53 ILSD-PRIMARY   sqlagt:select1046   16852 SIGNIFICANT  prov_base.c            965 SQL Query has returned (5720000) rows for (domsrvr/). Clause (SELECT issalg.id FROM issalg) Input (<None>)

    07/09 10:00:45.02 ILSD-PRIMARY   sqlagt:select1046   16852 SIGNIFICANT  prov_base.c            980 SQL Query has finished (5723550) rows for (domsrvr/).  Clause (SELECT issalg.id FROM issalg) Input (<None>)

     

    Thanku!



  • 6.  Re: How to avoid Issue's activity load preload on Service Desk startup

    Posted 07-09-2014 10:48 AM

    Thank you Emanuele. The above logs do seem to indicate that the cache PreLoad is somehow coming into effect.  Maybe there's some other customization that's impacting this somehow in addition to the web.cfg entries.  Maybe you have multiple web engines on the primary/secondary and one of the <hostname>*web*.cfg  files is introducing the above behavior.

     

    May I recommend opening a support ticket for this for better traction?

     

    _R



  • 7.  Re: How to avoid Issue's activity load preload on Service Desk startup

    Posted 07-16-2014 05:23 AM

    I add a last more information, before opening a support ticket.

     

    I posted the same question to a friend, employee in Ca Italy, and he answered me as follows:

    In object definition you have to swap the MLIST and RLIST settings.

    Unfortunately this has the contraindication that activities list in Issue's detail wouldn't be loaded immediately but just by clicking 'Search' button.

     

    I have the file \site\mods\majic\issalg.mod which contains (I can't remember why I built this file in past ) :

    MODIFY FACTORY issalg {

      STANDARD_LISTS {

        RLIST ON;

      };

    };

     

    I tried modifying it, according to my understanding of my friend answer, to:

    MODIFY FACTORY issalg {

      STANDARD_LISTS {

        MLIST OFF;

        RLIST ON;

      };

    };

     

    But without success (nothing changed).

    Any more ideas?

     

    If not, I'm going to open a support ticked and, after resolution, I'm updating this discussion.

     

    Regards,

    Emanuele



  • 8.  Re: How to avoid Issue's activity load preload on Service Desk startup
    Best Answer

    Posted 07-17-2014 10:09 AM

    Hi Emanuele.

    Just to confirm the rootcause of the problem, set both definition to OFF, RLIST and MLIST, and see, if the problem still persist.

    I don't think that this will have effects on the issue detail Activities list, because the initial search is triggered in a different way, at least in the web interface. I remember some dependicies of these settings in the old vbop and java client. But this is history..... I also remember a golden rule, never ever set MLIST and RLIST differently.

    Btw., MLIST and RLIST is OFF for all the ticket types like Incidents and Changes, and everything is working fine.

    Give it a try....

    Regards

    .............Michael



  • 9.  Re: How to avoid Issue's activity load preload on Service Desk startup

    Posted 07-17-2014 12:11 PM

    Thank you for your reply.

    Actually I tried in our test environment and it works .

    Tonight I try to apply the solution in production environment and, if it works even there (as it should), I close the post and confirm your answer.

     

    Soon,

    Emanuele



  • 10.  Re: How to avoid Issue's activity load preload on Service Desk startup

    Posted 07-17-2014 06:36 PM

    It works!

    Really thanx!

     

    Emanuele



  • 11.  Re: How to avoid Issue's activity load preload on Service Desk startup

    Posted 07-22-2014 11:12 AM

    Great idea Michael, thank you.  Glad it worked out for you as well Emanuele.



  • 12.  Re: How to avoid Issue's activity load preload on Service Desk startup

    Posted 07-15-2014 01:54 PM

    You might also want to check to see if you have any stored queries that reference issalg and are being used by a scoreboard node.



  • 13.  Re: How to avoid Issue's activity load preload on Service Desk startup

    Posted 07-16-2014 07:32 AM

    Hi Lindsay!

     

    I've checked in our Stored query but I didn't find anyone pointing to issalg object.

    I've also looked for the object type allowed to stored_query and  issalg isn't listed (see below the full list) .

    Is my analysis correct?

    Can I exclude your hypothesis?

      

    symname
    ambiguity_twaciAmbiguous CI Transactions
    chgChange Order
    cnoteAnnouncement
    cntContact
    crRequest
    g_chg_queueGlobal Change Queue
    g_cr_queueGlobal Request Queue
    g_iss_queueGlobal Issue Queue
    issIssue
    iss_wfIssue Workflow Task
    KDKnowledge Document
    KT_LIFE_CYCLE_REPKT Life Cycle Report
    KT_REPORT_CARDKnowledge Report Card
    nrConfiguration Item
    nraAmbiguous Configuration Item
    O_COMMENTSKnowledge Document Comment
    sa_notifAssistance Session
    superseded_ciSupereded Configuration Item
    urlURL
    wfWorkflow Task

     

    Thanks,

    Emanuele



  • 14.  Re: How to avoid Issue's activity load preload on Service Desk startup

    Posted 07-16-2014 04:44 PM

    If anything, it would be in the iss object with a reference to one of the BREL (issalg,act_log, act_log_all) to the issalg.



  • 15.  Re: How to avoid Issue's activity load preload on Service Desk startup

    Posted 07-17-2014 03:58 AM

    I've checked in Stored Query (object crsq) with the following whereclause, without result.

    delete_flag=0 and obj_type.sym = 'iss' and (where_clause like '%act_log%' OR where_clause like '%issalg%')

     

    Anyway thank you for your advice!

     

    Emanuele