Clarity PPM

Expand all | Collapse all

CLEANUP_CLARITY_FOR_DWH.xml

Jump to Best Answer
  • 1.  CLEANUP_CLARITY_FOR_DWH.xml

    Posted 05-09-2015 09:00 AM

    Good morning to all,  I have a question about the expected duration of the upgrade from 13.3.11 to 14.2.  My upgrade was running as expected when it seemed to stall on the  CLEANUP_CLARITY_FOR_DWH.xml script.  I discovered that the database was still executing a few DELETE statements, so I have continued to let it run.

     

    This upgrade is on a lab server, so I would expect it to run slower, but I am now concerned about my schedule for my other environments which have users.  It has been runnnig this step for close to 22 hours. 

     

    So, my question is about your experiences with upgrade runtimes, or any things you have discovered that might speed things up?

     

    Thanks,

     

    Terry



  • 2.  Re: CLEANUP_CLARITY_FOR_DWH.xml

    Posted 05-10-2015 11:42 AM

    Hi TDBecker,

     

    The file deleted lot for tables which includes the slice tables also. If you have huge data set it might take longer. Also check in the database if there is any activity going on or its hung at database then you need to start the upgrade again. Also you can check the install log if its getting updated or  not.

     

    My personal experience I ran upgrade on huge data set of about 150 GB and it took almost 50 hours to complete the upgrade.

     

     

    Regards

    Suman Pramanik



  • 3.  Re: CLEANUP_CLARITY_FOR_DWH.xml

    Posted 05-11-2015 10:48 AM

    Thanks Suman,   the upgrade finished in a little over 40 hours for me.  It spend most of its time in the PRTIMEENTRY delete statement.  What is so strange is that when I run a SELECT statment using the same logic, it runs in under 30 seconds, and returns 0 rows.  I've checked all of my pre-upgrade environements and get the same results.

     

    Our database scheam is about 62GB in size.

     

    We're going to reset everything and try again since this is our sandbox and we are practicing for our real upgrade.

     

    Terry

     




  • 4.  Re: CLEANUP_CLARITY_FOR_DWH.xml
    Best Answer

    Posted 05-11-2015 11:01 AM

    Thanks Terry, the DWH tables has cascading deletes that might take longer. Was all the services brought down during upgrade?

     

    Regards

    Suman Pramanik



  • 5.  Re: CLEANUP_CLARITY_FOR_DWH.xml

    Posted 05-11-2015 01:53 PM

    Hi Suman, thanks for your response.  Yes, we had everything down.  I noticed something new about the ugprade, it actually takes steps to bring the services down (maybe that is how it always performed and I never notices since we have been a weblogic shop for previous versions.  

     

    I was watching the database in OEM and it displayed the single delete statment using 98% of the CPU.   I couldn't find any blocking or waits that indicated there was a delay.

     

    Thanks again,

    Terry



  • 6.  Re: CLEANUP_CLARITY_FOR_DWH.xml

    Posted 05-11-2015 03:31 PM

    Hi Terry,

     

     

    DELETE statements are generally costly, so I believe this would be one of the reasons it takes longer on your environment. Also, it will depend from the amount of records you have in this table, please check this out with the query:


    select count(*) from prtimeentry


    The delete statement on a big dataset with many million records can take up to several minutes if run directly on the database.

     

    In addition to this the time will depend on your database I/O. We have seen rare, but normal cases when the upgrade took more than 24-36 hours due to I/O issues, with some of the installer scripts. Please ensure your database is running on supported hardware based on our 14.2 Release Notes, and if this is the case, work with your DBA to see if the I/O can be improved. If not, I would suggest you plan for having 2 days for the upgrade to run: unfortunately on some environments it just takes more time.

     


    I hope this helps.

     

    Kind Regards

     

     

    Nika Hadzhikidi
    CA Technologies
    Principal Support Engineer



  • 7.  Re: CLEANUP_CLARITY_FOR_DWH.xml

    Posted 05-12-2015 09:55 AM

    Thank you Nika and Suman for your responses to my question.  I wish I could mark you both as the Correct Answer.  I've asked my DBA to look for some things that migh help us trim down that upgrade time.

     

    I'll update this post if I find any helpful information that my benefit others.

     

    Thanks again,

    Terry

     

     

     

     

     

     



  • 8.  Re: CLEANUP_CLARITY_FOR_DWH.xml

    Posted 05-12-2015 10:05 AM

    Hi Terry,

     

    Glad to hear, we will wait to hear back.

     

    Regards

    Suman Pramanik



  • 9.  Re: CLEANUP_CLARITY_FOR_DWH.xml

    Posted 05-23-2015 10:16 AM

    Good morning. I'm repeating the upgrade on my development evironment.  The 3rd select statment has been running for over 46 hours now this time.  I have just under 3 million rows in the prtimeentry table. (2,824,186)

     

    I guess I'll just have to plan for this in my upgrade schedule. Prod should be faster since it will be a better server.

     

    I was wondering if there was any way to "test" the progress of the DELETE statement?  I guess I'm a little nervous when the install.log just sits there for this long.  I always love to see some sort of feedback.  It's petty boring just watching the grid in OEM trail on and on and on .  

    Has anyone every thought about running this statment prior to the upgrade?  If it is a cleanup step, might be something I would be interested in doing before I bring my application down.... 

     

    Thanks for all of your help.  It does mean alot having others sharing thoughts on Clarity upgrade issues.

     

    Terry



  • 10.  Re: CLEANUP_CLARITY_FOR_DWH.xml

    Posted 05-25-2015 03:15 AM

    Hi Terry,

     

    Yes its a nervous situation when the install.log doesn't update for long, the only way to check is to see if there is any transaction happening at the database side.

     

    Regards

    Suman Pramanik



  • 11.  Re: CLEANUP_CLARITY_FOR_DWH.xml

    Posted 07-28-2015 07:50 AM

    Hi Terry,

     

     

    Were you able to upgrade successfully as the script cleanup_clarity_for_dwh.xml took longer time.

     

    Regards

    Suman Pramanik



  • 12.  Re: CLEANUP_CLARITY_FOR_DWH.xml

    Posted 07-28-2015 09:24 AM

    Hi Suman,  thanks for checking in on me.  I was finally able to get it finished, but it did continue to take very long to complete.

     

    I’m really worried about having my system down for that period of time with the chance of a failure/restart.    I wish the whole cleanup step (BEGIN….END) was split into parts.  Maybe there is a good reason to package it all together.

     

    Thanks again,

    Terry



  • 13.  Re: CLEANUP_CLARITY_FOR_DWH.xml

    Posted 07-28-2015 09:30 AM

    Hi Terry,

     

    Yes we did see the delete statements taking longer and we are improving that in next release. Did it halted for you on the below script?

     

    DELETE FROM prTimeEntry

    WHERE NVL(prTimeSheetID,0) NOT IN (SELECT prid FROM prTimeSheet);

     

    Regards

    Suman Pramanik