Categories
- All Categories
- 137 Oracle Analytics News
- 24 Oracle Analytics Videos
- 14.6K Oracle Analytics Forums
- 5.6K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 52 Oracle Analytics Trainings
- 9 Oracle Analytics Data Visualizations Challenge
- 4 Oracle Analytics Career
- 2 Oracle Analytics Industry
- Find Partners
- For Partners
Oracle DAC is taking 4 hrs to start running the first task
Hi The nightly ETL is running for longer hours, when we investigated the logs, we found that the first task / ETL is actually starting after 4 hrs.
Ex: Execution plan scheduled to start @ 12:00 AM where as the first task starting @ 4:27 AM which is leading to load delay.
If we restart Database service, DAC service and Informatica service all together then the first task is starting @ 12:45 AM.
We tried below scenarios
1) Restarting Database service only --> no change in the load time
2) Restarting Informatica and DAC alone --> no change in the load time
3) Deleted all the inactive sessions in the DB --> no change in the load time
4) Increased heap memory in the DAC machine (from 2 GB to 8 GB) --> no change in the load time
5) Applied latest DAC patch (of the suitable version) --> no change in the load time
6) Analysed DAC repository tables --> no change in the load time
7) Purged the DAC logs --> no change in the load time'
Any thoughts and suggestions are greatly appreciated.
Load stats
DAC version
Thanks,
Anil.
Answers
-
Anil Vemula wrote:We tried below scenarios 1) Restarting Database service only --> no change in the load time2) Restarting Informatica and DAC alone --> no change in the load time3) Deleted all the inactive sessions in the DB --> no change in the load time4) Increased heap memory in the DAC machine (from 2 GB to 8 GB) --> no change in the load time5) Applied latest DAC patch (of the suitable version) --> no change in the load time6) Analysed DAC repository tables --> no change in the load time7) Purged the DAC logs --> no change in the load time'
Instead of trying out things you should rather first try to find out what's happening and why. Randomly changing things will never tell you what the root cause of an issue is.
0 -
Hi Christian,
DAC server log didn't give any info, we approached Oracle support, they are suggesting to check with DBA, DBA doesn't find anything in the DB logs, then Oracle support suggested to increase heap, that doesn't help, then Oracle support suggested to increase RAM on the DAC server.
We have tested various scenarios and shared those details (that I mentioned in my original post ) to give you some basic understanding.
We are not able to find the root cause of the issue, even Oracle support couldn't find, hence I approached you all.
Please share any idea / thought to find out the root cause.
Thanks,
Anil.
0 -
Anil Vemula wrote:DAC server log didn't give any info, we approached Oracle support, they are suggesting to check with DBA, DBA doesn't find anything in the DB logs, then Oracle support suggested to increase heap, that doesn't help, then Oracle support suggested to increase RAM on the DAC server. We have tested various scenarios and shared those details (that I mentioned in my original post ) to give you some basic understanding. We are not able to find the root cause of the issue, even Oracle support couldn't find, hence I approached you all.
O_o Ok I feel really sorry for you now. You're describing the point at which personally I'd simply have installed a second new env since a long time and just ran tests there and if push comes to shove trash the problematic env.
We can't reproduce the precise things done unless you dump all information in here (which you probably can't) so we can't implicitly know what was analyzed.
Seriously just create a new env.
0 -
We found the solution for this problem. After implementing the below solution the ETL started within 2 minutes after the execution plan starts, earlier it took 4 to 5 hrs to start the first ETL.
1) Enabled the row movement for all the DAC REP tables and recovered space by executing the below sql statements.
* ALTER TABLE <DAC REPO TABLES> ENABLE ROW MOVEMENT;
* ALTER TABLE <DAC REPO TABLES> SHRINK SPACE;
2) Cleared up all unwanted execution plans (I don't think this improved the performance but good to clean up.)
Thanks Chris for your responses.
Regards,
Anil.
0